Author Archives: paulbradshaw

VIDEO: Drew Sullivan’s tips on investigating people and businesses

Leave a reply

Drew Sullivan is a trainer of investigative journalists with a particular interest in corruption and organised crime. In this short video he gives his tips on investigating people and businesses: look for where they have made a mark – their births, partnerships, separations, deaths, connections and purchases.

New Code of Practice for LA Transparency

Leave a reply

At the end of September, the Department for Communities and Local Government produced a new code of practice which aims to encourage Local Authorities to improve their openness and transparency. The goal is that the code will encourage the publication of more Local Authority data, which will in turn improve local knowledge and ‘spark more improvements in the way services are delivered’.

Communities Secretary Eric Pickles seems very impressed with the new code saying “The code sets out clear expectations. It will help unlock more information and increase accessibility for everyone, taking us one step closer to our ambition to be the most transparent government in the world.”

It all sounds very good, but what does the new code bring to the table? Overall the message is quite clear – local authorities should publish their data by default. It seems good in theory, but local authorities already have a great justification for publishing their information. The increasing cost of complying with Freedom of Information (FOI) requests was said to be around £34m in November 2010 and anecdotal evidence suggests the number of requests are continuing to rise. Making more data available – and in a way which it is easily located – would help authorities to cut the workload and cost of dealing with FOI requests.

On that basis it’s easy to see why a number of Local Authorities have already adopted some of the points within the code. For example, one of the minimum requirements set out by the code is that expenditure over £500 is published, but even the accompanying press release notes that currently Nottingham City Council is the only council not already doing this on a regular basis – so is there any need to include this?

The press release goes on to say that “ministers are minded to make the Code a legally binding requirement to ensure authorities can be held fully accountable to the local people they serve.” Even if this becomes the case, there are already numerous Local Authorities who can’t – or don’t – comply with transparency laws that already exist.

In the short term the code should aid FOI requesters in their pursuit of information, by providing them with official guidelines with which to base their requests. It may even help to reinforce the idea that the Government are in favour of greater transparency. In the longer term I imagine some of the more upstanding Local Authorities will implement this new code of practice – a few might even go further. It will be interesting to see how the code is implemented in some of the more transparency shy Local Authorities though, after all it’s these authorities that generally hold the more interesting information.

Who is it and what to AsktheEU?

1 Reply

With the launch of Europe’s freedom of information requests website this week, several questions arise both regarding how it works and what we can ask the European Union.

As Help Me Investigate informed you already, asktheEU.org is defined by some limitations, among which the public nature of the requests. Other potential traps are the EU’s Data Protection rules, which prevent you from accessing certain personal records (even your own), the fares they charge for the production and delivery of a hard-copy of the information, as well as the response time-frame, as it takes longer for a request to be processed over the public holidays.

But while these catches are fascinating (and before scrutinizing them further), let us address some potential enquires for the EU. Continue reading →

AsktheEU and Open Information Europe-wide…

Leave a reply

The Freedom of Information Act is now edging on 7 years old, and despite some difficulties in using it effectively, it has proved an important and useful tool for journalists and members of the public.

In a similar vein, ‘civil society organisations’ have now launched AsktheEU.org, with which any EU citizen can file a request to obtain information held by public bodies within the EU.

“The right applies to the European Commission, the European Parliament, the Council of the European Union and most other EU offices, bodies and agencies. For some EU bodies such as the European Central Bank, however, the right only applies to the administrative functions of those bodies.”

Essentially, there is a wide range of bodies to seek information from and unlike the FOI Act, your request will be sent to the appropriate body by AsktheEU and a response is required within 15 working days.

There are search tools within the site to find other requests that may be similar to yours and all requests are made public on the site; however, no private requests can be made on this site, similar to using Whatdotheyknow.

To put it simply, as AsktheEU.org put it in the EU opendata mailing list;

“AsktheEU.org is a designed to radically simplify the process by which the public puts requests to European Union bodies: an email is sent from the website to the relevant EU body. All requests sent via AsktheEU.org and the responses are instantly made public. Requesters will have the opportunity to “me too” a request so that more than one person receives the answer, easing the workload on EU officials.”

VIDEO: Sheila Coronel gives her tips on investigating bribery

Leave a reply
Sheila Coronel is a Professor of Professional Practice in Investigative Journalism at Columbia Journalism School, and Director of the Toni Stabile Center for Investigative Journalism. She cut her investigative teeth in the Philippines. In this short video Sheila gives her advice on investigating bribery – you can also find more of her tips in a previous Help Me Investigate post.

Investigating bribery: 5 things to look for

Leave a reply

When investigating bribery, payments are often made through intermediaries, through offshore banks, and various other channels which can be hard to follow.?

At the Balkan Investigative Reporters Summer School, Sheila Coronel of Columbia University suggested that instead of focusing on the bribery itself, it is often more effective to use some of the following 5 techniques:

1. Investigate assets

While the payments themselves may be hard to trace, unusual purchases by the recipient can be easier to identify. Have they bought property? Expensive cars? Conspicuous consumption can provide useful evidence of earnings beyond their declared salary or job.

2. Investigate individual acts?

Instead of looking at the bribery itself, speak to the victims, to eyewitnesses. You might also go undercover to record it yourself – but be aware that this is a risky endeavour which also has ethical implications.

3. Prove examples in the field?

Instances of bribery can involve claims on paper (that a project is completed; that there were no other bids) which conflict with reality (the project has been abandoned; there were many other bids). If you can prove the documentation is not true, there will be questions to answer.

4. Investigate processes?

One result of bribery may be that processes (of bidding for contracts, for example) are perverted. Look at those processes and identify areas of interference, unfairness, or illegality (for example, where regulations are not followed).

5. Investigate public behaviour?

Those in charge of awarding contracts may be visibly behaving inappropriately or unethically – inviting those from bidding companies to family occasions, for example, or enjoying unusual hospitality from them. They may make statements that contradict formal policy or indicate they have not followed proper procedure. Social networks can be a useful source of such 'public' behaviour.?

FOI: What is the Section 44 exemption and how can I address it?

7 Replies

The Freedom of Information Act (FOIA) includes a number of exemptions – or reasons – why a public body can withhold the information you’ve requested. The majority of these are open to interpretation or opinion, and so when they are used to refuse your application they can be challenged – with the ultimate decision resting with the Information Commissioner. It’s particularly useful to follow those decisions, because they can set precedents and be used as part of your request to pre-empt possible excuses.

Even if you can’t pull together a convincing argument that an exemption doesn’t apply, it can be overruled by a public interest test. This ultimately means that even if something is exempt, the information must still be disclosed unless the public interest in maintaining the exemption is greater than the public interest in disclosing it.

Twenty (or so) working days ago, I sent off a FOI request to a local authority, requesting information about a councillor who had been referred to the councils standards committee. The email I got back introduced me to a whole new exemption – one which seems to be the FOI equivalent of the blue screen of death.

Section 44 (S44) of the FOIA – or to use its more friendly title ‘Prohibitions on Disclosure‘ – deals with data which is controlled by other legislation or “obligation”.

The most important aspect of a S44 exemption, is that it’s absolute – which means there is no need for the public body to consider the public interest of the data.

In relation to my request, the council refused under section 44(1)(a) – ‘Where the disclosure of information is prohibited under any enactment’. As with all exemptions the public body should point you to the reason for the exemption, in this case, they pointed out the legislation that prevents the release of the information (Complaints referred to the Standards Committee Referrals Sub Committee are confidential by virtue of paragraphs 1 and 2 of Part 1 of Schedule 12A of the Local Government Act 1972).

The first step should be to consider your initial request against the legislation; is every aspect of your request covered by the legislation? Does the S44 exemption seem relevant to your request (has it been applied correctly?)

The next step is more complex, and requires more in-depth research into the specific legislation the public body are using S44:

  • Does it actually mean the data can’t be released?
  • Has it been made irrelevant by newer legislation or by European obligations/directives?
  • Are there any situations where the law does not apply? (With reference to my FOI request, the legislation doesn’t apply should the individual involved agree to the release of the data, or if the data has been legally released previously).

Depending on the nature/importance of the information, it may be worth getting some legal advice – the law, as it’s written is very complex, and can be difficult to research/interpret.

Another avenue to try is to search Decision Notices on the ICO’s website, to see if they have ruled on a similar request previously. (There currently is only one previous case where S44(1)(a) has come up – FS50168774.)

This should give you some idea if the ICO agrees with the public body’s application of the S44 exemption, and the grounds on which it decided.

S44 is a tough exemption, it is down to the requester to ensure it’s been applied correctly, and with reference to relevant legislation.

There is little scope to respond to a S44 denial, but I’m sure as time goes on we will see more cases where people manage to successfully do just that.

Of course the final option is to petition the Government to change the legislation involved. I await a response from Ken Clarke and will let you know how that goes.

Structured Query Language (SQL): an introduction

Leave a reply
.
The Centre for Investigative Journalism this year offered a training session on Structured Query Language (SQL) for journalists at the tail end of their summer school, which was well worth attending,

SQL, which runs almost all database programmes, such as Access and FileMaker Pro, is a powerful computer-assisted reporting (CAR) tool that can help you analyse even large datasets by asking questions or “queries” of it, so that you can find the stories worth writing about.

For the purposes of the training, the tutor, David Donald, Data Editor at the Center for Public Integrity (CPI) in Washington D.C., used MySQL and Navicat Lite, both open source. MySQL also runs on all platforms (Windows, Mac or Linux).

I have summarised below some of the basics I have learned, as a taster:

Download and Install

You can download and install MySQL, followed by Navicat, a graphical user interface (GUI – ‘gooey’), from these sites:

Then Install Navicat Lite from here: 

  1. In Navicat Lite click on Connection > MySQL to create a new connection. 
  2. Give it any name you want, and leave ‘localhost’ as it is (this means your computer). 
  3. Type the password you created for your MySQL databases in the password box. 
  4. Click OK.
(You can also use this window to connect to a server in your company, but you’ll need to ask someone in your technical department for the details.)

Creating your first database
  1. Once you’ve created your connection you’ll notice, there are already a couple of databases – these were added when you installed MySQL. 
  2. Ignore these, but right click either on the name of your connection or the databases and select ‘New Database’.
  3. Give it a name and click OK.
  4. You should now be able to see it alongside the other databases.
 

Importing Tables

  1. Open the Navicat Lite window.
  2. Highlight ‘Tables’ on the left hand column.
  3. The ‘Import Wizard’ will appear at the top. 
  4. Start importing; follow instructions on screen.

When you first import a table for analysis, you will have to tick a few buttons in the Navicat window. Things to remember are the DATA TYPES that you can have for each column:

  • VARCHAR can be text, character or string. If the table uses coding, for example 1 for female, 2 for male, you will want to consider this text, not number, that is, VARCHAR, not FLOAT.
  • FLOAT – if you tick FLOAT, you are telling the computer the data in question is a number
  • DATEs – check if dates on your dataset use the American or British format and select accordingly. Years can be entered as four or two digits, eg 18/07/2011 or 18/07/11.

Basic commands
The following are the six basic commands in MySQL.Conventionally they are written in ALL CAPS.

The two required commands are: 
  • SELECT – tells you which columns or fields you are pulling data from.
  • FROM – the name of the table the data is coming from. May be case sensitive.
All other commands are optional but they MUST be used in this order, even if you don’t use all of them:
  • WHERE – is the criteria and works like a filter [eg =’NHS’ or =#01/05/2010# or >34 / For multiple criteria use AND. eg/ Category=’NHS’ AND Region=’England’]
  • GROUP BY – is the aggregator
  • HAVING – always used together with GROUP BY and is the second criteria after ag gregation
  • ORDER BY – to sort your data; can be numeric or alphabetic

Basic rules for data analysis
  • Never bring in more columns into queries than necessary. Keep them as simple as you can.
  • Queries are extremely literal: any spelling mistakes, spaces where there shouldn’t be one will affect results.
  • To indicate you are expecting TEXT, use single quote mark (‘…’), eg ‘NHS’
  • To indicate you are expecting a NUMBER, use NO PUNCTUATION.
  • To indicate you are expectating a DATE, use hashmarks on both sides (#…#), eg #07/07/77#
  • Use the wildcard (*) to indicate you want the query to consider everything, not specific columns/fields eg in SELECT
  • Never make any changes in the original table. Keep it pristine.

Functions/Expressions and Order by
 If you want to calculate use the function:

SUM(argument) – the ‘argument’ will be a column name, e.g. sum(debt) or sum(amount) [N.B.: no space between sum and the parentheses]

N.B.: You can use a function in SELECT but never in GROUP

You can ORDER BY ascending or descending (DESC) order. The default is ascending. Enter DESC If you want numbers ordered from highest to lowest.

Examples of queries
Say you are working on a table called TENNGIVE, which lists names of people who have made donations to political parties and the sums they donated. A query on MySQL could look like this:

  • SELECT last, rest, sum(amount) [= this means you want query results to show columns called last, rest and the sum of the amounts each person donated]
  • FROM TENNGIVE [=name of table you are using]
  • GROUP BY last, rest
  • HAVING sum(amount) > 2000 [=you only want a list of people who contributed more than 2,000pounds/dollars for example]
  • ORDER BY sum(amount) DESC [=you want the sum column ordered in descending order from high to low]
  • or ORDER BY 3 DESC [=in which 3 indicates you want it to sort column 3]

Observe what happens in this example.
– Last is a column with surnames
– Rest is a column with first names, including middle names
– The list included people called Haslam but I suspect some could be misspelled or they could have Haslam in their middle names
– This was resolved by using LIKE and a % for the possible mis-spellings

  • SELECT last, rest, occupation, sum(amount)
  • FROM TENNGIVE
  • WHERE last LIKE ‘hasl%’ or rest LIKE ‘%hasl%’ [This ensures people with Haslam in their last (last) OR middle names (rest) appear, even if misspelled]
  • GROUP BY last, rest, occupation [Note that GROUP BY does not, and should not, include the function: sum(amount)]
  • ORDER BY 3 DESC

COUNT(*)
Under SELECT you could want a COUNT of how many different occupations are on the table/database, for example 15 lawyers, 10 teachers, etc:
  • SELECT occupation, COUNT(occupation)
  • FROM TENNGIVE
  • GROUP BY occupation
  • ORDER BY 2 DESC

COUNT(occupation) is the ‘argument’ here. But the problem with COUNT is that it does not include any NULLs, which completely skews the results of your query. To resolve this problem use COUNT(*), which counts everything, including nulls.

Many data journalists panic when they realise they have been extracting data without the (*) after COUNT and publishing stories using completely erroneous data. You have been warned: to include the zeros in your dataset, use COUNT(*).

Cleaning data
Your queries could also give you inaccurate results if the data in your table is “dirty”. BBC and B.B.C., Housewife and Housewife(space), for example, could be counted twice when they are the same, or you may want to get rid of any HTML codes in your dataset.

A few ways to clean your data:
  1. Use TRIM in SELECT, eg. SELECT TRIM(occupation), COUNT(occupation)
  2. Use Google Refine, which you can download free onto your computer. The post Cleaning Data using Google Refine in the Online Journalism Blog may help.
  3. Other simple ways of cleaning data on an Excel sheet can be found here.

MySQL servers: MySQL vs. MySQL Server Express
  • MySQL is an open-source relational database system (RDBMS) owned by Oracle, with a freely available code source. It runs on all operating systems (OS). 
  • There is also another free RDBMS called SQL Server Express. The latest version is the 2008 one. Being a Microsoft product, SQL Server Express will only run on Windows but Mac users can download a (free) programme called MAMP (=Macintosh, Apache, MySQL and PHP). 
  • SQL Server is MySQL’s more full-bodied “big brother” but it comes at a cost.
  • Both servers have their pluses and minuses. I found this SQL Authority.com blog post (N.B. bear in mind this post is from 2009) helpful. 

David Donald says with MySQL there are no two ways: it’s “use it or lose it”. The best thing is to download it all and get practising.  

Online Tutorials
Don’t panic – online tutorials and forums are available if you get stuck:
  1. 1. MySQL Tutorial
  2. 2. Tutorials Point (includes a discussion forum)
  3. 3. Navicat support (for Windows, Mac and Linux)

Further reading
  

Further SQL training

SQL is not an exclusive remit of journalists of course. Far from it. Anyone who works or analyses large datasets/databases can benefit from it. As a matter of fact, a few City workers attended the session alongside journalists and CAR enthusiasts like myself.

The session was a crash course, which gave you enough basic knowledge to start using SQL. I assure you it was “safe” even for non-geeks to follow, but, as David said, once you learn it, you do need to keep using it in order to achieve geekdom… 

If you are interested in learning it in more depth, David Donald will be hosting a one-week “CAR bootcamp” in the first week of October. Registration will open after 15th September, so keep checking the CIJ website or sign up to their newsletter.

Updated on 13/09/2011:
Information on the autumn Data Journalism/CAR workshop with David Donald is already up on the CIJ website: 5-9 October 2011. The early bird discount is only valid until Thursday, 15th September, so better hurry. There is also the option of joining the Advanced SQL course on the last three days at a reduced cost. Check out the course’s timetable here and general info here.