FusionXL FXLData - RETIRED

Overview

The FXLData Function enables data retrieval directly from the connected Fusion Registry server, into the worksheet. The function FXLData=[args] is simpy entered into a cell in the worksheet, and the data is pulled into the worksheet from the connected server.

As the function can be re-run at any time, the worksheet can be saved, and when re-opened the data can easily be refreshed.

Function Execution

It is important to rememer 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 an populates the selected area with data

To refresh the data, select the first cell in the array, and press CTRL+SHIFT+ENTER at the same time.

To change the function parameters, select the first cell inthe array, update the function in the formula input, and press CTRL+SHIFT+ENTER at the same time to refresh the data.

To increase the array output size, select the range, ensure it covers the full selection of the last range, click inside the formula input box and press CTRL+SHIFT+ENTER at the same time to refresh the data.

It is not possible to change part of the array, or reduce the size, without deleting the rows of the original array and starting again

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)

Example

=FXLData("EXR","A.EUR.GBP._Z",2010,2020,FALSE,TRUE,FALSE,FALSE,"id")

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 seperators, 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
M:CAD:EUR:SP00:A	1.519

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 dimenison 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 dimenison and reported value only
• both - output a column with the Id and another for name for the dimenison 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