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 fromRelativePathso scheduled refresh keeps working. - Pass your key as
Authorization: Bearer exr_live_..., orX-API-Keyif 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.
// 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 RenamedLoad 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:
// 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]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}
| Code | Symptom | Fix |
|---|---|---|
| 401 | DataSource.Error: 401 | Key missing or invalid. Re-paste it into ApiKey, then clear any stored credential under Data → Get Data → Data Source Settings for api.exchangerate.dev. |
| 429 | 429 Too Many Requests | You passed the 12-per-minute cap. Stagger refreshes across queries, or move up a tier at /pricing. |
| — | Excel keeps asking for credentials | You connected to the full URL instead of using RelativePath. Copy the split base + RelativePath query above, and choose Anonymous. |
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.
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.