OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

Recreational Data – Could I Answer a Written Question My MP Asked?

leave a comment »

Via one of my feeds from TheyWorkForYou, I noticed this written answer to a question my MP, Andrew Turner, asked of the Secretary of State for International Development:


I’ve been playing around with development data lately, trying to sketch together some pieces for an OpenLearn course on data visualisation for development (hopefully!), so I thought this would be a good test of how quickly I could find the data and confirm the results.

Working backwards, GDP data (in various adjusted forms) is available from the World Bank API, which I’ve been accessing via the remote data interface calls in pandas (for example, Easy Access to World Bank and UN Development Data from IPython Notebooks).

So where do get the aid ranking from?

There are two ways of doing this – one to look for local UK sources (eg from DFID perhaps), the other to look for international sources of data. The advantage of the former is that these are presumably the sources that whoever answered the question went to. The advantage of the latter is that we should be able to generalise the question to query similar rankings for aid distributed by other countries.

“Official Development Assistance” seems to be a key phrase, with a quick websearch for that phrase and the term “data” turning up this Aid statistics – charts, tables and databases resource page, which in turn points to a whole raft of datatables as Excel files detailing statistics on resource flows to developing countries; the International Development Statistics (IDS) online databases page links to several more general online databases. (There’s also a beta data.oecd.org site.)

Forsaking the raw data files for a minute, the site claims that “the Query Wizard for International Development Statistics [QWIDS] is the easiest way to search our database as it automatically extracts the most appropriate dataset from OECD.Stat to match your search” – so let’s try that… QWIDS.


Nice and simple then…?!

A bit of tinkering (setting the donor, unticking recipients so only countries – rather than countries and groupings are included) gives what I think is the data for the aid disbursements from the UK to other countries, data I could export as a CSV file; but there are no tools onsite to help me look at the top 10.


Poking around, it looks like the data’s also there to allow us to look at disbursements (or perhaps just allocations) by donor country and sector into a particular country? Maybe?! This would then let us see how aid was being allocated from the UK to the top 10 recipients, broken down by sector, which might be more illuminating? I also wonder if there are any relationships between aid paid by donors into a particular sector, and imports into the recipient country from the donor country within the same sectors? For this, we need trade data breakdowns. (We can get total flows between countries (I think?!) but I’m not sure how to find the data broken down by sector?)

The stats.oecd.org site does let us sort, but I couldn’t find an easy or clean way to limit results to countries, and exclude groupings:


The order (of aid disbursements from the UK in 2012) has the same rank order as the response to my MP’s question.

For the GDP and GDP per capita data, we can go to the World Bank:


Note a couple of things – units tend to be given in US dollars rather than Sterling; there are all sorts of US dollars… (see for example Accounting for Inflation – Deflators, or “What Does ‘Prices in Real Terms’ Actually Mean?”).

Hmm… maybe it would have been easier to find the data on the DFID site instead…

PS Indeed it was – Statistics on International Development 2013 – Tables has a link to a dataset that contains the league table: “Table 4: Top Twenty Recipients UK Net Bilateral ODA 2010 – 2012″.

Written by Tony Hirst

September 15, 2014 at 1:35 pm

Posted in Anything you want

Local Data Journalism – Care Homes

leave a comment »

The front page of this week’s Isle of Wight County Press describes a tragic incident relating to a particular care home on the Island earlier this year:



(Unfortunately, the story doesn’t seem to appear on the County Press’ website? Must be part of a “divide the news into print and online, and never the twain shall meet” strategy?!)

As I recently started pottering around the CQC website and various datasets they publish, I thought I’d jot down a few notes about what I could find. The clues from the IWCP article were the name of the care home – Waxham House, High Park Road, Ryde – and the proprietor – Sanjay Ramdany.

Using the “CQC Care Directory – With Filters” from the CQC data and information page, I found a couple of homes registered to that provider.

1-120578256, 19/01/2011, Waxham House, 1 High Park Road, Ryde, Isle of Wight, PO33 1BP
1-120578313, 19/01/2011, Cornelia Heights 93 George Street, Ryde, Isle of Wight, PO33 2JE

1-101701588, Mr Sanjay Prakashsingh Ramdany & Mrs Sandhya Kumari Ramdany

Looking up “Waxham House” on the CQC website gives us a copy of the latest report outcome:


Looking at the breadcrumb navigation, it seems we can directly get a list of other homes operated by the same proprietors:

cqc provider

I wonder if we can search the site by proprietor name too?

cqc properieot search

Looks like it…

So how did their other home fare?




By the by, according to the Food Standards Agency, how’s the food?



And how much money is the local council paying these homes?

(Note – I haven’t updated the following datasets for a bit – I also note I need to add dates to the transaction tables. local spending explorer info; app.)

[Click through on the image to see the app - hit Search to remove the error message and load the data!]



Why the refunds?

A check on OpenCorporates for director names turned up nothing.

I’m not trying to offer any story here about the actual case reported by the County Press, more a partial story about how we can start to look for data around a story to see if there may be more to the story we can find from open data sources.

Written by Tony Hirst

September 14, 2014 at 9:55 am

Posted in Anything you want

Tagged with

Accounting for Inflation – Deflators, or “What Does ‘Prices in Real Terms’ Actually Mean?”

leave a comment »

“How much would £85.32 million in 2011-12 prices have been worth in 2007-08?” We see this sort of statement in the news all the time, either in reports about the effects of inflation, or as a way of making historical comparisons, so there must be a simple way of calculating the result.

The answer, it seems, comes in the form of “GDP Deflators“, tables of which are published quarterly (for example, see the UK’s GDP deflators at market prices, and money GDP: June 2014 (Quarterly National Accounts)).

The gov.uk published document How to use the GDP deflator series: Practical examples.

Deflation tables have the following form:


Several examples of common calculations are shown:




The House of Commons Library also publishes a Statistical Literacy Guide – How to adjust for inflation with a little more detail. It also qualifies the meaning of “prices in real terms” as constant prices, that is, prices where inflation has been taken into account (deflated).

The corollary of rising prices is a fall in the value of money, and expressing currency in real terms simply takes account of this fact. £100 in 1959 is nominally the same as £100 in 2009, but in real terms the [nominal] £100 in 1959 is worth more because of the inflation over this period. Of course, if inflation is zero, then nominal and real amounts are the same.

Often we want to express a series of actual (nominal) values in real terms. This involves revaluing every annual figure into a chosen year’s prices (a base year), effectively repeating the stages above on a series of nominal values and using the same base year (year x above) for all. … Once done, changes can be calculated in percentage or absolute terms. The choice of base year does not affect percentage change calculations, but it will affect the absolute change figure, so it is important to specify the base year.

The Commons Library Standard Note also clarifies that the idea that the process of inflating figures relates to the notion of purchasing power – for example, what would a pound in 2005 be worth today?

purchasing power

So what other “everyday economics” terms are there that I don’t really understand? “Seasonal adjustment” and “seasonally adjusted figures” for one.. But that will have to be the subject of a further post.

PS for deflators for other countries, the OECD aggregates a few: GeoBook: Deflators.

Written by Tony Hirst

September 14, 2014 at 8:39 am

Posted in Data

Public Accounts Committee – Armchair Auditors, Dead as a Parrot…

leave a comment »

Seems like the Commons Committee of Public Accounts have just produced their report on “Local government funding: assurance to Parliament” [report PDF] which reads to me like they have no idea what’s going on?!

On the question of how open spending data might have a role to play, the conclusions and recommendations (point 6) of the report were as follows:

The quality and accessibility of information to enable residents and councillors to scrutinise local authorities’ decisions varies. If the local accountability system is to work effectively it is fundamentally important that residents can hold local authorities to account for their decisions. It is therefore vital that residents can access relevant and understandable financial and performance information. The Department’s Local Government Transparency Code requires local authorities to publish data, including details of all expenditure over £500, information on senior salaries and details on local authorities’ land holdings and building assets. However, often this data is presented in a way which makes easy and effective scrutiny by the public very difficult. We are also concerned that the public might be less engaged with decisions on services that are significant in terms of expenditure, but do not affect them directly, such as adult care and children’s services. The Department expects that greater transparency of information will empower “armchair auditors” to hold local authorities to account, but there is no evidence that this has actually happened.

Recommendation: The Department should ensure that local authorities conform to the new mandatory Transparency Code on the publication of data, and work with local authorities to improve performance where shortcomings are identified.

Recommendation: The Department should assess whether the data published under the Transparency Code helps residents to scrutinise the performance of local authorities, and if alternative data would be of more value.

Para 18 of the report describes their consideration of this point:

We considered the new system of accountability when it was set up in 2011. At that time, the Department expected data transparency to empower ‘armchair auditors’ to hold local authorities to account for their decisions. We asked the Department whether it had any evidence that these armchair auditors had actually emerged. The Department said it thought that they had, but acknowledged that it had not actually measured whether this was the case. The Department acknowledged that residents play less of a role in challenging decisions on certain services, such as for vulnerable adults and children. For these services, the Department places greater reliance on the role of inspectors, such as the Care Quality Commission, for its assurance.

It seems that where services are delivered “in partnership”, there may also be a few “issues” (para 19):

Government departments are increasingly funding local services through partnership arrangements which are not subject to the same safeguards of local accountability and transparency as local authorities. The accountability structures for this type of arrangement are unclear. Even where the local accountability system is working effectively, it will not be able to provide departments with assurance over funding they grant to partnerships.

Some partnerships, like Local Enterprise Partnerships (LEPs), operate across local authority boundaries. The NAO’s report found that lines of accountability between local authorities and their electorate could be blurred in LEPs, where one local authority makes decisions on behalf of another. The Department told us the benefit of these bodies is that they can tackle issues that go beyond the boundaries of one local authority area and create incentives for the key economic stakeholders to work together. The Department told us it would produce a separate accountability system statement for LEPs and local growth deals.

The quality of evidence provided about “armchair auditors” from Sir Bob Kerslake, (as Permanent Secretary of DCLG?) also reads like something out of a Monty Python sketch…

Q55 Chair: Austin’s got a question, but first I want to pick up on some issues that have not been covered. When the new system of accountability was introduced, we talked about an army of armchair auditors. Have they emerged?

Sir Bob Kerslake: I think they have at a local level.

Q56 Chair: Have they? ["It's dead"]

Sir Bob Kerslake: I am sure that if you spoke to local councillors, theywould talk to you about the extent to which local residents challenge what they do. ["It's not dead, it's resting..."] We heard about one local resident, Mr Jackson

Q57 Chair: He’s the MP; you would expect him to challenge. ["I took the liberty of examining that parrot..."]]

Sir Bob Kerslake: Well, he’s one of the armchair auditors, I guess, ["of course it was nailed there..."] and I suspect that there are many more at a local level, but it will vary across the country.

Q58 Chair: Have you got any evidence of that? It is one of those hope and pray things, so it would be nice to hear whether it has happened in reality.

Sir Bob Kerslake: We haven’t measured it, but we can, for example, measure the number of people who have put forward requests to take over community assets and things like that

FWIW, I would love to see *any* member of @CommonsPAC show us what an “armchair auditor” could do with a clean and complete local spending dataset, let alone one that’s actually been released! Or perhaps Sir Bob would like to a demonstration…? ;-)

Written by Tony Hirst

September 12, 2014 at 5:20 pm

Posted in Policy

Using Open Public Data to Hold Companies to Account

leave a comment »

I’ve been in a ranty mood all day today, so to finish it off, here are some thoughts about how we can start to use #opendata to hold companies to account. The trigger was finding a dataset released by the Care Quality COmmission (CQC) listing the locations of premises registered with the CQC, and the operating companies of those locations (early observations on that data here).

The information is useful because it provides a way of generating aggregated lists of companies that are part of the same corporate group (for example, locations operated by Virgin Care companies, or companies operated by Care UK). When we have these aggregation lists, it means we can start to run the numbers across all the companies in a corporate group, and get some data back about how the companies that are part of a group are operating in general. The aggregated lists thus provide a basis for looking at the gross behaviour of a particular company. We can then start to run league tables against these companies (folk love league tables, right? At least, they do when it comes to public sector bashing). So we can start to see how the corporate groupings compare against each other, and perhaps also against public providers. Of course, there is a chance that the private groups will be shown to be performing better than public sector bodies, but that could be a useful basis for a productive conversation about why…

So what sorts of aggregate lists can we start to construct? The CQC data allows us to get lists of locations associated with various sorts of care delivery (care home, GP services, dentistry, more specialist services) and identify locations that are part of the same corporate group. For example, I notice that filtering the CQC data to care homes, the following are significant operators (the number relates to the number of locations they operate):

Voyage 1 Limited                          273
HC-One Limited                            169
Barchester Healthcare Homes Limited       168

When it comes to “brands”, we have the following multiple operators:

BRAND Four Seasons Group                   346
BRAND Voyage                               279
BRAND BUPA Group                           246
BRAND Priory Group                         183
BRAND HC-One Limited                       169
BRAND Barchester Healthcare                168
BRAND Care UK                              130
BRAND Caretech Community Services          118

For these operators, we could start to scrape their most recent CQC reports and build up a picture of how well the group as a whole is operating. In the same way that “armchair auditors” (whatever they are?!) are supposed to be able to hold local councils to account, perhaps they can do the same for companies, and give the directors a helping hand… (I would love to see open data activists buying a share and going along to a company shareholder meeting to give some opendata powered grief ;-)

Other public quality data sites provide us with hints at ways of generating additional aggregations. For example, from the Food Standards Agency, we can search on ‘McDonalds’ as a restaurant to bootstrap a search into premises operated by that company (although we’d probably also need to add in searches across takeaways, and perhaps also look for things like ‘McDonalds Ltd” to catch more of them?).

Note – the CQC data provides a possible steer here for how other data sets might be usefully extended in terms of the data they make available. For example, having a field for “operating company” or “brand” would make for more effective searches across branded or operated food establishments. Having company number (for limited companies and LLPs etc) provided would also be useful for disambiguation purposes.

Hmm, I wonder – would it make sense to start to identify the information that makes registers useful, and that we should start to keep tabs on? We could then perhaps start lobbying for companies to provide that data, and check that such data is being and continues to be collected? It may not be a register of beneficial ownership, but it would provide handy cribs for trying to establish what companies are part of a corporate grouping…

(By the by, picking up on Owen Boswarva’s post The UK National Information Infrastructure: It’s time for the private sector to release some open data too, these registers provide a proxy for the companies releasing certain sorts of data. For example, we can search for ‘Tesco’ as a supermarket on the FSA site. Of course, if companies were also obliged to publish information about their outlets as open data – something you could argue that as a public company they should be required to do, trading their limited liability for open information about where they might exert that right – we could start to run cross-checks (which is the sort of thing real auditors do, right?) and publish complete records of publicly account performance in terms of regulated quality inspections.)

The CQC and Food Standards Agency both operate quality inspection registers, so what other registers might we go to to build up a picture of how companies – particularly large corporate groupings – behave?

The Environment Agency publish several registers, including one detailing enforcement actions, which might be interesting to track, though I’m not sure how the data is licensed? The HSE (Health & Safety Executive) publish various notices by industry sector and subsector, but again, I’m not too clear on the licensing? The Chief Fire Officers Association (CFOA) publish a couple of enforcement registers which look as if they cover some of the same categories as the CQC data – though how easy it would be to reconcile the two registers, I don’t know (and again, I don’t know how the license is actually registered). One thing to bear in mind is that where registers contain personally identifiable information, any aggregations we build that incorporates such data (if we are licensed to build such things) means (I think) that we become data controllers for the purposes of the Data Protection Act (we are not the maintainers and publishers of the public register so we don’t benefit from the exemptions associated with that role).

Looking at the above, I’m starting to think it could be a really interesting exercise to pick some of the care home provider groups and have a go at aggregating any applicable quality scores and enforcement notices from the CQC, FSA, HSE and CFOA (and even the EA if any of their notices apply! Hmm… does any HSCIC data cover care homes at all too?) Coupled with this, a trawl of directors data to see how the separate companies in a group connect by virtue of directors (and what other companies may be indicated by common directors in a group?).

Other areas perhaps worth exploring – farms incorporated into agricultural groups? (Where would be find that data? One register that could be used to partially hold those locations to account may be the public register of pesticide enforcement notices as well as other EA notices?)

As well as registers and are there any other sources of information about companies we can add in to the mix? There’s lots: for limited companies we can pull down company registration details and lists of directors (and perhaps struck off directors) and some accounting information. Data about charities should be available from the Charities Commission. The HSCIC produces care quality indicators for a range of health providers, as well as prescribing data for individual GP practices. Data is also available about some of the medical trials that particular practices are involved in.

At a local council level, local councils maintain and publish a wide variety of registers, including registers of gaming machine licenses, licensed premises and so on. Where the premises are an outlet of a parent corporate group, we may be able to pick up the name of the parent group as the licensee. (Via @OwenBoswarva, it seems the Gambling Commission has a central list of operating license holders and licensed premises.)

Having identified influential corporate players, we might then look to see whether those same bodies are represented on lobbiest groups, such as the EU register of commission expert groups, or as benefactors of UK Parliamentary All Party groups, or as parties to meetings with Ministers etc.

We can also look across all those companies to see how much money the corporate groups are sinking from the public sector, by inspecting who payments are made to in the masses of transparency spending data that councils, government departments, and services such as the NHS publish. (For an example of this, see Spend Small Local Authority Spending Index; unfortunately, the bulk data you need to run this sort of analysis yourself is not openly available – you need to aggregate and clean it yourself.)

Once we start to get data that lists companies that are part of a group, we can start to aggregate open public data about all the companies in the group and look for patterns of behaviour within the groups, as well as across them. Lapses in one part of the group might suggest a weakness in high level management (useful for the financial analysts?), or act as a red flag for inspection and quality regimes.

Hmmm… methinks it’s time to start putting some of this open data to work; but put it to work by focussing on companies, rather than public bodies…

I think I also need to do a little bit of digging around how public registers are licensed? Should they all be licensed OGL by default? And what guidance, if any, is there around how we can make use of such data and not breach the Data Protection Act?

PS via @RDBinns, What do they know about me? Open data on how organisations use personal data, describing some of the things we can find from the data protection notifications published by the ICO [ICO data controller register].

Written by Tony Hirst

September 9, 2014 at 9:39 pm

Confused Fragments About Open Data Economics…

with 4 comments

Some fragments…

- the public paid for it so public has a right to it: the public presumably paid for it through their taxes. Companies that use open public data that don’t fully and fairly participate in the tax regime of the country that produced the data then they didn’t pay their fair share for access to it.

- data quality will improve: with open license conditions that allow users to take open (public) data and do what they want with it without the requirement to make derived data available in a bulk form under an open data license, how does the closed bit of the feedback loop work? I’ve looked at a lot of open public data releases on council and government websites and seen some companies making use of that data in presumably a cleaned form (if it hasn’t been cleaned, then they’re working with a lot of noise…) But if they have cleaned and normalised the data, have they provided this back ion an open form to the public body that gifted them access to it? Is there an open data quality improvement cycle working there? Erm… no… I suspect if anything, the open data users would try to sell the improved quality data back to the publisher. This may be their sole business model, or it may be a spin-off as a result of using the (cleaned and normalised) data fro some other commercial purpose.

Written by Tony Hirst

September 9, 2014 at 1:32 pm

Posted in oh_ffs, Open Data, Policy

Tagged with

Corporate Groupings in Care Provision – Finding the Data for GP Practices, Prequel…

leave a comment »

For some time I’ve been pondering the best way of trying to map the growth in the corporate GP care provision – the number of GP practices owned by Virgin Care, Care UK and so on. Listings about GP practices from the various HSCIC datasets don’t appear to identify corporate owners, so the stop gap solution I’d identified was to scrape lists of practices from the various corporate websites and then try to reconcile them against GP practice codes from the HSCIC as some sort of check.

However, today I stumbled across a dataset released by the Care Quality Commission (CQC) that provides a “complete directory of places where CQC regulated care is provided in England” [CQC information and data]. Two data files are provided – a simple register of locations, and “a second file … which contains details of registered managers and care home bed numbers. It also allows you to easily filter by the regulated activities, service types or service user bands.”

Both files contain fields that allow you to identify GP practices, but the second one also provides information about the actual provider (parent company owner) and any brand name associated with the service. Useful…:-)

What this means is it should be easy enough to pull the data into a report that identifies the practices associated with a particular brand or corporate group… (I’ll have a go at that as soon as I get a chance…)

Another thing that could be useful to do would be to match (that is, link) the location identifiers used by the CQC with the practice codes used by the HSCIC. [First attempt here.... Looks like work needs to be done...:-(] Then we could easily start to aggregate and analyse quality stats, referring and prescribing behaviour data, and so on, for the different corporate groupings and look to see if we can spot any meaningful differences between them (for example, signals that there might be corporate group level policies or behaviours being applied). We could probably also start to link in drug trial data, at least for trials that are registered, and that we can associate with a particular practice (eg Sketching Sponsor Partners Running UK Clinical Trials).

Finally, it’d possibly also be useful to reconcile companies against company registrations on Companies House, and perhaps charity registrations with the Charities Commission (cf. this quick data conversation with the 360 Giving Grant Navigator data).

PS more possible linkage:
– company names to company IDs on OpenCorporates (and from that we can look for additional linkage around registered company addresses, common directors etc)
– payments from local gov and NHS to the companies (from open spending data/transactions data)
– food hygiene inspection ratings (eg for care homes)

Written by Tony Hirst

September 9, 2014 at 12:12 pm

Posted in Open Data, Policy


Get every new post delivered to your Inbox.

Join 794 other followers