FXLData Wizard

From Metadata Technology Wiki
Jump to navigation Jump to search

An Excel ribbon bar tool for interactively building and editing FXLData functions

Overview

The FXLData Wizard is part of the FXLData add-in for Excel which provides a collection of functions for retrieving statistical data from a Fusion Registry directly into a worksheet.

The Wizard helps in the task of building and modifying =FXLData() and =FXLDataV() functions which take a number of parameters including the Fusion Registry to connect to, the Dataflow and a data query expression - typically an SDMX series key.

FXLData Excel ribbon

Installing the FXLData Excel add-in adds a FXLData menu option and the ƒx button to the ribbon to launch the Wizard.

  • Select a blank cell in the worksheet and launch the Wizard to create a new FXLData function.
  • Modify an existing =FXLData() or = FXLDataV() function by selecting the worksheet cell before launching the Wizard.

Ribbon Bar

Wizard icon.JPG Launch the function helper 'Wizard'

Refresh icon.JPG Refresh the FXLData function in the current selected cell when working in Legacy Mode

Purge icon.JPG Purge the local data cache - use this to refresh the result sets from the Fusion Registry data source

Automatic Calculation control whether changes to FXLData formulas automatically refresh the dataset in Legacy Mode - uncheck to speed up bulk changes to worksheets such as deleting large numbers of cells

Wizard

FXLData Wizard Example with Annotations.jpg

Guidance

  • All parameters can take either a literal value (e.g. a number or a text string), a cell reference (e.g. A4) or an Excel formula (e.g. sum(A4,A6))
  • A parameter's result value is shown to the right which is just the literal, the contents of the referenced cell, or the result of the Excel formula
  • Red indicates the value is not valid
  • Use the Apply button to apply changes made in the Wizard to the worksheet
  • The FXLData function is refreshed automatically everytime the Apply button is clicked
  • The Registry URL is the base URL of the Fusion Registry data source - the same URL used to access the web user interface
  • Use the + and - buttons to add and remove favourite Fusion Registries from the drop down list - the Def button sets the default
  • The Dataflow can be either just the source Dataflow's ID (e.g. EXR) provided that is unique, or the fully-qualified Dataflow identifier (e.g. ECB,EXR,1.0)
  • The Dataflow drop down provides an up to date list of all dataflows from the selected Fusion Registry that contain data
  • The Data Expression defines precisely what series to retrieve using an SDMX Series Key or JSON packet describing a series basket that can be generated using Fusion Data Browser
  • Use the keyword all for the Data Expression to retrieve all series for the selected Dataflow
  • The Start Period and End Period select only observations between those dates which must be in SDMX time format - both are optional
  • REST Args allows additional data REST query arguments to be set such as rounding, calculated measures, dimension at observation and aggregations
  • The REST Args expression must follow the standard syntax for URL parameters using '&' as a separator , e.g. round=2&attributes=UNIT
  • Favourite Rest Arg expressions can be saved and deleted using the + and - buttons - the ? button provides help on the available arguments and their usage