FXLData

An Excel addin providing a collection of functions for retrieving statistical data from a Fusion Registry directly into a worksheet.

Overview

The functions work like normal Excel formulas with the exception that the result is an array of series and observation values spanning multiple rows and columns. This behaviour differs from functions such as =sum() and =average() that return a single value into the cell containing the formula. Microsoft's documentation provides useful background information on how that these array formulas work.

The addin provides four separate formulas that all retrieve selected data but in slightly different ways.

Horizontal time-series presentation

Returns a selected dataset in time-series format with one series per row

Horizontal time-series example

=FXLData()

• The function has three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return
• A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown

Examples

=FXLData("https://demo.metadatatechnology.com/FusionRegistry","ECB,EXR,1.0","A...")
=FXLData("https://demo.metadatatechnology.com/FusionRegistry","ECB,EXR,1.0","A...",1000,"round=2",2010,2020,both,true,true,true,false)
=FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)

Like all Excel functions, arguments can either be literal values, cell references, or other formulas.

=FXLDataUrl()

• Behaves the same as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL
• A single optional argument allows the maximum number of series to be set

Examples

=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id")
=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id",1000)

Vertical presentation

Returns a selected dataset with one series per column and time on the rows.

Vertical example

=FXLDataV()

• Like its horizontal counterpart, FXLDataV has three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return
• There are a further seven optional arguments for controlling: the maximum number of series to retrieve, start / end period, labels, header behaviour and whether attributes are shown
• REST API data query arguments can also be given for special operations like number precision and calculated measures (e.g. % change)

Examples

=FXLDataV("https://demo.metadatatechnology.com/FusionRegistry","ECB,EXR,1.0","A...")
=FXLDataV(("https://demo.metadatatechnology.com/FusionRegistry","ECB,EXR,1.0","A...",1000,"calculatemeasure=CALC_MEASURE;pch;mvavg,3",2010,2020,id,false,false)
=FXLDataV(C13,C3,C6,C14,C12,C4,C5,C11,C8,C10)

=FXLDataUrlV()

• Behaves the same as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL
• A single optional argument allows the maximum number of series to be set

Examples

=FXLDataUrlV("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id")
=FXLDataUrlV("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id",1000)

=FXLData()

Arguments

Argument	Description	Example	Required
RegUrl	The base URL of the Fusion Registry service	https://demo.metadatatechnology.com/FusionRegistry	Mandatory
Dataflow	The Dataflow to query conforming to the SDMX REST specification for data queries	ECB,EXR,1.0	Mandatory
Expression	A series key pattern defining the series to select	A.CAD+HUF+INR...E	Mandatory
MaxSeries	The maximum number of series to return	1000	Optional defaults to 100
RestArguments	Example	Example
StartPeriod	Example	Example
EndPeriod	Example	Example
Labels	Example	Example
IncludeHeader	Example	Example
IncludeSeriesKey	Example	Example
IncludeBreakdown	Example	Example
IncludeAttributes	Example	Example
Example	Example	Example