---
title: Use Excel with Deephaven
sidebar_label: Excel
---

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 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, ensure you have met the following prerequisites:

- Install the latest 64-bit version of [Microsoft Excel](https://www.microsoft.com/en-us/microsoft-365/excel) with [Microsoft Office 365](https://www.microsoft.com/en-us/microsoft-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.

![The Deephaven Excel Add-in Setup Wizard window](../assets/how-to/excel/excel-installer.png)

Accept the default installation path, then click **Next**:

![The Setup Wizard's installation path selection screen, with **Next** highlighted](../assets/how-to/excel/excel-path.png)

Click **Install** to begin the installation.

At this stage in the installation process, you will be prompted to install [.NET Desktop Runtime](https://dotnet.microsoft.com/en-us/download/dotnet/8.0) 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](https://learn.microsoft.com/en-us/microsoft-365-apps/security/trusted-publisher). 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.

![A pop-up panel in the Setup Wizard prompting the user to accept or reject adding Deephaven to the user's list of Trusted Publishers](../assets/how-to/excel/excel-trusted-pub.png)

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.

![The add-ins tab in Excel, with the Deephaven add-in displayed](../assets/how-to/excel/excel-connections.png)

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

![The Deephaven Excel Connections Manager window](../assets/how-to/excel/new-connection.png)

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.

![The Deephaven Excel Credentials Editor window](../assets/how-to/excel/connect-plus.png)

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`](#deephaven_snapshot) and [`DEEPHAVEN_SUBSCRIBE`](#deephaven_subscribe). Both methods have the same syntax; they differ in that [`DEEPHAVEN_SNAPSHOT`](#deephaven_snapshot) retrieves a single snapshot of the data, while [`DEEPHAVEN_SUBSCRIBE`](#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`.

### `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](../query-management/ui-queries.md) called `test_pq` and save a script that generates a simple table:

```python order=static_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+`:

![`DEEPHAVEN_SNAPSHOT` retrieves a table from Deephaven](../assets/how-to/excel/excel-snap-plus.gif)

### `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](../query-management/ui-queries.md) called `test_pq` and save a script that generates a ticking table:

```python ticking-table order=null
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)
)
```

![The ticking `crypto_table` in the Deephaven console](../assets/how-to/excel/crypto-table.gif)

Next, fetch the table with `=DEEPHAVEN_SUBSCRIBE("con1+:test_pq/crypto_table",,FALSE)`:

![The `crypto_table` ticks in Excel after being retrieved with `DEEPHAVEN_SUBSCRIBE`](../assets/how-to/excel/subscribe-crypto-plus.gif)

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:

![Two cells are multiplied together using their relative references](../assets/how-to/excel/mult-relative-ref.gif)

> [!NOTE]
> For ticking data, you should expect Excel to update your sheet every few seconds.

### 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.

![A cell reference (`=B3#`) can copy an entire array of values](../assets/how-to/excel/cell-v-dynamic-array.gif)

> [!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#`:

![Column 3 extracted from the dynamic array](../assets/how-to/excel/index-1.gif)

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

![Column 4 extracted from the dynamic array](../assets/how-to/excel/index-2.gif)

### Multiply one column by another

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

![Multiplying one column by another using dynamic array references](../assets/how-to/excel/mult-cols.gif)

### 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)`:

![Using inline Excel INDEX commands to multiply columns directly in a formula](../assets/how-to/excel/inline-index.gif)

### 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.

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)`:

![The INDEX command extracts the Exchange column](../assets/how-to/excel/excel-filter2.gif)

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")`:

![Filtering the table using FILTER](../assets/how-to/excel/excel-filter-2.gif)

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")`.

![An entire table is filtered with `FILTER`](../assets/how-to/excel/filter-whole-table.gif)

### Filter server-side

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+:test_pq/crypto_table","Exchange = `kraken`",FALSE)``:

![Filter server-side by passing a filter string to DEEPHAVEN_SUBSCRIBE](../assets/how-to/excel/excel-server-side2.gif)

You can also make the `DEEPHAVEN_SUBSCRIBE` formula dependent on a cell value. For example, `=DEEPHAVEN_SUBSCRIBE("con1+:test_pq/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:

<LoopedVideo src='../assets/how-to/excel/cell-dependent2.mp4' />

## Related documentation

- [`empty_table`](/core/docs/how-to-guides/new-and-empty-table#empty_table)
- [`time_table`](/core/docs/how-to-guides/time-table)
- [`update`](/core/docs/reference/table-operations/select/update)
- [`tail`](/core/docs/reference/table-operations/filter/tail)