Best Currency API for Power BI and Excel (2026)

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 enterprise pricing tiers that blow your department budget. Some do not even support HTTPS on their free plan, which means Power BI's Web connector refuses to connect at all.

This article compares the 5 most relevant currency APIs for Power BI and Excel users in 2026. We test each one with real Power Query M code and Excel formulas so you can see exactly what works. Spoiler: AllRatesToday wins for data analysts because it returns flat, clean JSON that Power Query parses natively, supports historical time-series on the free tier, and updates every 60 seconds.

Side-by-Side Comparison

Here is how the top 5 currency APIs compare for Power BI and Excel use cases:

1. AllRatesToday — Best Overall for Power BI and Excel

AllRatesToday returns clean, flat JSON that Power Query parses without any transformation gymnastics. The API uses simple Bearer token authentication, which both Power BI Desktop and Excel's Power Query support natively. Historical data is available on the free tier, so you can build time-series dashboards without upgrading to a paid plan.

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/historical-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/convert",
            [
                Query = [
                    source = "USD",
                    target = "GBP",
                    amount = Number.ToText(Amount)
                ],
                Headers = [Authorization = "Bearer " & ApiKey]
            ]
        )
    ),
    Result = Source[result],
    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&apikey=YOUR_API_KEY"),
    FIND("""rate"":",
        WEBSERVICE("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&apikey=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&apikey=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.

• Data source: Reuters/Refinitiv and interbank feeds
• Update frequency: Every 60 seconds (real-time)
• Currencies: 160+ including majors, minors, and exotics
• Rate type: Mid-market (no bank markup)
• Free tier: Available — no credit card required
• 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 has a critical problem for Power BI users: the free tier serves data over HTTP, not HTTPS. Power BI's Web connector and Excel's WEBSERVICE function both require HTTPS by default, which means Fixer's free plan does not work with Power BI or Excel out of the box. You need the paid plan ($10/month minimum) just to get HTTPS access.

Power Query M (Paid Plan Only)

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, EUR base only, HTTP only
• Update frequency: Daily
• Power BI compatible: Only on paid plans (HTTPS required)
• Historical data: Paid plans only
• Limitation: Free tier uses HTTP — incompatible with Power BI and Excel WEBSERVICE

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
• Limitation: No real-time 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: Unlimited (no API key required)
• Update frequency: Daily at 16:00 CET (ECB schedule)
• Currencies: ~30 (ECB reference rates only)
• Historical data: Yes, but ECB data only (back to 1999)
• Limitation: No exotic currencies, no real-time 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.

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.

Why AllRatesToday Wins for Data Analysts

After testing all five APIs with Power BI Desktop and Excel, here is why AllRatesToday is the best choice for data analysts:

• 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.
• Real-time rates: Updated every 60 seconds from Reuters/Refinitiv and interbank feeds. Every other free API on this list provides daily rates at best — Frankfurter even skips weekends.
• Historical data on free tier: Build time-series trend charts in Power BI without paying for a premium plan. Open Exchange Rates, Fixer, and ExchangeRate-API all lock historical data behind paywalls.
• HTTPS on free tier: Unlike Fixer, AllRatesToday serves all data over HTTPS, which means Power BI and Excel connect without any workarounds.
• 160+ currencies: Covers majors, minors, and exotic pairs. Frankfurter's 30 ECB currencies are not enough for global financial reporting.
• Mid-market rates: The true interbank 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.

Quick Reference

• Latest rates: GET /api/v1/rates?source=USD
• Single pair: GET /api/v1/rates?source=USD&target=EUR
• Historical: GET /api/v1/historical-rates?source=USD&target=EUR&from=2026-01-01&to=2026-05-24
• Convert: GET /api/v1/convert?source=USD&target=GBP&amount=50000
• Auth (header): Authorization: Bearer YOUR_API_KEY
• Auth (query): ?apikey=YOUR_API_KEY