RTD Server
Introduced in Excel 2002
An RTD server is a COM Automation server that implement the IRTDServer interface.
Designed to replace DDE for updating spreadsheets in real-time.
Unlike XLLs, RTD automation servers can pull data from the server automatically when it changes.
This allows asynchronous calls which means that the user is not interrupted while the function is calculating.
The RTD server is instantiated by Excel when a user enters a new function RTD Function into Excel and specifies the RTD server's programmatic ID (ProgID).
Once the server is instantiated, Excel gives it a reference to a callback object and communication occurs via a push-pull mechanism.
The RTD server notifies Excel that data has changed (push) and, when appropriate, Excel requests new data from the RTD server (pull).
This push-pull architecture allows for RTD to function even if there is a modal dialog box displayed or something else happening that would normally prevent an update.
link - learn.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140061(v=office.10)
link - learn.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140059(v=office.10)
link - learn.microsoft.com/en-gb/previous-versions/office/developer/office-xp/aa140060(v=office.10)
link - learn.microsoft.com/en-gb/previous-versions/office/troubleshoot/office-developer/create-realtimedata-server-in-excel
link - learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function
Creating
Open Visual Studio 2022 as administrator.
New Project, C#, Windows, Library (Class Library .NET Framework).
Change the Project Name to "ExcelRTDServer".
Change the Location to somewhere on your C drive.
Place the solution and project in the same directory.
Check the .NET Framework version is 4.8 and press Create.
Rename the Class1.cs file to MyConnect.cs.
Add four references to this project, (Project > Add Reference).
(1) Assemblies, Extensions: "Extensibility"
(2) Assemblies, Extensions: "Microsoft.Office.Interop.Excel" (version 15)
(3) Assemblies, Framework: "System.Windows.Forms"
(4) COM, Type Libraries: "Microsoft Office 16.0 Object Library"
Remove the existing (default) class.
Add the IMyConnect Interface
Add the following interface to the MyConnect.cs file.
namespace ExcelRTDServer
{
[System.Runtime.InteropServices.Guid("662ac7cd-65d9-492f-89f1-cf37e021a6ab")]
[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsDual)]
[System.Runtime.InteropServices.ComVisible(true)]
public interface IMyConnect : Extensibility.IDTExtensibility2
{
new void OnConnection(
object Application,
Extensibility.ext_ConnectMode ConnectMode,
object AddInInst,
ref System.Array custom);
new void OnDisconnection(
Extensibility.ext_DisconnectMode RemoveMode,
ref System.Array custom);
new void OnAddInsUpdate(ref System.Array custom);
new void OnStartupComplete(ref System.Array custom);
new void OnBeginShutdown(ref System.Array custom);
}
This interface is the standard IDTExtensibility2 interface used for COM Add-ins.
This will allow us to display a message box when the add-in loads.
Add the MyConnect Class and Methods
Add the following class underneath the interface in the MyConnect.cs file.
This class needs to inherit from the IMyConnect interface and the IRtdServer interface.
[System.Runtime.InteropServices.ProgId("ExcelRTDServer.MyConnect")]
[System.Runtime.InteropServices.ComDefaultInterface(typeof(IMyConnect))]
[System.Runtime.InteropServices.Guid("4ba33ea4-8bfe-4450-9022-342e99d2fe81")]
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)]
[System.Runtime.InteropServices.ComVisible(true)]
public class MyConnect : IMyConnect, Microsoft.Office.Interop.Excel.IRtdServer
{
Microsoft.Office.Interop.Excel.Application _ApplicationObject;
Microsoft.Office.Core.COMAddIn _AddinInstance;
public void OnConnection(
object Application,
Extensibility.ext_ConnectMode ConnectMode,
object AddInInst,
ref System.Array custom)
{
this._ApplicationObject = (Microsoft.Office.Interop.Excel.Application)Application;
if (this._AddinInstance == null)
{
this._AddinInstance = (Microsoft.Office.Core.COMAddIn)AddInInst;
this._AddinInstance.Object = this;
}
System.Windows.Forms.MessageBox.Show("onConnection");
}
public void OnDisconnection(
Extensibility.ext_DisconnectMode RemoveMode,
ref System.Array custom)
{
this._ApplicationObject = null;
this._AddinInstance = null;
}
public void OnAddInsUpdate(ref System.Array custom)
{ }
public void OnStartupComplete(ref System.Array custom)
{ }
public void OnBeginShutdown(ref System.Array custom)
{ }
Additional Private Fields
Add the following three private variables underneath the OnBeginShutdown method (still inside the MyConnect class).
We need to have a way to keep track of all of the MyRTDData classes that are created, for this we will use a Generic Dictionary.
private Microsoft.Office.Interop.Excel.IRTDUpdateEvent m_xlRTDUpdate;
private System.Timers.Timer m_tmrTimer;
private System.Collections.Generic.Dictionary<string, MyRTDData> m_colRTDData;
Add the IRtdServer Methods
Add the following methods underneath the private fields (still inside the MyConnect class).
When the addin loads and initialises, the ServerStart method is called.
The ServerStart method is called the first time the Excel RTD function is used.
This stores the Excel callback object and initialises the timer.
Everytime the timer elapses event_TimerElapsed will be called.
int IRtdServer.ServerStart(Microsoft.Office.Interop.Excel.IRTDUpdateEvent CallbackObject)
{
System.Windows.Forms.MessageBox.Show("ServerStart");
// Hold a reference to the callback object.
m_xlRTDUpdate = CallbackObject;
// Create the time with a 2000 millisecond interval.
m_tmrTimer = new System.Timers.Timer(2000);
m_tmrTimer.Elapsed += event_TimerElapsed;
m_tmrTimer.AutoReset = true;
m_colRTDData = new System.Collections.Generic.Dictionary<string, MyRTDData>();
return 1;
}
The TimerElapsed event will be triggered every 2 seconds.
This calls the UpdateNotify method on the Excel callback object to tell Excel that is can pull new data.
private void event_TimerElapsed(object source, System.Timers.ElapsedEventArgs e)
{
// System.Windows.Forms.MessageBox.Show("event_TimerElapsed");
// Create a shared randomizer.
System.Random rdmRandomizer;
rdmRandomizer = new System.Random();
// Call update for each stock quote.
foreach (MyRTDData objRTDData in m_colRTDData.Values)
{
objRTDData.Update(rdmRandomizer);
}
// Tell Excel that we have updates.
m_xlRTDUpdate.UpdateNotify();
}
The ServerTerminate method is called when the connection to the real time server is disconnected.
void IRtdServer.ServerTerminate()
{
System.Windows.Forms.MessageBox.Show("ServerTerminate");
// Clear the RTDUpdateEvent reference.
m_xlRTDUpdate = null;
// Make sure the timer is stopped.
if (m_tmrTimer.Enabled == true)
{
m_tmrTimer.Stop();
}
m_tmrTimer = null;
}
The ConnectData method is called when a new RTD formulas is added.
dynamic IRtdServer.ConnectData(int TopicID, ref System.Array StringsArray, ref bool GetNewValues)
{
System.Windows.Forms.MessageBox.Show("ConnectData");
string strValue;
string strTicker;
MyRTDData objRTDData;
// Make sure that the timer is started.
if (m_tmrTimer.Enabled == false)
{
m_tmrTimer.Start();
}
GetNewValues = true;
try
{
strTicker = StringsArray.GetValue(0).ToString(); // First argument is the ticker.
strValue = StringsArray.GetValue(1).ToString().ToLower(); // Second argument is the type.
if (strValue == "last")
{
//Check if the data object was created.
try
{
objRTDData = m_colRTDData[strTicker];
}
catch
{
objRTDData = new MyRTDData(strTicker); // Item wasn't found - create.
m_colRTDData.Add(strTicker, objRTDData); // Add to collection.
}
if (objRTDData.TopicID == -1)
{
objRTDData.TopicID = TopicID; // We want this one's topic ID for later updates.
}
return objRTDData.Last;
}
if (strValue == "open")
{
//Check if the data object was created.
try
{
objRTDData = m_colRTDData[strTicker];
}
catch
{
objRTDData = new MyRTDData(strTicker); // Item wasn't found - create.
m_colRTDData.Add(strTicker, objRTDData); // Add to collection.
}
return objRTDData.Open;
}
return "Unrecognized value requested";
}
catch
{
return "ERROR IN QUOTE";
}
}
The DisconnectData method is called when a topic is not longer in use.
void IRtdServer.DisconnectData(int TopicID)
{
System.Windows.Forms.MessageBox.Show("DisconnectData");
//User no longer wants the quote.
//Loop over the quotes and try to find it.
foreach (MyRTDData objRTDData in m_colRTDData.Values)
{
if (objRTDData.TopicID == TopicID)
{
m_colRTDData.Remove(objRTDData.Ticker);
}
}
//Stop the timer if we are done.
if ((m_colRTDData.Count == 0) && (m_tmrTimer.Enabled == true))
{
m_tmrTimer.Stop();
}
}
The Heartbeat method is called to check that the real time server is still active.
int IRtdServer.Heartbeat()
{
System.Windows.Forms.MessageBox.Show("Heartbeat");
return 1;
}
The RefreshData method is called whenever new data needs to be pulled.
The data returned to Excel (from RefreshData) is a two-dimensional array of topic id and value.
System.Array Microsoft.Office.Interop.Excel.IRtdServer.RefreshData(ref int TopicCount)
{
//System.Windows.Forms.MessageBox.Show("RefreshData");
int intItemCount = 0;
object[,] aRetVal;
aRetVal = new object[2, m_colRTDData.Count];
for (int i = 0; i < m_colRTDData.Count; i++)
{
MyRTDData curItem;
curItem = m_colRTDData.Values.ToArray()[i];
if (curItem.TopicID != -1)
{
// Update the topic with the latest value.
aRetVal[0, i] = curItem.TopicID;
aRetVal[1, i] = curItem.Last;
intItemCount += 1;
}
}
// Tell Excel how many topics we updated.
TopicCount = intItemCount;
return aRetVal;
}
}
Add the MyRTDData Class
Create the following data storage class underneath the MyConnect class.
public class MyRTDData
{
//Private variables for the properties.
private string m_strTicker;
private double m_sngLast;
private double m_sngOpen;
private int m_intTopicID = -1;
public MyRTDData(string NewTicker)
{
System.Random rdm = new System.Random(); // This generates random numbers
m_strTicker = NewTicker; // Set ticker and topic ID.
m_sngOpen = rdm.NextDouble() * 100; // Use a random number for the opening price.
m_sngLast = m_sngOpen; // Set the last price to the opening price.
}
public string Ticker
{
get { return m_strTicker; }
}
public double Last
{
get { return m_sngLast; }
}
public double Open
{
get { return m_sngOpen; }
}
public int TopicID
{
get { return m_intTopicID; }
set { m_intTopicID = value; }
}
public void Update(System.Random rdm)
{
double sngPriceChange;
sngPriceChange = rdm.NextDouble() * 5;
if (rdm.Next(1, 10) < 5)
{
m_sngLast -= sngPriceChange; // Drop in price
}
else
{
m_sngLast += sngPriceChange; // Increase in price
}
}
}
}
Make sure the 2 Guid Ids are replaced using (Tools > Create GUID).
Select (Build > Build Solution).
At this point no registry entries are added in the registry.
Select (Project > Properties).
Display the Build tab.
Tick the "Register for COM Interop" checkbox (requires admin rights).
Close the Properties window.
Select (Build > Build Solution).
When the solution is built a "TLB" file appears in the "\bin\Debug\" folder.
When the solution is built the RegAsm tool automatically runs.
The RegAsm tool adds the necessary COM Interop class and interface information to the registry.
Registry Keys - COM Add-in
Microsoft Office COM add-ins are identified by reading from the registry.
You can view the registry by going to the Windows start menu, choosing Run, typing regedit and pressing OK.
There must be a key representing the COM Add-in under the Addins key:
64 Bit Office - HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\
32 Bit Office - HKEY_CURRENT_USER\Software\Wow6432Node\Microsoft\Office\Excel\Addins\
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ExcelRTDServer.MyConnect
(String)(Default) - (value not set)
(String) Description - A Long Description
(String) FriendlyName - ExcelRTDServer
(DWord) LoadBehavior - 2
Description - A brief description of the add-in.
FriendlyName - A user friendly name that is displayed in the COM add-ins dialog box.
Load Behavior - The load behaviour of the add-in.
Load the COM Add-in
When the add-in loads the following message box is displayed
SS - onconnection
Calling the RTD Server
You can use the Excel worksheet function RTD function to return data into Excel.
Enter the following formula into Excel.
=RTD("ExcelRTDServer.MyConnect", , "MSFT", "Last")
and press Enter
The following message box is displayed
SS - serverstart
Check that your calculation is set to Automatic.
You should see the number automatically change every 2 seconds.
Delete this function from the cell (or close Excel).
The following message box is displayed
SS - serverterminate
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext