Google-sheets – Get historical stock price in another currency

google sheetsgoogle-finance

This formula returns the daily stock prices of A1 in the last two years:

=GOOGLEFINANCE(A1,"Price",today()-(365*2),today(),"DAILY")

This formula returns the daily conversion rate from USD to EUR:

=GOOGLEFINANCE("Currency:USDEUR", "price", today()-(365*2),today())

I would like to put the two formulas together so that the historical stock prices are automatically given in EUR and not in USD. Is there any way to put the two formulas into one? Maybe with the help of SQL?

Best Answer

I took a stab at it here:

enter image description here

Column I has a formula like:

=QUERY({$F$2:$G$1000},"SELECT Col1 WHERE Col1 <= date'"& text(C3,"yyyy-mm-dd") &"' ORDER BY Col1 DESC LIMIT 1", -1)

This means that it pulls in the closest currency price date that's less than or equal to the stock price date.

Column J has a formula like:

=D3/VLOOKUP(I3, $F$2:$G$1000, 2, FALSE)

This means we're dividing the stock price by the currency conversion price on that nearest date. Hope this helps!

Related Topic