7 - Creating a basic trade ticket

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:

Requirements

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.

VBA solution

The VBA that drives the trade ticket consists of two parts:

1. 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.
2. 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.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim uic As Long
    Dim assettype As String
    Dim desc As String
    Dim symbol As String
    Dim dir As String
    
    If Target.CountLarge = 1 Then 'if the selection is a single cell
    
        If Target.Text = "TRADE" Then 'when TRADE is clicked
            uic = Target.Offset(0, -4).Value  'assign UIC
            assettype = Target.Offset(0, -5).Value  'assign AssetType
            symbol = Target.Offset(0, -6).Value 'assign Symbol
            desc = Target.Offset(0, -3).Value 'assign description
            
            With Ticket 'launch trade ticket
                'load trade ticket in the center of the Excel window
                .StartUpPosition = 0
                .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
                .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
                
                'load parameters
                .Caption = "Trade:  " + desc
                .symbollabel.Caption = symbol
                .assettypelabel.Caption = assettype
            End With
            
            'display ticket
            Ticket.Show
            
        End If
    
    End If
    
End Sub

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:

1. 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.
2. As soon as the user chooses the order direction, the Amount input box is displayed, together with a summary box labeled Order.
3. The user now enters the required amount, which cannot be anything other than a number.
4. The user clicks Place Market Order, which executes the OpenAPI call and checks to verify whether the order was received correctly.
5. The returned message is shown in a separate box, which in turn closes the trade ticket when the user hits OK.

Private Sub buybutton_Click()
    
    'show direction
    orderlabel.Visible = True
    amounttext.Visible = True
    amountvalue.Visible = True
    ordertext.Visible = True
    
    'logic to toggle buy/sell direction
    If buybutton.Value = True Then
        sellbutton.Value = False
        dir = "BUY"
    End If
    
    If buybutton.Value = False Then
        sellbutton.Value = True
        dir = "SELL"
    End If
    
    'update direction text on ticket
    orderlabel.Caption = dir + "  " + CStr(amountvalue.Value) + "  " + symbollabel.Caption
    
End Sub

Private Sub sellbutton_Click()

    orderlabel.Visible = True
    amounttext.Visible = True
    amountvalue.Visible = True
    ordertext.Visible = True
    
    'logic to toggle buy/sell direction
    If sellbutton.Value = True Then
        buybutton.Value = False
        dir = "SELL"
    End If
    
    If sellbutton.Value = False Then
        buybutton.Value = True
        dir = "BUY"
    End If
    
    'update direction text on ticket
    orderlabel.Caption = dir + "  " + CStr(amountvalue.Value) + "  " + symbollabel.Caption

End Sub

Private Sub amountvalue_Change()
    'update direction text on ticket
    orderlabel.Caption = dir + "  " + CStr(amountvalue.Value) + "  " + symbollabel.Caption
    placeorder.Visible = True
    
    'check whether the input value is valid
    If Not IsNumeric(amountvalue.Value) Then
        If amountvalue.Value <> "" Then
            MsgBox "Please a number.", , "Error!"
            amountvalue.Value = 0
        End If
    End If
        
End Sub

Private Sub placeorder_Click()
        
    uic = ActiveCell.Offset(, -4)
    
    orderlabel.Caption = "Sending " & dir & " order.."
        
        body = "{" & _
            "'AccountKey':'" & [accountkey] & "', " & _
            "'Amount':" & amountvalue.Value & ", " & _
            "'AssetType':'" & assettypelabel.Caption & "', " & _
            "'BuySell':'" & dir & "', " & _
            "'Uic':" & uic & ", " & _
            "'OrderType':'Market', " & _
            "'OrderDuration':{'DurationType':'DayOrder'}, " & _
            "'ManualOrder':true" & _
        "}"
        
        trade = Application.Run("OpenAPIPost", "trade/v2/orders", body)
        
        'check if order was placed successfully
        If InStr(3, trade, "OrderId") = 3 Then
            MsgBox "Order placed successfully.", , "Order placed!"
        Else
            MsgBox trade, , "Error!"
        End If
        
    'close trade ticket after confirmation
    Unload Ticket
        
End Sub