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 with Jinx

Jinx 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 @ExcelFunction annotation. Instead of returning a single fixed value, however, they return an instance of an class derived from com.exceljava.jinx.Rtd.

When a function returns an Rtd instance Jinx sets up the real time data subscription in Excel and each time the notify method of the Rtd instance is called 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 notify method is called.

Example Usage

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

fib

It uses a ScheduledExecutorService to notify Excel with a new value periodically.

package com.exceljava.jinx.examples;

import com.exceljava.jinx.Rtd;
import java.util.Date;
import java.text.DateFormat;
import java.util.concurrent.*;

static class CurrentTimeRtd extends Rtd<String> implements Runnable
{
    private final ScheduledExecutorService executor;
    private final DateFormat format;
    private ScheduledFuture<?> future;

    CurrentTimeRtd(DateFormat format, ScheduledExecutorService executor) {
        this.executor = executor;
        this.format = format;

        // call Rtd.notify with the initial value
        notify(getCurrentTime());
    }

    private String getCurrentTime() {
        return format.format(new Date());
    }

    public void run() {
        // notify Excel with the latest value
        notify(getCurrentTime());
    }

    @Override
    public void onConnected() {
        // schedule 'run' to update the value in Excel periodically
        this.future = executor.scheduleAtFixedRate(this, 0, 100, TimeUnit.MILLISECONDS);
    }

    @Override
    public void onDisconnected() {
        // cancel the scheduler
        if (null != future)
            future.cancel(true);
    }
}

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.

package com.exceljava.jinx.examples;

import com.exceljava.jinx.*
import java.util.concurrent.*;
import java.text.SimpleDateFormat;

public class RtdFunctions {

    public RtdFunctions() {
        this.executor = Executors.newScheduledThreadPool(4);
    }

    @ExcelFunction(
            value = "jinx.currentTime",
            description = "Return the current time"
    )
    @ExcelArguments({
            @ExcelArgument("format"),
    })
    public Rtd<String> currentTime(String format) {
        SimpleDateFormat dateFormat = new SimpleDateFormat(format);
        return new CurrentTimeRtd(dateFormat, executor);
    }
}

Note that the return type of this function is Rtd<String>.

The generic parameter is the type that eventually gets returned to Excel. If the return type can’t be converted to something Excel can understand then the result will be cached and a handle to that object returned (see cached objects). Array types can also be returned, but they can’t be resized automatically.

When this function is called from Excel the value displayed will periodically update, even though the function jinx.currentTime only gets called once.

=jinx.currentTime('yyyy/MM/dd HH:mm:ss')

Volatile RTD Functions

When Excel opens a workbook it will show the saved values for any formulas until they need recalculating.

For RTD functions this means that the value doesn’t start ticking until the workbook is recalculated, which isn’t always what we want.

Volatile functions (registered with isVolatile = true using the @ExcelFunction annotation) are always called when the workbook is opened. We can use that to ensure an RTD function starts ticking as soon as the workbook is opened.

Usually, marking a worksheet function as volatile also means that it will be called whenever any change happens on the sheet. For RTD functions Jinx takes care of that and will always returns the same Rtd instance if the function is repeatedly called from the same cell with the same arguments.

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 calling notify 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 via the Excel Object API by setting Application.RTD.ThrottleInterval (in milliseconds). The Excel Object API is what you are using when you write VBA code, but exactly the same API can be accessed from Java.

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 Java using the jinx-com4j package, which acts as a bridge between your Jinx add-in and the Excel Object API.

For details of jinx-com4j see its github page https://exceljava.github.io/jinx-com4j.

package com.exceljava.examples;

import com.exceljava.jinx.*;
import com.exceljava.com4j.JinxBridge;
import com.exceljava.com4j.excel._Application;

public class ThrottleInterval {
    private final ExcelAddIn xl;

    public ThrottleInterval(ExcelAddIn xl) {
        this.xl = xl;
    }

    @ExcelFunction
    public boolean setThrottleInterval(int interval) {
        _Application app = JinxBridge.getApplication(xl);
        app.getRTD().setThrottleInterval(interval);
        return true;
    }

    @ExcelFunction
    public int getThrottleInterval() {
        _Application app = JinxBridge.getApplication(xl);
        return app.getRTD().getThrottleInterval();
    }
}

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

© Copyright PyXLL Ltd.