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