YFQuoteRTD - A Real Time Data Service for Excel

A while back I wrote a little Excel add-in in VBA that pulled back quotes from Yahoo Finance. One of its major draw backs was that it was quite slow and each time you wanted to get updated quotes you had to recalculate the sheet which could take quite a while depending on the number of quotes you were trying to retrieve. The reason for this is that Excel (pre 2007) is single threaded so each quote has to be retrieved in sequence. Yahoo Finance's website can take up to a couple seconds for each request so it was not unusual for Excel to be frozen for up to 30 seconds when trying to pull back 10-20 quotes. Anyone who has used the Reuters suite of Excel add-ins will know that functions like RtGet() do not suffer this problem. They return control of Excel to user immediately after they are called giving a value such as '#NA #NA' and after a number of recalculations the quotes start coming in. This is the sort of behaviour the YF_Price function in the YFQuote add-in really needs.


Real Time Data Servers

A Real Time Data server or RTD is a COM component that implements the Excel.IRTDUpdateEvent interface. They are called from Excel via the RTD function as follows:

=RTD(RTD CLASS, SERVER, PARAM1, PARAM2, PARAM3,...)

The second argument is optional and can be used to call an RTD server on a remote host via DCOM (details) but for our purposes we will be calling a local RTD server so it can be left blank.


How It Works

The RTD behaves a bit like an asynchronous function - Excel calls the RTD server with the specified parameters and the RTD server immediately returns control to Excel. In the background the RTD server is now free to do its own processing and once it has a suitable value to return, it notifies Excel which updates the cell.

Functionally the RTD server is a bit more complicated than your standard asynchronous function as you only get one instance of a particular RTD server per Excel session. This means that this single instance has to be able to handle being called from more than one cell at a time and with different parameters and is achieved by abstracting the parameters as topics.

Each unique set of parameters constitutes a topic to which Excel assigns a numerical ID. When data is first requested, the parameters along with the topic ID are passed to the RTD server which must store them internally so that when updates are passed back to Excel they can be associated with the correct "topic".

The process flow looks like this:

1. The RTD server is initialized (this only happens once per Excel session).
2. A topic (a unique set of parameters) is connected to the RTD server which stores this data internally, calls a method to get the quote asynchronously and immediately returns a dummy value to Excel.
3. Once the asynchronous call returns it notifies Excel that there is an updated value available.
4. Excel calls the RefreshData method with returns an array containing the full list of topics associated with the RTD plus the corresponding value as a two dimensional array.
5. Excel updates the cells and recalculates.

The functions defined in the Excel.IRTDUpdateEvent are:

• ServerStart (effectively the constructor and called only once) - passes the callback to Excel.
  
• ServerTerminate (effectively the destructor and called only once).
• ConnectData - passes a new topic plus the associated parameters.
• DisconnectData - called when a particular topic is no longer required (e.g. the user has deleted a particular =RTD() function from a cell).
• Heartbeat - called by Excel to check that the RTD server is still alive.
• RefreshData - called by Excel when the RTD server calls callback.UpdateNotify() and should return a two dimensional array of topicID,Value plus set the updated topic count value that is passed in by reference.

YFQuoteRTD

YFQuoteRTD uses the process flow and functions described above along with a WinAPI timer to periodically refresh the quotes. Each time the timer ticks it loops through the available topics and fires an asynchronous call to get the quote for that topic. Each asynchronous call has a callback function specified that notifies Excel that an updated quote is available.

Both binaries and source code are available. The source code can be compiled with Visual C# Express which Microsoft has made freely available.

Downloads

Binary
Source