The Slog

Microsoft Excel XP (also known as Excel 2002 or version 10) provides a new worksheet function called RTD that allows the retrieval of data in real time (hence RTD - real time data). This function is specifically designed to access real-time data from market data vendors, such as Reuters and Bloomberg. In previous versions of Excel, this was accomplished by using DDE (Dynamic Data Exchange) or through custom developed worksheet functions, such as BLP (Bloomberg) or RTGet (Reuters). While these methods have been used successfully, they have limitations and are not always as robust as one would like. It seems likely that Microsoft intends to replace DDE with RTD as the preferred method of bringing real-time data into Excel.

We have written a RTD server for kdb+. It is a .NET application, and it communicates directly with a kdb+ Ticker Plant, or a chained ticker plant. The RTD feature allows real time data coming in from the ticker plant to be displayed in excel, similar to the animated picture here -

The schema can be customised according to whatever table names and column names are present in the ticker plant.

Download
The software is distributed with source under license. Click here to view license.

The download consists of a single zip file. You must accept the license terms before downloading. The code is now hosted at

https://code.kx.com/trac/browser/contrib/cskelton/excelrtd

To install, download the zip file, unzip it, and run the setup.exe program and follow the instructions.

You will need the Microsoft .NET Runtime installed. That can be downloaded from this link at the Microsoft Website.

Then change directory to the location where you installed the program.

Configuration
At that location you will see a number of files, 2 of which will be of immediate interest to you

K4RtdServer.dll.config

is an xml file that contains config information for the server. Change the host and port keys in the config file to connect to your ticker plant or chained ticker plant. Chained ticker plants are recommended as they provide some level of protection to your primary ticker plant.

The name key indicates the logical name of the plant, and is referenced from within the Excel worksheet.

For troubleshooting, a log can be activated - the log directory is specified in the config file under the logdir key. The program will create log files in the format logdir/log_hhmmssfff.txt. Several levels of tracing are available -

* “off”
* “error”
* “warning”
* “info”
* “verbose”

The RTD Server can also ‘fill’ data on your behalf - should a null value be received from your ticker plant, the rtd server will use the last non-null value received for that cell instead.

The other file of interest will be an example excel file that works with the default schema for demo trade and quote schema shipped with kdb+. If you open the file, ticker plant details and schema conforming, you will see something like this

It can be seen that the format for requesting data from the Rtd Server is

=RTD(”K4RtdServer”,,”plantname”,”tablename”,”column”,”symbol”)

The rtd server can also store the recent history of a cell, and this can be made available by using an index into the history as an additional parameter to the RTD call, e.g.

=RTD(”K4RtdServer”,,”plantname”,”tablename”,”column”,”symbol”,1)

will get the previous value of the cell. This can be useful for conditional formatting or perhaps triggering some other calculation. Other cells can be dependent on cells using the RTD function, as can series in charts.

Adjusting the update rate for Excel
To set the engine to handle a larger volume of updates, in Excel complete the following:

1. In Excel, go to the Visual Basic Editor (by pressing ALT+F11 or clicking Visual Basic Editor from the Macro menu (Tools menu)).

2. In the Immediate window (press CTRL+G or click Immediate Window on the View menu), type this code:

Application.RTD.ThrottleInterval = 1000

3. Make sure your cursor is on the line that you just typed, and then press ENTER.

4. To verify that it is set correctly, type this line of if code in the immediate window:

? Application.RTD.ThrottleInterval

5. If you put your cursor at the end of this line and press ENTER, it should display 1000. Then you know that your throttle interval is set correctly.

The default value for the engine interval is 2000 milliseconds.

Troubleshooting
Problem: Updates are not appearing in the excel worksheet

Possible solutions:

1) Check that the automatic calculation option is set. This can be found from the main menu->tools->options and then the calculation tab. The radio button Automatic should be set.

2) Open the log file to see if the program has been reporting any exceptions. Set the log level to verbose, close excel, reopen your worksheet and then look in the log file again.

3) Ask a qualified and authorised person to check that your server has registered with the ticker plant correctly.

Should the connection to the Ticker Plant be lost, after 15 seconds the following dialog box will appear

If you select yes, the rtd server will attempt to reconnect to the ticker plant. If you select no, then you will not be able to reconnect to the ticker plant again until you restart Excel.