So far, these tutorials have mostly focused on building charts, reporting tools, and (very rudimentary) automated trading systems. For this tutorial, we introduce a UserForm object with VBA to create a trade ticket which sends market orders to the OpenAPI for the selected instrument. The basis of this tool is the search functionality developed in Tutorial 1, which is extended by adding a TRADE action on the rightmost side, and a subscription to the /trade/v1/messages/ endpoint at the bottom, which we will use to keep track of the trades that are sent to the OpenAPI.
Most of the process however takes place behind the scenes in VBA, which automatically picks up the instrument details, populates the trade ticket, adjusts the contents based on the user's input, and sends the trade to the OpenAPI. The finished product looks as follows:
For this spreadsheet to work correctly, a number of requirements need to be met:
- The instrument universe is limited to CFDs, Stocks, Bonds, FxSpot and Funds to simplify the trade ticket, which only sends market orders for now.
- Account information is loaded on a separate screen, which allows the user to choose the account he wishes to trade on in the main screen.
- The spreadsheet loads the trade ticket with the relevant instrument when the user clicks on a cell marked TRADE.
- Depending on the choices the user makes, the trade ticket updates the information on the screen and keeps track of the values that need to be sent to the OpenAPI. Buttons appear only when valid data is entered, and a warning will be shown when the user tries to enter non-sensible input.
- When the user clicks Place Market Order, VBA sends the order to the OpenAPI and shows the response in a separate popup, which in turn closes the trade ticket when confirmed.
- To keep track of the orders, a simple subscription function is used, which automatically updates with the latest trade messages.
As described above, most of the work is done within the trade ticket's VBA. which will be the main focus of this tutorial.
The VBA that drives the trade ticket consists of two parts:
- Keeping track of the user's actions and initializing the trade ticket with the correct information from the search results when the user hits TRADE.
- Serving the trade UserForm and sending the order to the OpenAPI.
The first part is relatively simple, and will be contained within the Trade worksheet. Key points in this script are:
- Every time a cell selection is changed, and the total number of selected cells equals 1, VBA checks whether the text in the cell is equal to "TRADE". Only then is the trade ticket launched.
- For convenience, the trade ticket is opened in the center of the Excel window.
- Required parameters (assettype and symbol) are pre-loaded.
- The title of the ticket is changed according to which instrument is selected.
Next up, the trade ticket itself. The first step is to put the panel together using the VBA Toolbox to add all required elements.
And, finally, the following VBA to make it behave correctly. The designed user flow through this script is as follows:
- After the user opens the ticket, the first choice to be made is whether to buy or sell the selected instrument. Depending on the user's choice, the UI will automatically disable the opposite of the user's choice and flips back and forward between the two when the user keeps clicking the buy and sell buttons.
- As soon as the user chooses the order direction, the Amount input box is displayed, together with a summary box labeled Order.
- The user now enters the required amount, which cannot be anything other than a number.
- The user clicks Place Market Order, which executes the OpenAPI call and checks to verify whether the order was received correctly.
- The returned message is shown in a separate box, which in turn closes the trade ticket when the user hits OK.