FusionXL FXLData - RETIRED

Overview

The FusionXL =FXLData() formula is used to retrieve specified series from the connected Fusion Registry 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 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 about what they are and how they work.

Why use an array formula?

The result of a query is generally one or more series, each with potentially multiple observations. As a consequence, an FXLData formula returning five series with 10 time periods requires an array of 5 rows and 10 columns to hold the result.

Example

Formula in Cell A1 with Array Range A1:020

=fxldata("ECB,EXR,1.0","A..EUR.SP00.A","2010","2018",TRUE,TRUE,TRUE,FALSE,FALSE,2)

Result

Specifying the series to retrieve

Argument 1: ECB,EXR,1.0 specifies the Dataflow using either the full form with Agency and Version as shown, or just using the Datflow id ECB
Argument 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 Versiom 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

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

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. Round DP (optional default=do not round)

Example

=FXLData("EXR","A.EUR.GBP._Z",2010,2020,FALSE,TRUE,FALSE,FALSE,"id", 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

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