Find the high after a low

[matthewlouis]

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​

​

​

​

 

[matthewlouis]

Everything you stated as how the low and high interact is correct... I think... so let me say it.

After the initial high, the primary focus is the lowest low price found of course in the low price column. Yes, there will be lows, then a high, then a new low, then a pop back up to a lower high. I want the lowest low BEFORE it sets off to retrace off that low.

This is where you look in the High column to find the highest high off that lowest low. Again, looking for the largest drop (lowest low) and whatever retracement high price occurs after that.

Hope that helps describe the goal better....

One other thing. I copied 26 tabs to represent the 26 bear market since 1929 to run your Algo. After a while it kept saying not responding, I couldn’t work in it, I had to shut it down. It didn’t tell me there was an error anywhere. So I deleted all but one tab and I did not have that problem anymore. I opened it up again and deleted 13 of the 26 tabs, a bit slower but didn’t shut down. I have 32 mg of RAM, brand new $1400 computer from Dell I just got a week ago, very fast. Why would it struggle with those 26 tabs? I do have graphs for each period. Is that it?

 

[KRice]

On the Low Price curve, why would point #23 (1077.78) be taken as the bottom-out rather than point #32 (1074.36)? Both are part of a generally negative trend, and both occur after a brief upward spike....but point #32 is slightly lower.

 

[matthewlouis]

You are exactly correct! I visually missed that. Yes, 1074 is the lowest low before the retracement high. Great catch!

This is why I need an Algo. ?

 

[KRice]

Great!...the good news is that the current method finds that preferred point, but the method is still vulnerable to the occasional surprise where a later minimum drops off again causing a false bottom to be detected. I'll be looking at this on and off over the next day to investigate further.

 

[matthewlouis]

Thanks so much, can’t wait to see what you figure out ???

 

[KRice]

Here is a revised approach. We identify all of the extrema as before by examining the point-to-point slopes and identifying where the sign of the slope changes as the curve crosses each point, and whether the slope to the right of the point is positive, indicating (in this case) that the point is a local minimum (LMN). We know that one of these points is the minimum of interest. At this point, there are two ways forward in the worksheet: 1) proceed as before knowing that the likely minimum of interest may be a false bottom, or 2) attempt to rule out more of the LMN points. Both of these are in the sheet right now. I am curious how these compare for broader data sets, so please keep an eye on the index labeled "MN" in column M and compare that to the index in O3. I would consider the minima returned by these as likely bottom out points, but an an additional confirmation step is in order.

I've added some formulas that find the max of the Low Price curve from this likely bottom-out index forward. And then the range between that max and the likely bottom-out point on the Low Price curve is searched for the minimum value. That final search is returned in O1:Q1. This Low Price bottom out point is then used to partition the High Price curve into two parts. The left side of the High Price curve is searched for the maximum and reported as the "Initial High Price Max". The right side of the High Price curve is searched for the maximum and reported as the "Recovery High Price Max". Those three points of interest are summarized in G1:I3 and then plotted together with the High Price and Low Price curves, and used in the Retracement formula. The plot includes one more dotted curve representing a reduced set of local minima that emerged from the additional "rule out" algorithm that I was experimenting with in columns O:S.

This sheet is set up to accept 196 data points that could be pasted into H5:J200 (Date, High, Low)...although you'll probably have to adjust plot limits to accommodate different price ranges. I think the added search-for-minimum over a limited range should afford protection against false bottoms, but let me know if you encounter any surprises.

Regarding your other question, I wouldn't expect these worksheets to bog down your computer (I'm assuming you meant 32 GB of RAM rather than 32 MB?). This worksheet includes a few INDIRECT functions that can be slow, but there aren't very many. Have you adjusted any ranges to include entire columns?...that could be an issue. I've adjusted these formulas to stop at row 200. You may have to change that depending on the largest data set you have.

One option to consider is to keep the data on another sheet (or multiple sheets) without formulas, and then use a lookup scheme to reference the appropriate sheet and date range so that data are pulled from any of those data sheets into the computation worksheet.

The sheet below truncates the data due to size limitations, but the high/low data in post #7 was used.

MrExcel20200509.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Retracement	97.05%	2	1/7/2002	1176.97	Initial High Price Max (pre Low Price bottom)					32	2/20/2002	1074.36	Low Price Bottom Out
2			51	3/19/2002	1173.94	Recovery High Price Max (post Low Price bottom)					51	3/19/2002	1165.55	Low Price Max (post Low Price bottom)
3			32	2/20/2002	1074.36	Low Price Bottom Out					32	2/20/2002	1074.36	Likely Low Price Bottom Out
4			Index	Date	High Price	Low Price	Local Slope	Local Min	Min of Int		LMN Idx	Date	Low Price	Cand.	Local Slope
5			1	1/4/2002	1,176.55	1,163.42	0.04	LMN			1	1/4/2002	1163.42	1	-2.87
6			2	1/7/2002	1,176.97	1,163.55	-6.09				9	1/16/2002	1127.49
7			3	1/8/2002	1,167.60	1,157.46	-5.57				13	1/23/2002	1117.43
8			4	1/9/2002	1,174.26	1,151.89	-1.04				18	1/30/2002	1081.66
9			5	1/10/2002	1,159.93	1,150.85	-5.40				23	2/6/2002	1077.78
10			6	1/11/2002	1,159.41	1,145.45	-2.43				32	2/20/2002	1074.36	2	0.02
11			7	1/14/2002	1,145.60	1,138.15	-1.27				34	2/22/2002	1074.39	3	6.47
12			8	1/15/2002	1,148.81	1,136.88	-9.39				39	3/1/2002	1106.73	4	1.46
13			9	1/16/2002	1,146.19	1,127.49	0.08	LMN			47	3/13/2002	1151.01
14			10	1/17/2002	1,139.27	1,127.57	-3.12				53	3/21/2002	1139.48
15			11	1/18/2002	1,138.88	1,124.45	-1.63				56	3/26/2002	1131.61	5	-2.48
16			12	1/22/2002	1,135.26	1,117.91	-0.48				59	4/1/2002	1132.87
17			13	1/23/2002	1,131.94	1,117.43	10.75	LMN			61	4/3/2002	1119.68
18			14	1/24/2002	1,139.50	1,128.18	-0.36				64	4/8/2002	1111.79
19			15	1/25/2002	1,138.31	1,127.82	-0.39				67	4/11/2002	1102.42
20			16	1/28/2002	1,138.63	1,126.66	-27.92				69	4/15/2002	1099.41	6	-3.15
21			17	1/29/2002	1,137.47	1,098.74	-17.08				72	4/18/2002	1109.29
22			18	1/30/2002	1,113.79	1,081.66	31.64	LMN			80	4/30/2002	1063.46
23			19	1/31/2002	1,130.21	1,113.30	5.21				85	5/7/2002	1048.96	7	1.24
24			20	2/1/2002	1,130.20	1,118.51	-8.75				89	5/13/2002	1053.9	8	-5.26
25			21	2/4/2002	1,122.20	1,092.25	-9.67				96	5/22/2002	1075.64
26			22	2/5/2002	1,100.96	1,082.58	-4.80				101	5/30/2002	1054.26
27			23	2/6/2002	1,093.58	1,077.78	0.66	LMN			104	6/4/2002	1030.52
28			24	2/7/2002	1,094.03	1,078.44	1.47				107	6/7/2002	1012.49
29			25	2/8/2002	1,096.30	1,079.91	4.92				110	6/12/2002	1002.58
30			26	2/11/2002	1,112.01	1,094.68	8.30				112	6/14/2002	981.63
31			27	2/12/2002	1,112.68	1,102.98	4.52				118	6/24/2002	970.85
32			28	2/13/2002	1,120.56	1,107.50	4.80				120	6/26/2002	952.92
33			29	2/14/2002	1,124.72	1,112.30	-9.07				125	7/3/2002	934.87
34			30	2/15/2002	1,117.09	1,103.23	-5.25				130	7/11/2002	900.94
35			31	2/19/2002	1,104.18	1,082.24	-7.88				132	7/15/2002	876.46
36			32	2/20/2002	1,098.32	1,074.36	5.88	LMN	MN		138	7/23/2002	796.13	9	#N/A
Retracement

Cell Formulas
Range	Formula
F1	F1	=(I3-I2)/(I3-I1)
G1	G1	=INDEX(INDIRECT("g5:g"&$O$1),MATCH(I1,INDIRECT("I5:I"&$O$1),0))
H1:H2	H1	=IF(ISNUMBER(G1),INDEX(H$5:H$200,MATCH(G1,$G$5:$G$200,0)),"")
I1	I1	=AGGREGATE(14,6,INDIRECT("I5:I"&$O$1),1)
G2	G2	=INDEX(INDIRECT("g"&$O$1&":$g$200"),MATCH(I2,INDIRECT("I"&$O$1&":$I$200"),0))
I2	I2	=AGGREGATE(14,6,INDIRECT("I"&$O$1&":$I$200"),1)
G3:I3	G3	=O1
O1	O1	=INDEX(INDIRECT("g"&O3&":g"&O2),MATCH(Q1,INDIRECT("j"&O3&":j"&O2),0))
P5:P36,P1:P3	P1	=IF(ISNUMBER($O1),INDEX(H$5:H$200,MATCH($O1,$G$5:$G$200,0)),"")
Q1	Q1	=AGGREGATE(15,6,INDIRECT("j"&O3&":j"&O2),1)
O2	O2	=LOOKUP(1,0/FREQUENCY(0,1/(1+INDIRECT("I"&O3&":I200"))),INDIRECT("g"&O3&":g200"))
Q5:Q36,Q2:Q3	Q2	=IF(ISNUMBER($O2),INDEX(J$5:J$200,MATCH($O2,$G$5:$G$200,0)),"")
O3	O3	=AGGREGATE(15,3,(S5:S35>0)/((S5:S35>0)*(S5:S35<>""))*O5:O35,1)
K5:K36	K5	=IF(ISNUMBER(H5),(J6-J5)/(H6-H5),"")
M5:M36	M5	=IF(COUNTIF(M$4:M4,"MN")<1,IF(L5="LMN",IF(INDEX(J6:J$172,MATCH("LMN",L6:L$172,0))<J5,"","MN"),""),"")
O5:O36	O5	=IF(ROWS(O$5:O5)<=COUNTIF($L$5:$L$200,"LMN"),INDEX($G$5:$G$200, AGGREGATE(15,6,($L$5:$L$200="LMN")/($L$5:$L$200="LMN")*(ROW($L$5:$L$200)-ROW($L$4)),ROWS(O$5:O5))),"")
R5:R36	R5	=IF(ISNUMBER($O5),IF(OR(O5=1,Q6>Q5),MAX(R$4:R4)+1,""),"")
S5:S36	S5	=IF(ISNUMBER(R5), (INDEX($Q$5:$Q$50,MATCH(R5+1,$R$5:$R$50,0))-Q5)/(INDEX($O$5:$O$50,MATCH(R5+1,$R$5:$R$50,0))-O5),"")
L6:L36	L6	=IF(ISNUMBER(H6),IF(OR(G6=1,AND(SIGN(K6)<>SIGN(K5),K6>0)),"LMN",""),"")

 

[matthewlouis]

ok, this looks great. It may take me a day or so from now but I will update what we have done with this, test it using several time periods, and get back. This looks awesome.

Also, you are correct, I had changed your range to capture way too many rows .... like 2500. I will change that to 200 as that is a normal capture time anyway.

Thanks, Kirk, you’re a rock star! I will get back soon.

 

[KRice]

Good....thanks for the feedback. Parts of this are still somewhat clunky, but the more important part is to confirm that it yields the correct expected results. The plotted curves and green markers for the points of interest should help with the evaluations. You'll see this still uses the helper index column---I've found that's just more convenient to deal with than the dates (which change). Please keep an eye for discrepancies between the column M cell that receives the "MN" label (note its index) and compare to that reported in cell O3 (that value is taken from the supplemental steps that produce column S). I believe both of those still have some vulnerabilities to false bottoms on the declining part (left side) of the Low Price curve, but the added minimum search feature should rectify any misidentification. Depending on what you learn, the entire supplemental portion in columns O:S could probably be eliminated except we will need to ensure that the values passed to O1:Q3 can still be obtained from the remaining content.

 

[matthewlouis]

So far so good . . . I will pop in some date ranges and let you know what I find.

One thing I'm not getting are the same numbers starting with Columns O to Column S . . . I copied and pasted the same formula from your table in cell O5. But in the LMN Index, it returns 9 (your table above returned 9). That affects the next columns (???)

What I have in Cell O5:
=IF(ROWS(O$5:O5)<=COUNTIF($L$5:$L$200,"LMN"),INDEX($G$5:$G$200, AGGREGATE(15,6,($L$5:$L$200="LMN")/($L$5:$L$200="LMN")*(ROW($L$5:$L$200)-ROW($L$4)),ROWS(O$5:O5))),"")

32​	2/20/2002​	1,074.36​	Low Price Bottom Out
51​	3/19/2002​	1,165.55​	Low of Recovery Price High
32​	2/20/2002​	1,074.36​	Likely Low Price Bottom Out
LMN Index​	Date​	Low Price​	Cand.​	Local Slope​
9​	1/16/2002​	1,127.49​
13​	1/23/2002​	1,117.43​
18​	1/30/2002​	1,081.66​
23​	2/6/2002​	1,077.78​
32​	2/20/2002​	1,074.36​	1​	0.02​
34​	2/22/2002​	1,074.39​	2​	6.47​
39​	3/1/2002​	1,106.73​	3​	5.54​
47​	3/13/2002​	1,151.01​	4​	#N/A​

 

[KRice]

I've done some additional work on this. I added a data sheet of S&P500 historical data and can pull data between two dates from that sheet into the computation sheet. Based on examining a number of scenarios, I think the original approach that uses columns K,L, and M---to compute the local slopes, examine whether the slope sign changes across a point, and then based on those findings, identifies which points are local minima, and finally chooses the first potential bottom out point---I think that is sufficient for the first phase. That first potential bottom out point will, in a number of cases, be incorrect. That happens when price fluctuations cause a false bottom that triggers the rule set in the spreadsheet. Visually, we can see these curves continue down further to the "true bottom". I've looked at some other ideas in an attempt to mitigate this issue, but nothing has been satisfactory, and some of those other approaches run the risk of missing the point of interest altogether.

So the work-around is implemented in the second phase, where the "likely low price bottom out" (this could be an early false bottom, the true bottom, or something in between) is identified in Q3. Right now that value comes from column S, but it doesn't need to be.---it can be based on the determination of the point labelled "MN" in column M. I'll make that switch and repost. The point of this is that we identify this "likely" bottom and the max of the Low Price curve and then search between those two points for the minimum. This offers fairly high assurance that the bottom out point will not be missed. All of that is done in O1:Q3 and after I tweak the formulas, the rest of column O and beyond (to the right) will not be needed.

The 9 that you've made bold is correct. If you look at cell L13, that should show that index point #9 has been identified as a local minimum. The table headed by O4:S4 is simply consolidating those local minima from column L to eliminate the spaces between them. Those columns O:S are artifacts of the earlier effort to explore some other methods for finding which of the LMN points is the bottom out point. But I think the other approach I described obviates the need to keep those columns.