Sign Up

Import MetalpriceAPI Data to Microsoft Excel Power Query

< Back to Guides

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

Intro

Microsoft Excel Gold price tutorial using Power Query.

Part 1: Get Your MetalpriceAPI API Key

  1. Navigate to metalpriceapi.com and click GET STARTED

    Metal Dashboard

  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 price of gold and silver in USD.

Full API Request URL:

https://api.metalpriceapi.com/v1/latest?base=USD&currencies=XAU,XAG&api_key=[YOUR_API_KEY]

Part 3: Pull MetalpriceAPI 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 currency
      • To get the price per unit, create a new column with the formula =1/[currency_column], replacing [currency_column] with the actual column name like XAU or XAG.
  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 gold and silver on 2022-01-30
https://api.metalpriceapi.com/v1/2022-01-30?api_key=[API_KEY]&base=USD&currencies=XAU,XAG
  • Conversion rate of USD$25 to gold on 2022-01-30
https://api.metalpriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=XAU&amount=100&date=2022-01-30
  • Conversion rate of USD$25 to silver on 2022-01-30
https://api.metalpriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=XAG&amount=100&date=2022-01-30
  • Conversion rate of USD$25 to EUR on 2022-01-30
https://api.metalpriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=EUR&amount=100&date=2022-01-30
  • Time-series data for gold and silver between 2021-01-01 and 2021-12-01
https://api.metalpriceapi.com/v1/timeframe?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=XAU,XAG
  • Percent change data for gold and silver between 2021-01-01 and 2021-12-01
https://api.metalpriceapi.com/v1/change?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=XAU,XAG