Real Time Data ¶

Introduction ¶

Real Time Data (or RTD) is data that updates asynchronously, according to its own schedule rather than 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 its current value and updates the value displayed.

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. By registering a Python generator function, or a function that returns an RTD object, streams of values can be returned to Excel as a real time data function.

RTD functions have the return type rtd.

RTD Generators ¶

New in PyXLL 5.6

The simplest way to write an Excel RTD (Real Time Data) function using PyXLL is to write it as a Python generator.

A Python generator is a special type of Python function that yields a stream of results instead of just a single result.

The following is an example of a Python generator that yields a random number every 5 seconds:

import random
import time

def random_numbers():
    # Loop forever
    while True:
        # Yield a random number
        yield random.random()

        # Wait 5 seconds before continuing
        time.sleep(5)

To turn this into an RTD function in Excel all that’s needed is to add the xl_func decorator with the rtd return type:

from pyxll import xl_func
import random
import time

@xl_func(": rtd")
def random_numbers():
    while True:
        yield random.random()
        time.sleep(5)

When this random_numbers function is called from Excel it will tick every 5 seconds with a new random number.

To prevent the long running Python generator function from blocking the main Excel thread RTD generators are always run on a background thread. One thread is created for each generator. For more sophisticated thread management use a function that returns an RTD instance instead (see Using the RTD Class).

Each time the generator yields a value Excel is notified a new value is ready. Excel may not display every value as it throttles the updates that is displays (see Throttle Interval).

It’s important not to create a tight loop that constantly updates as doing so will prevent other threads from having time to run (including the main Excel thread) and will cause Excel to hang.

from pyxll import xl_func
import random
import asyncio

@xl_func(": rtd")
async def async_random_numbers():
    # Loop forever
    while True:
        # Yield a random number
        yield random.random()

        # Wait 5 seconds before continuing without blocking the event loop
        await asyncio.sleep(5)

Using the RTD Class ¶

For more control over the RTD behaviour of an Real Time Data function an RTD object can be returned from an RTD function instead of using a generator.

The RTD class has a value property. Setting this property notifies Excel that a new value is ready. RTD functions that use the RTD class return an RTD object and update the value on that returned object each time a new value is available (for example, by scheduling an update function on a background thread). Typically this is done by writing a derived class that handles updating its own value.

If multiple function calls from different cells return the same RTD object then those cells are subscribed to the same object. All the cells will update whenever the value property of the one RTD object is set.

RTD Class Example ¶

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
from datetime import datetime
import threading
import logging
import time

_log = logging.getLogger(__name__)


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()

from pyxll import RTD, xl_func
import asyncio

class AsyncRTDExample(RTD):

    def __init__(self):
        super().__init__(value=0)
        self.__stopped = False

    async def connect(self):
        while not self.__stopped:
            # Yield to the event loop for 1s
            await asyncio.sleep(1)

            # Update value (which notifies Excel)
            self.value += 1

    async def disconnect(self):
        self.__stopped = True


@xl_func(": rtd<int>")
def async_rtd_example():
    return AsyncRTDExample()

RTD Data Types ¶

RTD functions can return all the same data types as normal Worksheet Functions, including array types and cached Python objects.

By default, the rtd return type will use the same logic as a worksheet function with no return type specified or the var type.

To specify the return type explicity you have to include it in the function signature as a parameter to the rtd type.

For example, the following is how an RTD function that returns Python objects via the internal object cache would be declared:

@xl_func("string x: rtd<object>")
def rtd_object_func(x):
    # MyRTD sets self.value to a non-trivial Python object
    return MyRTD(x)

RTD data types can be used for RTD generators in the same way.

Although RTD functions can return array types, they cannot be automatically resized and so the array formula needs to be entered manually using Ctrl+Shift+Enter (see Array Functions).

Restarting RTD Functions ¶

Each time your RTD function ticks, Excel re-evaluates the RTD function. PyXLL keeps track of your RTD generator or RTD instance to ensure that each time Excel calls the RTD function the correct, already running, object is returned.

Without this caching behavior your Python function would be called each time the RTD function ticks, causing it to always restart and never progress past the initial value.

If the arguments to an RTD function are changed, or if it is moved from one cell to another, the Python function will be called and the RTD function will restart. Because the RTD object has to be cached the usual methods of forcing a cell to recalculate such as pressing F2 to edit the cell and then entering, or force recalculating by pressing Ctrl+Alt+F9, do not work for RTD functions.

There are situations though where an RTD function only returns a fixed number of values before stopping. In those cases you may want the RTD function to restart the next time Excel calculates the cell.

For example, one common case is an RTD function that returns just one value after a delay:

@xl_func("str x: rtd")
async def fetch_from_database(x):
    # Let the user know we're working on it
    yield "Please wait..."

    # Fetch some data
    data = await async_fetch_data_from_database(x)

    # Finally yield the data to Excel
    yield data

In this case, if the user was to attempt to recalculate the cell after the data has been returned they might expect that the data be re-fetched. But, the default behaviour is that the RTD generator is cached and so its same current state is returned again.

@xl_func("str x: rtd<auto_detach=True>")
async def fetch_from_database(x):
    # Let the user know we're working on it
    yield "Please wait..."

    # Fetch some data
    data = await async_fetch_data_from_database(x)

    # Finally yield the data to Excel.
    # The generator stops after this line.
    yield data

[PYXLL]
rtd_auto_detach = 1

from pyxll import RTD, xl_func
import asyncio

class AsyncRTDExample(RTD):

    def __init__(self):
        super().__init__(value=0)
        self.__stopped = False

    async def connect(self):
        # Send just a few ticks
        for i in range(5):
            if self.__stopped:
                break

            # Yield to the event loop for 1s
            await asyncio.sleep(1)

            # Update value (which notifies Excel)
            self.value += 1

        # And then detach.
        # The next time =async_rtd_example is called the Python
        # function will be called, starting a new AsyncRTDExample instance.
        self.detach()

    async def disconnect(self):
        self.__stopped = True


@xl_func(": rtd<int>")
def async_rtd_example():
    return AsyncRTDExample()

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 or yielding values from a generator 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

Starting RTD Functions Automatically ¶

When you enter an RTD function in an Excel formula is begins ticking automatically because the function has been called. When loading a workbook containing RTD functions however, they will not start ticking until the function is called.

To enable RTD functions to begin ticking as soon as a workbook is opened PyXLL RTD functions can be marked as needed to be recalculated when the workbook opens by using the Recalculating On Open feature of PyXLL.

To make a function recalculate when the workbook is loaded pass recalc_on_open=True to xl_func. If applied to an RTD function this will cause the RTD function to start ticking when the workbook is loaded.

You can change the default behaviour so that recalc_on_open is True by default for RTD functions (unless explicitly marked otherwise) by setting recalc_rtd_on_open = 1, e.g.

[PYXLL]
recalc_rtd_on_open = 1