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​

 

matthewlouis

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

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

KRice

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

matthewlouis

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

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

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

KRice

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


MrExcel20200509.xlsx
EFGHIJKLMNOPQRS
1Retracement97.05%21/7/20021176.97Initial High Price Max (pre Low Price bottom)322/20/20021074.36Low Price Bottom Out
2513/19/20021173.94Recovery High Price Max (post Low Price bottom)513/19/20021165.55Low Price Max (post Low Price bottom)
3322/20/20021074.36Low Price Bottom Out322/20/20021074.36Likely Low Price Bottom Out
4IndexDateHigh PriceLow PriceLocal SlopeLocal MinMin of IntLMN IdxDateLow PriceCand.Local Slope
511/4/20021,176.551,163.420.04LMN 11/4/20021163.421-2.87
621/7/20021,176.971,163.55-6.09  91/16/20021127.49  
731/8/20021,167.601,157.46-5.57  131/23/20021117.43  
841/9/20021,174.261,151.89-1.04  181/30/20021081.66  
951/10/20021,159.931,150.85-5.40  232/6/20021077.78  
1061/11/20021,159.411,145.45-2.43  322/20/20021074.3620.02
1171/14/20021,145.601,138.15-1.27  342/22/20021074.3936.47
1281/15/20021,148.811,136.88-9.39  393/1/20021106.7341.46
1391/16/20021,146.191,127.490.08LMN 473/13/20021151.01  
14101/17/20021,139.271,127.57-3.12  533/21/20021139.48  
15111/18/20021,138.881,124.45-1.63  563/26/20021131.615-2.48
16121/22/20021,135.261,117.91-0.48  594/1/20021132.87  
17131/23/20021,131.941,117.4310.75LMN 614/3/20021119.68  
18141/24/20021,139.501,128.18-0.36  644/8/20021111.79  
19151/25/20021,138.311,127.82-0.39  674/11/20021102.42  
20161/28/20021,138.631,126.66-27.92  694/15/20021099.416-3.15
21171/29/20021,137.471,098.74-17.08  724/18/20021109.29  
22181/30/20021,113.791,081.6631.64LMN 804/30/20021063.46  
23191/31/20021,130.211,113.305.21  855/7/20021048.9671.24
24202/1/20021,130.201,118.51-8.75  895/13/20021053.98-5.26
25212/4/20021,122.201,092.25-9.67  965/22/20021075.64  
26222/5/20021,100.961,082.58-4.80  1015/30/20021054.26  
27232/6/20021,093.581,077.780.66LMN 1046/4/20021030.52  
28242/7/20021,094.031,078.441.47  1076/7/20021012.49  
29252/8/20021,096.301,079.914.92  1106/12/20021002.58  
30262/11/20021,112.011,094.688.30  1126/14/2002981.63  
31272/12/20021,112.681,102.984.52  1186/24/2002970.85  
32282/13/20021,120.561,107.504.80  1206/26/2002952.92  
33292/14/20021,124.721,112.30-9.07  1257/3/2002934.87  
34302/15/20021,117.091,103.23-5.25  1307/11/2002900.94  
35312/19/20021,104.181,082.24-7.88  1327/15/2002876.46  
36322/20/20021,098.321,074.365.88LMNMN1387/23/2002796.139#N/A
Retracement
Cell Formulas
RangeFormula
F1F1=(I3-I2)/(I3-I1)
G1G1=INDEX(INDIRECT("g5:g"&$O$1),MATCH(I1,INDIRECT("I5:I"&$O$1),0))
H1:H2H1=IF(ISNUMBER(G1),INDEX(H$5:H$200,MATCH(G1,$G$5:$G$200,0)),"")
I1I1=AGGREGATE(14,6,INDIRECT("I5:I"&$O$1),1)
G2G2=INDEX(INDIRECT("g"&$O$1&":$g$200"),MATCH(I2,INDIRECT("I"&$O$1&":$I$200"),0))
I2I2=AGGREGATE(14,6,INDIRECT("I"&$O$1&":$I$200"),1)
G3:I3G3=O1
O1O1=INDEX(INDIRECT("g"&O3&":g"&O2),MATCH(Q1,INDIRECT("j"&O3&":j"&O2),0))
P5:P36,P1:P3P1=IF(ISNUMBER($O1),INDEX(H$5:H$200,MATCH($O1,$G$5:$G$200,0)),"")
Q1Q1=AGGREGATE(15,6,INDIRECT("j"&O3&":j"&O2),1)
O2O2=LOOKUP(1,0/FREQUENCY(0,1/(1+INDIRECT("I"&O3&":I200"))),INDIRECT("g"&O3&":g200"))
Q5:Q36,Q2:Q3Q2=IF(ISNUMBER($O2),INDEX(J$5:J$200,MATCH($O2,$G$5:$G$200,0)),"")
O3O3=AGGREGATE(15,3,(S5:S35>0)/((S5:S35>0)*(S5:S35<>""))*O5:O35,1)
K5:K36K5=IF(ISNUMBER(H5),(J6-J5)/(H6-H5),"")
M5:M36M5=IF(COUNTIF(M$4:M4,"MN")<1,IF(L5="LMN",IF(INDEX(J6:J$172,MATCH("LMN",L6:L$172,0))<J5,"","MN"),""),"")
O5:O36O5=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:R36R5=IF(ISNUMBER($O5),IF(OR(O5=1,Q6>Q5),MAX(R$4:R4)+1,""),"")
S5:S36S5=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:L36L6=IF(ISNUMBER(H6),IF(OR(G6=1,AND(SIGN(K6)<>SIGN(K5),K6>0)),"LMN",""),"")
 

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

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