Is there a method for retrieving stock price data for the most recent open market day with google fi-CodePudding

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.

CodePudding user response：

Try

=query(GOOGLEFINANCE("NASDAQ:TSLA", "close",EOMONTH(today(),-1)-3,3),"select * order by Col1 desc limit 1",0)

=index(query(GOOGLEFINANCE("NASDAQ:TSLA", "close",EOMONTH(today(),-1)-3,3),"select * order by Col1 desc limit 1",0),1,2)

CodePudding user response：

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

CodePudding user response：

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