Overview FXLData

FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.

Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.

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.

Horizontal time-series example

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], [TimeFormatFrequencyId], [DataflowName] )
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 eleven 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], [TimeFormatFrequencyId], [DataflowName] )
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 eleven 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)
• If TimeFormatFrequencyId is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily
• DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen.

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] , [TimeFormatFrequencyId] )
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
• The optional MaxSeries argument allows the maximum number of series to be set

=FXLDataUrlV( Url , [MaxSeries] , [TimeFormatFrequencyId] )
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
• The optional MaxSeries argument allows the maximum number of series to be set
• If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily

=FXLDataUrlFact( Url , [MaxSeries] , [TimeFormatFrequencyId] )
Returns the selected resultset as a data warehouse style fact table

• Takes a single mandatory argument: a fully-formed Fusion Registry data query URL
• The optional MaxSeries argument allows the maximum number of series to be set
• Returns one row per observation including all series and observation attributes
• If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily
• 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.

Read more about the Data Wizard here.