Financial analysts live in Excel, and I’m no exception. Python (enhanced with pandas, numpy, etc.) is more powerful, but sometimes the convenience of having a complete data set exposed in Excel is worth the tradeoff. And modern Excel is strong enough to run a realtime trading dashboard. I’ve done that for years now. And a few years ago I discovered that it’s really strong if you use the right tools. One of those is Microsoft’s Real-Time Data (RTD) protocol, and even though that was introduced to Excel almost 25 years ago I only recently discovered it and began to unlock its power.
Before I moved my dashboard to RTD, I pulled positions and account values via DDE (Dynamic Data Exchange), which is a protocol introduced 40 years ago! Ancient doesn’t always mean obsolete, but in this domain DDE hit its limits long ago and has been (appropriately) deprecated by Microsoft. Before I abandoned it, getting DDE to reliably feed data into Excel required jumping through hoops. Just establishing a connection between Excel and the broker’s API required starting a separate Java bridge process. The spreadsheet receiving the data had to be running in its own Excel process because every DDE update would lock the GUI. And if you happened to touch that workbook at the wrong moment in its update cycle it would lose the connection or crash completely. So on top of the Excel dashboard I wanted to work in, I had to start a separate “feeder” Excel workbook and a Java bridge. And the result wasn’t literally realtime – data arrived in my dashboard when the feeder was able to push it, which could be as frequently as every 10 seconds – but that was adequate for my needs.
And kludgy. But that’s the way all Excel data feeds were: You had to shoehorn cell updates into a system not designed for continuous calculation. Anyone who has added a “volatile” function to a heavy workbook knows this peril – or gives up – because every update means a global recalculation. The countermeasures are familiar hacks: polling loops, event handlers, lots of Visual Basic, etc.
I first saw the convenience of RTD when I used it to stream market data from ThinkOrSwim. Every other data source used an Excel add-in. ThinkOrSwim just magically appeared when needed. =RTD("tos.rtd",, "LAST", "SPY") would update the last trade price of SPY multiple times per second (once Excel’s default two-second RTD throttle is dialed down). But how? Unlike add-ins, you couldn’t even tell it was available until you asked Excel for it. It never consumed a measurable amount of CPU, never blocked the GUI, never lagged, never crashed. And that function: start to type “=RTD” and Excel reveals it’s a native function. This was a realtime data feed mechanism built in to Excel!
Once I began to dig into RTD I couldn’t stop. The interface could hardly be more simple: an RTD service only has to implement six methods and register itself as a Windows component. The mechanism was elegant: Excel tells the RTD server what topics the user has requested (e.g., “the last trade price of SPY”), the RTD server tells Excel when there is new data on that topic, then Excel asks for the data as soon as it can receive it. This was the trick to avoid blocking or crashing Excel, while feeding it data updates as fast as possible.
Implementing an RTD server properly turns out to be trickier than one might expect: You have to be very careful about thread management and subscription accounting. And you have to get the RTD server talking to the data source through whatever API is on the other end. But I think it’s worth the trouble: My Excel trading dashboard now gets realtime data through virtually invisible servers that have withstood all my attempts to break them. For example, I streamed 3,000 stock quotes through the market close on a busy day, and Excel just kept working. Once I’ve seen how it can be done right, I can’t go back.