"Microsoft Excel provides a new worksheet function, RTD, that allows you to call a Component Object Model (COM) Automation server for the purpose of retrieving data real-time. This article describes how to use Visual Basic to create a RealTimeData Server for use with Excel's RTD function."
The interface for the COM server is described in the article as:
The complexity is (in my view) around three of these methods. RefreshData is called by Excel to PULL data into Excel. But Excel only calls RefreshData after a PUSH notification has been sent to Excel by the RTD Server. By way of analogy - the push notification is like receiving a postcard from the mailman telling you that there is a parcel waiting for you. When you are ready to collect the parcel, you call RefreshData. This hybrid push/pull model has benefits in that the single threaded user interface thread of Excel has to be respected. Remember that if you click F2 on a cell and it enters edit mode then the entire cell based user interface is locked. This means that code that sends data to Excel has to be aware of this reality and work within these constraints.
The other issue is around ConnectData/DisconnectData. Each time a new call to RTD is made from a worksheet the ConnectData method implemented in your RTD Server will be called. In C# this is created as:
IRtdServer.ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
So, the values in topic1...topic 253 will be based as strings within the Array. So potentially over time a large number of these calls will be made. Now, think about how you would handle this when two cells are identical. Let's take an edge case - I put the same formula into every cells in an Excel 2007 worksheet
The RTD Server code has to be able to handle this and not blow up. Hence the way that the ConnectData array is processed is critical to performance. If you tried to subscribe to the same market data element 17 billion times you'd probably find that it acts like a massive memory leak or it simply crashes Excel.
And the resolution suggested:
This seems reasonable but then pushes us to a different problem. If the way to resolve the issue is to pull the RTD Server into the Excel process space then we end up with multiple instances of the RTD server running. The problem with that model is that the code that sits behind the COM RTD Server is then duplicated for each RTD server instance. If that code connects to third party libraries such as TIB R/V, Solace, Tervela, Terracotta Universal Messaging, IBM MQ Series then you'd getting into a potential minefield. The solution is to make sure that you understand not just the threading model of your implementation language but also that of your middleware and COM. Hence why RTD Servers are easy to implement in a reference implementation but quite a bit harder in a field implementation.
|Excel 2007 RTD Function Wizard User Interface|
The above image shows the key parts of how to call an Excel RTD Server from the Excel user interface.
|Excel 2007 user interface suggested a maximum of 38 parameters|
Equally a realistic implementation could look like this:
This more subtle challenge is now becoming clearer - the namespace of the function needs to be defined in a sensible way. What does this mean?
- User documentation to show the user that, for example, to see the price/earnings ratio for a stock they need to put the instrument identifier in topic 1 and the string "p_e_ratio" in topic 75
- Helper functions written in VBA to simplify this - so a new function could be written as =Get_PERatio_ByRIC("VOD.L")
- The ConnectData/DisconnectData issues described above need to be addressed for a robust implementation.
- The RefreshData usage has to fit within the constraints of the Excel run-time as well as the RTD threading model and the 'back-end' threading model.
- The namespace model and/or helper user-defined-functions need to be documented fully.
- The subtleties of bridging from C# (or Java or whatever other implementation language)
to COM must be understood.
Further suggested reading: