Generating spreadsheets of IATI data for use at country level

(Mark Brough) #1

ODI’s Budget Strengthening Initiative (BSI) has been working for Liberia’s Ministry of Finance and Development Planning (MFDP) to develop a tool to extract data from IATI in nice, clean spreadsheets. The tool is now live and you can see it here:

Aim of the tool

The idea was to use IATI to provide an easy to access source of aid data in a spreadsheet format, where data is otherwise unavailable at country level. Over time, IATI data could become the primary mechanism for data collection, but that will depend on the availability of systems for import and good quality data from specific donors, as well as confidence building and training at the country level.

The tool demonstrates that it is possible to get a good first list of projects from IATI data into a useful spreadsheet format. We will improve the format further over time in response to feedback from MFDP.

Next steps

We will continue to make further improvements to the tool’s usability, to the format of the spreadsheet output, and to include more donors as their data improves (or as we are able to develop workarounds to handle basic issues in their data).

More detailed description

In a little more detail, the tool does the following:

  • retrieves data each night from the IATI Datastore
  • captures data from a subset of official donors which have good quality data
  • converts all currencies to USD, using precise rates from the Federal Reserve and OECD
  • maps data against country fiscal years and quarters
  • reads data in v1.x or v2.x of the IATI Standard
  • flattens hierarchies in publishers’ data in an intelligent way (depending on the donor)
  • allows data to be viewed and exported in Excel files
  • available in English and French

A few limitations of the tool:

  • excludes (deliberately) donors that do not have good enough data
  • does not include documents or results
  • currently only available for Liberia

The source code is also available here:

Currently, we generate data for Liberia by fetching data each night, writing the data to a database, and then freezing this to a static site to make viewing the site much faster. We will spend some time trying to speed up this process, which should make it possible to make the site available for more countries (the only issue here is the speed of generating spreadsheet files).

Flattening IATI into a CRS-like dataset