OpenHalton

PART 1: Why Expense Data should be Open Data ?

Earlier this week I found out that the City of Vancouver had added their Council Expenses as open data to the city’s Open Data Catalogue. This is a great example of municipal government transparency and commitment to open data, as demonstrated by their posting of detailed expense transactions starting in year 2010.

This type of data begs to be visualized, pivoted & presented to citizens in rich formats.

While some think open data “geeks” are out to use open data as “weapons to hold the system accountable”, I think it’s our right to be able to derive INSIGHTS and drive ALL possible benefits from this data being opened.

Not only does it help understand how our taxpayer’s money is being spent, but along the way we get a really clear sense of how much our councilors are doing. Even a quick glimpse at the Vancouver Expense data reveals how much time councilors spend in the community: “Business and Event Expenses” is by far the largest category of expenses.

Check out what I did with this data: Vancouver Council Expense – 2010 Visualization

(Note: as of writing of this post, the City of Vancouver denied my written request to use council images from their site, making it quite boring by comparison to my desktop version below – that I can’t share according to the city’s Terms of Use)

When will Hamilton & the Halton municipalities release their Council Expense data? How long would it take our administration to reach a level of comfort and confidence to do what Vancouver, and so many other cities around the world do routinely?

PART 2: How did I do it?

I have just watched a cool TED video with Gary Flake talking about LiveLabs PivotViewer, so I wanted to try and mash-up and visualize Vancouver’s data using PivotViewer. Using some relatively simple data transformation tools, you can build a “collection” that can be visualized via an older desktop app called Live Labs Pivot or a newer Silverlight app, resulting in this:

My experience with Expense Data visualization in 3 steps:

1. Downloaded & cleaned up the XLS from the City’s catalogue (copied “raw data” for expense details into a standalone sheet RawData, pivoted RawData table to make sure totals match City’s Totals, added a council Table with council details (image URL, when elected, etc) and merged all of the data fields into one table on a CollectionData sheet. Along the way I added some simple formulas to extract council names, and calculate percentiles for each expense line item. Here’s the data source XLSX I ended up with.
2. Downloaded & installed all the PivotView pieces I needed to build & visualize: Silverlight Control, Pivot Collection Tool (Pauthor OSS project), LiveLabs Pivot (for local testing) and the PivotViewer Tool for Excel. For Pauthor I had to install the 2007 Office System Driver pack (in the process I found I needed to recompile for x86 it to fix this issue with Pauthor pick up the right drivers on a 64-bit OS). Next I followed the UserGuide & modified the .XLSX for the sample collection included with Pauthor: copied the CollectionData table into the “items” sheet, configured the “facet_cateogories” & lastly, got creative to use a VLOOKUP to specify a custom image for each item based on the type of expense. Here’s the collection XLSX I ended up with.
3. Lastly, I tweaked the .htm template for Pauthor so that the resulting image for each item in the collection merged images of councilors with custom image for each type of expense, and show a couple of extra fields. All I had to do then is to run Pauthor to build the collection, and then create a very simple Silverlight app with the PivotView control following this guide. I ended up with a PivotView app that loaded my collection from URL, so in the future I’ll just need to upload a new collection…. which brings me to this question:

So, when can we can see some Halton or Hamilton Council expense data? For that matter, any expense, budget or financial data?

Does anyone know if those get reported and/or captured in any sort of “data format”??

If Vancouver can do it, why can’t we?