If you’ve ever wanted to build your own programs - a somewhat high probability if you’re reading this website - you may have wondered how systems talk to each other. It is one thing to build a VBA program to collect data from users in userforms or to automate the manipulation of spreadsheet data, but is it possible to automatically populate your spreadsheets, too?
Of course it is! APIs, or Application Programming Interfaces, are extremely popular and allow one system to talk to another over the internet. If you want to programmatically capture data or even make automated decisions and send the results to another system, you can probably use an API to accomplish that goal. If a relevant API exists, it’s a much more efficient means of collecting data than using VBA to scrape webpages.
Note this tutorial applies many different VBA skills, the explanations of which fall outside the scope of this article. This is one of the most advanced tutorials on this site, and you may need to experiment and fill in some gaps on your own. Don’t let that discourage you, though. APIs are an incredibly powerful tool and the time invested to master them is worth it.
- API Basics
- Pros and Cons of Using VBA
- Setting Up
- Determining the Variables
- Initiating the Call
- Authentication
- Security
- Conclusion
API Basics
APIs are a standard method for your system to talk to another system somewhere on the internet. This is done through HTTP requests, just like a web browser. In fact, many open APIs can be queried directly through a web browser, which can be a useful way to test that you are querying the right URL.
Types of APIs
There are two main types of API: public/open and authenticated. The former can be used by anyone without an account, while the latter requires some kind of account. Accounts may be paid or free, but you need to register in some way. After registering, you can access your API key, which you use for authentication. Free services may still require an account simply as a minimal layer of security, helping them avoid being bombarded with requests during an attack.
Types of API Calls
There are also two common types of API calls or actions: GET and POST. The former is designed to retrieve information, such as the latest stock prices. The latter is designed to make server-side changes or initiate server-side actions, such as entering an order to buy 100 shares of a stock. This post will deal only with GETs, but I will be writing a POST article soon, as they are also very important. (If you sign up for the newsletter, you won’t miss it).
Naturally, POST requests almost always require authentication, since a POST request asks the server to take an action, and the server needs to know to which account that action applies. GET requests can be open or authenticated.
Endpoints and Parameters
Endpoints are the addresses to which one should direct the API call. For example, if this website had an API (it doesn’t), a potential account endpoint might look something like
Not all endpoints require parameters, though. An endpoint whose sole function is to return a list of available foreign currencies may not have any parameters at all, while an endpoint designed to return detailed company information will probably require at least a company parameter. Otherwise it might return thousands of companies with hundreds of pieces of information each, unnecessarily burdening the network with extraneous information.
Pros and Cons of Using VBA with APIs
To be blunt, I usually don’t use VBA for most of my API projects. API responses tend to be in JSON format, and there is no built-in JSON library (reference in VBA-speak) for VBA, which means you have to either use a third-party one or build your own parsing function. Microsoft also has a tendency to forget about VBA in documentation, meaning you might have to do some trial-and-error on your own before something works, which can be incredibly frustrating.
Moreover, most API libraries and wrappers are not written in VBA. Wrappers are code that makes using the API easy, written either by third-parties or by the API provider itself. Wrappers will be in Python, C#, or another popular language, but usually not VBA. That means you may have to do a lot of design work on your own just to get functioning requests before you even start to think about how your program will use the data it receives. It also means you will need to understand how the system works on a fundamental level, because you won’t have the crutch of wrappers and libraries to make interaction easier. A lot of people - myself included - choose Python or another popular language for their API needs for these very reasons. Don’t know Python? We have a whole series of Python tutorials to get you started.
Not all is doom and gloom for VBA and APIs, though. Perhaps the predominant reason to use VBA is that the language is native to Excel, and Excel has very powerful visualization and data analysis tools. Its spreadsheet design is familiar to almost every office worker, too, so coworkers or friends can easily use the programs you create. PowerPoint and other Microsoft Office applications use VBA as well, so if the final product will heavily use those applications, VBA might beat out languages like Python, which would require a lot of work to create nice visualizations or GUIs non-tech people can understand.
Setting Up
Alright, let’s get to some actual code. For this section, I am going to use IEX’s open API, which is available from the stem
Before you can begin anything, we need to reference the VBA library Microsoft XML, v6.0. Without this, you would need to build all the network and operating system backend code to make the requests. Fortunately Microsoft has already done that for us.
Once you add the reference, your skeleton should look something like this:
Sub unauth_get_request_skeleton()
'Add a reference to Microsoft XML v6.0 via Tools > References
Dim apiURL, requestString, ticker, endpoint, reqType, params As String
Dim request As MSXML2.ServerXMLHTTP60
apiURL = "https://api.iextrading.com/1.0/"
End SubSide note: You can use MSXML2.XMLHTTP60 instead of the server version, but you may have issues with caching. The Server version will make GET requests over the wire (over the internet) every time, whereas the regular version may only check your local cache and never send data over the wire. That means you may not receive fresh data from the service on every request.
Determining the Variables
Every API has its own documentation, and you will need to read your chosen API’s docs before you can do anything. IEX is relatively straightforward and mostly uses concatenation to build the query URL.
Let’s say you want to receive the last price paid for a set of particular stocks on IEX’s platform. To get such information, look under the IEX Market Data endpoint and the Last header (here). It seems you should hit the /tops/last endpoint. You should specify the symbols you want, too, as parameters.
Let’s start building out the request. Our variables should look like this:
apiURL = "https://api.iextrading.com/1.0/"
endpoint = "tops/last"
params = "symbols="
tickers = "MSFT,AAPL,AMZN"
requestString = apiURL & endpoint & "?" & params & tickersWhen you build your requests, you’ll need to decide the most efficient method for you, but I like to add forward slashes / directly to the variables so you don’t forget when you’re ready to concatenate them.
Notice the ? before
Initiating the Call
We should now have a string that is equal to
https://api.iextrading.com/1.0/tops/last?symbols=MSFT,AAPL,AMZNIf you type this into a web browser, you should receive a JSON-structured response. Tip: FireFox automatically displays JSON in an easy-to-read format, while many other browsers don’t.
Of course, this is a tutorial for VBA, so how do we send this information via VBA? Add these lines of code to open, send off, and receive the request:
Set request = New ServerXMLHTTP60 'creates the object
request.Open "GET", requestString, False 'populates object fields
request.send 'actually sends the request over the wire (internet)
'debug.print request.responseText 'uncomment this to view the unparsed response text in the immediate window
request.abort 'although not necessary, this aborts the open GET connectionMake powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
The ServerXMLHTTP60 object has many parts. The .Open method sets up the essentials. I usually set the Async variable to false to avoid any timeout issues. Submit the connection using .send, and then you can read whatever comes back across the wire by accessing the .ResponseText property. The ResponseText can be a very long string, depending on what you’re querying. For our example here, it is short enough that I’ve reproduced my own result below (yours is almost certain to be different since we are working with live data here). You can check your own result by typing ?request.responseText in the Immediate Window. This is what I received while I was writing the tutorial:
[{"symbol":"MSFT","price":127.07,"size":700,"time":1557518525651},{"symbol":"AAPL","price":197.26,"size":100,"time":1557518399269},{"symbol":"AMZN","price":1889.4,"size":100,"time":1557518399032}]
It is always good form to parse up the JSON response with a dedicated function, as JSON is generally set up like a dictionary already. However, in some cases, it might be easier to simply apply some string manipulation functions, which we do later in the tutorial.
Once you open a GET connection, it’s good practice to close it. Although not strictly necessary, use the .abort method to return the open it again, though, or you’ll catch an error on the second run.
Authentication
So far we’ve only used unauthenticated APIs. Many APIs require authentication, and any API calls that are POSTs will likely require you log in. Otherwise the service won’t know to which account it should apply the request.
If you’ve used early binding and have Intellisense on, you may have noticed the two parameters request.Open statement. These are binary string representations of your username and password for the service.
Depending on the API, you might be able to use these variables, or you might need to set request headers. If you’re building a program that interacts with an API, it is very important to read and understand the documentation, because there are different ways of achieving the same result. Reading the documentation is doubly important for VBA programmers, as we suffer from a lack of libraries, documentation, and wrappers.
For the API service you’re using, find a menu item along the lines of Generate API Keys or Authentication. These should help you find your keys, as they’re not always the same as the credentials you would use through the service’s website for normal consumer interaction.
Using the built-in User and Password Parameters
If your API supports it, all you need to do is set the Open statement. According to the Microsoft documentation, these two parameters are not sent unless the service denies access. If your machine receives an Access Denied response, the .Open method will then send the username and password.
Moreover, if the service requires such logon credentials and these fields are omitted, a native COM popup should appear, asking for logon credentials.
Using Request Headers
Not all services will accept the user/password combination. Many require request headers be set with the appropriate keys, usually an ID key and a secret key.
The headers are part of the MSXML2.ServerXMLHTTP60 object, so you set them with a method. The first part is the name of the header and the second part is the value of the header.
Let’s say the service has two keys, termed
id_header_name = "logon-id-key"
secret_header_name = "secret-pass-key"
id_key = "john.smith55"
pass = "SKjdfli23nmvfklj23lkjasklj3KLJDflk2j3r3"
request.setRequestHeader id_header_name, id_key
request.setRequestHeader secret_header_name, passHere, the headers are sent to the server along with the associated username and password. The server reads the header, understands it’s the ID or the password, and makes sure the value matches its records.
The IEX API is open, so you wouldn’t need to authenticate, and the auth data will just be ignored by the API server. I’m including it to show you how it might look if IEX did require authentication. The final code block, including data extraction, would look something like this:
Sub full_auth_with_parsing()
'Add a reference to Microsoft XML v6.0 via Tools > References
Dim apiURL As String, requestString As String, ticker As String, endpoint As String, reqType As String, params As String
Dim id_header_name As String, id_key As String, secret_header_name As String, secret_key As String
Dim request As MSXML2.ServerXMLHTTP60
Dim prices As Variant
Dim i As Integer
id_header_name = "logon-id-key"
secret_header_name = "secret-pass-key"
id_key = "john.smith55"
secret_key = "SKjdfli23nmvfklj23lkjasklj3KLJDflk2j3r3"
apiURL = "https://api.iextrading.com/1.0/"
endpoint = "tops/last"
params = "symbols="
tickers = "MSFT,AAPL,AMZN"
requestString = apiURL & endpoint & "?" & params & tickers
Set request = New ServerXMLHTTP60
request.Open "GET", requestString, False
request.setRequestHeader id_header_name, id_key
request.setRequestHeader secret_header_name, secret_key
request.send
'-----
'do stuff with data. Here's an example to extract prices (remove in production):
prices = Split(request.responseText, "price")
For i = 1 To UBound(prices)
prices(i) = Mid(prices(i), InStr(prices(i), ":") + 1, InStr(prices(i), ",") - InStr(prices(i), ":") - 1)
Debug.Print prices(i)
Next i
'-----
request.abort
End SubThere are several third-party JSON parsers for VBA designed to convert the ResponseText from your GET command to a VBA Dictionary object. JSON is actually set up as a dictionary, so using the VBA Dictionary object is an efficient way to store its contents. However, these third-party solutions often require importing custom classes, so we’re not going to present them here.
Some people also use MSScriptControl.ScriptControl to process their JSON responses, but in a pinch, you can always extract the data you need using basic string manipulation functions rather than key-item pairs. In the macro above, I used a combination of VBA Split and VBA Mid functions to extract the prices from each stock ticker item in the API response. Here’s the output:
127.07 197.26 1889.4
Pay attention to the data type! These are going to be of the String data type, since they’re being extracted from strings and we applied string manipulation functions. To do calculations you’ll want to convert them with a function like CDec or CDbl first.
Security
It is important that your requests are routed through HTTPS. If you route through HTTP, your request headers and any data you send might be sent in unsecured plaintext. If you are only making unauthenticated GET calls, this probably won’t be an issue. However, if you make any calls that require you to log in, or you make POST calls, you don’t want your information flying around cyberspace unencrypted.
Fortunately, using HTTPS in the API URL will probably be sufficient, since connecting to the endpoint will require the connection to be encrypted, anyway.
Please do note that I am not a security professional, and I didn’t write the Microsoft libraries. If you need to be absolutely sure, you can try a little sleuthing via network traffic analysis tools, which can tell you all the data that is coming in and going out. Just be aware that most people’s computers have many background connections constantly shipping data off, so you’ll probably need to turn them off to avoid being inundated with connection information.
Conclusion
APIs are designed to make systems talk to each other. They are a very powerful tool, and they really will take your programs to the next level by incorporating live, remote data. This tutorial looked at VBA GET requests, which only receive information. This can be extremely useful for gathering data, like financial or weather data, and processing it. To start out, you can simply paste your request strings directly into a browser’s URL bar and read the responses directly on the page. It will help you get a feel for writing API request strings and for dealing with the responses.
You may be at a disadvantage using VBA due to a lack of libraries and wrapper support, but it can certainly be rewarding, especially when coupled with the expansive suite of analysis and visualization tools available in Excel.
Finally, APIs sit at the intersection of several skills and topics, including networking, library references, complex objects, authentication, security, and third-party functions. You must also read code from other developers, since this code is embedded in the API’s documentation. You should definitely read the documentation for your particular API before you even start writing your program. So, yes, implementing APIs securely and efficiently can be complex and at times frustrating ( especially in VBA!), but a well-functioning implementation is just as rewarding and fulfilling as it was difficult and frustrating to develop in the first place.
If you’re serious about writing macros, subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.
