# UDFs

Source: https://docs.interop.io/adapters/ms-office/excel/udfs/index.html

## Overview

The Excel Adapter provides user defined functions (UDFs) which you can use in Excel formulas to exchange data with the io.Connect platform - receiving data from and sending data to io.Connect [Channels](https://docs.interop.io/desktop/capabilities/data-sharing/channels/overview/index.md), [shared contexts](https://docs.interop.io/desktop/capabilities/data-sharing/shared-contexts/overview/index.md), and [Interop methods and streams](https://docs.interop.io/desktop/capabilities/data-sharing/interop/overview/index.md), binding ranges to Channels and contexts, raising [notifications](https://docs.interop.io/desktop/capabilities/notifications/overview/index.md), and more.

The names of all io.Connect UDFs start with `IOXL_`. You can find them in the Excel Insert Function dialog together with a description of each function and its parameters.

Most io.Connect UDFs are real-time data (RTD) functions - they keep an active subscription to the respective io.Connect Channel, shared context, Interop stream, or Interop method. The cell first shows a status message (e.g., `"Subscribed to channel Red"`) and then updates automatically when new data arrives.

> ⚠️ *Note that the Excel Adapter must be connected to the io.Connect platform for the io.Connect UDFs to work.*

### Field Paths

Many io.Connect UDFs accept field paths for extracting or setting values in objects. A field path is a dot-separated list of property names. For instance, the `"data.instrument.ric"` field path points to the `ric` property of the following object:

```json
{
    "data": {
        "instrument": {
            "ric": "VOD.L"
        }
    }
}
```

### Volatile UDFs

Excel recalculates volatile functions each time any cell in the workbook changes. To register a volatile version of an io.Connect UDF, add its name to the `"volatile-udfs"` property of the [Excel Adapter settings](https://docs.interop.io/adapters/ms-office/excel/configuration/index.md#excel_adapter_settings). The volatile version of the UDF has a `_Vol` suffix (e.g., `IOXL_Invoke_Vol`).

### Custom UDFs

You can register your own UDFs written in C# via the [Excel service configuration](https://docs.interop.io/adapters/ms-office/excel/configuration/index.md#excel_service_configuration-custom_udfs) or at runtime via the [`registerUDF()`](https://docs.interop.io/adapters/ms-office/excel/excel-api/index.md#excel_service_operations-custom_udfs) method of the Excel API.

## Receiving Data

### IOXL_Channel

Joins an io.Connect Channel and shows the value of a field from the Channel context. The cell is updated each time the Channel context is updated.

```
=IOXL_Channel(channelName, outputPath)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `channelName` | `string` | **Required.** Name of the Channel to join. |
| `outputPath` | `string` | Field path to the value to show. |
| `filterPath` | `string` | *Reserved for future use.* |
| `filterValue` | `string` | *Reserved for future use.* |

Example:

```
=IOXL_Channel("Red", "data.contact.name.firstName")
```

### IOXL_Context

Shows the value of a field from an io.Connect shared context. The cell is updated each time the context is updated.

```
=IOXL_Context(contextName, outputPath)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `contextName` | `string` | **Required.** Name of the shared context. |
| `outputPath` | `string` | Field path to the value to show. |
| `filterPath` | `string` | *Reserved for future use.* |
| `filterValue` | `string` | *Reserved for future use.* |

Example:

```
=IOXL_Context("Portfolio", "positions.total")
```

### IOXL_Subscribe

Subscribes to an io.Connect Interop stream and shows the value of a field from the stream data. The cell is updated each time new data is published on the stream.

```
=IOXL_Subscribe(streamName, outputPath, filterPath, filterValue, jsonData)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `streamName` | `string` | **Required.** Name of the Interop stream. |
| `outputPath` | `string` | Field path to the value to show. |
| `filterPath` | `string` | Field path to a value in the stream data. If specified, the cell will be updated only when this value is equal to `filterValue`. |
| `filterValue` | `string` | Value to compare with the value at `filterPath`. |
| `jsonData` | `string` | Subscription arguments as a JSON string. |

Example:

```
=IOXL_Subscribe("Prices", "data.last", "symbol", "MSFT")
```

### IOXL_Method

Registers an io.Connect Interop method. When another app invokes the method, the cell shows the value of a field from the invocation arguments. You can use the same method name in multiple cells to show different fields.

```
=IOXL_Method(methodName, outputPath, filterPath, filterValue)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `methodName` | `string` | **Required.** Name for the Interop method. |
| `outputPath` | `string` | Field path to the value to show. |
| `filterPath` | `string` | Field path to a value in the invocation arguments. If specified, the cell will be updated only when this value is equal to `filterValue`. |
| `filterValue` | `string` | Value to compare with the value at `filterPath`. |

Example:

```
=IOXL_Method("Excel.ReceiveOrder", "order.id")
```

## Sending Data

### IOXL_SendData

Sends data from an Excel range to an io.Connect Channel, shared context, Interop method, or Interop stream.

```
=IOXL_SendData(targetType, target, targetPath, dataRange, isStructuredData, isJsonData, dataOrientation, argsNames, resultFieldPath, outputRange, gateAddress)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `targetType` | `string` | **Required.** Type of the target. Can be `"Channel"`, `"Context"`, `"Method"`, or `"Stream"`. |
| `target` | `string` | **Required.** Name of the Channel, shared context, Interop method, or Interop stream. |
| `targetPath` | `string` | **Required.** Field path where to set the data. |
| `dataRange` | `range` | **Required.** Range containing the data to send. |
| `isStructuredData` | `boolean` | If `TRUE`, the first row of the range will be used as property names and the rows below it as values. Defaults to `FALSE`. |
| `isJsonData` | `boolean` | If `TRUE`, the cell values will be parsed as JSON. Defaults to `FALSE`. |
| `dataOrientation` | `string` | *Reserved for future use.* |
| `argsNames` | `range` | Row containing property names to use instead of the first row of `dataRange`. |
| `resultFieldPath` | `string` | Field path to the value to show from the result of the Interop method. Valid only for Interop methods. |
| `outputRange` | `range` | Range in which to write the result of the Interop method. Requires `gateAddress`. Valid only for Interop methods. |
| `gateAddress` | `range` | Reference to a cell. The data will be sent only when the value of this cell changes. Use it to prevent sending the same data each time Excel recalculates. |

Examples:

```
=IOXL_SendData("Channel", "Red", "instrument.ric", A2)
=IOXL_SendData("Context", "Portfolio", "positions", A1:C10, TRUE)
```

### IOXL_Invoke

Invokes an io.Connect Interop method with data from an Excel range and shows the result.

```
=IOXL_Invoke(endpoint, dataRange, isJsonData, dataOrientation, argsNames, resultFieldPath, outputRange, gateAddress)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `endpoint` | `string` | **Required.** Name of the Interop method. |
| `dataRange` | `range` | **Required.** Range containing the method arguments. The first row is used as argument names and the rows below it as values. |
| `isJsonData` | `boolean` | If `TRUE`, the cell values will be parsed as JSON. Defaults to `FALSE`. |
| `dataOrientation` | `string` | *Reserved for future use.* |
| `argsNames` | `range` | Row containing argument names to use instead of the first row of `dataRange`. |
| `resultFieldPath` | `string` | Field path to the value to show from the method result. |
| `outputRange` | `range` | Range in which to write the method result. Requires `gateAddress`. |
| `gateAddress` | `range` | Reference to a cell. The method will be invoked only when the value of this cell changes. Use it to prevent invoking the method each time Excel recalculates. |

Example:

```
=IOXL_Invoke("Orders.Lookup", A1:B2, FALSE, , , "result.status")
```

### IOXL_CreateStream

Registers an io.Connect Interop stream. To publish data on the stream, use [`IOXL_SendData()`](#sending_data-ioxl_senddata) with `"Stream"` as a target type.

```
=IOXL_CreateStream(endpoint)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `endpoint` | `string` | **Required.** Name for the Interop stream. Use lowercase letters. |

Example:

```
=IOXL_CreateStream("excel.prices")
=IOXL_SendData("Stream", "excel.prices", "prices", A1:B10)
```

## Binding Ranges

### IOXL_BindRegion

Binds an Excel range to an io.Connect Channel, shared context, Interop method, or Interop stream. When the user selects or right-clicks a row in the range, data from that row is sent to the target. Right-clicking a bound range doesn't open the Excel context menu.

```
=IOXL_BindRegion(eventName, dataRange, target, updateFieldPath, column, glueTargetType, jsonTemplate, methodTargetType)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `eventName` | `string` | **Required.** User action that triggers sending the data. Can be `"selectionChange"` or `"rightClick"`. |
| `dataRange` | `range` | **Required.** Range to bind. |
| `target` | `string` | **Required.** Name of the Channel, shared context, Interop method, or Interop stream. |
| `updateFieldPath` | `string` | **Required.** Field path where to set the data. |
| `column` | `number` | Position (1-based) of the column within the range whose value to send. Set to `0` to send the values of the entire row. Defaults to `1`. |
| `glueTargetType` | `string` | Type of the target. Can be `"Channel"`, `"Context"`, `"Method"`, or `"Stream"`. Defaults to `"Channel"`. |
| `jsonTemplate` | `string` | Template for the data to send. Use `$0` to insert the value of the cell in the specified column (or of the first cell of the row when `column` is `0`). When `column` is `0`, use `$1`, `$2`, etc. to insert the values of the row cells. Values are inserted as JSON values (strings are quoted). Use `$$0`, `$$1`, etc. to insert the cell values as raw JSON. For instance, `"id": $1, "price": $$2`. |
| `methodTargetType` | `string` | Interop servers on which to invoke the Interop method. Can be `"All"`, `"Any"`, `"AllButMe"`, or `"AnyButMe"`. Defaults to `"All"`. |

Examples:

```
=IOXL_BindRegion("selectionChange", A2:C20, "Red", "instrument.ric", 1)
=IOXL_BindRegion("rightClick", A2:C20, "Portfolio", "selected", 0, "Context", """id"": $1, ""price"": $$2")
```

### IOXL_RegionSelection2Channel

Sends a value to an io.Connect Channel when the user selects a row in an Excel range. Equivalent to [`IOXL_BindRegion()`](#binding_ranges-ioxl_bindregion) with `"selectionChange"` as an event name and `"Channel"` as a target type.

```
=IOXL_RegionSelection2Channel(dataRange, channelName, updateFieldPath, column)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `dataRange` | `range` | **Required.** Range to bind. |
| `channelName` | `string` | **Required.** Name of the Channel. |
| `updateFieldPath` | `string` | **Required.** Field path where to set the data. |
| `column` | `number` | Position (1-based) of the column within the range whose value to send. Defaults to `1`. |

Example:

```
=IOXL_RegionSelection2Channel(A2:C20, "Red", "instrument.ric", 1)
```

### IOXL_RightClick2Channel

Sends a value to an io.Connect Channel when the user right-clicks a row in an Excel range. Equivalent to [`IOXL_BindRegion()`](#binding_ranges-ioxl_bindregion) with `"rightClick"` as an event name and `"Channel"` as a target type.

```
=IOXL_RightClick2Channel(dataRange, channelName, updateFieldPath, column)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `dataRange` | `range` | **Required.** Range to bind. |
| `channelName` | `string` | **Required.** Name of the Channel. |
| `updateFieldPath` | `string` | **Required.** Field path where to set the data. |
| `column` | `number` | Position (1-based) of the column within the range whose value to send. Defaults to `1`. |

Example:

```
=IOXL_RightClick2Channel(A2:C20, "Red", "instrument.ric", 1)
```

### IOXL_Region

Adds a context menu to an Excel range. The menu lists the available Interop methods whose names match the specified filters. When the user right-clicks a row in the range and selects a method, the method is invoked with the values of the row as arguments.

```
=IOXL_Region(methodFilters, menuRegion, menuRegionOrientation, menuContainsHeaders, menuContainsData, contextMenuCaption, dataRegion, dataRegionOrientation, dataContainsHeaders, headersRegion, headersOrientation, tooltip)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `methodFilters` | `string` | **Required.** Comma-separated names of the Interop methods to show in the context menu. |
| `menuRegion` | `range` | **Required.** Range in which the context menu will be available. |
| `menuRegionOrientation` | `string` | Orientation of the data in `menuRegion`. Can be `"Horizontal"` (headers in the first row) or `"Vertical"` (headers in the first column). |
| `menuContainsHeaders` | `boolean` | If `TRUE`, the first row (or column) of `menuRegion` contains the argument names. |
| `menuContainsData` | `boolean` | If `TRUE`, the method arguments will be taken from `menuRegion`. |
| `contextMenuCaption` | `string` | Caption for the context menu. |
| `dataRegion` | `range` | Separate range from which to take the method arguments. |
| `dataRegionOrientation` | `string` | Orientation of the data in `dataRegion`. Can be `"Horizontal"` or `"Vertical"`. |
| `dataContainsHeaders` | `boolean` | If `TRUE`, the first row (or column) of `dataRegion` contains the argument names. |
| `headersRegion` | `range` | Separate range containing the argument names. |
| `headersOrientation` | `string` | Orientation of the data in `headersRegion`. Can be `"Horizontal"` or `"Vertical"`. |
| `tooltip` | `string` | Tooltip for the context menu. |

Example:

```
=IOXL_Region("Orders.Open", A1:C20, "Horizontal", TRUE, TRUE, "Orders", , "Horizontal", FALSE, , "Horizontal", "Open order")
```

## Search

### IOXL_RegisterSearchProvider

Registers an Excel range as an io.Connect search provider. Apps using the io.Connect search can then find values from the range.

```
=IOXL_RegisterSearchProvider(dataRange, providerName, prefix, types)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `dataRange` | `range` | **Required.** Range containing the searchable data. The first column is used as a result ID, the second as a display name, and the third as a description. |
| `providerName` | `string` | **Required.** Name for the search provider. If empty, defaults to `"<sheet-name>:<range-address>"`. |
| `prefix` | `string` | If specified, the search provider will handle only queries starting with this prefix. |
| `types` | `string` | Comma-separated search types handled by the search provider. |

Example:

```
=IOXL_RegisterSearchProvider(A2:C100, "ExcelInstruments", "xl:", "instrument")
```

### IOXL_QuerySearch

Executes an io.Connect search query. The cell shows the query status. To show the results, use [`IOXL_ReadRTDCache()`](#cached_data-ioxl_readrtdcache) with a reference to the cell containing the query.

```
=IOXL_QuerySearch(searchTarget, outputPath, filterTypes)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `searchTarget` | `string` | **Required.** Search query. |
| `outputPath` | `string` | *Reserved for future use.* |
| `filterTypes` | `string` | Comma-separated search types by which to filter the results. |

Example:

```
A1: =IOXL_QuerySearch("IBM", "", "instrument")
B1: =IOXL_ReadRTDCache(A1)
```

## Notifications

### IOXL_RaiseNotification

Raises an io.Connect notification.

```
=IOXL_RaiseNotification(title, category, description, data, severity, type, actions)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `title` | `string` | **Required.** Title of the notification. |
| `category` | `string` | **Required.** Category of the notification. |
| `description` | `string` | **Required.** Description of the notification. |
| `data` | `string` | **Required.** Additional data for the notification. |
| `severity` | `string` | Severity of the notification. Can be `"None"`, `"Low"`, `"Medium"`, `"High"`, or `"Critical"`. Defaults to `"Low"`. |
| `type` | `string` | Type of the notification. Can be `"Notification"` or `"Alert"`. Defaults to `"Notification"`. |
| `actions` | `string` \| `range` | Notification actions. Each action is a string in the format `"<method-name>;<display-name>;<description>;<correlation-id>;<action-data>;<image>"`. Separate multiple actions with `\|` or place them in separate cells of a range. When the user clicks an action, the Interop method specified in `<method-name>` will be invoked. |

The Interop method invoked by a notification action receives the following arguments:

| Argument | Type | Description |
|----------|------|-------------|
| `actionData` | `string` | Value of `<action-data>`. |
| `correlationId` | `string` | Value of `<correlation-id>`. |
| `udfAddress` | `string` | Address of the cell containing the UDF. |
| `workbookName` | `string` | Name of the active workbook when the notification was raised. |
| `workbookPath` | `string` | Full path to the active workbook when the notification was raised. |
| `worksheetName` | `string` | Name of the active worksheet when the notification was raised. |

Example:

```
=IOXL_RaiseNotification("Limit Exceeded", "Risk", "Exposure is above the limit.", "portfolio-123", "High", "Alert", "ShowPortfolio;Show Portfolio;Open the portfolio;portfolio-123")
```

> ⚠️ *Note that a notification is raised each time Excel recalculates the UDF.*

## Cached Data

### IOXL_ReadRTDCache

Shows the latest data received by another io.Connect UDF. Use it to display objects and arrays, which other io.Connect UDFs can't display in a single cell. The data spills into the neighboring cells.

Works with [`IOXL_Channel()`](#receiving_data-ioxl_channel), [`IOXL_Context()`](#receiving_data-ioxl_context), [`IOXL_Subscribe()`](#receiving_data-ioxl_subscribe), [`IOXL_Method()`](#receiving_data-ioxl_method), and [`IOXL_QuerySearch()`](#search-ioxl_querysearch).

```
=IOXL_ReadRTDCache(cacheAddress, fields)
```

| Parameter | Type | Description |
|-----------|------|-------------|
| `cacheAddress` | `range` | **Required.** Reference to the cell containing the io.Connect UDF. |
| `fields` | `string` | Comma-separated field paths to the values to show. Valid only for `IOXL_Subscribe()` and `IOXL_Method()`. |
| `emptyValue` | `any` | *Reserved for future use.* |
| `nullValue` | `any` | *Reserved for future use.* |

Example:

```
A1: =IOXL_Subscribe("Prices", "data")
B1: =IOXL_ReadRTDCache(A1, "data.bid,data.ask")
```