You are here: Home » VBA » World Bank API

World Bank API

World Bank logo

The World Bank provides loans to developing countries for various purposes.

The World Bank is a vital source of financial and technical assistance to developing countries around the world. Our mission is to fight poverty with passion and professionalism for lasting results and to help people help themselves and their environment by providing resources, sharing knowledge, building capacity and forging partnerships in the public and private sectors.


The World Bank API provides important information from the World Bank's data sources. According to the API docs,

Using the World Bank API, you can tap into more than 1,000 indicators from key data sources like World Development Indicators, Doing Business and Governance Indicators, and more than 12,000 development photos. You no longer need an API key to access the API.

In the following code samples, we'll look at a few of the API methods. Note that the code below is not exhaustive of all the available methods and parameters. For example, some queries might offer additional parameters that affect the results returned by the query. Also, not all data points are returned by all functions.

First, paste the following constant at the top of a standard module. It represents the default URL for all API queries. The default language for all queries is English. I chose English but obviously you can choose another default (see Enums for available languages). You'll also need the Enums, Enum Translators and Helper Functions found below. They should also be pasted into a standard module in the same project.

Make sure your system meets the requirements for running the code found on this page.

Public Const baseURL As String = "http://open.worldbank.org/"

Return all countries

This function returns information on all the countries the World Bank keeps data on. The data returned is:

  • 2 letter ISO 3166-1 alpha-2 code code
  • Name
  • Region Name
  • Income Level
  • Lending Type
  • Capital City
  • Longitude
  • Latitude

(See Latitude Longitude Functions for more methods for extracting latitude and longitude)

The query is cached in the local temp folder, in order to speed up the function as well as limit the amount of requests to the API. To force the query to be re-run (after it has been run the first time), the forceRequery parameter must be set to True.

Function GetWBCountries(Optional language As lang = en, Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim countries As Object ' MSXML2.IXMLDOMNodeList
Dim country As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_countries" & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    ' assume 500 countries
    With xml
      .Open "GET", baseURL & GetLang(language) & "/countries?per_page=500", False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set countries = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 8 data points
  ReDim tempString(1 To countries.Length, 1 To 8) 

  For i = 1 To countries.Length
    Set country = GetNode(countries, i)

    For j = 1 To GetChildNodes(country).Length
      tempString(i, j) = GetNode(country, j).nodeTypedValue
    Next j
  Next i

  GetWBCountries = tempString

End Function

Sample Usage

The following sample code returns all World Bank countries into a String array and prints all data points to the debug window.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBCountries
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Get Country Information for an Individual Country

This function returns the same information as GetWBCountries except for one country only. Run GetWBCountries first to get the two-letter country codes for each country, then call this function to get individual information for any given country.

Function GetWBCountry(countryCode As String, Optional language As lang = en, _
                      Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim countries As Object ' MSXML2.IXMLDOMNodeList
Dim country As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_" & countryCode & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/countries/" & countryCode, False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set countries = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 8 data points
  ReDim tempString(1 To countries.Length, 1 To 8) 

  For i = 1 To countries.Length
    Set country = GetNode(countries, i)

    For j = 1 To GetChildNodes(country).Length
      tempString(i, j) = GetNode(country, j).nodeTypedValue
    Next j
  Next i

  GetWBCountry = tempString

End Function

Sample Usage

This sample procedure will return the country information for Brazil …

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBCountry("BR")
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

and this sample procedure will get all countries and then iterate through each one.

Sub tst2()

Dim countries() As String
Dim country() As String
Dim i As Long, j As Long, k As Long

  ' get all countries
  countries = GetWBCountries

  For i = LBound(countries) To UBound(countries)
    ' get each country
    country = GetWBCountry(countries(i,1))

    ' loop and print each country
    For j = LBound(country) To UBound(country)
      For k = LBound(country, 2) To UBound(country, 2)
        Debug.Print country(j, k)
      Next k
    Next j
  Next i
End Sub

Of course this is just an example of how the two functions relate; you could simply iterate through the results of GetWBCountries to get the same output.

Filter Countries by Income Level

We can filter countries by income level, as determined by the World Bank. Instead of creating a separate function to extract income level, I simply ran the query and created an Enum section for them. Here is the income level query response:

<?xml version="1.0" encoding="utf-8" ?>
<wb:incomeLevels xmlns:wb="http://www.worldbank.org" page="1" pages="1" per_page="50" total="7">
  <wb:incomeLevel id="NA">Aggregates</wb:incomeLevel>
  <wb:incomeLevel id="NOC">High income: nonOECD</wb:incomeLevel>
  <wb:incomeLevel id="OEC">High income: OECD</wb:incomeLevel>
  <wb:incomeLevel id="LIC">Low income</wb:incomeLevel>
  <wb:incomeLevel id="LMC">Lower middle income</wb:incomeLevel>
  <wb:incomeLevel id="NWB">NA</wb:incomeLevel>
  <wb:incomeLevel id="UMC">Upper middle income</wb:incomeLevel>
</wb:incomeLevels>

This function will return all countries that match the income level specified.

Function GetWBCountriesByIncomeLevel(incomeLevel As incomeLevel, _
                                     Optional language As lang = en, _
                                     Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim countries As Object ' MSXML2.IXMLDOMNodeList
Dim country As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long
Dim ic As String

Const XML_FILE_EXTENSION As String = ".xml"

  ic = GetIncomeLevel(incomeLevel)
  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_countriesbyincomelevel" & ic & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/countries?incomeLevel=" & _
                   ic & "&per_page=500", False
      .Send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set countries = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 8 data points
  ReDim tempString(1 To countries.Length, 1 To 8) 

  For i = 1 To countries.Length
    Set country = GetNode(countries, i)

    For j = 1 To GetChildNodes(country).Length
      tempString(i, j) = GetNode(country, j).nodeTypedValue
    Next j
  Next i

  GetWBCountriesByIncomeLevel = tempString

End Function

Sample Usage

This procedure returns all the countries classified as "Low income" by The World Bank.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBCountriesByIncomeLevel(LIC)
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Using a similar technique as above, we can filter countries by income level, then return each country separately.

Sub tst2()

Dim countries() As String
Dim country() As String
Dim i As Long, j As Long, k As Long

  ' get all countries
  countries = GetWBCountriesByIncomeLevel(LIC)

  For i = LBound(countries) To UBound(countries)
    ' get each country
    country = GetWBCountry(countries(i,1))

    ' loop and print each country
    For j = LBound(country) To UBound(country)
      For k = LBound(country, 2) To UBound(country, 2)
        Debug.Print country(j, k)
      Next k
    Next j
  Next i
End Sub

Filter Countries by Lending Type

We can also filter countries by lending type. The World Bank has a couple of different lending types, each corresponding to the type of financing available to a given country. Here's the query response for lending types:

<?xml version="1.0" encoding="utf-8" ?>
<wb:lendingTypes xmlns:wb="http://www.worldbank.org" page="1" pages="1" per_page="50" total="6">
  <wb:lendingType id="AGG">Aggregates</wb:lendingType>
  <wb:lendingType id="IBD">IBRD only</wb:lendingType>
  <wb:lendingType id="IDB">IDA blend</wb:lendingType>
  <wb:lendingType id="IDX">IDA only</wb:lendingType>
  <wb:lendingType id="NWB">NA</wb:lendingType>
  <wb:lendingType id="NC">Not classified</wb:lendingType>
</wb:lendingTypes>

As with income levels, I chose to create a Enum rather than query The World Bank for their lending types. When you enter the function you'll have to select one of the lending types, and the translator functions will convert them to a String for the API.

This function will return The World Bank-listed countries filtered by a given lending type.

Function GetWBCountriesByLendingType(lendingType As lendingType, _
                                     Optional language As lang = en, _
                                     Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim countries As Object ' MSXML2.IXMLDOMNodeList
Dim country As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long
Dim lt As String

Const XML_FILE_EXTENSION As String = ".xml"

  lt = GetLendingType(lendingType)
  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_countriesbylendingtype" & lt & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/countries?lendingType=" & _
                   lt & "&per_page=500", False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set countries = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 8 data points
  ReDim tempString(1 To countries.Length, 1 To 8) 

  For i = 1 To countries.Length
    Set country = GetNode(countries, i)

    For j = 1 To GetChildNodes(country).Length
      tempString(i, j) = GetNode(country, j).nodeTypedValue
    Next j
  Next i

  GetWBCountriesByLendingType = tempString

End Function

Sample Usage

This procedure will return the list of countries for which IDA-only financing is available.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBCountriesByLendingType(IDX)
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Filter Countries by Both Income Level and Lending Type

Turns out we can further filter countries by income level AND lending type. All we need to do is specify both when calling the API.

This function takes both income level and lending type and returns only those countries that match both.

Function GetWBCountriesByIncomeLevelAndLendingType(incomeLevel As incomeLevel, _
                                                   lendingType As lendingType, _
                                                   Optional language As lang = en, _
                                                   Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim countries As Object ' MSXML2.IXMLDOMNodeList
Dim country As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long
Dim ic As String
Dim lt As String

Const XML_FILE_EXTENSION As String = ".xml"

  ic = GetIncomeLevel(incomeLevel)
  lt = GetLendingType(lendingType)

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_countriesbyincomelevelandlendingtype" & ic & lt & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/countries?lendingType=" & _
                   lt & "&incomeLevel=" & ic & "&per_page=500", False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set countries = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 8 data points
  ReDim tempString(1 To countries.Length, 1 To 8) 

  For i = 1 To countries.Length
    Set country = GetNode(countries, i)

    For j = 1 To GetChildNodes(country).Length
      tempString(i, j) = GetNode(country, j).nodeTypedValue
    Next j
  Next i

  GetWBCountriesByIncomeLevelAndLendingType = tempString

End Function

Sample Usage

The following sample procedure returns all lower middle income countries available for IDA-only funding.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBCountriesByIncomeLevelAndLendingType(LMC, IDX)
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

List all World Bank Indicators

The following information is available for each Indicator:

  • Code
  • Name
  • Source
  • Description
  • Organization
  • Topics

Because of the odd way The World Bank returns data, it must be parsed slightly differently (see Documentation Best Practices for Web APIs for further discussion). The Indicator ID is an attribute of the indicator tag, so it has to be parsed separately from the other nodes.

Function GetWBIndicators(Optional language As lang = en, _
                         Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim indicators As Object ' MSXML2.IXMLDOMNodeList
Dim indicator As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_indicators" & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/indicators?per_page=2000", False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set indicators = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 6 data points
  ReDim tempString(1 To indicators.Length, 1 To 6)

  For i = 1 To indicators.Length
    Set indicator = GetNode(indicators, i)

    ' get first array value from id attribute of parent tag
    tempString(i, 1) = indicator.Attributes.getNamedItem("id").nodeTypedValue

    For j = 2 To 6
      tempString(i, j) = GetNode(indicator, j - 1).nodeTypedValue
    Next j
  Next i

  GetWBIndicators = tempString

End Function

Sample Usage

This sample procedure returns all available indicators.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBIndicators
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Return individual indicator

Just like the country function, this function returns an individual indicator. Run GetWBIndicators first to get all available indicators, then run the function below to return information about an individual indicator. For example, you might have a listbox with all indicators, which when selected, will populate a textbox with the information about a given indicator.

Visit the Indicators page for more ways to return information from the API.

Function GetWBIndicator(indicatorName As String, Optional language As lang = en, _
                                  Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim indicators As Object ' MSXML2.IXMLDOMNodeList
Dim indicator As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_indicator" & indicatorName & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/indicators/" & indicatorName, False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set indicators = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 6 data points
  ReDim tempString(1 To indicators.Length, 1 To 6)

  For i = 1 To indicators.Length
    Set indicator = GetNode(indicators, i)

    ' get first array value from id attribute of parent tag
    tempString(i, 1) = indicator.Attributes.getNamedItem("id").nodeTypedValue

    For j = 2 To 6
      tempString(i, j) = GetNode(indicator, j - 1).nodeTypedValue
    Next j
  Next i

  GetWBIndicator = tempString

End Function

Sample Usage

The following sample procedure returns data available about the total population indicator.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBIndicator("SP.POP.TOTL")
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Return all indicators by country

This function will filter the indicators by country. Use the two or three letter country code abbreviation, which you can acquire by running the GetWBCountries function.

Function GetWBIndicatorsByCountry(country As String, Optional language As lang = en, _
                                  Optional forceRequery As Boolean = False) As String()
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim indicators As Object ' MSXML2.IXMLDOMNodeList
Dim indicator As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_indicatorbycountry" & country & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", baseURL & GetLang(language) & "/countries/" & country & "/indicators?per_page=2000", False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set indicators = GetChildNodes(xmlDocRoot)

  ' resize array
  ' each subnode has 6 data points
  ReDim tempString(1 To indicators.Length, 1 To 6)

  For i = 1 To indicators.Length
    Set indicator = GetNode(indicators, i)

    ' get first array value from id attribute of parent tag
    tempString(i, 1) = indicator.Attributes.getNamedItem("id").nodeTypedValue

    For j = 2 To 6
      tempString(i, j) = GetNode(indicator, j - 1).nodeTypedValue
    Next j
  Next i

  GetWBIndicatorsByCountry = tempString

End Function

Sample Usage

This sample procedure returns all the indicators available for the U.S.A.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBIndicatorsByCountry("US")
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Return individual indicator per country

This function will return the given indicator for a given country. Use the GetWBCountries function to get the list of country abbreviations and the GetWBIndicatorsByCountry function to return the available indicators for that country.

You may also specify a date range, to only return data from a given time period.

Function GetWBCountryIndicator(country As String, indicatorName As String, _
                               Optional dateRange As String, Optional language As lang = en, _
                               Optional forceRequery As Boolean = False) As String()
' use 'all' as country to return all countries
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim dataNodes As Object ' MSXML2.IXMLDOMNodeList
Dim dataNode As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long
Dim i As Long, j As Long
Dim URL As String

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "WB_countryindicator" & country & indicatorName & Replace(dateRange, ":", ".") & XML_FILE_EXTENSION

  ' create URL
  If Len(dateRange) > 0 Then
    URL = baseURL & GetLang(language) & "/countries/" & country & _
    "/indicators/" & indicatorName & "?per_page=2000&date=" & dateRange
  Else
    URL = baseURL & GetLang(language) & "/countries/" & country & _
    "/indicators/" & indicatorName & "?per_page=2000"
  End If

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", URL, False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get second level nodes
  Set dataNodes = GetChildNodes(xmlDocRoot)

  numRows = dataNodes.Length

  ' resize array
  ' there are four data points per node
  ReDim tempString(1 To numRows, 1 To 4)

  For i = 1 To numRows
    Set dataNode = GetNode(dataNodes, i)

    For j = 1 To GetChildNodes(dataNode).Length
      tempString(i, j) = GetNode(dataNode, j).nodeTypedValue
    Next j
  Next i

  GetWBCountryIndicator = tempString

End Function

Sample Usage

This procedure returns the indicators for Brazil's GDP in 2006.

Sub tst()

Dim results() As String
Dim i As Long, j As Long

  results = GetWBCountryIndicator("BR", "NY.GDP.MKTP.CD", "2006:2006")
  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i
End Sub

Enum Section

The following Enums are required for one or more of the above functions. Paste the following at the top of a standard module.

Public Enum lang
  en
  es
  fr
  ar
End Enum

Public Enum incomeLevel
  ' http://open.worldbank.org/incomeLevels
  NA
  NOC
  OEC
  LIC
  LMC
  NWB
  UMC
End Enum

Public Enum lendingType
  ' http://api.worldbank.org/lendingTypes
  AGG
  IBD
  IDB
  IDX
  NWB
  NC
End Enum

Enum Translators

The following functions are needed to convert the Enums into String variables for the API. Paste the following into a standard module.

Function GetLendingType(lendingType As lendingType) As String
  Select Case lendingType
    Case 0
      GetLendingType = "AGG"
    Case 1
      GetLendingType = "IBD"
    Case 2
      GetLendingType = "IDB"
    Case 3
      GetLendingType = "IDX"
    Case 4
      GetLendingType = "NWB"
    Case 5
      GetLendingType = "NC"
  End Select
End Function
Function GetIncomeLevel(incomeLevel As incomeLevel) As String
  Select Case incomeLevel
    Case 0
      GetIncomeLevel = "NA"
    Case 1
      GetIncomeLevel = "NOC"
    Case 2
      GetIncomeLevel = "OEC"
    Case 3
      GetIncomeLevel = "LIC"
    Case 4
      GetIncomeLevel = "LMC"
    Case 5
      GetIncomeLevel = "NWB"
    Case 6
      GetIncomeLevel = "UMC"
  End Select
End Function
Function GetLang(lang As lang) As String
  Select Case lang
    Case 0
      GetLang = "en"
    Case 1
      GetLang = "es"
    Case 2
      GetLang = "fr"
    Case 3
      GetLang = "ar"
  End Select
End Function

Site last updated: May 17, 2012

Random Data Generator