# Find the high after a low

#### matthewlouis

##### Active Member
I have two dates, one is the date of the high price and one is the date of the low price. Only the high price is shown. My goal is to create a formula that will look in the high price column and find the highest high AFTER a the FIRST low price is made in the high price column. When I look at the table visually, the answer is 93.02, the highest value made since 88.20 0n 3/15/1966. Is there a formula that can automatically find this number?

I tried an array formula =MAX(IF(\$H\$5:\$H\$24895>\$H\$2,\$I\$5:\$I\$24895)) but that didn't work.

H = Date column
\$H\$2 = highest price date
\$I\$2 = Lowest price date
Starting in cell I5 (letter I) is the high price column

Visually, I see 93.02 for several rows but the then it starts dropping as the high drops. I need 93.02 to be the answer.

Here's the data:

 From Date​ To Date​ (HIGH PRICE DATE) 2/9/1966 (cell H2)​ (LOW PRICE DATE) 10/7/1966 (Cell I2)​ Column H​ Column I​ (Cell H3) Date​ (Cell I3) High Price​ (cell H5) 2/9/1966​ (cell I5) 94.72​ 2/10/1966​ 94.70​ 2/11/1966​ 94.52​ 2/14/1966​ 94.40​ 2/15/1966​ 94.04​ 2/16/1966​ 93.74​ 2/17/1966​ 93.58​ 2/18/1966​ 93.14​ 2/21/1966​ 92.83​ 2/23/1966​ 92.21​ 2/24/1966​ 91.81​ 2/25/1966​ 91.88​ 2/28/1966​ 91.95​ 3/1/1966​ 91.65​ 3/2/1966​ 90.65​ 3/3/1966​ 90.03​ 3/4/1966​ 90.25​ 3/7/1966​ 89.39​ 3/8/1966​ 89.00​ 3/9/1966​ 89.21​ 3/10/1966​ 90.14​ 3/11/1966​ 89.63​ 3/14/1966​ 88.92​ 3/15/1966​ 88.20​ 3/16/1966​ 88.55​ 3/17/1966​ 88.60​ 3/18/1966​ 89.23​ 3/21/1966​ 89.73​ 3/22/1966​ 90.28​ 3/23/1966​ 89.80​ 3/24/1966​ 89.80​ 3/25/1966​ 90.14​ 3/28/1966​ 90.41​ 3/29/1966​ 90.04​ 3/30/1966​ 89.57​ 3/31/1966​ 89.70​ 4/1/1966​ 90.37​ 4/4/1966​ 91.33​ 4/5/1966​ 92.04​ 4/6/1966​ 92.10​ 4/7/1966​ 92.42​ 4/11/1966​ 92.60​ 4/12/1966​ 92.51​ 4/13/1966​ 92.81​ 4/14/1966​ 92.80​ 4/15/1966​ 92.75​ 4/18/1966​ 92.59​ 4/19/1966​ 92.31​ 4/20/1966​ 92.75​ 4/21/1966​ 93.02​ 4/22/1966​ 92.87​ 4/25/1966​ 92.86​ 4/26/1966​ 92.77​ 4/27/1966​ 92.49​ 4/28/1966​ 91.92​ 4/29/1966​ 91.86​ 5/2/1966​ 91.75​ 5/3/1966​ 91.10​ 5/4/1966​ 90.11​ 5/5/1966​ 89.77​ 5/6/1966​ 88.52​ 5/9/1966​ 87.96​ 5/10/1966​ 87.88​ 5/11/1966​ 88.38​ 5/12/1966​ 87.49​ 5/13/1966​ 86.31​ 5/16/1966​ 86.04​ 5/17/1966​ 85.03​ 5/18/1966​ 85.64​ 5/19/1966​ 86.33​ 5/20/1966​ 85.79​ 5/23/1966​ 86.91​ 5/24/1966​ 87.70​ 5/25/1966​ 87.48​ 5/26/1966​ 87.88​ 5/27/1966​ 87.42​ 5/31/1966​ 87.65​ 6/1/1966​ 86.65​ 6/2/1966​ 86.85​ 6/3/1966​ 86.55​ 6/6/1966​ 86.28​ 6/7/1966​ 85.54​ 6/8/1966​ 85.43​ 6/9/1966​ 85.98​ 6/10/1966​ 86.97​ 6/13/1966​ 87.59​ 6/14/1966​ 87.57​ 6/15/1966​ 87.74​ 6/16/1966​ 87.18​ 6/17/1966​ 87.11​ 6/20/1966​ 87.03​ 6/21/1966​ 87.28​ 6/22/1966​ 87.38​ 6/23/1966​ 87.73​ 6/24/1966​ 87.31​ 6/27/1966​ 87.31​ 6/28/1966​ 86.43​ 6/29/1966​ 85.98​ 6/30/1966​ 85.37​ 7/1/1966​ 86.08​ 7/5/1966​ 86.41​ 7/6/1966​ 87.38​ 7/7/1966​ 88.02​ 7/8/1966​ 88.04​ 7/11/1966​ 88.19​ 7/12/1966​ 87.78​ 7/13/1966​ 87.06​ 7/14/1966​ 87.34​ 7/15/1966​ 87.68​ 7/18/1966​ 87.59​ 7/19/1966​ 87.17​ 7/20/1966​ 86.64​ 7/21/1966​ 86.24​ 7/22/1966​ 86.11​ 7/25/1966​ 85.57​ 7/26/1966​ 84.67​ 7/27/1966​ 84.83​ 7/28/1966​ 84.76​ 7/29/1966​ 84.30​ 8/1/1966​ 83.50​ 8/2/1966​ 83.04​ 8/3/1966​ 83.71​ 8/4/1966​ 84.54​ 8/5/1966​ 84.70​ 8/8/1966​ 84.31​ 8/9/1966​ 84.36​ 8/10/1966​ 83.83​ 8/11/1966​ 83.53​ 8/12/1966​ 83.88​ 8/15/1966​ 83.69​ 8/16/1966​ 82.71​ 8/17/1966​ 81.90​ 8/18/1966​ 81.38​ 8/19/1966​ 80.78​ 8/22/1966​ 79.88​ 8/23/1966​ 79.24​ 8/24/1966​ 79.63​ 8/25/1966​ 79.79​ 8/26/1966​ 77.85​ 8/29/1966​ 76.24​ 8/30/1966​ 76.46​ 8/31/1966​ 78.06​ 9/1/1966​ 78.50​ 9/2/1966​ 78.20​ 9/6/1966​ 78.16​ 9/7/1966​ 77.26​ 9/8/1966​ 76.95​ 9/9/1966​ 76.94​ 9/12/1966​ 78.34​ 9/13/1966​ 79.16​ 9/14/1966​ 79.43​ 9/15/1966​ 80.60​ 9/16/1966​ 80.81​ 9/19/1966​ 80.50​ 9/20/1966​ 79.90​ 9/21/1966​ 79.15​ 9/22/1966​ 78.41​ 9/23/1966​ 78.43​ 9/26/1966​ 78.34​ 9/27/1966​ 79.10​ 9/28/1966​ 78.36​ 9/29/1966​ 77.28​ 9/30/1966​ 77.09​ 10/3/1966​ 76.98​ 10/4/1966​ 75.76​ 10/5/1966​ 76.10​ 10/6/1966​ 75.09​ 10/7/1966​ 74.67​
 ​ ​

 ​

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### KRice

##### Well-known Member
Some additional requirements may be necessary. I plotted the first 15 data points: the first "low" of the high price occurs at the 11th data point (2/24/1966, 91.81) [green point], followed by a very brief rise to a local maximum at the 13th data point (2/28/1966, 91.95) [red point], followed by decreasing values to another minimum (not shown). Based on your description, the "highest high" after the "first low" would be this 13th data point. Could you clarify whether this is actually the value of interest, and if not, what additional rules would be used to exclude it?

By the way, I believe one way to obtain the answer is to examine the slope between consecutive data points. The first sign change in the slope indicates a local extremum (in this case a minimum at point #11). The next sign change indicates another extremum (as one move past point #13). These slopes can be searched to find the second sign change, which coincides with the point of interest.

#### matthewlouis

##### Active Member
Thanks for your response. Yes, I didn’t explain the high to low thing well. The point of interest is 88.20. That is the lowest low after the initial high on 2/9/1966. Yes, I recognize the 11th data point is lower and then higher... I should have said lowest low.

After 88.20, the next point of interest 93.02. That’s the highest level after 88.20.

The goal of this is to calculate the fibanacci retracement. I know the high and the low prices...how much did price retrace off of the high on 2/9/1966? The 2/9/1966 high of 94.72 to 88.20 to 93.02. Once I have identified those three numbers, they automatically calculate the fib retrace percentage.

what do you think? Is there a formula for looking at a string of numbers and finding those points of interest?

Hope this helps! Thanks again!

#### KRice

##### Well-known Member
Thanks for this clarification. This is the language then that I think is confusing.
...look in the high price column and find the highest high AFTER a the FIRST low price is made in the high price column.

If I look in the "high" price column (the only price column provided), I see a value of 88.20 on 3/15/1966, but that is not the "FIRST low price" in the "high price column". It happens to be the 4th minimum on a plot of the high price values. This is why I said some additional requirements may be necessary. There is some other rule that you must be applying when visually examining the curve and choosing the 4th minimum value.

I suspect that you really want to begin at the first data point and move forward in time, examining each local minimum as it occurs. If the current local minimum is less than the previous local minimum, this indicates that the "high" price curve is still on a generally negative trajectory. There may very well be several local minima and maxima before we encounter the local minima of interest. That occurs when the current local minimum is less than the next local minimum---this indicates that we have bottomed out on the generally negative price trend.

In the table, I've introduced an index column for convenience in plotting (that column can be ignored later). In the plot, I've indicated local minima with solid blue circles and local maxima with solid purple circles, with one exception: the fourth local minimum is a solid green circle. You'll see that prior to this point, each successive local minimum is less than its predecessor. Moving beyond the green circle, the next local minimum has a greater value, so this establishes that the "minimum of interest" (solid green circle) has been found.

Note that this minimum of interest is not the first minimum nor the last minimum, and it is not necessarily the 4th minimum. To find this point, I used three helper columns (I think at least 2 helper columns may be necessary to do this with formulas, but I'm not certain). The first helper column computes the local slope between adjacent data points. The next helper column looks for a sign change between successive slopes. A change in sign indicates that either a local minimum or local maximum has occurred; and by checking whether the slope is positive or negative, we know which type of extremum we have encountered (sign change and slope>0 signifies a local minimum in this case). These occurrences are labeled with "LMN" (for local minimum). Then the last helper column evaluates two things: 1) whether the high price value associated with each of the local minima ("LMN") is less than the next local minima, indicating that the current local minimum may be the minimum of interest; and 2) whether such a condition has already occurred...and if not, then this is the minimum of interest and the point is labeled with "MN".

Then the formulas in L2:M3 use this identification of "MN" to report the minimum value of interest, the date associated with it, as well as to define a reduced range for searching for the maximum value beyond that point...and finally for reporting the date associated with the maximum value.

MrExcel20200509.xlsx
GHIJKLM
1From DateTo DateLocal SlopePoints of InterestDate
2HIGH PRICE DATE ->2/9/196610/7/1966<- LOW PRICE DATEmin88.23/15/1966
3DateHigh Pricemax aft93.024/21/1966
4IndexLocal MinMin of Int
512/9/196694.72-0.02
622/10/196694.7-0.18
732/11/196694.52-0.04
842/14/196694.4-0.36
952/15/196694.04-0.3
1062/16/196693.74-0.16
1172/17/196693.58-0.44
1282/18/196693.14-0.103333333
1392/21/196692.83-0.31
14102/23/196692.21-0.4
15112/24/196691.810.07LMN
16122/25/196691.880.023333333
17132/28/196691.95-0.3
18143/1/196691.65-1
19153/2/196690.65-0.62
20163/3/196690.030.22LMN
21173/4/196690.25-0.286666667
22183/7/196689.39-0.39
23193/8/1966890.21LMN
24203/9/196689.210.93
25213/10/196690.14-0.51
26223/11/196689.63-0.236666667
27233/14/196688.92-0.72
28243/15/196688.20.35LMNMN
29253/16/196688.550.05
30263/17/196688.60.63
31273/18/196689.230.166666667
32283/21/196689.730.55
33293/22/196690.28-0.48
34303/23/196689.80
35313/24/196689.80.34LMN
36323/25/196690.140.09
37333/28/196690.41-0.37
38343/29/196690.04-0.47
39353/30/196689.570.13LMN
40363/31/196689.70.67
41374/1/196690.370.32
42384/4/196691.330.71
43394/5/196692.040.06
44404/6/196692.10.32
45414/7/196692.420.045
46424/11/196692.6-0.09
47434/12/196692.510.3LMN
48444/13/196692.81-0.01
49454/14/196692.8-0.05
50464/15/196692.75-0.053333333
51474/18/196692.59-0.28
52484/19/196692.310.44LMN
53494/20/196692.750.27
54504/21/196693.02-0.15
55514/22/196692.87-0.003333333
56524/25/196692.86-0.09
57534/26/196692.77-0.28
58544/27/196692.49-0.57
59554/28/196691.92-0.06
60564/29/196691.86-0.036666667
61575/2/196691.75-0.65
62585/3/196691.1-0.99
63595/4/196690.11-0.34
64605/5/196689.77-1.25
65615/6/196688.52-0.186666667
66625/9/196687.96-0.08
67635/10/196687.880.5LMN
68645/11/196688.38-0.89
69655/12/196687.49-1.18
70665/13/196686.31-0.09
71675/16/196686.04-1.01
72685/17/196685.030.61LMN
73695/18/196685.640.69
74705/19/196686.33-0.54
75715/20/196685.790.373333333LMN
76725/23/196686.910.79
77735/24/196687.7-0.22
78745/25/196687.480.4LMN
79755/26/196687.88-0.46
80765/27/196687.420.0575LMN
81775/31/196687.65-1
82786/1/196686.650.2LMN
83796/2/196686.85-0.3
84806/3/196686.55-0.09
85816/6/196686.28-0.74
86826/7/196685.54-0.11
87836/8/196685.430.55LMN
88846/9/196685.980.99
89856/10/196686.970.206666667
90866/13/196687.59-0.02
91876/14/196687.570.17LMN
92886/15/196687.74-0.56
93896/16/196687.18-0.07
94906/17/196687.11-0.026666667
95916/20/196687.030.25LMN
96926/21/196687.280.1
97936/22/196687.380.35
98946/23/196687.73-0.42
99956/24/196687.310
100966/27/196687.31-0.88
101976/28/196686.43-0.45
102986/29/196685.98-0.61
103996/30/196685.370.71LMN
1041007/1/196686.080.0825
1051017/5/196686.410.97
1061027/6/196687.380.64
1071037/7/196688.020.02
1081047/8/196688.040.05
1091057/11/196688.19-0.41
1101067/12/196687.78-0.72
1111077/13/196687.060.28LMN
1121087/14/196687.340.34
1131097/15/196687.68-0.03
1141107/18/196687.59-0.42
1151117/19/196687.17-0.53
1161127/20/196686.64-0.4
1171137/21/196686.24-0.13
1181147/22/196686.11-0.18
1191157/25/196685.57-0.9
1201167/26/196684.670.16LMN
1211177/27/196684.83-0.07
1221187/28/196684.76-0.46
1231197/29/196684.3-0.266666667
1241208/1/196683.5-0.46
1251218/2/196683.040.67LMN
1261228/3/196683.710.83
1271238/4/196684.540.16
1281248/5/196684.7-0.13
1291258/8/196684.310.05LMN
1301268/9/196684.36-0.53
1311278/10/196683.83-0.3
1321288/11/196683.530.35LMN
1331298/12/196683.88-0.063333333
1341308/15/196683.69-0.98
1351318/16/196682.71-0.81
1361328/17/196681.9-0.52
1371338/18/196681.38-0.6
1381348/19/196680.78-0.3
1391358/22/196679.88-0.64
1401368/23/196679.240.39LMN
1411378/24/196679.630.16
1421388/25/196679.79-1.94
1431398/26/196677.85-0.536666667
1441408/29/196676.240.22LMN
1451418/30/196676.461.6
1461428/31/196678.060.44
1471439/1/196678.5-0.3
1481449/2/196678.2-0.01
1491459/6/196678.16-0.9
1501469/7/196677.26-0.31
1511479/8/196676.95-0.01
1521489/9/196676.940.466666667LMN
1531499/12/196678.340.82
1541509/13/196679.160.27
1551519/14/196679.431.17
1561529/15/196680.60.21
1571539/16/196680.81-0.103333333
1581549/19/196680.5-0.6
1591559/20/196679.9-0.75
1601569/21/196679.15-0.74
1611579/22/196678.410.02LMN
1621589/23/196678.43-0.03
1631599/26/196678.340.76LMN
1641609/27/196679.1-0.74
1651619/28/196678.36-1.08
1661629/29/196677.28-0.19
1671639/30/196677.09-0.036666667
16816410/3/196676.98-1.22
16916510/4/196675.760.34LMN
17016610/5/196676.1-1.01
17116710/6/196675.09-0.42
17216810/7/196674.67
Sheet3
Cell Formulas
RangeFormula
L2L2=INDEX(I5:I172,MATCH("MN",L5:L172,0))
M2M2=INDEX(H5:H172,MATCH("MN",L5:L172,0))
L3L3=AGGREGATE(14,6,OFFSET(\$I\$5,MATCH("MN",\$L\$5:\$L\$172,0),,ROWS(\$L\$5:\$L\$172)-MATCH("MN",\$L\$5:\$L\$172,0),),1)
M3M3=INDEX(OFFSET(\$H\$5,MATCH("MN",\$L\$5:\$L\$172,0),,ROWS(\$L\$5:\$L\$172)-MATCH("MN",\$L\$5:\$L\$172,0),),MATCH(L3,OFFSET(\$I\$5,MATCH("MN",\$L\$5:\$L\$172,0),,ROWS(\$L\$5:\$L\$172)-MATCH("MN",\$L\$5:\$L\$172,0),),0))
K5:K172K5=IF(AND(SIGN(J5)<>SIGN(J4),J5>0),"LMN","")
L5:L171L5=IF(COUNTIF(L\$4:L4,"MN")<1,IF(K5="LMN",IF(INDEX(I6:I\$172,MATCH("LMN",K6:K\$172,0))<I5,"","MN"),""),"")
L172L172=IF(COUNTIF(L\$4:L171,"MN")<1,IF(K172="LMN",IF(INDEX(I\$172:I173,MATCH("LMN",K\$172:K173,0))<I172,"","MN"),""),"")
J5:J171J5=(I6-I5)/(H6-H5)

#### matthewlouis

##### Active Member

WOW!!! Kirk, I cannot thank you enough, outstanding. I have tested your sheet using several time frames back in 1929, 1966, 2000, and 2007 . . . very insightful about what happens when you get major rallies (retracements) off lows -- like we are having now.

Thanks again for the work you put into this, much appreciated.

#### KRice

##### Well-known Member
You're welcome...I glad to help. I am curious...have you encountered any situations where unexpected results have been produced? Sometimes it's difficult to develop a rule set that produces the same results as our visual interpretation. For example, in this plot, I changed two values to exploit the rule set coded in the worksheet shown in post #4. I'll call your attention to points #18, #19, and #20. Point #18 was originally found on the declining part of the curve, but it held no special significance. But in this exercise, I've changed points #19 and #20 (original values shown in F23:F24 for reference). Point #19 was originally shown as a solid blue circle denoting a local minimum. With a relatively small shift upward to point #19, it no longer is a local minimum...the change actually establishes point #18 as a new local minimum (shown as a solid red circle). Then point #20 is shifted until it also becomes a new local minimum that is greater than the previous one (meaning the newly shifted point #20 is less than both point #19 and point #21 (therefore a local minimum), and it is greater than point #18. This set of conditions flags point #18 as the new minimum point of interest, and you'll see that it is indeed returned as such in cell L2.

My question then is, what is the expected result (your interpretation) of this revised high-price curve? Would you interpret this hypothetical case as having a minimum of interest at point #18 or point # 24 (which is the same as before)? If the latter, then what other rule is being applied? Does the upward trend after point #18 (red) need to be sustained longer? Or does the recovery amount need to be significantly greater as we move from post-red minima-to-minima?

MrExcel20200509.xlsx
FGHIJKLM
2HIGH PRICE DATE ->2/9/196610/7/1966<- LOW PRICE DATEmin89.393/7/1966
3DateHigh Pricemax aft93.024/21/1966
4IndexLocal MinMin of Int
512/9/196694.72-0.02
622/10/196694.7-0.18
732/11/196694.52-0.04
842/14/196694.4-0.36
952/15/196694.04-0.3
1062/16/196693.74-0.16
1172/17/196693.58-0.44
1282/18/196693.14-0.103333333
1392/21/196692.83-0.31
14102/23/196692.21-0.4
15112/24/196691.810.07LMN
16122/25/196691.880.023333333
17132/28/196691.95-0.3
18143/1/196691.65-1
19153/2/196690.65-0.62
20163/3/196690.030.22LMN
21173/4/196690.25-0.286666667
22183/7/196689.390.51LMNMN
2389193/8/196689.9-0.4
2489.21203/9/196689.50.64LMN
25213/10/196690.14-0.51
26223/11/196689.63-0.236666667
27233/14/196688.92-0.72
Sheet3 (2)
Cell Formulas
RangeFormula
L2L2=INDEX(I5:I172,MATCH("MN",L5:L172,0))
M2M2=INDEX(H5:H172,MATCH("MN",L5:L172,0))
L3L3=AGGREGATE(14,6,OFFSET(\$I\$5,MATCH("MN",\$L\$5:\$L\$172,0),,ROWS(\$L\$5:\$L\$172)-MATCH("MN",\$L\$5:\$L\$172,0),),1)
M3M3=INDEX(OFFSET(\$H\$5,MATCH("MN",\$L\$5:\$L\$172,0),,ROWS(\$L\$5:\$L\$172)-MATCH("MN",\$L\$5:\$L\$172,0),),MATCH(L3,OFFSET(\$I\$5,MATCH("MN",\$L\$5:\$L\$172,0),,ROWS(\$L\$5:\$L\$172)-MATCH("MN",\$L\$5:\$L\$172,0),),0))
J5:J27J5=(I6-I5)/(H6-H5)
K5:K27K5=IF(AND(SIGN(J5)<>SIGN(J4),J5>0),"LMN","")
L5:L27L5=IF(COUNTIF(L\$4:L4,"MN")<1,IF(K5="LMN",IF(INDEX(I6:I\$172,MATCH("LMN",K6:K\$172,0))<I5,"","MN"),""),"")

#### matthewlouis

##### Active Member

Yes, I have found some issues. Below is a new table with such a time period.

EXPECTED VALUES:
High = Point 1 = 1,176.55 (1/4/2002)
Low = Point 2 = 1,077.78 (2/6/2002)
Retracement High = Point 3 = 1,173.94 (3/19/2002)

Below, the algo correctly shows Point 3 of 1,173.94. However, as you see on the table below, the low the algo shows is 1,117.43 (1/23/2002). While that is the first "low", price did go lower to 1,077.78, the expected value. Funny, it did work on the first time period you did . . . and your suspicions were correct. This time period didn't return the expected value for the low.

My ultimate goal . . . find the largest retracement between two dates, not necessarily the first one. Again, the first time period 2/9/1966 to 10/7/1966 did that . . . but I've done 25 time periods and several worked and several didn't.

Retracement = (Low Price - High Point after the Low Price) / (Low Price - High Price).

For the example below, (1,077.78 - 1,173.94) / (1,077.78 - 1,176.55) = 97.35%. In other words, this time period saw a -7.91% drop from the high on 1/4/2002 to low of 1,077.78 by 2/6/2002 . . . then it retraced 97.35% of that loss (almost back to the high) . . . and then it rolled over.

Hope this helps! What do you think? Thanks again for your attention to this!

 From Date To Date 1,176.55​ 1/4/2002​ 1/4/2002 7/23/2002 1,117.43​ 1/23/2002 this is NOT expected value​ Day Date​ High​ Low​ 1,173.94​ 3/19/2002 This IS the expected value​ 1​ 1/4/2002​ 1,176.55​ 1,163.42​ 0.14​ LMN 2 1/7/2002​ 1,176.97​ 1,163.55​ -9.37​ 3 1/8/2002​ 1,167.60​ 1,157.46​ 6.66​ LMN 4 1/9/2002​ 1,174.26​ 1,151.89​ -14.33​ 5 1/10/2002​ 1,159.93​ 1,150.85​ -0.52​ 6 1/11/2002​ 1,159.41​ 1,145.45​ -4.60​ 7 1/14/2002​ 1,145.60​ 1,138.15​ 3.21​ LMN 8 1/15/2002​ 1,148.81​ 1,136.88​ -2.62​ 9 1/16/2002​ 1,146.19​ 1,127.49​ -6.92​ 10 1/17/2002​ 1,139.27​ 1,127.57​ -0.39​ 11 1/18/2002​ 1,138.88​ 1,124.45​ -0.90​ 12 1/22/2002​ 1,135.26​ 1,117.91​ -3.32​ 13 1/23/2002​ 1,131.94​ 1,117.43​ 7.56​ LMN MN 14 1/24/2002​ 1,139.50​ 1,128.18​ -1.19​ 15 1/25/2002​ 1,138.31​ 1,127.82​ 0.11​ LMN 16 1/28/2002​ 1,138.63​ 1,126.66​ -1.16​ 17 1/29/2002​ 1,137.47​ 1,098.74​ -23.68​ 18 1/30/2002​ 1,113.79​ 1,081.66​ 16.42​ LMN 19 1/31/2002​ 1,130.21​ 1,113.30​ -0.01​ 20 2/1/2002​ 1,130.20​ 1,118.51​ -2.67​ 21 2/4/2002​ 1,122.20​ 1,092.25​ -21.24​ 22 2/5/2002​ 1,100.96​ 1,082.58​ -7.38​ 23 2/6/2002​ 1,093.58​ 1,077.78​ 0.45​ LMN EXPECTED LOW VALUE 24 2/7/2002​ 1,094.03​ 1,078.44​ 2.27​ 25 2/8/2002​ 1,096.30​ 1,079.91​ 5.24​ 26 2/11/2002​ 1,112.01​ 1,094.68​ 0.67​ 27 2/12/2002​ 1,112.68​ 1,102.98​ 7.88​ 28 2/13/2002​ 1,120.56​ 1,107.50​ 4.16​ 29 2/14/2002​ 1,124.72​ 1,112.30​ -7.63​ 30 2/15/2002​ 1,117.09​ 1,103.23​ -3.23​ 31 2/19/2002​ 1,104.18​ 1,082.24​ -5.86​ 32 2/20/2002​ 1,098.32​ 1,074.36​ 3.18​ LMN 33 2/21/2002​ 1,101.50​ 1,080.24​ -7.57​ 34 2/22/2002​ 1,093.93​ 1,074.39​ 6.26​ LMN 35 2/25/2002​ 1,112.71​ 1,089.84​ 2.34​ 36 2/26/2002​ 1,115.05​ 1,101.72​ 8.01​ 37 2/27/2002​ 1,123.06​ 1,102.26​ -1.49​ 38 2/28/2002​ 1,121.57​ 1,106.73​ 10.22​ LMN 39 3/1/2002​ 1,131.79​ 1,106.73​ 7.35​ 40 3/4/2002​ 1,153.84​ 1,130.93​ 3.90​ 41 3/5/2002​ 1,157.74​ 1,144.78​ 7.55​ 42 3/6/2002​ 1,165.29​ 1,145.11​ 2.65​ 43 3/7/2002​ 1,167.94​ 1,150.69​ 4.82​ 44 3/8/2002​ 1,172.76​ 1,157.54​ 0.09​ 45 3/11/2002​ 1,173.03​ 1,159.58​ -4.77​ 46 3/12/2002​ 1,168.26​ 1,154.34​ -2.68​ 47 3/13/2002​ 1,165.58​ 1,151.01​ -7.75​ 48 3/14/2002​ 1,157.83​ 1,151.08​ 8.65​ LMN 49 3/15/2002​ 1,166.48​ 1,153.04​ 2.08​ 50 3/18/2002​ 1,172.73​ 1,159.14​ 1.21​ 51 3/19/2002​ 1,173.94​ 1,165.55​ -3.65​ 52 3/20/2002​ 1,170.29​ 1,151.61​ -15.19​ 53 3/21/2002​ 1,155.10​ 1,139.48​ 1.39​ LMN 54 3/22/2002​ 1,156.49​ 1,144.60​ -1.82​ 55 3/25/2002​ 1,151.04​ 1,131.87​ -4.04​ 56 3/26/2002​ 1,147.00​ 1,131.61​ -0.05​ 57 3/27/2002​ 1,146.95​ 1,135.33​ 7.50​ LMN 58 3/28/2002​ 1,154.45​ 1,144.58​ -1.65​ 59 4/1/2002​ 1,147.84​ 1,132.87​ -1.30​ 60 4/2/2002​ 1,146.54​ 1,135.71​ -7.69​ 61 4/3/2002​ 1,138.85​ 1,119.68​ -8.40​ 62 4/4/2002​ 1,130.45​ 1,120.06​ 2.86​ LMN 63 4/5/2002​ 1,133.31​ 1,119.49​ -2.63​ 64 4/8/2002​ 1,125.41​ 1,111.79​ 2.88​ LMN 65 4/9/2002​ 1,128.29​ 1,116.73​ 3.47​ 66 4/10/2002​ 1,131.76​ 1,117.80​ -1.29​ 67 4/11/2002​ 1,130.47​ 1,102.42​ -17.70​ 68 4/12/2002​ 1,112.77​ 1,102.74​ 0.70​ LMN 69 4/15/2002​ 1,114.86​ 1,099.41​ 14.54​ 70 4/16/2002​ 1,129.40​ 1,102.55​ 3.60​ 71 4/17/2002​ 1,133.00​ 1,123.37​ -2.51​ 72 4/18/2002​ 1,130.49​ 1,109.29​ -1.67​ 73 4/19/2002​ 1,128.82​ 1,122.59​ -1.22​ 74 4/22/2002​ 1,125.17​ 1,105.62​ -14.00​ 75 4/23/2002​ 1,111.17​ 1,098.94​ -2.71​ 76 4/24/2002​ 1,108.46​ 1,092.51​ -14.10​ 77 4/25/2002​ 1,094.36​ 1,084.81​ 2.41​ LMN 78 4/26/2002​ 1,096.77​ 1,076.31​ -5.94​ 79 4/29/2002​ 1,078.95​ 1,063.62​ 3.67​ LMN 80 4/30/2002​ 1,082.62​ 1,063.46​ 5.70​ 81 5/1/2002​ 1,088.32​ 1,065.29​ 3.10​ 82 5/2/2002​ 1,091.42​ 1,079.46​ -6.86​ 83 5/3/2002​ 1,084.56​ 1,068.89​ -2.87​ 84 5/6/2002​ 1,075.96​ 1,052.65​ -17.29​ 85 5/7/2002​ 1,058.67​ 1,048.96​ 30.25​ LMN 86 5/8/2002​ 1,088.92​ 1,049.49​ -0.07​ 87 5/9/2002​ 1,088.85​ 1,072.23​ -13.42​ 88 5/10/2002​ 1,075.43​ 1,053.93​ -0.20​ 89 5/13/2002​ 1,074.84​ 1,053.90​ 22.87​ LMN 90 5/14/2002​ 1,097.71​ 1,074.56​ 6.52​ 91 5/15/2002​ 1,104.23​ 1,088.94​ -4.94​ 92 5/16/2002​ 1,099.29​ 1,089.17​ 7.30​ LMN 93 5/17/2002​ 1,106.59​ 1,096.77​ 0.00​ 94 5/20/2002​ 1,106.59​ 1,090.61​ -7.04​ 95 5/21/2002​ 1,099.55​ 1,079.08​ -13.53​ 96 5/22/2002​ 1,086.02​ 1,075.64​ 11.08​ LMN 97 5/23/2002​ 1,097.10​ 1,080.55​ -0.02​ 98 5/24/2002​ 1,097.08​ 1,082.19​ -2.77​ 99 5/28/2002​ 1,085.98​ 1,070.31​ -11.15​ 100 5/29/2002​ 1,074.83​ 1,067.66​ -5.33​ 101 5/30/2002​ 1,069.50​ 1,054.26​ 10.43​ LMN 102 5/31/2002​ 1,079.93​ 1,064.66​ -3.06​ 103 6/3/2002​ 1,070.74​ 1,039.90​ -24.68​ 104 6/4/2002​ 1,046.06​ 1,030.52​ 4.05​ LMN 105 6/5/2002​ 1,050.11​ 1,038.84​ -0.21​ 106 6/6/2002​ 1,049.90​ 1,026.91​ -16.88​ 107 6/7/2002​ 1,033.02​ 1,012.49​ 1.72​ LMN 108 6/10/2002​ 1,038.18​ 1,025.45​ 0.86​ 109 6/11/2002​ 1,039.04​ 1,012.94​ -17.19​ 110 6/12/2002​ 1,021.85​ 1,002.58​ 1.62​ LMN 111 6/13/2002​ 1,023.47​ 1,008.12​ -13.91​ 112 6/14/2002​ 1,009.56​ 981.63​ 8.87​ LMN 113 6/17/2002​ 1,036.17​ 1,007.27​ 4.66​ 114 6/18/2002​ 1,040.83​ 1,030.92​ -3.22​ 115 6/19/2002​ 1,037.61​ 1,017.88​ -14.28​ 116 6/20/2002​ 1,023.33​ 1,004.59​ -17.04​ 117 6/21/2002​ 1,006.29​ 985.65​ -1.39​ 118 6/24/2002​ 1,002.11​ 970.85​ 3.77​ LMN 119 6/25/2002​ 1,005.88​ 974.21​ -28.45​ 120 6/26/2002​ 977.43​ 952.92​ 13.24​ LMN 121 6/27/2002​ 990.67​ 963.74​ 11.12​ 122 6/28/2002​ 1,001.79​ 988.31​ -2.44​ 123 7/1/2002​ 994.46​ 967.43​ -25.81​ 124 7/2/2002​ 968.65​ 945.54​ -14.35​ 125 7/3/2002​ 954.30​ 934.87​ 17.39​ LMN 126 7/5/2002​ 989.07​ 953.99​ 1.50​ 127 7/8/2002​ 993.56​ 972.91​ -13.93​ 128 7/9/2002​ 979.63​ 951.71​ -23.29​ 129 7/10/2002​ 956.34​ 920.29​ -27.18​ 130 7/11/2002​ 929.16​ 900.94​ 5.15​ LMN 131 7/12/2002​ 934.31​ 913.71​ -4.31​ 132 7/15/2002​ 921.39​ 876.46​ -2.74​ 133 7/16/2002​ 918.65​ 897.13​ 7.87​ LMN 134 7/17/2002​ 926.52​ 895.03​ -18.72​ 135 7/18/2002​ 907.80​ 880.60​ -26.24​ 136 7/19/2002​ 881.56​ 842.07​ -9.14​ 137 7/22/2002​ 854.13​ 813.26​ -26.44​ 138 7/23/2002​ 827.69​ 796.13​ 0.02​ LMN

#### KRice

##### Well-known Member
I don't understand the expected values you describe. Until this point, the focus has been on analyzing the High Price curve...finding the minimum of a downward trend, and then finding the highest recovery after that point. The "Low = Point 2 = 1,077.78 (2/6/2002)" that you mention comes from the Low Price curve, not the High Price curve, yet the intermediate results (slope, identification of local minima) indicate that the formulas are operating on the High Price curve.

When I process the High Price curve, I find a Point 2 (meaning the min of the downward trend) of 1131.94 on 1/23/2002 (which has an index of 13 on the plot shown below). I suspect this still isn't what you want---this condition is precisely what I laid out in my previous post, and the conditions just happen to exploit the decision rules and find a false bottom. It sounds as if some tweaks to the rule are in order, but I doubt that it will be foolproof. I thought point #23 on the High Price curve (1093.58 on 2/6/2002) would be the minimum of interest, but I'm confused by your introduction of a point from the Low Price curve. Could you explain further?...is this an oversight, or am I missing something?

What are your thoughts on this? I wonder if the evaluation of the local minima should look ahead not just to the next minima (to determine if the next minima is higher, signaling a recovery), but perhaps the next two or three minima to offer greater confidence that there is a more sustained recovery...or perhaps to look ahead at the average of the next two minima. It can be very difficult to establish a set of rules that will work well to extract specific points of interest from a broad selection of irregular curves. I have a few ideas to try yet, but a program with greater flexibility and capability might be better suited.

Also, the "High = Point 1 = 1,176.55 (1/4/2002) isn't the largest value (I'm assuming you want the High Price curve). That occurs on the 2nd day (1/7/2002) with a value of 1176.97.

Finally, is the Low Price curve supposed to be involved in this somehow?

#### matthewlouis

##### Active Member
My mistake. Oh wow. I just realized I didn’t include the LOW prices on the original chart. I’m so sorry.

Yes, the low price curve must be included in this analysis because that is where the Algo must pick up the lowest low after the initial high from the high column.... and before the retracement high also from the high column.

The 1,077 number from the 2nd example time period comes from the low price column. The 1093 is a high from the High column. I bolded the expected numbers.

So the Algo needs to to search for two numbers in two different columns, not just one. Isn’t that just another column to add? Or is it more complicated than that?

Again, my apologies for leaving off the low price column, I cannot believe I did that with that is such a key part of the process. Do you want me to go back to the first example and add the LOW PRICE column so you want work with the same numbers? I can do that... feels like this is just a tweek away... if I would get you the right data!!

Last edited:

#### KRice

##### Well-known Member
About adding the low price column...I wouldn't worry about it. Your latest post offers enough to work with. I will suggest that you use the XL2BB add-in for uploading portions of your file. That makes code/data/formula exchanges much easier. You are probably not aware, but if someone copies the data in your tables posted above and pastes that into their spreadsheet, they will encounter some difficulties. A non-printing character CHAR(63) is appended to the end of the data in each cell, so dates and other numbers are treated as text, and nothing can be done with them until they are cleaned of the unwanted character. I processed the data by checking the string length ("n") and then extracting the leftmost n-1 characters...that gave me something to work with.

You're correct about the omission of the Low Price curve...whatever algorithm and helper columns were used to operate on the High Price curve would simply need to be applied to the Low Price curve, but I'd like to confirm how the two curves are to be associated with each other. Are you saying that the early part of the High Price curve is searched to find the maximum (see my note on post #8 about this...I think there was a small inconsistency in what you reported). That maximum on the High Price curve occurs on, let's say "dateH0"...the date of the initial max value on the High Price curve. Then the focus shifts to the Low Price curve and it is examined from dateH0 forward, looking to establish where the Low Price curve bottoms before it begins to recover...is that correct? Let's say this bottom out occurs at "dateLB"...the date from the Low Price curve where bottoming occurs. Then we turn back to the High Price curve and search from dateLB forward to find what...the global maximum from dateLB onward?

I'm experimenting with some walking averages and other ideas to see if there might be a more robust way to find the bottom...some way that isn't quite so sensitive to sporadic fluctuations that trigger the decision rule. As I mentioned above, I don't know if that can be done perfectly.

Replies
4
Views
177
Replies
4
Views
109
Replies
15
Views
151
Replies
0
Views
92
Replies
4
Views
52

1,130,370
Messages
5,641,749
Members
417,232
Latest member
MakeSense

### 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.

### Which adblocker are you using?

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

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