9

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?

Improve this question
3
  • 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
    Commented Mar 6, 2018 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
    Commented Jun 20, 2021 at 17:57
  • Sheets does provide closelast in the realtime data, simply the attribute "price". =googlefinance("GME"). But... that is not the same as the historical data requested in the question.
    – Douglas Held
    Commented Nov 6, 2022 at 13:29

7 Answers 7

Reset to default
8

=GOOGLEFINANCE("GOOG","CloseYest")

Improve this answer
4
  • 1
    You asked, "Is there another way to obtain the price for the last open market date?," and 'closeyest' does exactly that.
    – RDM
    Commented Nov 13, 2018 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
    Commented Feb 26, 2020 at 20:30
  • I confirmed that "CloseYest" currently works as expected for stock and ETF symbols. (Not just mutual funds.) This appears to be the best solution.
    – BlakeTNC
    Commented Oct 29, 2021 at 1:26
  • It's true googlefinance("GOOG", "closeyest") returns a value from the real-time API. But, it doesn't help with the historical close prices the question asks about, as it can't be used with a date parameter.
    – Douglas Held
    Commented Nov 6, 2022 at 16:08
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.

Improve this answer
7
  • I'm leaving the question open for a few days to see if there are other alternatives answers.
    – twalbaum
    Commented Mar 9, 2018 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!
    – Samuel Horwitz
    Commented Jun 29, 2019 at 0:14
  • To follow up above, I have a link to a demo sheet: docs.google.com/spreadsheets/d/…
    – Samuel Horwitz
    Commented Jun 29, 2019 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)
    – Samuel Horwitz
    Commented Jun 29, 2019 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
    Commented Jul 1, 2019 at 19:03
1

INDEX(SORT(GOOGLEFINANCE("GOOG", "close", TODAY()-5, 6,1), 1, FALSE), 2, 2)

This will show the list of results in reverse, allowing one to pick off the top of the list (last valid close).

Improve this answer
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.

Improve this answer
5
  • What is in column B? Could you share a link to a google sheet showing the correct result?
    – twalbaum
    Commented Feb 19, 2019 at 4:50
  • Column B is referencing within the returned array result, not the sheet itself.
    – PS0
    Commented Feb 24, 2019 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
    Commented Mar 3, 2019 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...
    – user1537723
    Commented Apr 21, 2020 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
    Commented Mar 23, 2021 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.

Improve this answer
0

I think the answer from @twalbaum is fishing toward the right idea, but it can be improved by understanding in a little more detail the Google Sheets GOOGLEFINANCE() API which, in my opinion, is a little confusing. With some parameter combinations, it gives a scalar result and with other parameter combinations, it gives a range result.

Carefully reading the API, there are three or four use cases based on the attribute 2nd parameter provided.

  • attribute missing: returns a scalar real-time result
  • real-time data: You get real-time attributes if you use an attribute value from the real-time set, including "price" and "priceopen".
  • historical data: You get historical attributes if you use an attribute value from the historical set, including "open" and "close".
  • mutual fund data: You get mutual fund attributes if you use an attribute from the mutual fund set, including "date" and "return152" among other oddities.

My answer will ignore the case of mutual fund data (as I haven't studied it, and the 2018 question is asking about GOOG which is not a mutual fund).

Now, the real-time API is where we get the last sold price, even if the market is closed. Incidentally, the date of the last sale can be displayed with googlefinance("GOOG", "tradetime").

Meanwhile, going to historical data... if we specify most non-trading days the value comes back (apparently) as the close for the prior Friday. See below:

Screenshot of Sheets example

except, and I'm assuming this is a Google implementation bug, the closing value doesn't carry forward if the function is called on a non-trading day (columns F and G in the example).

However we can pull a nearly* equivalent value, the weekly close, by using the technique described by @twalbaum. See the result in columns C and D.

So I'd propose the solution is to conditionally return the daily close price, and if that happens to be #N/A, then to return the weekly close price.

=IFNA( index( googlefinance( "GOOG", "close", $DATE_CELL ), 2, 2 ), INDEX( googlefinance( "GOOG", "close", $DATE_CELL - 7, 7, "weekly" ), 2, 2 ) )

* Note, between cells D6 and G6 etc., the values differ by a small amount. I assume that's another Google error.

Conclusion: It seems like pulling the historical data from Google may be an inexact science. I'd propose my solution as a slightly more straightforward solution than the accepted solution.

Formulas:

  • Column B: =TEXT( A14, "WWWW" )
  • Column C: =INDEX( googlefinance( $C$1, "close", A14-7,A14, "weekly" ), 2, 1 )
  • Column C: =INDEX( googlefinance( $C$1, "close", A14-7,A14, "weekly" ), 2, 2 )
  • Column E: =INDEX( googlefinance( $C$1, "close", A14), 2, 1 )
  • Column F: =INDEX( googlefinance( $C$1, "close", A14), 2, 2 )
Improve this answer
-1

The correct answer is to use the Workday() function as follows:

=Index(GOOGLEFINANCE("MSFT","price", Workday(DateValue("12/31/2023")+1,-1)),2,2)
Improve this answer

Your Answer

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

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