3

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

2
  • 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, 2019 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, 2019 at 14:32

3 Answers 3

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.

2
  • It'd good etiquette to add a disclaimer when you're the author of the apps you recommend on StackExchange. Apr 30, 2020 at 8:00
  • @PaulRazvanBerg Thanks. Updated. May 1, 2020 at 13:29
0
  • 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); )

5
  • 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, 2019 at 17:33
  • @RyanSmith answer updated
    – user0
    Feb 10, 2019 at 19:10
  • 1
    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, 2019 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, 2019 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, 2019 at 20:56
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.