Difference between revisions of "FXLData"
(→Quick Start) |
(→Overview) |
||
| Line 16: | Line 16: | ||
Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] provides general information on how to work with array formulas in Excel. | Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] provides general information on how to work with array formulas in Excel. | ||
| − | |||
| − | |||
| − | |||
====Parameterised functions==== | ====Parameterised functions==== | ||
| Line 68: | Line 65: | ||
====Administrative functions==== | ====Administrative functions==== | ||
<span style="color:red;>'''=FXLDataVersion()'''</span> | <span style="color:red;>'''=FXLDataVersion()'''</span> | ||
| − | * Returns the software version number | + | * Returns the software version number and build date |
| + | |||
| + | ====FXLData Wizard==== | ||
| + | The [[FXLData Wizard]] is an Excel ribbon bar tool for interactively building and editing FXLData functions and comes as part of the add in. | ||
=Prerequisites= | =Prerequisites= | ||
Revision as of 07:11, 6 October 2021
An Excel add-in providing a collection of functions for retrieving statistical data from a Fusion Registry directly into a worksheet.
Contents
Quick Start
- Download the latest version File:FXLData beta 0-4-3.zip and unzip the add-in which is a single file: FXLData.xlam
- Install the add in
- Open a blank Excel worksheet
- Paste the following formula into a cell
=fxldata("https://demo.metadatatechnology.com/FusionRegistry","EXR","A")
A sample 100 series of annual ECB exchange rates data will be retrieved from Metadata Technology's public demonstration Fusion Registry service and inserted directly into the sheet.
Overview
The FXLData family of functions are Excel array formulas.
These work in the same way normal as normal single-cell funcions 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","ECB,EXR,1.0","A...")
=FXLDataV("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)
=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' verical variants take the same arguments making it easy to switch between presentations.
=FXLDataUrl( Url , [MaxSeries] )
Returns a selected dataset 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 a selected dataset 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
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)
Administrative functions
=FXLDataVersion()
- Returns the software version number and build date
FXLData Wizard
The FXLData Wizard is an Excel ribbon bar tool for interactively building and editing FXLData functions and comes as part of the add in.
Prerequisites
- Fusion Registry 10.7.0 or later
- Office 365 Excel 2018 or later on Microsoft Windows only
Note that earlier versions of Excel can be used, 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.
Excel on Apple Mac is not supported because FXLData uses operating system services only available on Windows.
Installation
The FXLData add-in is distributed as a single Excel file:
FXLData.xlam
There are two installation options.
Option 1: Excel Add-ins manager
- From the Excel File menu, choose Options
- On the Options popup, choose Add-ins from the list
- At the bottom, choose Excel Add-ins from the 'Manage' list and click Go
- On the Add-ins popup, choose Browse and select the FXLData.xlam distribution file
Option 2: Excel XLSTART directory
- Copy the FXLData.xlam to the Excel XLSTART directory which is usually in the following location C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
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 using the RestAuguments parameter - refer to the guidance 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 | Options: 1. The dataflow to query conforming to the SDMX REST specification for data queries 2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. |
EXR ECB,EXR,1.0 |
Mandatory |
| 3 | Expression | Options: 1. One or more series keys separated by semi-colons for the dataflow specified using the Dataflow argument separated by semi-colons 2. One or more dataflow and series key expressions separated by semi-colons of the form <dataflow>/<series key> 3. A JSON series basket expression Note that the keyword 'all' can be used in place of a series key to indicate all series |
A A.CAD+HUF+INR...E all A.CAD...E;A.HUF...E;A.INR...E ECB,EXR,1.0/A.HUF...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 labelsid - IDs onlyname - names onlyboth - both IDs and names |
both | Optional Defaults to id
|
| 9 | IncludeHeader | BooleanTRUE includes component labels at the head of each column and shows metricsFALSE component labels and metrics are ommitted |
FALSE | Optional Defaults to TRUE
|
| 10 | IncludeSeriesKey | BooleanTRUE adds a calculated dimension 'series' with the series key as the valueFALSE series keys are not included in the dataset |
FALSE | Optional Defaults to TRUE
|
| 11 | IncludeBreakdown | Boolean Controls whether a breakdown of the dataset's components is shown. TRUE includes dimensions. Attributes are also shown if the IncludeAttributes argument is TRUE. FALSE no components are included |
TRUE | Optional Defaults to TRUE
|
| 12 | IncludeAttributes | BooleanTRUE includes dataset and series-level attributes in the component breakdownFALSE 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 by adding a REST argument to the URL - refer to the guidance below.
Use Case
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
=FXLDataV( 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 | Options: 1. The dataflow to query conforming to the SDMX REST specification for data queries 2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. |
EXR ECB,EXR,1.0 |
Mandatory |
| 3 | Expression | Options: 1. One or more series keys separated by semi-colons for the dataflow specified using the Dataflow argument separated by semi-colons 2. One or more dataflow and series key expressions separated by semi-colons of the form <dataflow>/<series key> 3. A JSON series basket expression Note that the keyword 'all' can be used in place of a series key to indicate all series |
A A.CAD+HUF+INR...E all A.CAD...E;A.HUF...E;A.INR...E ECB,EXR,1.0/A.HUF...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 labelsid - IDs onlyboth - both IDs and names |
both | Optional Defaults to id
|
| 9 | IncludeHeader | BooleanTRUE shows resultset metrics (e.g. 108/1256 series 2.15 seconds)FALSE metrics are omitted |
FALSE | Optional Defaults to TRUE
|
| 10 | IncludeSeriesKey | Ignored | Optional Ignored | |
| 11 | IncludeBreakdown | Ignored | Optional Ignored | |
| 12 | IncludeAttributes | BooleanTRUE includes dataset and series attributes in the header if labels=bothFALSE 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
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")
Choosing Attributes to Include in the Resultset
Include all attributes
- =FXLData() horizontal presentation: set includeBreakdown=TRUE and includeAttributes=TRUE
- =FXLDataV() vertical presentation: set labels=both and includeAttributes=TRUE
Include selected attributes
- Set includeBreakdown=TRUE for =FXLData() or labels=both for =FXLDataV()
- Add to the REST Args: attributes= followed by a comma-separated list of the attribute IDs to include. e.g.
attributes=UNIT,TITLE
Worked examples
The following are samples from the ECB's EXR (Exchange Rates) dataset:
Include all attributes using horizontal presentation.
=FXLData("https://demo.metadatatechnology.com/FusionRegistry","EXR","A",100,,"1990","1995","id",True,True,True,True)
Include all attributes using vertical presentation.
=FXLDataV("https://demo.metadatatechnology.com/FusionRegistry","EXR","A",100,,"1990","1995","both",True,True,True,True)
Include just the UNIT and TITLE attributes using horizontal presentation.
=FXLData("https://demo.metadatatechnology.com/FusionRegistry","EXR","A",100,"attributes=UNIT,TITLE","1990","1995","id",True,True,True,True)
Include just the UNIT and TITLE attributes using vertical presentation.
=FXLDataV("https://demo.metadatatechnology.com/FusionRegistry","EXR","A",100,"attributes=UNIT,TITLE","1990","1995","both",True,True,True,True)
Fusion Registry Authentication
Excel will automatically request the user to authenticate when executing and FXLData function where the Fusion Registry data source is operating in private mode.
Points to note:
- Excel will remember authentication details provided for the remainder of the session. Close and reopen Excel if you need to re-authenticate as a different user.
- A spread sheet can contain multiple FXLData functions using different Fusion Registries. Excel will ask for separate authentication details for each Fusion Registry, thus it is possible to connect to Fusion Registry A as user X, and Fusion Registry B as user Y.
- No authentication will be requested if a Fusion Registry is operating in public mode.
Resultset Metrics
Metrics are shown in the first row of the result set if headers are enabled.
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.
