FXLData

From Fusion Registry Wiki
Revision as of 14:13, 7 September 2021 by Glenn (talk | contribs) (→‎=FXLData())
Jump to navigation Jump to search

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

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

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

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

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.

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
Retrieved from "https://wiki.sdmxcloud.org/index.php?title=FXLData&oldid=3932"