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.

 

Getting IMF Data into Excel with NeXL Connector and F#

Introduction

IMF Data can be imported by applications via SDMX or JSON Restful APIs. This makes it easy for any .NET client, e.g. WCF or ASP.NET, to consume it. Unfortunately, Excel which is probably the most popular environment to analyse financial and economic data does not have an easy mechanism to access that wealth of data. In this post I would like to present a new way of getting any external data into Excel with single cell UDFs. I will start with a spreadsheet demo and then explain a bit more how it works.

IMF Data Spreadsheet

If you have Excel 2007 or later you can try it yourself. Simply download this spreadsheet: IMF.xlsm, right click to Unblock it and follow the steps below.

The spreadsheet contains a few User Defined Functions which start with IMF. One of them is IMF.getDatasetList():

This UDF calls IMF Data Service and gets a list of all available datasets. The interesting thing is that it returns a table of data but it is not an array formula. This is very convenient because usually we do not know the size of the data upfront. Also, a comment has been automatically added to the calling cell which shows the size of the table. More on this in the next section.

DatasetId is important because it is used in other calls to the API to identify the dataset we need. Each dataset usually has a few dimensions, e.g. Country or Indicator. We can get a list of dataset dimensions with UDF IMF.getDimensions():

As you can see BOP dataset (Balance of Payments) has 3 dimensions. Each of these dimensions will have a discrete set of possible values (codes) by which we can slice it. Before we request the actual data we need to know what are the possible codes. For that I have a UDF IMF.getCodeList(). Let’s see what are the codes for CL_FREQ:

We can get code lists for BOP Area and Indicator in a similar way:

We can now slice BOP dataset by specifying frequency, area(s) and indicator(s). The UDF to get the data is called IMF.getSeriesData():

I have requested BOP data with Annual frequency for Germany (DE) and Indicator IADFDC_BP6_EUR in 2015 only. Let’s try a range of periods:

If the data has a time series format then the first column is Period followed by each combination of requested dimension codes. For example we can request data for 2 countries and 2 indicators at the same time:

Note that you need to concatenate different codes with “+”, e.g. “DE+FR”.

The UDFs in my IMF spreadsheet are embedded in the spreadsheet and will not be available once I close it. If you want to use them in other spreadsheets then you can simply activate it as Excel Customization:

 

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 IMF 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 UDFs are defined in a module IMF. The module is marked with [<XlQualified(true)>] attribute so that their name in Excel always starts with IMF for easy discovery.

The project uses FSharp.Data and Newtonsoft.Json packages to make async requests to IMF Json Restful API and deserialize the response.

The response is usually a data table. It is then converted to a special XlTable object. 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. All IMF UDFs actually return async<XlTable> which means that the data is downloaded asynchronously without blocking Excel thread. You will see #WAIT until the data is available.

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.

 

 

 

Using R in Excel with NeXL Connector and F# RProvider

Introduction

Excel is probably the most popular environment for data and statistical analysis but its functionality is quite limited when compared to R or other statistical software. In this post I would like to present a new way of accessing R functions in Excel which is easier and more productive than any other approach so far. I will start with showing a simple example of fitting a multiple linear model to a sample R data and then explain how it works.

Linear model with R lm() function

If you have Excel 2007 or later and R, e.g. Microsoft R Open, you can try it yourself. Simply download this spreadsheet: RNet.xlsm, right click to Unblock it and follow steps below.

In this case scenario I will try to fit a linear model to R mtcars dataset. This dataset is of course available locally if you have R installed on your machine but I will access it from a remote location here.

My RNet.xlsm spreadsheet has a few User Defined Functions which start with RNet. One of them is RNet.readTable which maps to a similar function in R. I will use it to download the data into Excel, passing as argument the remote location of my dataset:

As you can see the UDF does not actually return the data directly. Instead, it creates a unique string identifier for R DataFrame object. So how is it useful? We can use this object handle as an argument in other UDFs. Let’s start with showing the data:

I have passed the data frame object handle to RNet.asXlTable() UDF. This UDF can return the whole data frame without using Excel array formula which is very convenient in the usual case when we do not know the size of the data upfront.

You might be wondering now what will happen if we point to a different dataset? Let’s try that:

I have changed the path from mtcars to airquality which is just another R sample dataset. As you can see a new object handle has been created and Excel has automatically cleared the previous data and updated it to show the new data frame. So in practice you can think of RNet.asXlTable as being an array function with automatic resizing.

Now, how do we add a linear model? In R we would use lm() function. In the spreadsheet there is a RNet.lm() UDF which maps to the R counterpart. It needs a string formula which defines the model and a data frame. Let’s revert to mtcars dataset and try it:

We get a new object handle, this time of type SymbolicExpression. This is how R objects are usually represented in .NET and returned to Excel. So how can we use this handle? First, we can peek inside:

I have used the RNet.asXlTable() UDF again. You can pass any SymbolicExpression object to it and it will try to show it in a table format. In this case our model is an R list and we can view all list members and their type. We can also drill down and show a specific list member, e.g. “coefficients”:

And so we get a whole R based model with just a few formulas. We can now use Excel recalculation engine to fit different models by just changing the formula in B4:

As you can see the model object handle has been recreated and a new set of coefficients has been automatically shown.

R has of course many more functions. We could expose all or some of them by creating a UDF for each but there is also another way of doing that. For any R function we can create a function object handle and then apply it to input arguments with special UDFs:

RNet.eval() is a special UDF which can parse and evaluate R function, including R function inline definition, and return it as an object handle to Excel. In this case I get an object which represents R lm() function. We can now apply it and recreate our linear model:

I have simply passed my formula and data frame as named arguments to RNet.invokeNamed() UDF. The first argument needs to be a function handle. Alternatively, I can pass the arguments by position:

I have used RNet.invoke() UDF which expects the arguments to be passed by position. The function handle is still passed as the first argument. We can now verify that the model has actually been created:

The UDFs in my RNet spreadsheet are embedded in the spreadsheet and will not be available once I close it. If you want to use them in other spreadsheets then you can simply activate it as Excel Customization:

How does it work?

The spreadsheet is powered by NeXL Connector and F# R Type Provider. 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. With F# R Type Provider I can easily access R functionality from .NET.

All UDFs in RNet 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. Any R distribution should be fine, I have used Microsoft R Open.

The UDFs are defined in a module RNet. The module is marked with [<XlQualified(true)>] attribute so that their name in Excel always starts with RNet for easy discovery.

Most UDFs are very simple and call R functions directly, e.g.:

let lm(formula : string, dataFrame : DataFrame) =
    R.lm(formula = formula, data = dataFrame)

F# R Type Provider exposes R function lm() and I simply pass input arguments to it. It returns a SymbolicExpression object which is a .NET representation of the R model object. Note that the 2nd argument is a DataFrame so in Excel we first have to create an object handle of that type. This is of course done in readTable() function:

let readTable(file : string, separator : string option,
              headers : bool option, rowNamesCol : int option) =
    let separator = defaultArg separator ","
    let headers = defaultArg headers true
    let rowNamesCol = defaultArg rowNamesCol 1
    let prms = namedParams ["file", box file; "sep", box separator;
                            "header", box headers; "row.names", box rowNamesCol]
    let frame = R.read_table(prms)
    match frame with
        | DataFrame(df) -> df
        | _ -> raise (new InvalidOperationException("Not a data frame"))

Again, this function simply calls R read_table(). Note there are some optional arguments. If you do not specify those in Excel then None is automatically passed to the function. The R function is called with named parameters this time so I create them with a convenience function namedParams. It returns a DataFrame as expected.

There are a few similar functions which map directly to R, including the higher order functions for applying a function handle to arguments: invoke() and invokeNamed().

The most interesting is the function asXlTable(). As you can see it expects a SymbolicExpression and optionally a list member. Using F# active patterns we can then process the object and convert it to XlTable object. 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.

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 RProvider assemblies into a macro enabled Excel spreadsheet. This spreadsheet is self contained and can be used on any machine with Excel 2007 or later, .NET and R.