Getting Real Time Bloomberg Data into Excel with NeXL and F#

Introduction

Bloomberg real time data can be consumed by .NET applications via Bloomberg API. In this post I would like to show how we can easily get that same data into Excel with a bit of F# async code and NeXL Connector. I will start with a demo spreadsheet and then delve into the code behind it.

Bloomberg Spreadsheet

If you have Excel 2007 and Bloomberg Subscription then you can try it yourself. Simply download this spreadsheet: Bloomberg.xlsm, right click to Unblock it and follow the steps below. If you do not have access to Bloomberg then you can try this spreadsheet instead:  BloombergEmu.xlsm. It uses Bloomberg Emulator.

The spreadsheet contains a User Defined Function Blp.getMarketData(). It takes a topic and an array of fields as arguments:

This UDF will subscribe the cell to a stream of real time data for a given topic and fields. The interesting thing is that it returns a table of data but it is not an array formula and it resizes automatically depending on the number of fields. If we change the topic or fields then the cell will automatically unsubscribe from the stream of data and subscribe to the new one.

How does it work?

The spreadsheet is powered by NeXL Connector. NeXL is available as a Nuget package and allows me to embed .NET functions as UDFs in Excel spreadsheets. It is free to use, including commercial use (CC 4.0 License), but requires permission to redistribute the runtime.

All UDFs in my spreadsheet are defined in an F# project. The project is open source and you can find it on Github. You can clone and build it on your local machine. I have used VS Code and F# Ionide plugin to create the project.

The project uses Bloomberglp.Blpapi.dll to create a session and subscribe to Bloomberg Data. The API is asynchronous and event based so I use a simple F# agent to process all messages. The agent is started in an instance of BlpSession and accepts messages of type  SessionMsg:

type SessionMsg =
    | OpenSession of serverHost : string * serverPort : int
    | SessionEvent of event : Event * session : Session
    | RefDataRequest of securities : string[] * fields : string[] * reply : AsyncReplyChannel<Element list>
    | SubscriptionStart of topic : string * fields : string[] * obsSubscription : ObservableSubscription

Once a session is opened successfully all session events are redirected into the agent as SessionEvent messages. We can then subscribe to a topic with SubscriptionStart messages. All real time data messages are then sent automatically into the agent as SessionEvent messages. So how do we get them out of the agent and into Excel?

In each SubscriptionStart message there is an instance of ObservableSubscription. This type is defined in my project. It implements IObservable<Message list>. When a data message arrives the agent will simply call a method ObservableSubscription.SendEvent which then calls OnNext for the subscribed observer. Messages have correlation ids so that we can track which subscription to call. This information is part of the state of the agent:

type SessionState =
   {
    Session : Session option
    RefDataService : Service option
    LastCorrId : int64
    RequestResponses : Dictionary<int64, Element list>
    Subscriptions : Dictionary<int64, ObservableSubscription>
    Replies : Dictionary<int64, AsyncReplyChannel<Element list>>
   }

The UDF Blp.getMarketData() is an F# function which takes a topic, an array of fields and an optional Session and returns IObservable<XlTable>:

let getMarketData (topic : string, fields : string[], session : BlpSession option) : IObservable<XlTable> =
    let fields = fields |> Array.distinct
    let session = defaultArg session defaultSession.Value
    let subscription = new ObservableSubscription()
    session.StartSubscription(topic, fields, subscription)
    subscription |> Observable.map (marketDataToXlTable fields)

Observable.map transforms the raw list of messages into XlTable. XlTable represents a table of data which can be shown in an Excel range and is defined in NeXL. It is a special type which signals to NeXL runtime that the result is a table and should be sent to a range below and to the right of the calling cell. A comment is also automatically added to the calling cell, e.g. XlTable 2 x 3. NeXL will automatically create an RTD subscription for our IObservable so there is nothing else to do and we can use it as a UDF in Excel.

Finally, the project is built with Fake. The script is in build.fsx. The “Build” target will build the project so that we get a .NET assembly with all UDFs inside. Then “Embed” target will package NeXL Runtime, UDF assembly and any referenced assemblies into a macro enabled Excel spreadsheet. This spreadsheet is self contained and can be used on any machine with Excel 2007 or later and .NET.

For NeXL News follow Twitter @nexl_connector.