Stock Market Returns

[RG77]

I want to measure the stock returns (SP500) after "x" number of down days.
And i want to know how often these events occurs.

Example when the market goes 6 weeks in a row, the SP500 has been lower/higher 6 months later by what percentage. And its gone down 6 weeks in a row, 5 times in the last 10 years

 

[excelsishya]

Hi,

See if below works for you

Voltality tracker

A B C D E F
1 DATE s&p Reurn Helper column Event 4
2 w1 300 0.15415068 FALSE Reurn From latest Event 543%
3 w2 350 -0.15748958 TRUE Return From earlist Event -87%
4 w3 299 -0.00335009 FALSE
5 w4 298 -0.00336135 FALSE
6 w5 297 -0.00337268 FALSE
7 w6 296 -0.0033841 FALSE
8 w7 295 -0.00339559 FALSE
9 w8 294 0.15996465 FALSE
10 w9 345 -3.76410288 TRUE
11 w10 8 -0.13353139 TRUE
12 w11 7 -0.15415068 TRUE
13 w12 6 -0.18232156 FALSE
14 w13 5 -0.22314355 FALSE
15 w14 4 -0.28768207 FALSE
16 w15 3 -0.40546511 FALSE
17 w16 2 -0.69314718 FALSE
18 w17 1 3.80666249 FALSE
19 w18 45 FALSE

Spreadsheet Formulas
Cell Formula
F1 =SUM((SUMPRODUCT(--(D2:D19)*1)))
D2 =AND(C2<0,C3<0,C4<0,C5<0,C6<0,C7<0)
F2 =OFFSET(B1,COUNT(B2:B19),0,1,1)/LOOKUP(TRUE,$D$2:$D$19,B2:B19)-1
D3 =AND(C3<0,C4<0,C5<0,C6<0,C7<0,C8<0)
F3 =OFFSET(B1,COUNT(B2:B19),0,1,1)/INDEX(B2:B19,MATCH(TRUE,$D$2:$D$19,0))-1
D4 =AND(C4<0,C5<0,C6<0,C7<0,C8<0,C9<0)
D5 =AND(C5<0,C6<0,C7<0,C8<0,C9<0,C10<0)
D6 =AND(C6<0,C7<0,C8<0,C9<0,C10<0,C11<0)
D7 =AND(C7<0,C8<0,C9<0,C10<0,C11<0,C12<0)
D8 =AND(C8<0,C9<0,C10<0,C11<0,C12<0,C13<0)
D9 =AND(C9<0,C10<0,C11<0,C12<0,C13<0,C14<0)
D10 =AND(C10<0,C11<0,C12<0,C13<0,C14<0,C15<0)
D11 =AND(C11<0,C12<0,C13<0,C14<0,C15<0,C16<0)
D12 =AND(C12<0,C13<0,C14<0,C15<0,C16<0,C17<0)
D13 =AND(C13<0,C14<0,C15<0,C16<0,C17<0,C18<0)
D14 =AND(C14<0,C15<0,C16<0,C17<0,C18<0,C19<0)
D15 =AND(C15<0,C16<0,C17<0,C18<0,C19<0,C20<0)
D16 =AND(C16<0,C17<0,C18<0,C19<0,C20<0,C21<0)
D17 =AND(C17<0,C18<0,C19<0,C20<0,C21<0,C22<0)
D18 =AND(C18<0,C19<0,C20<0,C21<0,C22<0,C23<0)
D19 =AND(C19<0,C20<0,C21<0,C22<0,C23<0,C24<0)


Reurn From latest Event =OFFSET(B1,COUNT(B2:B19),0,1,1)/LOOKUP(TRUE,$D$2:$D$19,B2:B19)-1

Return From earlist Event =OFFSET(B1,COUNT(B2:B19),0,1,1)/INDEX(B2:B19,MATCH(TRUE,$D$2:$D$19,0))-1

EVENT==SUM((SUMPRODUCT(--(D2:D19)*1)))

cretria matcher(6 week down)=D2 =AND(C2<0,C3<0,C4<0,C5<0,C6<0,C7<0)

hope you can build on this basic setup.