Today marks the deadline for councils to start publishing details of all spending over £500. Local government minister Eric Pickles says he expects all councils to be as open as possible. Some, such as Liverpool, have admitted they’ll miss that deadline, and final details of exactly how all councils should produce the information has yet to be issued.
So how should journalists deal with the data? Here are ten points which I hope might help…
1. Finding the data
Finding the data in the first place can be a bit of a pain but there are a number of sources which have sought to create one list of links. The Guardian’s Data Blog has a list of councils which have published and in what format here. The Communities and Local Government also keeps a list, which is visualised in a Dipity timeline.
2. Top-level data
If you are only interested in the top-level data – the companies which get the most money from a council, for example, or which department spends the most – the Openly Local website is excellent. As this link to Burnley Council’s page on Openly Local shows, you can also search for particular firms very quickly.
3. Beware the PDF!
The government’s stance on council spending is that all authorities are expected to release all details of spending over £500, but aren’t all expected to get the method of publishing right first time. As a result, some councils are producing their spending as PDFs. This is a bit of a bind, but not a complete pain as there are tools which allow you to convert PDFs to excel files, thus making them easier to search and play with. pdftoexcel is very good at doing this. If you find that the data from each pdf page goes on to a different excel worksheet, this guide on merging excel pages should be helpful.
4. About CSV
Until about 18 months ago, I had no idea CSV stood for Comma Separated Value and that files which ended in .csv could be used on spreadsheets. That probably makes me stupid, but seeing as many councils are producing their data as CSV files, I thought I’d mention it. See them as lightweight excel files in other words.
5. Using filters on spreadsheets
If there is one tool every journalist should know how to use on a spreadsheet, it’s how to create filters in every column. It enables you to quickly slice and dice data – bringing up the top 10 payments, for example. If the spreadsheet you’ve got also includes details of which department spent the money for each payment, you can also break information down by department. A guide on using Autofilters can be found here.
6. Introducing Google Fusion Tables
Google Fusion Tables makes it very easy to play around with the data which councils have to provide. A tour can found here. Once you have uploaded the spreadsheet, click the ‘show options’ link above your data and it will give you the chance to filter the information by column or to aggregate the data. This makes it easy to select ‘sum’ in the aggregate option and the ‘supplier’ column to see how much each supplier got overall.
7. Jumping to conclusions
Dan Slee, a press officer at Walsall Council, made a very good point about council data at the first Birmingham Hacks/Hackers meet up last year: It’s easy to jump to conclusions with the data, depening on the level of information available. For example, Birmingham City Council’s spending data for December includes a lot of spending on taxis. Is it all for ferrying council officers around or does it also include spending by, say, social services for ‘service users’ getting to day centres, or for children travelling to schools? Newcastle City Council’s spending data also includes the department which spent the money, so next to many of the taxi payments is the phrase ‘adult service learning disabilities’ which clears things up somewhat.
8. Search after search … after search
It may sound like a hard way to spend a day, but going through the spending data and typing the names of firms into Google to see what it brings up could be the best way to find unusual spending. Of course, this won’t always give you immediate answers. For example, Birmingham City Council spends a lot of money with A F BLAKEMORE AND SON LTD, which comes up in Google as a wholesale supplier to Spar. That doesn’t really tell us very much, but Manchester City Council’s spending included £675 to YoYogis – which quite clearly only provides Yoga aimed at children, according to its website. That was included in the spending report the Manchester Evening News produced on Saturday.
9. Ask questions
As always, the devil is in the detail, and the Manchester Evening News’s coverage of spending at councils in its area is proof of that. Many of the items of spending it references include a more detailed description of the spending than appears on the spreadsheet – for example the reason the council spent £20k with Manchester City or the £750 spent on a ‘huge chicken hotpot.’ If you search the MCC data for spending of £750, no firm which only does ‘huge chicken hotpot’ comes up but catering firms do. Asking the council further questions about spending is crucial. Interestingly, Birmingham City Council says on its website that people wanting more information can do so under the Freedom of Information Act. There’s nothing to suggest this would apply to journalists, who have access to the council’s press office, but if councils start to complain about the work caused by open data requests, perhaps they should also look at whether they could be releasing more detail in the first place.
10. Don’t give up
It may well be that councils you are interested in are being backward when it comes to releasing information. The Local Government Group has produced this template for spending spreadsheets. Hopefully, Eric Pickles will keep pushing councils to ensure they are releasing as much information as possible.