LearnTutorial

How to get live exchange rates in Excel with Power Query

To convert EUR to USD in Excel, use =B2/XLOOKUP("EUR",FxRates[currency],FxRates[rate]) when B2 holds euros and FxRates uses USD as its base. The table's rate is currency units per US dollar, so you divide the euro amount by the EUR rate. XLOOKUP needs Excel 2021 or Microsoft 365.

1 Oct 2026 · 5 min read

If you need exchange rates inside a workbook, Power Query is the practical place to start. The query below calls /v1/latest/USD, turns the JSON rates into a two-column table, and refreshes when the workbook opens or you click Refresh All. For cell-by-cell formulas and worked conversion examples, see the Excel currency conversion guide.

  • Power Query M is built into Excel on Mac and Windows, no add-in, and no WEBSERVICE(), which is Windows-only and breaks on Mac.
  • One call to /v1/latest/{base} returns every rate; split the base URL from RelativePath so scheduled refresh keeps working.
  • Pass your key as Authorization: Bearer exr_live_..., or X-API-Key if a connector cannot set the Authorization header.
  • The free tier is 5,000 calls a month at 10 requests a minute, with no card required.
  • Rates are indicative, built for models, analytics, and display, not for settling a trade.

Get a free API key

Sign up at exchangerate.dev/signup. After email confirmation your dashboard shows a key starting with exr_live_. Copy it; you will paste it into the query as a parameter. The free tier is 5,000 calls a month, no card required. For a complete workbook with historical rate snapshots, follow the multi-currency invoice tracker tutorial.

Open the Power Query editor

From the Excel ribbon, click Data → Get Data → Launch Power Query Editor. Inside the editor, click Home → New Source → Blank Query, then View → Advanced Editor. You will paste the M query into that box in the next step.

Paste the M query

Replace the editor contents with the query below and click Done, then rename the query FxRates in the right rail. The RelativePath argument is not optional, without it, scheduled refresh in Microsoft 365 fails with a credentials error. Microsoft documents RelativePath as the Web.Contents option for combining a fixed base URL with the request path.

Power Query · Advanced Editor
// excel: Power Query M query "FxRates"// curl-equivalent: https://api.exchangerate.dev/v1/latest/USD// Private workbook only: do not share or export this query with a key.// Replace ApiKey with your exchangerate.dev key from /signuplet    ApiKey = "exr_live_replace_with_your_key_here",    Base = "USD",    Source = Json.Document(        Web.Contents(            "https://api.exchangerate.dev",            [                RelativePath = "v1/latest/" & Base,                Timeout = #duration(0, 0, 0, 10),                Headers = [#"Authorization" = "Bearer " & ApiKey]            ]        )    ),    Rates = Record.ToTable(Source[rates]),    Renamed = Table.RenameColumns(Rates, {{"Name", "currency"}, {"Value", "rate"}})in    Renamed

Load to a worksheet, choose Anonymous

Click Home → Close & Load. Excel drops a two-column table, currency and rate, onto a new sheet. On first run Excel asks how to authenticate the https://api.exchangerate.dev source: choose Anonymous. Your key travels in the header, not the URL, so Anonymous is correct. Press Ctrl+Alt+F5 any time to refresh.

Switch the base or filter to a few pairs

Change the Base to re-base every rate, or add a symbols query to return only the pairs you need, fewer rows, same single call:

Power Query · variants
// Re-base every rate to the euroBase = "EUR"// Return only the quote currencies you need (one call, fewer rows)RelativePath = "v1/latest/USD",Query = [symbols = "EUR,GBP,JPY"]// One pair, rate calculated for youRelativePath = "v1/convert/USD/JPY/1"   // read Source[rate]
Building this with an AI assistant?

exchangerate.dev ships an MCP server and an llms.txt, so Claude, Cursor, or ChatGPT can read the live schema and write this query for you. Ask it: "write an Excel Power Query M query that pulls USD rates from exchangerate.dev with my key in the Authorization header, using RelativePath so refresh works." See the MCP guide.

Refresh and free-tier fit

Excel does not poll on its own. The table refreshes when you open the workbook, click Refresh All, or set a periodic refresh under Data → Queries & Connections → Properties. The math stays well inside the free tier:

  • 5,000 calls a month, 10 requests a minute, no card
  • A typical model fires a handful of calls a day (open + a few manual refreshes)
  • Even a 5-minute auto-refresh during market hours lands near 100 calls a day
  • Covers /v1/latest, /v1/convert, /v1/range, and /v1/{date}/{base}
CodeSymptomFix
401DataSource.Error: 401Key missing or invalid. Re-paste it into ApiKey, then clear any stored credential under Data → Get Data → Data Source Settings for api.exchangerate.dev.
429429 Too Many RequestsYou passed the 12-per-minute cap. Stagger refreshes across queries, or move up a tier at /pricing.
—Excel keeps asking for credentialsYou connected to the full URL instead of using RelativePath. Copy the split base + RelativePath query above, and choose Anonymous.
Indicative rates, not for settlement

These rates are published for models, analytics, and display. They are not a dealing quote, do not use them to settle a trade or transfer. Every response says so in its notice field.

Test the endpoint before you build the query

You can call /v1/latest/USD without a key and see the same JSON shape the M query parses. When it looks right, a free key gives the workbook its own 5,000-calls-a-month quota; no card.

Questions

Can I get Claude, Cursor, or ChatGPT to build this for me?

Yes. exchangerate.dev publishes an MCP server and an llms.txt file, so an AI assistant can read the live schema and the endpoint shapes. Ask it to write an Excel Power Query M query that pulls rates from exchangerate.dev with your key in the Authorization header, using RelativePath so refresh works. Because it reads the real schema, it uses the correct field names on the first try.

Is this good for a financial model in Excel?

Yes, this is the main use case. A Power Query connection gives a DCF, a hedging sheet, or a multi-currency P&L a live FX input that refreshes when you open the workbook, instead of a hardcoded rate that goes stale. Point a cell at the loaded rate table and your model recalculates from current data.

Does this work on Mac?

Yes. Power Query M is the path on both platforms. The legacy WEBSERVICE() function is Windows-only and broken on Mac, so this tutorial avoids it. The M query above runs identically on macOS Excel and Windows Excel.

Can I use these rates in a commercial product?

Free allows commercial use: show a Rates by Exchangerate.dev link wherever rates appear. Paid plans need no link. No plan allows redistribution via API, SDK, data feed or file export. See exchangerate.dev/pricing.

How do I pull just one currency pair?

Add a symbols filter to the latest endpoint, for example /v1/latest/USD?symbols=JPY, then read rates.JPY. Or call /v1/convert/USD/JPY/1, which returns the rate plus the converted amount in one response.

Keep reading

Start with a free API key

5,000 requests a month on the free plan. No credit card.

Get a free key