Below formula returns Annual Return for the year 2016
=INDEX(GOOGLEFINANCE("GOOG","price",date(2016,12,31)),2,2)/INDEX(GOOGLEFINANCE("GOOG","price",date(2016,1,1)),2,2)-1
This works fine for most stocks including GOOG, however it returns the below error for some stocks -
Error Function INDEX parameter 2 value is 2. Valid values are between 0 and 1 inclusive.
Any guidance will be helpful and much appreciated.
The problem is with the date. 1/1 is New Year's Day, and the markets are closed, therefore the stock price on that day is returning #N/A, which doesn't have an index 2,2.
The issue is that if you use GOOGLEFINANCE with a date the market was closed the formula returns #N/A. In your example both 2016/12/31 and 2016/1/1 were dates when the market was closed.
The only way I've been able to solve it so far is by using GOOGLEFINANCE to return prices in a range around the specified date and then use QUERY to return the last one prior to the specified date like this:
=QUERY(GOOGLEFINANCE("GOOG","Close",DATE(2016,12,31)-5,6),"select Col2 where Col1 <= date '2015-01-01' order by Col1 desc limit 1",False)
So to get the annual return for GOOG on 2016, enter GOOG on cell G3 and 2016 in cell G2 and use:
=QUERY(GOOGLEFINANCE(G3,"Close",DATE(G2,12,31)-5,6),"select Col2 where Col1 <= date '"&TEXT(DATE(G2,12,31), "YYYY-MM-DD")&"' order by Col1 desc limit 1",False)/QUERY(GOOGLEFINANCE(G3,"Close",DATE(G2,1,1)-5,6),"select Col2 where Col1 < date '"&TEXT(DATE(G2,1,1), "YYYY-MM-DD")&"' order by Col1 desc limit 1",False)-1
See this google sheet for more clarity and also this other question here in Stack Exchange in case someone comes up with a better solution as this is remarkably long for something that should be simpler.
I think if you put GOOG into A1 and BIOCON into A2 then:
=INDEX(GOOGLEFINANCE(A1,"price",date(2016,12,31)),2,2)/INDEX(GOOGLEFINANCE(A1,"price",date(2016,1,1)),2,2)-1
somewhere and copy it down one row (in the same column) it should work.
