How to Get Live Exchange Rates in Excel using AllRatesToday API

Need live currency exchange rates inside your spreadsheet? Whether you're building a financial dashboard, tracking FX exposure, or just want today's USD/EUR rate in a cell, the AllRatesToday API makes it straightforward.

This guide walks you through five methods, from a no-code Power Query table to a one-cell formula, official central-bank rates and a VBA macro, so you can pick the one that fits your workflow.

Prerequisites

Before you start, you need an AllRatesToday API key. It takes about 30 seconds:

1. Create a free account. The key is in your welcome email.
2. You can also copy it any time from your dashboard. It looks like art_live_abc123...

The endpoint and the formats Excel likes

Every method below calls the same endpoint. Add format=csv or format=xml and you skip JSON parsing in Excel entirely:

GET https://allratestoday.com/api/v1/rates?source=USD&target=EUR

  format=csv    -> CSV table (UTF-8 with BOM, opens cleanly in Excel)
  format=xml    -> XML, for FILTERXML
  format=xlsx   -> a real Excel workbook
  api_key=KEY   -> the key in the URL, for tools that can't send headers

Leave out target to get every currency against source in one call (160+ rows). Replace USD and EUR with any of the 160+ supported codes.

Method 1: Power Query RECOMMENDED

Power Query is built into Excel 2016+ and Microsoft 365. It loads the CSV straight into a table, refreshes on a schedule, and needs no code.

1. Go to Data → Get Data → From Other Sources → From Web.
2. Paste this URL (with your key) and click OK:
   https://allratestoday.com/api/v1/rates?source=USD&format=csv&api_key=YOUR_API_KEY
3. If Excel asks how to connect, choose Anonymous.
4. Excel recognises the CSV and previews a table with source, target, rate and time columns. Click Load.
5. Look up any rate with =XLOOKUP("EUR", Table1[target], Table1[rate]).

Refresh automatically

Right-click the table → Table → External Data Properties (or Data → Queries & Connections → Properties) and tick Refresh every N minutes and Refresh data when opening the file. Each refresh is one API call: hourly refresh on a workbook open all month is about 720 calls, which fits the €4.99 plan (5,000 a month).

Keep the key out of the URL (optional)

To send the key as a header instead, open Home → Advanced Editor in Power Query and use:

let
    ApiKey   = "YOUR_API_KEY",
    Raw      = Web.Contents("https://allratestoday.com/api/v1/rates",
                 [Query   = [source = "USD", format = "csv"],
                  Headers = [Authorization = "Bearer " & ApiKey]]),
    Csv      = Csv.Document(Raw, [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
    Typed    = Table.TransformColumnTypes(Promoted, {{"rate", type number}}, "en-US")
in
    Typed

The "en-US" culture makes sure rates like 0.888455 parse correctly in locales that use a decimal comma.

Method 2: One formula with WEBSERVICE and FILTERXML

On Windows Excel, one formula returns a live rate into a cell. Ask for XML and let FILTERXML pull out the number:

=FILTERXML(WEBSERVICE("https://allratestoday.com/api/v1/rates?source=USD&target=EUR&format=xml&api_key=YOUR_API_KEY"),"//rate/rate")

With the currencies in cells A2 (from) and B2 (to):

=FILTERXML(WEBSERVICE("https://allratestoday.com/api/v1/rates?source="&A2&"&target="&B2&"&format=xml&api_key=YOUR_API_KEY"),"//rate/rate")

The XPath //rate/rate selects the numeric rate inside each result, so a comma-separated target such as EUR,GBP,JPY spills one rate per row in Microsoft 365.

Method 3: Official central bank rates for month-end and VAT

For accounting you often need the rate a named publisher set, such as the ECB reference rate, not a live market rate. The same techniques work with the central bank rates API (122 banks, latest table on every plan):

Power Query URL (whole ECB table):
https://allratestoday.com/api/v1/central-bank/ecb/latest?format=csv&api_key=YOUR_API_KEY

One cell (USD->EUR at the ECB reference rate):
=FILTERXML(WEBSERVICE("https://allratestoday.com/api/v1/central-bank/ecb/latest?source=USD&target=EUR&format=xml&api_key=YOUR_API_KEY"),"//rate/rate")

Swap ecb for another bank code (boe, rbi, boc and so on). Paste a URL with format=xlsx into your browser and you download the table as a ready-made workbook.

Method 4: VBA Macro

A VBA macro gives you full control: custom headers, error handling, and refresh on a timer (Windows Excel).

Step-by-Step

1. Press Alt+F11 to open the VBA Editor.
2. Go to Insert → Module to create a new module.
3. Paste the code below into the module.
4. Close the VBA Editor and run the macro from Developer → Macros → GetExchangeRate.

Full VBA Code

Sub GetExchangeRate()
    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim rate As Double

    ' --- Configuration ---
    apiKey = "YOUR_API_KEY"   ' Replace with your AllRatesToday API key
    url = "https://allratestoday.com/api/v1/rates?source=USD&target=EUR&format=xml"

    ' --- Make the API call ---
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    http.Send

    If http.Status = 200 Then
        ' --- Read the rate from the XML response ---
        Dim doc As Object
        Set doc = CreateObject("MSXML2.DOMDocument.6.0")
        doc.LoadXML http.responseText
        rate = Val(doc.SelectSingleNode("//rate/rate").Text)

        ' --- Output to cells ---
        Sheet1.Range("A2").Value = "USD/EUR"
        Sheet1.Range("B2").Value = rate
        Sheet1.Range("C2").Value = Now()  ' Timestamp
    Else
        MsgBox "API Error: " & http.Status & " - " & http.responseText
    End If

    Set http = Nothing
End Sub

' --- Optional: auto-refresh every 15 minutes ---
Public NextRun As Date

Sub StartAutoRefresh()
    GetExchangeRate
    NextRun = Now + TimeValue("00:15:00")
    Application.OnTime NextRun, "StartAutoRefresh"
End Sub

Sub StopAutoRefresh()
    On Error Resume Next
    Application.OnTime NextRun, "StartAutoRefresh", , False
    On Error GoTo 0
End Sub

Method 5: Google Sheets (Apps Script)

Google Sheets has no WEBSERVICE, but Apps Script's UrlFetchApp can send the key as a header. (The Google Sheets guide covers more options.)

Step-by-Step

1. Open your Google Sheet and go to Extensions → Apps Script.
2. Delete the default code and paste the script below.
3. Replace YOUR_API_KEY with your actual key.
4. Save the script (Ctrl+S) and close the Apps Script editor.
5. Back in the sheet, use the custom function: =getRate("USD","EUR")

Apps Script Code

/**
 * Fetches the live exchange rate from AllRatesToday.
 *
 * @param {string} source  Source currency code, e.g. "USD"
 * @param {string} target  Target currency code, e.g. "EUR"
 * @return {number}        The current exchange rate
 * @customfunction
 */
function getRate(source, target) {
  var API_KEY = "YOUR_API_KEY"; // Replace with your key

  var url = "https://allratestoday.com/api/v1/rates"
          + "?source=" + encodeURIComponent(source)
          + "&target=" + encodeURIComponent(target);

  var response = UrlFetchApp.fetch(url, {
    headers: { Authorization: "Bearer " + API_KEY },
    muteHttpExceptions: true
  });
  var body = JSON.parse(response.getContentText());

  if (response.getResponseCode() !== 200) {
    throw new Error("API error: " + (body.error || response.getResponseCode()));
  }

  // The API returns an array of rows: [{ rate, source, target, time }]
  return body[0].rate;
}

Now you can use =getRate("USD","EUR") in any cell.

Which Method Should You Use?

Frequently Asked Questions