Automatic currency conversion in Google Sheets

Question

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!

Reference: Automatically store updated GOOGLEFINANCE values with a script

Answer 1

Other option is using the CurrencyConverter function from this Google Sheets add-on. It is fast and and has simple syntax. For example,

=CurrencyConverter(100, "USD", "EUR", "2/28/2020")

returns 91.09957183

Disclaimer: I am author of this add-on.

Answer 2

• 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); )

Answer 3

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); )