So What’s Wrong With Council Spending Data? Part II

There are two key aspects of payment data, the first is obviously the actual format of the payment amount. Here’s four basic forms of ten thousand pounds.

From a data ingestion viewpoint none of these provide any major issues, as I described in the first part of this series I validate dates by importing into a stage table with a date type defined, in the second stage of the process I clean up the amount column, removing ‘£’ symbols and ‘commas’ then cast them into a numeric(20,0) datatype, this facilitates all normal number operations on the column.

This is a slightly less obvious aspect of spending open data but it’s a subject I will come back to more broadly in a later post, but looking at it here provides a good introduction to an issue that can affect comparability of data sets across councils.

Most of the spending data will come from Commercial Payments Systems. They will be used primarily by the Finance Departments and used by Invoice processors. Processing invoices will include paying the amount due but may also include reimbursements(un-payments if you like) to the Council. These will either be full or partial negative amount payments, here’s a selection of the top reimbursements noted.

These can be quite significant amounts that if removed can distort the councils payments profile. There are some distinct variations in reporting across different councils.

Clearly this will affect comparisons between councils when the data sets are not equivalent, we can’t just use the best data sets we have to delete everything below £500 for all datasets to make them more comparable. A poor but forced solution.

The table below shows what percentage of beneficiary names exactly match the Companies House dataset, most fall within the 20-30% range, typically they have three or more words in the company name. The total suppliers is also provided to provide some idea of total number of commercial suppliers(circa 75% of TS)

Abbreviation often appear in beneficiary name and cause problems when trying to identify the underlying organisation, there are over 400 Associations in the 25 council 2019 dataset, different councils use different variations

All of the above may appear at the start, middle or end of the string, they also use other abbreviations such as SERVS and DIR. Then they may just appear as their initials

Over 1200 beneficiary names have the ‘T/A’ string in them meaning ‘Trading as’, such as

But frequently the real name of the company may appear on the other side of the ‘T/A’ string

This issue arises from the fact that most of the sources of spending data resides in flexible payments software. Presumably when these are installed they will be configured to the particular council departments needs. I first came across this issue with the Cumbria dataset initially with utility payments

This became more of a problem as I looked at the Manchester and Liverpool Combined Authorities data sets and payments to Care Homes started to appear

Initially I was focused on extracting or cleaning the data to get the company names but it has occurred to me that in the second example the actual home has significant public interest(similar issue arises with education establishments). With the introduction of the Care Quality Commission dataset this increased possibilities of a more detailed analysis of the Care Home payments.

Consider a fictional company ‘X Y JONES’ the following variations could appear in the council datasets

When I started to look at the SIC codes of matched companies another issue appeared, some quite large companies appeared as ‘99999 – Dormant Company’. Typical examples of this would be ‘BRITISH GAS LIMITED’ and ‘BRITISH TELECOM’. Technically the supply side of British Gas plc split away as Centrica in 1997. British Telecom changed it’s trading name in the early nineties and has now become BT Group. This can be explained again by considering where the data comes from, the company name was accurate at the time it was entered but hasn’t been updated as corporate structures change, indeed I could imagine old payment details being exported and imported through newer and newer council IT systems. This issue does not just apply to large companies, the question it does raise though if the company is dormant with respect to Corporation Tax, where is the money being paid to. See more information on Dormant Companies here.

On a similar issue to above, in 2019 Oldham Council paid £499,617.17 to Bardon Aggregates, however according to Companies House Bardon Aggregates was dissolved in March 2017. There are over 400 companies in the combined dataset that are currently flagged as being in a status of not ‘Active’ that could be in ‘Liquidation’, ‘Administration’ etc. true many of them have changed status since 2019 and it’s an area that needs further investigation.

Many beneficiary names are truncated, Trafford consistently truncate to 35 characters

Others truncate to varying lengths presumably as items are exported from different IT systems before final release.

A special mention to Bolton who release beneficiary names with the ‘#’ symbol randomly scattered through them

This was one I wasn’t expecting but there seems to a random tendency to occasionally swap an apostrophe for the grave[ ` ] symbol. Indeed Blackburn with Darwin seem to have remapped all their keyboards to do this.