Overview FXLData
An Excel add-in providing a collection of functions for retrieving statistical data from a Fusion Registry directly into a worksheet
The FXLData family of functions are Excel array formulas.
These work in the same way normal as normal single-cell functions such as =SUM() and =STDEV() with the exception that the result is an array of series and observation values spanning a range of rows and columns.
Microsoft's documentation provides general information on how to work with array formulas in Excel.
Parameterised functions
These functions take a number of separate arguments making them suitable for building sophisticated data models where Start Period, End Period and other parameters use cell references or Excel formulas.
Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations.
=FXLData( RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes] )
Returns a selected dataset in 'horizontal' time-series format with one series per row
- 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
=FXLDataV( RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes] )
Returns a selected dataset in 'vertical' format with one series per column
- 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, and whether dimensions, attributes and metrics are shown
- IncludeSeriesKey is ignored
- IncludeBreakdown is ignored
- Set Labels=both to show IDs and dimensions / attributes
- If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header
- IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)
Examples
=FXLData("https://demo.metadatatechnology.com/FusionRegistry","WB,WDI_POVERTY,1.0","A...")
=FXLDataV("https://demo.metadatatechnology.com/FusionRegistry","WB,WDI_POVERTY,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)
=FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))
Like all Excel functions, arguments can either be literal values, cell references, or other formulas.
Functions taking a Fusion Registry data query URL
These functions take a single fully-formed Fusion Registry data query URL making them suitable for use cases where another tool such as the Fusion Data Browser has been used for data discovery, with data retrieval being performed directly in Excel.
Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations.
=FXLDataUrl( Url , [MaxSeries] )
Returns the selected resultset in 'horizontal' time-series format with one series per row
- Behaves in the same way 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
=FXLDataUrlV( Url , [MaxSeries] )
Returns the selected resultset in 'vertical' format with one series per column
- Behaves in the same way 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
=FXLDataUrlFact( Url , [MaxSeries] )
Returns the selected resultset as a data warehouse style fact table
- 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
- Returns one row per observation including all series and observation attributes
- Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels
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")
=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)
=FXLDataUrlFact("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...",20)
Administrative functions
=FXLDataVersion()
- Returns the software version number and build date
=FXLDataVersion("changelog")
Returns the latest version of the change log
Data Wizard
In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.
