# Calculating annual return of stocks with Google finance data

Below formula returns Annual Return for the year 2016

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.

• NSE: BIOCON for example...
– Leo
Aug 21, 2017 at 3:56

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.

I think if you put GOOG into A1 and BIOCON into A2 then:

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, 2017 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, 2017 at 7:31
• Also throws the same error with GOOG Feb 19, 2019 at 5:36

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.