Archive for the 'Uncategorized' Category

Finally some answers

January 30, 2011

It’s an innocent enough question:

what does the government spend money on?

Now, I’m not an accountant and I’m not a statistician and personally I don’t have a political axe to grind, I just want some answers to this question that make sense.

This attitude gives me a lot of freedom: I don’t have to get bogged down in any system of understanding spending unless it really helps. But this attitude also gives me a lot of opportunity to look a right idiot, because I’m effectively feeling my way around this unfamiliar financial and political world, pushing for information that may or may not be useful in the end, asking questions that must seem really stupid to experts.

I think it’s only fair that I document my fools progress so that other people who might be on a similar mission can be helped along their way.

So, from the beginning to right now, this is what I’ve found out about government money.

I should add that public spending is making more and more sense to me these days and while I don’t have all the answers right now, I do have a vision for the future and some useful stuff to share.

In the beginning

When I first thought about government spending I had some vague idea that there would be lists of spending, one for each department or maybe one big list for all of government, showing money in and money out for each department. It would just be a matter of finding these sacred and quite possibly hidden lists. I mean someone in the department must be keeping track of the money in and out, right?

The people at Where Does My Money Go? had something that matched my idea of a list of spending. The list they found on the Treasury website showed different government departments and how much they had spent on certain types of public services. I helped to interpret this and the team went on to make this colourful interactive display.

The beauty of this display is allows drilling down into the details of the spending. This is a relatively new technology and lends itself to spending data nicely, it makes those lists accessible and it doesn’t feel like work to look through them they way it does to look into a spreadsheet.

But we quickly found that the list we had used raised more questions than it answered. We only had spending for certain departments, nothing in detail on local councils, and the only details we had were in these odd sounding categories of spending that were designed to allow comparisons between UN countries.

I had a feeling that there must be more detail that we weren’t getting, but where was it?

Therein’ I pestered the heck out of the poor folks at the Treasury about where these spending lists are stored and what else is stored there.

The short answer is that it was all stored in something aptly called COINS, which is a database for frequently revised budgets and something else that is really, really interesting and that I’ll come back to shortly.

A moment of clarity

While I was looking at this spending it slowly dawned on me that just looking at money in and out isn’t the full story of the way public money is managed. For one thing the lists of money in and out, on their own, soon become unwieldy and make it hard for people to answer questions about public spending.

For another thing, Governments don’t just receive and spend money, their wealth is also made up of the things they own like buildings, roads and also determined by the money they owe — the debts as we all know too well.

This is when I found out that accountants have elegant ways to deal with all of this financial jumble.

Let me explain accountancy in the terms I’ve come to understand it.

There are three main figures to consider.

Let’s split the finances of an organisation into either 1) something it is owed or owns and 2) something it owes to someone else. That is two of the 3 main figures explained already.

Just these two figures can give us a simple but important measure of how healthy the finances are. Hypothetically if the organisation received everything it was owed and then sold off everything it has and used that to pay off all its debts, how much would be left? What is left is our third figure. This is what all the people who invested in the organisation would be left with for their return on their investment.

For the mathematically minded this can be expressed at:

1 – 2 = 3
owed and owned – debts = return to investors

We should find that if we sum the amount of money that people invest and the profit the organisation makes, the result is equal to the third figure.

Just to put this into context for the case where the organisation is a council or government body, the investors are the taxpayers and the bond buyers.

Another way of looking at these three figures is that organisation is ultimately balancing the money it has for its activities and the money it receives and borrows to make its activities happen. So it balances what it owns and is owed (figure 1) with its debts and investments and profits (figures 2 and 3).

For the mathematically minded that is:

2 + 3 = 1
investment and profits + debts = owned and owed

just a rearrangement of first expression.

If you ever heard of double entry book-keeping then keeping this equation equal is basically it: accounting for what is produced with what is needed to produce it.

There’s lots more to accounting than this, obviously, but I hope I’ve conveyed that this way of categorising financial information is neat and yields useful information.

Here’s the killer

You might ask: wouldn’t it be ideal if each publicly funded body published their accounts under these categories so we can understand their financial health?

It turns out that the government have been producing resource accounts for every department and every local authority for years, and this is the killer: in 2001, yes, 10 years ago, they even produced a guide to interpreting these accounts so that we, the public, can extract all kinds of useful information.

You might ask: wouldn’t it be ideal if someone collected together all the public accounts? Even better if they summed the accounts into one set by eliminating all double counting where public bodies exchange money between themselves.

It seems that at the same time as the resource accounts started to emerge in the early 2000s, the Treasury started to run something called ‘Whole of Government Accounts’. It brings together, or consolidates, all the resource accounts of pretty much every public body into one set of resource accounts. What a perfect way to categorise all public spending at a high level.

Guess where Whole of Government Accounts is stored? In COINS. Disappointingly when COINS was published all of the Whole of Government Accounts was removed. The Treasury plan to publish the first ever version of Whole of Government Accounts this spring.

The vision for the future

The government is currently publishing more spending information than any of their predecessors. We have all central government spending over £25,000 and soon we’ll have all local government spending over £500.

How do we make some sense out of all this spending data?

Lots of information can be made manageable if it can be categorised well.

I’m interested in using the categorisation system in the resource accounts. According to the introduction to resource accounts, it is possible to calculate departmental costs ranging from staff wages to money spent on programmes with their own objectives, as well as lots of useful information about assets and liabilities (what I referred to as figures 1 and 2 above).

I believe using this accounting system and allowing a user to ‘drill down’, as they currently do in Where Does My Money Go?, from this high level organised system into the details of the spending will give ‘accountability’ to the accounts.

There are many difficulties in data compatibility between the resource accounts and the lists of transactions that are currently being published, but I think with some iterations of the data being published, these difficulties could be overcome.

What do you think?


The whole of government accounts: an exercise in elimination

June 25, 2010

When the large sample of COINS data was published on the 4th June it was accompanied by a guide to the data. The guide is very useful, but one thing it doesn’t explain in very much detail is where the COINS data comes from. The guide lists the inputs:

COINS – the Combined On-line Information System – is used by the Treasury to collect financial data from across the public sector to support fiscal management, the production of Parliamentary Supply Estimates and public expenditure statistics, the preparation of Whole of Government Accounts (WGA) and to meet data requirements of the Office for National Statistics (ONS).

But in what form are each of these different types of financial data  entered into COINS? To answer this question for the Whole Of Government Accounts (WGA) data, I’ve been looking more closely at the data gathered for this exercise.

After reading the WGA materials on the Treasury and the Communities and Local Government web pages, and chatting to some very helpful members of the WGA team, my understanding of the WGA exercise is that it identifies exchanges of funds between public bodies. These exchanges include the flow of funds from Central Government bodies down to Local Authorities and all the exchanges of funds between departments. When the transactions between public bodies are identified, the WGA exercise makes some adjustments to avoid double counting the money. So, if body A gives money to body B, then WGA would be responsible for subtracting the amount body B received from body A’s total.

As we know the COINS data is made up of spending or income records for each department. In these department records there is a Counter Party ID (CPID),  if that identifies another government department that means that some funds have been exchanged between the two departments.

There are scripts used on the COINS data to look for  eliminations using the CPID code.  which has the code in every department’s spending, if money of the public body money was exchanged with and the WGA team perform lots of checks on this. You can see this process happening in the adjustments table in COINS.

There are two guides to the WGA, one for  local authorities and the other for central government departments.

Central Government Accounts

The process of WGA for Central Government departments is simply that each central government department is required to fill in a C-Pack once a year, which is a spreadsheet constructed by the WGA team.

Point2.4. of the Guide for the C-Pack (PDF) says:

The key deliverable is the C-Pack, and the upload of Resource Accounts data and CPID data into COINS.

Local Authority Accounts

The WGA process for Local Authorities is a slightly different exercise. The Local Authority is asked by WGA to fill in an L-Pack once a year.  If you follow that link to the L-Pack excel spreadsheet that the Communities and Local Government branch of WGA prepare, they you will see that it is quite a complex looking creature. I’m going on a training course to understand it better, but I do know that the results of every local authority filling out this form amounts to quite a significant documentation of public spending and income.

In fact the WAG guide for local authorities states:

Local government controls over 50% of public sector fixed assets, accounts for about 25% of net public expenditure and represents 10% of UK GDP.

Now, here is the interesting part. The Local Authority spending and income that is recorded in the L-Pack is not in the COINS data that was published recently.

Now, I thought this missing detail in COINS might be because the WGA would be published separately.

There is a WGA report expected in spring 2011, but on further investigation it transpires that the level of detail will be the same as company accounts. We will get some extra details in this report, for example spending on PFIs will be included for the first time. But essentially we will miss out on all the lovely detail from the L-packs and C-packs.


The WAG is an exercise in eliminating excess data that clouds the picture of public spending and income.  The WAG team’s work seem to keep process of reporting spending and income more manageable. This is completely understandable. But on the other hand it would be great to have this detail of exchanges of funds so we can understand public spending as it really is.

UPDATE: I now understand the auditing going on it Whole Of Government Accounts better.

Auditing, I believe, means matching up buyers and providers:

A perfect match is:
Barnet Council purchases £5.5 m from Enfield Council.
Enfield Concil sales £5.5 m to Barnet Council.

The COINS scripts would eliminate this to zero as perfect match.

Another example:
Barnet Council purchases £5.0 m from Enfield Council.
Enfield Council sales £5.5 m to Barnet Council.
COINS would eliminate 5.0m and and put 0.5M into suspense.
The the suspense needs to be investigated more to see where the mistake is.
This investigation is the job of the Whole of Government’s Account team.
You can set a tolerance in COINS, which is the extent of the difference between two accounts it will put into suspense. The tolerance was set to 5.0m for 2008/09 accounts. I will be set to 1.0m for
2009/10 accounts.

COINS: the ins and outs

June 15, 2010

It’s been over a week, now, since the government spending recorded in the COINS database was free for everyone to see.

Before COINS was published I’d been looking into the different data gathering processes that ended with the data being stored in COINS. So you can imagine that having the actual COINS data is the final piece in the puzzle to understanding those processes. In short: it is all starting to make more sense.

In the next two blog posts here, I will describe two important processes that involve gathering data to be stored in COINS.

I’ve drawn a rough sketch of the processes in this diagram (click on the image to see more detail):

The two processes are:

  1. The Whole Of Government Accounts data gathering exercise which is performed by a branch of the Treasury. I’ve discovered this is an exercise in aggregating local spending, and avoiding double counting of spending in central government departments.
  2. The estimates and budgeting process which determines the resources departments are allocated.

I’m also writing a much more detailed guide to COINS, after more detailed talks with the very helpful people at HM Treasury, that is published on the Open Knowledge Foundation blog and will be published shortly on

COINS: a perfect example of a mystery

June 7, 2010

TV shows like Lost use mystery in a pretty smart way: giving tantalising clues as they go along and always making you want to watch more, to get some kind of satisfaction of knowing enough.

I’ve been looking into public spending and the mystery of how public spending records are stored is very similar to the TV shows.

I’ll tell you the story of the COINS database, which is a store of public spending and planning data, and you can judge for yourself how similar the story is to a gripping TV show.

The way COINS is described in documentation is that it’s a store for government spending records. In the background to reports on public expenditure, like the PESA report, and many of the Office of National statistics reports on government spending, the guidance says the reports rely heavily on COINS data.

I asked for the COINS schema, but what I got was the dimensions of the OLAP database and no description of the fields meant, so that was the first mystery. Then I requests and got the COINS training notes, but with all the screen shots and lots of the descriptions redacted, so this was the second mystery.

When I met some people from the Treasury to talk about COINS some more and they told me about the thousands of spending codes in COINS, which I then requested, but with a number of them redacted, this was the third mystery.

Then, on Friday 4th June, we got the COINS data itself, well a sample from this year, and some COINS guidance(PDF) to go with it. The guidance says:

It is possible that you won’t be able to recreate the numbers that Treasury or ONS have published. This is because:

• COINS has a single structure that is updated to reflect the latest classification of spend and organisation of government. The snapshots taken by the Treasury at defined points (e.g. to enable reports to be published) contain certain key fields, which then reference the latest structure. Any changes to the structure since the report was published may mean that it is not possible to recreate published figures;
• of the time difference between the publication of aggregated information and this release of data, with the potential that data have been updated between the differing points in time.
• Not all the data used to calculate these numbers are sourced from COINS.

So it’s not possible to check the figures against the published reports, like PESA, to check we understand the data published. Another mystery.

There is a description of useful combinations of the fields in COINS that would give accurate public spending figures from COINS — which means we are getting there.

So, what do you think? It will be satisfying when we understand all the spending!

Update on local spending, HMRC Aspire contract and more COINS.

May 17, 2010

Here are three updates on requests about public spending, made recently:

  1. I’ve been asking for more detail on the way local council accounts are held at Cambridge City Council, Warwickshire County Council, East Cambridgeshire District Council and Huntingdonshire District Council. I picked Cambridgeshire based councils because I live there, and Warwickshire County Council because they have a new open data website. You may also remember that I asked for Cambridgeshire County Council’s accounts database schema, well I’ve now asked for the data with a few examples of reasonable redactions.
  2. Francis Irving had his request for a copy of the Aspire contract rejected. The Aspire contract is the agreement that HM Revenue and Customs made to outsource most, maybe all, of its IT services to Capgemini. Francis was told to be more specific in his request, as the cost of dealing with his existing request would exceed the £600 limit. Included with the rejection letter was some further reading on the Aspire contract. I’ve been working my way through this and trying to make some specific, and hopefully useful, requests for information. I’ve asked for more details about how Capgemini’s performance is being measured. Let’s see how that goes.
  3. There have been some more requests for bits of the COINS database from Julian Todd who is now appealing his request for 23 complete COINS records and from Francis Irving who has read the COINS training materials (PDF) and consequently asks for fact table.

To end I’ll just add this Wordle that I made by pasting into all the text in the COINS training materials I received:

It says it all really: “redacted data” and “Screen-shot exempt”.

UPDATE 15th June 2010: I’ve since discovered that HM Treasury rarely use the API to COINS — preferring to load the required data sets into Excel — so I wasn’t missing much when the screen shots where redacted.

The New Leaders and COINS

May 17, 2010

It’s exciting times right now for people interested in where our tax money is used. The new leaders of the country have proposed to make the allocation of our tax money clearer than ever before. For example, I’ve been reading this speech George Osborne gave to the Institute of Chartered Accountants at the start of 2009. In it he states:

And so as a step towards spending transparency in our central government departments, we will publish shortly after coming to office the Treasury’s COINS database that reports several thousand programme spending items in a consistent format across departments.

For the first time, we will throw open the government’s books and shed light on wasteful spending.

For the first time, anyone will be able to find out how and where their taxes are being spent – and use this information to hold the next government – and every successive government – to account.

So come on then, publish it!

Public Spending Presentation

April 30, 2010

These are the slides I made for a brief presentation on the openness of public spending data:

Roundup of what I know about public spending.

April 30, 2010

This is a roundup of where I’ve got, so far, with my quest to find out more about UK public spending. I’d appreciate help and advice on every line of enquiry I mention here.

I’m interested in tracing records of spending as they pass from one department to another, so I’ll list my finding for each department I’ve looked into.

HM Revenue and Customs (HMRC)

I’d like to know how our tax records are stored, but so far I’ve only got this very very sketchy description. That reply does give some good clues about how National Insurance tax is stored in the statement:

For example the collection of National Insurance is supported by the National Insurance and PAYE system (NPS). This is a substantial build on what was previously known as the National Insurance Recording system (NIRS) to add on the PAYE business processes given the close interaction between the collection of PAYE tax and national Insurance. It has its own underlying database with customer information, but links to other databases within HMRC and in DWP to ensure that customer data is treated consistently throughout both departments.

Another thing we know about how our tax records are stored is that most of HMRC is outsourced and the contract for this outsourcing is not public yet. We know the contract is called ‘the Aspire contract‘, and we have some background reading to do on it.

HM Treasury (HMT)

There is a project within the Treasury called ‘The Whole of Government Accounts‘, which aims to “consolidate the accounts of about 1300 bodies from within the central government, health service, local government and public corporation sectors.” A database called the Combined Online INformation System (COINS) was developed to make this consolidation of accounts possible or easier or both. The Whole of Government Accounts has yet to report its work.

When I first started looking at the COINS system there was very little public about how detailed the spending records are. As you can see from this blog post, we’ve got a pretty good understanding of the data in COINS. Here’s a snapshot from a presentation I made, it shows the information about COINS that is now public:

We are now working to get:

The Office of National Statistics (ONS)

I know of two databases that store spending data in the ONS:

  • The CSDB (central shared database) does not use an ORACLE database.
  • The CORD system (Centralised ONS Repository of Data) used for some parts of the annual production process in ELS does use an ORACLE database. I have the schema for this one.

As well as these two databases, I’ve been told the ONS gets a copy of the COINS data for it’s reports and I’ve had a request for the COINS data sent to the ONS rejected.

Other Departmental Accounts

I was told by my contacts at HMT that each government department has it’s own record of accounts. I’ve been investigating how the Department for Work and Pensions store their accounts, the next step is to request the data.

Local Spending

I’ve got the schema for the Oracle database Cambridge County Council use to store their accounts. The next step is to ask for the data or a sample of it.

In The Begining There Were Mystery Boxes

April 22, 2010

The guy who created LOST, the TV show, gave a great TED talk. It was all about how he likes ‘mystery boxes’.

He started off  by describing these magic mystery boxes he liked as a kid and then he realised his TV shows and films are filled with mystery boxes. Not literally, but that the plot and characters are like mystery boxes: you don’t know what they are about until the end. The rest of the time is a big tease about what might be.

The guy, no, lets say his real name, JJ Abrams, says:

So there’s this thing with mystery boxes that I started feeling, like, compelled. Then there’s the thing of, like, mystery in terms of imagination — the withholding of information. You know, doing that intentionally is much more engaging.

If you like the idea of magical mystery boxes then you’re going to like this.

I’ve been looking at where our tax money is spent. I know that sounds completely unrelated to cool TV shows and popular films, but this research has been all about the tease of what might be in the mystery boxes of the British government’s spending records.

The plan for this blog is to show the government spending boxes, share the questions I asked about them, the replies I get and in the end, all being well, all will be revealed.

This is the rough picture of public spending databases I’ve gathered so far, as part of my work as a researcher at ‘Where Does My Money Go?’ (click on the image to see the full picture).

UPDATE: I’ve written a version of this blog post for the Open Knowledge Foundation Blog here.