0

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?

  • 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

=GOOGLEFINANCE("GOOG","CloseYest")

  • 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

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.

  • 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
  • 1
    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 Jun 29 at 0:14
  • To follow up above, I have a link to a demo sheet: docs.google.com/spreadsheets/d/… – Samuel Horwitz Jun 29 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 Jun 29 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 at 19:03
-1
=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.

  • What is in column B? Could you share a link to a google sheet showing the correct result? – twalbaum Feb 19 at 4:50
  • Column B is referencing within the returned array result, not the sheet itself. – PS0 Feb 24 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 at 7:08

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.