Stock market rolling period returns

[mdelzi23]

Hello,
I am trying to calculate stock market returns over different periods. I have downloaded the daily index values from yahoo finance for the S&P 500 and the Russell 2000 index. I would like to start with the 1 year, 3 year and 6 year rolling periods. This is difficult because the data excludes the weekends and holidays which is difficult to calculate 365 days. When I enter a starting period the ending date some times falls on a weekend or holiday. I have used the vlookup function to randomly pick starting dates and find its corresponding index value, but the rolling period often ends on a weekend or holiday where there is no corresponding date or value. I would like to create a lookup that allows the ending date to move out to the next trading day in the future and find the corresponding index value.

This is the formula I have been using =WORKDAY(EDATE(C9,12)-1,0,'S&P Daily '!J2:J7288)
12 is the 12 month rolling period from a specific date. I get a "#N/A" when the end date lands on a weekend, holiday or the data is missing.

Any suggestion would be very helpful.

Thanks

 

[StephenCrump]

Perhaps something like this:

B5: =HLOOKUP(B3,F2:H4,2,)
B6: =HLOOKUP(B3,F2:H4,3,)
A9: =CalcDate
A10: =EDATE(A9,-12*Period) Copy down
B9: =IFERROR(VLOOKUP(A9,$E$8:$F$15,2,TRUE)/VLOOKUP(EDATE(A9,-12*Period),$E$8:$F$15,2,TRUE)-1,"") Copy down
C9: =IFERROR(MEDIAN(Cap,B9,MIN(B9-Offset,0)),"") Copy down
B16: {=GEOMEAN(IFERROR(1+B10:B15,""))^(1/Period)-1} Array-entered, copy to C16

	A	B	C	D	E	F	G	H
1	Dashboard				Parameters
2	CalcDate	31 Dec 2015			Term	1	3	6
3	Period	3			Cap	10%	17%	43%
4					Offset	-10%	-15%	-25%
5	Cap	17%
6	Offset	-15%
7
8	Results:				S&P Data
9	Returns	Raw	Adjusted		Date	Close
10	31 Dec 2015	43.31%	17.00%		31 Dec 2008	903.25
11	31 Dec 2012	27.90%	17.00%		31 Dec 2009	1,115.10
12	31 Dec 2009				31 Dec 2010	1,257.64
13	31 Dec 2006				30 Dec 2011	1,257.60
14	31 Dec 2003				31 Dec 2012	1,426.19
15	31 Dec 2000				31 Dec 2013	1,848.36
16					31 Dec 2014	2,058.90
17	Average (% pa)	10.63%	5.37%		31 Dec 2015	2,043.94

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​