Using VBA and Excel to Make Authenticated Requests for Alpaca’s Trading API

GET Requests are limited to receiving information from a server—they’re unable to change anything on the server side. That means you can do things like query positions, check individual stock information, and see how much money your account has, but it’s not possible to place trades. To make changes on the server side — and therefore place orders into the system — you’ll need to use POST requests. Another function that asks the server to do something (other than return info) is a DELETE request. This tutorial is focused on getting you up and running with POST and DELETE requests.

If you want to focus on GET requests (or just want a refresher), you can go back to the GET tutorial here:

Using VBA and Excel to Make Authenticated Requests for Alpaca’s Trading API

The Skeleton

Use the same skeleton as in the GET tutorial, albeit with a couple of tweaks. The important thing to remember, which is not immediately obvious from the skeleton, is that you need to make the reference to Microsoft XML, v6.0. If you don’t enable the reference, you’ll need to use late binding and won’t be able to see any of the Intellisense. For learning purposes, use Intellisense and make all variables early-bound.

The skeleton:

Sub authenticated_post_order()
Dim req As MSXML2.ServerXMLHTTP60
Dim key_id, key_header_name, secret_key, secret_header_name, liveURL, paperURL As String
Set req = New MSXML2.ServerXMLHTTP60
key_id = “PKFK502DMES8EA63U1HG”
key_header_name = “APCA-API-KEY-ID”
secret_key = “eRN3sZ9PWQ2hLZB3h8jy/JhvVLJutgejWOn0BdTv”
secret_header_name = “APCA-API-SECRET-KEY”
liveURL = “https://api.alpaca.markets"
paperURL = “https://paper-api.alpaca.markets"
req.Open “POST”, paperURL & “/v1/orders/”, False
req.setRequestHeader key_header_name, key_id
req.setRequestHeader secret_header_name, secret_key
order_string = “”
req.send order_string
End Sub

This skeleton has two important and one unimportant changes from the GET version. First, the type of request is now POST. The second is that the orders endpoint is now hardcoded into the skeleton. In fact, at the time of this writing (February 2019), there is only one POST and one DELETE request available. They both should be directed to the orders endpoint, so that is what is in the skeleton. If you’re going to use this skeleton for GETs and POSTs that are not for orders, you’ll want to take out the hardcoded endpoint.

You’ll also need to change your API keys so you’re accessing your own account. If you don’t remember how to find your keys, please refer to the GET tutorial.

Placing an Order

This is the only POST request available for Alpaca right now, but it’s extremely important—it wouldn’t be possible to have any positions or orders without it! Next, simply transmit a JSON-formatted POST request and Alpaca’s servers will interpret the message, make the necessary changes on its side, and send your order to the market.

There are a number of required parameters you need when making requests. The key-value pairs the server will recognize are as follows:

• symbol — the ticket symbol of the asset you want to buy or sell
• qty — the number of shares
• side — buy or sell
• type — market, limit, stop, or stop_limit
• time_in_force — day, gtc (good til cancelled), or opg (placed at open)
• limit_price — required only for limit orders
• stop_price — required only for stop orders
• client_order_id — an optional unique ID for the order, created by you

The first three key values are pretty straight-forward. If you’ve traded or invested before, the fourth is intuitive as well. If you don’t know the different types of orders, you can learn more from the SEC. Alpaca also explains all the details of orders available on their system here.

For order lifespan, Alpaca offers the normal day, good till cancelled, and another less common one, the opg order. These are placed at the market open. They can be accepted, modified, and deleted up to 09:15, and they can be deleted until 09:28. After 09:28:00, the order cannot be cancelled.

The other notable parameter is client_order_id, which is a unique identifier that you can add to your request. If you generate identifiers internally, you can use this parameter to make trade matching easier for yourself between your system and Alpaca’s. Make sure you’re sending unique IDs, because Alpaca will check against the orders you’ve already sent and will return an error if the ID is already in use.

The JSON Object and Its Structure

As mentioned in the first tutorial in this VBA series, the language of VBA does not have a nice built-in library for JSON. It is rather unfortunate, since JSON is the overwhelming choice of data structuring for APIs. Luckily there are JSON parsers and libraries out there for free.

This is important because it’s time for you to send your own JSON-formatted string, which you’ll need to build yourself. VBA also does not do well with strings within strings, so this is going to look like a mess (sorry!). First, look at how a basic JSON object is structured before “cluttering it up”.

There is always a key and a value for JSON objects. It will be set up like this:

{key1:value1, key2:value2, …}

It’s relatively easy to see the structure here, so try building this in VBA.

Building the JSON string

You’ll need a relatively long string for the order, because of the volume of necessary information. Each key and each value also need to be sent to Alpaca enclosed in quotation marks so Alpaca recognizes everything as strings and the servers can parse it correctly.

VBA only has one way to mark strings, which is with double quotation marks "vba string". Single quotes are used to make comments `single quote comment. To make strings inside of strings, you’ll need to use double double quotes. That means to make a string within a string you’ll need this: "the next part is a ""string inside a string""(Contrast this with Python, which allows single and double quotes, making quotes within quotes much easier to write and to read. i.e."the next part is a 'string inside a string'").

So, if you want to make the JSON {"qty":"5"}, you’ll need to write it in VBA as

"{""qty"":""5""}"

It’s pretty ugly. Take a peek at the breakdown: the string starts with the curly brace {, then has another string inside it ""qty"", an unenclosed colon as part of the “original” string, another string with double double quotes for the ""5"", and finally a closing double quote after the ending curly brace.

So, as an example, in order to buy 5 shares of Microsoft with a limit price of $85.00 that is good until the close of the market today with a client-generated unique identifier of “my microsoft order”, your string should look like this:

order_string = "{""symbol"":""MSFT"",""qty"":""5"",""side"":""buy"",""type"":""limit"", ""limit_price"":""85.00"", ""time_in_force"":""day"", ""client_order_id"":""my microsoft order""}"

Okay. So, this is an ugly thing, but it gets worse. This is all hardcoded. In a real system, you’re going to want to put some kind of front-end on it, even if it’s just a regular VBA input box. No one wants to edit the code by hand every time they place a trade. Plus, if you’re automating trades based on signals, rewriting the code by hand every time defeats the purpose of said automation.

That means writing every variable and concatenating everything. It will look something like this:

Dim sysinput, quantinput as String
Dim sym, quant, side, type_order, limit_price, time_in_force As String
Dim sym_key, quant_key, side_key, type_order_key, limit_price_key, time_in_force_key As String
syminput = UCase(InputBox("Enter ticker"))
quantinput = CStr(InputBox("Enter number of shares"))
sym = """" & syminput & """"
quant = """" & quantinput & """"
sym_key = """symbol"":"
quant_key = ",""qty"":"
order_string = "{" & sym_key & sym & quant_key & quant & "}"

Note the UCase function, since you need to ensure that it is upper case (or you will get an unknown asset error). Similarly, you will need to ensure the quantinput is a string, not an integer. Also, note when we build the sym variable, we need four quotes on each side. This is because you’ll need to enclose the double double quotes within single double quotes, concatenate it to the input we received from the user, and end the string with double double quotes.

For some much-needed simplification, notice how the keys are written: the surrounding punctuation is hardcoded (because these don’t change). The keys are simple static strings, so it is much better to write the punctuation into them than trying to remind users to include the punctuation or trying to do it with concatenations in the final string (which is rather tortuous aesthetically and torturous mentally).

But if you set up everything correctly, you should just be able to use a series of ampersands and variable names to create the JSON for the order_string, just like you did above.

Successfully placed orders

If you’ve successfully placed the order, you will get a response like this example:

{“id”:”536df729–043d-4a86–9046–1e903718077a”,”client_order_id”:”my microsoft order”,”created_at”:”2019–02–20T18:38:25.130459488Z”,”updated_at”:”2019–02–20T18:38:25.146788558Z”,”submitted_at”:”2019–02–20T18:38:25.076480994Z”,”filled_at”:null,”expired_at”:null,”canceled_at”:null,”failed_at”:null,”asset_id”:”b6d1aa75–5c9c-4353-a305–9e2caa1925ab”,”symbol”:”MSFT”,”asset_class”:”us_equity”,”qty”:”5",”filled_qty”:”0",”filled_avg_price”:null,”order_type”:”limit”,”type”:”limit”,”side”:”buy”,”time_in_force”:”day”,”limit_price”:”85",”stop_price”:null,”status”:”new”}

Another JSON string with order details. If you want to check the status, you can make an order GET request.

Deleting

If you want to delete an order, you can check whether it is filled first or not with a GET. If it is filled, you can tell the user it is already filled (or yourself, if this system is only for yourself). You can send the DELETE request, and if the order is not cancellable, you’ll get a 422. But that does not say exactly why the error is occurring, so it is good to check with an order GET request.

Fortunately, if you want to cancel, you don’t need to build any JSON strings. Like many of the GET requests, you just need to hit the /v1/orders endpoint and send the order ID along with it. If your “HTTP action verb” is DELETE, the server understands that you want to delete the order ID attached to the end. The code snippet looks like this:

order_id = “536df729–043d-4a86–9046–1e903718077a”
req.Open “DELETE”, paperURL & “/v1/orders/” & order_id, False
req.send

Note that order_id matches the ID parameter from the order POST, not the client_order_id. Client IDs can contain spaces, which will not be properly sent in the DELETE.

Final Thoughts

You’ve done it! You can always come back anytime you want a basic way to authenticate HTTP requests to Alpaca’s system via VBA. You will need to find a way to parse all the JSON (I strongly recommend one of the freely available tools online), but once you get that working, you will be able to implement Excel’s beautiful visualization tools (honestly, that’s the only reason most do/would use VBA in place of another language with more robust API libraries). There are Python and a .NET libraries already developed by Alpaca, but they don’t have all the pretty charts and the built-in spreadsheet capabilities that VBA and Excel do!

Happy coding and trading :)

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.

Follow Automation Generation, a Medium’s publication created for developers/makers in trading and fintech.