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

R

rebel123

Active Member
Joined
Apr 18, 2017
Messages
351
Office Version
  1. 365
Platform
  1. MacOS
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.


DateOpenHighLowCloseVolumeDaily ResultFormula 1Formula 2Formula 3Gain
Aug 01, 2013$32.06$32.09$31.60$31.6742,557,900
Aug 02, 2013$31.69$31.90$31.57$31.8929,199,900$0.22
Aug 05, 2013$31.90$32.00$31.64$31.7030,984,000-$0.19
Aug 06, 2013$31.55$31.67$31.38$31.5836,331,500-$0.12
Aug 07, 2013$31.54$32.10$31.25$32.0638,078,600$0.48
Aug 08, 2013$32.24$33.07$32.05$32.8959,034,400$0.83
Aug 09, 2013$32.77$32.90$32.47$32.7026,800,700-$0.19
Aug 12, 2013$32.46$32.97$32.46$32.8725,493,700$0.17
Aug 13, 2013$32.51$32.55$32.21$32.2339,464,100-$0.64
Aug 14, 2013$32.14$33.36$31.70$32.3548,519,600$0.12
Aug 15, 2013$32.00$32.18$30.84$31.7933,338,000-$0.56
Aug 16, 2013$31.79$31.99$31.66$31.8032,866,300$0.01
Aug 19, 2013$31.76$31.97$31.38$31.3927,902,500-$0.41
Aug 20, 2013$31.44$31.90$31.37$31.6222,979,600$0.23
Aug 21, 2013$31.61$32.01$31.54$31.6137,409,100-$0.01
Aug 22, 2013$32.19$32.49$32.10$32.3931,169,900$0.78
Aug 23, 2013$35.17$35.20$34.00$34.75225,493,800$2.36
Aug 26, 2013$34.40$34.67$34.03$34.1572,786,800-$0.60
Aug 27, 2013$33.52$34.10$33.15$33.2658,522,300-$0.89
Aug 28, 2013$33.39$33.60$33.00$33.0244,257,400-$0.24
Aug 29, 2013$32.93$33.60$32.80$33.5545,284,700$0.53
Aug 30, 2013$33.37$33.48$33.09$33.4042,790,200-$0.15
Sep 03, 2013$31.75$32.07$31.29$31.88154,507,000-$1.52
Sep 04, 2013$31.39$31.47$31.11$31.20142,320,600-$0.68
Sep 05, 2013$31.10$31.44$30.95$31.2371,644,900$0.03
Sep 06, 2013$31.31$31.39$31.13$31.1575,434,900-$0.08
Sep 09, 2013$31.22$31.79$31.20$31.6649,628,500$0.51
Sep 10, 2013$31.90$32.40$31.79$32.3956,881,200$0.73
Sep 11, 2013$32.57$32.93$32.53$32.7439,087,500$0.35
Sep 12, 2013$32.72$32.78$32.59$32.6932,860,200-$0.05
Sep 13, 2013$32.77$33.07$32.51$33.0340,899,000$0.34
Sep 16, 2013$33.38$33.50$32.73$32.8052,839,700-$0.23
Sep 17, 2013$33.42$33.47$32.90$32.9384,716,500$0.13
Sep 18, 2013$32.99$33.40$32.83$33.3264,099,900$0.39
Sep 19, 2013$33.48$33.68$33.32$33.6442,026,600$0.32
Sep 20, 2013$33.41$33.48$32.69$32.79102,904,900-$0.85
Sep 23, 2013$32.54$32.97$32.50$32.7439,826,100-$0.05
Sep 24, 2013$32.87$32.87$32.15$32.4540,685,000-$0.29
Sep 25, 2013$32.49$32.80$32.40$32.5128,907,500$0.06
Sep 26, 2013$32.64$33.00$32.59$32.7728,504,000$0.26
Sep 27, 2013$32.88$33.75$32.87$33.2755,348,000$0.50
Sep 30, 2013$33.00$33.31$32.70$33.2839,839,500$0.01
Oct 01, 2013$33.35$33.61$33.30$33.5836,718,700$0.30
Oct 02, 2013$33.36$34.03$33.29$33.9246,946,800$0.34
Oct 03, 2013$33.88$34.00$33.42$33.8638,703,800-$0.06
Oct 04, 2013$33.69$33.99$33.62$33.8833,008,100$0.02
Oct 07, 2013$33.60$33.71$33.20$33.3035,069,300-$0.58
Oct 08, 2013$33.31$33.33$32.80$33.0141,017,600-$0.29
Oct 09, 2013$33.07$33.35$32.96$33.0735,878,600$0.06
Oct 10, 2013$33.31$33.89$33.26$33.7642,875,100$0.69
Oct 11, 2013$33.68$34.14$33.68$34.1330,033,300$0.37
Oct 14, 2013$33.90$34.50$33.78$34.4527,757,900$0.32
Oct 15, 2013$34.67$34.99$34.47$34.4947,097,800$0.04
Oct 16, 2013$34.60$34.90$34.56$34.6435,111,600$0.15
Oct 17, 2013$34.45$34.99$34.37$34.9231,359,200$0.28
Oct 18, 2013$34.82$34.99$34.33$34.9641,811,700$0.04
Oct 21, 2013$34.98$35.20$34.91$34.9927,433,500$0.03
Oct 22, 2013$35.02$35.10$34.52$34.5840,438,500-$0.41
Oct 23, 2013$34.35$34.49$33.67$33.7658,600,500-$0.82
Oct 24, 2013$33.82$34.10$33.57$33.7253,209,700-$0.04
Oct 25, 2013$35.88$36.29$35.47$35.73113,494,000$2.01
Oct 28, 2013$35.61$35.73$35.27$35.5738,383,600-$0.16
Oct 29, 2013$35.63$35.72$35.26$35.5231,702,200-$0.05
Oct 30, 2013$35.53$35.79$35.43$35.5436,997,700$0.02
Oct 31, 2013$35.66$35.69$35.34$35.4141,682,300-$0.13
Nov 01, 2013$35.67$35.69$35.39$35.5340,264,600$0.12
Nov 04, 2013$35.59$35.98$35.55$35.9428,060,700$0.41
Nov 05, 2013$35.79$36.71$35.77$36.6451,681,900$0.70
Nov 06, 2013$37.24$38.22$37.06$38.1888,948,800$1.54
Nov 07, 2013$37.96$38.01$37.43$37.5060,437,400-$0.68
Nov 08, 2013$37.67$37.78$37.34$37.7836,737,800$0.28
Nov 11, 2013$37.69$37.78$37.35$37.5926,872,500-$0.19
Nov 12, 2013$37.38$37.60$37.20$37.3631,651,600-$0.23
Nov 13, 2013$36.98$38.16$36.90$38.1644,957,600$0.80
Nov 14, 2013$37.87$38.13$37.72$38.0246,183,700-$0.14
Nov 15, 2013$37.95$38.02$37.72$37.8450,601,300-$0.18
Nov 18, 2013$37.35$37.58$37.07$37.2053,277,500-$0.64
Nov 19, 2013$36.85$37.23$36.67$36.7444,275,000-$0.46
Nov 20, 2013$36.92$37.41$36.86$37.0832,229,900$0.34
Nov 21, 2013$37.27$37.53$37.26$37.4023,064,700$0.32
Nov 22, 2013$37.53$37.68$37.33$37.5727,982,000$0.17
Nov 25, 2013$37.93$37.95$37.57$37.6430,646,800$0.07
Nov 26, 2013$37.57$37.65$37.35$37.3534,465,300-$0.29
Nov 27, 2013$37.57$37.76$37.49$37.6026,002,100$0.25
Nov 29, 2013$37.82$38.29$37.82$38.1322,090,400$0.53
Dec 02, 2013$38.09$38.78$38.06$38.4542,950,400$0.32
Dec 03, 2013$38.14$38.49$38.08$38.3152,109,800-$0.14
Dec 04, 2013$38.21$38.98$38.12$38.9451,983,600$0.63
Dec 05, 2013$38.85$38.88$37.18$38.00116,305,000-$0.94
Dec 06, 2013$38.42$38.55$37.99$38.3636,457,300$0.36
Dec 09, 2013$38.56$38.87$38.37$38.7130,286,000$0.35
Dec 10, 2013$38.61$38.90$38.02$38.1137,828,600-$0.60
Dec 11, 2013$38.06$38.30$37.39$37.6139,853,400-$0.50
Dec 12, 2013$37.64$37.64$37.18$37.2236,012,800-$0.39
Dec 13, 2013$37.42$37.45$36.62$36.6940,066,100-$0.53
Dec 16, 2013$36.73$37.00$36.54$36.8931,734,200$0.20
Dec 17, 2013$36.94$37.11$36.33$36.5245,687,700-$0.37
Dec 18, 2013$36.36$36.60$35.53$36.5863,192,100$0.06
Dec 19, 2013$36.51$36.55$36.08$36.2534,160,100-$0.33
Dec 20, 2013$36.20$36.93$36.19$36.8062,649,100$0.55
Dec 23, 2013$36.81$36.89$36.55$36.6225,128,700-$0.18
Dec 24, 2013$36.72$37.17$36.64$37.0814,243,000$0.46
Dec 26, 2013$37.20$37.49$37.17$37.4417,612,800$0.36
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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:
Excel Formula: 
=AND(G4<0,G3<0,G2<0)
then use column I to detect whether the 4th days is up with this equation
Excel Formula: 
=AND(H3,G4>0)
the at the top of each column you can put this in H2
Excel Formula: 
=COUNTIF(H4:H104,TRUE)
and in I2 this equation
Excel Formula: 
=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:
Excel Formula: 
=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
 
Upvote 0
offthelip said:
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:
Excel Formula: 
=AND(G4<0,G3<0,G2<0)
then use column I to detect whether the 4th days is up with this equation
Excel Formula: 
=AND(H3,G4>0)
the at the top of each column you can put this in H2
Excel Formula: 
=COUNTIF(H4:H104,TRUE)
and in I2 this equation
Excel Formula: 
=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:
Excel Formula: 
=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
Click to expand...
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.
 
Upvote 0
try this,
stock stuff.xlsm
ABCDEFGHIJK
1DateOpenHighLowCloseVolumeDaily ResultThree down daysFormula 2Formula 3Gain
2Aug 01, 201332.0632.0931.631.6742,557,90094-0.05
3Aug 02, 201331.6931.931.5731.8929,199,9000.2
4Aug 05, 201331.93231.6431.730,984,000-0.2FALSEFALSE 
5Aug 06, 201331.5531.6731.3831.5836,331,5000.03FALSEFALSE 
6Aug 07, 201331.5432.131.2532.0638,078,6000.52FALSEFALSE 
7Aug 08, 201332.2433.0732.0532.8959,034,4000.65FALSEFALSE 
8Aug 09, 201332.7732.932.4732.726,800,700-0.07FALSEFALSE 
9Aug 12, 201332.4632.9732.4632.8725,493,7000.41FALSEFALSE 
10Aug 13, 201332.5132.5532.2132.2339,464,100-0.28FALSEFALSE 
11Aug 14, 201332.1433.3631.732.3548,519,6000.21FALSEFALSE 
12Aug 15, 20133232.1830.8431.7933,338,000-0.21FALSEFALSE 
13Aug 16, 201331.7931.9931.6631.832,866,3000.01FALSEFALSE 
14Aug 19, 201331.7631.9731.3831.3927,902,500-0.37FALSEFALSE 
15Aug 20, 201331.4431.931.3731.6222,979,6000.18FALSEFALSE 
16Aug 21, 201331.6132.0131.5431.6137,409,1000FALSEFALSE 
17Aug 22, 201332.1932.4932.132.3931,169,9000.2FALSEFALSE 
18Aug 23, 201335.1735.23434.75225,493,800-0.42FALSEFALSE 
19Aug 26, 201334.434.6734.0334.1572,786,800-0.25FALSEFALSE 
20Aug 27, 201333.5234.133.1533.2658,522,300-0.26TRUEFALSE-0.37
21Aug 28, 201333.3933.63333.0244,257,400-0.37TRUEFALSE0.62
22Aug 29, 201332.9333.632.833.5545,284,7000.62FALSETRUE 
23Aug 30, 201333.3733.4833.0933.442,790,2000.03FALSEFALSE 
24Sep 03, 201331.7532.0731.2931.88154,507,0000.13FALSEFALSE 
25Sep 04, 201331.3931.4731.1131.2142,320,600-0.19FALSEFALSE 
26Sep 05, 201331.131.4430.9531.2371,644,9000.13FALSEFALSE 
27Sep 06, 201331.3131.3931.1331.1575,434,900-0.16FALSEFALSE 
28Sep 09, 201331.2231.7931.231.6649,628,5000.44FALSEFALSE 
29Sep 10, 201331.932.431.7932.3956,881,2000.49FALSEFALSE 
30Sep 11, 201332.5732.9332.5332.7439,087,5000.17FALSEFALSE 
31Sep 12, 201332.7232.7832.5932.6932,860,200-0.03FALSEFALSE 
32Sep 13, 201332.7733.0732.5133.0340,899,0000.26FALSEFALSE 
33Sep 16, 201333.3833.532.7332.852,839,700-0.58FALSEFALSE 
34Sep 17, 201333.4233.4732.932.9384,716,500-0.49FALSEFALSE 
35Sep 18, 201332.9933.432.8333.3264,099,9000.33FALSEFALSE 
36Sep 19, 201333.4833.6833.3233.6442,026,6000.16FALSEFALSE 
37Sep 20, 201333.4133.4832.6932.79102,904,900-0.62FALSEFALSE 
38Sep 23, 201332.5432.9732.532.7439,826,1000.2FALSEFALSE 
39Sep 24, 201332.8732.8732.1532.4540,685,000-0.42FALSEFALSE 
40Sep 25, 201332.4932.832.432.5128,907,5000.02FALSEFALSE 
41Sep 26, 201332.643332.5932.7728,504,0000.13FALSEFALSE 
42Sep 27, 201332.8833.7532.8733.2755,348,0000.39FALSEFALSE 
43Sep 30, 20133333.3132.733.2839,839,5000.28FALSEFALSE 
44Oct 01, 201333.3533.6133.333.5836,718,7000.23FALSEFALSE 
45Oct 02, 201333.3634.0333.2933.9246,946,8000.56FALSEFALSE 
46Oct 03, 201333.883433.4233.8638,703,800-0.02FALSEFALSE 
47Oct 04, 201333.6933.9933.6233.8833,008,1000.19FALSEFALSE 
48Oct 07, 201333.633.7133.233.335,069,300-0.3FALSEFALSE 
49Oct 08, 201333.3133.3332.833.0141,017,600-0.3FALSEFALSE 
50Oct 09, 201333.0733.3532.9633.0735,878,6000FALSEFALSE 
Sheet1
Cell Formulas
RangeFormula
H2:I2H2=COUNTIF(H4:H104,TRUE)
K2K2=AVERAGE(K4:K104)
G3:G50G3=E3-B3
H4:H50H4=AND(G4<0,G3<0,G2<0)
I4:I50I4=AND(H3,G4>0)
K4:K50K4=IF(H4,G5,"")

Note I only copied the first 50 rows
 
Upvote 0
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"



DateOpenHighLowCloseVolumeDaily Result3 Days Down4 Days DownAvg Gain 3 DayAvg Gain 4 Day
Aug 01, 2013$32.06$32.09$31.60$31.6742,557,90010
Aug 02, 2013$31.69$31.90$31.57$31.8929,199,900$0.22
Aug 05, 2013$31.90$32.00$31.64$31.7030,984,000-$0.19FALSEFALSE
Aug 06, 2013$31.55$31.67$31.38$31.5836,331,500-$0.12FALSEFALSE
Aug 07, 2013$31.54$32.10$31.25$32.0638,078,600$0.48FALSEFALSE
Aug 08, 2013$32.24$33.07$32.05$32.8959,034,400$0.83FALSEFALSE
Aug 09, 2013$32.77$32.90$32.47$32.7026,800,700-$0.19FALSEFALSE
Aug 12, 2013$32.46$32.97$32.46$32.8725,493,700$0.17FALSEFALSE
Aug 13, 2013$32.51$32.55$32.21$32.2339,464,100-$0.64FALSEFALSE
Aug 14, 2013$32.14$33.36$31.70$32.3548,519,600$0.12FALSEFALSE
Aug 15, 2013$32.00$32.18$30.84$31.7933,338,000-$0.56FALSEFALSE
Aug 16, 2013$31.79$31.99$31.66$31.8032,866,300$0.01FALSEFALSE
Aug 19, 2013$31.76$31.97$31.38$31.3927,902,500-$0.41FALSEFALSE
Aug 20, 2013$31.44$31.90$31.37$31.6222,979,600$0.23FALSEFALSE
Aug 21, 2013$31.61$32.01$31.54$31.6137,409,100-$0.01FALSEFALSE
Aug 22, 2013$32.19$32.49$32.10$32.3931,169,900$0.78FALSEFALSE
Aug 23, 2013$35.17$35.20$34.00$34.75225,493,800$2.36FALSEFALSE
Aug 26, 2013$34.40$34.67$34.03$34.1572,786,800-$0.60FALSEFALSE
Aug 27, 2013$33.52$34.10$33.15$33.2658,522,300-$0.89FALSEFALSE
Aug 28, 2013$33.39$33.60$33.00$33.0244,257,400-$0.24TRUEFALSE
Aug 29, 2013$32.93$33.60$32.80$33.5545,284,700$0.53FALSEFALSE
Aug 30, 2013$33.37$33.48$33.09$33.4042,790,200-$0.15FALSEFALSE
Sep 03, 2013$31.75$32.07$31.29$31.88154,507,000-$1.52FALSEFALSE
 
Upvote 0
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
 
Upvote 0
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-

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.
 
Last edited:
Upvote 0
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.
 
Upvote 1
Solution
You must log in or register to reply here.

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top