Difference between revisions of "FusionXL FXLData - RETIRED"
(→Overview) |
m (Glenn moved page FusionXL FXLData to FusionXL FXLData - RETIRED without leaving a redirect: Replaced by new FXLData add-in) |
||
(45 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
== Overview == | == Overview == | ||
− | The | + | The =FXLData() formula is used to retrieve specified series from the Fusion Registry to which FusionXL is currently connected directly into a worksheet. |
− | FXLData is an Excel 'array formula' | + | ====Excel Array Formula==== |
+ | =FXLData() is an Excel 'array formula'.<br> | ||
+ | Array formulas work in a similar way to normal single-cell formulas like =SUM() and =STDEV() except that the result is an 'array' of cells spanning a number of rows and columns rather than just the cell into which the formula has been entered. See [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 Microsoft's article on array formulas] for more general information. | ||
− | + | The result of querying Fusion Registry for data is generally one or more time series, each with potentially multiple observations. As a consequence, an =FXLData() formula in a worksheet returning five series with 10 time periods requires an array of 5 rows and 10 columns to hold the result. | |
− | |||
+ | Excel distinguishes array formulas in the function bar by wrapping them in {}. | ||
− | + | ====Example==== | |
+ | Formula in Cell A1 with Array Range A1:O20 | ||
+ | =fxldata("ECB,EXR,1.0","A..EUR.SP00.A","2010","2018",TRUE,TRUE,TRUE,FALSE,FALSE,2) | ||
− | + | Result<br> | |
+ | [[File:FXLdataExample.PNG|600px]] | ||
+ | |||
+ | ====Identifying the series to retrieve==== | ||
+ | The first two arguments specify the series. All other arguments are optional. | ||
+ | =fxldata("ECB,EXR,1.0","A..EUR.SP00.A") | ||
+ | |||
+ | 1. <code>ECB,EXR,1.0</code> specifies the Dataflow using either the full form with Agency and Version as shown, the Agency and Dataflow <code>ECB,EXR</code> or just using the Datflow id <code>EXR</code><br> | ||
+ | 2. <code>A..EUR.SP00.A</code> series key conforming to the SDMX REST API GET data query specification<br> | ||
+ | |||
+ | ====Video demo==== | ||
+ | [https://www.youtube.com/watch?v=zpslTahktak&feature=youtu.be Video] | ||
+ | |||
+ | ====Compatibility==== | ||
+ | Fusion Registry 10.5.8 or higher<br> | ||
+ | FusionXL 2.3.0 or higher | ||
== Function Execution == | == Function Execution == | ||
Line 17: | Line 36: | ||
The steps to run the function are: | The steps to run the function are: | ||
# Select a range of cells | # Select a range of cells | ||
− | # In the formula entry box (located above the worksheet) enter the function | + | # In the formula entry box (located above the worksheet) enter the function e.g. =FXLData("EXR", "M.CAD.EUR.SP00.A", "2018") |
# Press CTRL+SHIFT+ENTER at the same time, this runs the function and populates the selected area with data | # Press CTRL+SHIFT+ENTER at the same time, this runs the function and populates the selected area with data | ||
Line 62: | Line 81: | ||
# Include Attribute Breakdown (optional default=true) | # Include Attribute Breakdown (optional default=true) | ||
# Label Function (optional default=Id) | # Label Function (optional default=Id) | ||
+ | # Dimension In Header Columns (default=TIME_PERIOD) | ||
# Round DP (optional default=do not round) | # Round DP (optional default=do not round) | ||
'''Example''' | '''Example''' | ||
− | =FXLData("EXR","A.EUR.GBP._Z",2010,2020,FALSE,TRUE,FALSE,FALSE,"id", 2) | + | =FXLData("EXR","A.EUR.GBP._Z",2010,2020,FALSE,TRUE,FALSE,FALSE,"id", "REF_AREA", 2) |
== Dataflow Identification == | == Dataflow Identification == | ||
Line 80: | Line 100: | ||
== Series Identification == | == Series Identification == | ||
+ | [[File:FusionXL ShortCode.png|thumb|In the Fusion Data Browser the series identification is called the Short Code]] | ||
+ | [[File:FXLShortCode2.png|thumb|In Fusion Registry, the data web service page can help build short codes, including codes with wildcards and multiple selections - which result in multiple series]] | ||
The series identification is the SDMX Series Key, for example '''D.RON.EUR.SP00.A'''. This is the combination of Dimension values for each dimension, for example the series key is shown in BOLD in the following SDMX query. Note it is also valid to include colon separators, for example '''D:RON:EUR:SP00:A''' | The series identification is the SDMX Series Key, for example '''D.RON.EUR.SP00.A'''. This is the combination of Dimension values for each dimension, for example the series key is shown in BOLD in the following SDMX query. Note it is also valid to include colon separators, for example '''D:RON:EUR:SP00:A''' | ||
Line 98: | Line 120: | ||
When the series key includes multiple series, the dataset will display as a row per series. Take note when creating a selection zone in the worksheet. | When the series key includes multiple series, the dataset will display as a row per series. Take note when creating a selection zone in the worksheet. | ||
− | |||
== Start Period and End Period == | == Start Period and End Period == | ||
Line 104: | Line 125: | ||
== Include Series Key Column == | == Include Series Key Column == | ||
+ | [[File:FusionXL SeriesColumn.png|thumb|Showing the series column - where each series identifier is a join of the dimension values (joined by colon)]] | ||
This can be useful if multiple series are being returned from a single FXLData request (i.e. the serie key contains wildcards or multiple selections). A column is added to the output which includes the series key for the data. | This can be useful if multiple series are being returned from a single FXLData request (i.e. the serie key contains wildcards or multiple selections). A column is added to the output which includes the series key for the data. | ||
Line 150: | Line 172: | ||
== Label Function == | == Label Function == | ||
+ | [[File:FXL Name ID.png|thumb|Showing labels and ids (both) in the output table]] | ||
This function is only relevant when the dimension breakdown is included. The 3 valid values are: | This function is only relevant when the dimension breakdown is included. The 3 valid values are: | ||
* id - output the ID of the dimension and reported value only | * id - output the ID of the dimension and reported value only | ||
Line 167: | Line 190: | ||
|} | |} | ||
+ | == Dimension In Header Columns == | ||
+ | [[File:FusionXL DimAtObs.png|thumb|Showing geo in the header columns]] | ||
+ | Specifies which Dimension values will be presented in the columns. The default value if not specified is TIME_PERIOD, which results with each row showing a series and the corresponding time periods make up the columns. | ||
+ | |||
+ | |||
+ | '''Example default behaviour:''' | ||
+ | {| class="wikitable" | ||
+ | |- | ||
+ | ! Frequency !! Reference Area !! 2002 !! 2003 | ||
+ | |- | ||
+ | | Annual || United Kingdom || 19.2 || 29.2 | ||
+ | |- | ||
+ | | Annual || France || 19.3 || 29.3 | ||
+ | |- | ||
+ | | Annual || Germany || 19.4 || 29.4 | ||
+ | |} | ||
+ | |||
+ | When passing the ID of any other Dimension into this function parameter, the table will place that Dimension's values into the columns, and time will become part of the rows, for example: | ||
+ | |||
+ | '''Example REF_AREA in columns:''' | ||
+ | {| class="wikitable" | ||
+ | |- | ||
+ | ! Frequency !! Time !! United Kingdom !! France !! Germany | ||
+ | |- | ||
+ | | Annual || 2002 || 19.2 || 19.3 || 19.4 | ||
+ | |- | ||
+ | | Annual || 2003|| 29.2 || 29.3 || 29.4 | ||
+ | |} | ||
== Round DP == | == Round DP == | ||
Line 214: | Line 265: | ||
'''Default Behaviour''' halfup | '''Default Behaviour''' halfup | ||
+ | |||
+ | |||
+ | [https://wiki.sdmxcloud.org/index.php?title=Main_Page§ion=11 Back to Main Page] |
Latest revision as of 06:00, 8 September 2021
Contents
- 1 Overview
- 2 Function Execution
- 3 Function Arguments
- 4 Dataflow Identification
- 5 Series Identification
- 6 Start Period and End Period
- 7 Include Series Key Column
- 8 Include Header Row
- 9 Include Dimension Breakdown
- 10 Include Dimension Breakdown
- 11 Label Function
- 12 Dimension In Header Columns
- 13 Round DP
Overview
The =FXLData() formula is used to retrieve specified series from the Fusion Registry to which FusionXL is currently connected directly into a worksheet.
Excel Array Formula
=FXLData() is an Excel 'array formula'.
Array formulas work in a similar way to normal single-cell formulas like =SUM() and =STDEV() except that the result is an 'array' of cells spanning a number of rows and columns rather than just the cell into which the formula has been entered. See Microsoft's article on array formulas for more general information.
The result of querying Fusion Registry for data is generally one or more time series, each with potentially multiple observations. As a consequence, an =FXLData() formula in a worksheet returning five series with 10 time periods requires an array of 5 rows and 10 columns to hold the result.
Excel distinguishes array formulas in the function bar by wrapping them in {}.
Example
Formula in Cell A1 with Array Range A1:O20
=fxldata("ECB,EXR,1.0","A..EUR.SP00.A","2010","2018",TRUE,TRUE,TRUE,FALSE,FALSE,2)
Identifying the series to retrieve
The first two arguments specify the series. All other arguments are optional.
=fxldata("ECB,EXR,1.0","A..EUR.SP00.A")
1. ECB,EXR,1.0
specifies the Dataflow using either the full form with Agency and Version as shown, the Agency and Dataflow ECB,EXR
or just using the Datflow id EXR
2. A..EUR.SP00.A
series key conforming to the SDMX REST API GET data query specification
Video demo
Compatibility
Fusion Registry 10.5.8 or higher
FusionXL 2.3.0 or higher
Function Execution
It is important to remember that the FXLData function returns an Array (multiple rows, multiple columns) of data. As such, a selection covering multiple cells should first be made, this is the area which will be populated with data. This area should be at least as big as the amount of data that is to be returned, it can be bigger.
The steps to run the function are:
- Select a range of cells
- In the formula entry box (located above the worksheet) enter the function e.g. =FXLData("EXR", "M.CAD.EUR.SP00.A", "2018")
- Press CTRL+SHIFT+ENTER at the same time, this runs the function and populates the selected area with data
Refresh Data
To refresh the data, select the first cell in the array, then click in the formula entry box, then press CTRL+SHIFT+ENTER at the same time.
Change Function Parameters
To change the function parameters, select the first cell in the array, click in the formula entry box to update formula input, and press CTRL+SHIFT+ENTER at the same time to refresh the data.
Increase Columns in output array
It is possible to increase the array column selection size. To do this:
- Select the first cell (top left cell),
- Drag an area to cover the same number of rows that are in the current array
- Include in the selection all the columns of the current array and any additional columns that are required for the data
- When the selected area has been choosen, click the formula box and press CTRL+SHIFT+ENTER at the same time to refresh the data.
Increase Rows in output array
It is not possible to change the rows in the array without first deleting the rows of the original array and starting again
Dynamic Data Refersh
If the FXLData function includes references to other cells, then changing the values of the referenced cells will automatically refresh the data in the array. For example:
- Enter into Cell B1 the Dataflow ID
- Enter into Cell B2 a series key
- Enter into Cell B3 a start period
- Enter into Cell B4 an end period
- Select an area on the worksheet for the result array to go in
- Type the following function in the formula bar: =FXL(B1, B2, B3, B4) followed by press CTRL+SHIFT+ENTER
- Change the value of either the dataflow Id, series key, start, or end period (Cells B1-B4) on change the data will automatically refresh
Function Arguments
- Dataflow Identification
- Series Identification
- Start Period (optional default to first period for which there is data)
- End Period (optional default to last period for which there is data)
- Include Series Key Column (optional default=true)
- Include Header Row (optional default=true)
- Include Dimension Breakdown (optional default=true)
- Include Attribute Breakdown (optional default=true)
- Label Function (optional default=Id)
- Dimension In Header Columns (default=TIME_PERIOD)
- Round DP (optional default=do not round)
Example
=FXLData("EXR","A.EUR.GBP._Z",2010,2020,FALSE,TRUE,FALSE,FALSE,"id", "REF_AREA", 2)
Dataflow Identification
The dataflow identifier is the first argument to the function. The parameter syntax is aligned to the SDMX REST API, in that the identifier must include the Dataflow Id and can optionally include the Agency and Version for the Dataflow. Examples are:
By ID only
EXR
By Agency and Id
ECB,EXR
By Agency, Id, and Version
ECB,EXR,1.0
Series Identification
The series identification is the SDMX Series Key, for example D.RON.EUR.SP00.A. This is the combination of Dimension values for each dimension, for example the series key is shown in BOLD in the following SDMX query. Note it is also valid to include colon separators, for example D:RON:EUR:SP00:A
https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/D.RON.EUR.SP00.A
To help build a series key, the Fusion Registry web service page shows the key as the query is built. In addition, the CSV data formats, including Fusion-CSV-Series, SDMX-CSV, and Fusion-CSV-TS can be asked to include the series key in the output dataset. The Fusion Data Browser's Series List page also shows the Short Code for a series, which is the same as the series key.
The Series Key uniquely identifies one single series, however Dimension values can be left blank, to indicate a wildcard - or alternatively multiple selections for a Dimension can be made using the plus operator (meaning this OR that). Example keys include:
EXB Exchange Rates any currency (note the .. with no selection for currency)
D..EUR.SP00.A
EXB Exchange Rates currencies include: GBP, USD and NZD
D.GBP+NZD+USD.EUR.SP00.A
Note as each series may have missing values, the server will ensure each series contains the same 'shape' i.e each time period has a value, because of this each missing time period will report a zero value
When the series key includes multiple series, the dataset will display as a row per series. Take note when creating a selection zone in the worksheet.
Start Period and End Period
Both dates are optional, if they are provided they can be in any valid SDMX Date format regardless of the frequency of the data being retrieved. The end period always resolves to end of period, for example 2001 resolves to 2001-12-31T23:59:59)
Include Series Key Column
This can be useful if multiple series are being returned from a single FXLData request (i.e. the serie key contains wildcards or multiple selections). A column is added to the output which includes the series key for the data.
Example, series column=true:
Series | 2019-01 | 2019-02 | 2019-03 |
---|---|---|---|
M:CAD:EUR:SP00:A | 1.519 | 1.44 | 1.51 |
Include Header Row
When set to true (which is the default), the output will include a row which adds labels to each column, this may be the name of the Dimension (if breakdown is included) or the Time Period.
Example, header row=true:
FREQ | CURRENCY | CURRENCY_DENOM | EXR_TYPE | Series | 2019-01 |
---|---|---|---|---|---|
M | CAD | EUR | SP00 | M:CAD:EUR:SP00:A | 1.519 |
Example, header row=false:
M | CAD | EUR | SP00 | M:CAD:EUR:SP00:A | 1.519 |
Include Dimension Breakdown
When set to true, a column will be output for each Dimension (or 2 columns if labels is set to both) of the series, providing it's value.
FREQ | CURRENCY | CURRENCY_DENOM | EXR_TYPE | 2019-01 |
---|---|---|---|---|
M | CAD | EUR | SP00 | 1.519 |
Include Dimension Breakdown
When set to true, a column will be output for each Attribute (or 2 columns if labels is set to both) of the series, providing it's value.
FREQ | CURRENCY | CURRENCY_DENOM | EXR_TYPE | TITLE | 2019-01 |
---|---|---|---|---|---|
M | CAD | EUR | SP00 | ECB reference exchange rate, Canadian dollar/Euro, 2:15 pm (C.E.T.) | 1.519 |
Label Function
This function is only relevant when the dimension breakdown is included. The 3 valid values are:
- id - output the ID of the dimension and reported value only
- name - output the Name for the dimension and reported value only
- both - output a column with the Id and another for name for the dimension and reported value only
Example (labels=both):
FREQ | Frequency | REF_AREA | Reference Area | 2002 |
---|---|---|---|---|
A | Annual | UK | United Kingdom | 19.2 |
A | Annual | FR | France | 19.3 |
A | Annual | DE | Germany | 19.4 |
Dimension In Header Columns
Specifies which Dimension values will be presented in the columns. The default value if not specified is TIME_PERIOD, which results with each row showing a series and the corresponding time periods make up the columns.
Example default behaviour:
Frequency | Reference Area | 2002 | 2003 |
---|---|---|---|
Annual | United Kingdom | 19.2 | 29.2 |
Annual | France | 19.3 | 29.3 |
Annual | Germany | 19.4 | 29.4 |
When passing the ID of any other Dimension into this function parameter, the table will place that Dimension's values into the columns, and time will become part of the rows, for example:
Example REF_AREA in columns:
Frequency | Time | United Kingdom | France | Germany |
---|---|---|---|---|
Annual | 2002 | 19.2 | 19.3 | 19.4 |
Annual | 2003 | 29.2 | 29.3 | 29.4 |
Round DP
Rounds to the number of decimal places given, with a maximum value of 10 decimal places
Syntax
[decimals];[maxdp];[round function]
NOTE: The maxdp and round function are optional
Example Round Value
2
Example Round Value with round function
0;up
Example Round Value with round function and maxdp
0;maxdp;up
Max DP
If provided, this the round function will be applied as the maximum number of decimal places, observation values will be rounded to this maximum but will not be padded out with additional zero values to match the round. For example, if the observation value is 2.32 and the round value is set to 4, the number will be displayed as 2.3200, if maxdp is added, the nuber will remain as 2.32 (as it is below the round threshold and not padded with zero values to meet the threshold).
Default Behaviour Pad values with zeros to meet the round value
Round Function
The round function determines the mathmatical rule applied to the rounding, the following options are valid
Round Function | Description |
---|---|
ceil | Rounding mode to round towards positive infinity. |
floor | Rounding mode to round towards negative infinity. |
halfeven | Rounding mode to round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor. |
halfup | Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. |
halfdown | Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down. |
up | Rounding mode to round away from zero. |
down | Rounding mode to round towards zero. |
Default Behaviour halfup