How to get a historical exchange rate for a date

The short way

A rate "on a date" is three decisions before it is a formula: whose rate (a central bank's fixing, or some vendor's market close), what hour of that day, and what to do when the day has no rate. Make them once and write them next to the column. Then:

Google Sheets. Google's own example sheet builds the symbol as CURRENCY: followed by the two currency codes and asks for "price" over a date range. Asking for a range rather than one day gets you the last rate on or before the date even when the date is a Saturday:

=LET(h, GOOGLEFINANCE("CURRENCY:EURUSD", "price", A2 - 7, A2), INDEX(h, ROWS(h), 2))

With the date in A2, that is the last value in the week up to A2; change the final 2 to 1 to see which day it came from. Google's help page says currencies "don't have trading windows", so open, high, low and volume do not return for them — a currency is one number a day here. It also says Google treats the dates you pass as noon UTC, so a value can land a day off.

Excel. Type EUR/USD in A1, select it, and choose the Currencies data type on the Data tab — Microsoft's help says the pair is written from-currency, slash, to-currency in ISO codes. Then, with the date in B1:

=TAKE(STOCKHISTORY(A1, B1 - 7, B1, 0, 0, 0, 1), -1)

returns the date and the close of the last day on or before B1, without headers. Microsoft's Excel blog announcing STOCKHISTORY says it accepts currency pairs, and that the easiest way to pass one is a cell holding the data type. It needs a Microsoft 365 subscription, and the currencies help page says pairs are only available to Microsoft 365 accounts on worldwide multi-tenant tenants. Its other limits are on what to use instead of the Stocks data type and STOCKHISTORY.

Python, with an official rate. The ECB Data Portal needs no key. This returns the euro reference rate for a date, or for the last ECB working day before it:

import pandas as pd

def ecb_rate(currency: str, day: str) -> tuple[str, float]:
    """Units of `currency` per one euro on `day`, or on the last ECB working day before it."""
    start = (pd.Timestamp(day) - pd.Timedelta(days=10)).date()
    url = ("https://data-api.ecb.europa.eu/service/data/EXR/"
           f"D.{currency}.EUR.SP00.A?startPeriod={start}&endPeriod={day}&format=csvdata")
    rates = pd.read_csv(url, usecols=["TIME_PERIOD", "OBS_VALUE"])
    last = rates.iloc[-1]
    return last["TIME_PERIOD"], float(last["OBS_VALUE"])

print(ecb_rate("USD", "2026-04-03"))   # Good Friday: ('2026-04-02', 1.1525)

What the options are

The euro reference rates. The ECB Data Portal serves them as an SDMX series keyed EXR/D.{currency}.EUR.SP00.A, about 30 currencies against the euro, one value per working day from 1999. The ECB's page says they come from a concertation between central banks that normally takes place around 14:10 CET and are published around 16:00 CET; the series' own title in the API reads "2.15 pm (C.E.T.)". They are quoted as units of the other currency per euro, so USD returns dollars per euro. In Sheets, =INDEX(IMPORTDATA(url), 2, 8) on the same URL returns the value — it is the eighth column of the CSV.

The Federal Reserve's noon rates. FRED carries the Fed's H.10 release as daily series — DEXUSEU for the euro, DEXJPUS for the yen — whose notes describe them as noon buying rates in New York City for cable transfers payable in foreign currencies. The API takes a free key at 120 requests a minute, FRED ships a free Excel add-in, and the CSV behind each series page's download link needs no key at all, which makes it a one-cell lookup in Sheets:

=INDEX(IMPORTDATA("https://fred.stlouisfed.org/graph/fredgraph.csv?id=DEXUSEU&cosd=2026-03-31&coed=2026-03-31"), 2, 2)

H.10 is a weekly release: on 8 October 2026 the newest DEXUSEU value was for 2 October, published on 5 October. A rate for yesterday is not there yet.

A vendor's daily close. Alpha Vantage's FX_DAILY returns open, high, low and close per day for any pair in its currency list, on the free key, and datatype=csv makes it readable by IMPORTDATA or Power Query's From Web; outputsize=full returns the whole history in one call, which matters at 25 requests a day. Its documentation does not say at what hour the daily bar closes.

A rate at a moment. Twelve Data's exchange-rate endpoint takes a date that can be a day or a date and time, read in the time zone you pass — the nearest thing here to the rate at the minute a trade was filled. The free key allows 800 requests a day and is licensed for internal non-display use only; anything shown to other people is a paid plan.

Another central bank's fixing. Some tax authorities and contracts name their own central bank's rate. FXMacroData collects official reference rates and fixings for 22 currencies, such as Brazil's PTAX and the Central Bank of Iceland's 16:00 rate, in one schema, from $50 a month; its free tier is US data only.

Where this breaks

A reference rate is not the rate you got. The ECB says its rates are published for information purposes only and that using them for transactions is strongly discouraged. Your broker converted at its own rate, with its own margin inside it, at its own moment. For what a trade or a dividend actually cost or paid, the confirmation's rate is the record; the reference rate is for valuing things nobody converted. Tracking foreign dividends works through what that means for a ledger.

Two official rates for the same day disagree. For 31 March 2026 the ECB's reference rate was 1.1498 dollars per euro and the Fed's H.10 noon rate 1.1518. On €100,000 that is $200 between two answers to "the rate on 31 March", both correct, nine hours apart. Pick one source and one hour per job and do not mix them inside a ledger.

Weekends and holidays are different holes in each source. The ECB publishes nothing on TARGET closing days: in 2026 there is no row for Good Friday, 3 April, or Easter Monday, 6 April, while H.10 has both (1.1523 and 1.1545). The Fed skips US holidays instead, and FRED's CSV keeps the date with an empty value — Thanksgiving, 27 November 2025, is a row with nothing in it, which a spreadsheet reads as zero if you let it. A lookup for the exact date fails, or a join silently drops the row. The usual rule is the last published rate before the date; whichever rule you use, apply it everywhere.

Which way round. EURUSD at 1.15 is dollars per euro. FRED's DEXUSEU is dollars per euro and its DEXJPUS is yen per dollar; the H.10 release marks the Australian dollar, the euro, the New Zealand dollar and the pound as dollars per unit and quotes every other currency per dollar. The IRS's yearly average table is foreign currency per dollar, which is why its instruction is to divide a foreign amount by the rate. Get it backwards and €10,000 at 1.15 becomes $8,696 instead of $11,500.

Cross rates are arithmetic, not observations. The ECB publishes everything against the euro and H.10 everything against the dollar. A yen-per-dollar figure from the ECB is two euro rates divided — consistent, and nobody's quote.

GOOGLEFINANCE has limits beyond the date. Google's help says historical data cannot be read through the Sheets API or Apps Script — a script gets #N/A — and that the data is not for financial-industry professional use. What to use instead of GOOGLEFINANCE covers both.

A tax authority's own table is not always a rate for your date. HMRC's monthly exchange rates are for customs and VAT: published on the penultimate Thursday of each month, they apply to the following calendar month and reflect rates at midday the day before publication, so HMRC's rate "for" 15 October is a September rate. For capital gains, HMRC's manual converts the cost at the acquisition date and the proceeds at the disposal date, and says it does not prescribe a reference point but expects a reasonable and consistent method. The IRS says it has no official exchange rate, generally accepts any posted rate used consistently, and asks for the spot rate when you receive, pay or accrue an item. Which of these applies to you is the tax authority's material, not this page's.

If you outgrow this

When it is a ledger, not a date. Calling an API once per transaction spends a free quota in an afternoon. Download the series once and join it to the transactions by the rule you chose:

trades = trades.assign(date=trades["date"].dt.as_unit("ns")).sort_values("date")
rates = rates.assign(date=rates["date"].dt.as_unit("ns")).sort_values("date")   # columns: date, rate
priced = pd.merge_asof(trades, rates, on="date", direction="backward")   # last rate on or before

The two as_unit calls are for pandas 3.0, released on 21 January 2026, which parses date strings to microseconds where pandas 2 used nanoseconds. merge_asof refuses keys of two different resolutions with a MergeError, and a ledger parsed under pandas 3 meeting a rate series stored at nanoseconds by pandas 2 is exactly that case. Both snippets on this page ran under pandas 3.0.6 on 9 October 2026: the ECB function returned the Good Friday value shown, and the join returned the last rate on or before each trade.

When the hour matters more than the day — a trade at 15:30 New York against a fixing set at 14:10 Frankfurt — the forex data collection separates reference rates, vendor quotes and tick history, and says which is for what.

When the rate is one series among many, pulling an economic data series covers FRED, the ECB and the other official sources in general, and the rest of the shelf is macro and economic data. Why a tracker's converted value never quite matches the broker's is in why your tracker and your broker disagree.