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]

Kirk, I actually have all the SPX data on the sheet. It’s hidden, that’s why you see things start with Column G. My data is daily all the way back to 1928. Would you like me to send that sheet where the columns are uncovered? I had created search bars for the start and finish and that’s where it was looking. If you have it that’s fine.

 

[matthewlouis]

Probably not a good idea to have all that data — 24,340 rows— on the same sheet. So never mind, I’ll adjust mine once I see what you have.

 

[KRice]

That's okay. I have it on a separate sheet. In the block that originally held date, low and high data (in columns H, I, and J), I used formulas to extract information from the main table that lies between two input dates that the user inputs. The main table is on a separate sheet. The input dates are entered in one of two ways: either a drop-down selector to choose an already-defined data range in a lookup area, or manual entry of From and To dates. Then enter either item "1" or "2" to copy that date range into the analysis block. I've also implemented a nice VBA solution from Jon Peltier (Calculate Nice Axis Scales in Excel VBA - Peltier Tech Blog) for adjusting chart axes. Some named ranges are used to plot data satisfying the From/To input date range, and then after seeing the plot, if you think that it needs some attention, the first thing to try is to select the chart and hit ctrl-A (the shortcut that invokes the VBA chart axis routine). Here's the cleaned up file:

StockMarketRecovery_v2.xlsm

Shared with Dropbox

www.dropbox.com

 

[matthewlouis]

Ok, I will open this... I have zero experience with VBA. Have always done the straight out formula thing. I’m a financial advisor so I’ve never had the time to invest in learning code. Just a wannabe who relies on experts like you ?

 

[KRice]

You shouldn't have to deal directly with the VBA (I hope). I placed the entirety of Jon's VBA code (from the link in post #23) in a module and then created a shortcut to run the routine on only the selected chart. Given that the price ranges of interest have changed over time, the VBA is an easy way to update the chart axes limits...and the code does a much better job establishing those limits than the default method used by Excel. Please check (or confirm for me) that the S&P high/low values from decades ago are the same? I think I loaded some data from 20's or 30's and saw only one curve (the high and low price curves were superimposed). I don't know if that indicates an issue with the original data source, or perhaps this is an artifact of how things were documented then?

 

[matthewlouis]

Ok I will try and get to that this afternoon and will let you know.

 

[matthewlouis]

I will also check you SPX data. Historically, high and low data didn’t start until the 1950s. But it’s ok, I have found in your previous work, that just because the high and lows were the same, your algo still worked way back then, of course, the newer time periods were more accurate with different highs and lows. I’ll check your data out and let you know if it matches what I have

 

[matthewlouis]

SPX data is correct. Very impressive looking table and chart!

OK, when I change dates (in blue) nothing changes (?) what am I missing? Control +A doesn't change anything.

 

[matthewlouis]

Ah, hit # 2 Manual Entry, I see.

 

[KRice]

Just now checking back in...sounds like you figured it out. The user entry block is either cell A5 (that's a drop-down selector that uses the list in columns Q:S) or the manual entry cells B6:C6. So those two different input options are called Item 1 and Item 2, respectively. So then the orange cell (A9) is used to indicate which input option to actually use for the analysis.