We will cover the basics of using ActiveX controls in Excel to gather and share information with TWS.
What is ActiveX?
ActiveX is a legacy technology developed by Microsoft, it essentially allows applications to share information with each other. This lesson will cover the basics of ActiveX.
The current ActiveX API for Excel wraps the C#/.NET API and is provided as an open source project TWSLib.
The initial connection procedure is like other socket-based technologies such as the DDE socket bridge, the python API or Java.
In TWS, navigate to Global Configuration -> API -> Settings and ensure that “Enable ActiveX and Socket Clients” is selected.
- By default, the ActiveX sample connects on port 7496, so let’s make sure this option is selected as well.
- Then, navigate to C:\TWS API\samples\Excel and open the ActiveX Sample spreadsheet.
- In the ActiveX sample, the connectivity to TWS via the API is handled in the “General” tab.
- As with other socket-based technologies, the host, port, and clientId are required when establishing a connection to TWS
When using Excel and TWS on the same machine, leave the Host column blank, as this represents the “localhost”
- The port will be the same port TWS listens on, 7496, by default.
- The client ID can be any positive integer and will be used to this specific API connection. See our Connectivity documentation for further details.
- Once you review these parameters, click “Connect to TWS”. If successful, there will be a green “Connected” button displayed.
- When you are done using the spreadsheet, to terminate the socket connection by clicking on “Disconnect from TWS”.
Requesting Market Data
Similar to the new DDE spreadsheet, the ActiveX sample consists of a separate tab for each of the different functionalities offered with the API.
The tickers tab demonstrates how to query watchlist data from TWS. For a more in-depth overview of what is required to request market data in the sample, such as the contract description, we encourage you to review Lesson 3 of the course, as the spreadsheets are similar.
- After entering the parameters of the contract click the symbol, for example IBM in row 12.
- Then click on “Request Market Data”.
- The quotes for available tick types will begin to populate.
- When you want data to stop streaming click the button “Cancel Market Data”.
Details for programmers
When creating your own custom spreadsheet using the ActiveX API, we would encourage you to review the underlying VBA code of the sample spreadsheet.
Please note, IBKR is not able to provide any programming assistance.
To view the underlying VBA code, press Alt-F11 to open the VBA Editor. Here the underlying code for the Tickers tab is shown, showing the different subroutines this tab uses to perform requests and return data. See other forms and modules in the VBA project explorer on the left.
Disclosure: Interactive Brokers
The analysis in this material is provided for information only and is not and should not be construed as an offer to sell or the solicitation of an offer to buy any security. To the extent that this material discusses general market activity, industry or sector trends or other broad-based economic or political conditions, it should not be construed as research or investment advice. To the extent that it includes references to specific securities, commodities, currencies, or other instruments, those references do not constitute a recommendation by IBKR to buy, sell or hold such investments. This material does not and is not intended to take into account the particular financial conditions, investment objectives or requirements of individual customers. Before acting on this material, you should consider whether it is suitable for your particular circumstances and, as necessary, seek professional advice.
Supporting documentation for any claims and statistical information will be provided upon request.
Any stock, options or futures symbols displayed are for illustrative purposes only and are not intended to portray recommendations.
Disclosure: API Examples Discussed
Throughout the lesson, please keep in mind that the examples discussed are purely for technical demonstration purposes, and do not constitute trading advice. Also, it is important to remember that placing trades in a paper account is recommended before any live trading.