# Automatic currency conversion in Google Sheets

I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it automatically, fixed at that moment's EUR -> USD rate (and not fluctuate with the currency exchange rate over time), and rounded up to the nearest dollar.

Currently, I only enter the converted amount manually, based on the output from a calculation cell with the `=GOOGLEFINANCE("CURRENCY:EURUSD")` function.

For each accounting entry, today's date is manually entered in column A, the EUR figure manually entered in column F, and I would like the automatically converted EUR -> USD figure (rounded up to the nearest dollar) to appear next to that EUR entry in column E, and remain unchanging. As a reference the `=GOOGLEFINANCE("CURRENCY:EURUSD")` function lives in cell J1, which is part of where I currently do the aforementioned manual currency conversions in the small calc table I1:L2.

Link to sample sheet here

Many thanks for any help!

• I just may not have phrased it correctly - once the the conversion to USD is calculated, I don't want the USD converted figure to fluctuate when the currency rate fluctuates over time. Will edit question to make more clear, thank you. – Ryan Smith Feb 10 at 14:24
• @MARKMYANSWER no, i just want it automated, and not fluctuating once calcluated, so if anyone can think of way to do that in any way that would be great :) – Ryan Smith Feb 10 at 14:32

## 2 Answers

• paste in cell E3 and drag down

``````=IF(LEN(F3); ROUND(F3 * IFERROR(VLOOKUP(A3;
{DATEVALUE(QUERY(GOOGLEFINANCE("currency:eurusd"; "price"; A3; A3+1);
"select Col1 where Col1 is not null"; 0))\
QUERY(GOOGLEFINANCE("currency:eurusd"; "price"; A3; A3+1);
"select Col2 where Col2 is not null"; 0)}; 2; 0);
GOOGLEFINANCE("currency:eurusd")); 0); )``````

• Thanks for the formula, however I believe this does not work for two reasons: 1) cell J1's Google Finance function is a volatile formula, and the array formula output will accordingly constantly change as the EUR:USD conversion rate changes; and 2) It outputs a text string, which I can't use....it would need to output a value. I've modified my original question to include a link to a sample google sheet to work this out on. Thank you! – Ryan Smith Feb 10 at 17:33
• @RyanSmith answer updated – user0 Feb 10 at 19:10
• everything worked brilliantly and you were a great help to work through the specifics of the problem, thanks again for your time and support! – Ryan Smith Feb 10 at 19:42
• This solution seems to work fine without the ARRAYFORMULA component, am I correct or am I not seeing a potential error? – Ryan Smith Feb 10 at 20:36
• not sure what exactly you are asking... `ARRAYFORMULA` is on the first row, right after `IFERROR` and its needed to construct virtual table - that stuff what is in between `{}` brackets. and yes, it may work without it, but not sure (I didnt tested it out) if in all possible scenarios so I left it there as "safety future" – user0 Feb 10 at 20:56

Formula variation without `ARRAYFORMULA`:

``````=IF(LEN(F3); ROUND(F3 * IFERROR(VLOOKUP(A3;
{DATEVALUE(QUERY(GOOGLEFINANCE("currency:eurusd"; "price"; A3; A3+1);
"select Col1 where Col1 is not null"; 0))\
QUERY(GOOGLEFINANCE("currency:eurusd"; "price"; A3; A3+1);
"select Col2 where Col2 is not null"; 0)}; 2; 0);
GOOGLEFINANCE("currency:eurusd")); 0); )
``````