Google Sheets currency formula builder

Tell it which cells hold the amount, currencies and date. Copy the two formulas and fill them down.

From and To can be cells such as B2 or fixed codes such as USD. Leave Date empty for the current rate only.

Rate formula for E2

=IF(OR(B2="",C2=""),"",IF(B2=C2,1,IF(D2="",GOOGLEFINANCE("CURRENCY:"&B2&C2),LET(hist,GOOGLEFINANCE("CURRENCY:"&B2&C2,"price",D2-7,D2+1),days,ARRAYFORMULA(IFERROR(INT(INDEX(hist,0,1)),0)),pos,XMATCH(INT(D2),days,-1),IF(pos=1,NA(),INDEX(hist,pos,2))))))

Converted amount, next to it

=IF(OR(A2="",E2=""),"",ROUND(A2*E2,2))

How the rate formula reads

Outside in. Each line either answers the row or passes it to the next one.

  1. IF(OR(B2="",C2=""),"", ...)Blank until the currency cells are filled in.
  2. IF(B2=C2,1, ...)Same currency on both sides: the rate is exactly 1.
  3. IF(D2="",GOOGLEFINANCE("CURRENCY:"&B2&C2), ...)No date in D2: use the current rate.
  4. 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.
  5. days = ARRAYFORMULA(IFERROR(INT(INDEX(hist,0,1)),0))Column 1 as whole days. The header text becomes 0.
  6. pos = XMATCH(INT(D2),days,-1)Row of D2 itself, or of the last earlier day that has a close.
  7. 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.

Positionhist column 1column 2
1DateClose
2Mon 2026-10-260.8571
3Tue 2026-10-270.8590
4Wed 2026-10-280.8566
5Thu 2026-10-290.8602
6Fri 2026-10-300.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.

Free, from Producfy

Get the next guide before you need it

One email when a new guide or tool goes up: working formulas, queries and scripts for spreadsheets, SQL and the CRM and billing exports that end up in them. At most one a week.

Esc