Find the high after a low

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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.
1589086446713.png
 

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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.
1589167549490.png

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
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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?
1589215345612.png


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
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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 DateTo Date
1,176.55​
1/4/2002​
1/4/20027/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​
LMNMN
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​
LMNEXPECTED 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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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?
1589315018497.png
 

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,398
Members
417,208
Latest member
wendy823

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
Top