--- title: "Best Currency API for Power BI and Excel (2026)" description: "Compare the 5 best currency exchange rate APIs for Power BI and Excel in 2026. Side-by-side comparison of Power Query M code, Excel formulas, free tiers, historical data, and update speeds." canonical: https://allratestoday.com/blog/best-currency-api-for-power-bi-2026/ source: AllRatesToday (https://allratestoday.com/) — Markdown mirror of the page above; index of all pages at https://allratestoday.com/llms.txt generated: 2026-10-07T11:43:32.338Z --- ![Data analytics dashboard on screen](https://images.pexels.com/photos/97080/pexels-photo-97080.jpeg?auto=compress%3C/header%3Ecs=tinysrgb%3C/header%3Eh=650%3C/header%3Ew=940) 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: | API | Power BI Compatible | Excel Add-in | Free Tier | Historical Data | Update Speed | | --- | --- | --- | --- | --- | --- | | AllRatesToday | Yes (native Web connector) | WEBSERVICE formula | Free, no CC | Yes (free tier) | Every 60s | | Exchange Rate API | Yes (native Web connector) | WEBSERVICE formula | 300 req/mo, no CC | Yes | Every 60s | | Open Exchange Rates | Yes | No | 1,000 req/mo | Paid only | Hourly | | Fixer.io | HTTP only (free) | No | 100 req/mo | Paid only | Daily | | ExchangeRate-API | Yes | No | 1,500 req/mo | Paid only | Daily | | Frankfurter | Yes | No | Unlimited | Yes (ECB only) | Daily (16:00 CET) | **Key takeaway:** AllRatesToday is the only API on this list that combines Power BI compatibility, real-time 60-second updates, historical data on the free tier, and no credit card requirement. For data analysts who need more than daily ECB rates, it is the clear choice. ## 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. **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 interbank 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:** 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 on v1; 165+ on the 2026 v2 (official sources) - **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. **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. ## 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 institutional interbank market data. 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` ## Power Your Dashboards with Real-Time Exchange Rates Get your free API key in 30 seconds. Connect Power BI or Excel to 160+ real-time mid-market exchange rates — no credit card, no enterprise sales call. Compare all options on our [Exchange Rate API](https://exchange-rateapi.com/) page. [Get Your Free API Key](https://allratestoday.com/register) ## Related Articles - [Excel Exchange Rates](https://allratestoday.com/blog/excel-exchange-rates/) - [Best Currency API for Google Sheets](https://allratestoday.com/blog/best-currency-api-for-google-sheets-2026/) - [10 Best Currency Exchange APIs](https://allratestoday.com/blog/best-free-currency-exchange-api-2026/)