RTD Function

Excel is the most popular spreadsheet software tool that helps businesses, and working professionals store, manage, structure, and analyze data efficiently. It helps to interpret data visually and intuitively by allowing charting and graphing of the data.

The multitude of features and functions offered by Excel significantly simplifies our workflow. These functions automatically perform complex mathematical, statistical, and arithmetic operations on the data, yielding valuable insights.

As a result, Excel proves to be an invaluable resource for data analysis and drawing meaningful conclusions, ultimately saving us time and effort. 

By leveraging the diverse functions within Excel, we eliminate the need for manual and cumbersome calculations, as the software performs these tasks seamlessly.

The RTD function retrieves real-time data from a program supporting COM automation.

Excel has a variety of functions that we have used, including financial, statistical, mathematical, and auditing capabilities. Which function depends on the use case and scenario we are working on?

In this article, we will discuss another category of functions in Excel called the Lookup and Reference category. These functions help users deal with data arrays and are especially useful when cross-referencing the different data sets provided. 

One of the functions belonging to this category is the RTD function.

What is the Real-Time Data RTD Function in Excel?

The acronym RTD stands for Real-Time Data. This function is a powerful Lookup and Reference function in Excel that allows users to retrieve real-time data from a program supporting COM automation.

COM automation is a process that enables the automatic execution of the tasks generally selected from menus. 

COM stands for Component Object Model. In the case of the RTD function, Excel utilizes COM automation to communicate with the corresponding automation server and retrieve real-time data.

For example, a small program could be written to extract data from a database provided, which could be pasted into a spreadsheet where the data can be charted, graphed, and summarized for detailed analysis without any manual intervention.

In financial analysis, the RTD function is important as it helps release values from the real-time data servers in our Excel spreadsheet. 

It is used to build reliable refreshable data sheets quickly and models using the cell values as function arguments.

By leveraging the RTD function derived from COM technology, users can enhance the robustness, reliability, and convenience of their work in Excel. This is especially beneficial when working with worksheets that require real-time data updates. 

The working of this function depends on the availability of an RTD server for Excel to access the real-time data.

If an RTD server is available, the RTD function retrieves data from the server to use in the Excel worksheet. Whenever the data is modified or new data is added from the server, the function result is updated so that the worksheet can extract and accept the data.

The RTD server updates the data only when Excel is idle. So the user does not have to determine whether Excel is available to accept the updates. 

This property distinguishes the RTD function from other Excel functions because other functions are updated when the worksheet is recalculated.

The formula for using the Real Time Data function in Excel is as follows:

=RTD(ProgID, ServerName, topic1, [topic2],....)

The terms in parentheses are called the arguments of the function. These are the values that the function requires to perform the computations and calculate the desired result. The arguments that the RTD function in Excel requires are:

1. ProgID

This is a required argument in the RTD function. It is a string argument that refers to the program ID of the RTD server on the local system. It is a COM add-in registered by a setup procedure. It must be enclosed within double quotes(“”).

2. ServerName

This is a required argument. It is a string argument that refers to the name of the server on which the RTD server is executed. 

If the server is executed locally within the system, this argument should be an empty string(“”) or may be left blank. The name should always be enclosed within double quotes.

3. Topic1, [Topic2],....

Here topic 1 is a required argument, while the successive topics are optional and can be omitted. These are the strings that determine the data to be retrieved. 

Examples of the RTD Function in Excel

Having looked at the theoretical concepts related to the RTD function in Excel, let us now see an example to understand the applications of this function practically.

Suppose we need to retrieve information from a server delivering continuous data for the total marks scored by students in different subjects. The server is pre-installed on the user’s computer and is registered as a COM add-in by an internal setup procedure. 

The general form of the RTD function used in this case is as follows:

=RTD(“ProgID”, “ServerName”, “StudentName”, “RollNum”, “SubCode”, “Marks”)

There are three topics in this case:

• The topic StudentName indicates the name of the student.
• The topic RollNum indicates the roll number of the student.
• The topic SubCode indicates the subject code of the subject whose marks we want to know.
• The topic Marks indicate the marks obtained by the particular student in a particular subject.

Assuming the server is pre-installed and executed on the local computer, we provide an empty string(“”) in the ServerName argument. 

To retrieve information about a student, the user may select any cell and input the following formula:

=RTD(“ExcelRTD.StudentReport”, “”, “John”, “23CH4468”, “HS8194”, “Marks_John”)

Here ExcelRTD.StudentReport is the program ID of the server provided. The above formula is used to see the marks in the subject with code HS8194 of a student named John, whose roll number is 23CH4468. 

The RTD function can retrieve numbers and any information from the dataset we want. Hence we can use the RTD function throughout the Excel worksheet to retrieve different data from the server. The data retrieved is updated automatically.

These user-defined functions act as wrappers for the RTD function. This method helps users shield themselves from the RTD server name and program ID details. It enables the user to focus only on the data important to them.

For example, we could have used the GetMarks(SubCode) in the above scenario. This function assumes that the user is dealing with the same server and student, intending to retrieve marks in different subjects of the same student. 

The user has to provide the subject code of the subject whose marks he wants to know to get the desired data.

Before using the RTD function, the RTD COM automation add-in must be installed on the local computer. 

The RTD servers are not synced with Microsoft Office, so a real-time data server must be manually installed to use the function. 

The RTD function continues to retrieve information even if Excel is in manual calculation mode, as the RTD server updates data when Excel is idle. 

The new data is retrieved in this case, and the current values are used in the manual calculations performed.

The RTD servers must be digitally signed. If an RTD server is not digitally signed, the server does not load properly, and the function shows a #N/A! Error in the cells referencing the RTD server.

Researched and Authored by Devang Shekhar | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• GETPIVOTDATA Function Excel
• NOW Function
• TODAY Function
• TIME Function
• WORKDAY Function