The Useful Welsh Government Expenditure Data (Website that I built)
Over dinner last week I was discussing the Welsh Government "teacher portal" Hwb+ (as you do. Hwb and Hwb+ are different systems created by different companies. Learning Possibilities created Hwb+ and CDSM created Hwb). After dinner the friends I was with asked me how much had the Welsh Government spent on Hwb+/Hwb over the years - "5 million, I guess" was my off the cuff reply. But this got me thinking - where were the numbers on this and how difficult would it be to find?
It turns out that on face value that the answer to the question should be right there online as the Welsh Government publishes all their expenditure over £25,000. The data can be download here. Each ODS (an open source spreadsheet file format - which Excel can read) file contains a years worth of data extracted from Welsh Government finance data.
Seems promising, but this is where the problems begin:
(1) Each ODS file contains the data for each month as TABs - one for each month.
(2) The field mapping from month to month is not consistent - sometimes the columns containing data swap between months
(3) The formatting of dates changes between xx/yy/zz, xx-yy-zz and xx.yy.zz
All that conspires to make the data as it stands useless for answering the question above. Indeed, as the data stands it's a all but useless for its intended purpose. What's needed is for all the data to be placed onto one sheet, all the "dirt" to be removed and for this data set to be search-able.
What I did:
I first downloaded all the data and consolidated it onto one sheet, removed the noise by aligning all the columns and sorted out the date formating. That CSV file can be downloaded here:
That file allowed me to answer my initial question about Hwb+ (more of that later).
I wanted to make the data available in a useful form. Yes, the CSV can be downloaded but could I serve this up via the web in a more useful manner?
Requirements for the site
- Load data from csv file
- Dynamically create a table to display the data
- Allow the table to be searchable
- Allow for data to be exported
In my mind, I'm going to create a site that allows people to search for "Company XYZ", filter the table and to export the results to Excel as needed.
So at times like this StackOverflow is the website to visit and post your problem (StackOverflow is a forum for programmers to ask questions and get some feedback). I posted a question and waited in anticipation. Initially the feedback was good (but not useful to me - the assumed level of competency was too high.) - "use Ajax for server side programming", "use MySQL and PHP" - all useful but i needed a noob answer.
Eventually "annoyingmouse" posted some code that did essentially what i was looking for - opened a CSV file and dynamically created a table. Some Googling enhanced this table with export features and I was essentially ready to go.
So i present The Useful Version of Welsh Government Expenditure web site (here) - it takes ~6 seconds or so to load on desktop as it's pulling in a CSV file with 137,000 rows to create the table - but give it a go. Search for a company and export their data. As a bonus the table also displays the Welsh Government document number, so if you are going to file a Freedom of Information request, you can reference the right document (more on that in a new post).
Well from Welsh Government official data the taxpayer has spent nearly £10million on the services over the years. These services give every teacher and student in Wales email, blogging, cloud based file storage and a search-able cloud based resource creation and sharing portal - but this isn't a post about Hwb+/Hwb - it's about open data and allowing the taxpayer to easily have access to it. For those interested a chart of the expenditure looks like this:
I'll write a technical post about this if anyone is interested ;-)
Leaves me to wonder about other companies, data journalism and accountability - but that's a blog for another day.