Excel DDE

The Dynamic Data Exchange protocol is a method of inter-process communication developed by Microsoft to establish communication between Windows applications running on the same computer. The DDE API is available for Windows computers to create a means of communication between Microsoft Excel and TWS or IB Gateway. This allows users to make calls to the standard API library using Visual Basic.

The Excel DDE API has the full functionality offered by the Trader Workstation API implemented via a DDE socket bridge that uses the open source Java – DDE interoperability library JDDE. This means the DDE API offering is built utilizing both Java and VB together in order to create a complete Excel offering to the standard put in place by Microsoft.

• 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 DDE API to become familiar with the technologies involved such as the DDE protocol and VBA.
• 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.

• Other programs running on the same computer which use DDE can interfere with the communication between Excel and TWS and cause Excel to ‘hang’ or ‘freeze’ after making the initial request. The only solution to this problem is to close other programs using DDE one-by-one to find the culprit. Programs which are known to cause this issue include Google Chrome, Microsoft OneNote, Skype and Adobe Creative Cloud.
• Because the DDE Bridge is built using Java technologies, please install the latest version on your operating system from https://www.java.com/en/download/ .

In many cases, customers might see a response for a given market data column which displays “N/A”, “0”, “-1” in some of their cells. This can be occur for a few different reasons.

1. While you may have part of the required market data subscription on the instrument, you may not have all of it. This is particularly common for derivatives traders who may not have a market data subscription for the given underlying.
2. You might also encounter this by requesting too many symbols simultaneously. This is discussed further in the Outgoing Message Rate Limitations section
3. Similar to (2) above, every client has a maximum number of market data lines shared between TWS and their API. By default, all customer’s have a maximum of 100 market data lines, allowing for 100 unique contracts to be requested at any one time. If you surpass this limit, you may find that some fields can not be returned.
4. This may also happen in the event your machine, in tandem with Excel, simply can not handle the volume of data requested. While the machine limitations are unique to each user, you may explore and modify the refresh rate as described in our documentation

Some TWS Settings affect API. You can open the Global Configuration by selecting the Cog Wheel icon in the top right corner.

Memory Allocation

In TWS/ IB Gateway – “Global Configuration” – “General”, you can adjust the Memory Allocation (in MB)*.

This feature is to control how much memory your computer can assign to the TWS/ IB Gateway application. Usually, higher value allows users to have faster data returning speed.

Normally, it is recommended for API users to set 4000. However, it depends on your computer memory size because setting too high may cause High Memory Usage and application not responding.

For details, please visit: https://www.ibkrguides.com/traderworkstation/increase-tws-memory-size.htm

API Settings

In TWS Global Configuration – API – Settings, there are many API settings. Please enable/disable some API settings based on your use case.

In this section, only the most important API settings for API connection and incident troubleshooting are covered.

Please:

• Enable “ActiveX and Socket Clients”
• Disable “Read-Only API”
• Enable “Create API message log file”
• Enable “Include market data in API log file”
• Change “Logging Level” to “Detail”

Note:

1. In IB Gateway Global Configuration – API – settings, there is no “Compatibility Mode: Send ISLAND for US stocks trading on NASDAQ”. Specifying NASDAQ exchange in contract details may cause error if connecting to IB Gateway. For this error, please specify ISLAND exchange.

Daily& Weekly Reauthentication

Note:

1. IBHK users does not have “Never lock Trader Workstation” and “Auto restart” in TWS.
2. Windows Sleeping Mode also causes API disconnection. It is strongly suggested to choose “Never Sleep” in Windows.

The DDE Socket Bridge is an essential component of the Excel DDE API library. This governs all communication between the Trader Workstation and Excel using the JDDE implementation.

1. Launch Trader Workstation or IB Gateway
2. Open the Windows File Explorer
3. Navigate to C:\TWS API\samples\DdeSocketBridge
4. Double click the file, runDdeSocketBridge.bat. It may just be labeled as runDdeSocketBridge on your computer.
5. There should be two popup windows that appear.
   • The first will be a black command prompt window. You may see a few lines stating you are connected, and a few stating “Error… data farm connection is OK.” These “errors” are only notifications, and should not cause concern.
   • You will also see a small java window. This will list the service name, host, port, clientId, and you should see “Connected!”. If you see “Disconnected” instead, you may need to Configure Your Socket Bridge.
6. Assuming your socket bridge is showing “Connected!” then you can navigate to C:\TWS API\samples\Excel and open the  TwsDde.xls file sample.

For various reasons, you may find the need to modify the connection values used for the DDE Socket Bridge. You are able to modify the ClientID, Host IP, Socket Port, or even the Service Name.

To elaborate further on the Excel DDE API, it is worth elaborating upon the underlying architecture of the system given its complexity. As stated prior, the Excel DDE is implemented using the open source Java – DDE interoperability library, JDDE because DDE is built around 32-bit systems natively. This is implemented utilizing two key parts.

First and foremost, there is the creation of the socket bridge itself. If you explore further in the DdeSocketBridge directory, you will find the full path towards  C:\TWS API\samples\DdeSocketBridge\src\main where the source code of the socket bridge is housed. The socket bridge is used as a translation tool between the DDE implemented by Visual Basic and the Trader Workstation. Listed within  C:\TWS API\samples\DdeSocketBridge\src\main\java\com\ib\api\impl\EWrapperImpl.java users will be able to find the full Java implementation structure of the TWS API. This particular file is used to receive and transmit data from Trader Workstation to Excel.

Meanwhile, the C:\TWS API\samples\DdeSocketBridge\src\main\java\com\ib\api\dde\dde2socket\requests directory encapsulates all of the various outbound requests sent from Excel to Trader Workstation. These files could be modified such that all outbound requests will then better modify values such as defaults or other passed information so as to not require they be implemented in each Excel request directly.

It is important to note that whenever modifications are made under  C:\TWS API\samples\DdeSocketBridge\src\main then the socket bridge will need to be recompiled

The socket bridge is only half of the greater DDE API. As the socket bridge handles the transmission of data from Excel to the socket bridge, then the socket bridge to TWS and back again. However, once the data has reached Excel from the socket bridge, Visual Basic must now carry the translations into the designated cells for which the data will be displayed. The content of the visual basic which impacts the default sample sheet is hosted in C:\TWS API\samples\Excel\TwsDde. Users who wish to modify how data behaves within Excel itself, such as how or where data is set, would need to make modifications here. Files such as BasicOrders.cls will handle how orders are placed or from which sheets and cells the data can be handled. These functions and can be copied to new files or resaved as needed by developers so long as they are appropriately modified to match their new environment.

The Excel DDE formula is comprised of several separate components. As a baseline there is the Server, also known as the Service Name from your DDE Socket Bridge. This is followed by your Request Type, and finally the Request String.

We will use this formula to elaborate on this structure: =Stwsserver|reqMktData!'id1?EUR_CASH_IDEALPRO_USD_~/'

• ‘=Stwsserver’: This first part indicates the Service Name set for our socket bridge connection. The ‘=S’ indicates the start of a formula, and that the next value will be our Service Name.
• ‘|’: The pipe, the character typically above the Enter/Return key, is used to divide our service name from the rest of the request.
• ‘reqMktData’ is used to state the Request Type we would like to make. These request types are based on the TWS API EClient request types documented in the TWS API Documentation.
• “!'”: The exclamation, (!) indicates the start of the Request Type.
• After the exclamation, we will see a pair of single quotes (‘). These will encapsulate our Request String. This may include the Id, Contract Object, or otherwise.
• All of the arguments included within the Request String should be delimited with a question mark (?) between them. In our example above, we can see this separates the id from the Contract.

It is possible to view live quotes for multiple products updating real time within Excel. Requests via the TWS DDE API are nothing but Excel formulas (DDE data links) each of them serving a very specific purpose. Market Data retrieval requires at least two different DDE links: one to start the market data subscription and another one which will be receiving the specific tick type.

In many instances, customers are able to designate a single cell and print out values as they are retrieved from the DDE Socket Bridge as they are produced. These are single variable responses, such as data ticks to retrieve specifically a “LAST” or “BID” value.

In many cases, you will find that data may be returned to visual basic in an array of values rather than as a single value. In these instances, we must implement Visual Basic to receive and distribute this data throughout the sheet. This can not be done using a formula alone.

For nearly all instrument-based requests, you will need to create a contract. The standard format of a contract is built for the request, and will utilize an underscore (_) delimited string of all of the contracts attributes. The contract is then concluded with a final “~/'”

Below, we have included an array of different samples that can be used as samples.

Note: The format of the option local symbol conforms to the Option Symbology Initiative (OSI).

This guide is intended to introduce new customers to the DDE 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.

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.

As stated within the Architecture section, the Excel DDE API is built on the underlying Java 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 guide is intended to introduce new customers to the DDE Excel example use cases.