How to Integrate with Excel

Microsoft Excel allows the ingestion of data from external resources, displaying it into a tab by using its Power Query add-on.

Depending on your Excel version, Power Query may need an additional download:

  • Excel 2010/2013 for Windows: Power Query is available as a free add-in that can be downloaded from here
  • Microsoft 365 for Windows or Excel 2016/2019: Power Query is bundled with MS Excel.
  • Excel for Microsoft 365 for Mac: Unfortunately, Power Query only allows to sync data with local CSV or Text files. Read more about it here. A solution could consist of using a Cloud service that synchronizes the CSV into a local folder and passes the file reference to Excel as explained in the link above.

  1. Create a new Excel sheet. If you are using Excel 365/2016/2019, click on the Data tab and select From Web, then Advanced. excel-1.png If you are using Power Query as an extension in Excel 2010/2013, click on the Power Query tab and select From Web. excel-2.png

  2. Fill the advanced form with the following data (get your API Key from https://app.climatiq.io/):

  • URL parts: The REST API endpoint URL. You can get the latest URL version from here.
  • Add a new HTTP header request: Authorization - Bearer [YOUR_API_KEY]
  1. Click OK. Excel will try to authenticate with the Climatiq API and, once authenticated, will show a list of emission factor records with no formatting or columns defined yet.
  2. Click on To Table to convert the data into a table and leave the options with the default values. Click OK.

excel-2.png

  1. Now Excel needs to parse the columns for every row, so click on the small icon on top of the column and it will display a set of columns to parse. Select those that you are interested in, or leave it as it is and click OK.

excel-3.png

  1. Finally, click on Close and Load and Power Query will import the data into the sheet.

excel-5.png

If you need to refresh the data at some point and bring the latest, click on Refresh at any point. Keep in mind that any custom data will be lost.