How to extract the API data in an Excel sheet

POSTED BY Hortense Regnaut | 28 October 2021 | GMS Quick Tip

  1. What is the API data in the GMS?
    1. GMS Focus: Very Important Note about the API data
  2. How to extract the API data
    1. Step 1: retrieve the API data URL
    2. Step 2: create a new, blank Excel document
    3. Step 3: paste the API data URL in the Excel document
    4. Step 4: Enter the credentials
    5. Step 5: load the API data in the Excel

 

  1. What is the API Data? 

The GMS harbors multiple series of datasets drawn from the information entered and generated on the system. This data can be used for multiple purposes, including the redaction of Annual Reports. This is why the GMS team is offering OCHA staff the possibility to extract this data depending on their needs, in the form of API Datasets.

The API data contains the raw data from the GMS. The datasets have been grouped by themes (Annual reports – contributions, annual reports – allocations, MPTF data extract, etc.)

The datasets can be downloaded as JSON and CSV files. Both these formats require some degree of specific knowledge to read them.

But Don’t Panic! You don’t need to be an IT expert to use these datasets. This blog post will take you through the step-by-step process on how to extract the API data as an Excel sheet for internal purposes.

GMS Focus: Very Important Note on API datasets 

Please do not share the API data publicly, as it includes critical information on partners and projects across all CBPFs. While our team is in the process of narrowing down the data per individual fund, it is very important that the data provided through the APIs remains private and for internal use ONLY.

 

 II. How to retrieve the API data 

Step 1: Go to https://cbpfapi.unocha.org/vo3 and copy the CSV version of the Dataset you wish to extract. 

Please note that, at the moment, you cannot select a specific Pooled Fund from the drop-down list at the top of the page. The extract you chose will be applied for all funds.

 

Step 2: Open a new, blank Excel document and save it on your computer. 

 

Step 3: In the Excel document, go to the [Data] tab, and click on [From Web] 

Paste the copied URL in the text field. Keep the default option ‘basic’ ticked. Click on [OK].

Important Note: Please double check that the beginning of the URL is “https”, and not “http”. If it is “http”, please add an ‘s’ (refer to screenshot below for an illustration of this)

 

Step 4: Enter your credentials in the new pop-up window to retrieve the data. 

To know what your username and the password are, please contact gms-support@un.org

Please note, make sure that you are in the ‘basic’ tab of this pop-up window to enter your credentials, as illustrated in the screenshot.

Then, click on the drop-down list ‘Select which level to apply these settings to’, and select the last option of the list: ‘https://cbpfapi.unocha.org/vo3/odata/GlobalGenericDataExtractSecure’.

And click on [Connect].

 

Step 5: Click on [Load] to load the data on the Excel document. 

The data might take a few minutes to load on the Excel document.

And Voila!