Data Web API

Data Analysts

Data analysts who prefer to work with data using Excel can do so using a free plug-in called PowerPivot provided by Microsoft. PowerPivot is a powerful data mashup and data exploration tool which can be downloaded from Microsoft Business Intelligence.

Please note that you will need Microsoft Office 2010 or later.

Excel 2010 & PowerPivot

With PowerPivot installed, open up Excel and on the top ribbon you will find the tab called PowerPivot. Select this tab and on the left top corner there is a section named “PowerPivot Window”. Click on it and this will open separate PowerPivot Window.

Under the Home tab, click on the button "From Data Feeds". This will open a dialog wizard for importing data into Power Pivot.

In the "Data Feed Url:" text box, enter the url http://web-api.theglobalfund.org/odata/reference/GeographicAreas and click next to continue with the data import.

The next step of the dialog will allow you to specify a friendly name for the table. For the sake of this guide, change the Friendly Name to Country Names and click the "Finish" button.

At this point, Power Pivot will start to download the data. Once it has done so, you should see a dialog like below.

Close the dialog, and now you will see a table of data.

Once again click on the "From Data Feeds" button, and follow the same steps, this time using the following Url:

http://web-api.theglobalfund.org/odata/grants/GrantAgreements

This will download all available Grant Agreements which you should also see in as a table

Now you will need to estableish a relationship between these two tables. To do this, click on the Diagram View button located in the Home tab or the ribbon. It looks like the botton pictured below

From the Country Names table, drag the column GeographicAreaId and drop it onto the GeographicAreaId column on the Grant Data table. If you want to be sure of the relationship you just created, double click on the arrow and you can make any edits to the relationship if needed.

Select the PivotTable menu, and once again, for this guide, select PivotTable.

You will be redirected back to excel worksheet with classic view and functions of the pivot table. On the right side you will find two data tables (Country Names and Grant Data). From table “Country Names” select item GeographicAreaName and from “Grant Data” table select item TotalSignedAmount and your table should look like this.

Excel 2013 & Power Pivot

Assuming you have Power Pivot installed, open up Excel and select the DATA tab as below.

Open up the menu "From Other Sources" and select the "OData Data Feed".

In the "Link to File:" text box, type in the following url:

http://web-api.theglobalfund.org/odata/reference/GeographicAreas

This resource provides a table which contains a list of Geographic Areas. Please note, for now, the last segment of the url is Case Sensitive.

Click "Next" and you should now see a table available. Select the table GeographicAreas and click on next.

You can leave the default values for the Save Data Connection File dialog or customize it as below to make it a bit more readable and manageable and click Finish.

You will be asked what you want to add to the sheet. For the purpose of this guide, we will select table, and add it to the existing worksheet

After clicking Ok, you will see a table has been downloaded to the sheet as in the image below.

Add a new sheet to the workbook, and follow the same steps above, but this time add the following url:

http://web-api.theglobalfund.org/odata/grants/GrantAgreements

Once you have downloaded the grant agreements table, select the POWERPIVOT tab and click on the Manage button in the menu. This will open up the Power Pivot interface in a new window.

Select the diagram view. This allows you to see the tables and create relationships between them.

From the GeographicAreas table, drag the column GeographicAreaId and drop it onto the GeographicAreaId column on the GrantAgreements table. If you want to be sure of the relationship you just created, double click on the arrow and you can make any edits to the relationship if needed.

Select the PivotTable menu, and once again, for this guide, select PivotTable.

On the Right hand side of your screen, expand out the GeographicAreas and tick the GeographicAreaName column.

Then expand out the GrantAgreements and select the GrantNumber, TotalSignedAmount, TotalCommittedAmount.

The result should be the table below.

There is a complete list of available tables which you can get here, and add them to your model in the same way as was done for the GeographicAreas and GrantAgreements.

To update the data and download the latest data sets, just "Refresh All" under the DATA tab in Excel and you have the latest available data at your finger tips.


All amounts are in USD or the USD-equivalent for grants signed in EUR

The geographical designations employed by the Global Fund do not represent or imply any opinion or judgment on the legal status of any country, territory, city or area on its governmental or state authorities, or on the delimitation of its frontiers or boundaries.