Sign Up

Import EnergypriceAPI Data to Microsoft Excel Power Query

< Back to Guides

Sure, here's a full tutorial on using Power Query in Excel to get energy prices from the EnergypriceAPI:

Intro

Microsoft Excel energy prices tutorial using Power Query.

Part 1: Get Your EnergypriceAPI API Key

  1. Navigate to energypriceapi.com and click GET FREE API KEY
  2. You will be prompted to create an account and your will receive a verification email. Click on the email to verify your account, and you'll be redirected to the dashboard.
  3. Copy API Key

Part 2: Create Your API Request URL

In this example, we will get the latest prices of BRENT and WTI in USD.

Full API Request URL:

https://api.energypriceapi.com/v1/latest?base=USD&currencies=BRENT,WTI&api_key=[YOUR_API_KEY]

Part 3: Pull EnergypriceAPI Data into Microsoft Excel using Power Query

  1. Open up Microsoft Excel and click Data > Get Data > From Other Sources > From Web
  2. In the From Web dialog box, enter the API URL from step 2 and click OK.
  3. If prompted, select Anonymous access and click Connect.
  4. In the Navigator dialog box, select the record that appears (usually named "latest") and click Transform Data.
  5. This will open the Power Query Editor. Here you can perform transformations on your data:
    • To convert the Unix timestamp to a human-readable date:
      • Select the "timestamp" column
      • Click Transform > Date/Time > From Unix Time Stamp
      • Choose the appropriate time unit (usually Seconds)
    • To get the price per unit in your selected currency:
      • Select the "rates" column
      • Click Transform > Structured Column > Expand
      • Uncheck "Use original column name as prefix" and click OK
      • This will create new columns for each commodity
      • To get the price per unit, create a new column with the formula =1/[commodity_column], replacing [commodity_column] with the actual column name like BRENT or WTI.
  6. When you're done with transformations, click Close & Load in the Home tab to load the data into a new worksheet.

Part 4: Refresh Data

To refresh your data and get the latest prices:

  1. Click anywhere in your data table
  2. Click Data > Refresh All
  3. Excel will re-run the Power Query, fetching the latest data from the API

Part 5: More Example API URLs

You can experiment with endpoints and query strings as described in the documentation to see other types of data. Just replace the URL in the Power Query with one of these:

  • Historical price of Brent Crude Oil and WTI Crude Oil on 2022-01-30
https://api.energypriceapi.com/v1/2022-01-30?api_key=[API_KEY]&base=USD&currencies=BRENT,WTI
  • Conversion rate of USD$25 to BRENT on 2022-01-30
https://api.energypriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=BRENT&amount=100&date=2022-01-30
  • Conversion rate of USD$25 to WTI on 2022-01-30
https://api.energypriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=WTI&amount=100&date=2022-01-30
  • Time-series data for BRENT and WTI between 2021-01-01 and 2021-12-01
https://api.energypriceapi.com/v1/timeframe?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=BRENT,WTI
  • Percent change data for BRENT and WTI between 2021-01-01 and 2021-12-01
https://api.energypriceapi.com/v1/change?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=BRENT,WTI