# How to get a historical exchange rate for a date

One formula gets an FX rate for a date. Choosing the rate — a central bank's fixing or a market close, which hour, what on a holiday — is the part that matters.

*https://stockmarketstack.com/how-to/get-a-historical-exchange-rate-on-a-date · next to Macro & Economic Data APIs*

**Answer:** In Google Sheets, GOOGLEFINANCE with CURRENCY: and a date range returns daily rates, with no open, high or low and dates treated as noon UTC. In Excel, STOCKHISTORY takes a Currencies data type. In Python, the ECB and FRED publish official daily rates free. None of them is the rate your broker applied. Decide which rate the job needs, which way round the pair is quoted, and what happens on a weekend or a holiday.

## The tools that do this

*In the order this page recommends trying them. Paid placement does not affect this order.*

1. [ECB Data Portal API](https://stockmarketstack.com/tools/ecb-data-portal.md) — The euro reference rates, keyless, as CSV — about 30 currencies against the euro from 1999, one fixing per working day around 14:10 CET.
2. [FRED](https://stockmarketstack.com/tools/fred.md) — The Federal Reserve's H.10 noon buying rates in New York as daily series, free key or keyless CSV, published weekly. Some quoted per dollar, some in dollars.
3. [Alpha Vantage](https://stockmarketstack.com/tools/alpha-vantage.md) — Daily open, high, low and close for a pair on the free key, as CSV that IMPORTDATA or Power Query reads. 25 requests a day.
4. [Twelve Data](https://stockmarketstack.com/tools/twelve-data.md) — A rate at a date or at a minute, in the time zone you name — the closest to the moment a trade happened. Free key licensed for non-display use only.
5. [FXMacroData](https://stockmarketstack.com/tools/fxmacrodata.md) — Other central banks' fixings, such as Brazil's PTAX, for 22 currencies in one schema. From $50 a month; the free tier is US data only.

## 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:

```text
=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:

```text
=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](https://stockmarketstack.com/alternatives/excel-stocks-data-type).

**Python, with an official rate.** The [ECB Data Portal](https://stockmarketstack.com/tools/ecb-data-portal) needs no key.
This returns the euro reference rate for a date, or for the last ECB working day before it:

```python
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](https://stockmarketstack.com/tools/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](https://stockmarketstack.com/tools/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:

```text
=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](https://stockmarketstack.com/tools/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](https://stockmarketstack.com/tools/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](https://stockmarketstack.com/tools/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](https://stockmarketstack.com/how-to/track-foreign-dividends-and-withholding)
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](https://stockmarketstack.com/alternatives/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:

```python
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](https://stockmarketstack.com/collections/forex-data) 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](https://stockmarketstack.com/how-to/pull-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](https://stockmarketstack.com/categories/macro-economic-data). Why a tracker's converted value never
quite matches the broker's is in [why your tracker and your broker disagree](https://stockmarketstack.com/guides/why-your-tracker-and-broker-disagree).

## FAQ

### Why is the GOOGLEFINANCE rate for my date a day off?

Google's help page says it treats dates passed to GOOGLEFINANCE as noon UTC and that values can shift by a day as a result. It does not document what time of day a currency's daily value is taken. Show the date column the function returns next to the rate, so you can see which day you actually got, rather than reading only the number.

### Why does the ECB have no rate for my date?

Because the ECB sets its reference rates only on working days and skips TARGET closing days. Weekends and TARGET holidays have no row at all — in 2026 there is nothing for Good Friday, 3 April, or Easter Monday, 6 April. The usual rule is the last published rate before the date, but it is a rule you choose and should apply the same way every time.

### Which exchange rate should I use for my taxes?

The tax authority's own guidance decides, and it often leaves the choice to you within limits. The IRS says it has no official exchange rate and generally accepts any posted rate used consistently, converting at the spot rate when you receive, pay or accrue an item. HMRC's capital gains manual converts cost at the acquisition date and proceeds at the disposal date, and asks for a reasonable and consistent method. This page explains what each rate is, not which one your return needs.

### Is the ECB reference rate the mid-market rate?

It is a fixing, not a quote anyone dealt at. The ECB sets it in a daily concertation between central banks around 14:10 CET, publishes it around 16:00 CET, and says the rates are for information purposes only and that using them for transactions is strongly discouraged. It is close to the market at that moment and nothing more.

## Sources

1. [GOOGLEFINANCE — Google Docs Editors Help](https://support.google.com/docs/answer/3093281) — Google, read 2026-10-08
2. [STOCKHISTORY function](https://support.microsoft.com/en-us/office/stockhistory-function-1ac8b5b3-5f62-4d94-8ab8-7504ec7239a8) — Microsoft, read 2026-10-08
3. [Get a currency exchange rate](https://support.microsoft.com/en-us/office/get-a-currency-exchange-rate-76572809-c9a0-439e-b626-d9994576af23) — Microsoft, read 2026-10-08
4. [Announcing STOCKHISTORY](https://techcommunity.microsoft.com/blog/excelblog/announcing-stockhistory/1404338) — Microsoft Excel Blog, 2020-06-10. Read on 8 October 2026; the STOCKHISTORY help page is silent on currency pairs, so this post is still Microsoft's statement that they work.
5. [Euro foreign exchange reference rates](https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html) — European Central Bank, read 2026-10-08
6. [U.S. Dollars to Euro Spot Exchange Rate (DEXUSEU)](https://fred.stlouisfed.org/series/DEXUSEU) — Federal Reserve Bank of St. Louis, read 2026-10-08
7. [Foreign Exchange Rates — H.10 Weekly](https://www.federalreserve.gov/releases/h10/current/default.htm) — Board of Governors of the Federal Reserve System, 2026-10-05
8. [Alpha Vantage API Documentation — FX_DAILY](https://www.alphavantage.co/documentation/) — Alpha Vantage, read 2026-10-08
9. [Exchange rate — API reference](https://twelvedata.com/docs/currencies/exchange-rate) — Twelve Data, read 2026-10-08
10. [Yearly average currency exchange rates](https://www.irs.gov/individuals/international-taxpayers/yearly-average-currency-exchange-rates) — Internal Revenue Service, read 2026-10-08
11. [CG78310 — Foreign currency: assets acquired or sold for currency](https://www.gov.uk/hmrc-internal-manuals/capital-gains-manual/cg78310) — HM Revenue & Customs, read 2026-10-08
12. [Check foreign currency exchange rates — UK Integrated Online Tariff](https://www.trade-tariff.service.gov.uk/exchange_rates) — HM Revenue & Customs, read 2026-10-08
13. [What's new in 3.0.0 (January 21, 2026)](https://pandas.pydata.org/docs/whatsnew/v3.0.0.html) — pandas development team, 2026-01-21

*Last updated 2026-10-09. A reference page, corrected in place — not a dated post.*