Difference between revisions of "Overview FXLData"
Jump to navigation
Jump to search
(→Parameterised functions) |
(→Data Wizard) |
||
| (13 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
__NOTOC__ | __NOTOC__ | ||
| − | + | 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. | |
| − | |||
| − | |||
| − | |||
| − | |||
[[File:Fxldata animation.gif]] | [[File:Fxldata animation.gif]] | ||
| Line 16: | Line 12: | ||
Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. | Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. | ||
| − | <span style="color:red;>'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' )</span> <br/> | + | <span style="color:red;>'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )</span> <br/> |
Returns a selected dataset in 'horizontal' time-series format with one series per row<br/> | Returns a selected dataset in 'horizontal' time-series format with one series per row<br/> | ||
* Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return | * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return | ||
| − | * A further | + | * 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 |
| − | <span style="color:red;>'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes]'' )</span> <br> | + | <span style="color:red;>'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )</span> <br> |
Returns a selected dataset in 'vertical' format with one series per column | 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 | * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return | ||
| − | * A further | + | * 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 | * IncludeSeriesKey is ignored | ||
* IncludeBreakdown is ignored | * IncludeBreakdown is ignored | ||
| Line 30: | Line 26: | ||
* If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header | * 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) | * 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''''' | '''''Examples''''' | ||
| Line 43: | Line 41: | ||
Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. | Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. | ||
| − | <span style="color:red;>'''=FXLDataUrl'''( ''Url , [MaxSeries] '' )</span><br/> | + | <span style="color:red;>'''=FXLDataUrl'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )</span><br/> |
Returns the selected resultset in 'horizontal' time-series format with one series per row<br/> | Returns the selected resultset in 'horizontal' time-series format with one series per row<br/> | ||
* Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL | * 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 |
| − | <span style="color:red;>'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' )</span><br/> | + | <span style="color:red;>'''=FXLDataUrlV'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )</span><br/> |
Returns the selected resultset in 'vertical' format with one series per column<br/> | Returns the selected resultset in 'vertical' format with one series per column<br/> | ||
* Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL | * 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 | ||
| − | <span style="color:red;>'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' )</span><br/> | + | <span style="color:red;>'''=FXLDataUrlFact'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )</span><br/> |
Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]<br/> | Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]<br/> | ||
* Takes a single mandatory argument: a fully-formed Fusion Registry data query URL | * 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 | * 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 | * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels | ||
| − | Examples | + | '''''Examples''''' |
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>") | =FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>") | ||
=FXLDataUrlV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>",1000) | =FXLDataUrlV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>",1000) | ||
| Line 76: | Line 76: | ||
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. | 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. | ||
| − | [[File:Wizard Version R1. | + | [[File:Wizard Version R1.3.png|Wizard - August 2025|600px]] |
| + | |||
| + | [[FXLData_Wizard |Read more about the Data Wizard here. ]] | ||
Latest revision as of 00:39, 13 August 2025
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.
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.
