Google Sheets currency formula builder
Tell it which cells hold the amount, currencies and date. Copy the two formulas and fill them down.
Rate formula for E2
Converted amount, next to it
How the rate formula reads
Outside in. Each line either answers the row or passes it to the next one.
IF(OR(B2="",C2=""),"", ...)Blank until the currency cells are filled in.IF(B2=C2,1, ...)Same currency on both sides: the rate is exactly 1.IF(D2="",GOOGLEFINANCE("CURRENCY:"&B2&C2), ...)No date in D2: use the current rate.hist = GOOGLEFINANCE("CURRENCY:"&B2&C2,"price",D2-7,D2+1)Daily closes from 7 days before D2 to the day after, with a header row on top.days = ARRAYFORMULA(IFERROR(INT(INDEX(hist,0,1)),0))Column 1 as whole days. The header text becomes 0.pos = XMATCH(INT(D2),days,-1)Row of D2 itself, or of the last earlier day that has a close.IF(pos=1,NA(),INDEX(hist,pos,2))Only the header matched: #N/A. Otherwise the close from that row.
What a weekend date does
Date is Saturday, October 31. There is no close for that day, so the formula takes the last earlier one.
| Position | hist column 1 | column 2 |
|---|---|---|
| 1 | Date | Close |
| 2 | Mon 2026-10-26 | 0.8571 |
| 3 | Tue 2026-10-27 | 0.8590 |
| 4 | Wed 2026-10-28 | 0.8566 |
| 5 | Thu 2026-10-29 | 0.8602 |
| 6 | Fri 2026-10-30 | 0.8615 |
pos = 6, so the rate is 0.8615. If only row 1 came back, the cell would show #N/A.Closes are illustrative.Checked by tests that compare this builder's output with the formulas in the guide, and the rounding formula is evaluated in HyperFormula. GOOGLEFINANCE itself has not been run in Google Sheets, so compare the first rows with a rate you trust. Full setup with dropdowns: Build a dynamic FX calculator in Google Sheets.