Excel forms for Stocks- How to Track Dow Jones (DJIA) Patterns Like Up, Down, Up Up, Down Down

[rebel123]

I uploaded the file here- https://file.io/Vr7mDPckVRSI

PART 1


In the beginning, I need at least the formula to figure out UP down.... But then I need an idea of probability also.

PART 2

I will consider (or you can consider) editing any columns or adding or deleting columns.

The whole idea of what I am trying to figure out here is probability. And get answers.
This is the Dow Jones stock market results every day for the last 5 years.
So, I am not even sure if my extra columns are correct.

I want to answer questions, such as follows:
1. If the Dow Jones goes up 1 day what are the odds it goes down the next day? (this assumes that the previous day it went down. Otherwise the pattern went up to or more days)\
So for example, if the pattern was
Up Down Up Down Up Down Up Down Up Down Up Down
Then the answer should pretty much be 100% because it's going up and down everyday. I understand the first one in bold might not be an answer, since it's the first set in the pattern.

So for example, if the pattern was

Up Up Down Up Down Down Down Up
The number of occurrences would be

Up Down- Happened Once
Down Up- Happened Once

So I have no idea to calculate real probability on this king of pattern either.


2. If the Dow Jones goes down 1 day what are the odds it goes up the next day? (this assumes that the previous day it went up. Otherwise the pattern went down to or more days)

I attached an image

 

[StephenCrump]

Not exhaustively tested, but I think this works:

	A	B	C	D	E
1
2		Up		N (No of Ups)	1
3		Down		Freq (Down at N+1)	1
4		Down		Freq (Up >=N)	2
5		Down		Pr. Down after 1 Ups	50%
6		Down
7		Down
8		Up
9		Down
10		Down
11		Down
12		Down
13		Up
14		Up
15		Down
16		Down
17		Down
18		Down
19		Down
20		Down
21		Down
22		Up
Sheet3

Cell Formulas
Range	Formula
E3	E3	=SUM(--(N=FREQUENCY(IF(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,MATCH(2,1/(Moves="Down")))="Up",ROW(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,MATCH(2,1/(Moves="Down"))))),IF(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,MATCH(2,1/(Moves="Down")))="Down",ROW(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,MATCH(2,1/(Moves="Down"))))))))
E4	E4	=E3+SUM(--(N+1<=FREQUENCY(IF(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,ROWS(Moves))="Up",ROW(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,ROWS(Moves)))),IF(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,ROWS(Moves))="Down",ROW(INDEX(Moves,MATCH("Down",Moves,)):INDEX(Moves,ROWS(Moves)))))))
D5	D5	="Pr. Down after "&N&" Ups"
E5	E5	=IFERROR(E3/E4,"-")

Named Ranges
Name	Refers To	Cells
Moves	=Sheet3!$B$2:$B$22	E3:E4
N	=Sheet3!$E$2	D5, E3:E4

	Up		N (No of Ups)	2
	Up		Freq (Down at N+1)	1
	Up		Freq (Up >=N)	2
	Down		Pr. Down after 2 Ups	50%
	Down
	Down
	Up
	Down
	Down
	Down
	Down
	Up
	Up
	Down
	Down
	Down
	Up
	Up
	Up
	Down
	Up

	Up		N (No of Ups)	4
	Down		Freq (Down at N+1)	2
	Up		Freq (Up >=N)	3
	Up		Pr. Down after 4 Ups	67%
	Up
	Up
	Up
	Down
	Up
	Up
	Up
	Up
	Down
	Up
	Down
	Down
	Up
	Up
	Up
	Up
	Down

 

[rebel123]

OK thanks man let me test it now.... I may need help even inputting this... lol

 

[rebel123]

Here is what I redid...

 

[rebel123]

So, I re-uploaded the file here https://file.io/14USg7SayERy

As mentioned, you were probably correct and it's 100% my fault. I figured out how to do the down ups.
So, I would just need 4 formulas that would start in cells J2 K2 L2 and M2.

 

[StephenCrump]

I didn't look at your attachment before, but now neither of your links appears to be working.

Attached is an expanded version of my workbook, set up to accommodate Down or Up runs of variable length N: Frequency Up Down.xlsx

I have also conditionally formatted to illustrate/check the counts.

	B	C	D	E	F	G
1	Move	Helper		Input
2	Up	FALSE		Direction of run	Down
3	Down	FALSE		N (No of Downs required)	2
4	Up	FALSE
5	Down	FALSE		Calculations		Check
6	Up	FALSE		Reverse direction	Up
7	Up	FALSE		Freq (Up at 3)	32	32
8	Down	FALSE		Freq (Down at 2+)	65	65
9	Up	FALSE		Prob (Up after 2 Downs)	49%
10	Down	FALSE
11	Down	FALSE
12	Down	TRUE
13	Down	FALSE
14	Down	FALSE
15	Down	FALSE
16	Down	FALSE
17	Up	FALSE
18	Down	FALSE
19	Down	FALSE
20	Up	TRUE
21	Down	FALSE
22	Down	FALSE
23	Down	TRUE
24	Up	FALSE
25	Up	FALSE
26	Up	FALSE
27	Down	FALSE
28	Up	FALSE
29	Down	FALSE
30	Up	FALSE
31	Down	FALSE
32	Down	FALSE
33	Down	TRUE
34	Down	FALSE
35	Up	FALSE
36	Up	FALSE
37	Down	FALSE
38	Down	FALSE
39	Down	TRUE
40	Up	FALSE
41	Down	FALSE
42	Down	FALSE
43	Up	TRUE
44	Down	FALSE
Sheet3

Cell Formulas
Range	Formula
E3	E3	="N (No of " & Direction & "s required)"
F6	F6	=IF(Direction="Up","Down","Up")
E7	E7	="Freq (" & Reverse & " at " &N+1 &")"
F7	F7	=SUM(--(N=FREQUENCY(IF(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=Reverse)))=Direction,ROW(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=Reverse))))),IF(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=Reverse)))=Reverse,ROW(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=Reverse))))))))
G7	G7	=COUNTIFS(Helper,TRUE,Moves,Reverse)
E8	E8	="Freq (" & Direction & " at " & N &"+)"
F8	F8	=F7+SUM(--(N+1<=FREQUENCY(IF(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,ROWS(Moves))=Direction,ROW(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,ROWS(Moves)))),IF(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,ROWS(Moves))=Reverse,ROW(INDEX(Moves,MATCH(Reverse,Moves,)):INDEX(Moves,ROWS(Moves)))))))
G8	G8	=COUNTIF(Helper,TRUE)
E9	E9	="Prob (" & Reverse &" after "&N& " "& Direction & "s)"
F9	F9	=IFERROR(F7/F8,"-")
C2:C44	C2	=IFERROR(AND(OFFSET(B2,-N-1,)=Reverse,AND(OFFSET(B2,-1,,-N)=Direction)),FALSE)

Named Ranges
Name	Refers To	Cells
Direction	=Sheet3!$F$2	C2:C44, E3, F6:F8, E8:E9
Helper	=Sheet3!$C$2:$C$501	G7:G8
Moves	=Sheet3!$B$2:$B$501	C2, G7, F7:F8
N	=Sheet3!$F$3	C2:C44, E7:E9, F7:F8
Reverse	=Sheet3!$F$6	C2:C44, G7, E9, E7, F7:F8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B501	Expression	=AND(C2,B2=Reverse)	text	NO
B2:B501	Expression	=AND(C2,B2=Direction)	text	NO
B2:B501	Expression	=AND(B2=Direction,ISNUMBER(MATCH(TRUE,OFFSET(C2,1,,N),)))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F2	List	Up,Down

 

[StephenCrump]

So far we've considered only two possibilities, Up or Down.

We can adjust to accommodate Same, i.e. no movement. But it depends on how you want to treat these.

For example, is Down|Up|Up|Same|Up|Up
- A run of five ups?
- Two runs of two Ups?
- Neither?

 

[rebel123]

This stuff is way over my head... I re-uploaded the file here SafeNote | Encrypt and send files and notes with a link that automatically destruct after being read.

So if you could please either send me the link to the file OR simply just let me know the results in terms of a
percentage.


My main questions I want to answer, are all percentage questions.

1. What percentage moved down up?
2. What percentage moved up down?
3. What percentage moved up up?
4. What percentage moved down down?

 

[rebel123]

IF the link isnt working i separated it...


https :// safeno te.co/open?reference=60194a5ebe0333@55242225

 

[StephenCrump]

Here's a simple illustration, using the workbook I attached to Post #6, and with a smaller data sample. If I'm interpreting you correctly, your probabilities of Up Up, and Up Down are two sides of the same coin, as illustrated with the green/red colouring?

	Move	Helper		Input
	Up	FALSE		Direction of run	Up
	Down	FALSE		N (No of Ups required)	1
	Up	FALSE
	Down	TRUE		Calculations		Check
	Up	FALSE		Reverse direction	Down
	Up	TRUE		Freq (Down at 2)	6	6
	Down	FALSE		Freq (Up at 1+)	9	9
	Up	FALSE		Prob (Down after 1 Ups)	67%
	Down	TRUE
	Down	FALSE		Your probabilities
	Down	FALSE		Up Up	33%
	Down	FALSE		Up Down	67%
	Down	FALSE
	Down	FALSE
	Down	FALSE
	Up	FALSE
	Down	TRUE
	Down	FALSE
	Up	FALSE
	Down	TRUE
	Down	FALSE
	Down	FALSE
	Up	FALSE
	Up	TRUE
	Up	FALSE
	Down	FALSE
	Up	FALSE
	Down	TRUE
	Up	FALSE
	Down	TRUE
	Down	FALSE
	Down	FALSE
	Down	FALSE
	Up	FALSE
	Up	TRUE
	Down	FALSE

If that's correct, then using your workbook, the probabilities look like this (I have also extrapolated to show runs of 2. You can extend further if you wish):

My workbook: DJIA movements up down.xlsx

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Open	High	Low	Close	Adj Close	Volume	Up Or Down	Daily Movement		Input
2	2016-02-01	16,454	16,511	16,299	16,449	16,449	1144500	-4.45	Down		Direction of run	Up	Down	Up	Down
3	2016-02-02	16,420	16,420	16,108	16,154	16,154	1262100	-266.67	Down		Minimum length of run required (N)	1	1	2	2
4	2016-02-03	16,186	16,382	15,960	16,337	16,337	1418700	150.46	Up		Calculations
5	2016-02-04	16,330	16,486	16,266	16,417	16,417	1314900	86.91	Up		Reverse direction	Down	Up	Down	Up
6	2016-02-05	16,418	16,424	16,130	16,205	16,205	1390100	-212.98	Down		Freq (run reverses at N+1)	156	175	72	81
7	2016-02-08	16,148	16,148	15,804	16,027	16,027	1658800	-120.46	Down		Freq (run of length N or more))	316	315	160	140
8	2016-02-09	16,005	16,137	15,881	16,014	16,014	1277400	8.97	Up		Prob (reverse after run of N)	49%	56%	45%	58%
9	2016-02-10	16,036	16,202	15,900	15,915	15,915	1222900	-120.87	Down
10	2016-02-11	15,898	15,898	15,503	15,660	15,660	1720700	-237.64	Down		Your probabilities	UpUp	DownDown	UpUpUp	DownDownDown
11	2016-02-12	15,692	15,974	15,692	15,974	15,974	1325500	282.22	Up			50.6%	44.4%	55.0%	42.1%
12	2016-02-16	16,012	16,196	16,012	16,196	16,196	1420300	184.02	Up			UpDown	DownUp	UpUpDown	DownDownUp
13	2016-02-17	16,218	16,486	16,218	16,454	16,454	1240800	235.85	Up			49.4%	55.6%	45.0%	57.9%
14	2016-02-18	16,484	16,512	16,390	16,413	16,413	1049500	-70.33	Down
DJIA 5 History

Cell Formulas
Range	Formula
L5:O5	L5	=IF(L2="Up","Down","Up")
L6:O6	L6	=SUM(--(L3=FREQUENCY(IF(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=L5)))=L2,ROW(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=L5))))),IF(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=L5)))=L5,ROW(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,MATCH(2,1/(Moves=L5))))))))
L7:O7	L7	=L6+SUM(--(L3+1<=FREQUENCY(IF(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,ROWS(Moves))=L2,ROW(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,ROWS(Moves)))),IF(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,ROWS(Moves))=L5,ROW(INDEX(Moves,MATCH(L5,Moves,)):INDEX(Moves,ROWS(Moves)))))))
L8:O8	L8	=IFERROR(L6/L7,"-")
L10:O10	L10	=REPT(L2,L3+1)
L11:O11	L11	=1-L8
L12:O12	L12	=REPT(L2,L3)&L5
L13:O13	L13	=L8
H2:H14	H2	=E2-B2

Named Ranges
Name	Refers To	Cells
Moves	='DJIA 5 History'!$I$2:$I$1260	L6:O7

Cells with Data Validation
Cell	Allow	Criteria
L2:O2	List	Up,Down