Using Excel with Deephaven

Deephaven's Excel Client allows you to integrate Deephaven data into your spreadsheets through an Excel add-in. 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 crash course shows you how to install the add-in, connect to Deephaven, and fetch a static or ticking table from Deephaven. For a comprehensive guide to the Excel client, see the Community guide.

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.

Note

Please contact Deephaven support for access to the Excel add-in installer.

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.

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 client 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 Enterprise Core+ and choose a Connection ID. The Connection ID is a short string of your choosing that you can use to reference this connection from your Excel formulas.

Next, enter the JSON URL for your Deephaven Core+ server, <your_server_address>:<port>/iris/connection.json. Use port 8000 for servers with an Envoy service and port 8123 for servers without an Envoy service.

For example, https://mydeephavenserver.example.com:8000/iris/connection.json.

Enter the UserId and Password that you use to log onto your Deephaven Core+ server. If you have permissions, you can enter another user's name in the OperateAs field to operate as that user.

Before setting the credentials, 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 client 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 client to the Deephaven connection and table you want to access.
    • If you are using a Core+ connection, the format is "<ConnectionID>:<PersistentQueryName>/<TableName>". For example, "con1+:pq1/t1" to access table t1 in persistent query pq1 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.

Note

For instructions on connecting to a Deephaven Community Core instance, see the Community Core Excel client guide.

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.

The Deephaven Excel client can fetch tables from any Persistent Query you have access to.

For this example, create a new Persistent Query called test_pq and save a script that generates a simple table:

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+:test_pq/static_table",,FALSE) to retrieve a snapshot of the table static_table from connection con1:

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.

For this example, create a new Persistent Query called test_pq and save a script that generates a ticking table:

from deephaven import time_table
import random


def rand_exchange() -> str:
    return random.choice(["kraken", "gemini", "bitstamp", "binance", "coinbase-pro"])


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+:test_pq/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.

Excel operations and formulas

Now that you have a table in Excel, you can access, filter, and modify the data the same way you would any other Excel table. For example, you can use INDEX and dynamic array references to multiply specific columns:

img

Or filter tables with FILTER:

img

And much more. With the Deephaven Excel client, you can use the full power of Excel to analyze, visualize, and modify your data.