For Excel Persons - How to Pull Market Data with VBA

21 Dec 2018

(Writer’s Update: the original post used MSXML2.XMLHTTP60 to send the requests. However, this implementation may cause static cached data to be returned, which is unacceptable in dynamic environments like stock trading. Therefore, I suggest using MSXML2.ServerXMLHTTP60, which should solve the caching issue. I updated this article to reflect that minor but important change.)

We already looked at what types of data are available through Alpaca’s API. Here, let’s walk through an example using real code of VBA.

Know Nothing about Coding? Use VBA!

If you know nothing about coding but have used Excel all your academic and professional life (I think most of us, yes), the VBA solution is a good way to dive into automated trading on a familiar platform without learning different development environments and software.

By the way, my areas of expertise are Python and VBA. The former already has a large community surrounding its development, complete with a full library, so it makes even more sense that we will work through VBA in this post. Unfortunately, the .NET library is yet to be packaged into a VBA-referenceable download, so we will only focus on the unauthenticated endpoint to avoid implementing security on our own.

Note: as more features come online through the unauthenticated endpoint I will publish more tutorial-style articles. Once a full VBA-referenceable library is released, I will also write a comprehensive guide to its use.

Setup

In order to send internet requests in VBA, you’ll need to use some objects not normally available in VBA. You can opt for so-called late binding, but I always found it helpful to learn by Intellisense, VBA’s on-the-fly suggestion tool for object methods and properties. In order to enable Intellisense on the not-normally-included objects, you need to make the reference to them explicit.

In the VB editor (VBE), navigate to Tools > References, and then select Microsoft XML, v6.0. Now you can dimensionalize your request object as MSXML2.ServerXMLHTTP60. Let’s use req as the name of the object (for request) and dimensionalize all of our other variables. We should have this so far:

Sub unauthenicated_test()
Dim req As MSXML2.ServerXMLHTTP60
Dim apiURL, queryString, timeframe, symbols, limit, start As String
Set req = New MSXML2.ServerXMLHTTP60
End unauthenticated_test()

Since this is an open API, that’s all we need to setup. No credentials, no security, no complications.

Building the URL and Sending the Request

Since this is an open RESTful API, we just need to build the string that makes the query. You could put this same string right into a browser URL address bar and get the response in a browser window (nicely formatted, too).

The only required parameters are the timeframe and symbol set, but I included some extra options so you can get the same response as in this tutorial.

apiURL = “https://data.alpaca.markets/v1/bars/"
timeframe = “1Min”
symbols = “?symbols=AAPL,MSFT”
limit = “&limit=3”
start = “&start=2018–12–13T13:30:00–06:00”

Note the question mark in symbols and the ampersands in limit and start. You don’t need to add these, but it makes life much easier later when concatenating everything. Moreover, notice the capitalization. This is important! If you use “aapl” instead of “AAPL” for the request, you will receive a blank response. Furthermore, make sure your start string is in ISO8601 format.

Now, we need to build the final string and use the .Open method on our req object:

queryString = timeframe &; symbols & limit & start
req.Open “GET”, apiURL & queryString, False

The API URL and query string should read https://data.alpaca.markets/v1/bars/1Min?symbols=AAPL,MSFT&limit=3&start=2018-12-13T13:30:00-06:00 before sending.

GET refers to the type of request. Since we are retrieving information, we can use GET. We also need to set the destination URL as the concatenation of the base API URL and the query string. We will set the last variable to False so the program will wait for a response before moving on to the next line.

Again, this is an open API, so we don’t need to set any headers. The next step is just to send, like so req.Send. Since the query string is written directly into the URL, we don’t need to worry about writing the request body.

Handling the Response

We should get a string in return, which is actually a JSON-formatted string. It will look something like this:

{“ AAPL”:[{”t” :1544821020,”o” :165.785,”h” :165.835,”l” :165.71,”c” :165.72,”v” :7933},{”t” :1544821080,”o” :165.74,”h” :165.82,”l” :165.54,”c” :165.55,”v” :18507},{”t” :1544821140,”o” :165.54,”h” :165.62,”l” :165.38,”c” :165.4,”v” :41052}
],”MSFT”:[{”t” :1544821020,”o” :106.29,”h” :106.34,”l” :106.255,”c” :106.255,”v” :10695},{”t” :1544821080,”o” :106.29,”h” :106.32,”l” :106.05,”c” :106.05,”v” :14181},{”t” :1544821140,”o” :106.06,”h” :106.13,”l” :105.98,”c” :106.08,”v” :29039
}]}

This is literally just a string of text. Unfortunately, there is no nice built-in library in VBA to easily parse JSON data, so you will need to find one online somewhere (there are many) or build your own parsing function. To be honest, you might just want to use Python for making the requests, as the JSON-parsing libraries are extensive and make life much easier. Then you can write the result to an Excel file and run VBA code for processing.

I like VBA, but it certainly lacks a nice way to chop up JSON strings. Of course, if you don’t want to deal with potential errors arising from the Python-Excel-VBA interfacing, you can do everything via VBA. This is particularly useful for people who already have extensive systems built in VBA and like the powerful charting features of Excel, which makes visualizations much nicer and easier than trying to rebuild all the visuals in a Python library like matplotlib. In fact, I would argue the main reason for using VBA for the API is to quickly take advantage of Excel’s visualization features. The spreadsheet GUI is also quite intuitive, and it will certainly be more familiar to those who have never touched any code before.

You can find more information on the bars parameters in the documentation.

So we’ve built our first, very basic API call in VBA. If you know nothing about coding but have used Excel all your academic and professional life, the VBA solution is a good way to dive into automated trading on a familiar platform without learning different development environments and software.

Technology and services are offered by AlpacaDB, Inc. Brokerage services are provided by Alpaca Securities LLC (alpaca.markets), member FINRA/SIPC. Alpaca Securities LLC is a wholly-owned subsidiary of AlpacaDB, Inc.

You can find us @AlpacaHQ, if you use twitter.

Use Cases Market Data API