How to make API calls with  VBA

Hi there, hope you're doing good ! ^^

In today's tutorial we will see how we can use API with vba in order to populate our spreadsheet with real world values such as stock prices and so on. However, it is good to know that VBA is not the best language to do so and that nost APIs will be a lot easier to use with other coding languages such as Python so if you have the possibility I can only advise you to use another language such as Python and then populate your excel file with those values. Nonetheless, in some cases, like a company environment it could be too much time consuming or even to some extent impossible to get access to another language and that's when we need to make API call directly with VBA, so let's get down to it.

Before starting opening anything, it is important to know that we can encounter two sorts of APIs :

- public/Open Apis : where everyone can make a call and retrieve data

- Authenticated API : where we need to have an API key and authenticate ourselves to the API in order to be allowed to retrieve any info

So let's first approach the easier case, and see how we can retrieve info from an Open API. To do so, we will use the API of the iextrading website and create a basic stocks App allowing us to retrieve the live price of the following stocks :

• Apple (APPL)
• Microsoft (MSFT)
• Tesla (TSLA)
• Facebook (FB)
• Amazon (AMZN)
• Google (GOOGL)
• Twitter (TWTR)
• Intellia Therapeutics (NTLA)
• Editas Medicine (EDIT)
• CRISPR (CRSP)
• Qualcomm (QCOM)
• IBM (IBM)
• Nvidia (NVDA)
• Nasdaq ETF(NDAQ)
• S&P 500 ETF (SPY)
• Dow Jones ETF (DIA)

Alright, so first let's open a new excel file create the following shell for our little stock app price and save it with the ".xlsm" extension.

Once it's done open the vba IDE with shortcut alt+ F11. There create a new code module call it Stock_prices and copy the following code

Code explanation

Given the API doc for the iextrading API we have to pass the following API call if we want to retrieve the stock price for our stocks :

• https://api.iextrading.com/1.0/tops/last?symbols=AAPL,MSFT,TSLA,FB,AMZN,GOOGL,TWTR,NTLA,EDIT,CRSP,QCOM,IBM,NVDA,NDAQ,SPY,DIA

=> You can try it out for yourself by copying and pasting this url link into your web browser.

That's why we define each part of this URL into different variables that we link together in the requestString Variable in order to have more flexibility if we want to change one stock or else.

Then in order to be able to make our API call based on our requestString variable we need to use a ServerXMLHTTP60 Microsoft object populate it and then send the request.

=> You can find more info about this object here https://support.microsoft.com/en-ca/help/290761/frequently-asked-questions-about-serverxmlhttp

Finally, once we get our response saved by the ServerXMLHTTP60 object in request.responseText we just format it so we can retrieve only the info that we need in our stock application.

Alright so now before being able to run our macro we have to import the Microsoft XML, v6.0 library. To do so, in your vba IDE go into Tools, reference and search and check the Microsoft XML, v6.0 library.

Now that we're correctly set up the only thing left is to press f5 to run our macro and normally your excel spreadsheet should be populated with the latest values for our stocks tickers as below :

Alright so that's it for the Open API case. ^^

Let's move on now  to the authenticated API case. Here we will use the OpenWeather and it's authenticated API to create a basic weather API giving us the weather in the following cities :

- London
- Paris
- Roma
- Dubai
- Honolulu
- Singapore
- Zurich

So first go over the Openweather website and create a free account. Once it's done, go back into our excel file, create a new spreadsheet and copy the below shell for our app.

Now, back in the vba IDE, create a new code module called Weather and copy the following scripts in it :

Code Explanation :

As you can see the core structure of the code is largely similar to the previous case, the only difference is in the URL where aside from the difference due to the APIs nature we also have to specify our user key API.

=> In the code I used my key API, so normally it should work also fine on your side. However if you want to use your personal key API, go bakc to the Openweather website there, click on my API Keys in the drop down list menu under your username.

There copy your Key Api and paste it in our vba code in place of my API key.

Alright, now the last remaining thing to do is to test our macro, so press F5 and normally you should end up with a result similar to the one below :

And that's it we now have two basic app using the two possible forms of API. So congrats to you and as always don't hesitate to play around, and explore a bit further those two API to create more complex and interesting apps.

Full code can be found here

Have a good day and happy coding ! ✌️