How to Get Live Exchange Rates in Excel: Power Query, WEBSERVICE and a Currency API

Every tutorial about getting live exchange rates in Excel promises the same thing and quietly delivers something else. You follow the steps, a number appears in a cell, and it looks live. It usually isn't. It is a once-a-day reference rate, or a delayed quote, or a scraped HTML table that will silently return the wrong currency the next time you refresh.

This guide covers the four methods that actually work — Excel's built-in Currencies data type, the WEBSERVICE function, Power Query, and a reusable LAMBDA — and, more importantly, tells you which one runs in your version of Excel, how fresh each one really is, and where each one produces numbers that are wrong without throwing an error.

Every formula and every query below was written against the documented Finexly API response shapes and tested for correctness before publishing.

The Four Methods, and Which One You Can Actually Use

Excel's currency features are unusually version-dependent. Half the tutorials on this topic recommend a method that does not exist on the reader's machine. Start here.

1. If you are on a Mac, Power Query's Web connector is not listed among your available sources and WEBSERVICE does not work at all. Microsoft is explicit that WEBSERVICE "may appear in the Excel for Mac function gallery, but it relies on Windows operating system features, so it will not return results on Mac." Mac users on Microsoft 365 are realistically limited to the Currencies data type.
2. If you are on perpetual Excel 2019, 2021 or 2024, the Currencies data type is not available to you — linked data types are Microsoft 365 and Excel-for-the-web only. Your route is WEBSERVICE or Power Query.

Method 1: Excel's Built-In Currencies Data Type

This is the zero-code option, and it is the one most articles lead with.

1. Type currency pairs into a column using ISO 4217 codes separated by a slash or a colon — USD/EUR, GBP:JPY. (If you are unsure which code to use, our ISO 4217 reference lists all of them.)
2. Select the cells, then go to Data → Data Types → Currencies.
3. Each cell becomes a linked record with a small currency icon. Click the icon, or use the Insert Data button, and choose Price to spill the rate into the adjacent cell.
4. Refresh with Data → Refresh All.

What Microsoft Actually Promises

This is the part the tutorials skip. Microsoft's own documentation carries a caution on the Currencies page: "Currency information is provided 'as-is' and may be delayed. Therefore, this data should not be used for trading purposes or advice." There is no published refresh interval for currency pairs. The underlying financial data comes from LSEG Data & Analytics (formerly Refinitiv), surfaced through Bing.

Three more constraints that matter if you are building anything real:

• Availability is tenant-scoped. Microsoft states that "currency pairs are only available to Microsoft 365 accounts (Worldwide Multi-Tenant clients)." If your organisation is on a sovereign or government cloud, the Currencies button will be greyed out and no amount of troubleshooting will change that.
• There is no server-side refresh. Linked data types refresh only while the workbook is open in Excel. The automatic five-minute option exists but is currently gated to the Insiders program, and Microsoft notes that "some linked data types can only be refreshed manually."
• The pair is opaque. You get a number. You cannot see the timestamp, the source, or the spread. For invoicing or reporting you need an auditable rate with a date attached — which is a very different problem.

Use this method to eyeball a rate. Do not use it as the input to anything you have to defend later.

Method 2: WEBSERVICE Plus a Currency API

WEBSERVICE(url) performs an HTTP GET and returns the response body as text. It takes exactly one argument — a URL. That single fact drives everything else about this method.

Why Your API Key Has to Go in the URL

Because WEBSERVICE has no headers parameter, you cannot send Authorization: Bearer …. Any API you use from WEBSERVICE must accept the key as a query parameter. Finexly supports both:

# Recommended everywhere else — but impossible from WEBSERVICE
curl -H "Authorization: Bearer YOUR_API_KEY" \
  "https://api.finexly.com/v1/rate?from=USD&to=EUR"

# The query-parameter form, which is what WEBSERVICE needs
https://api.finexly.com/v1/rate?from=USD&to=EUR&api_key=YOUR_API_KEY

Be honest with yourself about the trade-off. Keys in URLs get written to server access logs and can leak through HTTP referrer headers, and the key is stored in plain text inside the workbook — so anyone you email the file to has your key. Use a separate, low-quota key for spreadsheets, and never ship a WEBSERVICE workbook containing a production key.

A Working Formula

Put the URL in A1 and the raw response in A2:

A1:  ="https://api.finexly.com/v1/rate?from="&B1&"&to="&C1&"&api_key="&$D$1
A2:  =WEBSERVICE(A1)

A2 now contains the literal response text:

{"pair":"USD_EUR","rate":0.9215}

Now extract the number. Almost every competing tutorial does this positionally — INDEX(TEXTSPLIT(...), 1, 6) — which breaks silently the moment the API adds a field or you switch endpoints. Anchor on the key name instead:

=LET(
   raw,  A2,
   p,    FIND("""rate"":", raw) + 7,
   e,    MIN(IFERROR(FIND(",", raw, p), 9999), IFERROR(FIND("}", raw, p), 9999)),
   IFERROR(VALUE(MID(raw, p, e - p)), NA())
 )

This finds the literal "rate":, steps past its seven characters, and reads up to whichever comes first — a comma or a closing brace. It returns 0.9215 from the two-field /v1/rate response and from the four-field /v1/convert-amount response, and it returns #N/A rather than a wrong number if the call fails. LET requires Excel 2021 or Microsoft 365; on Excel 2016/2019 you can inline the same FIND calls at the cost of readability.

The Volatility Trap

WEBSERVICE is a volatile function. It recalculates on essentially every worksheet change, not just when you press F9. Build a 40-row converter with one WEBSERVICE per row and a single keystroke fires 40 HTTP requests.

On a free tier of 1,000 requests per month, that is roughly 25 keystrokes before you are cut off. Two defences: keep WEBSERVICE to a single cell and reference that one cell everywhere else, or switch calculation to manual (Formulas → Calculation Options → Manual) while you build. Better still, use Power Query, which is not volatile.

Method 3: Power Query — The One That Scales

Power Query is the only method here that can send an Authorization header, refresh on a schedule, and handle a fifty-thousand-row table without melting. It is also the method every tutorial demonstrates entirely through GUI clicks, so the actual M code is nowhere to be found. Here it is.

Open Data → Get Data → Launch Power Query Editor, then New Source → Blank Query, then Home → Advanced Editor, and paste:

let
    ApiKey = "YOUR_API_KEY",
    Pairs  = "USD_EUR,USD_GBP,USD_JPY,USD_CHF",
    Source = Json.Document(
        Web.Contents(
            "https://api.finexly.com",
            [
                RelativePath = "v1/convert",
                Query        = [ q = Pairs ],
                Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
            ]
        )
    ),
    ToTable  = Record.ToTable(Source),
    Expanded = Table.ExpandRecordColumn(ToTable, "Value", {"rate"}, {"rate"}),
    Split    = Table.SplitColumn(
                   Expanded, "Name",
                   Splitter.SplitTextByDelimiter("_", QuoteStyle.None),
                   {"Base", "Quote"}
               ),
    Typed    = Table.TransformColumnTypes(
                   Split,
                   {{"Base", type text}, {"Quote", type text}, {"rate", type number}}
               )
in
    Typed

Name it FxRates and load it to a worksheet. You get a clean three-column table: Base, Quote, rate.

Three details in that query are doing real work, and all three are missing from every GUI walkthrough:

• Headers keeps the key out of the URL. This is the single biggest security advantage Power Query has over WEBSERVICE.
• RelativePath and Query are split out from the base URL deliberately. If you concatenate the whole URL as one string, Power Query classifies it as a dynamic data source, which cannot be refreshed unattended in the Power BI service. Passing the path and query separately keeps the source static and refreshable.
• One request, many pairs. /v1/convert returns every pair you ask for in a single call. A workbook refreshing hourly during an eight-hour day across 22 business days makes 176 requests a month this way. Thirty separate /v1/rate calls on the same schedule would make 5,280 — five times the free monthly allowance, for identical data.

A Reusable Rate Function

For a single pair on demand, wrap it in an M function:

let
    FxRate = (baseCode as text, quoteCode as text) as number =>
        let
            ApiKey = "YOUR_API_KEY",
            Source = Json.Document(
                Web.Contents(
                    "https://api.finexly.com",
                    [
                        RelativePath = "v1/rate",
                        Query        = [ from = baseCode, to = quoteCode ],
                        Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
                    ]
                )
            )
        in
            Source[rate]
in
    FxRate

Call it from any other query as FxRate("USD", "EUR").

Converting 50,000 Rows Without 50,000 Lookups

Dragging XLOOKUP down a large table is slow and fragile — and if the query output resizes on refresh, full-column references like F:F will quietly misalign. Do the join inside Power Query instead:

1. Load your transactions table into Power Query.
2. Home → Merge Queries, matching your Currency column to FxRates[Quote].
3. Expand the merged column and keep rate.
4. Add Column → Custom Column with the conversion.

That is one join over the whole table, evaluated once per refresh, with no volatile formulas anywhere in the workbook.

Method 4: A Reusable =FXRATE() LAMBDA

If the people using the workbook are spreadsheet users rather than query authors, hide all of the above behind one function. Go to Formulas → Name Manager → New, name it FXRATE, and set Refers to:

=LAMBDA(from_code, to_code, key,
   LET(
     url,  "https://api.finexly.com/v1/rate?from=" & from_code &
           "&to=" & to_code & "&api_key=" & key,
     raw,  WEBSERVICE(url),
     p,    FIND("""rate"":", raw) + 7,
     e,    MIN(IFERROR(FIND(",", raw, p), 9999), IFERROR(FIND("}", raw, p), 9999)),
     IFERROR(VALUE(MID(raw, p, e - p)), NA())
   )
 )

Now anyone can write =FXRATE("USD","EUR",$D$1) and get 0.9215. Store the key once in D1, protect that cell, and the rest of the workbook never touches it. Note that this inherits every WEBSERVICE limitation — Windows-only, volatile, key in the URL — so it is a usability win, not a security or scaling one.

Five Ways Excel Currency Conversions Go Silently Wrong

These are correctness bugs, not error messages. Nothing turns red. The numbers are simply not what you think they are.

1. Multiplying When You Should Divide

This is the most common error in published Excel currency tutorials, and it is worth being precise about the damage. A USD_EUR rate of 0.9215 converts USD into EUR. To go the other way — EUR into USD — you divide.

• Correct: 100 / 0.9215 = 108.52 USD
• Wrong: 100 × 0.9215 = 92.15 USD

The result is off by the square of the rate, 0.9215² = 0.8492 — a 15.1% understatement that looks entirely plausible on a report. Write the direction into your column headers (USD→EUR rate, not Rate) and the mistake becomes hard to make.

2. Parsing JSON by Position

INDEX(TEXTSPLIT(response, ":", "}"), 1, 6) works right up until the API adds a field, at which point index 6 returns a different value with no error. Anchor on the key name, as in the LET formula above, or use Power Query's Json.Document, which parses properly.

3. Rounding Everything to Two Decimals

Excel defaults to two decimal places; ISO 4217 does not. JPY, KRW and VND have zero minor units — ¥250.75 is not a valid amount. BHD, KWD, OMR and TND have three. Rounding a JPY total to two decimals then re-rounding downstream is how reconciliations end up off by a few units. We cover the full set of rules in our guide to currency rounding and decimal places.

4. Treating a Mid-Market Rate as the Rate You'll Be Charged

Every rate in this article — and in every free API — is the mid-market rate: the midpoint between bid and ask. It is not what your bank, card network or payment processor gives you. Real spreads run from roughly 0.5% to several percent. If you are pricing goods or quoting a customer, add an explicit markup column rather than pretending the mid-market rate is the settlement rate. Our explainer on where exchange rate APIs get their data covers what these feeds do and don't represent.

5. Letting a Live Workbook Rewrite Last Month's Numbers

This one is severe and almost never mentioned. If your rate cells refresh live, then every historical row in the workbook is revalued at today's rate every time someone opens the file. March's revenue changes in April. Your reconciliation will never tie twice.

Once a transaction has occurred, its rate is a fact about that date, not a live value. Freeze it: convert once, paste the result as a value alongside the rate and the date used, and keep the live query for new rows only. This is the same discipline that multi-currency invoicing and expense management systems enforce at the database level.

Bonus: Cross Rates

If your API returns everything against a single base, converting EUR→GBP means dividing one rate by the other:

EUR→GBP  =  USD_GBP / USD_EUR  =  0.7892 / 0.9215  =  0.856430
1,000 EUR  =  856.43 GBP

Round only at the final step. Rounding the intermediate cross rate to four decimals and then multiplying introduces error that compounds across a large table. There is more detail in our guide to cross exchange rates.

Auto-Refresh, Rate Limits and Staying on the Free Tier

To make a Power Query rate table update on its own: right-click the query in Queries & Connections → Properties, then tick Refresh data when opening the file and/or Refresh every N minutes.

Two things nobody warns you about:

• Refresh on open triggers Excel's external data security prompt. Users will see a yellow bar and, until they click Enable, the "automatic" refresh does nothing. Adding the folder to Trust Center → Trusted Locations avoids it.
• Refreshing every 15 minutes against a daily-published rate is pure waste. Match your interval to how often your source actually changes. For most invoicing and reporting work, once on open is plenty.

Watch your consumption using the response headers Finexly returns — X-RateLimit-Limit, X-RateLimit-Used and X-RateLimit-Units. The free plan allows 1,000 requests per month with no credit card, which is comfortable for a refresh-on-open workbook and tight for a volatile WEBSERVICE grid. If you are consistently near the ceiling, the fix is almost always batching into /v1/convert rather than moving to a paid plan. Our caching and error-handling guide covers the same principles outside Excel.

Troubleshooting

Frequently Asked Questions

Can Excel get real-time exchange rates? Not truly real-time. The Currencies data type is explicitly documented as delayed with no published interval, and most free APIs publish once per business day. What you can get is current — a rate refreshed on demand from a live source, which is what a currency API gives you. For genuine tick-level FX you need a streaming feed, not a spreadsheet.

How do I get exchange rates in Excel without an API key? The Currencies data type needs no key, but it requires Microsoft 365 and gives you no timestamp or audit trail. For anything you have to reconcile later, a keyed API is the better answer — and a free currency API tier costs nothing.

Why doesn't WEBSERVICE work in Excel for Mac? Microsoft's documentation states that WEBSERVICE relies on Windows operating system features, so it returns no result on Mac even though it appears in the function gallery. The same applies to FILTERXML. Mac users should use Power Query where possible or the Currencies data type.

How do I get historical exchange rates in Excel for a specific date? Build a rate table with one row per date, populated from a historical rates source, then XLOOKUP against it with match mode -1 so weekends and holidays fall back to the last available business day. Never revalue past transactions at today's rate.

Can I use Power Query to convert an entire transaction table at once? Yes, and you should. Merge your transactions query against your rates query on the currency code, expand the rate column, and add a custom column for the converted amount. One join replaces tens of thousands of volatile formulas and refreshes in a single pass.

Which method should I pick? One pair, occasional check: Currencies data type. A prototype on Windows: WEBSERVICE. Anything that other people depend on: Power Query, every time.

Ready to put current rates into your spreadsheet properly? Get your free Finexly API key — no credit card required. You get 1,000 requests per month across 170+ currencies, the api_key query parameter that makes WEBSERVICE possible, and header-based auth for Power Query. See the API documentation for the full endpoint reference, or compare currency APIs if you are still evaluating options.