StreamLink for Excel

Overview

StreamLink for Excel has the following features:

  • Retrieve field values in real time from individual records. See the SLGet function.

  • Retrieve field values in real time from container container constituents. See the SLGetContainerConstituent function.

  • Contribute (publish) field-value pairs to the Caplin Platform. See the SLContrib function.

  • Contribute (publish) pages to the Caplin Platform. See the 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.

System requirements

StreamLink for Excel 7.1.1 is supported on:

  • Microsoft Office 365

  • Microsoft Windows 10

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.

Installing StreamLink for Excel

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

  1. Run the StreamLink for Excel installer

    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.

    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

  2. Click Add to create a new connection profile. The StreamLink Configuration window opens:

    sl4xl config

  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

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

Configuring a connection to two Liberators

Follow the instructions for completing a Liberator connection profile in 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.

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

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, 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
{
  "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.

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