Jamie Thomson

Thoughts, about stuff

Archive for March 2013

Charting London Borough of Hounslow expenditure

leave a comment »

In November 2012 my family and I moved into the London Borough of Hounslow and as I am expecting to be here for a very long time I decided to avail myself of some information pertaining to how the council spends its money. All expenditure is published on the council website at Council spending over £500. Its great that that information exists and is published however the format in which it is published isn’t particularly useful to folks like me that want to analyse and drill into the data in greater detail, what we get is a PDF file (rubbish) and a CSV file (better) per month:

image_thumb1

Why is PDF rubbish? Because the data is static, we can’t explore it, reshape it, drill into it. The data is presented in whatever format the person who produced the PDF decides. This is all bad bad bad. CSV on the other hand (which stands for comma-separated-values) is better because it contains only raw data and there’s no pre-determined presentation of the data. One can take the monthly CSV files and collate them into a single view that allows exploration and comparison of the data and that is exactly what I have done; I have taken all available data (from April 2012 onwards) and published it online at All London Borough of Hounslow Supplier expenditure over 500GBP since April 2012.

The publishing format is a Microsoft Excel workbook however you do not need Excel installed in order to view it, you only need a web browser. You do have the option to download the workbook to take advantage of the greater power of Excel and do your own analysis.

Putting the data into Excel enables us to provide summaries and visualisations over the data such as expenditure per month:

Top ten expenditures per external Supplier, Expense Type & Service Area:



 

All the charts are attached to objects called slicers that makes them interactive. Here’s an example of a slicer:

image_thumb7

Clicking on a Supplier will cause the charts to display data for only that Supplier (you can select multiple Suppliers by holding down the CTRL button).

Similar to Slicers are Timelines which enable us to show data for a particular month or groups of months:

image_thumb9

Importantly, i shall be adding new data to this Excel workbook as and when it becomes available so check back every month to see the data changing.

The first month for which data was available was April 2012 hence when April 2013 rolls around we can start to do year-on-year comparisons and that is when the information might start to reveal some interesting spending trends of the council.

If you’re interested in the council’s absolute total expenditure since April 2012 I show that on the first sheet:

 

Finally, having access to all this data enables to discover interesting facts such as how much the council has spent with a particular chauffeur supplier:

 

If you find any other interesting titbits hidden in this corpus do let me know!

I encourage you to take a look and if you have any feedback please leave a comment below.

@Jamiet

Advertisements

Written by Jamiet

March 24, 2013 at 6:58 pm