SUMMARY
This step-by-step article discusses the new functionality that the RealTimeData (RTD) function provides. Microsoft Excel 2002 provides a new worksheet function, RealTimeData (RTD), which allows you to call a Component Object Model (COM) Automation server for the purpose of retrieving data in real time.
When you need to create a workbook that includes data that is updated in real time -- for example, financial or scientific data -- you can now use the RTD worksheet function. In earlier versions of Excel, Dynamic Data Exchange (DDE) is used for that purpose. However, the RTD function, which is based on COM technology, provides advantages in terms of robustness, reliability, and convenience. RTD depends on the availability of an RTD server to make the real-time data available to Excel. For more information about how to create an RTD server, see the "References" section of this article.
The RTD function retrieves data from an RTD server for use in the workbook. The function result is updated whenever new data becomes available from the server and the workbook is able to accept it. The server waits until Excel is idle before updating, which relieves the developer of having to determine whether Excel is available to accept updates. The RTD function differs from other functions in this regard, because other functions are updated only when the worksheet is recalculated.
Although the RTD function provides a link to data on a server, it is not the same type of link as references to cells in other worksheets or workbooks. For example, if you use the RTD function in a workbook, you do not receive the Links startup message when you open the workbook, nor can you manage the status of an RTD function through the
Edit Links dialog box.
back to the top
Syntax
The RTD function uses the following syntax
=RTD(RealTimeServerProgID,ServerName,Topic1,[Topic2], ...)
where the function arguments are as follows:
- RealTimeServerProgID
A string that represents the Program ID of the RTD server installed on the local system. This is typically a COM add-in that is registered by a setup procedure or by using RegSvr32. RealTimeServerProgID is a required argument. - ServerName
A string that represents the name of the server on which the RTD server is to be run. If the RTD server is run locally, the ServerName should be an empty string ( "") or omitted. - Topic1, [Topic2], ...
Strings that identify the data being retrieved. You can use from one to 28 topics, although only one topic is required.
back to the top
Using RTD
As an example of the RTD function, consider retrieving information from a server that delivers continuous data for runners in several simultaneous races. Assume that the server is provided as a Dynamic Link Library (DLL) named RaceReport.dll, that it is installed on the user's computer, and that it is registered as a COM add-in by its own setup process. The general form of the RTD function as used with this server might be as follows
=RTD("MyRTDServerProdID","MyServer","RaceNum","RunnerID","StatType")
where the topics RaceNum, RunnerID, and StatType identify the race of interest, the runner whose data is required, and the type of data for a particular use of the function, respectively.
Here the user might select a cell and type the following formula in it
=RTD("ExcelRTD.RaceReport","","2","16","Time")
to see the current elapsed time for runner number 16 in race number 2.
The number of topics and the nature of the associated data is determined by the developer of the COM server. For example, the race-reporting server might deliver different data if you use the following RTD function:
=RTD("ExcelRTD.RaceReport","","3","25","Position")
In this case, the data returned indicates the current position of runner number 25 in race number 3.
In this manner, the same function can be used differently throughout the workbook to retrieve different data from the same server, all of which is updated automatically.
back to the top
Wrappers to Simplify Use
The RTD function can be used inside User Defined Functions (UDFs) in Microsoft Visual Basic for Applications, which then become wrappers for the RTD function. This approach can be useful in shielding users from the details of the RTD server name and ProgID, and in allowing them to focus only on the data that is important to them.
For example, in the preceding scenario, the user might be provided with two functions: GetTime(RunnerID) and GetPosition(RunnerID). These functions assume that the user is always dealing with the same server and race number, and therefore the user only has to specify a runner's ID to receive current data on the runner's elapsed time and position, respectively.
back to the top
Calculation
Because RTD updates data when Excel is idle, it continues to receive information if Excel is in manual calculation mode. In that event, the new data is cached and the current values are used when a manual calculation is performed.
back to the top
Security
The RTD server is regarded as a macro by Excel's security features. This functionality means that if your security is set to
High, the RTD server will not be available unless it is digitally signed and trusted, and those cells that depend on its data will display the following:
If your security is set to
Medium, you receive the following error message when you open a workbook (
file name) that uses the RTD function:
file name contains macros.
Macros may contain viruses. It is always safe to disable macros, but if the macros are legitimate, you might lose some functionality.
back to the top