0

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.

  • 1
    NSE: BIOCON for example... – Leo Aug 21 '17 at 3:56
1

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.

0

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.

  • Tanks. I tried but it still throws the same error for NSE:BIOCON. – Leo Aug 21 '17 at 4:48
  • I just copied the formula to a new sheet and yes it does work. The original sheet contains > 1000 google finance formulas and probably Google limits it in some way. – Leo Aug 21 '17 at 7:31
  • Also throws the same error with GOOG – twalbaum Feb 19 at 5:36
0

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.

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.