Difference between revisions of "FXLData Wizard"
Jump to navigation
Jump to search
(→Guidance) |
|||
| (25 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | [[Category:How to and other features in FXLData]] | |
| − | + | __NOTOC__ | |
| − | + | An Excel ribbon bar tool for interactively building and editing [[FXLData]] functions. | |
=Overview= | =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 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. | ||
| Line 7: | Line 7: | ||
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|series key]]. | 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|series key]]. | ||
| − | [[File:FXLData Wizard Ribbon. | + | [[File:FXLData Wizard Ribbon 058.JPG|FXLData Excel ribbon]] |
| − | [[FXLData#Quick_Start|Installing]] the FXLData- | + | [[FXLData#Quick_Start|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. | * 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. | * Modify an existing =FXLData() or = FXLDataV() function by selecting the worksheet cell before launching the Wizard. | ||
| + | |||
| + | =Ribbon Bar= | ||
| + | [[File:Wizard icon.JPG]] Launch the function helper 'Wizard' | ||
| + | |||
| + | [[File:Refresh icon.JPG]] Refresh the FXLData function in the current selected cell when working in Legacy Mode | ||
| + | |||
| + | [[File: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= | =Wizard= | ||
| − | [[File: | + | [[File:Wizard.png|1200px]] |
=Guidance= | =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)) | * 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 | * 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 | * 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 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 | * 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 | * 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 | + | * 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 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 Formats|SDMX time format]] - both are optional | ||
| + | * '''REST Args''' allows additional data [[Data Query Web Service|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 | ||
| + | * Daily Time Format check box - (introduced in July 2024) - '''Daily Time Format''' or YYYY-MM-DD both an Excel friendly and [[SDMX_Time_Formats |SDMX compatible format]]. For instance if Daily Time Format is checked and an underlying date is 2024 then 2024-12-31 will be rendered to Excel. | ||
Latest revision as of 02:33, 22 July 2024
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.
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
Launch the function helper 'Wizard'
Refresh the FXLData function in the current selected cell when working in Legacy Mode
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
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
- Daily Time Format check box - (introduced in July 2024) - Daily Time Format or YYYY-MM-DD both an Excel friendly and SDMX compatible format. For instance if Daily Time Format is checked and an underlying date is 2024 then 2024-12-31 will be rendered to Excel.
