Exchange rates in Excel with an API and Power Query

You can load exchange rates into Excel 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).

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. 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.

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.

Errors you may see

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 docs.

Frequently asked questions