IB ActiveX API Order status processing with VBA Dictionary object

In this post: you will find a solution for duplicate order status update messages sent back by Interactive Brokers servers to API clients

IB ActiveX API order status update

Interactive Brokers platform and its API is one of the best trading automation products available to retail traders and financial advisors. However, it is not without its own quirks, which require work-arounds. If you ever submitted an order programmatically to Trader Workstation using Interactive Brokers API – you are probably aware of the well-known issue involving Interactive Brokers servers sending back duplicate order status updates. This can be problematic if your order management system or trading algorithm relies on accurate order status updates. For example, a duplicate “FILLED” order status message for the same order ID can be misleading and cause issues for your trading logic. This problem occurs regardless of which IB API implementation you use – ActiveX, Java, or C++. The solution is to filter out duplicate order status updates up-front, before they reach your trading algo or portfolio management logic.

This post demonstrates a method for eliminating duplicate IB ActiveX API order status updates using VBA Dictionary object. An instance of Dictionary will be used to store received order status updates so that we can filter out duplicates received by ActiveX API’s ‘orderStatus()’ function. The solution demonstrated here is implemented in our IB Excel Trader spreadsheet application.

IB ActiveX API order status update fix for duplicate messages

We’ll demonstrate our solution by modifying VBA macro code found TwsActiveX.xls Excel file, which is distributed with Interactive Brokers’ IB API Client software.

Both TwsActiveX.xls and IB Excel Trader contain class module cTWSControl, which contains subroutine “Private Sub m_TWSControl_orderStatus”. This subroutine is invoked by TWS ActiveX Control whenever IB servers (via Trader Workstation) send order status updates back to your spreadsheet:

Below is the “m_TWSControl_orderStatus” function code copied from “cTWSControl” class module of TwsActiveX.xls distributed with IB API v9.71:

'-----------------------------
' orders events
'-----------------------------
Private Sub m_TWSControl_orderStatus(ByVal id As Long, ByVal status As String, ByVal filled As Long, ByVal remaining As Long, ByVal avgFillPrice As Double, ByVal permId As Long, ByVal parentId As Long, ByVal lastFillPrice As Double, ByVal clientId As Long, ByVal whyHeld As String)
    On Error Resume Next
    ' update Orders sheet data
        Call ThisWorkbook.Sheets(SHEET_NAME_BASICORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
        Call ThisWorkbook.Sheets(SHEET_NAME_CONDORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
        Call ThisWorkbook.Sheets(SHEET_NAME_ADVANCEDORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
        Call ThisWorkbook.Sheets(SHEET_NAME_ADVISORS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)

    On Error Resume Next
    ' update Open Orders sheet data
    Call ThisWorkbook.Sheets(SHEET_NAME_OPENORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
End Sub

We will update this code to store order status updates in an instance of VBA Dictionary object, provided by Microsoft Scripting Runtime. A Dictionary stores key-value pairs, where each key has to be unique. For each status update received by the orderStatus method we will create a key consisting of order’s “permId”, concatenated with the status. Every type ‘orderStatus’ method is called – we will check if an status update for this order has already been received (and therefore will appear in the Dictionary). If it has been – we ignore this update by exiting the function (Sub) right away. One important caveat is that we need to store the “remaining” quantity passed to orderStatus in the ‘remaining’ parameter. We will store it as the Dictionary record’s value. It is important to store in order to avoid ignoring partial order fill status updates. If an order is filled partially – there will be multiple partial fills with the same status, but different “remaining” quantity, which will mean we do not want to disregard those status updates, since our algo will need to know about partial fills.

In order to make VBA Dictionary object available in your Excel spreadsheet :
1. Open Excel VBA Editor (Alt + F11)
2. Go to Tools -> References… menu.
3. Find in the list “Microsoft Scripting Runtime”
4. Make sure the checkbox next it is checked.

Enable MicrosoftScripting Runtime in Excel

Here is the updated version, with comments indicating the changes we made, including the new ‘orderStatusUpdates’ Dictionary object:

'At the top of cTWSControl declare and initialize Dictionary object
Public orderStatusUpdates As New Dictionary
'..........
'..........

'-----------------------------
' orders events
'-----------------------------
Private Sub m_TWSControl_orderStatus(ByVal id As Long, ByVal status As String, ByVal filled As Long, ByVal remaining As Long, ByVal avgFillPrice As Double, ByVal permId As Long, ByVal parentId As Long, ByVal lastFillPrice As Double, ByVal clientId As Long, ByVal whyHeld As String)
    On Error Resume Next
    'we want to make sure we get a fresh valid order id every type an order status is updated
    'this is a general improvement to ensure that we always have a valid unused order id for submitting a new order
    objTWSControl.m_TWSControl.reqIds (1001)
    
    'prepare status update key variable
    Dim statusUpdateKey As String
    'value is concatenated unique (per order) permId and status, separated by a hash character
    statusUpdateKey = Str(permId) & "#" & UCase(status)
    If orderStatusUpdates.Exists(statusUpdateKey) = True Then
        Dim remVal As Long
       'here dictionary already contains a key/value with this order and status value, we need to check if 'remaining' value is same (which will mean it's a        duplicate message), or different, which means it's a valid partial order fill update
        remVal = orderStatusUpdates.Item(statusUpdatesKey)
        If remVal = remaining Then
            'duplicate order status update: - ignore it by exiting the subroutine immediately
            Exit Sub
        Else
            'remaining order quantity, it will change for partial fills
            orderStatusUpdates.Item(statusUpdateKey) = remaining
        End If
    Else
        'this is the first order status update message for this order id - add a new record to Dictionary
        Call orderStatusUpdates.Add(statusUpdatesKey, remaining)
    End If
       
    'Execute remainder of order status update processing logic. Code below will NOT be called for duplicate order status updates.
     On Error Resume Next
    ' update Orders sheet data
        Call ThisWorkbook.Sheets(SHEET_NAME_BASICORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
        Call ThisWorkbook.Sheets(SHEET_NAME_CONDORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
        Call ThisWorkbook.Sheets(SHEET_NAME_ADVANCEDORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
        Call ThisWorkbook.Sheets(SHEET_NAME_ADVISORS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)

    On Error Resume Next
    ' update Open Orders sheet data
    Call ThisWorkbook.Sheets(SHEET_NAME_OPENORDERS).UpdateOrderStatus(id, status, filled, remaining, avgFillPrice, parentId, lastFillPrice)
End Sub

———-
The VBA Dictionary object is one of the most powerful and versatile data structures available in Excel VBA. In addition to its quick lookup and ability to store any type of object/variable as keys or values – users can construct even more powerful and sophisticated data structures by using two or more layers of Dictionary objects. Think about using Dictionary objects as item values in another Dictionary object – with this type of constructs you can model pretty much any domain entity.

More information on using Dictionary object in Excel VBA:

This algorithm for filtering out duplicate order status updates can also be used in Java, C++, and C# applications. Each of those languages has a dictionary data structure (also referred to as “hash” or “hashmap”).


VBA Programming book
Excel Trading Systems book


Trading Geeks provides consulting services in trading strategy and software development for independent traders, financial advisors, and hedge funds. With questions about Interactive Brokers APIs, content presented in this article, or to request a quote for your project- please send us a message using the Contact Us form on the right.

Posted in Interactive Brokers API, Programming examples Tagged with: , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

IB Data Downloader


ib_downloader_3_downconf ib_downloader_3_options

IB Excel Trader

  • IB Excel Trader version 1.6 is now available!
  • Trade Stocks, ETFs, Futures, and Forex directly from Excel.
  • Implement custom trading rules using spreadsheet formulas or VBA.
  • Program entry rules for single or bracket exit orders. Market, Stop, Limit, Stop-Limit, as well as complex algo orders are supported.
  • Order Log sheet (new!). Contains a detailed list of each order status change in a filterable Excel table.
  • Use our Customization Service to extend IB Excel Trader and contract our programmers to develop your custom trading strategies.
IB-Excel-Trader-1-5_wTWS-2

Interactive Brokers (IB) is a low cost provider of trade execution and clearing services for individuals, advisors, prop trading groups, brokers and hedge funds. IB's premier technology provides direct access to stocks, options, futures, forex, bonds and funds on over 100 markets worldwide from a single IB Universal account.

Member NYSE, FINRA, SIPC. Visit www.interactivebrokers.com for more information.

Contact Us!

Please enter your contact details and a short message below and we will respond to your message shortly.