matthewlouis
Active Member
- Joined
- Mar 28, 2014
- Messages
- 374
- Office Version
- 365
- 2019
- Platform
- Windows
This is a spreadsheet for creating historical probabilities for stock trading. I have successfully used this system for many years -- but I have never codified it in Excel, just used stock charts with anecdotal conclusions from gains and losses. Stocks consistently break higher (and lower) only to come back and “test” important prices that were broken on the way up (or way down). These “tests” occur at prices called Swing Point Highs (SPHs) and Swing Point Lows (SPLs). This sheet is for SPHs and I’ll just reverse the formulas for the SPL sheet.
This is an Index and Match with multiple conditions spreadsheet. I don’t need formulas for columns H through O (hat tip to PJMorris for solving those for me and Jasonb75’s help!) I need formulas for Columns P through U. There are no formulas in the spreadsheet below posted using XLB22; just the actual results that should be returned with the right formula.
I show THREE examples that are color-coded to match. There may be a way to decrease the number of columns (?) but I would rather have the columns as presented for analysis.
Column P and Column Q: This is where I want to see when (if) the price of the Swing Point High (SPH) in Column H was touched (tested) <= 6 trading days using the date from Column O. You index Column D for that result in Column P.
For example, in the 1st one color-coded YELLOW, the Swing Point High of 2,187.87 in Column H, Row 4 was NOT tested, so there is “No” there. In other words, between the date the SPH of 2,187.87 was closed over on 11/21/16 (Column M), 6 trading days from 11/21/16 is . . . 11/30/16. There wasn’t a price that touched the SPH of 2,187.87 between 11/22/16 (day after 11/21/16) and 11/30/16 (Column D) that touched (tested) the 2,187.87 SPH. Thus, all tests that occurred <= 6 days will be in Column P with the corresponding date in Column Q.
Column R and Column S: This is where the 1ST test occurred > 6 days, i.e., 7 days or more. In this 1st example in YELLOW, the 1st test came on the 60th trading day (12/1/16) at 2,187.44. (2187.44 is less than 2,187.87, that’s a test). IMPORTANT: There’s a 60-day window where tests must occur between or they aren’t relevant. In this case, the 60-day window ended on 12/1/2016 which just happened to be the date of the 1st test. Had the test come on 12/2/2016, there would be a “No” in Column R, Row 4 and Column S, Row 4.
Column T and Column U: What happens with stocks is after they test the SPH the 1st time, the price will bounce . . . then they may (or may not) come back for a 2nd test, bounce from there (Column T). Then, then they may come back and test a 3rd time (Column U). Finally, note that the SIX-day window used on the 1st test does NOT apply to the 2nd and 3rd tests. However, the SIXTY-day window does apply to the 2nd and 3rd tests – in this case 12/1/16 – so the 2nd and 3rd tests must occur on or before 12/1/16, in this example, it wasn’t applicable.
This is an Index and Match with multiple conditions spreadsheet. I don’t need formulas for columns H through O (hat tip to PJMorris for solving those for me and Jasonb75’s help!) I need formulas for Columns P through U. There are no formulas in the spreadsheet below posted using XLB22; just the actual results that should be returned with the right formula.
I show THREE examples that are color-coded to match. There may be a way to decrease the number of columns (?) but I would rather have the columns as presented for analysis.
Column P and Column Q: This is where I want to see when (if) the price of the Swing Point High (SPH) in Column H was touched (tested) <= 6 trading days using the date from Column O. You index Column D for that result in Column P.
For example, in the 1st one color-coded YELLOW, the Swing Point High of 2,187.87 in Column H, Row 4 was NOT tested, so there is “No” there. In other words, between the date the SPH of 2,187.87 was closed over on 11/21/16 (Column M), 6 trading days from 11/21/16 is . . . 11/30/16. There wasn’t a price that touched the SPH of 2,187.87 between 11/22/16 (day after 11/21/16) and 11/30/16 (Column D) that touched (tested) the 2,187.87 SPH. Thus, all tests that occurred <= 6 days will be in Column P with the corresponding date in Column Q.
Column R and Column S: This is where the 1ST test occurred > 6 days, i.e., 7 days or more. In this 1st example in YELLOW, the 1st test came on the 60th trading day (12/1/16) at 2,187.44. (2187.44 is less than 2,187.87, that’s a test). IMPORTANT: There’s a 60-day window where tests must occur between or they aren’t relevant. In this case, the 60-day window ended on 12/1/2016 which just happened to be the date of the 1st test. Had the test come on 12/2/2016, there would be a “No” in Column R, Row 4 and Column S, Row 4.
Column T and Column U: What happens with stocks is after they test the SPH the 1st time, the price will bounce . . . then they may (or may not) come back for a 2nd test, bounce from there (Column T). Then, then they may come back and test a 3rd time (Column U). Finally, note that the SIX-day window used on the 1st test does NOT apply to the 2nd and 3rd tests. However, the SIXTY-day window does apply to the 2nd and 3rd tests – in this case 12/1/16 – so the 2nd and 3rd tests must occur on or before 12/1/16, in this example, it wasn’t applicable.
| 2020 July 11 Data for PJ.xlsb | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 1 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | ||
| 2 | Daily SPH | Date | Date Index | High | Low | Close | Daily Volume | Swing Point High Price | Date of Swing Point High | Swing Point High Volume | 60 Day Window for "Closed Over" Date | Price Closed Over Swing Point High | Date Price Closed Over Swing Point High | Volume on Date Price Closed Over Swing Point High | 6 - Day Window for FIRST Test Date | FIRST TEST - Index Column D to see if price touched the Swing Point High Price <= Column O Date (6 Days) | First Test Date <= 6 Bars | Index Column D - did Price FIRST Test into Swing Point High Price GREATER THAN Column O Date AND LESS THAN Column K Date | First Test Date > 6 Bars | Index Column D - did Price SECOND Test into Swing Point High Price LESS THAN Column K Date | Index Column D - did Price THIRD Test into Swing Point High Price LESS THAN Column K Date | ||
| 3 | |||||||||||||||||||||||
| 4 | Daily SPH | 9/7/16 | -965 | 2,187.87 | 2,179.07 | 2,186.16 | 3,319,420,000 | 2,187.87 | 9/7/2016 | 3,319,420,000 | 12/1/2016 | 2,198.18 | 11/21/2016 | 3,607,010,000 | 11/30/2016 | No | No | 2,187.44 | 12/1/2016 | No | No | ||
| 5 | Daily SPH | 9/8/16 | -964 | 2,184.94 | 2,177.49 | 2,181.30 | 3,727,840,000 | ||||||||||||||||
| 6 | Daily SPH | 9/9/16 | -963 | 2,169.08 | 2,127.81 | 2,127.81 | 4,233,960,000 | ||||||||||||||||
| 7 | Daily SPH | 9/12/16 | -962 | 2,163.30 | 2,119.12 | 2,159.04 | 4,010,480,000 | ||||||||||||||||
| 8 | Daily SPH | 9/13/16 | -961 | 2,150.47 | 2,120.27 | 2,127.02 | 4,141,670,000 | ||||||||||||||||
| 9 | Daily SPH | 9/14/16 | -960 | 2,141.33 | 2,119.90 | 2,125.77 | 3,664,100,000 | ||||||||||||||||
| 10 | Daily SPH | 9/15/16 | -959 | 2,151.31 | 2,122.36 | 2,147.26 | 3,373,720,000 | ||||||||||||||||
| 11 | Daily SPH | 9/16/16 | -958 | 2,146.48 | 2,131.20 | 2,139.16 | 5,014,360,000 | ||||||||||||||||
| 12 | Daily SPH | 9/19/16 | -957 | 2,153.61 | 2,135.91 | 2,139.12 | 3,163,000,000 | ||||||||||||||||
| 13 | Daily SPH | 9/20/16 | -956 | 2,150.80 | 2,139.17 | 2,139.76 | 3,140,730,000 | ||||||||||||||||
| 14 | Daily SPH | 9/21/16 | -955 | 2,165.11 | 2,139.57 | 2,163.12 | 3,712,090,000 | ||||||||||||||||
| 15 | Daily SPH | 9/22/16 | -954 | 2,179.99 | 2,170.94 | 2,177.18 | 3,552,830,000 | 2,179.99 | 9/22/2016 | 3,552,830,000 | 12/16/2016 | 2,180.39 | 11/15/2016 | 4,543,860,000 | 11/23/2016 | 2,179.22 | 11/16/2016 | No | No | No | No | ||
| 16 | Daily SPH | 9/23/16 | -953 | 2,173.75 | 2,163.97 | 2,164.69 | 3,317,190,000 | ||||||||||||||||
| 17 | Daily SPH | 9/26/16 | -952 | 2,158.54 | 2,145.04 | 2,146.10 | 3,216,170,000 | ||||||||||||||||
| 18 | Daily SPH | 9/27/16 | -951 | 2,161.13 | 2,141.55 | 2,159.93 | 3,437,770,000 | ||||||||||||||||
| 19 | Daily SPH | 9/28/16 | -950 | 2,172.40 | 2,151.79 | 2,171.37 | 3,891,460,000 | ||||||||||||||||
| 20 | Daily SPH | 9/29/16 | -949 | 2,172.67 | 2,145.20 | 2,151.13 | 4,249,220,000 | ||||||||||||||||
| 21 | Daily SPH | 9/30/16 | -948 | 2,175.30 | 2,156.51 | 2,168.27 | 4,173,340,000 | 2,175.30 | 9/30/2016 | 4,173,340,000 | 12/27/2016 | 2,180.39 | 11/15/2016 | 4,543,860,000 | 11/23/2016 | No | No | No | No | No | No | ||
| 22 | Daily SPH | 10/3/16 | -947 | 2,164.41 | 2,154.77 | 2,161.20 | 3,137,550,000 | ||||||||||||||||
| 23 | Daily SPH | 10/4/16 | -946 | 2,165.46 | 2,144.01 | 2,150.49 | 3,750,890,000 | ||||||||||||||||
| 24 | Daily SPH | 10/5/16 | -945 | 2,163.95 | 2,155.15 | 2,159.73 | 3,906,550,000 | ||||||||||||||||
| 25 | Daily SPH | 10/6/16 | -944 | 2,162.93 | 2,150.28 | 2,160.77 | 3,461,550,000 | ||||||||||||||||
| 26 | Daily SPH | 10/7/16 | -943 | 2,165.86 | 2,144.85 | 2,153.74 | 3,619,890,000 | ||||||||||||||||
| 27 | Daily SPH | 10/10/16 | -942 | 2,169.60 | 2,160.39 | 2,163.66 | 2,916,550,000 | 2,169.60 | 10/10/2016 | 2,916,550,000 | 1/5/2017 | 2,180.39 | 11/15/2016 | 4,543,860,000 | 11/23/2016 | ||||||||
| 28 | Daily SPH | 10/11/16 | -941 | 2,161.56 | 2,128.84 | 2,136.73 | 3,438,270,000 | ||||||||||||||||
| 29 | Daily SPH | 10/12/16 | -940 | 2,145.36 | 2,132.77 | 2,139.18 | 2,977,100,000 | ||||||||||||||||
| 30 | Daily SPH | 10/13/16 | -939 | 2,138.19 | 2,114.72 | 2,132.55 | 3,580,450,000 | ||||||||||||||||
| 31 | Daily SPH | 10/14/16 | -938 | 2,149.19 | 2,132.98 | 2,132.98 | 3,228,150,000 | ||||||||||||||||
| 32 | Daily SPH | 10/17/16 | -937 | 2,135.61 | 2,124.43 | 2,126.50 | 2,830,390,000 | ||||||||||||||||
| 33 | Daily SPH | 10/18/16 | -936 | 2,144.38 | 2,135.49 | 2,139.60 | 3,170,000,000 | ||||||||||||||||
| 34 | Daily SPH | 10/19/16 | -935 | 2,148.44 | 2,138.15 | 2,144.29 | 3,362,670,000 | ||||||||||||||||
| 35 | Daily SPH | 10/20/16 | -934 | 2,147.18 | 2,133.44 | 2,141.34 | 3,337,170,000 | ||||||||||||||||
| 36 | Daily SPH | 10/21/16 | -933 | 2,142.63 | 2,130.09 | 2,141.16 | 3,448,850,000 | ||||||||||||||||
| 37 | Daily SPH | 10/24/16 | -932 | 2,154.79 | 2,146.91 | 2,151.33 | 3,357,320,000 | 2,154.79 | 10/24/2016 | 3,357,320,000 | 1/20/2017 | 2,163.26 | 11/9/2016 | 6,264,150,000 | 11/17/2016 | ||||||||
| 38 | Daily SPH | 10/25/16 | -931 | 2,151.44 | 2,141.93 | 2,143.16 | 3,751,340,000 | ||||||||||||||||
| 39 | Daily SPH | 10/26/16 | -930 | 2,145.73 | 2,131.59 | 2,139.43 | 3,775,200,000 | ||||||||||||||||
| 40 | Daily SPH | 10/27/16 | -929 | 2,147.13 | 2,132.52 | 2,133.04 | 4,204,830,000 | ||||||||||||||||
| 41 | Daily SPH | 10/28/16 | -928 | 2,140.72 | 2,119.36 | 2,126.41 | 4,019,510,000 | ||||||||||||||||
| 42 | Daily SPH | 10/31/16 | -927 | 2,133.25 | 2,125.53 | 2,126.15 | 3,922,400,000 | ||||||||||||||||
| 43 | Daily SPH | 11/1/16 | -926 | 2,131.45 | 2,097.85 | 2,111.72 | 4,532,160,000 | ||||||||||||||||
| 44 | Daily SPH | 11/2/16 | -925 | 2,111.76 | 2,094.00 | 2,097.94 | 4,248,580,000 | ||||||||||||||||
| 45 | Daily SPH | 11/3/16 | -924 | 2,102.56 | 2,085.23 | 2,088.66 | 3,886,740,000 | ||||||||||||||||
| 46 | Daily SPH | 11/4/16 | -923 | 2,099.07 | 2,083.79 | 2,085.18 | 3,837,860,000 | ||||||||||||||||
| 47 | Daily SPH | 11/7/16 | -922 | 2,132.00 | 2,100.59 | 2,131.52 | 3,736,060,000 | ||||||||||||||||
| 48 | Daily SPH | 11/8/16 | -921 | 2,146.87 | 2,123.56 | 2,139.56 | 3,916,930,000 | ||||||||||||||||
| 49 | Daily SPH | 11/9/16 | -920 | 2,170.10 | 2,125.35 | 2,163.26 | 6,264,150,000 | ||||||||||||||||
| 50 | Daily SPH | 11/10/16 | -919 | 2,182.30 | 2,151.17 | 2,167.48 | 6,451,640,000 | ||||||||||||||||
| 51 | Daily SPH | 11/11/16 | -918 | 2,165.92 | 2,152.49 | 2,164.45 | 4,988,050,000 | ||||||||||||||||
| 52 | Daily SPH | 11/14/16 | -917 | 2,171.36 | 2,156.08 | 2,164.20 | 5,367,200,000 | ||||||||||||||||
| 53 | Daily SPH | 11/15/16 | -916 | 2,180.84 | 2,166.38 | 2,180.39 | 4,543,860,000 | ||||||||||||||||
| 54 | Daily SPH | 11/16/16 | -915 | 2,179.22 | 2,172.20 | 2,176.94 | 3,830,590,000 | ||||||||||||||||
| 55 | Daily SPH | 11/17/16 | -914 | 2,188.06 | 2,176.65 | 2,187.12 | 3,809,160,000 | ||||||||||||||||
| 56 | Daily SPH | 11/18/16 | -913 | 2,189.89 | 2,180.38 | 2,181.90 | 3,572,400,000 | ||||||||||||||||
| 57 | Daily SPH | 11/21/16 | -912 | 2,198.70 | 2,186.43 | 2,198.18 | 3,607,010,000 | ||||||||||||||||
| 58 | Daily SPH | 11/22/16 | -911 | 2,204.80 | 2,194.51 | 2,202.94 | 3,957,940,000 | ||||||||||||||||
| 59 | Daily SPH | 11/23/16 | -910 | 2,204.72 | 2,194.51 | 2,204.72 | 3,418,640,000 | ||||||||||||||||
| 60 | Daily SPH | 11/25/16 | -909 | 2,213.35 | 2,206.27 | 2,213.35 | 1,584,600,000 | ||||||||||||||||
| 61 | Daily SPH | 11/28/16 | -908 | 2,211.14 | 2,200.36 | 2,201.72 | 3,505,650,000 | ||||||||||||||||
| 62 | Daily SPH | 11/29/16 | -907 | 2,210.46 | 2,198.15 | 2,204.66 | 3,706,560,000 | ||||||||||||||||
| 63 | Daily SPH | 11/30/16 | -906 | 2,214.10 | 2,198.81 | 2,198.81 | 5,533,980,000 | ||||||||||||||||
| 64 | Daily SPH | 12/1/16 | -905 | 2,202.60 | 2,187.44 | 2,191.08 | 5,063,740,000 | ||||||||||||||||
| 65 | Daily SPH | 12/2/16 | -904 | 2,197.95 | 2,188.37 | 2,191.95 | 3,779,500,000 | ||||||||||||||||
| 66 | Daily SPH | 12/5/16 | -903 | 2,209.42 | 2,199.97 | 2,204.71 | 3,895,230,000 | ||||||||||||||||
| 67 | Daily SPH | 12/6/16 | -902 | 2,212.78 | 2,202.21 | 2,212.23 | 3,855,320,000 | ||||||||||||||||
| 68 | Daily SPH | 12/7/16 | -901 | 2,241.63 | 2,208.93 | 2,241.35 | 4,501,820,000 | ||||||||||||||||
| 69 | Daily SPH | 12/8/16 | -900 | 2,251.69 | 2,237.57 | 2,246.19 | 4,200,580,000 | ||||||||||||||||
| 70 | Daily SPH | 12/9/16 | -899 | 2,259.80 | 2,249.23 | 2,259.53 | 3,884,480,000 | ||||||||||||||||
| 71 | Daily SPH | 12/12/16 | -898 | 2,264.03 | 2,252.37 | 2,256.96 | 4,034,510,000 | ||||||||||||||||
| 72 | Daily SPH | 12/13/16 | -897 | 2,277.53 | 2,263.32 | 2,271.72 | 3,857,590,000 | 2,277.53 | 12/13/2016 | 3,857,590,000 | 3/13/2017 | 2,280.07 | 1/24/2017 | 3,810,960,000 | 2/1/2017 | ||||||||
| 73 | Daily SPH | 12/14/16 | -896 | 2,276.20 | 2,248.44 | 2,253.28 | 4,406,970,000 | ||||||||||||||||
| 74 | Daily SPH | 12/15/16 | -895 | 2,272.12 | 2,253.77 | 2,262.03 | 4,168,200,000 | ||||||||||||||||
| 75 | Daily SPH | 12/16/16 | -894 | 2,268.05 | 2,254.24 | 2,258.07 | 5,920,340,000 | ||||||||||||||||
| 76 | Daily SPH | 12/19/16 | -893 | 2,267.47 | 2,258.21 | 2,262.53 | 3,248,370,000 | ||||||||||||||||
| 77 | Daily SPH | 12/20/16 | -892 | 2,272.56 | 2,266.14 | 2,270.76 | 3,298,780,000 | ||||||||||||||||
| 78 | Daily SPH | 12/21/16 | -891 | 2,271.23 | 2,265.15 | 2,265.18 | 2,852,230,000 | ||||||||||||||||
| 79 | Daily SPH | 12/22/16 | -890 | 2,263.18 | 2,256.08 | 2,260.96 | 2,876,320,000 | ||||||||||||||||
| 80 | Daily SPH | 12/23/16 | -889 | 2,263.79 | 2,258.84 | 2,263.79 | 2,020,550,000 | ||||||||||||||||
| 81 | Daily SPH | 12/27/16 | -888 | 2,273.82 | 2,266.15 | 2,268.88 | 1,987,080,000 | 2,273.82 | 12/27/2016 | 1,987,080,000 | 3/24/2017 | 2,276.98 | 1/6/2017 | 3,339,890,000 | 1/17/2017 | ||||||||
| 82 | Daily SPH | 12/28/16 | -887 | 2,271.31 | 2,249.11 | 2,249.92 | 2,392,360,000 | ||||||||||||||||
| 83 | Daily SPH | 12/29/16 | -886 | 2,254.51 | 2,244.56 | 2,249.26 | 2,336,370,000 | ||||||||||||||||
| 84 | Daily SPH | 12/30/16 | -885 | 2,253.58 | 2,233.62 | 2,238.83 | 2,670,900,000 | ||||||||||||||||
| 85 | Daily SPH | 1/3/17 | -884 | 2,263.88 | 2,245.13 | 2,257.83 | 3,770,530,000 | ||||||||||||||||
| 86 | Daily SPH | 1/4/17 | -883 | 2,272.82 | 2,261.60 | 2,270.75 | 3,764,890,000 | ||||||||||||||||
| 87 | Daily SPH | 1/5/17 | -882 | 2,271.50 | 2,260.45 | 2,269.00 | 3,761,820,000 | ||||||||||||||||
| 88 | Daily SPH | 1/6/17 | -881 | 2,282.10 | 2,264.06 | 2,276.98 | 3,339,890,000 | 2,282.10 | 1/6/2017 | 3,339,890,000 | 4/4/2017 | 2,298.37 | 1/25/2017 | 3,846,020,000 | 2/2/2017 | ||||||||
| 89 | Daily SPH | 1/9/17 | -880 | 2,275.49 | 2,268.90 | 2,268.90 | 3,217,610,000 | ||||||||||||||||
| 90 | Daily SPH | 1/10/17 | -879 | 2,279.27 | 2,265.27 | 2,268.90 | 3,638,790,000 | ||||||||||||||||
| 91 | Daily SPH | 1/11/17 | -878 | 2,275.32 | 2,260.83 | 2,275.32 | 3,620,410,000 | ||||||||||||||||
| 92 | Daily SPH | 1/12/17 | -877 | 2,271.78 | 2,254.25 | 2,270.44 | 3,462,130,000 | ||||||||||||||||
| 93 | Daily SPH | 1/13/17 | -876 | 2,278.68 | 2,271.51 | 2,274.64 | 3,081,270,000 | ||||||||||||||||
| 94 | Daily SPH | 1/17/17 | -875 | 2,272.08 | 2,262.81 | 2,267.89 | 3,584,990,000 | ||||||||||||||||
| 95 | Daily SPH | 1/18/17 | -874 | 2,272.01 | 2,263.35 | 2,271.89 | 3,315,250,000 | ||||||||||||||||
| 96 | Daily SPH | 1/19/17 | -873 | 2,274.33 | 2,258.41 | 2,263.69 | 3,165,970,000 | ||||||||||||||||
| 97 | Daily SPH | 1/20/17 | -872 | 2,276.96 | 2,265.01 | 2,271.31 | 3,524,970,000 | ||||||||||||||||
| 98 | Daily SPH | 1/23/17 | -871 | 2,271.78 | 2,257.02 | 2,265.20 | 3,152,710,000 | ||||||||||||||||
| 99 | Daily SPH | 1/24/17 | -870 | 2,284.63 | 2,266.68 | 2,280.07 | 3,810,960,000 | ||||||||||||||||
| 100 | Daily SPH | 1/25/17 | -869 | 2,299.55 | 2,288.88 | 2,298.37 | 3,846,020,000 | ||||||||||||||||
| 101 | Daily SPH | 1/26/17 | -868 | 2,300.99 | 2,294.08 | 2,296.68 | 3,610,360,000 | 2,300.99 | 1/26/2017 | 3,610,360,000 | 4/24/2017 | 2,307.87 | 2/9/2017 | 3,677,940,000 | 2/17/2017 | ||||||||
Daily SPH 2016 | |||||||||||||||||||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| T4:V4 | Cell Value | =41229 | text | YES |
| T4:V4 | Cell Value | =41166 | text | YES |
| T4:V4 | Cell Value | =41064 | text | YES |
| T4:V4 | Cell Value | =41229 | text | YES |
| O4:S4,K4:N965 | Cell Value | =41229 | text | YES |
| O4:S4,K4:N965 | Cell Value | =41166 | text | YES |
| O4:S4,K4:N965 | Cell Value | =41064 | text | YES |
| K4:K965,H4:I965,O4:S4,M4:N965 | Cell Value | =41229 | text | YES |
