2

How can I fetch current weekly closing price of a stock in google sheet? I have tried using the formula GOOGLEFINANCE("GOOG", "price", TODAY(), TODAY(), "WEEKLY"). But its showing no results.

Ameerudheen.K
  • 657
  • 1
  • 11
  • 31

1 Answers1

4

=GOOGLEFINANCE("NASDAQ:GOOGL", "close",TODAY()-60,TODAY(),"WEEKLY")

You can adjust the TODAY()-60 to a start date DATE(2019,1,1) for example:

=GOOGLEFINANCE("NASDAQ:GOOGL", "close",DATE(2019,1,1),TODAY(),"WEEKLY")

or how you'd like to do it. It might update the current week in real time if you use "price" instead of "close", not sure since it's the weekend, and so nothing is ticking or updating right now.

Since it appears you just want a single result of the most recent weeks closing price, try this:

=INDEX(GOOGLEFINANCE("NASDAQ:GOOGL","price",TODAY()-14,TODAY(),"WEEKLY"),3,2)

Improved the integer for the one above, but it could still theoretically break.

Try this instead, as it gives a 30 day range but only selects the result from within 1 week:

=INDEX(QUERY(GOOGLEFINANCE("NASDAQ:GOOGL","price",TODAY()-30,TODAY(),"WEEKLY"),"select Col2 where Col1 < date'"&TEXT(TODAY(),"yyyy-mm-dd")&"' and Col1 > date'"&TEXT(TODAY()-7,"yyyy-mm-dd")&"' limit 1"),2)

Hope that (finally) helps!

thecraigsea
  • 98
  • 1
  • 7
  • I have tried this but its result is multiple cell results so i cant append to only one cell – Ameerudheen.K Oct 12 '19 at 13:56
  • Hi - I've edited the answer above to include another formula. I didn't understand your question at first, but if you just want the most recent weekly closing stock price: `=INDEX(GOOGLEFINANCE("NASDAQ:GOOGL","price",TODAY()-8,TODAY(),"WEEKLY"),3,2) ` will put it in a single cell. – thecraigsea Oct 12 '19 at 14:18
  • It works bro but here on index part can i specify the latest row and coloumn and index? becuse for some stock there are only one row result and for other there are two. so how to take last row and coloumn? – Ameerudheen.K Oct 13 '19 at 05:59
  • Are you familiar with appscript? – Ameerudheen.K Oct 13 '19 at 13:45
  • Hmm. Some stocks do behave differently. Adding to answer: A quick fix would be to increase the integer `TODAY()-8` to `TODAY()-14` that would give you two weeks to index against, but it could still theoretically break if a stock returns one or two values. If we give it a 30 day range, and then always select the value within 1 week, this should give you the result you want. Try: `=INDEX(QUERY(GOOGLEFINANCE("NASDAQ:GOOGL","price",TODAY()-30,TODAY(),"WEEKLY"),"select Col2 where Col1 < date'"&TEXT(TODAY(),"yyyy-mm-dd")&"' and Col1 > date'"&TEXT(TODAY()-7,"yyyy-mm-dd")&"' limit 1"),2)` – thecraigsea Oct 13 '19 at 14:19
  • 1
    I don't know appscript very well. Just enough to tinker with. Sorry I was in the middle of editing the comment and it got locked, so I had to correct/re-enter it. But I think the solution above works without getting into appscript. – thecraigsea Oct 13 '19 at 14:26