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.
