Category Archives: Tips and tricks

What to do when an FOI response is not provided in the form asked for

Leave a reply
Heard the one about the FOI request for data to be provided in an Excel spreadsheet? The authority printed it out, scanned it, and sent a PDF version of the scan.

You’ll forgive the journalist for being suspicious when an authority goes to such extremes to make it hard to interrogate their data.

In cases like these it’s worth looking at the Information Commissioner’s awareness guidance 29 on ‘means of communication’ (PDF):

This quotes Section 11(1) of the Freedom of Information Act, which stipulates that authorities should comply with your preference for “a copy of the information in [a] form acceptable to the applicant … so far as is reasonably practicable”

The key phrase here is “reasonably practicable”. In the example above, there is no excuse that simply sending the original Excel file – instead of a scanned PDF – was not “reasonably practicable”. 

What then? Well, you should ask for an explanation, and make a formal complaint to the authority quoting the ICO guidance and Section 11(1) or the FOI Act. If that doesn’t get any results, write to the ICO. Here’s the full passage from the guidance:

“If a public authority decides that it is not reasonably practicable to provide the information in the form preferred by the applicant … the authority must tell the applicant and give its reasons. The duty on the public authority is then to provide the information by any means which are reasonable in the circumstances. 

“If the applicant is not satisfied with the decision and wants to make a complaint, they must complete the public authority’s complaints procedure (if there is one). Once this process is complete, if the applicant remains dissatisfied, they may write to the ICO.”

If you are unlucky to deal with an authority which is regularly uncooperative in this manner, it may be worth quoting the awareness guidance 29 and Section 11(1) of the Act in your request for the information to be provided in spreadsheet form, for example:

“I would like this information to be provided in spreadsheet format (xls or csv) in line with Section 11(1) of the FOI Act and ICO Awareness Guidance 29.

Also useful more broadly when looking at the way a request is handled are the guidelines on ‘Request handling’ on the ICO website.

The best way to set up a spreadsheet for inputting FOI responses

Leave a reply

On Help Me Investigate the Olympics we’ve been sending FOI requests to all of the local authorities which hosted the Olympic torch relay. 

That’s the easy part. The difficult bit is compiling the results into something meaningful. To do this, it’s helpful to create a spreadsheet using Google Docs so that all members can input the results. In the process, however, we’ve used a number of techniques to both speed up and future-proof the results.

Drop-down menus 

Firstly, it helps to make sure that you will be able to match the data you’re inputting with other data, such as the population of a local authority, or the political party in charge.

You can do this in your Google spreadsheet by creating a drop-down menu for the ‘matching’ column (normally an authority name or code) which draws from another list. This is how:
  1. Select the column you want your drop-down lists to appear in (normally A)
  2. Click on Data>Validation…
  3. On the window that appears, click on the drop-down menu for Criteria and select Items from a list.
  4. You can now either type in the list yourself, or select a range of cells that contain the items for your list (Create list from a range). It’s best to do the latter: click on the grid button to the right of this box, go to the sheet containing your ‘official’ list (which you’ll need to have copied into another sheet in your spreadsheet – here’s a good one to use from OpenlyLocal) and select the column or row containing your items. You should end up with a reference that looks like this: SheetName!A:A. Click OK. And click Save to apply the drop-down.
  5. Back on your data entry sheet each cell in the column you first selected should now have a small drop-down icon to the right. Users can select from this to make sure they’re using the same names for authorities as your ‘official’ list.

Input data at the most granular level

If you’re inputting data that isn’t consistently categorised across different respondents (as is most often the case), you need to input the data so that this doesn’t cause a problem.

The simplest way to do this – and also the fastest way – is to avoid imposing a particular classification system (either one authority’s, or your own) onto your data as you’re inputting it.

Imposing a classification system slows down the inputting process (the user has to make a decision where to categorise each piece of data), can introduce inconsistency (one user makes a different decision to another), and add extra work later on (what if you realise you decide to add a new category halfway through?)

Much simpler is to input the data in as basic a fashion as possible.

For example, we’re requesting information on spending. Instead of having one column per local authority, and having columns for each type of spending, like this:

NAME ——— TYPE1 — TYPE2 — TOTAL
COUNCIL A – 100 ——- 10000 —- 10100

It’s simpler to have one row per item like so:

NAME ——— TYPE — AMOUNT — NOTES
COUNCIL A – Lights –10000 ——— “Blah blah”

This allows you to add extra columns when required (for example, if a third party contributed funding, or if you want a column for answers to a particular question).

It also means you don’t have to think about classification now. You can impose this on the data later by, for example, filtering it to look for rows which mention “light”, “lighting”, and other terms you’ve come across while inputting.

You can also generate a per-council view of the data with a straightforward pivot table.

It’s a good idea to have a ‘NOTEWORTHY?’ column so if you do come across interesting individual items you can simply put YES in that column and come back to them later, rather than get distracted by them. 

Use spreadsheet techniques to split pasted data

Even if your data has been supplied in a word document, you can paste it into your spreadsheet and use formulae to split it up as you need, rather than having to manually input each item separately.

It makes sense to have your spreadsheet set up in the same order as data is normally provided, i.e. the ‘item’ and ‘cost’ columns coming in that order, next to each other, so that these formulae work best.

Here are some typical techniques, depending on how it’s been provided:

Item and cost are separated by a space: if the items are always only one word (no spaces), then followed by the cost, you can paste them into another column in your spreadsheet and type this formula in your ‘item’ cell to grab and split them: 

=SPLIT(E34,” “)

If item and cost are separated by a colon, e.g. “Item: £200” then you can adapt it as follows:

=SPLIT(E34,”:”)

Note: change E34 to the first cell your raw data has been pasted into.

This will split the contents of that cell wherever there is a space, so anything before that space will be pulled into your ‘item’ cell, and anything after will be pulled into the cell next to it (ideally your ‘cost’ cell)

if it works, copy that formula down as many rows as you need to apply it to all the data you originally pasted.

Finally, you’ll probably want to delete the raw data – but if you do this the formulae will stop working (there’ll be nothing for them to split), so before you do that select all the cells containing the results of your formulae (both columns). Click on Copy > Paste special… and select Values only.

This will paste the results of your formulae on top of the same cells that previously contained them. That means you can safely delete anything that the now-gone formulae used.

Other formats: the SPLIT function can also be used where your costs always begin with a pound sign, like so:  

=SPLIT(E34,”£”)

This will remove the pound sign, but you don’t need it.

Where there’s no pound sign and the data has multiple spaces, but the figure is always at the end of each line, try using =RIGHT to grab it like so:

=RIGHT(E34,6)

The number (6 in the example above) specifies how many characters you want. ‘6’ will grab any amount at the end of a line up to 99,999 (note that the comma is counted as a character). If you are likely to have higher amounts, change it to 7, or 8, etc.

This formula will also grab any other characters if your amounts are small, so with the line “Lighting 100” it will give you “ng 100”. So you will probably have to combine this formula with the =SPLIT function explained above, and then just use the second cell that you get from that.

There are other techniques you can use, but these are the most commonly useful.

Secret City, a documentary investigation into the City of London,the Corporation that runs it and its role in the financial crisis.

1 Reply

City

Imagine a city consisting of fewer than 10 thousand residents. Imagine that city’s government elected by businesses, and the bigger the business, the more votes it has. This city has funds amounting to billions of pounds, accumulated over a thousand years, which it can use at will, with no public accountability, to promote its interests around the world.

This city is London. Not the metropolis we know so much about, but the thing referred to as the “square mile” or The City. Often referred to either as an archaic but quaint and harmless relic from the past, or as a place where bankers simply do business, neither description gets close to the reality of this entity.

Secret City, the film I’ve made with Michael Chanan does try to do just that.

Ten years ago I met with my former lecturer, Maurice (now Lord) Glasman, in an East End cafe, seeking the odd bit of part time teaching in political theory. I came out of that meeting bewildered. Maurice told me the story of this barely visible body, the Corporation of London, which is the government of London. I’d heard of it, but thought it was a bit like a local authority, with a bloke called the Lord Mayor at the helm, who was a bit like the Queen – ceremonial and harmless.

Glasman told of its historical origins, how it pre-dated Parliament, how it had shaped modern-day London, refusing to incorporate, transporting English people who’d fled the enclosures, and creating the poverty-stricken surrounding boroughs. He’d become interested in the Corporation whilst petitioning a private bill it was pushing through Parliament to deliver plural votes to businesses.

Votes

I joined Maurice, the Rev William Taylor and a multi-millionaire businessman in their campaign to petition this bill, the City of London Ward Elections act, and was stunned by two things: one, that it seemed to be trying every trick in the book to allocate votes on the basis of the wealth of a company; two, that Parliament seemed powerless to stop it.

Indeed, although there were no rational ground on which to pass the bill, it was eventually given the nod. This was hard politics, the dirty underbelly of British culture.

It is one thing for a few people to know reality of the City of London and the Corporation that runs it, but another to make it public knowledge. Bits and bobs have been written about the Corporation – back in 2002 by David Henckie at The Guardian, and over the past year by Melanie Newman at The Bureau for Investigative Journalism. But there’s hardly anything written in books or magazines, and certainly no “fourth estate” to regularly scrutinise this particular government.

Back in 2002 Political Quarterly published a piece I wrote about the Ward Elections Act campaign, but the impetus for making a film about it came from those who I’d told the story to.

On recounting the tale of the Corporation to a UWE student, Anthony Killick, we considered film a perfect way to tell the story of this state within a state. With plenty of contacts, no budget, little equipment but a determination to tell the story of the Corporation we began. The enthusiasm the story invokes in those who hear it led to Michael Chanan offering to direct the film, and thus Secret City was born, in no small thanks to the availability of relatively cheap and usable digital camera equipment and editing suites.

One of the most surprising things the film tells us is that despite all the complex theorising about the nature of power, power is not always as nebulous as one may imagine.

Indeed the Corporation of London is a hub, a centralised power that reaches far and wide. There is in fact a small conspiring clique of people intent on moulding the fabric of Britain and much of the rest of the world around its interests. There was a point from which slavery was organised, a spearhead of the empire, a location from which global capitalism started out. The incredible thing is that this location and its institutions seem to all intents and purposes unchanged over the past 1000 years.

We see in the film how the past few centuries have witnessed the Corporation – formally a pluralist democratic body – subsumed under the interest of finance capital to become a single interest lobby group.

The Corporation has promoted the domination of finance capitalism for a couple of centuries now, but its propaganda machine went into overdrive from the 1980s, as it sought to use Thatcher and Reagan’s neo-liberalism to push for further deregulation.

By the 1990s, the Corporation had in many respects turned into one of the most effective ideology factories in the world, lobbying UK regulators, and using its global network of influence to push for an ever more deregulated financial sector. This continued right up to the 2008 crash.

Having primed audiences around the world for two or three decades to understand the importance of deregulated finance, the Corporation faced a bit of a crisis: how to maintain “confidence” in neo-liberalism when the whole world has seen it fall flat and fail. How, for example, could people be convinced to accept George Osbourne’s £500bn tax break for insurance companies while we’re being told there’s not enough money for the NHS?

As Melanie Newman and the Bureau for Investigative Journalism showed in The Guardian earlier this year, the Corporation established a fund to deal with public perceptions after the crash, spending £90million in 2011 alone on consultancies, donations, lobbying, and so on.

There are many nebulous, distributed and invisible forms of power, but such a realisation ought not, as is too frequently done, push us to discount other, concrete forms of power that we can point to, touch and indeed overthrow.

Secret City shines a light on one of these sites and we hope will lead to a more adequate understanding of power and in particular the role of the Corporation of London in sustaining the hegemony of finance. It is for this reason that we chose Parliament for the preview screening of the film, inviting MPs, journalists, campaign groups and academics, before rolling it out to community, political and campaign groups, cinemas and universities.

The preview screening of Secret City took place in Parliament on 16th October. See secretcity-thefilm.com for details of forthcoming screenings. If you would like a screening, please contact salter.lee@gmail.com

How to start investigating Clinical Commissioning Groups (CCGs)

Leave a reply

This week, Help Me Investigate embarks on a significant project to investigate the new bodies which will control £60m of public health spending in England: Clinical Commissioning Groups (CCGs). 

As part of that project, we’re publishing a guide to getting started. Here it is:

Following CCGs across local newspapers and blogs

If you’re going to start scrutinising a field, it’s very useful to be kept up to date with developments in that field:

  • Concerns raised in one local newspaper may be checked elsewhere; 
  • Specialist magazines may provide guides to jargon or processes that helps save you a lot of time; 
  • Politicians might raise concerns and get answers; 
  • And expert bloggers can provide leads and questions that you might want to follow up.

Rather than checking a list of websites on the off chance that one has been updated, a much more efficient way to keep up to date on what’s happening is to use a free RSS reader. 

RSS readers pull in ‘feeds’ from a range of sources: newspapers and magazines, blogs, and searches across multiple sites, FOI requests, and parliamentary questions. You can also get mobile apps so you can quickly scan the latest feeds when you’re on the move.

Google Reader is probably the most widely-used RSS reader, and I’m going to use that for this walkthrough. At the end I’ll provide a bunch of feeds that you can subscribe to with two clicks, too, so you have a head start.

Finding your feeds

I’ve written separately about 7 ways to follow a field you want to investigate, and the principles are much the same. With CCGs you will want to follow some or all of the following:

National newspaper health sections or correspondents – most of the reports in the national press and broadcast media will not be relevant, but you can scan easily past them. Every so often, however, you will find out about a new report, initiative, or concern that’s being picked up by their reporters.

Specialist health media – the health industry is not one single profession, and you can find publications catering for numerous parts in it, from magazines for GPs, to the Nursing Times, NHSmanagers.net, Health Service Journal, British Medical Journal, Pulse, and so on. Follow the feeds from these and others that you find – coverage is typically earlier, deeper, and more explanatory than in more general media (you also get specialist sections, such as Pulse’s on commissioning at http://www.pulsetoday.co.uk/gp-commissioning or Health Service Journal’s.

 

Local health news – some newspapers have no health correspondent at all or the role is combined with other coverage. Rather than try to subscribe to every newspaper or journalist individually, go to Google Alerts and create an alert for “Clinical Commissioning Group” (change ‘Result type’ to ‘News’) – you can subscribe to an RSS feed of that alert. (You can also search for ‘CCG’ but note that this also means ‘Collectible Card Game’ and ‘Colour Changing Glass’ so add -game or -glass to your search to try to exclude these results)

Alertsrss

This search might also bring up any report or research that mentions that phrase – as long as it’s been reported, but you can change the result type from ‘News’ to ‘Everything’ if you want to include results on Google more generally.

Experts and individuals on blogs – a number of health professionals maintain blogs where they provide an insight into how changes in the NHS are affecting them, or analysis of data and other documentation surrounding changes. You can use a specialist search engine like IceRocket (or Google Blog Search) to find these, and subscribe individually – but more effective will be looking for a list of blogs maintained by someone in that network. Look for these in the outside column under ‘Blogroll’ or ‘Links’. You’ll find that one blog may lead you to lots of others, which in turn link to others. Start with The Jobbing Doctor, for example, and see where those links take you.

Don’t be limited to one network, however. To get a variety of sources try to find people from different parts of the system. For example, CCGinformation.com is written by those working within CCGs. And Dilys Jones Associates is a firm of consultants which deals with clinical commissioning (or at least has written about it more than once).

Formal bodies – The Department of Health, like most government bodies, has a number of RSS feeds. If you want to follow news on CCGs, for example, add this to Google Reader: http://www.dh.gov.uk/health/tag/clinical-commissioning-groups/ . Looking at the ‘sub-sites’ listed on the right also takes you to other feeds on ‘Modernisation of health and care’, for example (http://healthandcare.dh.gov.uk/), or ‘Transparency’ (http://transparency.dh.gov.uk/).

Individual CCGs already have their own sites, too, many with RSS feeds. Stick Herts Valley CCG (http://www.hertsvalleyscc.nhs.uk) into Google Reader, for example, and you can follow their feed.

FOI requests – What Do They Know not only allows you to make Freedom of Information (FOI) requests to public bodies – it also allows you to subscribe to RSS feeds or email alerts about FOI requests. These can be done based on an organisation, an individual making requests, or mentions of a keyword in the requests themselves. Here’s the feed for FOI requests mentioning “CCG”: http://www.whatdotheyknow.com/feed/search/CCG

Hansard – Likewise, sister site They Work For You collects information about what happens in Parliament and other assemblies. Conduct a search on the site and you can subscribe to an RSS feed of new results against the same search. Here’s one for “CCG” on there: http://www.theyworkforyou.com/search/rss/?s=ccg

Discussion – It’s trickier to subscribe to a feed of discussions about an issue or body unless you know where that discussion is. But Boardreader – a search engine for forums – offers RSS feeds for search results on a particular term if you conduct the search and then put the URL of the search results (e.g. http://boardreader.com/s/%22clinical%20commissioning%22.html) into Google Reader’s ‘Subscribe’ box. That would, for example, lead you to discussions such as this one on Mumsnet.

Boardreaderrss

You can also subscribe to the feed of a search on Twitter – although you may prefer to follow this on Twitter itself, as the results of this can drown out the rest of your feeds.

I’ve put together a bundle of 19 such feeds at this Google Reader ‘bundle’, which allows you to subscribe to them as one ordered collection with a single click. Would you add any others?

 

 

 

 

Get involved in a new HMI project: investigating CCGs

Leave a reply

CCG structure image from BBC

This year a collection of new groups will be given responsibility for £60m of public health spending in England. Clinical Commissioning Groups (CCGs).

It’s a journalism basic to ‘follow the money’, but with over 200 of these groups and very few health journalists in the UK there’s an opportunity for student journalists and concerned citizens to play a key role in understanding what CCGs do – and scrutinising their activities.

There are a number of potential avenues to explore, from concerns about potential conflicts of interest in the new arrangements, to issues of accountability, whistleblowing, and efficiency. 

In partnership with news organisations, we’re building a network of journalists, students and citizens to start pulling together information, exchanging tips and leads, and pursuing questions in the public interest.

If you want to get involved, contact me on paul@helpmeinvestigate.com or add your name via the form here.

*image from the BBC

A case study in online journalism: investigating the Olympic torch relay

Leave a reply

Over at the Online Journalism Blog I’ve written three posts on the background to the investigation into the Olympic torch relay which resulted in a number of national, local and international stories, and the ebook 8,000 Holes. 

  1. Part one outlines some of the data journalism processes involved 
  2. Part 2 explains how verification, SEO and ‘passive aggressive newsgathering’ played a role.
  3. Part 3 looks at how ebooks offered a new opportunity to tell the story in depth – and publish while the story was still topical.

Six questions to ask before you submit an FOI

Leave a reply

David Higgerson suggests six questions that journalists can ask to improve the end results of a possible FOI request – or save you sending one entirely. they are:

  1. Is this information available elsewhere?
  2. Will they release the information to me without going through FOI?
  3. Is there another way of getting this information?
  4. Do I need to think about jargon in my FOI request?
  5. Are there examples of the information being released elsewhere?
  6. What reasons for refusal could a public body come up with?

It’s nice to see the post using an example from Help Me Investigate’s very early days – Heather Brooke’s FOI request to Birmingham City Council.

New guidelines on the ‘FOI for environmental reporting’ – EIR

Leave a reply

Cleland Thom provides another of his useful roundups on ‘right to know’ laws in the UK. This time it’s the Environmental Information Regulations (EIR), a set of regulations which work in a similar way to the Freedom of Information Act, but specifically cover information about the environment.

The regulations aren’t just useful if you’re reporting on the environment – transport, energy and other issues all touch on the areas the EIR covers.

Now the Information Commissioner has issued guidelines on the regulations (PDF) for the first time. Cleland’s rundown of the basics goes as follows:

  1. The ‘default position’ is that authorities must disclose information if journalists ask for it.
  2. The regulations make it hard for an authority refuse a information request.
  3. Public authorities can release business information they hold – without the business’ knowledge or consent.
  4. Authorities cannot ask why a journalist wants information, or be obstructive or secretive because they know what they might do with it.
  5. The information must be accurate and up to date.
  6. They must respond in 20 working days.
  7. The media does not have automatic right to reproduce information they’re given. Check copyright first.
  8. Journalists can make requests by phone. But it’s best to provide written confirmation  to ensure accuracy.
  9. Authorities cannot ‘neither confirm nor deny’ they have information, unless it’s to do with international relations, defence, national security or public safety
  10. Authorities can refuse a request if it is too general – but they should then ask for a more precise request.
  11. Authorities can refuse to hand over information when It involves personal data; is against the public interest; is unreasonable; release WILL (not might) have an adverse effect on the course of justice, or someone’s intellectual property rights; the information is incomplete, or not available at all.

To give Cleland Thom a deserved plug, he is a consultant and trainer in media law, and is good.

More guidelines on EIR in this article in Local Government Lawyer from September 2011.

FOI and redaction costs – why they don’t count

Leave a reply

If you come across a public authority which claims that it cannot meet your FOI request because of the costs involved in redacting data, quote the following decision by the Information Tribunal (PDF) between South Yorkshire Police and the the Information Commission (Appeal Number: EA/2009/0029).

As Public Partners put it:

Under the Appropriate Limit and Fees Regulations no public authority need spend more than £600 on complying with a request, with most having a ceiling of £450 – this is called the appropriate limit. The limit is made up of the costs involved in locating, retrieving and extracting information. All the time spent on these activities can be added together at a rate of £25 per hour until the limit is reached. In this case South Yorkshire Police refused to disclose the whole of a 187 page document because redacting intelligence information line by line would cost them well over £450.

The [tribunal] ruled very firmly that the Police were wrong. Their decision couldn’t be much clearer: “we find that a public authority cannot include the time cost of redaction when estimating its costs”. All the Police’s arguments were dismissed and the rule now seems conclusive.