Very Complex- How to Find Stock Market Probability of 3, 4 & 5 negative days (Find Results of Day 4,5&6)

[rebel123]

I am copying and pasting the stock history prices for Microsoft (MSFT Ticker).
I want to find out in the last 10 years (Since August 1, 2013) how do I find out the following probability?
However, I only copied 103 rows of data, so that it is easier to view.

I would please need an equals sign answer response in which I can drag that answer down the columns labeled
"Formula 1" and if your formula requires more columns, you can drag them over to "Formula 2" "Formula 3"

Here are the questions of probability needed.
1. On 3 negative days, what is the probability of day 4 being positive? Day 4 is measured by the "Open" Column
and the 3 days are obviously measured under the "Close" column.

2. On 4 negative days, what is the probability of day 4 being positive? Day 5 is measured by the "Open" Column
and the 4 days are obviously measured under the "Close" column.

3. On 5 negative days, what is the probability of day 6 being positive? Day 6 is measured by the "Open" Column
and the 5 days are obviously measured under the "Close" column.

4. Also, for all of the above answers, I am looking for an Average Gain. So, that formula could be copied into the
"Gain" Column.

For example, let's say that Microsoft had 6 results for 4 negative days in a row in which we are measuring day 5.
Let's say these were the results-

1
5
-3
4
-1.5
2.5

Then the average result equals 1.333333333


Here is the table. Please let me know if anything is not clear, but I explained this complex formula, fairly simple (in my opinion).
I could not copy the whole history between now and current (there was a limit on characters), but the formula could
be figured out nonetheless.

Date	Open	High	Low	Close	Volume	Daily Result	Formula 1	Formula 2	Formula 3	Gain
Aug 01, 2013	$32.06	$32.09	$31.60	$31.67	42,557,900
Aug 02, 2013	$31.69	$31.90	$31.57	$31.89	29,199,900	$0.22
Aug 05, 2013	$31.90	$32.00	$31.64	$31.70	30,984,000	-$0.19
Aug 06, 2013	$31.55	$31.67	$31.38	$31.58	36,331,500	-$0.12
Aug 07, 2013	$31.54	$32.10	$31.25	$32.06	38,078,600	$0.48
Aug 08, 2013	$32.24	$33.07	$32.05	$32.89	59,034,400	$0.83
Aug 09, 2013	$32.77	$32.90	$32.47	$32.70	26,800,700	-$0.19
Aug 12, 2013	$32.46	$32.97	$32.46	$32.87	25,493,700	$0.17
Aug 13, 2013	$32.51	$32.55	$32.21	$32.23	39,464,100	-$0.64
Aug 14, 2013	$32.14	$33.36	$31.70	$32.35	48,519,600	$0.12
Aug 15, 2013	$32.00	$32.18	$30.84	$31.79	33,338,000	-$0.56
Aug 16, 2013	$31.79	$31.99	$31.66	$31.80	32,866,300	$0.01
Aug 19, 2013	$31.76	$31.97	$31.38	$31.39	27,902,500	-$0.41
Aug 20, 2013	$31.44	$31.90	$31.37	$31.62	22,979,600	$0.23
Aug 21, 2013	$31.61	$32.01	$31.54	$31.61	37,409,100	-$0.01
Aug 22, 2013	$32.19	$32.49	$32.10	$32.39	31,169,900	$0.78
Aug 23, 2013	$35.17	$35.20	$34.00	$34.75	225,493,800	$2.36
Aug 26, 2013	$34.40	$34.67	$34.03	$34.15	72,786,800	-$0.60
Aug 27, 2013	$33.52	$34.10	$33.15	$33.26	58,522,300	-$0.89
Aug 28, 2013	$33.39	$33.60	$33.00	$33.02	44,257,400	-$0.24
Aug 29, 2013	$32.93	$33.60	$32.80	$33.55	45,284,700	$0.53
Aug 30, 2013	$33.37	$33.48	$33.09	$33.40	42,790,200	-$0.15
Sep 03, 2013	$31.75	$32.07	$31.29	$31.88	154,507,000	-$1.52
Sep 04, 2013	$31.39	$31.47	$31.11	$31.20	142,320,600	-$0.68
Sep 05, 2013	$31.10	$31.44	$30.95	$31.23	71,644,900	$0.03
Sep 06, 2013	$31.31	$31.39	$31.13	$31.15	75,434,900	-$0.08
Sep 09, 2013	$31.22	$31.79	$31.20	$31.66	49,628,500	$0.51
Sep 10, 2013	$31.90	$32.40	$31.79	$32.39	56,881,200	$0.73
Sep 11, 2013	$32.57	$32.93	$32.53	$32.74	39,087,500	$0.35
Sep 12, 2013	$32.72	$32.78	$32.59	$32.69	32,860,200	-$0.05
Sep 13, 2013	$32.77	$33.07	$32.51	$33.03	40,899,000	$0.34
Sep 16, 2013	$33.38	$33.50	$32.73	$32.80	52,839,700	-$0.23
Sep 17, 2013	$33.42	$33.47	$32.90	$32.93	84,716,500	$0.13
Sep 18, 2013	$32.99	$33.40	$32.83	$33.32	64,099,900	$0.39
Sep 19, 2013	$33.48	$33.68	$33.32	$33.64	42,026,600	$0.32
Sep 20, 2013	$33.41	$33.48	$32.69	$32.79	102,904,900	-$0.85
Sep 23, 2013	$32.54	$32.97	$32.50	$32.74	39,826,100	-$0.05
Sep 24, 2013	$32.87	$32.87	$32.15	$32.45	40,685,000	-$0.29
Sep 25, 2013	$32.49	$32.80	$32.40	$32.51	28,907,500	$0.06
Sep 26, 2013	$32.64	$33.00	$32.59	$32.77	28,504,000	$0.26
Sep 27, 2013	$32.88	$33.75	$32.87	$33.27	55,348,000	$0.50
Sep 30, 2013	$33.00	$33.31	$32.70	$33.28	39,839,500	$0.01
Oct 01, 2013	$33.35	$33.61	$33.30	$33.58	36,718,700	$0.30
Oct 02, 2013	$33.36	$34.03	$33.29	$33.92	46,946,800	$0.34
Oct 03, 2013	$33.88	$34.00	$33.42	$33.86	38,703,800	-$0.06
Oct 04, 2013	$33.69	$33.99	$33.62	$33.88	33,008,100	$0.02
Oct 07, 2013	$33.60	$33.71	$33.20	$33.30	35,069,300	-$0.58
Oct 08, 2013	$33.31	$33.33	$32.80	$33.01	41,017,600	-$0.29
Oct 09, 2013	$33.07	$33.35	$32.96	$33.07	35,878,600	$0.06
Oct 10, 2013	$33.31	$33.89	$33.26	$33.76	42,875,100	$0.69
Oct 11, 2013	$33.68	$34.14	$33.68	$34.13	30,033,300	$0.37
Oct 14, 2013	$33.90	$34.50	$33.78	$34.45	27,757,900	$0.32
Oct 15, 2013	$34.67	$34.99	$34.47	$34.49	47,097,800	$0.04
Oct 16, 2013	$34.60	$34.90	$34.56	$34.64	35,111,600	$0.15
Oct 17, 2013	$34.45	$34.99	$34.37	$34.92	31,359,200	$0.28
Oct 18, 2013	$34.82	$34.99	$34.33	$34.96	41,811,700	$0.04
Oct 21, 2013	$34.98	$35.20	$34.91	$34.99	27,433,500	$0.03
Oct 22, 2013	$35.02	$35.10	$34.52	$34.58	40,438,500	-$0.41
Oct 23, 2013	$34.35	$34.49	$33.67	$33.76	58,600,500	-$0.82
Oct 24, 2013	$33.82	$34.10	$33.57	$33.72	53,209,700	-$0.04
Oct 25, 2013	$35.88	$36.29	$35.47	$35.73	113,494,000	$2.01
Oct 28, 2013	$35.61	$35.73	$35.27	$35.57	38,383,600	-$0.16
Oct 29, 2013	$35.63	$35.72	$35.26	$35.52	31,702,200	-$0.05
Oct 30, 2013	$35.53	$35.79	$35.43	$35.54	36,997,700	$0.02
Oct 31, 2013	$35.66	$35.69	$35.34	$35.41	41,682,300	-$0.13
Nov 01, 2013	$35.67	$35.69	$35.39	$35.53	40,264,600	$0.12
Nov 04, 2013	$35.59	$35.98	$35.55	$35.94	28,060,700	$0.41
Nov 05, 2013	$35.79	$36.71	$35.77	$36.64	51,681,900	$0.70
Nov 06, 2013	$37.24	$38.22	$37.06	$38.18	88,948,800	$1.54
Nov 07, 2013	$37.96	$38.01	$37.43	$37.50	60,437,400	-$0.68
Nov 08, 2013	$37.67	$37.78	$37.34	$37.78	36,737,800	$0.28
Nov 11, 2013	$37.69	$37.78	$37.35	$37.59	26,872,500	-$0.19
Nov 12, 2013	$37.38	$37.60	$37.20	$37.36	31,651,600	-$0.23
Nov 13, 2013	$36.98	$38.16	$36.90	$38.16	44,957,600	$0.80
Nov 14, 2013	$37.87	$38.13	$37.72	$38.02	46,183,700	-$0.14
Nov 15, 2013	$37.95	$38.02	$37.72	$37.84	50,601,300	-$0.18
Nov 18, 2013	$37.35	$37.58	$37.07	$37.20	53,277,500	-$0.64
Nov 19, 2013	$36.85	$37.23	$36.67	$36.74	44,275,000	-$0.46
Nov 20, 2013	$36.92	$37.41	$36.86	$37.08	32,229,900	$0.34
Nov 21, 2013	$37.27	$37.53	$37.26	$37.40	23,064,700	$0.32
Nov 22, 2013	$37.53	$37.68	$37.33	$37.57	27,982,000	$0.17
Nov 25, 2013	$37.93	$37.95	$37.57	$37.64	30,646,800	$0.07
Nov 26, 2013	$37.57	$37.65	$37.35	$37.35	34,465,300	-$0.29
Nov 27, 2013	$37.57	$37.76	$37.49	$37.60	26,002,100	$0.25
Nov 29, 2013	$37.82	$38.29	$37.82	$38.13	22,090,400	$0.53
Dec 02, 2013	$38.09	$38.78	$38.06	$38.45	42,950,400	$0.32
Dec 03, 2013	$38.14	$38.49	$38.08	$38.31	52,109,800	-$0.14
Dec 04, 2013	$38.21	$38.98	$38.12	$38.94	51,983,600	$0.63
Dec 05, 2013	$38.85	$38.88	$37.18	$38.00	116,305,000	-$0.94
Dec 06, 2013	$38.42	$38.55	$37.99	$38.36	36,457,300	$0.36
Dec 09, 2013	$38.56	$38.87	$38.37	$38.71	30,286,000	$0.35
Dec 10, 2013	$38.61	$38.90	$38.02	$38.11	37,828,600	-$0.60
Dec 11, 2013	$38.06	$38.30	$37.39	$37.61	39,853,400	-$0.50
Dec 12, 2013	$37.64	$37.64	$37.18	$37.22	36,012,800	-$0.39
Dec 13, 2013	$37.42	$37.45	$36.62	$36.69	40,066,100	-$0.53
Dec 16, 2013	$36.73	$37.00	$36.54	$36.89	31,734,200	$0.20
Dec 17, 2013	$36.94	$37.11	$36.33	$36.52	45,687,700	-$0.37
Dec 18, 2013	$36.36	$36.60	$35.53	$36.58	63,192,100	$0.06
Dec 19, 2013	$36.51	$36.55	$36.08	$36.25	34,160,100	-$0.33
Dec 20, 2013	$36.20	$36.93	$36.19	$36.80	62,649,100	$0.55
Dec 23, 2013	$36.81	$36.89	$36.55	$36.62	25,128,700	-$0.18
Dec 24, 2013	$36.72	$37.17	$36.64	$37.08	14,243,000	$0.46
Dec 26, 2013	$37.20	$37.49	$37.17	$37.44	17,612,800	$0.36

 

[offthelip]

You can do this very easly by using two columns, which then be expanded to answer your other questions. this is the solution for three days
Use column H to detect 3 down days with this equation:

=AND(G4<0,G3<0,G2<0)

then use column I to detect whether the 4th days is up with this equation

=AND(H3,G4>0)

the at the top of each column you can put this in H2

=COUNTIF(H4:H104,TRUE)

and in I2 this equation

=COUNTIF(I4:I104,TRUE)

the probabilty is then I2 divided by H2
If you want the gain you can do this by adding another column which records the gain for the day after 3 down days wit this equation:

=IF(H4,G5,"")

I have found you can do quite complicated stock market calculations quite simply by using multiple columns. You can obviously put the "helper columns out of sight to the right if you want.
You can then use average or sum at the top of the column to find average gain or total gain. Other values such as standard deviation , maximum loss etc can also be easily calculated

 

[rebel123]

You can do this very easly by using two columns, which then be expanded to answer your other questions. this is the solution for three days
Use column H to detect 3 down days with this equation:

=AND(G4<0,G3<0,G2<0)

then use column I to detect whether the 4th days is up with this equation

=AND(H3,G4>0)

the at the top of each column you can put this in H2

=COUNTIF(H4:H104,TRUE)

and in I2 this equation

=COUNTIF(I4:I104,TRUE)

the probabilty is then I2 divided by H2
If you want the gain you can do this by adding another column which records the gain for the day after 3 down days wit this equation:

=IF(H4,G5,"")

I have found you can do quite complicated stock market calculations quite simply by using multiple columns. You can obviously put the "helper columns out of sight to the right if you want.
You can then use average or sum at the top of the column to find average gain or total gain. Other values such as standard deviation , maximum loss etc can also be easily calculated

is there anyway you could respond with the actual file? I am not sure where you are copying these into?

OR.... If you copy and paste the table back on here, does this copy and paste know the formula if I then
copy that into MS Excel? As mentioned in my profile, I have MAC MS Excel.

 

[offthelip]

try this,

stock stuff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Open	High	Low	Close	Volume	Daily Result	Three down days	Formula 2	Formula 3	Gain
2	Aug 01, 2013	32.06	32.09	31.6	31.67	42,557,900		9	4		-0.05
3	Aug 02, 2013	31.69	31.9	31.57	31.89	29,199,900	0.2
4	Aug 05, 2013	31.9	32	31.64	31.7	30,984,000	-0.2	FALSE	FALSE
5	Aug 06, 2013	31.55	31.67	31.38	31.58	36,331,500	0.03	FALSE	FALSE
6	Aug 07, 2013	31.54	32.1	31.25	32.06	38,078,600	0.52	FALSE	FALSE
7	Aug 08, 2013	32.24	33.07	32.05	32.89	59,034,400	0.65	FALSE	FALSE
8	Aug 09, 2013	32.77	32.9	32.47	32.7	26,800,700	-0.07	FALSE	FALSE
9	Aug 12, 2013	32.46	32.97	32.46	32.87	25,493,700	0.41	FALSE	FALSE
10	Aug 13, 2013	32.51	32.55	32.21	32.23	39,464,100	-0.28	FALSE	FALSE
11	Aug 14, 2013	32.14	33.36	31.7	32.35	48,519,600	0.21	FALSE	FALSE
12	Aug 15, 2013	32	32.18	30.84	31.79	33,338,000	-0.21	FALSE	FALSE
13	Aug 16, 2013	31.79	31.99	31.66	31.8	32,866,300	0.01	FALSE	FALSE
14	Aug 19, 2013	31.76	31.97	31.38	31.39	27,902,500	-0.37	FALSE	FALSE
15	Aug 20, 2013	31.44	31.9	31.37	31.62	22,979,600	0.18	FALSE	FALSE
16	Aug 21, 2013	31.61	32.01	31.54	31.61	37,409,100	0	FALSE	FALSE
17	Aug 22, 2013	32.19	32.49	32.1	32.39	31,169,900	0.2	FALSE	FALSE
18	Aug 23, 2013	35.17	35.2	34	34.75	225,493,800	-0.42	FALSE	FALSE
19	Aug 26, 2013	34.4	34.67	34.03	34.15	72,786,800	-0.25	FALSE	FALSE
20	Aug 27, 2013	33.52	34.1	33.15	33.26	58,522,300	-0.26	TRUE	FALSE		-0.37
21	Aug 28, 2013	33.39	33.6	33	33.02	44,257,400	-0.37	TRUE	FALSE		0.62
22	Aug 29, 2013	32.93	33.6	32.8	33.55	45,284,700	0.62	FALSE	TRUE
23	Aug 30, 2013	33.37	33.48	33.09	33.4	42,790,200	0.03	FALSE	FALSE
24	Sep 03, 2013	31.75	32.07	31.29	31.88	154,507,000	0.13	FALSE	FALSE
25	Sep 04, 2013	31.39	31.47	31.11	31.2	142,320,600	-0.19	FALSE	FALSE
26	Sep 05, 2013	31.1	31.44	30.95	31.23	71,644,900	0.13	FALSE	FALSE
27	Sep 06, 2013	31.31	31.39	31.13	31.15	75,434,900	-0.16	FALSE	FALSE
28	Sep 09, 2013	31.22	31.79	31.2	31.66	49,628,500	0.44	FALSE	FALSE
29	Sep 10, 2013	31.9	32.4	31.79	32.39	56,881,200	0.49	FALSE	FALSE
30	Sep 11, 2013	32.57	32.93	32.53	32.74	39,087,500	0.17	FALSE	FALSE
31	Sep 12, 2013	32.72	32.78	32.59	32.69	32,860,200	-0.03	FALSE	FALSE
32	Sep 13, 2013	32.77	33.07	32.51	33.03	40,899,000	0.26	FALSE	FALSE
33	Sep 16, 2013	33.38	33.5	32.73	32.8	52,839,700	-0.58	FALSE	FALSE
34	Sep 17, 2013	33.42	33.47	32.9	32.93	84,716,500	-0.49	FALSE	FALSE
35	Sep 18, 2013	32.99	33.4	32.83	33.32	64,099,900	0.33	FALSE	FALSE
36	Sep 19, 2013	33.48	33.68	33.32	33.64	42,026,600	0.16	FALSE	FALSE
37	Sep 20, 2013	33.41	33.48	32.69	32.79	102,904,900	-0.62	FALSE	FALSE
38	Sep 23, 2013	32.54	32.97	32.5	32.74	39,826,100	0.2	FALSE	FALSE
39	Sep 24, 2013	32.87	32.87	32.15	32.45	40,685,000	-0.42	FALSE	FALSE
40	Sep 25, 2013	32.49	32.8	32.4	32.51	28,907,500	0.02	FALSE	FALSE
41	Sep 26, 2013	32.64	33	32.59	32.77	28,504,000	0.13	FALSE	FALSE
42	Sep 27, 2013	32.88	33.75	32.87	33.27	55,348,000	0.39	FALSE	FALSE
43	Sep 30, 2013	33	33.31	32.7	33.28	39,839,500	0.28	FALSE	FALSE
44	Oct 01, 2013	33.35	33.61	33.3	33.58	36,718,700	0.23	FALSE	FALSE
45	Oct 02, 2013	33.36	34.03	33.29	33.92	46,946,800	0.56	FALSE	FALSE
46	Oct 03, 2013	33.88	34	33.42	33.86	38,703,800	-0.02	FALSE	FALSE
47	Oct 04, 2013	33.69	33.99	33.62	33.88	33,008,100	0.19	FALSE	FALSE
48	Oct 07, 2013	33.6	33.71	33.2	33.3	35,069,300	-0.3	FALSE	FALSE
49	Oct 08, 2013	33.31	33.33	32.8	33.01	41,017,600	-0.3	FALSE	FALSE
50	Oct 09, 2013	33.07	33.35	32.96	33.07	35,878,600	0	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
H2:I2	H2	=COUNTIF(H4:H104,TRUE)
K2	K2	=AVERAGE(K4:K104)
G3:G50	G3	=E3-B3
H4:H50	H4	=AND(G4<0,G3<0,G2<0)
I4:I50	I4	=AND(H3,G4>0)
K4:K50	K4	=IF(H4,G5,"")

Note I only copied the first 50 rows

 

[rebel123]

Also, I really do not think I need the 5 negative days in there.

I like how you changed the column to "3 days down" so I think this is a better
spreadsheet here.

So far, yours are calculating correctly for 3 Days Down and 4 Days Down.

However, can we redo the part that says "Avg Gain 3 Day" and "Avg Gain 4 Day"

Date	Open	High	Low	Close	Volume	Daily Result	3 Days Down	4 Days Down	Avg Gain 3 Day	Avg Gain 4 Day
Aug 01, 2013	$32.06	$32.09	$31.60	$31.67	42,557,900		1	0
Aug 02, 2013	$31.69	$31.90	$31.57	$31.89	29,199,900	$0.22
Aug 05, 2013	$31.90	$32.00	$31.64	$31.70	30,984,000	-$0.19	FALSE	FALSE
Aug 06, 2013	$31.55	$31.67	$31.38	$31.58	36,331,500	-$0.12	FALSE	FALSE
Aug 07, 2013	$31.54	$32.10	$31.25	$32.06	38,078,600	$0.48	FALSE	FALSE
Aug 08, 2013	$32.24	$33.07	$32.05	$32.89	59,034,400	$0.83	FALSE	FALSE
Aug 09, 2013	$32.77	$32.90	$32.47	$32.70	26,800,700	-$0.19	FALSE	FALSE
Aug 12, 2013	$32.46	$32.97	$32.46	$32.87	25,493,700	$0.17	FALSE	FALSE
Aug 13, 2013	$32.51	$32.55	$32.21	$32.23	39,464,100	-$0.64	FALSE	FALSE
Aug 14, 2013	$32.14	$33.36	$31.70	$32.35	48,519,600	$0.12	FALSE	FALSE
Aug 15, 2013	$32.00	$32.18	$30.84	$31.79	33,338,000	-$0.56	FALSE	FALSE
Aug 16, 2013	$31.79	$31.99	$31.66	$31.80	32,866,300	$0.01	FALSE	FALSE
Aug 19, 2013	$31.76	$31.97	$31.38	$31.39	27,902,500	-$0.41	FALSE	FALSE
Aug 20, 2013	$31.44	$31.90	$31.37	$31.62	22,979,600	$0.23	FALSE	FALSE
Aug 21, 2013	$31.61	$32.01	$31.54	$31.61	37,409,100	-$0.01	FALSE	FALSE
Aug 22, 2013	$32.19	$32.49	$32.10	$32.39	31,169,900	$0.78	FALSE	FALSE
Aug 23, 2013	$35.17	$35.20	$34.00	$34.75	225,493,800	$2.36	FALSE	FALSE
Aug 26, 2013	$34.40	$34.67	$34.03	$34.15	72,786,800	-$0.60	FALSE	FALSE
Aug 27, 2013	$33.52	$34.10	$33.15	$33.26	58,522,300	-$0.89	FALSE	FALSE
Aug 28, 2013	$33.39	$33.60	$33.00	$33.02	44,257,400	-$0.24	TRUE	FALSE
Aug 29, 2013	$32.93	$33.60	$32.80	$33.55	45,284,700	$0.53	FALSE	FALSE
Aug 30, 2013	$33.37	$33.48	$33.09	$33.40	42,790,200	-$0.15	FALSE	FALSE
Sep 03, 2013	$31.75	$32.07	$31.29	$31.88	154,507,000	-$1.52	FALSE	FALSE

 

[offthelip]

What do you think is wrong with the equation for the gain on the 4th day after 3 down days ?
It look correct to me:
It checks whether there is aTRUE flag for three down days and then picks up tomorrows gain/loss

 

[rebel123]

Here is the whole file-

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

Or you can download it here-

/tmp/files - stockmarket34daysdown.xlsx

When you respond, can you please respond with the actual file, because my actual lines of data are 2519 rows long
which is too long too paste here and I do not understand the add on...

ALSO

I know nothing about Micros, so please let me know if I enable or disable them. That could be another possibility.

 

[offthelip]

Have you tried downloading the spreadsheet which I have posted using XL2BB? If you are not sure how to do this: Click on the box which is above the column with the row numbers and to the right of the column letters. (if you hover over it says BB2XL Click to copy to clipboard) , so after clicking go to excel and in a blank workhseet select cell A1 and select paste. This will give you the spreadsheet that I posted with number and the equations. Study the equations , I believe they do what you asked for in item 1: detect 3 down days and then one up day. It does not detect 4 down days!!
I think it is important for you to understand how the equations work, this will allow you to tailor them to meet all of your requirements.

 

[rebel123]

Thanks.