6

When using the GOOGLEFINANCE formula to get historical prices and performance for different periods, if the date used in the formula is one where the market was closed the formula returns #N/A. This occurs for every weekend as well as market holidays.

For example =GOOGLEFINANCE("GOOG","Close","1/1/15") returns #N/A

Since I'm looking at performance, the correct price for a stock on a date the market was closed is the close price of the last date the market was open prior to that date.

I have seen workarounds using WORKDAY, but that would involve having a separate table with all the market holidays. This seems like a common enough problem for there to be an easier solution.

Is there another way to obtain the price for the last open market date for any given date?

2
  • Both return an answer for 1/1/18, but not for market holidays prior to 1/1/17. I'll change the date of my example to avoid confusion. Also, when it does work it returns the closing price of the next day of trading, and to calculate returns up to that date I need the previous day.
    – twalbaum
    Mar 6 '18 at 8:50
  • 1
    It's a pity GoogleFinance doesn't offer a "CloseLast" attribute, so we wouldn't have to go through awkward contortions to get something rather obviously required!
    – maxhugen
    Jun 20 at 17:57
5

A workaround that solves the above is getting the Close price for the stock for a range of days before and after the date in question, and then querying the result for the most recent price on or before that date, like this:

=QUERY(GOOGLEFINANCE("GOOG","Close",DATEVALUE("1/1/15")-5,6),"select Col2 where Col1 < date '2015-01-02' order by Col1 desc limit 1",False)

To make this dynamic, you can reference the date to another cell (A1 in the example below) like this:

=QUERY(GOOGLEFINANCE("GOOG","Close",A1-5,6),"select Col2 where Col1 < date '"&TEXT(A1+1, "YYYY-MM-DD")&"' order by Col1 desc limit 1",False)

I came up with this answer after trying multiple options, taking a hint from this post that was trying to find accurate moving average for historical data.

7
  • I'm leaving the question open for a few days to see if there are other alternatives answers.
    – twalbaum
    Mar 9 '18 at 0:33
  • 2
    I tried this out and while it works almost completely perfectly, it seems to be "off by one". I have to do =QUERY(GOOGLEFINANCE("GOOG","Close",A1-5,6),"select Col1, Col2 where Col1 <= date '"&TEXT(A1+1, "YYYY-MM-DD")&"' order by Col1 desc limit 1",False) in order to get the date contained in A1 (if it's available). Note the +1 in the less than or equal clause. Can you confirm if I am correct or am I missing something? If I'm correct, you may want to slightly update your answer. Thank you for the base code however! Jun 29 '19 at 0:14
  • To follow up above, I have a link to a demo sheet: docs.google.com/spreadsheets/d/… Jun 29 '19 at 0:20
  • For more clarity, it actually seems better to just cull by desired month, which when combined with sorting and limiting will get the final day of month with data: =QUERY(GOOGLEFINANCE("GOOG","Close",A1-5,6),"select Col2 where month(Col1) = month(date '"&TEXT(A1, "YYYY-MM-DD")&"') order by Col1 desc limit 1",False) Jun 29 '19 at 16:51
  • Not sure about the last comment, but you are definitely right about the formula throwing the wrong date result for some cases. This is because the date column that googlefinance returns is a timestamp that has 4pm, which is "larger" than the input date (which is 00:00am). I made an adjustment that solves for this.
    – twalbaum
    Jul 1 '19 at 19:03
3

=GOOGLEFINANCE("GOOG","CloseYest")

2
  • 1
    You asked, "Is there another way to obtain the price for the last open market date?," and 'closeyest' does exactly that.
    – RDM
    Nov 13 '18 at 19:40
  • 1
    "CloseYest" only applies to mutual funds (not stocks) and in any case doesn't give the historical closing price if the date passed to the function is a weekend or holiday.
    – JimTheFrog
    Feb 26 '20 at 20:30
0
=index(googlefinance("GOOG","price",A1-5, 5),countA(index(googlefinance("GOOG","price",A1-5, 5),$B:$B,2)),2)

where A1 is the date

This takes the previous 5 days and gets the last result returned in the array result by counting the results returned. Some markets have up to 4 days closed in a row so you can still return a result with 5.

5
  • What is in column B? Could you share a link to a google sheet showing the correct result?
    – twalbaum
    Feb 19 '19 at 4:50
  • Column B is referencing within the returned array result, not the sheet itself.
    – PS0
    Feb 24 '19 at 10:30
  • This fails for most cases. It would work if you change the COUNTA for a COUNTIF and make the condition $A:$A <= A1
    – twalbaum
    Mar 3 '19 at 7:08
  • More than a year later, I'd like refresh this thread. First of all, the original expression using COUNTA seems to work with no issues at all, so I don't know what poster PS0 meant by 'this fails for most cases'; why would it? However, if you do replace COUNTA with COUNTIF, it is not clear to me in this complex expression where exactly should the inequality $A:$A<=A1 be inserted. The standard syntax for COUNTIF is COUNTIF(A1:A10,">20"), so in =index(googlefinance(A1,"price",today()-5, 5),COUNTA(index(googlefinance(A1,"price",today()-5, 5),B:B,2)),2) it should replace 2, but that fails... Apr 21 '20 at 16:31
  • The correct answer for date 1/1/2015 is 524.96, which is the close price on 12/31/2014. This expression returns 523.37 which is the close price on 01/02/2015. It will fail for any date that had 2 or more days with the market closed in the previous 5 days counting the date in question. So it only works for Friday or Saturday of a week with no market holidays.
    – twalbaum
    Mar 23 at 16:56
0

Created an index column T5-T29 1-25.

In U5 enter =GOOGLEFINANCE("GOOG","price",-1,today(),1)

then check for blank and return index of last close.

In U31 enter =if(ISBLANK(U29), if(ISBLANK(U28),if(ISBLANK(U27),if(ISBLANK(U26),if(ISBLANK(U25),if(isblank(U24),if(isblank(U23),T22,T23),T24),T25),T26),T27),T28),T29)

This is then referenced by below to show last close.

=index(GOOGLEFINANCE(A2,"price",R$2,today(),1),U$31,2) 

Fast and inelegant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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