The Dynamic Data Exchange (DDE) in Excel using a sample spreadsheet

In this lesson, we’re going to define Excel DDE, run the DdeSocketBridge batch file, access market data using a blank spreadsheet and walk-through a sample DDE spreadsheet exploring syntax.

What is DDE?

The Dynamic Data Exchange (DDE) protocol is another method provided by Microsoft that allows inter-process communication between Windows applications.

 For the purposes of IBKR, this allows communication between TWS and Excel using standard DDE control strings.

Differences between legacy DDE connection and new DDE socket bridge

• Beginning with TWS API 975, the new DDE socket bridge was rolled out to replace the legacy DDE connection, which had some limitations.
• Legacy DDE connects to TWS using a dynamic link library (dll) file, which only allowed one active connection per machine.
• While the new socket bridge allows you to connect up to 32 instances to the same TWS session.
• Plus, the DDE Socket Bridge utilizes Java.  So ensure that Java is installed before running the batch file.
•  The legacy DDE connection requires 32-bit TWS, while the new socket bridge works in either 32 or 64-bit architecture.
• The available functions for the legacy DDE are limited while the new socket bridge brings all the same functions we offer with our other socket based programming languages such as news, tick data, histogram, streaming PnL.

Running the new socket bridge and new sample

• Full instructions on running the new socket bridge and sample can be found on the GitHub page.
• First launch TWS or IB Gateway.
• Then navigate to Global Configuration -> API -> Settings and ensure that “Enable ActiveX and Socket Clients” is checked.
• When using the IB Gateway, the socket connection is enabled automatically.
• By default, the socket bridge connects using port 7496. Make sure your TWS is set to the same.
• The “Enable DDE connections” setting was used for the legacy DDE connection. Starting with TWS Build 980 and above, it is deprecated.
• Make sure to leave this setting unchecked.
• Then, navigate to C:TWS APIsamplesDdeSocketBridge and double click on the batch file “runDdeSocketBridge” to run it.
  • If the socket bridge window does not open and appears to only flash momentarily, you may need a fresh install of Java. See the Pre-requisites section listed in Lesson 1.
• Upon successful connection of the socket bridge, there should be a popup window that shows Connected!
• Now that the connection has been established, make sure to leave terminal running and now we can begin to use the sample spreadsheet. If terminal is closed, it will disconnect the socket bridge.

What if you want to connect using a different port or specify a different username?

This can be done by modifying the batch file: runDdeSocketBridge.bat.

Open runDdeSocketBridge.bat in Editor/Notepad and modify to have: 

echo off
if not exist "DdeSocketBridge.jar" goto :error
java -Djava.library.path=.srcmainresources -jar DdeSocketBridge.jar -myDDE -p7497 goto :end
:error
echo DdeSocketBridge.jar is not found
:end

Requesting data from a blank spreadsheet

Lets start with a blank spreadsheet and subscribe to ticker symbol AMZN and request the bid, ask and last price.

As defined previously – make sure that the runDdeSocketBridge is running by double clicking on the .bat (batch) file.

Market Data retrieval requires at least two different DDE links:

• one –  to start the market data subscription and
• two –  specifies the returning tick type.

The formula to start the request will need to provide TWS with enough information so that TWS can identify which instrument we are interested in.

I am going to specify the stock AMZN with this formula:
twsuser
The user name with which you logged into the TWS
requestId
The request unique identifier (any positive integer)
symbol
The contract symbol
sectype
The asset class of the contract
exchange
the listing exchange or venue we want to query the data from
currency
The contract currency

=Stwsserver|tik!'id2?req?AMZN_STK_SMART_USD_~/'

Now for the second request to receive the bid price:
twsuser
The user name used in the first request – twsserver
requestId
The request identifier used in the first request – 2
Tick type – bid price
Now – I’ll also request the ask and last price.

This is how you request market data from TWS using Excel DDE. Next, we’ll open a sample spreadsheet provided in the TWS API download which contains a variety of examples.

=Stwsserver|tik!id2?bid

Now let’s navigate to the samples folder to open the new sample DDE spreadsheet, NewTwsDde.xls.
As you can see, the sample spreadsheet/workbook consists of multiple tabs which for the different API functionalities.

• In the tickers tab, you may request streaming market data.
• This tab pulls the TWS watch list data, like RTD.
  To begin to stream data, you will first need to fill in the contract description section for your instrument.
• There are already a few sample contracts that have been filled out.
• A stock contract would typically consist of a symbol, secType, exchange, currency, and primaryExchange as shown in row 19.
• These parameters for any instrument type can be found in the TWS contract description window.

In TWS you can right click on the instrument of interest, then go to “Financial Instrument Info”, then “Description”.

• For more information on contract definitions, check out the API documentation.
• As with RTD, you can also request generic ticks.
• Once you have your request ready, click on the symbol column of interest.
• If requesting data for multiple symbols, you may highlight multiple rows by pressing the Ctrl key as you click on row.
• Click on “Req Mkt Data” on the top left of the sheet.
• The quotes for different tick types will begin to stream to Excel from TWS.
• When you want data to stop streaming click on “Cancel Mkt Data”

Functionality for the rest of the spreadsheet

There is a lot of functionality throughout in the Excel tabs and it would take some time to go through each and everyone one of them during this lesson.

We encourage you to review the corresponding documentation for each tab of interest on our github guide. For example, for the “historicalData” tab, and the “Account” tab.

How to port legacy DDE sheets and formulas

The new socket bridge is backwards compatible with legacy DDE Excel sheets and formulas. You only need to change the username in the DDE formulas to the socket bridge username, whether this is entered by a macro button like in the sample spreadsheets or directly in the formula bar.

• For example, a client with IBKR username “sample123” may have the following DDE control string to request market data from an old spreadsheet.

=Ssample123|tik!'id1?req?EUR_CASH_IDEALPRO_USD_~/'

• After the socket bridge is connected, the client would only need to ensure the formulas reference the socket bridge username, which is “twsserver” by default.

=Stwsserver|tik!'id1?req?EUR_CASH_IDEALPRO_USD_~/'