Stephen, thank you...that is very helpful. Did you recall weighing in on this a year and a half ago?
The objective then strongly suggests that Jeff's interpretation for the current request is correct. It appears the OP is indeed looking for a conditional probability. The initial wording...
where I could figure out probability of stocks going up two times and then down once
probably means there is a desire to estimate the expected probability that the Dow Jones closing price will go down
given that the index closed up the previous two days. The approach I took for this was to utilize helper columns. To examine the Up-Up-Down scenario (an Up closing followed by another Up closing followed by a Down closing), one column uses a simple formula that performs a walking comparison moving down the column day by day, looking back 3 days to determine whether there is a string of 2 "up" closings (from day 1 to 2 and from 2 to 3). I've referred to this column as UUX since it looks for Up closings during the previous two day-to-day intervals and does not evaluate the closing between "yesterday" and "today" (that comes next). Another column mimics this approach except the formula also checks for the change Down from day 3 ("yesterday") to day 4 (the current day corresponding to the row the formula is on). The first column gives the count of UUX instances and the second column gives the count of UUD instances, so the estimated conditional probability sought would be (count of True UUD)/(count of True UUX).
The same approach was used for the UUUX and UUUD scenario using another two helper columns.
Jeff was right about my first post...I assumed the objective was to estimate the likelihood for a UUD string and a UUUD string. I've left those in the file in columns H and I, respectively (in fact, they are needed for the probability calculation). The opposing trends of DDU and DDDU are also shown (columns J:K).
The L:M helper columns perform the UUX and UUUX checks described above. Both conditional probabilities for a Down that occurs when the previous two or three closings were Up appear to be about 49 %...if I didn't make any errors.
Here is a mini-sheet showing the setup and the computations of interest. The most recent version of my file is available at the link in the previous post if there is any interest in revising or extending the approach.
| MrExcel_20220630_DJIA.xlsx |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R |
|---|
| 1 | | | | | | | | 13.4% | 6.9% | 11.7% | 5.2% | 27.5% | 14.1% | <-- "TRUE" % | | Conditional Probabilities | | |
|---|
| 2 | | | | | | | | 7678 | 7677 | 7678 | 7677 | 7678 | 7677 | <-- Total Count | | P(D|UU) | UUD/UUX | 48.6% |
|---|
| 3 | | | | | | | | 1027 | 528 | 896 | 399 | 2114 | 1083 | <-- Count of TRUE's | | P(D|UUU) | UUUD/UUUX | 48.8% |
|---|
| 4 | Date | Open | High | Low | Close* | Adj Close** | Volume | UUD | UUUD | DDU | DDDU | UUX | UUUX | | | | | |
|---|
| 5 | 29-Jun-22 | 31,067.41 | 31,152.96 | 30,894.53 | 31,029.31 | 31,029.31 | 269,390,000 | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | | | | | |
|---|
| 6 | 28-Jun-22 | 31,549.05 | 31,885.09 | 30,934.33 | 30,946.99 | 30,946.99 | 350,780,000 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | | | | | |
|---|
| 7 | 27-Jun-22 | 31,533.60 | 31,598.59 | 31,351.37 | 31,438.26 | 31,438.26 | 309,910,000 | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | | | | | |
|---|
| 8 | 24-Jun-22 | 30,846.94 | 31,517.29 | 30,846.94 | 31,500.68 | 31,500.68 | 465,480,000 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | | | | | |
|---|
|
|---|
