Excel Development: Five Excel RTD Functions

The UDF functions in Excel are described above. This article describes the RTD functions that are equally important. Starting from Excel 2002, Excel introduced a new mechanism for viewing and updating real-time data, namely, real-time data (RTD function), which is a Push-Pull method, when you need to update the data, RTD pushes a message to Excel saying that you want to update the data. After receiving the message, Excel actively pulls new data from Pull. The RTD function was initially used to update real-time changes, such as real-time stock quotation data, real-time weather forecast data, and team score data.

In the past, to implement these functions, we needed to rely on other technologies such as Dynamic Data Exchange (DDE) to access real-time Data resources. However, DDE is very different from the standard Excel function styles, in addition, it is not designed to obtain real-time data for Excel. It lacks robustness and is inefficient. The introduction of RTD solves these problems.

This article first introduces some common use cases of RTD, the basic structure of the RTD function, precautions, and finally demonstrates how to use the RTD function to obtain real-time market data from the Google Financial API.

The RTD function is very useful. If you encounter the following situations, you should consider using the RTD function:

The Excel RTD function is a Com component that implements the IRtdServer interface. Excel interacts with real-time data through the Com component. To implement the RTD function, you must implement the IRtdServer interface, which is located in the Microsoft. Office. Interop. Excel namespace. Jump to the definition and you can see the interface:

[()][(4160)]{    [(11)]    ConnectData(TopicID, Strings, GetNewValues);    [(13)]    DisconnectData(TopicID);    [(14)]    Heartbeat();           [(12)]    RefreshData(TopicCount);           [(10)]    ServerStart(CallbackObject);             [(15)]    ServerTerminate();}

The ServerStart parameter contains the IRTDUpdateEvent interface, which is implemented as follows:

[()][(4160)]{    [(11)]    HeartbeatInterval { ; ; }    [(12)]    Disconnect();    [(10)]    UpdateNotify();}

The comment on the very hard interface is very clear, but here we will explain it one by one. First, let's look at the IRtdServer interface. Here we will explain it in the general execution order:

• ServerStart method. This method is the first method to be executed when the RTD topic is changed after Excel is started. The return value of this method is 1, indicating that the RTD topic is started normally, and the return value is 0 or negative, indicating failure, other RTD functions of the same topic will not be executed. The only parameter of the method is IRTDUpdateEvent type. This object is used to notify Excel that my new data is ready and refreshed. Then, Excel will call the RefreshData method, we will perform some initialization operations in this method class. The most common is to create a local variable of IRTDUpdateEvent and assign this parameter to this local variable to facilitate subsequent notifications of refreshing data in Excel.
• The ConnectData method is executed when you open a document containing the RTD function or enter an RTD function in the cell. The first parameter of this method is TopicID, which uniquely identifies the RTD function in the cell. It is automatically allocated by Excel. In some cases, we need to define our own data structure, record TopicID, And then refresh the specified TopicID cell. The second parameter is an Array-type String object. Generally, this parameter is used to pass the required parameter information in RTD. For example, if we want to compile the RTD function for real-time quotations, the parameters such as the stock code and Indicator Name (Open and Open) are passed in as arrays. The third parameter is the Bool-type GetNewValues, which is used to determine whether the latest data is always obtained. If this parameter is set to true, each time the Excel document is saved and re-opened, what we see is not necessarily the data stored last time, but the latest real-time data, which is also a reference type parameter. If this parameter is not set, the data of the last request is displayed when the table containing the RTD is opened again.
• Heartbeat method. Excel calls this method to determine whether the RTD Server service is available. 0 and negative numbers indicate unavailability, 1 indicates availability, and 1 is usually returned directly.
• The RefreshData method. The TopicCount parameter indicates the number of topics to be updated, which is used to return to Excel. The return value of this method is a two-dimensional array. The first dimension is the TopicID of the data to be updated, that is, the TopicID in the ConnectData method. The second dimension is the value obtained corresponding to the TopicID. When new data is obtained, call the UpdateNotify method of the IRTDUpdateEvent type object passed in by ServerStart to notify Excel that new data needs to be updated. Then, Excel will call the RefreshData method, then, the two-dimensional data containing the TopicID is returned to update the data in the workbook.
• DisconnectData method, which corresponds to ConnectData,TopcIDUniquely identifies an application of this function in Excel. After we remove a topic from Excel (delete all RTD functions with the same parameters), DisconnectData will be called. Here, we can release monitoring on this topic, instead of getting new data.
• It is called when Excel no longer needs to obtain real-time data from RTD Server. Here, you can perform some cleanup operations, such as clearing the cache and disabling the clock. So far, the lifecycle of An RTD Server is over.

The above interface has been introduced. Now we will introduce the IRTDUpdateEvent interface. An important method of this interface is the updatenoworkflow method. This method sends a notification to Excel, prompting that new data needs to be updated. This is where Excel will call the RefreshData method to read the updated data. Note that the UpdateNofity method must be called in the Excel main thread.

The above briefly explains the basic principles of the RTD function. Next we will demonstrate how to use the Excel RTD to obtain real-time quotations from the Google Fiancial API and refresh the data. Google Financal API, it provides real-time market data for major exchanges around the world. The supported market and timeliness are described on its official website. You can refer to this article for the usage method. The main idea is as follows. First, we define the request parameters. There are two real-time market request parameters: stock code and Indicator Name. Because we request http instead of registering the Event Callback method, we need to use the timer control in RTD to actively pull the request. After the request is processed, the results are stored in the object, then, call the UpdateNotify method to notify Excel to update. The RTD function is actually a class library registered as a Com component, so we first create a class library named YYGoogleFinancialRTD:

{StockCode {;}index {;}topicid {;}value {;;}}

3.2 Create RTD

How to create the RTD function is the focus of this Article. Here we first create a new class named FinancialRtd, and then let it implement the IRtdServer interface. Use VS to automatically complete the five methods. Like writing udfs using. NET, we need to add some custom attributes to the class name. Among the four attributes, A ProgID attribute is added compared with the UDF, and the unique identifier of this attribute is changed to the RTD function.

[()][()][(.AutoDual)][()]: {    xlRTDUpdate;    tmrTimer;    <> stockDatas;    gfinancial = ;}

Before implementing the five methods, we need to define some local variables. The first variable is the xlRTDUpdate object, which is used to save references to the objects passed in ServerStart, to call the updatenoworkflow method of the object later. The Timer control is used to regularly obtain real-time market data from the http interface. The List <RealStockData> object is used to save all requests and their return values. The Gfinancial object is used to obtain real-time quotations from the Google Financial API. Since the current transaction time is no longer available, I added a Random on the basis of the closing price to demonstrate real-time changes. You can see this in the downloaded code. I will not talk about it here. After defining these variables, we can start coding.

The first method to be implemented is the ServerStart method. In this method, we save the CallbackObject object to the defined local variable for future calling of the updatenoworkflow method of this object. Then, we initialized the Timer object and set it to 2 S to obtain real-time market data. 1 indicates that the RTD service is enabled normally. The Elapsed method of timer is described later.

ServerStart(CallbackObject){    xlRTDUpdate = CallbackObject;    gfinancial = ();    tmrTimer = ();    tmrTimer.Interval = 2000;    tmrTimer.Elapsed += tmrTimer_Elapsed;    1;}

The second important method to be implemented is the ConnectData method:

ConnectData(TopicID, Strings, GetNewValues){    GetNewValues = ;    strStockCode = Strings.GetValue(0).ToString().ToLower();    strIndex = Strings.GetValue(1).ToString();    {        (stockDatas == )        {            stockDatas = <>();        }        temp = ();        temp.StockCode = strStockCode;        temp.Index = strIndex;        gfinancial.GetRealStock(temp);        (temp.Value != )        {            stockDatas.Add(temp);        }    }    {        ;    }    (!tmrTimer.Enabled)    {        tmrTimer.Start();    }    (i = 0; i < stockDatas.Count; i++)    {        (stockDatas[i].StockCode.Equals(strStockCode, .OrdinalIgnoreCase) && stockDatas[i].Index.ToString().Equals(strIndex, .OrdinalIgnoreCase))        {            (stockDatas[i].TopicId == -1)            {                stockDatas[i].TopicId = TopicID;            }            stockDatas[i].Value;        }    }    ;}

In this method, we first parse the passed parameters. According to the previous conventions, the first parameter is the stock code, and the second parameter is the indicator name. Then we instantiate a RealStockData object, assign values to the relevant StockCode and Index of the object, and then request a real-time quote for the request, and store the Value in the Value Attribute of the object. Then load the request to the List set object. Finally, the system cyclically checks whether the object exists. If the object already exists, it returns the value directly. Otherwise, the TopicID allocated to the Excel file of the request is saved to the TopicId object of the object. For later use. If the input parameters do not meet the requirements, the system prompts that the user request format is incorrect. Each cell request only executes this method once. Later, the update is implemented through the refresh mechanism.

For continuity, the Elapse method of timer is introduced. The implementation of this method is as follows:

tmrTimer_Elapsed(sender, e){    gfinancial.GetRealStock(stockDatas);    xlRTDUpdate.UpdateNotify();}

This method is very simple. The first sentence is to talk about all the requests in the List set that we saved before, the real-time quotations of the previous requests, and the returned values are saved to the values of each element, then, call the UpdateNotify method to notify Excel that the new data Value has been obtained, and the new data Value is stored in the Value Attribute of each element of stockDatas. When Excel receives the UpdateNotify method, it will go back and call the RefreshData method. The implementation of this method is as follows:

RefreshData(TopicCount){    [,] rets = [2, stockDatas.Count];    counter = 0;    (data stockDatas)    {        (data.TopicId != -1)        {            rets[0, counter] = data.TopicId;            rets[1, counter] = data.Value;        }        counter++;    }    TopicCount = stockDatas.Count;    rets;}

This method is simple. First, we create a two-dimensional array. The second-dimensional size is the number of all valid requests. Here, the number of several stockDatas elements.

Then, traverse all requests and fill in the two-dimensional array. The value of the first dimension is TopicID. Excel updates the corresponding cell through this ID, and the second dimension is the value of the cell, fill the value in the corresponding TopicID. In addition, we also need to notify Excel of the number of cells to be refreshed. This number is of course the number of all requests. Finally, we return the two arrays.

At this point, the most important methods have been introduced.

The DisconnectData method corresponding to the ConnectData method is triggered when we delete the RTD function we previously entered in Excel. The method is implemented as follows, the operation is to remove the request corresponding to the TopicID from all our request sets.

DisconnectData(TopicID){    (i = stockDatas.Count - 1; i > 0; i--)    {        (stockDatas[i].TopicId == TopicID)        {            stockDatas.RemoveAt(i);        }    }    ((stockDatas == || stockDatas.Count == 0) && tmrTimer.Enabled)    {        tmrTimer.Stop();    }}

The Heartbeat method returns only 1, indicating that our RTD is still running.

Heartbeat(){    1;}

The ServerTerminate method corresponding to the ServerStart method is triggered when the RTD worksheet is closed. This method is mainly used to release resources. Its implementation is as follows:

ServerTerminate(){    xlRTDUpdate = ;    (tmrTimer.Enabled)    {        tmrTimer.Stop();    }    tmrTimer.Elapsed -= (tmrTimer_Elapsed);    tmrTimer.Dispose();}

So far, our function has been compiled.

After that, we need to register the class library as a Com component, just like the UDF function. During Visual Studio compilation, we can check to register as a Com component, on Windows 7 and later systems, registering Com components involves entering the Registry. Therefore, you need to run the current Visual Studio as administrator.

This article introduces a very important function and RTD function in Excel. It is a Push-Pull data update mechanism provided by Excel, there are many applications that require high real-time data, such as live video competition scores, real-time weather forecasts, and real-time exchange quotations. In addition, through the RTD mechanism, we can implement asynchronous UDF functions on this basis, that is, when the request is sent, we will not process it for now, write down the TopicID, and put it in the processing queue, after processing is complete, call the updatenoworkflow method and refresh the processed TopicID cell. The following describes asynchronous udfs in Excel. asynchronous udfs can greatly improve the user experience of Excel plug-ins and improve the scalability and stability of the system.

Click here to download all the code in this article. I hope this article will help you understand the RTD function in Excel.