Funding/activities related to specific health issue in country x with free-text search

Author of this query: IATI Secretariat


1. Query 

Activities responding to a particular disease


2. Description 

Source of query (hypothetical or actual): University, global health researcher

Intended use of the data (hypothetical or actual): Research on international funding of certain health issues/disease focus.


3. Steps to access and / or analyze data

Data needed: 

  • Recipient countries
  • Titles and descriptions of relevant activities
  • Disbursements, expenditures and budgets for relevant activities

Data access tool(s) recommended: 

Steps suggested:

Part 1: Locate country related activities

Using Datastore Classic’s query builder select:

  • One or multiple recipient countries
  • Formatting option ‘CSV’
  • EntireSelection
  • One activity per row
  • No repeated rows

The resulting CSV file will contain activities containing the recipient country/ies you have chosen.

Part 2: Locate disease relevant activities within the list of activities

  1. Most health related issues and diseases don’t have a specific sector code. Rather, they can be found using text searches. This can either be done by filtering datasets for specific words or using custom conditional formatting in Excel.
  2. We recommend using custom conditional formatting. For example, applying the following formula to the entire spreadsheet will highlight rows that contain the text “diabet”, which will include ‘diabetic’ and ‘diabetes’. (Note that the * characters are wildcards meaning other characters can come before and after the given text).

=COUNTIF(1:1,"*diabet*")

This can be repeated for various words as well as sector codes.

Part 3: Locate financial information

  1. While financial information is present in the files downloaded from Datastore Classic, it needs a lot of processing work, e.g. splitting transactions into their share per sector and/or country, to be able to use it. Rather than doing this work in Excel, pre-processed financial data can be pulled from the Country Development Finance Data tool and combined with the data in the file from IATI Datastore Classic with the relevant activities.
  2. There are two options for retrieving data from the Country Development Finance Data tool
    • Single country
      1. On the tool, choose the country you are interested in and download either the transaction or budget data as required. This will return all of the financial data for activities happening in the selected country.
      2. You need to filter these results for the activities that correspond to the health related activities you found using Datastore Classic. A good way of doing this is using a v-lookup in excel to search for the presence of each iati-identifier value from the IATI Datastore Classic file in the `IATI Identifier` column in the Country Development Finance Data tool’s output.
    • Multi-country
      1. To do this, you will need to use the list of iati-identifiers for the relevant activities you’ve identified and follow the steps in the Pulling pre-processed financial data for specific set of IATI activities guidance. This will allow you to combine multiple files from the Country Development Finance Data tool and then search within the resulting dataset.

4. Result

List of country based activities that also address a specific health issue, e.g., diabetes, and the projected and actual spend of these activities


5. Caveats / Considerations / Challenges

  • Organisations report their spending to IATI according to varying timelines (e.g. monthly, quarterly, annual) which means that the completeness of the data varies depending on when the data is retrieved.
  • As multiple organisations in the delivery chain can report their spending, this likely results in some double counting of resources.
Files