Power BI and Excel: Currency APIs Compared for Exchange Rate Data
Data analysts and financial modelers live in Power BI and Excel. Whether you are building a multi-currency revenue dashboard, a treasury report, or a procurement cost tracker, you need exchange rate data that flows cleanly into your existing workflows — without requiring Python scripts, custom connectors, or a data engineering team.
The challenge: most currency APIs were designed for software developers, not data analysts. They return nested JSON that breaks Power Query, require OAuth flows that Excel cannot handle, or lock historical data behind pricing tiers that do not fit a department budget, or cap free plans so tightly that a scheduled refresh burns through them in a week.
This article compares the 5 most relevant currency APIs for Power BI and Excel users in 2026. We show working Power Query M code for each so you can see exactly what the connection looks like. Vendor facts below were re-checked on 24 October 2026. We build AllRatesToday, so read that section with that in mind; the short version is that it fits when you need 60-second refresh or official central-bank rates, and a free alternative is often enough when you do not.
Side-by-Side Comparison
Here is how the top 5 currency APIs compare for Power BI and Excel use cases:
| API | Power BI Compatible | Excel Add-in | Free Tier | Historical Data | Update Speed |
|---|---|---|---|---|---|
| AllRatesToday | Yes (native Web connector) | WEBSERVICE formula | Free, no CC (non-commercial) | Paid plans (from €4.99/mo) | Every 60s |
| Open Exchange Rates | Yes | No | 1,000 req/mo (USD base) | Paid only ($12/mo) | Hourly |
| Fixer.io | Yes (HTTPS) | No | 100 req/mo (non-commercial) | Yes, on free tier | Hourly |
| ExchangeRate-API | Yes | No | 1,500 req/mo | Paid only (Pro $10/mo) | Daily |
| Frankfurter | Yes | No | Keyless, no caps | Yes (ECB, back to 1999) | Daily (16:00 CET) |
Key takeaway: All five connect to Power BI through the standard Web connector; none ships a native Power BI connector. The real differences are refresh frequency, where historical data sits, and the free-tier caps. If daily ECB rates are enough, Frankfurter costs nothing. AllRatesToday stands out on this list for 60-second updates, official central-bank rates, and CSV/XLSX output, but its history is on paid plans from €4.99/mo.
1. AllRatesToday — 60-Second Rates and Central-Bank Data
AllRatesToday returns flat JSON that Power Query parses without much transformation. The API uses Bearer token authentication, which both Power BI Desktop and Excel's Power Query support natively. Table endpoints can also return format=csv, xml or xlsx, which is handy when you would rather use Csv.Document or Excel.Workbook than parse JSON. Historical data is included on every paid plan (from €4.99/mo); the free tier covers latest rates for testing and non-commercial use. There is no native Power BI connector: everything below is Web.Contents in Power Query. Alongside mid-market rates it also serves official rates published by 100+ central banks and tax authorities, which matters for statutory or month-end reporting.
Power Query M — Get Latest Rates
Open Power BI Desktop, click Get Data > Web, choose Advanced, and paste the following Power Query M code into a blank query:
let
ApiKey = "YOUR_API_KEY",
Source = Json.Document(
Web.Contents(
"https://allratestoday.com/api/v1/rates",
[
Query = [source = "USD"],
Headers = [Authorization = "Bearer " & ApiKey]
]
)
),
Rates = Record.ToTable(Source[rates]),
RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}}),
TypedColumns = Table.TransformColumnTypes(RenamedColumns, {{"Currency", type text}, {"Rate", type number}})
in
TypedColumns This returns a clean two-column table (Currency, Rate) that you can immediately use in relationships, measures, and visuals.
Power Query M — Get Historical Rates for a Date Range
let
ApiKey = "YOUR_API_KEY",
Source = Json.Document(
Web.Contents(
"https://allratestoday.com/api/v1/rates",
[
Query = [
source = "USD",
target = "EUR",
from = "2026-01-01",
to = "2026-05-24"
],
Headers = [Authorization = "Bearer " & ApiKey]
]
)
),
RatesList = Source[rates],
ToTable = Table.FromList(RatesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumns = Table.ExpandRecordColumn(ToTable, "Column1", {"time", "rate"}, {"Date", "Rate"}),
TypedColumns = Table.TransformColumnTypes(ExpandedColumns, {{"Date", type date}, {"Rate", type number}})
in
TypedColumns This query pulls daily USD/EUR rates for an entire date range into a table you can plot as a line chart. Combine it with your revenue data to calculate localized totals over time.
Power Query M — Convert an Amount
let
ApiKey = "YOUR_API_KEY",
Amount = 50000,
Source = Json.Document(
Web.Contents(
"https://allratestoday.com/api/v1/rates",
[
Query = [
source = "USD",
target = "GBP",
amount = Number.ToText(Amount)
],
Headers = [Authorization = "Bearer " & ApiKey]
]
)
),
Result = Source[to][amount],
RateUsed = Source[rate]
in
[ConvertedAmount = Result, ExchangeRate = RateUsed, OriginalAmount = Amount] Excel — WEBSERVICE Formula
If you are working in Excel (Microsoft 365 or Excel 2019+), you can pull exchange rates directly into cells without Power Query:
=VALUE(MID(
WEBSERVICE("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&api_key=YOUR_API_KEY"),
FIND("""rate"":",
WEBSERVICE("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&api_key=YOUR_API_KEY")
) + 7,
10
)) For a cleaner approach, use Excel's built-in Data > From Web feature which leverages Power Query under the hood and returns structured data.
Excel — Power Query (Get & Transform)
In Excel, go to Data > Get Data > From Other Sources > From Web, then enter:
https://allratestoday.com/api/v1/rates?source=USD&api_key=YOUR_API_KEY Excel will open the Power Query Editor. Click Into Table, expand the rates record, and you will get a two-column table of currency codes and exchange rates that refreshes on demand or on a schedule.
Pro tip: In Power BI Service, you can schedule automatic refreshes up to 8 times per day. Combined with AllRatesToday's 60-second update frequency, your dashboards will always show near-real-time exchange rates without manual intervention.
- Data source: institutional foreign exchange market data
- Update frequency: Every 60 seconds (real-time)
- Currencies: 160+ including majors, minors, and exotics
- Rate type: Mid-market (no bank markup)
- Free tier: no credit card; personal and non-commercial use
- Historical data: Paid plans from €4.99/mo
- Output formats: JSON, CSV, XML, XLSX on table endpoints
- Official rates: central-bank and tax-authority rates for 100+ institutions
- Authentication: Bearer token or query parameter
2. Open Exchange Rates
Open Exchange Rates works with Power BI's Web connector but has two limitations that frustrate data analysts. First, the free tier locks you to USD as the base currency, which means you need to calculate cross-rates manually in DAX if your reporting currency is EUR or GBP. Second, historical data requires a paid plan starting at $12/month.
Power Query M
let
AppId = "YOUR_APP_ID",
Source = Json.Document(
Web.Contents(
"https://openexchangerates.org/api/latest.json",
[Query = [app_id = AppId, base = "USD"]]
)
),
Rates = Record.ToTable(Source[rates]),
RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}})
in
RenamedColumns - Free tier: 1,000 requests/month, USD base only
- Update frequency: Hourly on free, more frequent on paid
- Historical data: Paid plans only ($12+/month)
- Limitation: Free plan locked to USD base currency; cross-rate calculation required for other base currencies
3. Fixer.io
Fixer's free plan (checked October 2026) supports HTTPS, updates hourly and includes the historical endpoint, so it works with Power BI's Web connector out of the box. The constraints are the 100 requests per month cap, which a scheduled refresh can exhaust quickly, and the non-commercial licence on the free tier. Paid plans start at $14.99/month. Its sibling currencylayer has the same $14.99 entry price with HTTPS and history on the free tier, but daily rather than hourly updates.
Power Query M
let
ApiKey = "YOUR_API_KEY",
Source = Json.Document(
Web.Contents(
"https://data.fixer.io/api/latest",
[Query = [access_key = ApiKey, base = "EUR", symbols = "USD,GBP,JPY"]]
)
),
Rates = Record.ToTable(Source[rates])
in
Rates - Free tier: 100 requests/month, HTTPS supported, non-commercial use
- Update frequency: Hourly
- Historical data: Included on the free tier
- Paid plans: from $14.99/month
- Limitation: 100 requests/month is tight for scheduled refreshes; free tier is non-commercial
4. ExchangeRate-API
ExchangeRate-API offers a straightforward REST endpoint that works with Power BI. The JSON response is clean and easy to parse. However, rates update only once per day, and historical data requires a paid plan.
Power Query M
let
ApiKey = "YOUR_API_KEY",
Source = Json.Document(
Web.Contents(
"https://v6.exchangerate-api.com/v6/" & ApiKey & "/latest/USD"
)
),
Rates = Record.ToTable(Source[conversion_rates]),
RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}})
in
RenamedColumns - Free tier: 1,500 requests/month
- Update frequency: Daily
- Historical data: Paid plans only (Pro from $10/month)
- Limitation: No intraday rates, no historical data on free tier
5. Frankfurter
Frankfurter is open source and free with no API key required. This makes it the easiest API to set up in Power BI — just paste a URL and go. The catch: it only provides European Central Bank reference rates, which cover about 30 currencies, update once per business day, and do not include weekends or holidays.
Power Query M
let
Source = Json.Document(
Web.Contents(
"https://api.frankfurter.app/latest",
[Query = [from = "USD", to = "EUR,GBP,JPY,CAD,AUD"]]
)
),
Rates = Record.ToTable(Source[rates]),
RenamedColumns = Table.RenameColumns(Rates, {{"Name", "Currency"}, {"Value", "Rate"}})
in
RenamedColumns - Free tier: Keyless, no published caps
- Update frequency: Daily at 16:00 CET (ECB schedule)
- Currencies: ~30 ECB currencies
- Historical data: Yes, ECB data back to 1999
- Limitation: No exotic currencies, no intraday data, no weekend or holiday updates
Building a Multi-Currency Dashboard in Power BI
Here is a practical example of how to build a revenue dashboard that converts sales from multiple currencies into a single reporting currency using AllRatesToday.
Step 1: Create the Exchange Rate Query
In Power BI Desktop, open the Power Query Editor and create a new blank query with the AllRatesToday M code shown above. Name this query ExchangeRates.
Step 2: Create a DAX Measure for Converted Revenue
Converted Revenue (USD) =
VAR CurrentCurrency = SELECTEDVALUE(Sales[Currency])
VAR ExchangeRate =
LOOKUPVALUE(
ExchangeRates[Rate],
ExchangeRates[Currency],
CurrentCurrency
)
RETURN
IF(
CurrentCurrency = "USD",
SUM(Sales[Amount]),
DIVIDE(SUM(Sales[Amount]), ExchangeRate, 0)
) Step 3: Schedule Automatic Refresh
After publishing to Power BI Service, configure a scheduled refresh under Dataset Settings > Scheduled Refresh. Set it to refresh every 3 hours during business hours to keep your exchange rates current throughout the trading day.
Important: When using Power BI Service scheduled refresh with API calls, you need to configure the data source credentials. Go to Settings > Data source credentials > Edit credentials and set the authentication type to Anonymous (the API key is passed in the query header).
Excel Multi-Currency Pricing Sheet
Data analysts often need a simple spreadsheet that pulls live rates and converts a list of prices. Here is how to set this up in Excel using Power Query.
Step 1: Load Exchange Rates via Power Query
Go to Data > Get Data > From Web and paste your AllRatesToday API URL. Load the resulting table to a worksheet named Rates.
Step 2: Use XLOOKUP for Conversion
=B2 * XLOOKUP(C2, Rates[Currency], Rates[Rate], 0) Where B2 is your amount, C2 is the target currency code, and the Rates table contains the exchange rates loaded from AllRatesToday.
Step 3: Refresh on Open
Right-click the Rates query in the Queries & Connections pane, select Properties, and check Refresh data when opening the file. Every time you open the workbook, Excel will pull the latest exchange rates automatically.
Where AllRatesToday Fits
Having connected all five to Power BI Desktop and Excel, these are the cases where AllRatesToday is worth choosing over the free options:
- Clean JSON for Power Query: The API returns flat, predictable JSON that Power Query converts to a table in one step. No nested records to expand, no arrays to transpose, no XML to parse.
- Intraday rates: Updated every 60 seconds. Open Exchange Rates and Fixer update hourly on free plans; ExchangeRate-API and Frankfurter are daily, and Frankfurter skips weekends.
- Affordable historical data: Build time-series trend charts in Power BI from the €4.99/mo Small plan, with any base currency. Open Exchange Rates, Fixer, and ExchangeRate-API also put historical data on paid plans.
- Spreadsheet-native output:
format=csvorformat=xlsxon table endpoints feeds Power Query without JSON parsing. - Official central-bank rates: ECB, Fed, RBI and 100+ other institutions from the same key, for reports that must cite a published rate rather than a market mid.
- 160+ currencies: Covers majors, minors, and exotic pairs; Frankfurter's ~30 ECB currencies may not cover a global reporting set.
- Mid-market rates: The mid-market rate with no markup, which is what finance teams need for accurate FX reporting and reconciliation.
- No credit card required: Sign up, get your API key, and paste it into Power Query in under two minutes.
- Simple authentication: Bearer token or query parameter — both work with Power BI's Web connector without custom connector development.
Which One Should You Pick?
- Frankfurter if a free dashboard on ECB daily reference rates is all you need. Keyless, no caps, history to 1999.
- ExchangeRate-API if daily updates are fine and 1,500 calls a month cover your refresh schedule, with Pro at $10/mo when you need history.
- Fixer or currencylayer if you want free history for a personal, low-volume workbook and can live with 100 requests a month.
- Open Exchange Rates if USD-base hourly rates and 1,000 calls a month suit you, with history from $12/mo.
- AllRatesToday if you need 60-second refresh, official central-bank rates in finance reporting, or CSV/XLSX output straight into Power Query, and a plan from €4.99/mo for history is acceptable.
Quick Reference
- Latest rates:
GET /api/v1/rates?source=USD - Single pair:
GET /api/v1/rates?source=USD&target=EUR - Historical:
GET /api/v1/rates?source=USD&target=EUR&from=2026-01-01&to=2026-05-24(paid plans) - Convert:
GET /api/v1/rates?source=USD&target=GBP&amount=50000 - Auth (header):
Authorization: Bearer YOUR_API_KEY - Auth (query):
?api_key=YOUR_API_KEY
Power Your Dashboards with Real-Time Exchange Rates
Get your free API key in 30 seconds and test the connection in Power Query. Connect Power BI or Excel to 160+ mid-market exchange rates and 100+ central-bank feeds — no credit card. Compare all options on our Exchange Rate API page.
Get Your Free API Key