FXLData

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

Overview

The FXLData family of 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 general information on how that these array formulas work.

Functions 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)

Functions for 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)

Administrative functions

=FXLDataVersion()

• Returns the software version number

Prerequisites

1. Fusion Registry 10.7.0 or later
2. Microsoft Office 365 Excel 2018 or later

Note that the functions will work with earlier versions of Excel, however execution of array functions is more complex involving preselecting the cells into which the data will be placed and pressing Ctrl-Shift-Enter. Refer to Microsoft's documentation.

Function Reference

=FXLData()

By default ƒ_x =FXLData() returns a horizontal time-series dataset, one series per row with time spanning the columns i.e. time is the dimension at observation. A different dimension at observation can be chosen, more information on how to do this is given in the 'Dimension at Observation behaviour' paragraph below.

Use Case
This parameterised form is suitable for hand coding in a spreadsheet. Like all Excel functions, the arguments can be literal values, cell references or Excel expressions allowing the creation of complex data models. In its simplest form, only three arguments are required:

• The 'base URL' of the Fusion Registry to connect to
• The ID of the Dataflow
• A series key specifying which series to retrieve, or simply 'all' to retrieve all series

=FXLData("https://demo.metadatatechnology.com/FusionRegistry","EXR","all")

By default, the number of series returned is capped at 100, but can be altered using the MaxSeries argument

Syntax
=FXLData( RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes] )

	Argument	Description	Examples	Required?
1	RegUrl	The base URL of the Fusion Registry service	https://demo.metadatatechnology.com/FusionRegistry	Mandatory
2	Dataflow	The Dataflow to query conforming to the SDMX REST specification for data queries	ECB,EXR,1.0	Mandatory
3	Expression	A series key pattern defining the series to select	A.CAD+HUF+INR...E	Mandatory
4	MaxSeries	The maximum number of series to return	1000	Optional Defaults to 100 series
5	RestArguments	Additional data REST query arguments intended for special use cases like adding calculated measures or changing the dimension at observation	round=3&calculatemeasure=THE_MEASURE;pch;mvavg,3&dimensionAtObservation=CURRENCY_DENOM	Optional
6	StartPeriod	The start period in SDMX standard time format	2010-06-12 2010	Optional Defaults to the start of the available data
7	EndPeriod	The end period in SDMX standard time format	2010-06-12 2010	Optional Defaults to the end of the available data
8	Labels	Component labels id - IDs only name - names only both - both IDs and names	both	Optional Defaults to id
9	IncludeHeader	Boolean TRUE includes component labels at the head of each column and shows metrics FALSE component labels and metrics are ommitted	FALSE	Optional Defaults to TRUE
10	IncludeSeriesKey	Boolean TRUE adds a calculated dimension 'series' with the series key as the value FALSE series keys are not included in the dataset	FALSE	Optional Defaults to TRUE
11	IncludeBreakdown	Boolean TRUE includes dimensions FALSE excludes dimensions	FALSE	Optional Defaults to TRUE
12	IncludeAttributes	Boolean TRUE includes dataset and series-level attributes FALSE excludes attributes	FALSE	Optional Defaults to TRUE

=FXLDataUrl()

By default ƒ_x =FXLDataUrl() returns a horizontal time-series dataset. Time is the dimension at observation by default resulting in one series per row with time periods spanning the columns. A different dimension at observation can be chosen, more information on how to do this is given in the 'Dimension at Observation behaviour' paragraph below.

Use Case

Fusion Data Browser Query Syntax function

This version of the function is designed primarily for use cases where a data query URL is generated by a data discovery tool, for example:

• The Fusion Registry REST Web Service query builder
• The Fusion Data Browser Export Data Query Syntax function

Syntax
=FXLDataUrl( Url , [MaxSeries] )

	Argument	Description	Examples	Required?
1	Url	A complete and fully-formed SDMX data query URL	https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id	Mandatory
2	MaxSeries	The maximum number of series to return	1000	Optional Defaults to 100 series

=FXLDataV()

By default ƒ_x =FXLDataV() returns a vertical dataset, one series per column with time spanning the rows. The dimension at observation cannot be changed, the REST argument, if set is ignored.

Use Case
This parameterised form is suitable for hand coding in a spreadsheet. Like all Excel functions, the arguments can be literal values, cell references or Excel expressions allowing the creation of complex data models. In its simplest form, only three arguments are required:

• The 'base URL' of the Fusion Registry to connect to
• The ID of the Dataflow
• A series key specifying which series to retrieve, or simply 'all' to retrieve all series

=FXLDataV("https://demo.metadatatechnology.com/FusionRegistry","EXR","all")

By default, the number of series returned is capped at 100, but can be altered using the MaxSeries argument

Syntax
=FXLData( RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeAttributes] )

	Argument	Description	Examples	Required?
1	RegUrl	The base URL of the Fusion Registry service	https://demo.metadatatechnology.com/FusionRegistry	Mandatory
2	Dataflow	The Dataflow to query conforming to the SDMX REST specification for data queries	ECB,EXR,1.0	Mandatory
3	Expression	A series key pattern defining the series to select	A.CAD+HUF+INR...E	Mandatory
4	MaxSeries	The maximum number of series to return	1000	Optional Defaults to 100 series
5	RestArguments	Additional data REST query arguments intended for special use cases like adding calculated measures or changing the dimension at observation	round=3&calculatemeasure=THE_MEASURE;pch;mvavg,3&dimensionAtObservation=CURRENCY_DENOM	Optional
6	StartPeriod	The start period in SDMX standard time format	2010-06-12 2010	Optional Defaults to the start of the available data
7	EndPeriod	The end period in SDMX standard time format	2010-06-12 2010	Optional Defaults to the end of the available data
8	Labels	Component labels id - includes the series key only at the head of each column both - shows a complete list of all dimensions and optionally attributes at the head of each column, including IDs and names. Display of attributes is controlled by the IncludeAttributes argument.	both	Optional Defaults to id
9	IncludeHeader	Boolean TRUE shows metrics at the top of the result set FALSE metrics are ommitted	FALSE	Optional Defaults to TRUE
10	IncludeAttributes	Boolean TRUE includes dataset and series-level attributes FALSE excludes attributes	FALSE	Optional Defaults to TRUE

=FXLDataUrlV()

ƒ_x =FXLDataUrlV() returns a vertical dataset, one series per column with time spanning the rows. The dimension at observation cannot be changed, the REST argument, if set is ignored.

Use Case

Fusion Data Browser Query Syntax function

This version of the function is designed primarily for use cases where a data query URL is generated by a data discovery tool, for example:

• The Fusion Registry REST Web Service query builder
• The Fusion Data Browser Export Data Query Syntax function

Syntax
=FXLDataUrlV( Url , [MaxSeries] )

	Argument	Description	Examples	Required?
1	Url	A complete and fully-formed SDMX data query URL	https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id	Mandatory
2	MaxSeries	The maximum number of series to return	1000	Optional Defaults to 100 series

=FXLDataVersion()

ƒ_x =FXLDataUrlV() returns the version number of the FXLData addin.

Syntax
=FXLDataVersion()

Example

=FXLDataVersion()

Returns 1.3.2 18/12/2021

Dimension at Observation

For horizontal presentation functions, the dimension at observation defaults to time but can be changed using the dimensionAtObservation REST argument.

REST Argument Syntax
dimensionAtObservation=<DIMENSION_ID> | AllDimensions

Behaviour

Value	Behaviour	Example
<DIMENSION_ID>	Using the ID of any other dimension in the dataset will cause the observations to be broken down by that dimension which will appear across the columns	dimensionAtObservation=REF_AREA
TIME_PERIOD	The same as the default behaviour where time is the dimension at observation	(dimensionAtObservation argument is ommitted)
AllDimensions	Setting the dimension at observation to the special value AllDimensions generates a result set with a single row per observation	dimensionAtObservation=AllDimensions

Examples
Setting REF_AREA as the dimension at observation

=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1&dimensionAtObservation=REF_AREA")

AllDimensions - the result is precisely one observation per row

=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1&dimensionAtObservation=AllDimensions")

Default behaviour with time as the dimension at observation

=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1")

Metrics

Metrics are shown in the first row of the result set if headers are enabled.

Example Metrics

Series Count
<series retrieved>/<series available> series

• <series retrieved> is the actual number of series returned by the function which is controlled by the MaxSeries argument.
  
• <series available> is the total number of series available for the query.

Function Execution Time
<execution time> seconds

• The actual time the function took to retrieve the data from the Fusion Registry and return the result set to Excel.

Controlling when metrics are displayed For parameterised functions (=FXLData() and =FXLDataV()), metrics are displayed when the IncludeHeader argument is TRUE. Set to FALSE to omit metrics.
For URL functions (=FXLDataUrl() and =FXLDataUrlV()), metrics are displayed if the REST API parameter excludeHeader=false. Set the parameter to excludeHeader=true to omit metrics.