Microsoft Office

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, shared contexts, and Interop methods and streams, binding ranges to Channels and contexts, raising notifications, 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:

{
    "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. 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 or at runtime via the registerUDF() method of the Excel API.

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

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() 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)

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() 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() 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")

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() 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)

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.

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(), IOXL_Context(), IOXL_Subscribe(), IOXL_Method(), and 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")