RealTimeData

Traders, operations teams and plant managers often live in Excel. When they need live numbers — prices, machine readings, order counts — copying values in by hand or refreshing a query every minute is not good enough. Excel's Real-Time Data (RTD) mechanism lets a COM server push updates straight into cells.

How RTD works

In a worksheet, a user writes:

=RTD("Cloudsect.LiveFeed", , "ORDERS", "LUCKNOW")

The first argument is the ProgID of a registered COM server, the second is an optional remote server name, and the rest are "topic" strings you define. Excel starts the server and asks it for data on that topic.

The server implements the IRtdServer interface:

  • ServerStart(callback) — called once; keep the callback object to notify Excel later.
  • ConnectData(topicId, strings, ref newValues) — a cell has subscribed to a topic; return an initial value.
  • RefreshData(ref topicCount) — Excel asks for all changed values as a 2-D array of topic IDs and values.
  • DisconnectData(topicId) — no cell uses this topic any more; stop tracking it.
  • Heartbeat() — Excel checks the server is alive; return 1.
  • ServerTerminate() — clean up connections and timers.

Push notification, pull data

The server does not write to cells directly. When new data arrives, it calls callback.UpdateNotify() once. Excel then calls RefreshData when it is ready and collects every pending change in one batch. This keeps Excel responsive no matter how fast data arrives.

Excel also throttles refreshes — by default to at most once every 2 seconds. You can change it through Application.RTD.ThrottleInterval (in milliseconds), but lower values cost CPU on the client.

A practical architecture

  1. A background service subscribes to the real source — a message queue, a database change feed or a market data API.
  2. The RTD server keeps a thread-safe dictionary of the latest value per topic and a set of "dirty" topics.
  3. When a value changes, it is stored, the topic is marked dirty, and UpdateNotify is raised if a notification is not already pending.
  4. RefreshData returns only the dirty topics and clears the set.

Gotchas we hit

  • Threading: Excel's COM objects live on its main (STA) thread. Call UpdateNotify on the thread that received ServerStart, typically by marshalling through a hidden window or synchronisation context. Calling it from a worker thread causes intermittent crashes.
  • Do not flood notifications: one pending UpdateNotify is enough. Excel will collect everything in the next refresh.
  • Bitness: a 32-bit Excel needs a 32-bit registered server and vice versa. Register both if your users run mixed versions.
  • Registration: .NET servers must be COM-visible and registered (for example with regasm /codebase); per-user registration avoids administrator rights.
  • Calculation mode: RTD updates respect manual calculation. Users in manual mode will think the feed is broken.

Beyond Excel

The same design — a source subscriber, a latest-value cache and batched delivery — is how we build live web dashboards today, with WebSockets or server-sent events replacing COM. The principle has not changed: notify cheaply, deliver in batches, and never let a fast data source overwhelm the client.

Back to Insights