"What was the rate on the 14th?"
Live rates are for dashboards. The questions money documents ask are historical: what was USD/EUR on the invoice date, what rate applies to a receipt from March, what did the quarter average. Sheets can answer these – with real syntax, two quirks and one hard wall, all documented below.
The formula, unwrapped
A historical GOOGLEFINANCE call returns a small table, not a number – headers plus the value row. INDEX extracts the figure:
=INDEX(GOOGLEFINANCE("CURRENCY:USDEUR","price",DATE(2026,3,14)),2,2)
For a whole invoice column, point the date argument at the cell: DATE() becomes A2, filled down. Each cell fetches its own dated rate. (Google's documented historical attribute is close; price with a date is the form most examples use, and both return the same date-and-close table. If one errors for you, try the other – the troubleshooting guide covers the rest.)
The two quirks to price in
- Dates are treated as noon UTC. Google documents this – it "treats dates passed into GOOGLEFINANCE as noon UTC time" – and it means a "rate on the 14th" can reflect a different market moment than your local close. For bookkeeping at normal precision this rarely matters, but know it before reconciling against a bank statement to four decimals.
- Only the closing price exists for currencies. Google's note: "Currencies don't have trading windows, so open, low, high, and volume won't return." Averages you build yourself from a date range of close rows.
And the standing caveats on the whole feature: the live quote is "delayed by up to 20 minutes" per the help page, Google "cannot guarantee the accuracy of the exchange rates displayed" per its disclaimer – and the CURRENCY: prefix itself appears nowhere in the GOOGLEFINANCE documentation. It works; it is not promised.
The wall: no automation allowed
Here is the sentence almost nobody quotes, from Google's own help: "Historical data cannot be downloaded or accessed via the Sheets API or Apps Script."
That means every scripted pipeline – nightly rate pulls, automated reconciliation, anything a script reads – is out, by policy, not by bug. In-sheet formulas are the ceiling of what GOOGLEFINANCE offers historically. If you need rates in code, the practical sources are the free reference feeds (the ECB publishes daily euro reference rates with full history, free, no key – with its own caveat that "using the rates for transaction purposes is strongly discouraged") or a commercial rate API. When a formula is enough compares the routes.
A practical pattern: the rate column that freezes itself
For invoice or expense sheets, fetch then freeze: fill the historical formula down, check the values, then copy the rate column and paste special → values only. Rates on money documents should not silently recompute months later if Google's data shifts or the formula errors – a frozen number with a date beside it is the audit-friendly shape. The multi-currency invoice tracker builds this in as a column.
When the spreadsheet is the detour
If the actual job is one conversion for a quote or an invoice line while you are writing the email or the document, Currency Converter answers it in a sidebar – live rates for 180+ currencies inside Gmail, Docs, Calendar, Slides and Sheets, where no formula can follow. Sidebar conversions are unlimited and free, no sign-up. Its rate-on-a-date lookup is a Sheets formula in the Premium tier, with history back to 2000.
Sources
- GOOGLEFINANCE – Google Docs Editors Help – read 2026-09-16
- Google Finance disclaimer – read 2026-09-16
- ECB euro foreign exchange reference rates – read 2026-09-16
- Currency Converter by 8apps – product claims, read 2026-09-16