Build a Dynamic FX Calculator in Google Sheets with GOOGLEFINANCE
How do I make a currency converter in Google Sheets that updates when I change the currencies?
Build the GOOGLEFINANCE ticker from cells instead of typing it: "CURRENCY:"&B2&C2 joins the two codes from dropdowns into a pair such as USDEUR. Multiply the amount by that rate. Add a date column when you need the rate for a past transaction rather than the current quote.
To build a dynamic FX calculator in Google Sheets, put the two currency codes in their own cells and build the ticker from them: GOOGLEFINANCE("CURRENCY:"&B2&C2) returns the rate for whatever pair B2 and C2 hold. Multiply the amount by that rate and the result follows every change to the dropdowns. For a past transaction, add a date column and pull the daily close instead of the live quote.
The formulas below handle the cases that break the one-line version: blank rows, a row where both currencies match, and dates that fall on a weekend. If you would rather fill in your own cell addresses, the currency formula builder generates the same formulas.
How do you convert currency in Google Sheets with GOOGLEFINANCE?
You convert currency by multiplying an amount by GOOGLEFINANCE("CURRENCY:USDEUR"), where the ticker is CURRENCY: followed by the source code and then the target code.
Google’s GOOGLEFINANCE reference gives the syntax as GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]). With only a ticker, the function returns a current value in a single cell. Order matters: USDEUR is euros per dollar, and EURUSD is dollars per euro. Swapping the codes does not throw an error. It quietly gives you the reciprocal, which is the most common way a converter goes wrong without anyone noticing.
A hard-coded ticker works for one pair, but every new pair means editing the formula. The rest of this guide moves the codes into cells so the formula never changes.
How do you make the currency pair follow a dropdown?
Join the text "CURRENCY:" with the two code cells using &, and point both cells at a dropdown so nobody types a code by hand.
Start a Codes sheet with one three-letter code per row in column A: USD, EUR, GBP, CAD, JPY, and any others you deal in. On the calculator sheet, select B2:C20 and add a dropdown. Google’s dropdown instructions list several routes; Data > Data validation > Add rule, then Dropdown from a range with Codes!A1:A20, is the one I would use because the list stays editable in one place. Leave the default that rejects entries not on the list, so a typo such as EUD never reaches the formula.
Set up the calculator sheet with these fictional rows:
| A: Amount | B: From | C: To | D: Date | E: Rate | F: Converted |
|---|---|---|---|---|---|
| 1250 | USD | EUR | |||
| 980 | GBP | USD | 2026-10-30 | ||
| 450000 | JPY | USD | 2026-10-31 | ||
| 300 | CAD | CAD | |||
| EUR | GBP |
Then put this in E2 for a live rate:
=IF(OR(B2="",C2=""),"",IF(B2=C2,1,GOOGLEFINANCE("CURRENCY:"&B2&C2)))
The outer check returns a blank while either code is missing. The middle check returns 1 when both codes match, so row 5 shows CAD to CAD at a rate of exactly 1 without asking GOOGLEFINANCE for a pair it has no reason to quote.
How do you get the exchange rate for a specific date?
Pass a start and end date and GOOGLEFINANCE returns a small table of daily closes; the formula then picks the latest close on or before the transaction date.
The GOOGLEFINANCE reference states that historical data, even for a single day, comes back as an expanded array with column headers. That is why GOOGLEFINANCE("CURRENCY:USDEUR","price",D2) spills two rows and two columns instead of returning one number, and why wrapping it in plain arithmetic fails. The same page does not say what a weekend or holiday returns. Row 4 is dated Saturday, October 31, 2026, so the formula has to cope with a date that may have no close at all.
This version of E2 uses the live rate when D2 is blank and the dated rate when it is filled:
=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))))))
LET names the history table hist so GOOGLEFINANCE runs once per row. days strips the time from each date in the first column and turns the header text into 0. XMATCH with match mode -1 finds the exact date or the next earlier one; Google documents a sorting requirement only for its binary search modes, and this formula uses the default linear search. If the only match is the header row, pos is 1, and the cell shows #N/A instead of the word “Close”.
The window runs from seven days before the date to one day after it. Asking for the day after keeps the target date inside the range whichever way the end date is counted, and the XMATCH step discards anything later than D2. For row 4, the Saturday has no exact match, so the formula falls back to the most recent earlier close in the window.
How should the converted amount be rounded?
Round the converted amount to the target currency’s minor unit with ROUND, and keep the rate itself unrounded in its own column.
Put this in F2 and fill it down with E2:
=IF(OR(A2="",E2=""),"",ROUND(A2*E2,2))
Here is the arithmetic with an illustrative rate, not a real quote. If E2 showed 0.9213, then 1250 * 0.9213 = 1151.625, and ROUND gives 1151.63. Row 6 has no amount, so F6 stays blank even though E6 may show a rate.
Two decimal places suit dollars, euros and pounds, but not every currency. Yen has no minor unit in everyday invoicing, so a USD to JPY column wants ROUND(A2*E2,0). With the illustrative rate of 0.0066 on row 4, 450000 * 0.0066 = 2970, which needs two places because the target there is dollars. The decimals follow the target currency, not the source.
I would not round the rate. A rate cut to two places turns 0.0066 into 0.01 and inflates row 4 by more than half.
How current are GOOGLEFINANCE exchange rates?
GOOGLEFINANCE quotes may be delayed by up to 20 minutes, according to Google’s function reference, which also says the data is not sourced from all markets.
That is fine for budgeting, expense reports and a rough check on a supplier quote. It is not a rate you can hold a bank or payment provider to. When the number has to match what an invoice or statement says, type the provider’s rate into column E for that row and leave a note saying where it came from.
Live rates also move after you close an invoice. A converted amount that keeps updating is useful on a pricing sheet and wrong on a record of what was paid. For closed rows, either fill in the date so the formula pins a historical close, or copy column F and paste values only. The dated version is easier to audit later because the inputs are still visible. The same habit of keeping the source next to a derived value applies when you convert UTC handoff times to local time: the readable result is not the record.
What breaks a GOOGLEFINANCE currency calculator?
The usual failures are scripts that read the sheet, reversed pairs, codes outside the dropdown list, and too many rows calling GOOGLEFINANCE at once.
| Symptom | Likely cause | Fix |
|---|---|---|
#N/A when a script reads column E |
Historical data is blocked outside the sheet | Read the values in the sheet, or store rates as values first |
| Amount roughly inverted | From and To swapped | Check the pair order: USDEUR is euros per dollar |
#N/A on one dated row |
No close anywhere in the lookback window | Widen the window or enter the rate by hand |
| Dropdown rejects a new code | Code not in Codes!A1:A20 |
Add it to the list and extend the range |
The first row is the one that surprises people. Google’s reference says historical data cannot be downloaded or accessed through the Sheets API or Apps Script, and that cells come back as #N/A if you try. A script that totals dated conversions, or a tool that exports the sheet through the API, will not see those numbers. Paste the rates as values before anything outside the sheet reads them.
Each dated row is its own GOOGLEFINANCE request. For a ledger with hundreds of rows, I prefer a separate rates table with one row per date and pair, then a lookup from the ledger. That keeps the number of requests tied to distinct dates instead of transactions. An exact-match lookup is the right tool there, the same approach as when you look up a project owner by ID without approximate matches. Other patterns for keeping report figures honest are in Spreadsheets and Data.