Excel Macro Development

Microsoft Excel is an extremely powerful tool for manipulating large amounts of data and organising it in a useful way. However, much of the time spent is on tasks that could be automated by creating a Visual Basic for Applications (VBA) macro. An example could be turning data exports from Google Analytics into a PDF report for customers.

Using an Excel macro can be quicker and more convenient to set-up than developing an entire software application, especially if all the data is already in a spreadsheet. When the Excel document grows in the amount of data or its scope you may need to redevelop it as a full app. But by that time, you will have a much better understanding of your requirements.

My Portfolio

Monthly SEO Report

Summary: turns data exports from Google Analytics and Search Console into a structured report with graphs so customers know how an SEO contract is affecting their website’s traffic.

First data is exported from Google Analytics and Search Console by the user with specific constraints (e.g. only data for organic traffic). Some sheets needed to be filled in manually such as Search Console data because at the time you could only export the last three months worth of data.

Then the user clicks a “Generate Report” button and is prompted to select the CSV files they exported earlier. If any files were skipped by the user, the section wouldn’t be included in the final document (e.g. non-ecommerce websites didn’t have sales data).

Once complete, the macro would generate a Word document report showing the customer how their website has been performing. It would include bar charts, pie graphs, and tables that were automatically generated. This file would be reviewed by an SEO Analyst for adding a summary and further actions.

Weekly Backup Check

Summary: a checklist is generated for the user to manually verify that backups have run successfully.

The user would click a “Generate List” button. This would generate a list of servers that need to be checked and notes about them. If the backup had run successfully (if there are any files in the location specified in the notes) the user would choose “Yes” from the dropdown, otherwise “No”.

Once the user had completed the list, they would click the “Submit” button which would:

  1. Send an automatically generated email to the manager if any hadn’t backed up. The email title would depend on the priority specified for that server.
  2. Copy the data to an archive sheet so there is a record of which backups are successful and which aren’t. This information could be used to see which backups fail and if there is any pattern to when and why.

There is a sheet that stores a table of the servers. This includes extra information such as their backup folder location (the backups are pulled from the live server), priority and more.

Bandwidth Tracking

Summary: a tool that takes data exported from WHMCS and generates a list of websites to manually check bandwidth usage for in AWStats.

The user would first export customer data from WHMCS. Then they would click the “Generate” button and import the customer data to get a list of actively paying websites. The user would then manually input the bandwidth for this month from AWStats. When all websites have been filled out, they would click the “Submit” button.

The macro would copy the data to an archive sheet. Each website would be checked (using the archive sheet) to see if it has been over its bandwidth limit for three months. If it has, an email would be sent to the website’s account manager.

Support Centre Data Comparison

Summary: a tool that takes data exported from WHMCS and other sources to make sure they all match up (e.g. no terminated websites still on any of the servers).

First the user would export data from WHMCS (including active paid websites). Then they would run a VBScript inside the WWW folders of each server to get a text file listing the websites. The user would then click “Compare” in the macro and import the two exported files.

These would be compared on a new sheet and any discrepancies would be highlighted in red. For example, customers would occasionally not be paying for their web hosting due to an administration error and this tool would highlight this sort of case.

Portfolio Links