Stuff that occurs to me

All of my 'how to' posts are tagged here. The most popular posts are about blocking and private accounts on Twitter, also the science communication jobs list. None of the science or medical information I might post to this blog should be taken as medical advice (I'm not medically trained).

Think of this blog as a sort of nursery for my half-baked ideas hence 'stuff that occurs to me'.

Contact: @JoBrodie Email: jo DOT brodie AT gmail DOT com

Science in London: The 2018/19 scientific society talks in London blog post

Monday, 4 March 2013

Trying to find this year's QOF stats on diabetes - worked example

This is an unfinished post that I last saved on 10 February 2013. I was in the middle of updating my own stats website at DiabetesStatistics and (a) discovered that all the previous links to stats files were now out of date because people will keep moving things around and (b) I found conflicting numbers and hadn't managed to resolve it. I'm publishing this now as I was so surprised to read of 'new' figures today... which seem to be the October 2012 QOF figures. Odd.


When I worked at Diabetes UK one of my tasks was to contribute to our "Diabetes in the UK" document which began to be updated annually to take account of new QOF (Quality and Outcomes Framework) figures for people with diabetes. You can see the history of the document from 1988 to the present at this page on diabetes prevalence here.

Here are the 2011/2012 figures I've found for each nation.

England: 2,566,436 people over age of 17 with diabetes (any type)
Scotland: 234,871 or possibly 247,278 (not sure about this one, see explanatory text below)
Northern Ireland: 75,837
Wales: 167,537

Total: 3,018,610 / 3044681
Wales 167537 3185538 5.3%
England 2544197 55068625 4.6%
UK 3018610 65358176 4.6%
Scotland 231248 5210522 4.4%
Northern Ireland 75628 1893491 4.0%

Briefly, and probably somewhat simplistically, QOF is a form of accounting so that each GP practice in the various nations gets the appropriate payment for the number of people they are looking after with different conditions. I'm not sure that it was particularly designed to provide health charities with a handy record of how many people (over the age of 17) have a particular condition, but that info is there and that's the only reason I've ever looked at the data.

Taking England (because I live there and it's got the biggest population) as an example, the data is available at the level of 'everyone in England', 'everyone in a particular Strategic Health Authority', 'everyone at a particular practice' - using the latter you can also find out information about how many people in a particular town (eg with 5 practices) have a particular condition.

I won't pretend that I've ever gone to the QOF's landing page and gone straight to the relevant file and picked out the answer I want. Usually it's a case of clicking on a few things, trying to remember how I did it last year and eventually finding it. Or asking a colleague.

The QOF figures for 2011/2012 came out in October but I paid little heed to them as I'm no longer in the business of updating statistics, but then I remembered my very large DiabetesStatistics google site and I thought... I really ought to try and update it.

Easier said than done.

I created it largely for me but also for colleagues and anyone else who might be interested in statistics. Its strength, for me, is that I left reasonably good instructions on how I got hold of the figures rather than just the figures themselves.

However an additional challenge is that statistics websites often move things around, so while the instructions are good, you can't 'follow along' using previously captured figures as a guide to how to do it now because all the pages and files are '404 not found' or worse, take you to the sites' homepages.

Every single link in this section below, of this QOF overview page is now non-functioning ;)

NHS Information Centre's pages on QOF ENGLAND
Landing page (2010/2011) » data tables » Prevalence data tables » SHA level QOF tables (opens .xls)

Below is the very detailed record of my attempts to correct the links for 2010/2011 data and find 2011/2012 data. It's quite long...

In the text below the addresses are shown as embedded links but for URL hackers I've left the full addresses at the end, though the NHS IC ones don't really lend themselves to that unfortunately.

Firstly, I found where the 2010/2011 data for England had been moved, here are the links for each of them... this was partly to correct the links but also to refamiliarise myself with the process of where to look for the relevant number.

2010-2011 QOF data for ENGLAND - published in 2011
So, now onto the four nations...

2011-2012 QOF data for ENGLAND - published on 26 October 2012
Checking as we go...
For fun (your mileage may vary) you can also fact-check you've got the correct stat by googling that number along with the word diabetes - I found this question asked and answered in Parliament, which mentioned this specific figure, so this is an independent verification.

I also searched on Google for million Diabetes UK and then restricted to the most recent month to see any instances of things like "there are X million people" on DUK's site, or "According to Diabetes UK there are x million people with diabetes". I found a couple of references to 3.7 million, so then searched for that to find their September 2012 report which specifically mentions 3.7 million people with diabetes in the UK - this will include all England, Scotland, Northern Ireland and Wales 'known cases' of diabetes - this just tells me that once I've got all my figures they should probably combine to form 3.7 million (unless they're including an estimate of people with undiagnosed diabetes which is about 850,000 but I doubt it).

And Googling for 75,837 diabetes brings up plenty of evidence that others have picked the same figure, which is always heartening.

This one was a wee bit tougher because the relevant spreadsheet has macros and regardless of whether or not I enabled or disabled them the file wouldn't work on my computer (a Mac) and I couldn't select the QOF register I wanted to look at. Fortunately I spotted there was a second tab, which presents the data in a different way, so I went for that one instead. On Googling the resulting number (234,871) I didn't find much evidence of it having been used by anyone, so possibly another figure is in use.

However it is similar to the figure for last year's Scotland prevalence 2010/2011 on Diabetes UK's page (223,494) and slightly larger, so it's pretty plausible.

It's also possible that I picked the wrong file, so corrections would be appreciated! So many files...
Incidentally my page on Scotland stats took me to the 2011 register information, from which I was able to work out the landing page above.

(*) It should be possible to do it using the first tab ('by NHS board') but the macros weren't friendly. Where it says (near the top) 'please select QOF register' it should be possible to select Diabetes from a drop-down menu and get the relevant information for NHS Scotland as a whole, and then for each NHS region.

Other figures I found
Diabetes rate in Scotland continues to increase BBC News (20 August 2012) - 247,278 people, this figure comes from the Scottish Diabetes Survey.

The Scottish Diabetes Survey seems to be consistently a bit higher than QOF because for 2010/2011 prevalence Diabetes UK was using QOF figures of 223,494 and SDS for 2010 had it as 237,468.

Last year I probably knew why there was a difference ;)

  • After a lot of false starts, I discovered this helpful page from the GP Contract website, called QOF Database - Wales - DM 19
  • Look for DM 19 | 2012 = 167,537

GP Contract - QOF Database - Wales - DM 19


General Medical Services Contract: Quality and Outcomes Framework Statistics, 2011-12
QOF Data Summary for Wales and LHBs, 2011-12


Unless you are tasked with trying to find information from scratch and the links above don't work you probably don't need to look further. If the links above don't work you may be able to draw some conclusions about site hierarchies from the links' structures below.\

Other useful resources
Association of Public Health Observatories (APHO)'s Diabetes Prevalence Model (England, Scotland, Wales only)

Raw URLs / addresses
2010-2011 QOF data for ENGLAND - published in 2011
Quality and Outcomes Framework - 2010-11
Quality and Outcomes Framework - 2010-11, England level
Quality and Outcomes Framework - 2010-11, England level: Prevalence data tables

2012 data
Quality and Outcomes Framework - 2011-12
Quality and Outcomes Framework - 2011-12, England level
Quality and Outcomes Framework - 2011-12, England level: Prevalence data tables

The chances are that next year's file might be called qof-12-13-data-tab-eng-nat-prev.xls which you can search for, although it's not clear from the PUB0123 type numbers in the URL where it will be located so file searching probably best unless the landing page helps you.

My DiabetesStatistics page on England

NI Direct QOF data
Local Commissioning Group Level (for relevant year)
Achievement in the Clinical Areas table:  Diabetes XLS

My DiabetesStatistics page on Northern Ireland

ISD Scotland's Quality and Outcomes Framework
Register and prevalence data at Scotland, NHS Board and CHP level
Prevalence reported from QOF registers (practices with any contract type)

Scottish Diabetes Survey

My DiabetesStatistics page on Scotland


Quality and Outcomes Framework (QOF) by local health board and disease registers 

My DiabetesStatisitcs page on Wales


  1. Why is there a difference between SDS and QOF - SDS includes children and QOF doesn't.


Comment policy: I enthusiastically welcome corrections and I entertain polite disagreement ;) Because of the nature of this blog it attracts a LOT - 5 a day at the moment - of spam comments (I write about spam practices,misleading marketing and unevidenced quackery) and so I'm more likely to post a pasted version of your comment, removing any hyperlinks.

Comments written in ALL CAPS LOCK will be deleted and I won't publish any pro-homeopathy comments, that ship has sailed I'm afraid (it's nonsense).