Difference between revisions of "FusionXL FXLData - RETIRED"

From Fusion Registry Wiki
Jump to navigation Jump to search
(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 FusionXL =FXLData() formula is used to retrieve data from the connected Fusion Registry directly into a worksheet.
+
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' which works in a similar way to normal single-cell formulas like =SUM() and =STDEV() except that the output is spread over 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 about what they are and how they work.
+
====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.
  
Why use an array formula?<br>
+
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.
Because Fusion Registry is a statistical data warehouse, the results of a query are always one or more series each potentially containing multiple observations over time. The simplest use of FXLData is to retrive a single series. In this case, FXLData will return an array consisting of one row and multiple columns with each column representing a separate time period.  
 
  
 +
Excel distinguishes array formulas in the function bar by wrapping them in {}.
  
'''FusionXL Versiom 2.3.0 or higher is required '''
+
====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)
  
See [https://www.youtube.com/watch?v=zpslTahktak&feature=youtu.be Video] for Demo
+
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 (e.g. =FXLData("EXR", "M.CAD.EUR.SP00.A", "2018")
+
# 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&section=11 Back to Main Page]

Latest revision as of 06:00, 8 September 2021

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)

Result
FXLdataExample.PNG

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

Video

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:

  1. Select a range of cells
  2. In the formula entry box (located above the worksheet) enter the function e.g. =FXLData("EXR", "M.CAD.EUR.SP00.A", "2018")
  3. Press CTRL+SHIFT+ENTER at the same time, this runs the function and populates the selected area with data

FXLData.png

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:

  1. Select the first cell (top left cell),
  2. Drag an area to cover the same number of rows that are in the current array
  3. Include in the selection all the columns of the current array and any additional columns that are required for the data
  4. 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:

  1. Enter into Cell B1 the Dataflow ID
  2. Enter into Cell B2 a series key
  3. Enter into Cell B3 a start period
  4. Enter into Cell B4 an end period
  5. Select an area on the worksheet for the result array to go in
  6. Type the following function in the formula bar: =FXL(B1, B2, B3, B4) followed by press CTRL+SHIFT+ENTER
  7. Change the value of either the dataflow Id, series key, start, or end period (Cells B1-B4) on change the data will automatically refresh


FXLData Advanced.png

Function Arguments

  1. Dataflow Identification
  2. Series Identification
  3. Start Period (optional default to first period for which there is data)
  4. End Period (optional default to last period for which there is data)
  5. Include Series Key Column (optional default=true)
  6. Include Header Row (optional default=true)
  7. Include Dimension Breakdown (optional default=true)
  8. Include Attribute Breakdown (optional default=true)
  9. Label Function (optional default=Id)
  10. Dimension In Header Columns (default=TIME_PERIOD)
  11. 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

In the Fusion Data Browser the series identification is called the Short Code
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

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

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.

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

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:

  • 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

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:

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


Back to Main Page