Opening up municipal spending data

Written by
  • Niels Erik Kaaber Rasmussen
Outdated Content Warning: This content refers to an older version of OpenSpending. See here for information about the next version of OpenSpending and ways to contribute.

The open data web-agency Buhl & Rasmussen has developed a site visualizing the budgets of all 98 Danish municipalities for one of the biggest Danish news sites, Politiken.

Municipalities are central for the functioning of the welfare state Denmark. They take care of a range of important tasks like social- and health care, primary education, social benefits, traffic and much more. However even in a year with local elections they do not attract much public attention.

One reason to this might be that the barriers for ordinary citizens to engage in local politics are too high. One way to lower the barriers might be to make it easier to understand the most central decision made by municipalities each year: their budgets.

The data

There are comprehensive data openly available on Danish municipal budgets and accounts. Budgets and accounts are structured in a hierarchy with 4 levels and roughly 250 possible expenditure and income posts. Data for all 98 municipalities can be obtained from Statistics Denmark and dates back to 1978. Obviously a lot of changes has taken place since 1978 but historical data for the last 5 years or so are reasonable comparable with today’s figures.

Reducing complexity - preparing the data

Multiple problems arise when comparing historical accounts to the latest budget, as I decided to do for this project. First inflation must be taken into account. Secondly the responsibilities of the municipalities are not fixed over time and thirdly accounting practices changes over time.

I choose to adjust time series using a combined consumer price and wage index. While this does not fix the problem with changing responsibilities and accounting practices it improves the overall comparability.

Dealing with national reimbursements

Some expenses paid for by local government are reimbursed in part by national government. Shall the reimbursed part be included as an expense or not? I decided to do both(!). When presenting the budget using the bubble diagram all figures represents expenditure minus related income if any. In addition to the bubble diagram a table view shows the budgets with income and expenditures separated from each other.

In the budgets expenditure posts are split in operational and construction expenses. The separation makes good sense for a lot of analyses however for simplicity I choose not to differ (the separate parts of an expenditure post are shown with mouseover).

I made an effort to include not just spending data but also figures describing the different sources of income. Openspending makes such a good case in highlighting public spending but seems to ignore income. To get a reasonable understanding of municipal budgets you’d have to consider both spending and income.

The solution

The main visualization is the well-known bubble chart from OpenSpending slightly altered to include an information box, when users click at the lowest level. The popup includes information on historical spending, related income and presents the expenditure as part of the total budget, divided by the number of inhabitants and as compared to national average.

Information popup showing historical data, expenditure as part of the total budget, expenditure per inhabitants and expenditure compared to national average.

The bubble chart itself is an inspiring tool - thanks to the Open Knowledge Foundation and the OpenSpending-project for providing it.

In addition to the bubble chart I created some features useful when dealing with multiple budgets with same structured format. For users interested in a specific expenditure post, that post can be selected and a list of the municipalities spending the most/least per inhabitant (of in percent of the total budget) can be shown.

The standardized structure of the budgets also made it possible to build a function to compare two different municipalities in details.

Comparing two municipalities. The small charts shows historical data.

The compare-feature makes it easy for one to see how the neighbour municipal prioritize differently.

Municipal elections are going to be held in Denmark later this year. Hopefully the opening of the budgets will help lowering the barrier for citizens to engage in discussions.

Local spending in your city

While data on budgets and accounts for municipalities in Denmark are relatively easy to obtain this is far from the case in every municipal or city around the world. Some places the data is not published at all, many places the data is not published in a machine-readable format and seldomly is data made available in a manner that makes it possible to compare one city to another in a meaningful way.

The OpenSpending project is organizing a City Spending Data Party on July 19-21 - if you’re interesting in local spending data this is a great chance to get involved.