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
In Excel, go to Data > From Web > Basic.
Insert the desired URL and then press on Load.
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
- Click on Load and the data will be retrieved onto a new worksheet.
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.
To refresh the Excel file, go to Data > Refresh All.
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.
- In Excel, go to Data > From Web > Advanced.
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.Then, right click on the file “data-api.ecb.europa.eu” and select "CSV".
- Excel will open the Power Query Editor with a spreadsheet containing not only the data but also description for each code (metadata).
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.
- 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.