Timeline for How to get close price for last date market was open?
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 30, 2021 at 2:57 | comment | added | twalbaum |
Good question. googlefinance does weird things sometimes, for example try the formula with -4,4) for date 1/2/15 and it returns only data for 12/29, 12/30 and 12/31, not 1/2 as expected. When you change the formula to -4,5), then 1/2 appears. That being said I can't find a case where -5,5) doesn't work. I guess it doesn't matter how long you go after date of interest, or do you see any issues with that?
|
|
| Sep 27, 2021 at 8:34 | comment | added | Christopher Oezbek |
What is the point of selecting dates after the date of interest? Why not use DATEVALUE("1/1/15")-5,5) rather than ,6)?
|
|
| Jul 1, 2019 at 19:06 | history | edited | twalbaum | CC BY-SA 4.0 |
Modified formula to correct issue that caused it to take the wrong date price.
|
| Jul 1, 2019 at 19:03 | comment | added | twalbaum | 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. | |
| Jun 29, 2019 at 16:51 | comment | added | Samuel Horwitz |
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, 2019 at 0:20 | comment | added | Samuel Horwitz | To follow up above, I have a link to a demo sheet: docs.google.com/spreadsheets/d/… | |
| Jun 29, 2019 at 0:14 | comment | added | Samuel Horwitz |
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!
|
|
| Feb 19, 2019 at 9:18 | vote | accept | twalbaum | ||
| Mar 9, 2018 at 0:33 | comment | added | twalbaum | I'm leaving the question open for a few days to see if there are other alternatives answers. | |
| Mar 7, 2018 at 5:24 | history | answered | twalbaum | CC BY-SA 3.0 |