StreamLink for Excel

StreamLink for Excel enables an Excel Spreadsheet to retrieve real-time data (RTD) from a Caplin Liberator. It supports the retrieval of records and container constituents from a Caplin Liberator, and the contribution of field-value pairs back to the platform.

Cell data will be updated in real time as new values are received from a Caplin Liberator. The update frequency is under the control of Excel’s RTD Throttle Interval, which by default refreshes real-time data every 2 seconds.

StreamLink for Excel comprises 5 Excel functions and a connection manager.

System requirements

StreamLink for Excel is supported on:

  • Microsoft Excel 2013

  • Microsoft Excel 2010

Installation

Please read the notes below before running the installer.

All users

StreamLink for Excel is packaged as an Add-In file, SL4XL.xla, and installed into your Excel application’s start up folder, XLSTART. The installer will prompt you for the location of XLSTART, and suggest a possible location for it based on your version of Microsoft Excel. If your installation of Microsoft Excel has been customised, then the location suggested by the installer could be wrong.

Make a note of the correct location of XLSTART before running the installer:

  1. Open Excel

  2. Open Excel’s list of Trusted Locations (File > Options > Trust Centre > Trust Centre Settings…​ > Trusted Locations)

  3. Double-click the entry for "Excel 20xx Default Location: Excel StartUp"

  4. Make a note of the location of the Excel StartUp folder. Refer to this location when you run the StreamLink for Excel installer.

Correct the installer’s suggested location of XLSTART if it is wrong.

Users upgrading from an existing installation of SL4XL

There are known issues when upgrading an existing installation of StreamLink for Excel if the existing installation’s SL4XL.xla file has been registered as an Excel Add-In.

There are two ways to install an Excel Add-In file, (file extension .xla):

  • place the Add-In file in Excel’s startup folder, <microsoft office application root>/XLSTART. Excel loads the file automatically on start up, but does not list the file as a registered Add-In under 'Developer tab > Add-Ins'. This is the installation method used by the SL4XL installer.

  • register the Add-In file as an Add-In using 'Developer tab > Add-Ins'

If you encounter errors after upgrading Streamlink for Excel, please see the solutions detailed under known post-install issues.

Configuring a connection to a Liberator

Before you can use StreamLink for Excel’s functions, you must enter the connection details for at least one Liberator server. You will refer to these settings by name when using the Excel functions.

Open the Liberator connection tool by selecting Start > Programs > Caplin > StreamLink for Excel > RTTP Server Configuration. You will be presented with the StreamLink Configurations dialog box.

sl4xl main

To add a new configuration, click Add. You will be presented with the StreamLink Configuration dialog box:

sl4xl config

Populate the dialog box with the connection details for your Liberator server. The following fields are mandatory:

Mandatory fields
Description

Name

The value you enter into this field will be used to refer to this connection profile when you use the StreamLink for Excel functions.

Liberator URL

Supports the following three protocols: HTTP, HTTPS and RTTP

Username

The username of the account on the Liberator server

Password

The password of the account on the Liberator server

The StreamLink Configuration dialog box provides fields for the most common StreamLink options. To configure advanced options not provided here, click Advanced and edit the JSON configuration directly. The values in this advanced view correspond to the StreamLink.NET JSON configuration options.

sl4xl advanced

Restart Excel to make the new connection profile available to Excel.

Excel functions

StreamLink for Excel provides five functions.

SLGet

Specifies real-time data to display in a cell.

Syntax

SLGet(Server_ID, Subject, Field)
Function parameters
Parameter Description

Server_ID

The Caplin Liberator to supply the data, identified using the Caplin Liberator connection tool. You can enter the server ID as a separate cell and reference that cell in your formula.

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

Specifies real-time data to display in a cell.

Syntax

SLGetContainerConstituent(Server_ID, ContainerSubject, RowNumber, Field)
Function parameters
Parameter Description

Server_ID

The Caplin Liberator to supply the data, identified using the Caplin Liberator connection tool. You can enter the server ID as a separate cell and reference that cell in your formula.

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 the specified container.

Syntax

SLGetContainerSize(Server_ID, ContainerSubject)
Function parameters
Parameter Description

Server_ID

The Caplin Liberator to supply the data, identified using the Caplin Liberator connection tool. You can enter the server ID as a separate cell and reference that cell in your formula.

ContainerSubject

The container subject whose size should be returned.

SLGetConnectionStatus

Inserts the connection status of the connection to the Liberator in the cell.

Syntax

SLGetConnectionStatus(Server_ID)
Function parameters
Parameter Description

Server_ID

The Caplin Liberator whose status should be queried.

SLContrib

Contributes data to a Liberator in order for other clients to view it.

Syntax

SLContrib(Server_ID, Subject, Field, Value, ChangedValuesOnly)
Function parameters
Parameter Description

Server_ID

The Caplin Liberator to contribute the data to, identified using the Caplin Liberator connection tool. You can enter the server ID as a separate cell and reference that cell in your formula.

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

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.

ChangedValuesOnly

Optional parameter. If not specified then the values will be contributed every time the Excel sheet updates.

To contribute a value only when that value has changed from its previous value, set this parameter to the numeral '1'.

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)

Advanced tasks

This section covers the following tasks:

  • Copying connection profiles between two Windows user accounts

  • Changing the frequency at which real-time data (RTD) refreshes

Copying connection profiles between two Windows user accounts

A user’s StreamLink for Excel connection profiles are stored in the file %APPDATA%\StreamLinkExcel\StreamLinkConfiguration.json. You can copy connection profiles from one Windows user account to another by copying this file.

APPDATA is a Windows environment variable that contains the full path to the user’s roaming application data. By default, the location is C:\Users_username_\AppData\roaming, but it is common in corporate networks for this location to be customised. We use the environment variable APPDATA in the instructions below rather than a specific path.

To replace the existing Liberator configuration profiles for a target user account with those of a source user account, follow the instructions below:

  1. Prepare the target Windows account:

    1. Install StreamLink for Excel on the target machine if it is not already installed.

    2. Log on to Windows as the target user.

    3. As the target user, open Start Menu > Caplin Systems Ltd > RTTP Server Configuration. Opening RTTP Server Configuration creates a configuration directory and configuration file within the target user’s Windows Profile.

  2. Copy the StreamLink for Excel configuration file from the source user to the target user:

    1. Log on as the source user

    2. Open Windows Explorer and type %APPDATA%\StreamLinkExcel into the address bar and press Return

    3. Copy the file StreamLinkConfiguration.json to a portable medium (network drive, cloud storage, email, or USB flash drive)

    4. Log on as the target user

    5. Open Windows Explorer and type %APPDATA%\StreamLinkExcel into the address bar and press Return

    6. Replace file StreamLinkConfiguration.json with the file you copied to the portable medium in step (c)

  3. Open RTTP Server Configuration on the target user account to display the new profiles.

  4. Edit each connection profile on the target user account and update any options that are incompatible with the permissions of the target user account (see Troubleshooting, below)

  5. Restart Excel on the target user account to load the new connection profiles

Troubleshooting

A connection profile ported from one Windows account to another will fail if it refers to a resource that is inaccessible by the target account. When a connection profile fails, the StreamLink for Excel functions return the text 'Bad Server'.

Two configuration options that you should check for compatibility with the target account are:

  • Liberator URL: the network location may not be accessible from the target user account

  • Log File Directory: the directory may not exist on the target machine or the target user account may not have permission to write to the directory

Changing the data-refresh frequency

By default, Excel refreshes real-time data (RTD) fields every 2 seconds. If this default value does not suit your needs, you can change it by altering Excel’s RTD Throttle Interval. Note that by changing the value of this option, you will change the refresh rate for all RTD-based functions in all Excel spreadsheets, not just those spreadsheets that use StreamLink for Excel’s functions.

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

  1. Open the Excel VBA interface:

    1. Confirm you can see the Developer tab. The Developer tab is not accessible by default, and if you cannot see it then you will need to enable it:

      1. Click the File tab

      2. Click Options

      3. Click Customise Ribbon

      4. Under Customise Ribbon: Main Tabs, select the checkbox next to Developer

    2. From the Developer tab, click Visual Basic

  2. Adjust the RTD Throttle Interval:

    1. From the View tab, select Immediate Window

    2. To view the current value for the RTD Throttle Interval, type the text below into the Immediate Window and press the <return> key:

      ? Application.RTD.ThrottleInterval

    3. To assign a new value to the RTD Throttle Interval, use the VBA assignment operator '='. For example, to change the RTD Throttle Interval to 1000ms, type the text below into the Immediate Window and press the <return> key:

      Application.RTD.ThrottleInterval = 1000

Known post-install issues

There are two known issues that may occur after installation:

  • Excel throws one of the errors below on start up (the file location may be different in your case):

    • 'C:\Program Files (x86)\Caplin\StreamLink for Excel\SL4XL.xla' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

    • Sorry, we couldn’t find C:\Program Files (x86)\Caplin\StreamLink for Excel\SL4XL.xla. Is it possible it was moved, renamed or deleted?

  • Existing spreadsheets that used StreamLink for Excel no longer work, with cell formulas evaluating to '#Name?'

Discussion

Both issues are caused by upgrading from an existing installation of StreamLink for Excel in which the SL4XL.xla file has been manually registered as an Excel Add-In. The errors you encounter in this scenario depend on where the SL4XL.xla file was located:

  • If SL4XL.xla was located in StreamLink For Excel’s application directory, then the file will have been removed during the upgrade. The new version of SL4XL.xla will have been installed in Excel’s XLSTART folder. The change in location of SL4XL.xla has two consequences:

    • Excel will try and fail to load the old Add-In on start up

    • links from existing spreadsheets to the old Add-In will fail

  • If SL4XL.xla was located somewhere other than StreamLink for Excel’s application directory, then the file will not have been removed during the upgrade. Because Add-Ins take precedence over .xla files in Excel’s XLSTART folder, Excel will try to run the old Add-In linked to new application code in StreamLink for Excel’s application directory. The effects of this mix are unpredictable.

Solution

Deregister the old Add-In and repair all spreadsheets that reference the old Add-In.

To deregister the old Add-In, follow the steps below:

  1. Open Excel

  2. Navigate to the 'Developer' tab on the ribbon. If you cannot see the 'Developer' tab:

    1. Click the File tab

    2. Click Options

    3. Click Customise Ribbon

    4. Under Customise Ribbon: Main Tabs, select the checkbox next to Developer

  3. From the Developer tab, click Add-Ins. A list of known Add-Ins will be displayed.

  4. If you can see 'SL4XL' listed as an Add-In, un-check its entry in the list. Excel will report that it can no longer locate the file for the Add-In and will prompt you to remove it from the list. Click OK to remove it from the list.

  5. Click OK to close the Add-Ins dialog

To repair existing spreadsheets that link to the SL4XL.xla file at its old location, follow one of the steps below:

  • Edit each formula individually. StreamLink for Excel functions will be prefixed by the path to the old SL4XL.xla file. If you remove the prefix, Excel will use the new version of SL4XL.xla loaded from Excel’s XLSTART folder. Example below:

    Original: ='C:\Program Files (x86)\Caplin\StreamLink for Excel\SL4XL.xla'!SLGetConnectionStatus("Test")

    Corrected: =SLGetConnectionStatus("Test")

  • Repair the data source for the whole spreadsheet

    1. Open 'Data tab > Edit Links'

    2. Left-click the row for data source SL4XL.xla

    3. Click Change Source…​

    4. Browse to Excel’s XLSTART folder and select SL4XL.xla.

    5. Click Close

    6. Save the spreadsheet