Sign Up

Import ForexRateAPI Data to Microsoft Excel Power Query

< Back to Guides

Sure, here's a full tutorial on using Power Query in Excel to get foreign exchange rates from the ForexRateAPI:

Intro

Microsoft Excel foreign exchange rates tutorial using Power Query.

Part 1: Get Your ForexRateAPI API Key

  1. Navigate to forexrateapi.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 rates of EUR and JPY in USD.

Full API Request URL:

https://api.forexrateapi.com/v1/latest?base=USD&currencies=EUR,JPY&api_key=[YOUR_API_KEY]

Part 3: Pull ForexRateAPI 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 EUR or JPY.
  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 European Euro and Japanese Yen on 2022-01-30
https://api.forexrateapi.com/v1/2022-01-30?api_key=[API_KEY]&base=USD&currencies=EUR,JPY
  • Conversion rate of USD$25 to EUR on 2022-01-30
https://api.forexrateapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=EUR&amount=100&date=2022-01-30
  • Conversion rate of USD$25 to JPY on 2022-01-30
https://api.forexrateapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=JPY&amount=100&date=2022-01-30
  • Time-series data for EUR and JPY between 2021-01-01 and 2021-12-01
https://api.forexrateapi.com/v1/timeframe?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=EUR,JPY
  • Percent change data for EUR and JPY between 2021-01-01 and 2021-12-01
https://api.forexrateapi.com/v1/change?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=EUR,JPY