The problem is one total, three currencies
Invoice clients in USD, EUR and GBP and one question becomes surprisingly slippery: how much did we invoice this quarter? Answering needs every row in one base currency – which means a rate per invoice, and a decision about which day's rate. Most trackers dodge the decision and drift; this one makes it a column.
The columns
| Col | Field | Notes |
|---|---|---|
| A | Date | invoice date, ISO format |
| B | Client | |
| C | Amount | as billed |
| D | Currency | 3-letter code, e.g. EUR |
| E | Rate | to base currency – see policy below |
| F | Base amount | =C2*E2 |
| G | Status | sent / paid / overdue |
The tracker's whole intelligence is columns D–F: original amount preserved, rate explicit, base total computable. =SUM(F:F) and a pivot by client or month answer everything the flat version couldn't.
The rate-date policy (decide once, write it down)
Pick one and note it in the sheet header:
- Invoice-date rate – each row uses the rate from its own date. The common bookkeeping convention, and what makes quarter totals meaningful.
- Payment-date rate – matches what actually landed, at the cost of rates arriving late.
For invoice-date rates, GOOGLEFINANCE fills column E:
=IF(D2="USD", 1, INDEX(GOOGLEFINANCE("CURRENCY:"&D2&"USD","price",A2),2,2))
(Base USD shown; swap the code for yours. The historical exchange rates guide explains why the INDEX wrapper is there.) Then freeze the rates: once checked, copy column E and paste special → values only. Money documents should not recompute themselves months later – Google's live quote is "delayed by up to 20 minutes", Google "cannot guarantee the accuracy of the exchange rates displayed", and a frozen rate with its date beside it is the defensible shape. For serious books, your accountant may prefer an official source – the ECB publishes free daily reference rates, with its own caveat against transaction use.
The two failure modes to design out
- Mixed-currency sums. Never
SUMcolumn C. The base column exists so no one ever adds euros to dollars; consider hiding C from report views. - Backfilled rates. A row added late with today's rate against March's invoice quietly corrupts the quarter. The formula above pulls the rate from the row's date – keep it that way, and paste-values only after checking.
The same skeleton runs expense reports across currencies and a freelancer's billed-versus-received sheet.
Where Currency Converter fits
Building the tracker is a Sheets job. Running it day to day is mostly small conversions in odd places – checking a client's figure while reading their email, converting a number while drafting the invoice in Docs. Currency Converter handles those in a sidebar inside Gmail, Docs, Calendar, Slides and Sheets – live rates for 180+ currencies, unlimited and free in the sidebar, no sign-up – so the tracker stays the record while the sidebar does the arithmetic. Its Premium tier adds a Sheets formula with historical rates back to 2000, if you would rather not lean on GOOGLEFINANCE for column E.
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