Excel RTD (Real Time Data Server)

Excel RTD (Real Time Data Server) COM Server:

Microsoft Office Excel provides a worksheet function, RealTimeData (RTD). This function enables you to call a Component Object Model (COM) Automation server to retrieve data in real time.

Syntax:

=RTD(ProgID,Server,String1,[String2],...)

The first argument, ProgID, represents the Programmatic Identifier (ProgID) of the RealTimeData server. The Server argument indicates the name of the machine on which the RealTimeData server is run; this argument can be a null string or omitted if the RealTimeData server is to run locally. The remaining arguments simply represent parameters to send to the RealTimeData server; each unique combination of these parameters represents one “topic,” which has an associated “topic id.” Parameters are case-sensitive. For example, the following illustrates calls to the RTD server that would result in three separate topic ids:

=RTD("ExcelRTD.RTDFunctions",,"AAA", "10")

=RTD("ExcelRTD.RTDFunctions",,"AAA", "5")

=RTD("ExcelRTD.RTDFunctions",,"aaa", "5")

When you have to create a workbook that includes data that is updated in real time, for example, financial data 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. The RTD function is based on COM technology and provides advantages in robustness, reliability, and convenience. RTD depends on the availability of an RTD server to make the real-time data available to Excel.

Set Up RTD Server Project in the Visual Studio using C#:

Considering that you are familiar with the requirements of COM and can create a Visual Studio project for a COM library and name it as “MyRtdServer” which implements “IRtdServer” interface

Reference Microsoft.Office.Interop.Excel, which includes, among other things, the definition of the required interface, IRtdServer. Of course, to make life easier, we will also want to include the namespace in our “using” list.

Code example:

// Library to Dictionary for topics
using System.Collections.Generic;
// Library Where guid, progID attributes live
using System.Runtime.InteropServices;
// Library having IRtdServer Interface
using Microsoft.Office.Interop.Excel;
//System timer library to get ticks
using System.Timers;
namespace MyRtdServer
{
	[ Guid("C6C35D89-B2F6-46F4-86A9-88DB0C36D93B"),
	ProgId("RtdServer.MyRtdServer"), ]
	[assembly:ComVisible(true)]
	public class MyRtdServer : IRtdServer
	{
		private IRTDUpdateEvent _callback;
		private Timer _timer;
		private int _topicId;
		public MyRtdServer()
		{
			//Constructor
		}
		public int ServerStart(IRTDUpdateEvent callback)
		{
			_callback = callback;
			_timer = new Timer();
			_timer.Elapsed += new ElapsedEventHandler(TimerEventHandler);
			_timer.Interval = 2000;
			return 1;
		}
		public void ServerTerminate()
		{
			if (null != _timer)
			{
				>_timer.Dispose();
				>_timer = null;
			}
		}
		public int Heartbeat()
		{
			return 1;
		}
		//Initial method gets called upon server run
		public object ConnectData(int topicId, ref Array strings, ref bool newValues)
		{
			_topicId = topicId;
			_timer.Start();
			return GetCurrentTime();
		}
		public void DisconnectData(int topicId)
		{
			_timer.Stop();
		}
		//Timer event tick handler
		private void TimerEventHandler(object sender, EventArgs args)
		{
			//UpdateNotify is called to inform Excel that new data are available
			//the timer is turned off so that if Excel is busy, the TimerEventHandler is not called repeatedly
			_timer.Stop();
			_callback.UpdateNotify();
		}
		//Bulk refresh action goes here
		public Array RefreshData(ref int topicCount)
		{
			object[,] data = new object[2, 1];
			data[0, 0] = _topicId;
			data[1, 0] = GetCurrentTime();
			topicCount = 1;
			_timer.Start();
			return data;
		}
		private string GetCurrentTime()
		{
			return "RTD Server Time: " + DateTime.Now.ToString("hh: mm:ss");
		}
	}
}

Note: its COM Library project hence requires your Visual Studio in Admin mode

Once you compile the project it will register “MyRtdServer.dll” in your system. Now you can access this library in your VBA code to generate your own User Defined functions or directly call using Excel.WorksheetFunction.RTD method where you will pass your server as a first parameter, leave second parameter empty and third parameter goes as your input and it will return current date time which is implemented under GetCurrentTime method.

in Excel Sheet try to type as:

=RTD(“RtdServer.MyRtdServer”,,”your input”)

You can call same COM in VBA code as well if you wish:

Public function myTestingServer(ParamArray Args()) As Variant
	Dim result As String
	result = Excel.WorksheetFunction.RTD("RtdServer.MyRtdServer", "", "yourParameter")
End Function

Note:

• The MyRtdServer class has attributes Class ID and ProgId that can be used by COM clients to locate the COM server. By default (if you don’t give registration-free COM), COM clients will locate the COM server by looking up either the ProgId (to find the CLSID) or the CLSID directly in the registry. Hence developer insures to run Visual Studio in Admin mode.
• Mark your assembly as being visible to COM as follows
• m_callback is needed to hold onto a reference to the callback interface provided by Excel. This interface is primarily used to let Excel know that new data is available
• m_timer is a timer used to periodically notify Excel via the callback interface. Its optional and developer can take call based on implementation
• m_topicId is used to identify the topic that Excel is “subscribing” to
• ServerStart is the first method called by Excel and is where we prepare the RTD server. In particular we set the callback member variable and prepare the timer. Notice that the timer is not yet enabled. If it returns 1 mean everything is working as expected
• ServerTerminate is called when Excel is ready to unload the RTD server. Here we simply release the timer
• ConnectData is called for each “topic” that Excel wants to “subscribe” to. It is called once for every unique subscription. This implementation assumes there will only be a single topic. ConnectData also starts the timer and returns an initial value that Excel can display. Developer is advised to put Refresh method to subscribe bulk data update.
• DisconnectData is called to tell the RTD server that Excel is no longer interested in data for the particular topic. In this case, we simply stop the timer to prevent the RTD server from notifying Excel of any further updates
• TimerEventHandler is called when the timer Tick event is raised. It stops the timer and uses the callback interface to let Excel know that updates are available. Stopping the timer is important since we don’t want to call UpdateNotify repeatedly otherwise excel may crash or may go unresponsive.
• RefreshData is called when Excel is ready to retrieve any updated data for the topics that it has previously subscribed to via ConnectData. This is a bulk job engine which saves time and provide efficient way to get bulk data from server and transfer to Excel. The data returned to Excel is an Object containing a two-dimensional array. The first dimension represents the list of topic IDs. The second dimension represents the values associated with the topic IDs.
• Heartbeat is called by Excel if it hasn’t received any updates recently in a try to determine whether your RTD server is still OK. Returning 1 indicates that everything is fine.

VB.Net code example: Add a class module and name it  RTDFunctions.

Option Explicit

Implements IRtdServer  'Interface allows Excel to contact this RealTimeData server

Private m_colTopics As Collection

Private Function IRtdServer_ConnectData(ByVal TopicID As Long, Strings() As Variant, GetNewValues As Boolean) As Variant
    '** ConnectData is called whenever a new RTD topic is requested

'Create a new topic class with the given TopicId and string and add it to the
    'm_colTopics collection
    Dim oTopic As New Topic
    m_colTopics.Add oTopic, CStr(TopicID)
    oTopic.TopicID = TopicID
    oTopic.TopicString = Strings(0)
    If UBound(Strings) >= 1 Then oTopic.SetIncrement Strings(1)

'For this example, the initial value for a new topic is always 0
    IRtdServer_ConnectData = oTopic.TopicValue

Debug.Print "ConnectData", TopicID
End Function

Private Sub IRtdServer_DisconnectData(ByVal TopicID As Long)
   '** DisconnectData is called whenever a specific topic is not longer needed

'Remove the topic from the collection
   m_colTopics.Remove CStr(TopicID)

Debug.Print "DisconnectData", TopicID
End Sub

Private Function IRtdServer_Heartbeat() As Long
    '** Called by Excel if the heartbeat interval has elapsed since the last time
    '   Excel was called with UpdateNotify.
    Debug.Print "HeartBeat"
End Function

Private Function IRtdServer_RefreshData(TopicCount As Long) As Variant()
    '** Called when Excel is requesting a refresh on topics. RefreshData will be called
    '   after an UpdateNotify has been issued by the server. This event should:
    '   - supply a value for TopicCount (number of topics to update)
    '   - return a two dimensional variant array containing the topic ids and the
    '     new values of each.

Dim oTopic As Topic, n As Integer
    ReDim aUpdates(0 To 1, 0 To m_colTopics.Count - 1) As Variant
    For Each oTopic In m_colTopics
        oTopic.Update
        aUpdates(0, n) = oTopic.TopicID
        aUpdates(1, n) = oTopic.TopicValue
        n = n + 1
    Next
    TopicCount = m_colTopics.Count
    IRtdServer_RefreshData = aUpdates

Debug.Print "RefreshData", TopicCount & " topics updated"
End Function

Private Function IRtdServer_ServerStart(ByVal CallbackObject As Excel.IRTDUpdateEvent) As Long
    '** ServerStart is called when the first RTD topic is requested

Set oCallBack = CallbackObject
    Set m_colTopics = New Collection
    g_TimerID = SetTimer(0, 0, TIMER_INTERVAL, AddressOf TimerCallback)
    If g_TimerID > 0 Then IRtdServer_ServerStart = 1       'Any value <1 indicates failure.

Debug.Print "ServerStart"
End Function

Private Sub IRtdServer_ServerTerminate()
    '** ServerTerminate is called when no more topics are needed by Excel.

KillTimer 0, g_TimerID

'** Cleanup any remaining topics. This is done here since 
    '   IRtdServer_DisconnectData is only called if a topic is disconnected 
    '   while the book is open. Items left in the collection when we terminate
    '   are those topics left running when the workbook was closed.

Dim oTopic As Topic
    For Each oTopic In m_colTopics
        m_colTopics.Remove CStr(oTopic.TopicID)
        Set oTopic = Nothing
    Next

Debug.Print "ServerTerminate"

End Sub

Add another class module. Change the class module Name property to Topic and change the Instancing property to Private. Add the following code to the Topic class module:

Option Explicit

Private m_TopicID As Long
Private m_TopicString As String
Private m_Value As Variant
Private m_IncrementVal As Long

Private Sub Class_Initialize()
    m_Value = 0
    m_IncrementVal = 1
End Sub

Friend Property Let TopicID(ID As Long)
    m_TopicID = ID
End Property

Friend Property Get TopicID() As Long
    TopicID = m_TopicID
End Property

Friend Property Let TopicString(s As String)
    s = UCase(s)
    If s = "AAA" Or s = "BBB" Or s = "CCC" Then
        m_TopicString = s
    Else
        m_Value = CVErr(xlErrValue) 'Return #VALUE if not one of the listed topics
    End If
End Property

Friend Sub Update()
    On Error Resume Next 'the next operation will fail if m_Value is an error (like #NUM or #VALUE)
    m_Value = m_Value + m_IncrementVal
End Sub

Friend Sub SetIncrement(v As Variant)
    On Error Resume Next
    m_IncrementVal = CLng(v)
    If Err <> 0 Then
        m_Value = CVErr(xlErrNum) 'Return #NUM if Increment value is not numeric
    End If
End Sub

Friend Property Get TopicValue() As Variant
    If Not (IsError(m_Value)) Then
        TopicValue = m_TopicString & ": " & m_Value
    Else
        TopicValue = m_Value
    End If
End Property

Add Module. Add the following code to the new module:

Public Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, _
ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long

Public Const TIMER_INTERVAL = 5000
Public oCallBack As Excel.IRTDUpdateEvent
Public g_TimerID As Long

Public Sub TimerCallback(ByVal hWnd As Long, ByVal uMsg As Long, ByVal idEvent As Long, ByVal dwTime As Long)
    oCallBack.UpdateNotify
End Sub

Build project to generate ExcelRTD.dll to build the component.

Use the RTD Server in Excel:

1. Start a new workbook in Microsoft Excel.
2. In cell A1, enter the following formula, and then press the ENTER =RTD(“ExcelRTD.RTDFunctions”,,”AAA”, 5) The initial return value is “AAA: 0”. After five seconds, the value updates to “AAA: 10” and after 10 seconds, the value updates to “AAA:15,” and so on.
3. In cell A2, enter the following formula and press ENTER =RTD(“ExcelRTD.RTDFunctions”,,”BBB”, 3) The initial return value is “BBB: 0”. Every five seconds the cell value increments by 3.

Next>> DataTable

Share on Facebook

Tweet