Investing in financial products involves risk to your capital.

Close Navigation
Learn more about IBKR accounts

Excel ActiveX

Introduction

The ActiveX API is available for Windows computers to create a means of communication between Microsoft Excel and TWS or IB Gateway.

The ActiveX API wraps the C#/.NET API and is provided as an open source project TWSLib. ActiveX is a legacy technology developed by Microsoft, it essentially allows applications to share information with each other. The current ActiveX API for Excel wraps the C#/.NET API and is provided as an open source project TWSLib. It is suggested to also consider using the C# API directly as it provides seamless integration with the .NET framework.

One possible advantage of using the ActiveX for Excel API as compared to RTDServer or DDE is that ActiveX does provide the same number of functions as the other socket-based technologies (C#, Java, C++, Python). Disadvantages of the ActiveX Excel API is that it is more difficult to program as compared to other Excel APIs and not as robust as non-Excel socket-based API applications.

Notes & Limitations

  • The sample ActiveX spreadsheet provided with the API is meant only as a demonstration of API functionality, and not intended as a production-level tool to be used in trading. While it is designed with examples of almost all API functions, it does not have the necessary functionality to handle problems that may occur during trading such as disconnections, error codes, or dropped events in a robust way.
  • One possible advantage of using the ActiveX for Excel API as compared to RTDServer or DDE is that ActiveX does provide the same number of functions as the other socket-based technologies (C#, Java, C++, Python). Disadvantages of the ActiveX Excel API is that it is more difficult to program as compared to other Excel APIs and not as robust as non-Excel socket-based API applications.
  • Sample spreadsheet applications are distributed with the API download for each of the API technologies (RTD Server, ActiveX, DDE). It is important to keep in mind that the sample applications are intended as simple demonstrations of API functionality for third party programmers. They do not have robust error handling functionality and are not intended to be used as production level trading tools.
  • Interactive Brokers does not offer any programming assistance and therefore it is strongly advised to anyone willing to use any of the TWS ActiveX API to become familiar with the technologies involved such as C#.
  • By design, Microsoft Excel gives precedence to the user interface over the data connection to other applications. For that reason, Excel only receives updates when it is in a ‘ready’ state, and may ignore data sent for instance when a modal dialogue box is displayed to the user, a cell is being edited, or Excel is busy doing other things. A new Excel Real Time Data server (RTD) API has been introduced to help address some of these limitations, but they are inherent to Excel as a trading application and not specific to an API technology.
  • Excel must be set to the US convention for commas and periods. That is, commas denote thousands and periods denote decimals.

Configure Trader Workstation for ActiveX

  1. Log in to the Trader Workstation or IB Gateway
  2. Open the Global Configuration by selecting the Cog Wheel icon in the top right corner
     

  3. On the left, select API and then Settings
  4. At a minimum, you will need to maintain these settings:
    • “Enable ActiveX and Socket Clients” must be checked
    • “Read-only API” should be unchecked
    • Unless you are interested in modifying each request or the base code, we would advise setting your Socket port to 7496
  5. This will let you get started; however, there are some option settings we would encourage you to enable:
    • “Create API message log file” should be checked
    • “Include market data in API log file” should be checked
    • “Logging Level” should be changed from “Error” to “Detail”

Sample Spreadsheet Guide

This guide is intended to introduce new customers to the ActiveX Excel Sample Sheet and introduce the systems available of our underlying API.

Sample spreadsheet applications are distributed with the API download for each of the API technologies (RTD Server, ActiveX, DDE). It is important to keep in mind that the sample applications are intended as simple demonstrations of API functionality for third party programmers. They do not have robust error handling functionality and are not intended to be used as production level trading tools.

Locating the Sample Spreadsheet

While having TWS up and running and all the necessary API settings are set on TWS, as show on {TWS API CONFIG LINK}, we can then locate our sample sheet. Navigate to C:TWS APIsamplesExcel and select “TwsActiveX.xls”.

Initial Connection

After launching the sheet, you will be greeted with the General tab, which includes the connection details.

As it is with other Socket-based technologies, the host, port and ClientID are required when establishing the connection to TWS:

  • If TWS and Excel are on the same machine, then Host could be left blank as this represents the ‘localHost’.
  • Port Number must be the same port TWS listens on. By default, it will be set to 7496.
  • The client ID can be any positive integer and will be used to identify this specific API connection.

Assuming your connection details are correct, you may click the “Connect to TWS” button in the top right corner now. The red “Not connected” button will turn yellow while “Connecting”. When can start using the sheet once the button says “Connected” in green.

Note: Please be aware it may take a second or two for the connection to begin. You may need to wait a moment for the connection to establish.

ActiveX Connected status

 

 

Navigating Through The Sample

Selecting other tabs will display a green and grey sheet with several symbols listed. By looking in the bottom left, you should see a white banner labeled “Tickers”. You will then see several additional tabs on the bottom such as Bulletins, Market Depth, Basic Orders, and more. All of these tabs on the bottom correspond to different functionality of the API. You may need to click the arrow in the bottom left to scroll to the right, showing even more tabs.

Displays the ActiveX Page with several tabs listed on the bottom.

Requesting Data

Requesting data through the Excel ActiveX API is quite simple in our sample structure. All that must be done for most pages is to select the row or rows of data, and select the “Request ___ Data” button at the top. This will request data for whatever respective row is selected.

Highlights buttons on the ActiveX Tickers tab.

Receiving Data

Endpoints can be received in two standard ways. The first most common method is to retrieve all of the data in-line within the same page. This is prevalent in the Tickers and BasicOrders and several others. This is common for situations where single cell values are released for a given value.

Other times, you will see a new page created to house returned data. This is prevalent for Historical Market Data, Market Scanners, and more. This is where multiple values will be retrieved that correspond to the same identifier.

Receiving Data: In-row data

For in-line data, you will see the value appear directly to the right. The code has been designed to reference the column headers. As such, if you find that certain data in in-line responses are not needed, you may simply remove the column form the sheet.

Displays receiving tick data in activeX

Receiving Data: New Page

This will generate a brand new page at the end of the tab list as defined in the “Page Name” column of the original request. In the case of Historical data and the IBKR Stock request, you will see column V list “IBKR_STK_SMART_USD”. In Column W, we will see the Activate Page column.

Receive Historical Data

Architecture

The Interactive Brokers ActiveX API is rather unique in the sense that it demands the most Visual Basic knowledge to operate the API of our three Excel offerings. However, the API is similar to RTD in the sense that much of the underlying functionality and requests are based on C# source code.

To elaborate on this process further, C# makes the underlying requests to EClient.reqMktData, receiving data in EWrapper.tickData, and so on with accordance to the Trader Workstation API. As such, changes to method request systems are built in the C# code directly. As with our other source code, this is the baseline for all clients and is used to interpret data from a TCP socket bridge connection to the Trader Workstation. This is documented within C:TWS APIsourceCSharpClientactivex

The ActiveX component of this API comes from the communication between C# and Visual Basic. Specifically, the use of the System.Runtime.InteropServices Namespace supports the COM interoperability that provides the foundation of ActiveX.

Finally, Visual Basic will intercept the messages sent through the Namespace and then assign that data to specific cells in your active workbook. This code is all established at {TWS API}samplesExcelTwsActiveX

 

Underlying Requests

As stated within the Architecture section, the Excel ActiveX API is built on the underlying C# API with standard Visual Basic Translations to allocate the data onto the Excel sheets. For additional insight into the underlying methods, see the TWS API Documentation.

This website uses cookies to collect usage information in order to offer a better browsing experience. By browsing this site or by clicking on the "ACCEPT COOKIES" button you accept our Cookie Policy.