Difference between revisions of "FXLData"

From Fusion Registry Wiki
Jump to navigation Jump to search
Line 1: Line 1:
−
=FXLData addin for Excel=
+
An Excel addin for retrieving statistical data directly from a Fusion Registry service into a worksheet.
 +
=Overview=
 
The FXLData addin provides a collection of Excel functions that can be used to retrieve data directly from Fusion Registry into normal worksheets.
 
The FXLData addin provides a collection of Excel functions that can be used to retrieve data directly from Fusion Registry into normal worksheets.
  
−
The functions work like normal Excel formulas such as =sum() and =average() with the exception that the result is an array of series and observation values spanning multiple rows and columns rather than a single cell. Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] provides useful background information on how that these ''array formulas'' work in Excel.
+
The functions work like normal Excel formulas with the exception that the result is an array of series and observation values spanning multiple rows and columns. This behaviour differs from functions such as =sum() and =average() that return a single value into the cell containing the formula. Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] provides useful background information on how that these ''array formulas'' work.
 +
 
 +
The addin provides four separate formulas that all retrieve selected data but in slightly different ways.
 +
 
 +
====Horizontal time-series presentation====
 +
Returns a selected dataset in time-series format with one series per row
 +
[[File:Fxldata example1.PNG|thumb|=FXLData() example]]
  
−
The addin provides four separate functions<br>
 
 
'''=FXLData()'''
 
'''=FXLData()'''
−
[[File:Fxldata example1.PNG|thumb|=FXLData() example]]
 
−
* Returns a selected dataset in time-series format with one series per row.
 
 
* The function has three mandatory arguments: Base URL of the Fusion Registry, Dataflow, Series Key
 
* The function has three mandatory arguments: Base URL of the Fusion Registry, Dataflow, Series Key
 
* A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown
 
* A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown
Line 15: Line 19:
 
  =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)
 
  =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)
  
−
{|
+
'''=FXLDataUrl()'''
−
|-
+
* The function has a single mandatory argument - a fully-formed Fusion Registry data query URL
−
| '''=FXLData()'''|| Returns a selected dataset in time-series format with one series per row. The function has three mandatory arguments, plus a number of optional. || Example
+
* A single optional argument allows the maximum number of series to be set
−
|-
+
Examples
−
| '''=FXLDataUrl()'''|| Returns a selected dataset || Example
+
=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id")
−
|-
+
  =FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id",1000)
−
| Example || Example || Example
 
−
|-
 
−
| Example || Example || Example
 
−
|}
 

Revision as of 07:23, 7 September 2021

An Excel addin for retrieving statistical data directly from a Fusion Registry service into a worksheet.

Overview

The FXLData addin provides a collection of Excel functions that can be used to retrieve data directly from Fusion Registry into normal worksheets.

The functions work like normal Excel formulas with the exception that the result is an array of series and observation values spanning multiple rows and columns. This behaviour differs from functions such as =sum() and =average() that return a single value into the cell containing the formula. Microsoft's documentation provides useful background information on how that these array formulas work.

The addin provides four separate formulas that all retrieve selected data but in slightly different ways.

Horizontal time-series presentation

Returns a selected dataset in time-series format with one series per row

=FXLData() example

=FXLData()

  • The function has three mandatory arguments: Base URL of the Fusion Registry, Dataflow, Series Key
  • A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown

Examples 

=FXLData("https://demo.metadatatechnology.com/FusionRegistry","ECB,EXR,1.0","A...")
=FXLData("https://demo.metadatatechnology.com/FusionRegistry","ECB,EXR,1.0","A...",1000,"round=2",2010,2020,both,true,true,true,false)
=FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)

=FXLDataUrl()

  • The function has a single mandatory argument - a fully-formed Fusion Registry data query URL
  • A single optional argument allows the maximum number of series to be set

Examples 

=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id")
=FXLDataUrl("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id",1000)
Retrieved from "https://wiki.sdmxcloud.org/index.php?title=FXLData&oldid=3894"