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 task.

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 xl_func decorator. Instead of returning a single fixed value, however, they return an instance of an class derived from RTD.

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 is ready.

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 is set.

Example Usage

The following example shows a class derived from RTD that periodically updates its value to the current time.

../_images/rtd.gif

It uses a separate thread to set the value property, which notifies Excel that new data is ready.

from pyxll import xl_func, RTD

class CurrentTimeRTD(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)
        super(CurrentTimeRTD, self).__init__(value=initial_value)
        self.__format = format
        self.__running = True
        self.__thread = threading.Thread(target=self.__thread_func)
        self.__thread.start()

    def connect(self):
        # Called when Excel connects to this RTD instance, which occurs
        # shortly after an Excel function has returned an RTD object.
        _log.info("CurrentTimeRTD Connected")

    def disconnect(self):
        # 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
        _log.info("CurrentTimeRTD Disconnected")

    def __thread_func(self):
        while self.__running:
            # 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
            time.sleep(0.5)

In order to access this real time data in Excel all that’s required is a worksheet function that returns an instance of this CurrentTimeRTD class.

@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
    updates automatically.

    :param format: datetime format string
    """
    return CurrentTimeRTD(format)

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.

=rtd_current_time()

Throttle Interval

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")
def set_throttle_interval(interval):
    xl = xl_app()
    xl.RTD.ThrottleInterval = interval
    return "OK"

Alternatively it can be set in the registry by modifying the following key. It is a DWORD in milliseconds.

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options\RTDThrottleInterval