Co-authored by Guto Ifan
DI analysts recently ‘data dived’ headlong into the increasing wealth of data on development activities now available from the International Aid Transparency Initiative (IATI).We then produced a short blog series looking at aid from the UK’s development ministry (DFID) to Bangladesh, education in Nigeria and global malaria control.
Now, we want to share a guide to how we did it. Here’s why.
Many of you may know of IATI as a multi-stakeholder initiative: a common, open data publishing standard agreed between donors and partner countries, designed to improve and increase the available data about development activities. But if you’re a development researcher, you might not have clocked the potential of IATI data to add significant value to your work; especially useful if you spend time doing analysis with data from the OECD DAC Creditor Reporting System (CRS).
One key aim of these blogs is to inspire other analysts working in research organisations to start ‘diving into’ IATI data and using it. Two reasons:
1. The value of open data will really pick up when the data starts being used, and questions and feedback can go direct to data publishers.
2. (the DI mantra): data must be interpreted if it’s to become useful information. In IATI’s case, this means that it has to be used by people – researchers, bloggers, journalists and academics, both in developing countries and in donor countries – who can make sense of the data and turn it into communicable information. In short, we want others to have a go!
With that goal in mind, here are the steps we went through to compare data reported by DFID to the OECD DAC and published in the CRS, with data available under the IATI standard. If you have questions, please leave a comment and we’ll do our best to help.
Step #1: Fetch the IATI data you’re interested in
This is now easier to do than ever. Go to the IATI Standard datastore at http://datastore.iatistandard.org.
Select the CSV (comma-separated variables, a structured text-only file) option in the query builder.
In the options section of the page choose ‘One Transaction per row’ as your format, ‘Multi-sector expansion’ (which gives you sector-level details for each project) and ‘Entire selection’ as your sample size.
Select your reporting organisation as ‘Department for International Development: United Kingdom: GB-1’ (tip: if you type the letter ‘D’ when clicked into this box it will go down to the first entry starting with D). Then pick the sector, country or region of interest in the boxes below. Click ‘submit’ and you will get a link to download the CSV file.
Preparing the IATI data
If you open the CSV file, eg in Excel, you will see each row represents one component of a project. Column A will give you the sector code, followed by the sector name. You will then see a ‘sector-percentage’ column. This gives you the amount from the ‘transaction-value’ column that can be attributed to the sector code in column A (the ‘transaction-value’ column gives the total value of the whole project across all sectors).
In column F you will find the ‘transaction-type’ column. This column indicates whether the transaction is a commitment, disbursement or expenditure. The first step is to isolate the disbursement and expenditure (D+E in the codes), from the commitments. This makes the data comparable to the ‘disbursements’ column in the Creditor Reporting System.
We can also choose the year that we want to analyse by filtering the ‘transaction-date’ column. You may have to change the number format of this column from Numbers to Date (press Ctrl+1 to change the format of the cells in the column).
Create a new column after the ‘transaction-value’ column, and multiply with the corresponding ‘sector-percentage’ column, letting it take the full value of the transaction if there is no sector percentage given (so the formula should look something like: =IF(D2=0,I2,I2*(D2/100)) ). The new column will therefore contain the value attributable to each row entry.
Around column Y, find the ‘iati-identifier’ heading. Under this you will see a unique code for each different project. The string of numbers following the ‘GB-1’ (if you’re looking at DFID data) will correspond to the ‘projectnumber’ column in the CRS. Create a new column, only showing what comes after the ‘GB-1’ (for example through a =RIGHT(Z2,10) formula, to isolate the last 10 characters of the cells).
Downloading and preparing the Creditor Reporting System data
It is possible to access project-level CRS data from either the OECD.Stat website (available here) or from the Query Wizard for International Development Statistics, QWIDS (available here). On the OECD.Stat website, clicking the magnifying glass icon next to the data points will lead you to the project level data behind them. On QWIDS you need to tick the box next to ‘Show project-level data and/or detailed sector level data’ while selecting your data.
As we said above, you will need the disbursements data from the CRS to compare directly with your IATI data. In the agency column, you will need to isolate DFID spending from other agencies in the CRS. The IATI data will be in pounds sterling, so you will need to use the values in the ‘disbursement_national’ column.
Comparing the two data sets
With both sets of data downloaded to Excel and prepared as above, you can now compare the two and see the impact of multiple-sector reporting.
In the first column of IATI you will find sector codes that correspond with the purpose codes in column AT of the CRS data. To compare the difference for each developing country, you can use the ‘recipient-country’ column of IATI and the ‘recipientname’ column of the CRS.
Questions? Comments? Leave them below!