3

What is the best format that we cam reasonably get government bodies to publish their spend transaction data in?

In an ideal world we would ask for Linked Data, where every supplier was correctly identified by well-known URIs. Alternatively, a well specified spending transaction format using CSV this suggested one from Openspending or XML would also be great. But government bodies seem to struggle to even output a simply specified CSV/XLS with any consistency.

Of course there is little money for them to achieve these goals. I imagine the requirement to publish their spend data arrives at the accounts department, who ask IT to write a custom report for their (probably arcane and proprietary!) finance systems.

In the light of these constraints, what is the best format to ask for this data in? Is there a neat format that is hard to get wrong that we can aggregate with less pain? Or are my expectations about what is generally possible by accounts systems too low? Perhaps there is a common format, that accounts systems might commonly export to?

Any examples of other countries doing this sort of thing, would be most useful.

Background of our experience in the UK

In the UK the Prime Minister required hundreds of public bodies to publish their monthly lists of spending transactions over £25,000, with a specified spreadsheet format with headings such as:

Entity, Date, Expense type, Supplier, Amount

The publishers list the data files on data.gov.uk and tag them 'spend-transactions'. We have over 300 bodies publishing these at the moment and plans for many more (browse the main departments).

We have quite an involved ETL pipeline that downloads these files, maps the columns names to the standard ones, parses the values and dates, reconciles supplier names using OpenCorporates. The result ends up as a database, browsable with OpenSpending's tool.

The ETL is a pain to maintain, because of the sheer quantity of files and variability in the CSV:

  • There seem limitless variations to column names (e.g. variations on Amount: Billed Amount, Transaction Amount, Gross Amt, Total inv, Net Value, Sum of Amount, BPPC Payment Amount, Charged, Amount in sterling, AP Amount, Line Value Exc Rec VAT, Total Value, ...) so we have to do lots of manual reconciliation (for which we use Nomenklatura). There are often plenty of extra columns that weren't required, and some of the required ones are missing.

  • Even parsing dates is not so simple. We detect from a range of formats for a file. But I've seen the format changing half way through a CSV, I think because they make the file up from several different systems!

BTW We've done some work on providing feedback, about what files fail parsing, and which departments are not up to date, but we can probably work harder on this.

4

I think it was best said in John King's wrap-up for the public hearing on public access to federal data last month at the National Academies. To paraphrase:

If you require people to do stuff for which they get no benefit, they're going to spend the minimum effort in doing so, and you'll get a crappy result.

So ... rather than focus on the format ... focus on what benefit they could get out of it. I've been a big proponent of tool building to support data formats -- create some great tool that people want to use, but to use it, they have to put their data into a proscribed format. Maybe you could give them a tool to do the reformatting, so that they're the ones maintaining it should their fields change.

Right now, their different formats support their existing systems. They actually need those systems to get their job done. The extra reporting is just an extra burden that's been placed on them.

Talk to the various IG (Inspector General) and finance departments ... maybe there are some common analysis that they all do that's challenging with their existing tools. If it's more cumbersome than the data reformatting, you have a potential way in. If you can't find some way that you can actually improve someone's life (with that someone being in the power to make the change), you're just going to be treated like another unfunded mandate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.