You can get exchange rates in Excel straight from an API with Power Query, the data tool built into Excel for Windows (Microsoft 365 and Excel 2016 or later). This guide connects Excel to the fxapi FX rates API: latest rates, a historical date, monthly average rates as CSV, conversion formulas and a refresh schedule – with your API key sent in a request header rather than pasted into a URL.
Before you start
- An fxapi key from the free sign-up.
- Excel for Windows with Data → Get Data (Power Query).
| Endpoint | Gives you | Plans |
|---|---|---|
/v1/latest | Latest rates for 190+ currencies | All |
/v1/rate | One currency pair, also as a plain number | All |
/v1/historical | End-of-day rates for one date | All |
/v1/average | Monthly, quarterly or yearly averages, CSV or JSON | All |
/v1/range | Daily (or finer) time series, CSV or JSON | Professional and up |
Step 1: create an API key parameter
In Excel: Data → Get Data → Launch Power Query Editor → Home → Manage Parameters → New Parameter.
- Name:
FxApiKey - Type: Text
- Current value: your API key
A parameter keeps the key in one place and avoids the privacy-level (“Formula.Firewall”) errors you can get when a query reads the key from a worksheet cell.
Step 2: latest exchange rates in Excel
The quickest route is the UI: Data → Get Data → From Other Sources → From Web → Advanced. Enter https://api.fxapi.com/v1/latest?base_currency=EUR as the URL, add an HTTP request header apikey with your key, and choose Anonymous when Excel asks for credentials.
For a cleaner, reusable query, open Home → Advanced Editor and paste:
let
Source = Json.Document(
Web.Contents(
"https://api.fxapi.com/v1/latest",
[
Query = [base_currency = "EUR"],
Headers = [apikey = FxApiKey]
]
)
),
UpdatedAt = Source[meta][last_updated_at],
AsTable = Record.ToTable(Source[data]),
Expanded = Table.ExpandRecordColumn(AsTable, "Value", {"code", "value"}, {"Code", "Rate"}),
Typed = Table.TransformColumnTypes(Expanded, {{"Code", type text}, {"Rate", type number}}),
Stamped = Table.AddColumn(Typed, "LastUpdatedAt", each UpdatedAt, type text)
in
Stamped
Name the query Rates and choose Close & Load. You get a table with one row per currency: Rate is the number of units of that currency per 1 EUR, and LastUpdatedAt is the UTC timestamp of the data. Keeping the URL fixed and passing parameters through Query also lets the query refresh in Power BI later.
Step 3: convert amounts with XLOOKUP
With a EUR base, every rate means “units of currency X per 1 EUR”:
EUR → USD: =[@AmountEUR] * XLOOKUP("USD", Rates[Code], Rates[Rate])
USD → EUR: =[@AmountUSD] / XLOOKUP("USD", Rates[Code], Rates[Rate])
GBP → USD: =[@AmountGBP] / XLOOKUP("GBP", Rates[Code], Rates[Rate]) * XLOOKUP("USD", Rates[Code], Rates[Rate])
The last line is a cross rate through the base currency, so one request covers every currency pair. XLOOKUP needs Excel 2021 or Microsoft 365; in Excel 2016 or 2019, use INDEX(Rates[Rate], MATCH("USD", Rates[Code], 0)) instead. The API also has a /v1/convert endpoint (Basic plan and up), but in a spreadsheet the formulas above do the same job without extra requests. Round the result with ROUND(x, 2) – or 0 for JPY and 3 for KWD; see currency rounding and precision.
Step 4: a historical exchange rate for a date
Change the endpoint and add a date (from 1999-01-01 up to yesterday):
Web.Contents(
"https://api.fxapi.com/v1/historical",
[
Query = [date = "2024-12-31", base_currency = "EUR", currencies = "USD,GBP,CHF"],
Headers = [apikey = FxApiKey]
]
)
Values are end-of-day rates (UTC). Avoid invoking a historical query once per table row: each distinct date is a separate request. For a series, use monthly averages (below) or /v1/range on the Professional plan.
Step 5: monthly average rates as CSV
For month-end reporting and IAS 21 / ASC 830 translation, /v1/average returns monthly averages – plus the min and max – for a whole period in one request. With format=csv, Power Query reads it directly:
let
Source = Csv.Document(
Web.Contents(
"https://api.fxapi.com/v1/average",
[
Query = [
base_currency = "EUR",
currencies = "USD,GBP,CHF",
date_from = "2025-10-01",
date_to = "2026-09-30",
period = "month",
format = "csv"
],
Headers = [apikey = FxApiKey]
]
),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(
Promoted,
{
{"date_from", type date}, {"date_to", type date}, {"days", Int64.Type},
{"average", type number}, {"min", type number}, {"max", type number}
},
"en-US"
)
in
Typed
The CSV columns are period, date_from, date_to, days, source, base_currency, currency, average, min, max. Daily rates are converted to your base currency first and then averaged; the first and last periods are clipped to your date span. Use period = "quarter" or "year" for other reporting periods. The "en-US" culture argument makes the decimal point parse correctly on any regional setting.
On the Professional plan, /v1/range with format=csv returns a daily series with columns datetime, base_currency, currency, value – handy for charts and pivot tables. See the time series API.
Need a one-off file rather than a query that refreshes? Every endpoint that offers format=csv also accepts format=xlsx and returns the same table as a workbook (one sheet, frozen header row, rates stored as numbers), for example from a script that saves average.xlsx for the month-end pack.
Step 6: set a refresh schedule
In Data → Queries & Connections, right-click the query → Properties:
- Refresh every N minutes – match your plan’s update interval: daily data on Free, hourly on Basic, every 60 seconds on Professional and Enterprise (pricing).
- Refresh data when opening the file – good default for reports.
- Enable background refresh – keeps Excel responsive.
Budget your quota: one query refreshing hourly for eight working hours a day uses roughly 170 requests a month; refreshing on open only is usually a few dozen. The Free plan includes 300 requests per month.
A single rate in one cell with WEBSERVICE
If you only need one or two rates and no Power Query, /v1/rate with format=text answers with nothing but the number, which Excel’s WEBSERVICE function can read:
=VALUE(WEBSERVICE("https://api.fxapi.com/v1/rate?from=EUR&to=USD&format=text&apikey=YOUR_API_KEY"))
WEBSERVICE returns text, so VALUE turns it into a number; if your Excel uses a decimal comma, write NUMBERVALUE(WEBSERVICE(...), ".") instead. Add &date=2025-12-31 to the URL for the close of a past day. Three caveats:
- The key sits in the formula.
WEBSERVICEcan’t send headers, so anyone who opens the workbook can read the key. For shared files, use the Power Query setup above. - Every recalculation is a request and counts against your quota, so keep the number of these cells small.
- Windows only. Microsoft notes that
WEBSERVICErelies on Windows features and returns no results on a Mac.
Errors you may see
| Typical symptom | HTTP status | Fix |
|---|---|---|
| Credentials prompt or “Access to the resource is forbidden” | 401 | Check the FxApiKey parameter |
| “Access to the resource is forbidden” | 403 | Endpoint not in your plan (/v1/range needs Professional) |
| “Web.Contents failed to get contents … (422)” | 422 | Check dates (≤ yesterday, ≥ 1999-01-01) and currency codes |
| “… (429): Too Many Requests” | 429 | Monthly quota used up, or more than 10 requests/minute on Free |
To see the API’s own error text, wrap the call: Web.Contents(url, [..., ManualStatusHandling = {401, 403, 422, 429}]) and read the response body with Text.FromBinary.
Next steps
- Closing vs. average exchange rates – which one your report needs.
- Using Google Sheets instead? See exchange rates in Google Sheets.
- Full parameter reference: API documentation.
Frequently asked questions
How do I get exchange rates into Excel automatically?
https://api.fxapi.com/v1/latest with your key in the apikey header, and set the query to refresh on open or every N minutes.Can I get monthly average exchange rates in Excel?
/v1/average with period=month&format=csv returns one row per currency and month, including min and max. It’s available on every plan, including Free, and a whole year costs one request.Why does Excel ask for credentials when I call the API?
apikey header defined in the query.Does the refresh schedule work when the workbook is closed?
Where should I store the API key in the workbook?
Can I get a single exchange rate into one Excel cell?
=VALUE(WEBSERVICE("https://api.fxapi.com/v1/rate?from=EUR&to=USD&format=text&apikey=YOUR_API_KEY")). The key is then visible in the formula and every recalculation counts as a request, so use Power Query for shared or larger workbooks.