Pulling pre-processed financial data for specific activities

Author of this query: IATI Secretariat

1. Scenario

  • Data user has found a list of relevant IATI activities and has their IATI Identifiers (e.g. from d-portal or IATI Datastore Classic)
  • Wants to get pre-processed financial data

2. Tools 

3. Description

CDFD contains pre-processed transaction and budget data. Transaction values are converted into USD, aggregated into quarters and then split into multiple rows where all values in all other data fields are the same. Data can be downloaded on a per country or per region basis.

If a data user wants to find the financial information across countries and regions, data can be downloaded in bulk and combined. This combined data can then be merged with the list of relevant IATI Identifiers, and data for the relevant identifiers returned.

4. Steps to access the data

1) In a new Excel file create a column of relevant IATI Identifiers. The column doesn’t need a title. Save this as a new Excel file.

2) Bulk download all data from the CDFD: https://github.com/iati-data-access/data/archive/refs/heads/gh-pages.zip. This will be a zipped file containing multiple CSV files.

3) Separate out the files into two folders. One for transactions, one for budgets.

4) Open a new Excel file.

5) Go to the ‘Data’ tab, select Get Data / From File / From Folder.

6) Select either your newly created transaction or budget folder.

7) The metadata for each file will be displayed, select ‘Combine / Combine and Transform Data’. Note, do not load the data as this will be too many rows for Excel to handle!

8) Choose a file to preview, and select the Data icon. If the data looks as expected click ‘OK’.

9) This will open up the Power Query Editor with a preview of the data.

10) In the Editor go to ‘Home’, New Source / File / Excel. Select your Excel file of relevant IATI Identifiers.

11) Preview the data and click ‘OK’. You can then give the data a column name and rename the table to something useful.

12) On the left hand side you should now see two sets of data under ‘Other Queries’.

13) Close the Power Query Editor, keeping the changes. This will take you back to Excel and you’ll want to stop the download of your data under the ‘Queries & Connections’ box (right click on the data table and select ‘Cancel’).

14) Once stopped, go to the ‘Data’ tab, Get Data / Combine Queries / Merge.

15) Now you need to choose your two data sources to merge. 

  • First is the transaction or budget data. Select the relevant table and highlight the IATI Identifier column.
  • Second is your table of relevant identifiers. Select this table and highlight the column of identifiers.

16) Choose a Right Outer join, this will keep all rows from the first sheet that are mapped to the second (your relevant identifiers).

(If you’re asked about security/privacy settings, click ‘Ignore’ and save. Also note, the names of the chosen files shouldn’t contain the word ‘merge’.)

17) Once you’ve selected your two data sources, highlighted the columns to merge on, and selected the kind of join required, click ‘OK’. Then select ‘Close and Load.’

18) This will start the process of merging and downloading the data. Note, this may take a long time!

19) This will result in a table of relevant data. The ‘Queries & Connections’ panel on the right hand side will show you how many rows have been loaded.

20) Job done! You can now analyse your financial data.