The aim of this project was to produce a tailored quarterly report for the Chief Executives of each Local Health District (LHD) in New South Wales, with the intention of providing a summary of how their district is measuring against a selection of NSW Health's Key Performance Indicators.

This report was to be provided as a PDF download to the Chief Executive of each LHD. The goal was to build a set of visually appealing, yet easy to interpret charts that at a glance, would provide the recipient an overview of their districts performance.

To do this, I investigated a selection of data-visualisation tools; grading them on their flexibility and customisation, to the quality of their output as a PDF document. Crisp vector graphics was a must; for file size and clarity of presentation.

Few web-orientated BI tools approached these requirements; being targeted for the screen they tended to produce artwork that was pixelated at high zoom levels, and they lacked the degree of customisation I was after, instead having elements that were either fixed and not easily changed.

To have that level of customisation I opted to use my programming background to assist me in this task. Despite the supposedly inferior quality of Excels charting tools, I was able to coax it into producing some very appealing designs just by manipulating the charts directly and drawing shapes through scripting. I wrote a program that would aggregate data from CSV files, update the series in the excel charts and then transfer these to a predesigned PowerPoint document template. PowerPoint was chosen as the documentation tool because of it's simple slide-based model and close integration with Excel.

Despite having a finished working report, I eventually started running into road-blocks with Excel. Bugs that made it more time-consuming to work around than I liked; plus there was issues with speed and stability, essentially running two pieces of software together and making them interact. I wanted to simplify the process.

My first change was switching to using a database instead of directly pulling data from CSV files. All my data-manipulation code was rewritten as SQL queries, vastly simplifying the task.

The second change was more ambitious; I wanted to do away with the interaction between PowerPoint and Excel. I still needed PowerPoint for layout, so I had to work out another way of producing charts. Native PowerPoint charts were far below and too simple for what I needed. I looked at using Python libraries for interacting with PowerPoint, but they proved to be too slow at produce the reports.

I settled on writing a 'near enough' copy of Excel's chart model purely in VBA and some simple geometry algorithms. You could add series and chart elements as you would in Excel VBA, except instead of using buggy chart objects; the tools drew all the charts using simple lines and shapes. While there was a large start-up cost, overall being able to dive into the chart drawing code has proved a massive advantage and allowed me to fix the bugs behind Excel's charts.

In addition to this, I wrote a VBA SQL interface to make it easier to manipulate data, and a VBA JSON parser as an improved logging tool for debugging.