Skip to main content

Excel Getting Started

Overview​

KeySquare provides a python based Excel-Addin for connecting to the underlying platform.
The addin can be used to:

  1. Get the latest snapshot of data
  2. Subscribe to real-time data (using Excel RTD behind the scenes)
  3. Publish data

Data access is controlled using permissions configured in KeyAccess (details here).

Installation​

KeySquare Excel-Addin can be installed without admin privileges.

The latest version of the addin can be found here

32-bit or 64-bit version?

Version of the plugin you need depends on the version of Microsoft Excel you have installed.

To find out, simply:

  1. Open MS Excel
  2. Go to File -> Account
  3. Click on 'About Excel'

Image showing Excel bittines

Minimum Requirements​

  1. Microsoft Excel for Office 365 (2015+)
  2. Access to running KeySquare platform (setup instructions)

Instructions​

tip

We suggest closing MS Excel before installing the addin

  1. Extract the downloaded excel.zip to a temporarty location e.g. C://temp/keysquare-excel
  2. Extract the contents of excel.zip to this folder
  3. You should see three files from the extract
    1. keysquare-$version.zip
    2. setup.cmd
    3. setup.ps1
  4. Double click on setup.cmd to run the setup script
Prefer to setup manually?
  1. Extract the contents of keysquare-$version.zip to C:\KeySquare\ExcelAddin (or a location of your choosing)
  2. You should see the following files in the folder:
    1. python-3.11.9-embed-amd64 for 64-bit or python-3.11.9-embed-win32 for 32-bit
    2. keysquare.xll
    3. keysquare.ini
    4. xloil.ini
  3. Create a new folder %APPDATA%\xloil
  4. Move keysquare.ini and xloil.ini to %APPDATA%\xloil
note

If you extracted keysquare-$version.zip to a folder other than C:\KeySquare\ExcelAddin, you will also need to modify the PATH properties in keysquare.ini and xloil.ini to point to your folder

  1. You'll be asked where to install the plugin. The default locatation is C:\KeySquare\ExcelAddin. Enter Y to continue or N to pick a different location
  2. Once setup has finished, open MS Excel
  3. Go to File -> Options -> Add-ins
  4. Select Manage Excel Add-ins and click Go
    Manage Excel Add-ins
  5. In the dialog window
    1. Click Browse and Navigate to C:\KeySquare\ExcelAddin
    2. Select keysquare.xll and click OK
    3. Make sure KeySquare is selected in Add-ins dialog and click OK Select keysquare xll
  6. You should see a 'loading' message in Excel bottom bar followed by a new 'KeySquare' ribbon near the top.
    It's best to restart Excel at this stage to ensure addin is installed properly

Connecting to KeySquare​

The easiest way to connect to KeySquare is by using the controls in the Ribbon

KeySquare Ribbon​

KeySquare Excel Ribbon

  1. The Info button provides links to useful resources relevant to KeySquare
  2. Connection details are comprised of three parts
    1. Hostname :: This is the server where KeyAccess is running
    2. Port :: This is the port to connect to KeyAccess
    3. App Name :: This is the application name to use to connect to KeySquare. This app name must be pre-registered in KeyAccess and permissioned to access relevant data
  3. Once connection details have been updated, click 'Connect' to start. The status will update to 'CONNECTED' to indicate successful connection

Connecting without Ribbon​

If preferred, the following functions can be used to connect/disconnect to KeySquare:

KS_CONNECT​

# To connect to KeySquare
KS_CONNECT(hostname: text, port: number, applicationName: text)

KS_DISCONNECT​

# To disconnect
KS_DISCONNECT()

Platform Information​

Following are useful functions to get more details about your session

KS_STATUS​

Indicates the current status of your platform connection

Provides the following data:

  • Status: Either CONNECTED or DISCONNECTED
  • Time: Indicates the time this status was checked
KS_STATUS()

Example​

ks status example

KS_SESSION_DETAILS​

Details pertaining to your connection to KeySquare platform

KS_SESSION_DETAILS()

Example​

ks session details example

Exploring Data​

info

All data published on KeySquare platform has a unique topic. Some knowledge of the topic structure is required when subscribing to data.

For more information about KeySquare topics click here

Our Flexible topic is handled differently see Flexible Data which shows how it is used differently

KS_CATALOG​

KS_CATALOG() can be used to get a list of all the data types published on the platform.

These data types are normally published by java applications on start-up

Details on how to add new data type here

Example​

KS_CATALOG()

KS_CATALOG Example

KS_SNAPSHOT​

KS_SNAPSHOT can be used to get the latest snapshot of data for a particular data type.

It accepts the following arguments:

  • Data Type: [Required] This data type must be present in the catalog
  • Id: [Optional] This is usually data type specific
  • Group: [Optional] This is usually data type specific
  • Source: [Optional] This is usually the publisher application name
  • Schema: [Optional] This is required if there are multiple data types with the same name
  • Fields: [Optional] A range of fields to return data for. This can be specified to either fix the column header or to return a subset of fields

Examples​

The following examples assume that a data type with name: Price is available

# Get all records of type: Price
KS_SNAPSHOT("Price")

ks-snapshot-price

# Get latest Price(s) for US10Y instrument regardless of source
KS_SNAPSHOT("Price", "US10Y")

ks-snapshot-price-10y

# Get latest Price(s) for US10Y instrument where source is CNBC
KS_SNAPSHOT("Price", "US10Y",,"CNBC")

ks-snapshot-price-10y-cnbc

# Get latest Price(s) for all instruments where source is CNBC
KS_SNAPSHOT("Price",,,"CNBC")

ks-snapshot-price-cnbc

# Get latest Price(s) for all instruments where source is CNBC - only display a subset of fields
KS_SNAPSHOT("Price",,,"CNBC",,D2:J2)

ks-snapshot-price-fields

KS_SAMPLE_MESSAGE​

KS_SAMPLE_MESSAGE can be used to get an empty message. This is helpful to see what the field name and field value types are for a particular data type

Example​

KS_SAMPLE_MESSAGE("Price")

ks-sample-message

Realtime Data​

KS_SUBSCRIBE​

KS_SUBSCRIBE can be used to get realtime data in excel.

It accepts the following arguments:

  • Data Type: [Required] This data type must be present in the catalog
  • Id: [Required] This is usually data type specific
  • Group: [Optional] This is usually data type specific
  • Source: [Optional] This is usually the publisher application name
  • Schema: [Optional] This is required if there are multiple data types with the same name
  • Fields: [Optional] A range of fields to return data for. This can be specified to either fix the column header or to return a subset of fields

Three key differences between KS_SNAPSHOT and KS_SUBSCRIBE are:

  1. KS_SNAPSHOT provides the latest snapshot of requested data once whereas KS_SUBSCRIBE continues to update as new data is published
  2. KS_SNAPSHOT can return multiple rows, whereas KS_SUBSCRIBE only returns one row. If the supplied Id does not uniquely identify a record (e.g. where two 'source(s)' publish records with same id) inconsistent behaviour is observed
  3. KS_SNAPSHOT returns headers when fields is not provided whereas KS_SUBSCRIBE never returns headers. KS_SAMPLE_MESSAGE can be used to acquire field names for KS_SUBSCRIBE if required

Publishing Data​

KS_PUBLISH​

KS_PUBLISH can be used to publish a new record for a particular data type.

It accepts the following arguments:

  • Data Type: [Required] This data type must be present in the catalog
  • Id: [Required] This is usually data type specific
  • Fields: [Required] Field names related to data to publish
  • Data: [Required] Data to publish
  • Group: [Optional] This can be used to group similar data together
  • Source: [Optional] This is usually the publisher application name
  • Schema: [Optional] This is required if there are multiple data types with the same name
tip

Use KS_SAMPLE_MESSAGE to get the list of field names and their corresponding types to know the structure of record to publish

note

time, topicId, topicGroup and topicSource are meta fields that will be auto-populated

Flexible Data​

Whilst we recommend defining a data schema as an explicit contract between publisher(s) and subscriber(s) to avoid unwanted side-effects, it is at times desirable to relax this constraint for testing and POC purpose.

To support this use-case, KeySquare platform supports 'Flexible' publication and subscription. Following section describes how to use this feature in Excel Addin.

Flexible Snapshot​

Flexible snapshotting works just like regular snapshot where data_type = Flexible, however you must provide Id, Group or Fields. This is to help ensure that all returned rows have the same fields.

Example​

ks snapshot flexible

Note: we specify group = testType in the example above referring to data published in flexible publish example.

Flexible Subscribe​

Flexible subscription works just like regular subscription where data_type = Flexible

Flexible Publish​

Flexible publication works just like regular publication where data_type = Flexible

Example​

ks publish flexible

tip

Although not required, we recommend setting group when publishing Flexible data to group similar data together. This can then be used in KS_SNAPSHOT

Example​

ks subscrible flexible