Council spending data: 10 tips for journalists looking for stories

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.

Useful links

Guardian Data Editor Simon Rogers’ guide to data on journalism.co.uk

O.Useful blog on getting started with council data

About these ads

9 comments

  1. or you can also try out the BIOLAP Council Expenses Dashboard….

    BIOLAP has details of more than 150 Councils’ payments to suppliers over £500 on its free Council Expenses Dashboard at – http://www.biolap.co.uk/index.php/councilexpenses.html

    Users can slice and dice the Council’s data, compare it to spend by other Councils and sort the data by value of spend, as well as drill through to the underlying transactions.

    BIOLAP is a BI Consultancy practice specialising in Planning, Analysis and Reporting solutions.

  2. Thanks for the mention. Worth adding that OpenlyLocal provides a one-click Freedom of Information services for transactions over £10,000 and provides the whole dataset as an open data download

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s