1

I am using google sheets, and would like to know the most recent stock pricing available for the date calculated. Currently i am using this,

=GOOGLEFINANCE("NASDAQ:TSLA", "close",EOMONTH(today(),-1))

If the date it returned is not an open market day, i receive an error. Which makes sense. I would like to return the most closest market open day however. Not sure if this is possible. Thanks in advance.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
JordanLeo
  • 25
  • 5

2 Answers2

1

use:

=VLOOKUP(IF(ABS((LARGE(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)), COUNTIF(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)),">"&
 DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))+"16:00")+1))-(
 DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))+"16:00"))<ABS((SMALL(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)), COUNTIF(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)),"<"&
 DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))+"16:00")+1))-(
 DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))+"16:00")), LARGE(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)), COUNTIF(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)),">"&
 DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))+"16:00")+1), SMALL(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)), COUNTIF(
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)),"<"&
 DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY()))+"16:00")+1)), 
 GOOGLEFINANCE("NASDAQ:TSLA", "CLOSE", EOMONTH(TODAY(), -2)+1, EOMONTH(TODAY(), -1)), 2, 0)
  • today and tomorrow this will give you B8
  • on 3rd this will give you B9

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • This is only giving me the most recent price. I would like the closest closing price to the date specified (1 month ago). Not sure if that clears it up. – JordanLeo Nov 01 '21 at 02:44
  • Instead of getting the most recent closing price of the current time. I would like to go back 1 month and then see what the closed closing price to that is. So say for example today()-31 does not equal a market day, so i want to instead find the closest day to that which was an open market day. I hope that makes sense. – JordanLeo Nov 01 '21 at 05:48
  • 1
    Hi there @JordanLeo! I don't have a clear idea of your goal. I understand that you want the stock price of the date 30 days ago. But, what if that date lands on a Saturday or Sunday? – Jacques-Guzel Heron Nov 01 '21 at 11:03
  • @Jacques-GuzelHeron So basically what i am trying to do is calculate the price difference of a stock from one month ago to the present date. As you said, if the price does land on a Saturday or a Sunday what will i do? I am not currently sure. My question was, is there some way to get the closest closing price to that saturday/sunday? Thanks. – JordanLeo Nov 01 '21 at 11:57
  • @JordanLeo answer updated – player0 Nov 01 '21 at 13:10
1

Try

=index(sort(GOOGLEFINANCE("NASDAQ:TSLA", "close",today()-33,3),1,0),2,2)

for the value on today minus 30 days or previous if the day was off

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Thank you so much. This works perfectly. I will just have to figure out what all this means haha. I am fairly new to google sheets. Its all very interesting. – JordanLeo Nov 01 '21 at 12:12