Refreshable Excel data spreadsheets

From Fusion Registry Wiki
Jump to navigation Jump to search

Overview

Refreshable data spreadsheets can be created using Excel's standard data management features with the data being retrieved directly from Fusion Registry's CSV REST web services.

Excel Refreshable Data Spreadsheet

Principles

Excel has built-in functionality for loading data from a range of sources including the Web which allows data to be retrieved directly from Fusion Registry's REST API. The REST API can provide data in a number of different formats including XML and JSON, but CSV is the best choice being easily interpreted and processed by Excel.

A simple refreshable sheet can be created using a Fusion Registry GET data query URL as the source.
Example https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv

Basic Steps

  1. Open a new Excel workbook and choose the Data menu
  2. From the ribbon bar choose New Query > From Other Sources > From Web
  3. Enter the Fusion Registry GET data query URL for the data you are interested in

Excel will connect to the URL and download the data into the worksheet which can be saved and used as normal.
The data can be refreshed as needed using the Refresh All option on the Data ribbon bar.
The Show Queries option, also on the Data ribbon bar, opens the query sidebar with more information about the queries and data sources used in the workbook.

Generating Fusion Registry GET data query URLs

URLs can be crafted by hand if you understand the SDMX REST syntax and know what datasets you need. However, there are a couple of other ways to discover data and generate the URLs automatically.

Fusion Data Browser

Fusion Data Browser Query Syntax Function
  1. Launch the Fusion Data Browser (demo)
  2. Choose a data set and use the quick filters to select the data of interest
  3. Export to CSV, but rather than exporting the data as a file choose the Query Syntax function which will display the URL

The URL for the query can be copied and pasted into Excel.

Note: Check that a GET query is generated. Complex data selections in Fusion Data Browser generate a HTTP POST which requires the Power Query Editor in Excel. There's more about this in the section on GET and POST queries.

Fusion Registry Web Services Query Builder

  1. From the Fusion Registry user interface, choose Web Service > Data from the left-hand menu bar
  2. Choose the Dataflow and the sub-cube using the query builder
  3. Choose the CSV data format

The URL for the query can be copied and pasted into Excel.

Private Fusion Registry data services and authentication

Excel will need to authenticate with the REST API if the Registry is operating in private mode or Content Security rules are in place to control user access to data.

In private mode, Excel will detect authentication is required and automatically prompt for a username and password. In other cases, you may need to explicitly set the credentials that Excel will use when calling the API.

  1. In Excel, after entering the URL, choose the Transform Data option on the data preview window - this will show the Power Query Editor
  2. On the Power Query ribbon bar, choose the Data Source Setting option
  3. Choose the new data source from the list and select the Edit Permissions option
  4. Under Credentials choose Edit...
  5. Choose Basic and enter the User name and Password

Note: Excel only supports authentication on GET queries.

GET and POST queries

HTTP GET data queries largely follow the standard SDMX syntax and are suitable where a regular sub-cube is required from a single Dataflow. For more complex queries Fusion Registry provides a POST option which allows data to be retrieved from multiple Dataflows and arbitrary selections of series which cannot be described using the sub-cube syntax.

GET queries

Excel uses the HTTP GET method by default for web data sources.

When creating a refreshable Excel sheet using the Fusion Registry REST API as the source, the format URL parameter or the HTTP Accept header must be set to specify the CSV data format. While both are supported by Excel, the format parameter approach is generally recommended for simplicity. Use Excel's 'advanced' web query option to set Accept and other headers.

Four different basic CSV formats are supported by Fusion Registry, all of which can be used in refreshable Excel data sheets.

Format Description URL Parameter Accept Header Format Parameter Example
Fusion CSV Flat CSV with one observation per row format=csv application/vnd.csv https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv
Fusion CSV Time Series Time Series CSV with one series per row and time periods across the columns format=csv-ts application/vnd.csv-ts https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-ts
Fusion CSV Vertical Time Series Time Series CSV with one series per column format=csv-series application/vnd.csv-series https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-series
SDMX CSV The SDMX standard CSV format format=sdmx-csv application/vnd.sdmx-csv https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=sdmx-csv

POST queries

Editing M Code in Power Query

POST queries work by describing the query in JSON and sending that to the Fusion Registry REST API using the HTTP POST method.

In Excel, POST queries need to be coded in Microsoft's Power Query M formular language using the Power Query Editor advanced editing function.

The following Microsoft M code example selects nine specific series identified by their series keys from the ECB's Exchange Rate dataset. The JSON query is assigned to the content variable and passed as a parameter to the Web.Contents() function using the expression Content=Text.ToBinary(content). The 'flat' CSV data format is used by setting the Accept header to application/vnd.csv. 

let
content = "{
     ""queries"": [{
     ""obj"": ""DataQueryDef"", 
     ""series"": [
       ""A:E5:PLN:ERD0:A"", 
       ""A:E5:PLN:ERC0:A"", 
       ""A:E5:MXN:EN00:A"", 
       ""A:E5:NOK:ERU1:A"",
       ""M:CZK:EUR:SP00:A"",
       ""M:CAD:EUR:SP00:A"",
       ""A:CZK:EUR:SP00:A"",
       ""A:CAD:EUR:SP00:E"",
       ""A:SEK:EUR:SP00:E""],
     ""dataflow"": {
       ""id"": ""EXR"",
       ""agencyId"": ""ECB"",
       ""version"": ""1.0""
     },
     ""type"": ""series""}]
   }",    
   Source = Csv.Document(Web.Contents("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data", [Headers=[Accept="application/vnd.csv;labels=both", #"Content-Type"="application/json"],Content=Text.ToBinary(content)]
   ),[Delimiter=",", Columns=31, Encoding=65001, QuoteStyle=QuoteStyle.None]),
   #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
   #"Promoted Headers"

To build a refreshable spreadsheet using this code:

  1. Create an new Excel worksheet and from the Data ribbon bar choose New Query > From Other Sources > Blank Query
  2. The Power Query Editor should be displayed - in the Home ribbon bar choose Advanced Editor and paste in the M code

Power Query should display a preview of the data. Choose Close and Load to complete the process and load the entire query result into the worksheet.

Note: POST queries will only work in Excel if the Fusion Registry API is public due to a restriction in Microsoft's Power Query engine.

Generating the Microsoft M code for a POST query

Fusion Data Browser generated POST query JSON

For complex queries, in particular where lists of specific series are chosen or when using Series Baskets, Fusion Data Browser can generate the JSON needed for the M code.

  1. Launch the Fusion Data Browser
  2. Choose the required datasets and series using Series Baskets if needed
  3. Choose Export to CSV, and choose the Query Syntax function which will display the POST URL and the JSON
  4. Build the M code by hand setting the value of the content variable to the JSON, setting the URL of the Fusion Registry REST API in the Web.Contents function, and similarly setting the Accept header to the CSV data format required

Note: Double-quotes in the JSON need to be escaped in the M code by replacing each single " mark in the JSON with two marks: ""

Raw JSON 

"agencyId": "ECB"

JSON in the M Code 

""agencyId"": ""ECB""

Fusion Data Browser generated M code (forthcoming feature)

A function will be provided to automatically generate the complete M code needed for a query in a forthcoming release of the Fusion Data Browser.

M code for automatically promoting the first row to headers

The M code example below can be used to pull any API data query directly into a refreshable Excel worksheet, automatically promoting the component labels in the first row to the headers. Just replace the the 'url' variable with your data GET query URL.

To execute, from the Excel ribbon bar:
Data > New Query > Blank Query > Advanced Editor
Paste the M code into the Advanced Editor box.
Choose 'Done', followed by 'Close and Load'. 

let
  url = "https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.HUF+MXN+MYR...?format=csv-ts",
  Source = Csv.Document(Web.Contents(url),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
  #"Promoted Headers"

IDs and Labels

By default, Fusion Registry will return IDs only in data query results.

You can control whether the data returned to the worksheet includes IDs, Labels or both using additional URL parameters or modified HTTP Accept Headers.

URL Parameter Meaning Example
labels=id IDs only https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=id
labels=name Names only https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=name
labels=both Both names and IDs https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=both

Adding a labels suffix to the Accept header achieves the same results:

Accept Header Meaning
application/vnd.csv;labels=both Flat CSV (one row per observation) with separate columns for IDs and Names
application/vnd.csv-ts;labels=name Time Series CSV (one row per series) with Names only
application/vnd.csv-series;labels=both Vertical Time Series CSV (one column per series) with both IDs and Names
Retrieved from "https://wiki.sdmxcloud.org/index.php?title=Refreshable_Excel_data_spreadsheets&oldid=6012"