MS Windows Spreadsheet Linking using DDE
Investor/RT for MS Windows uses DDE (Dynamic Data Exchange) to provide market data to other applications such as
- Microsoft Excel
- OpenOffice Calc, the spreadsheet component of the OpenOffice software suite
In order to create a link in your spreadsheet, you type a formula into a cell. The formula specifies the ticker symbol and the name of the data item you want Investor/RT to place there. The cell formula formats are the following :
- Microsoft Excel:
=rt|symbol!data_column - OpenOffice Calc:
=DDE("RT";"symbol";"data_column")
In both case:
- symbol is the ticker symbol and
- data_column is the name of the data column you want, e.g. Last or V#1.
The data column names are the titles Investor/RT uses in quote pages. To see a complete list of data column names you can link to your Excel spreadsheet, open an Investor/RT quote page and click the "Add New Column" button on the toolbar. A list of quote page column names will then appear.
The letters in the data column name are not case sensitive. You may abbreviate a column name by entering only the first few letters. Thus =RT|AAPL!LAST is equivalent to =rt|AAPL!la. Note that the ticker symbol must exactly match the ticker symbol you want in Investor/RT, usually it is all uppercase letters. This formula places the last price of Apple Computer into the cell.
Examples / Open Office Calc
=RT("IBM","VO") returns the current volume for IBM.=RT("AAPL",C1) returns data on Apple Computer based on field designator in Cell C1.=RT(A5,B1) returns data on Ticker in cell A5 based on the field designator in Cell B1.=(RT(A1,"LA")+RT(A1,"HI")+RT(A1,"LO")/3 returns the average of high, low, and last for the ticker symbol in cell 1.
Additional information about Microsoft Excel
While the DDE feature was first introduced in Windows 2000, it still works perfectly with the latest Investor/RT 16.1 version running under Windows 11, along with the latest Excel version available through a Microsoft 365 subscription. Make sure the DDE feature is activated in your MS Excel settings (see the troubleshooting section below) and that the configuration variable EnableDDEServer is set to true (its default value)
Warning: Enclose ticker or data column in single quotes if such strings contain spaces or other non-alphabetic characters such as #, @, or $.
Indeed, certain characters, such as $, @ or left parentheses, have special meaning to Excel, and therefore will cause an error if you use them in your formula. Spaces within names also cause a syntax error. If an Investor/RT data column name has embedded spaces or special characters, you must enclose the name with single quotes('). For example, Investor/RT has a field named (H+L)/2 used to show the average of the high and low price for the day. To link this column, use the formula: =rt|AAPL!'(H+L)/2' Note that the same thing applies to ticker symbols that contain spaces or special non-alphabetic characters. Use single quotes around such tickers, e.g. =RT|'$INDU'!Open
Note: to export any Custom indicator value generated on a chart to Excel (by definition not available as a built-in QuotePage column), first store their Spot value into a V# variable and then use=RT|'@ES#'!'V#1' T# variable managed by SET statements, and possibly generated through Automated Trading systems can also be exported to Excel.
New DDE features: using Data Column ID instead of Data Column Name
The DDE (Dynamic Data Exchange) feature of Investor/RT has been improved. The general format for Excel linkage to Investor/RT is:
=RT | symbol ! data-designator
where symbol is the ticker symbol and where the Data-designator can be expressed as
- a Data Column Name or
- a Data Column ID number
For example: =RT | INTC ! Last will setup the Excel worksheet cell to dynamically update as the last price changes. Each data column in Investor/RT also has a data column id number. To find the DDE data column id number for any column use the File> Preferences> QuotePages> QuotePage Formats window. Pick the data column of interest in the list on the left and click the "Get Info" button to find the number of that column. For example, the 52WeekHigh data column has id number 21, while the Last data column has id number 2. =RT | INTC ! Last and =RT | INTC ! 2 do the same thing =RT | MSFT ! 52Week and =RT | MSFT ! 21 are also similar.
Reminder: to have a full list of all "native" data transmitted by a data feed for a given instrument, open a Quote page, right click on a ticker name and select "Get Info".
The Investor/RT DDE now supports the "System" topic. Use "System" as the ticker symbol and "Version" as the data designator to obtain a description of the version of Investor/RT that is supply the data and the data service in use, i.e. =RT|System!Version
Troubleshooting section
1) Activating DDE:
- To activate Dynamic Data Exchange (DDE) in Excel, you must adjust the Trust Center and Advanced options to allow DDE server lookups and unblock external data applications. Here are the exact steps to enable it:
- Click File > Options (at the Bottom Left Corner of the Excel window)
- Navigate to Advanced on the left menu and Scroll down to the General section.
- Uncheck the box that says Ignore other applications that use Dynamic Data Exchange (DDE).
- Next, click Trust Center in the left menu, then click Trust Center Settings....
- Go to External Content.
- Under Security settings for Dynamic Data Exchange, select Enable Dynamic Data Exchange Server Lookup. (Note: Enable Dynamic Data Exchange Server Launch allows data to be sent out of Excel, but is generally kept disabled for security reasons).
- Click OK
2) Error message : “Can not run RT.exe. The program or one of his components is missing”
Usual fix : You need to run both Investor/RT and Excel as Administrator
- Start I/RT first (prior to starting Excel). Right click on the InvestorRT. exe file and select “Run As Administrator”
- Start Excel and run it also as Administrator
For more information/examples about the DDE capabilities, please refer to these forum posts
https://www.linnsoft.com/topic/can-dde-formulas-contain-cell-references
https://www.linnsoft.com/topic/dailyweekly-high-lows-excel-download
and also to this video
Reminder: other ways to achieve such export of data to Excel are
- through the dedicated Exportdata RTX indicator
- or simply (for one-time operation) through the regular "Export" menu options that can be accessed with a right-click menu on any chart, as highlighted in this video.