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
Dashboard	Parameters

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]CalcDate[/TD]
[TD="bgcolor: #B7DEE8, align: center"]31 Dec 2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Term[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Period[/TD]
[TD="bgcolor: #B7DEE8, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Cap[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]17%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]43%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Offset[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-10%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-15%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-25%[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Cap[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Offset[/TD]
[TD="align: right"]-15%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Results:[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]S&P Data[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Returns[/TD]
[TD="align: right"]Raw[/TD]
[TD="align: right"]Adjusted[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Date[/TD]
[TD="align: right"]Close[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]31 Dec 2015[/TD]
[TD="align: right"]43.31%[/TD]
[TD="align: right"]17.00%[/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2008[/TD]
[TD="align: right"]903.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]31 Dec 2012[/TD]
[TD="align: right"]27.90%[/TD]
[TD="align: right"]17.00%[/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2009[/TD]
[TD="align: right"]1,115.10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]31 Dec 2009[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2010[/TD]
[TD="align: right"]1,257.64[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]31 Dec 2006[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]30 Dec 2011[/TD]
[TD="align: right"]1,257.60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]31 Dec 2003[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2012[/TD]
[TD="align: right"]1,426.19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]31 Dec 2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2013[/TD]
[TD="align: right"]1,848.36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2014[/TD]
[TD="align: right"]2,058.90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]Average (% pa)[/TD]
[TD="align: right"]10.63%[/TD]
[TD="align: right"]5.37%[/TD]
[TD="align: right"][/TD]
[TD="align: center"]31 Dec 2015[/TD]
[TD="align: right"]2,043.94[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​