Browse

Download data via refreshable Excel

You can create refreshable Excel worksheets with the help of the ECB Data Portal API calls and Power Query for Excel. There are two alternative methods:

Method 1 – Basic approach for retrieving data 

This method allows you to retrieve data in a refreshable Excel spreadsheet format without series key code descriptions in an unstructured way. Hence, choose this method if you are familiar with the datasets' code descriptions. To retrieve data with all code descriptions in a formatted way, refer to Method 2 - Advanced approach for retrieving data and code descriptions

  1. In Excel, go to Data > From Web > Basic

    Figure 1


    Image
    Excel load data from web

  2. Insert the desired URL and then press on Load. 

    Figure 2


    Image
    Excel specify URL

    The URL must be in the ECB Data Portal API format, as specified in the API help page, and must end with ?format=csvdata. For example,

    https://data-api.ecb.europa.eu/service/data/EXR/M..EUR.SP00.A?format=csvdata

  3. Click on Load and the data will be retrieved onto a new worksheet.  
  4. You may format the data by using a PivotTable, click on Insert > PivotTable > New worksheet. Then, choose the fields you want to display in the PivotTable. 

    Figure 3


    Image
    Figure 1.

  5. To refresh the Excel file, go to Data > Refresh All. 

    Figure 4


    Image
    Refresh all data in Excel

 

Method 2 – Advanced approach for retrieving data and code descriptions

This method allows you to retrieve data together with all code descriptions already formatted in PivotTable view.

  1. In Excel, go to Data > From Web > Advanced
  2. In the "URL parts" field, insert the ECB Data Portal API URL without any format specified as documented on the API help page. For example: https://data-api.ecb.europa.eu/service/data/EXR/M..EUR.SP00.A 

    In the HTTP request header parameters field, specify the Accept parameter as application/vnd.ecb.data+csv;version=1.0.0. As explained in the Content negotiation page, this will download the data in a CSV format already optimised for PivotTables.

    Figure 5


    Image
    Excel advanced load

  3. Then, right click on the file “data-api.ecb.europa.eu” and select "CSV".

    Fidure 6


    Image
    Excel csv

  4. Excel will open the Power Query Editor with a spreadsheet containing not only the data but also description for each code (metadata). 
  5. Click on Close & Load to get back to Excel default view and save the Excel file. The query configuration will be stored in the Excel file.

    Figure 7


    Image
    Excel adv close and load

     

  6. To refresh the Excel file, go to Data > Refresh All. 

For more information about refreshing external data in Excel refer to the Microsoft Excel support pages. 

 

 Back