Skip to main content
Version: Python

Excel Add-in

Deephaven's Excel add-in allows you to integrate Deephaven data into your spreadsheets. You can query static or dynamic tables using custom filters that you define and use the results of your queries in other calculations. As ticking data changes, the rest of the calculations in your sheet will also update.

This guide shows you how to install the add-in, connect to Deephaven, and use the add-in to access your data.

Install Deephaven's Excel add-in​

Before installing Deephaven's Excel add-in, make sure you have satisfied the prerequisites:

  • Install the latest 64-bit version of Microsoft Excel with Microsoft Office 365.
    • You can alternatively use Office 2021 or later.
  • Ensure that you are running a 64-bit Windows operating system.

Download the Deephaven Excel add-in installer when you have met the prerequisites. Close any open instances of Excel, then run the installer.

img

Accept the default installation path, then click Next:

img

Click Install to begin the installation.

note

At this stage in the installation process, you will be prompted to install .NET Desktop Runtime if you do not already have it. This is a prerequisite for the Deephaven Excel add-in. Click Install to continue.

When you see an Add Deephaven as Trusted Publisher dialog box, choose:

  • Yes to add Deephaven to your computer's list of Trusted Publishers. This is needed so that Excel can run Deephaven code.
  • No if you are unsure or your company's policy prohibits it. In this case, the add-in will not run. To proceed, you should consult with your company's IT administrator.

img

Finally, click Finish to complete the installation.

Connect to Deephaven​

To connect Deephaven's Excel add-in with Deephaven, find "Add-ins" in Excel's menu and look for the "Deephaven" section in the ribbon that appears. Inside the Deephaven drop-down menu, click on Connections.

note

If the Deephaven add-in was not installed correctly or does not have the correct permissions, the add-ins menu might not appear. In this case, you may need to consult your IT administrator for assistance.

img

Next, in the Connection Manager, select New to create a new connection.

img

Select Community Core, choose a Connection ID, and enter your Connection String. The Connection ID is a short string of your choosing that you can use to reference this connection from your Excel formulas. The Connection String specifies the host address and port of the Deephaven Core server you are connecting to. The host address might be a numeric IP address like 10.128.0.21 or a name like myserver.example.com. The port number is specified in the configuration when you start Deephaven and defaults to 10000. In the configuration we are using, the connection string is 10.128.0.21:10000; yours will likely be different.

Before setting the Connection ID and Connection String, click Test Creds to ensure the connection is valid. Finally, click Set Credentials to save the connection.

img

That's it! You're connected to Deephaven, and you can now use the Deephaven Excel add-in to access your data.

Usage​

You can use two functions to access data in Deephaven from Excel: DEEPHAVEN_SNAPSHOT and DEEPHAVEN_SUBSCRIBE. Both methods have the same syntax; they differ in that DEEPHAVEN_SNAPSHOT retrieves a single snapshot of the data, while DEEPHAVEN_SUBSCRIBE subscribes to the data and updates it in real time. Each method takes the following parameters:

  • TABLE_DESCRIPTOR: A string that points the Deephaven Excel add-in to the Deephaven connection and table you want to access. The format is "<ConnectionID>:<TableName>". For example, "con1:t1" to access table t1 in connection con1.
  • FILTER_STRING (optional): A conditional expression interpreted by the Deephaven server to filter the data that Excel receives. For example, "Symbol = `AAPL` && Quantity > 50".
  • WANT_HEADERS (optional): A Boolean value that determines whether Excel includes the headers (column names) from the Deephaven table. If TRUE, Excel will include the headers. The default is FALSE.

DEEPHAVEN_SNAPSHOT​

To retrieve a snapshot of a table from Deephaven, use DEEPHAVEN_SNAPSHOT. This function fetches data from a Deephaven table, optionally filtered by a filter string, at a specific point in time. It works on both static and ticking tables.

First, create a static Deephaven table in the Deephaven console:

from deephaven import empty_table

static_table = empty_table(10).update(formulas=["X = randomInt(0,10)"])

Next, from your Excel sheet, use =DEEPHAVEN_SNAPSHOT("con1:static_table",,FALSE) to retrieve a snapshot of the table static_table from connection con1:

img

You can add a filter to the snapshot by including a filter string: =DEEPHAVEN_SNAPSHOT("con1:static_table", "X > 5", FALSE):

img

Change the WANT_HEADERS parameter to TRUE to include the headers in the snapshot. Here, use =DEEPHAVEN_SNAPSHOT("con1:static_table",,TRUE):

img

DEEPHAVEN_SUBSCRIBE​

To subscribe to updates from ticking tables, use DEEPHAVEN_SUBSCRIBE. This function fetches the data from a ticking Deephaven table and updates as the source table changes.

First, create a ticking table in the Deephaven console:

from deephaven import time_table
import random


def rand_exchange() -> str:
    r = random.randint(0, 4)
    if r == 0:
        s = "kraken"
    if r == 1:
        s = "gemini"
    if r == 2:
        s = "bitstamp"
    if r == 3:
        s = "binance"
    if r == 4:
        s = "coinbase-pro"
    return s


crypto_table = (
    time_table("PT1S")
    .update(
        [
            "Exchange = rand_exchange()",
            "Price = randomDouble(2000,4000)",
            "Size = randomDouble(0,4)",
        ]
    )
    .tail(10)
)

img

Next, fetch the table with =DEEPHAVEN_SUBSCRIBE("con1:crypto_table",,FALSE):

img

Cells that depend on the data in our new ticking rows will also update when the data changes. For example, you can use relative references in the formula =D3 * E3 to multiply the Price and Size columns for individual rows:

img

note

For ticking data, you should expect Excel to update your sheet every few seconds.

Having a fixed number of formulas in your Excel sheet is a limitation because you may not know in advance how many rows your table has. For example, add a filter string to the DEEPHAVEN_SUBSCRIBE formula above - =DEEPHAVEN_SUBSCRIBE("con1:crypto_table","Exchange = `kraken`", FALSE). This filters the table only to show rows where the Exchange column is "kraken", and the table size changes as it ticks:

img

To ensure that your Price * Size column matches the size of the source table, use an INDEX formula: =INDEX(B3#,,2) * INDEX(B3#,,3). The INDEX formula is discussed in more detail below.

img

Cell references vs. dynamic array references​

Cell references like the formula =B3 copy the value of the referenced cell and update when the referenced cell changes. Dynamic array references like =B3# copy the entire array of values from the referenced cell and update when the referenced cell changes. For example:

img

note

If you change your table's definition on the server, your DEEPHAVEN_SNAPSHOT and DEEPHAVEN_SUBSCRIBE functions may still reference data from the original table. To address this, press the Reconnect button in the Connection Manager.

Extract a column from a dynamic array​

Say you want to take column 3 of the ticking table above and use it elsewhere in your spreadsheet. You can use the Excel formula INDEX as in =INDEX(B3#,,3) to extract column 3 from the dynamic array at B3#:

img

Extract column 4 by using =INDEX(B3#,,4):

img

Multiply one column by another​

Now, you can multiply one column by another by using dynamic array references, such as =G3# * H3#:

img

Inline Excel INDEX commands​

You don't have to extract columns somewhere on the sheet before you can use them in a formula. Instead, you can refer to them inside a formula directly. For example, we can get the same result as the formula in the previous section with =INDEX(B3#,,3) * INDEX(B3#,,4):

img

Filter dynamic arrays with FILTER​

You can use a filter expression on one dynamic array to control what rows are selected from another dynamic array. We will demonstrate this in several steps. First, use the INDEX command to extract the Exchange column from the B3# table and store it at G3#. Use the formula =INDEX(B3#,,2):

img

Next, Excel looks at the filter G3# = "kraken" to determine which rows of G3# match "kraken", then pulls in the corresponding rows from B3# to form the result. The full expression is =FILTER(B3#,G3#="kraken"):

img

To make this filtering operation more compact, you can inline the expression that defines the filter source rather than having it occupy space on your sheet. This allows us to combine the previous two formulas into one: =FILTER(B3#,,INDEX(B3#,,2)="kraken")

img

Filter server-side​

You can also filter tables on the server side by using a filter string.

If you are working with a large table, you may prefer to filter the table on the server side before it reaches the client. Do this by using DEEPHAVEN_SUBSCRIBE with a filter string, such as =DEEPHAVEN_SUBSCRIBE("con1:crypto_table","Exchange = `kraken`",FALSE):

img

You can also make the DEEPHAVEN_SUBSCRIBE formula dependent on a cell value. For example, =DEEPHAVEN_SUBSCRIBE("con1:crypto_table", F2, FALSE), with F2 containing the formula ="Exchange = `" & C2 & "`". This way, you can change the filter string by changing the value in cell C2: