Real Time Data
Real Time Data (or RTD) is data that updates according to it’s own schedule, not just when it is re-evaluated (as
is the case for a regular Excel worksheet function).
Examples of real time data include stock prices and other live market data, server loads or the progress of an external
Real Time Data has been a first-class feature of Excel since Excel 2002. It uses a hybrid push-pull mechanism where
the source of the real time data notifies Excel that new data is available, and then some small time later Excel
queries the real time data source for it’s current value and updates the value displayed.
Streaming Data From Python
PyXLL provides a convenient and simple way to stream real time data to Excel without the complexity of writing
(and registering) a Real Time Data COM server.
Real Time Data functions are registered in the same way as other worksheet functions using the
decorator. Instead of returning a single fixed value, however, they return an instance of an class derived
RTD functions have the return type rtd.
When a function returns a
RTD instance PyXLL sets up the real time data subscription in Excel and
each time the value property of the
RTD instance is set Excel is notified that new data
If multiple function calls from different cells return the same instance of an
RTD class then those
cells are subscribed to the same real time data source, so they will all update whenever the value property
The following example shows a class derived from
RTD that periodically updates its value
to the current time.
It uses a separate thread to set the value property, which notifies Excel that new data is ready.
from pyxll import xl_func, RTD
"""CurrentTimeRTD periodically updates its value with the current
date and time. Whenever the value is updated Excel is notified and
when Excel refreshes the new value will be displayed.
def __init__(self, format):
initial_value = datetime.now().strftime(format)
self.__format = format
self.__running = True
self.__thread = threading.Thread(target=self.__thread_func)
# Called when Excel connects to this RTD instance, which occurs
# shortly after an Excel function has returned an RTD object.
# Called when Excel no longer needs the RTD instance. This is
# usually because there are no longer any cells that need it
# or because Excel is shutting down.
self.__running = False
# Setting 'value' on an RTD instance triggers an update in Excel
new_value = datetime.now().strftime(self.__format)
if self.value != new_value:
self.value = new_value
In order to access this real time data in Excel all that’s required is a worksheet function that
returns an instance of this
@xl_func("string format: rtd")
def rtd_current_time(format="%Y-%m-%d %H:%M:%S"):
"""Return the current time as 'real time data' that
:param format: datetime format string
Note that the return type of this function is rtd.
When this function is called from Excel the value displayed will periodically update, even though the function
rtd_current_time isn’t volatile and only gets called once.
Excel throttles the rate of updates made via RTD functions. Instead of updating every time it is notified
of new data it waits for a period of time and then updates all cells with new data at once.
The default throttle time is 2,000 milliseconds (2 seconds). This means that even if you are setting value on an
RTD instance more frequently you will not see the value in Excel updating more often than once
every two seconds.
The throttle interval can be changed by setting Application.RTD.ThrottleInterval (in milliseconds). Setting the
throttle interval is persistent across Excel sessions (meaning that if you close and restart Excel then the value
you set the interval to will be remembered).
The following code shows how to set the throttle interval in Python.
from pyxll import xl_func, xl_app
@xl_func("int interval: string")
xl = xl_app()
xl.RTD.ThrottleInterval = interval
Alternatively it can be set in the registry by modifying the following key. It is a DWORD in milliseconds.