Pulling pre-processed financial data for specific activities
Author of this query: IATI Secretariat
- Data user has found a list of relevant IATI activities and has their IATI Identifiers (e.g. from d-portal or IATI Datastore Search)
- Wants to get pre-processed financial data
- Country Development Finance Data (CDFD)
- Windows version of Microsoft Excel (includes Power Query)
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.
3) This will be a zipped file containing multiple CSV files.
4) There will be 1 file per country or region (Delete the unreadable index files so tat there are only readable files).
5) Extract the files and select a destination for where to save the files.
6) Open a new Excel file.
7) Go to the ‘Data’ tab, select Get Data / From File / From Folder.
8) Select the newly extracted folder with the CDFD files.
9) 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!
10) Choose a file to preview, and select the Data icon. If the data looks as expected click ‘OK’.
11) This will open up the Power Query Editor with a preview of the data.
12) In the Editor go to ‘Home’, New Source / File / Excel. Select your Excel file of relevant IATI Identifiers.
13) Preview the data and click ‘OK’. You can then give the data a column name and rename the table to something useful.
14) On the left hand side you should now see two sets of data under ‘Other Queries’.
15) 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’).
16) Once stopped, go to the ‘Data’ tab, Get Data / Combine Queries / Merge.
17) Now you need to choose your two data sources to merge.
- First is the folder with all the CDFD files. 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.
18) 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’.)
19) 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.’
20) This will start the process of merging and downloading the data. Note, this may take a long time!
21) 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.
22) Job done! You can now analyse your financial data.