# StreamLink for Excel

StreamLink for Excel enables an Excel Spreadsheet to retrieve real-time data (RTD) from, and publish data to, a [Caplin Liberator](../liberator/index.md).

## Overview

StreamLink for Excel has the following features:

* Retrieve field values in real time from individual [records](../datasource/datasource-records.md). See the [SLGet](#slget) function.
* Retrieve field values in real time from container [container](../datasource/datasource-containers.md) constituents. See the [SLGetContainerConstituent](#slgetcontainerconstituent) function.
* Contribute (publish) field-value pairs to the Caplin Platform. See the [SLContrib](#slcontrib) function.
* Contribute (publish) [pages](../datasource/datasource-pages.md) to the Caplin Platform. See the [SLContribPage](#slcontribpage) function.

The update frequency for real time data is controlled by Excel’s RTD Throttle Interval, which defaults to 2 seconds. See [Changing Excel’s RTD throttle interval](#changing-excels-rtd-throttle-interval).

## System requirements

StreamLink for Excel 7.1.1 is supported on:

* Microsoft Office 365
* Microsoft Windows 10, 11

The StreamLink for Excel installer requires Administrator privileges.

## Breaking changes

In version 7.1.1 of StreamLink for Excel, the technology used to implement SL4XL’s Excel functions changed from Excel VBA to Excel XLL. Existing spreadsheets that are linked to the old `SL4XL.vba` file must be updated before they will work with StreamLink for Excel 7.1.1.

For instructions, see [Troubleshooting](#troubleshooting).

## Installing StreamLink for Excel

Follow the instructions below to install StreamLink for Excel (SL4XL):

1. Run the StreamLink for Excel installer

   **💡 TIP**\
   If Microsoft Windows Defender SmartScreen prevents the installer from starting, then, in the SmartScreen dialog, click **More info** and **Run anyway**.
2. If you have a previous version of StreamLink for Excel installed, the installer will launch the uninstaller for the previous version. Uninstall the previous version before continuing with the installation.
3. Read the licence agreement and click **I Agree** to continue or **Cancel** to exit the installer.
4. Click **Next** to accept the location of Excel’s startup folder.

   **💡 TIP**\
   If you have two versions of Excel installed, check that the installer has selected the startup folder of the Excel version you want to install SL4XL to.
5. Click **Install** to accept the default installation location and begin the installation.
6. Click **Finish** to close the installer.

## Configuring a connection to a Liberator

All StreamLink for Excel functions take a `ServerId` parameter that specifies the name of a Liberator connection profile.

To create a Liberator connection profile, follow the steps below:

1. In Excel, click **StreamLink for Excel > Configuration**. The StreamLink Configurations window opens:

   ![sl4xl-main](../../images/sl4xl-main.png)
2. Click **Add** to create a new connection profile. The StreamLink Configuration window opens:

   ![sl4xl-config](../../images/sl4xl-config.png)
3. Complete the **Name**, **Liberator URL**, **Username**, and **Password** fields. All other fields are optional.

   **StreamLink configuration fields**

   |  | Description |
   | --- | --- |
   | Name | An arbitrary name for the Liberator connection, as used by the `ServerName` parameter of the StreamLink for Excel functions. |
   | Liberator URL | The URL of the Liberator. For example: `rttp://lib1.example.com:18080`. |
   | Username | The username of the account on the Liberator server, if you are not using KeyMaster to authenticate with Liberator. |
   | Password | The password of the account on the Liberator server, if you are not using KeyMaster to authenticate with Liberator. |
   | Use KeyMaster | Set if you are using KeyMaster to authenticate with Liberator. |
   | KeyMaster URL | The URL of the KeyMaster server, if you are using KeyMaster to authenticate with Liberator. |
4. To set advanced StreamLink connection options, click **Advanced**. The Advanced Configuration Options dialog opens.

   ![sl4xl-advanced](../../images/sl4xl-advanced.png)

   The dialog enables you to edit the full JSON string used to configure a StreamLink.NET connection. For information on StreamLink.NET connection options, see StreamLink.NET’s [StreamLinkFactory](https://docs.caplin.com/developer/api/streamlink.net/latest/class_caplin_1_1_stream_link_1_1_stream_link_factory.html) class.

## Configuring a connection to two Liberators

Follow the instructions for completing a Liberator connection profile in [Configuring a connection to a Liberator](#configuring-a-connection-to-a-liberator), but specify the value for the **Liberator URL** using the syntax below:

**Liberator URL syntax**

| Connection type | Syntax | Example |
| --- | --- | --- |
| Failover | [__url1__, __url2__] | ["rttp://lib1.example.com:18080", "rttp://lib2.example.com:18080"] |
| Load balancing | [[__url1__], [__url2__]] | [["rttp://lib1.example.com:18080"], ["rttp://lib2.example.com:18080"]] |

## Excel functions

StreamLink for Excel provides five functions.

### SLGet

Retrieves a real-time field value from a [record](../datasource/datasource-records.md).

**Syntax**

```
SLGet(ServerId, Subject, Field)
```

| Parameter | Description |
| --- | --- |
| `ServerId` | The ID of the Liberator, as specified in StreamLink for Excel’s RTTP Server Configuration Tool. Accepts a string or a cell reference. |
| `Subject` | The subject of the quote to be returned. You can enter the subject as a separate cell and reference that cell in your formula. |
| `Field` | The field of the quote to be returned. You can enter the field as a separate cell and reference that cell in your formula. |

### SLGetContainerConstituent

Retrieves a real-time field value from a [container](../datasource/datasource-containers.md) constituent.

**Syntax**

```
SLGetContainerConstituent(ServerId, ContainerSubject, RowNumber, Field)
```

| Parameter | Description |
| --- | --- |
| `ServerId` | The ID of the Liberator, as specified in StreamLink for Excel’s RTTP Server Configuration Tool. Accepts a string or a cell reference. |
| `ContainerSubject` | The container to query. |
| `RowNumber` | The row number within the container. Container rows are indexed from 0. |
| `Field` | The field of the quote to be returned. You can enter the field as a separate cell and reference that cell in your formula. |

### SLGetContainerSize

Returns the size of a specified container.

**Syntax**

```
SLGetContainerSize(ServerId, ContainerSubject)
```

| Parameter | Description |
| --- | --- |
| `ServerId` | The ID of the Liberator, as specified in StreamLink for Excel’s RTTP Server Configuration Tool. Accepts a string or a cell reference. |
| `ContainerSubject` | The container subject whose size should be returned. |

### SLGetConnectionStatus

Returns the status of named Liberator connection.

**Syntax**

```
SLGetConnectionStatus(ServerI_ID)
```

| Parameter | Description |
| --- | --- |
| `ServerId` | The ID of the Liberator, as specified in StreamLink for Excel’s RTTP Server Configuration Tool. Accepts a string or a cell reference. |

### SLContrib

Contributes (publishes) field-value pairs to a subject.

**Syntax**

```
SLContrib(ServerId, Subject, Field, Value, ChangedValuesOnly)
```

| Parameter | Description |
| --- | --- |
| `ServerId` | The ID of the Liberator, as specified in StreamLink for Excel’s RTTP Server Configuration Tool. Accepts a string or a cell reference. |
| `Subject` | The subject to be published. You can enter the subject as a separate cell and reference that cell in your formula. This subject will be used for all field/value pairs specified in the Field and Value parameters. |
| `Field` | The field or fields of the subject to be published. You can enter the field contained in one cell or a selection of fields in a range of cells (e.g. A8:A12). Supports printable US-ASCII characters only (non-US-ASCII characters are replaced with spaces). |
| `Value` | The value (or values if more than one field has been specified) to contribute to Caplin Liberator. You can use the value contained in one cell or a selection of values in a range of cells (e.g. B8:B12). It is not possible to contribute blank values. Supports printable US-ASCII characters only (non-US-ASCII characters are replaced with spaces). |
| `ChangedValuesOnly` | Optional. If set to `TRUE`, values are only contributed if they have changed since the most recent contribution. If set to `FALSE` (the default), values are contributed each time the Excel sheet updates. IMPORTANT: From StreamLink for Excel 7.1.1, this parameter is ignored and values are contributed each time the Excel sheet updates. |

**Example**

```
SLContrib("IDN", $B7, $A8:$A12, $B8:$B12)
```

The above formula contributes the field-value pairings `$A8:$A12 - $B8:$B12` to the subject in cell `$B7`.

The optional parameter `ChangedValuesOnly` has not been included, so the field-value pairs will be contributed to the subject every time the Excel spreadsheet updates. To contribute to the subject only when the values in B8 to B12 have changed since the previous contribution, include the parameter `ChangedValuesOnly` with the numeric value of '1'.

```
SLContrib("IDN", $B7, $A8:$A12, $B8:$B12, 1)
```

### SLContribPage

Contributes a range of cells to a Liberator as a DataSource [page](../datasource/datasource-pages.md), 25 rows deep and 80 characters wide.

**Syntax**

```
SLContribPage(ServerId, PageName, PageData, Format)
```

| Parameter | Description |
| --- | --- |
| `ServerId` | The ID of the Liberator, as specified in StreamLink for Excel’s RTTP Server Configuration Tool. Accepts a string or a cell reference. |
| `PageName` | The page’s subject. Accepts a string or a cell reference. |
| `PageData` | The cell-range to publish as a page. The range can be up to 25 rows deep (if the range is greater than 25 rows, then it is truncated). Data on each row can be up to 80 characters wide (if the row width is greater than 80 characters, then it is truncated). Supports printable US-ASCII characters only (non-US-ASCII characters are replaced with spaces). |
| `Format` | Set to `TRUE` to format page data according to the display width of Excel columns. Set to `FALSE` to format page data according to actual character data. For example, consider an Excel cell range that is two columns wide. The first column has a display width of 10 characters and contains cells that each contain only one character of data. If you set `Format` to `TRUE`, then data in the second column begins at character 11. If you set `Format` to `FALSE`, then data in the second column begins at character 2. |

**Example**

```
SLContribPage('IDN', '/EXAMPLE/PAGE', A1:B25, TRUE)
```

## Copying configuration settings

A user’s StreamLink for Excel connection settings are stored in the file `%APPDATA%\StreamLinkExcel\StreamLinkConfiguration.json`.

**Example StreamLinkConfiguration.json file**

```json
{
  "configurations": [
    {
      "slxl_name": "Liberator1",
      "slxl_log_file_dir": "",
      "slxl_log_level": "CONFIG",
      "liberator_urls": "rttp://192.168.34.57:18080",
      "username": "jsmith",
      "encrypted_password": "+z7oaCoHuESkYz+kLfSTUOtCPDs6GNBJufPyli1Fj4g="
    }
  ]
}
```

To copy StreamLink for Excel connection profiles from User A to User B:

1. Close Excel on User A’s computer.
2. Close Excel on User B’s computer.
3. Copy User A’s `%APPDATA%\StreamLinkExcel\StreamLinkConfiguration.json` file to User B’s `%APPDATA%\StreamLinkExcel` folder.
4. Check that the copied configuration file does not contain any settings incompatible with User B’s Windows account. For example, if a connection profile includes a log file directory, ensure that the directory exists on User B’s computer.

## Changing Excel’s RTD throttle interval

By default, Excel throttles real-time data (RTD) updates to one update every 2000 milliseconds (2 seconds). This is a performance optimisation designed to prevent fast changing real-time data from overloading Excel.

**📌 NOTE**\
The RTD throttle interval is an Excel-wide setting. The setting applies to all spreadsheets with a RTD component, not just those that use StreamLink for Excel.

To change the RTD Throttle Interval, follow the steps below:

1. In the **Developer** ribbon, click **Visual Basic**.

   If you can’t see the **Developer** ribbon, follow the instructions below to enable it:
   1. Click the **File > Options > Customise Ribbon**
   2. In the **Main Tabs** list, select the checkbox next to **Developer**
   3. Click **OK** to confirm the new setting
2. In the **Visual Basic** window, click **View > Immediate Window**
3. In the immediate window, type the expression below to print the current setting for the RTD Throttle Interval to the console:

   ```
   ? Application.RTD.ThrottleInterval
   ```
4. In the immediate window, type the expression below and press Return to assign a new value to the RTD Throttle Interval. The example below sets the throttle interval to 1000 milliseconds (1 second):

   ```
   Application.RTD.ThrottleInterval = 1000
   ```

   The new value is written to the Windows Registry under `Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Excel\Options\RTDThrottleInterval` and persists between Excel sessions.

## Troubleshooting

After upgrading to a later version of SL4XL, or when opening a spreadsheet created using earlier version of SL4XL, you may see variations on the messages below when opening a spreadsheet that uses SL4XL functions:

* "SECURITY WARNING Automatic update of links has been disabled [Enable Content]"
* "C:\Program Files (x86)\Microsoft Office\root\&lt;version>\xlstart\SL4XL.xla not found"
* "This Workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest data. Otherwise you can keep working with the data you have."

This is a known issue from SL4XL 7.1.1, when the technology used to implement SL4XL’s functions changed from Excel VBA to Excel XLL. Spreadsheets created using versions of SL4XL earlier than 7.1.1 require alterations before they are compatible with SL4XL 7.1.1.

Follow the steps below to resolve the issue:

1. Open the broken Excel spreadsheet
2. If Excel prompts you to update links, click **Don’t Update**.
3. For each cell that displays the error text `#NAME?`, edit the cell’s formula and remove the hardcoded link to the `SL4XL.xla` file:

   **Example formula**

   ```
   ='C:\Program Files (x86)\Microsoft Office\root\Office16\xlstart\SL4XL.xla'!SLGetConnectionStatus("Liberator1")
   ```

   **Example formula corrected**

   ```
   =SLGetConnectionStatus("Liberator1")
   ```