Integrate External Data Sources with APIs with Excel VBA

The example will demonstrate how to connect to a web API, fetch data, and display it in Excel.

Integrating External Data Sources with APIs in Excel VBA

Introduction

To integrate external data sources into Excel using VBA (Visual Basic for Applications), we commonly rely on APIs (Application Programming Interfaces). APIs allow applications to communicate with each other. In the context of Excel, APIs enable you to pull data from external sources (e.g., weather data, stock market data, social media, etc.) directly into Excel, automating the process and making data management much easier.

In this example, we’ll show how to use VBA to connect to a RESTful API, send a request, retrieve data in JSON format, and process that data into a readable format in Excel.

Steps to Integrate API with VBA

1. Setting up a reference to Microsoft XML, v6.0 library: Before interacting with an API using VBA, we need to set up a reference to the Microsoft XML library, which allows us to make HTTP requests.
   • Open the Visual Basic for Applications (VBA) Editor by pressing Alt + F11.
   • Go to Tools > References.
   • Look for Microsoft XML, v6.0 and check it (this is required to make HTTP requests in VBA).
   • If it’s not available, you can select the latest version of Microsoft XML.
2. API Request Process:
   • Send a request to the API endpoint using the XMLHttpRequest object.
   • Handle the response from the API, which is typically in JSON format.
   • Parse the JSON response and extract relevant information.
   • Display the data in Excel.

VBA Code Example:

Let’s say we want to retrieve data from a public API (e.g., a weather API, stock price API, or a cryptocurrency API). For this example, I’ll use the OpenWeather API for weather data.

Steps to set up and get weather data using OpenWeather API:

1. Sign up on the OpenWeather website and get an API key.
2. The API endpoint to get the current weather is:
3. http://api.openweathermap.org/data/2.5/weather?q={city_name}&appid={API_KEY}

VBA Code:

Sub GetWeatherData()
    ' Variables for API request and response
    Dim http As Object
    Dim url As String
    Dim jsonResponse As String
    Dim json As Object
    Dim cityName As String
    Dim apiKey As String
    Dim temperature As Double
    Dim weatherDescription As String   
    ' Specify city name and API Key
    cityName = "London" ' You can change this to any city name
    apiKey = "your_api_key_here" ' Replace with your actual OpenWeather API Key   
    ' API URL for weather data
    url = "http://api.openweathermap.org/data/2.5/weather?q=" & cityName & "&appid=" & apiKey & "&units=metric"   
    ' Create the XMLHTTP object to send the request
    Set http = CreateObject("MSXML2.XMLHTTP")   
    ' Open the HTTP request (GET method)
    http.Open "GET", url, False   
    ' Send the request
    http.Send   
    ' Get the response from the API
    jsonResponse = http.responseText   
    ' Parse the JSON response
    Set json = JsonConverter.ParseJson(jsonResponse)   
    ' Extract data from the JSON object
    temperature = json("main")("temp")
    weatherDescription = json("weather")(1)("description")  
    ' Output the data into Excel (assuming you want to display it in the first row)
    Range("A1").Value = "City: " & cityName
    Range("A2").Value = "Temperature: " & temperature & " °C"
    Range("A3").Value = "Weather: " & weatherDescription
End Sub

Explanation of the Code:

1. Variables:
   • http: This is the XMLHttpRequest object that allows us to make HTTP requests.
   • url: This stores the API endpoint (including the city name and API key).
   • jsonResponse: Stores the raw JSON response received from the API.
   • json: An object that will hold the parsed JSON response.
   • cityName and apiKey: Stores the city name and your API key respectively.
   • temperature, weatherDescription: Variables to store the parsed temperature and weather description.
2. API Request:
   • We build the URL by concatenating the base URL of the API with the query parameters (cityName, apiKey, and units=metric for temperature in Celsius).
   • We use the MSXML2.XMLHTTP object to send an HTTP GET request to the API.
   • The Send method sends the request, and responseText receives the raw response.
3. Parsing the JSON:
   • The JsonConverter.ParseJson function (which requires a third-party library to be installed) is used to parse the JSON response into a VBA dictionary object.
   • You can download the JSON Converter for VBA here and add it to your project to handle JSON parsing.
4. Extracting Data:
   • The temperature and weather description are extracted from the JSON response using the appropriate keys (json(« main »)(« temp ») and json(« weather »)(1)(« description »)).
5. Displaying Data in Excel:
   • The weather data (city, temperature, weather description) is displayed in cells A1, A2, and A3.

Important Notes:

• Error Handling: Always include error handling in real-world applications. For instance, handle scenarios where the API might be down or if the data format changes.
• Rate Limits: Some APIs (like OpenWeather) have rate limits, so ensure you don’t exceed them.
• API Key Security: Be cautious with exposing your API key. You can store the key in a secure location or use environment variables to keep it safe.

Conclusion

By using VBA to interact with APIs, you can automatically pull data from external sources and display it directly in Excel. This process can be customized for any API and data type, making it a powerful tool for automating data retrieval tasks.