How Does Excel’s RTD Function Work ?

Excel has a function called RTD the enables a user to update a spreadsheet with real-time data. with its help you can publish your data,stock quotes, currency exchange rates, inventory levels, price quotes, weather information, sports scores, and so on, to client in time when those data changed. The first thing you have to do is create an RTD Sever, which must be a component object model automation server implement the interface Excel.IRtdServer.  Excel uses the RTD server to communicate with a real-time data source.

1.RTD Work Sheet Function

Let have a look at the syntax for the Excel RTD worksheet function first:

=RTD(ProgID, Server, String1, String2, … String28)

The ProgID parameter is a required String value representing the programmatic ID (ProgID) of the RTD server.

The Server parameter is a required String value representing the name of the computer on your intranet (using Distributed COM (DCOM)) on which the RTD server is running. If the RTD server is running on the local computer, leave this parameter blank or use two quotation marks ("").

The String1 through String28 parameters represent topics to be sent to the RTD server. Only the String1 parameter is required; the String2 through String28 parameters are optional. There is a limit of 28 parameters, and in most cases, only the String1 parameter is used. The actual values for the String1 through String28 parameters depend on the requirements of the real-time data server.

Basically, Excel will create an instance of the COM object corresponding to the ProgID (that’s the RTD Server). For each unique set of topics, Excel will create a “topic” which will be used by the RTD server to tell Excel when new data is available. Once Excel is ready for new data, it will call into the RTD servers that said they have new information and grab that information and place it in a cell. The fact that the RTD server calls into Excel and informs it of new data availability (and the fact that Excel then knows it actively needs to take the data and place it in a cell) is what makes this into a “push model”.

2.RTD’s Push-Pull Model

Push model – the RTD server detects a change, notifies Excel about it and Excel updates the data (this is different from regular in-cell formulas .

Pull model – Excel decides to call them when a change is made in something that they depend on)

Ok , One figure is more then one hundred words:

3.Create An RTD Server

RTD Sever is  a component object model automation server implement the interface Excel.IRtdServer.

3.1 Introduction to IRtdServer

IRtdServer has six interface methods as followings:

1.)ConnectData(TopicID, Strings, GetNewValues)

This method is called whenever Excel requests new topics from the RTD server. The TopicID parameter is a required Long value that represents a unique, arbitrary value automatically assigned by Excel (for example, "34") that identifies the topic.

The Strings parameter is a required array of one or more Variant values that the user enters into the RTD function to uniquely identify the topic. This Variant array should always be an array of String values.

The GetNewValues parameter is a required Boolean value; True if new values are to be retrieved. Leave the GetNewValues parameter alone if you want Excel to use the previous value that it had saved with the Excel spreadsheet.

2.)DisconnectData(TopicID)
This method is called whenever Excel no longer requires a specific topic.

The TopicID parameter is a required Long value that represents the arbitrary, unique value automatically assigned by Excel in theConnectData method that identifies the topic.

3.)Heartbeat
If the RTD server is no longer able to process RefreshData method calls, the Heartbeat method enables Excel to pop up a dialog box that says "The real-time data server ‘XYZ’ is not responding. Would you like Microsoft Excel to attempt to restart the server?"

This method returns a Long value; a value of 1 indicates that the real-time data server connection still exists, and a value of zero (0) or a negative value indicates that the real-time data server connection no longer exists.

4.)RefreshData(TopicCount)
After a call from the IRTDUpdateEvent callback object’sUpdateNotify method, this method is called by Excel to pull new values from the real-time data server (also known as a topic refresh).

This method returns a two-dimensional array of Variant values. The first dimension represents a list of topic IDs; these topic IDs map to theTopicID parameter in the ConnectData method above. This is how Excel associates topics with data.

The second dimension represents the values associated with the topic IDs.

The TopicCount parameter is a required Long value that the RTD server provides; it represents the number of elements returned in the array ofVariant values.

5.)ServerStart(CallbackObject)
This method is called when Excel requests the first topic from the real-time data server.

This method returns a Long value; a value of 1 indicates a successful request, and a value of zero (0) or a negative value indicates a failed request.

This method call is immediately followed by a call to the ConnectDatamethod.

The CallbackObject parameter is a required IRTDUpdateEventcallback object that the RTD server uses to notify Excel when it should gather updates from the RTD server through the IRTDUpdateEventcallback object’s UpdateNotify method.

6.)ServerTerminate
This method is called when Excel no longer requires topics from the RTD server (for example, the user quits Excel).

Create an RTD server to deliver the real-time data to the RTD function. The real-time data server contains the topics that the RTD function relies upon. See the resources section for more information.

3.2  Introduction to IRTDUpdateEvent

As you see at 5.) of 2.1, RTD Server use the IRTDUpdateEvent callback objects to interactive with Excel.The members of the IRTDUpdateEvent callback object are described as followings.

1.)HeartbeatInterval property
This property returns or sets the time interval between RTD server updates.

This property returns a Long value that represents the number of milliseconds between RTD server updates; this property cannot be set below the default of 15,000 milliseconds, because of the standard 15-second RTD server timeout.

2.)Disconnect method
This method tells Excel that the real-time data source will be disconnecting from the RTD server. Excel can use this method to take some type of action before it loses its RTD connection.

3.)UpdateNotify method
This method is called by the RTD server when it has a new value for one or more topics.

3.3 Create An Component

The Component can be written in VB , C++, C# language , it’s up to you. you can query the data source check out the changes topics and then get the changed data only , alternative , you can get data periodically with the help of a timer , or a query loop.

Normally , Excel RTD function have only a return value , which mean you can not update a range at once ,  another problem is RTD Sever is in-process of excel , so its speed is crucial.    There are two structure to speed up.

1.) RTD Return values for a range ,convert them into cells with VBA code.

2.) RTD Server cache values for topics.

Further more , you can get much more speed enforcement with multiple thread and asynchronous method , welcome to visit Multiple-Thread In COM.

Reference:

http://blogs.msdn.com/cumgranosalis/archive/2006/05/23/RTDPart1.aspx

http://blogs.msdn.com/cumgranosalis/archive/2006/05/24/RTDPart2.aspx

http://msdn.microsoft.com/en-us/library/aa140061(office.10).aspx

http://msdn.microsoft.com/en-us/library/aa140059(office.10).aspx