Real-Time Data (RTD)

Excel offers the Real-Time Data (RTD) in-cell function, which allows generic access to live, updating data. To access Deephaven data via the RTD function, you must first install the Deephaven Excel Add-In, then run Excel one time with administrator privileges to register the underlying COM server for Excel interaction.

Excel's built-in RTD function, unlike most Excel functions, operates continuously, reporting data that automatically updates. The Deephaven Excel Add-In leverages the RTD function to provide live, ticking data from Deephaven.

After an RTD function (=RTD()) has been entered into a cell, the returned value will initially be “(pending)” to indicate that no data has been returned from the server yet. When the server returns a value, the cell will automatically update with any new data. If there is a problem with the RTD formula’s parameters, the cell will instead display an error message. If there is a problem that cannot be displayed in a single RTD function’s return value, the error will be recorded and displayed by the Show RTD Errors button.

Using RTD to Retrieve Deephaven Data

The RTD function can only access data generated by running Persistent Queries. To use the RTD function, you pass the parameters into the =RTD() function in an Excel cell. Within each cell of an RTD function, the value will automatically update any time Deephaven finds new data that affects that single value.

Be aware that Deephaven itself can produce updates faster than Excel's RTD function refreshes. If Deephaven produces values of 0, 1, 2, 3, and 4 for a given RTD function all within one second of each other, and the RTD function only updates every three seconds, then by the time the RTD function updates, it will only display the last known value, in this case 4.

For example:

=RTD("deephaven.pqserver",, "<name of a persistent query>", "<table from that persistent query>", "<column from that table>", "<aggregation operation>")

Once executed, the selected cell will show the most up-to-date available information on an ongoing basis.

The Excel RTD function takes an unbounded number of text strings as parameters; minimum two. To retrieve data from Deephaven via the RTD function, you will need to pass in at least six parameters.

1. Parameter 1: The exact string "deephaven.pqserver". This tells Excel what COM server to activate.
2. Parameter 2: Blank, unless you have reason to do otherwise. This tells Excel which computer to connect to (normally your own).
3. Parameter 3: The name of the Persistent Query.
4. Parameter 4: The name of the table within the Persistent Query.
5. Parameter 5: The name of the column within the table.
6. Parameter 6: The aggregation operation to perform on the selected column. Because the RTD function can only return a single value per cell, an aggregation operation is required to ensure only one value is found. You must ensure the aggregation operation you select is compatible with the data type of the selected column. The allowed operations are:
   • First
   • Last
   • Min
   • Max
   • Sum
   • Avg
   • Std
   • Var
   • Count

After these first six parameters, you may add further parameters in pairs. For each pair of parameters, the first value is the name of a column, and the second value is a specific value the column must be equal to.

Examples

=RTD("deephaven.pqserver", , "GetSomeDataQuery", "queryTable", "Bid", "Max")

The following can be used to retrieve the first Symbol value across the table named queryTable within the Persistent Query named GetSomeDataQuery, where the Date is 2019-08-25:

=RTD("deephaven.pqserver", , "GetSomeDataQuery", "queryTable", "Symbol", "First", "Date", "2019-08-25")

The following can be used to retrieve the average bid size at the New York Stock Exchange (NYSE) on 2019-08-25, from table named queryTable within the Persistent Query named GetSomeDataQuery:

=RTD("deephaven.pqserver", , "GetSomeDataQuery", "queryTable", "BidSize", "Avg", "Exchange", "NYSE", "Date", "2019-08-25")

Restart RTD

Selecting the Restart RTD button will stop and restart the RTD server.

Show RTD Errors

The Show RTD Errors button will only activate if errors are found while executing an RTD operation. When an error is found and the button is selected, a new error dialog panel will show any errors that resulted from RTD operations.