Finding the Trend Through High Low

[JoshF]

I probably did not explain the below as easily as possible, but I am not sure how to do so. I attached an excel file to helpfully better explain myself. Thanks in advance for your time.

Goal: To use data values to find a trend of higher highs and higher lows. Additional to find where this trend breaks. A trend requires 4-5 data points. Two higher lows and at least two higher highs.



I have a set of data that contains the daily high, daily open, daily close, and the daily low. I want to be able to use the excel formula/macro for any sort of data that has a value associated with a date. I am looking to find the "trend" of the data through the highs and lows. For example:

High is on Day 1. Day 3 a low is put in. On Day 5 a higher high is put in and on Day 7 a higher low is put in. Destroying this trend would be a case where on Day 9 a lower high is in and Day 11 a lower lower is put in, instead of a higher high. The tricky part is defining a trend change from a significant low versus a low found on a meaningless day. The low on 10/17 is inconsequential and not confirmed. If that low was below 10/14 low or 10/12, then it would prove to be a trend changer. I have labeled the highs as 1,2,3 in the picture and the lows as 8, 9, 10. A trend would need at least four data points for confirmation. Meaning two higher lows with two higher highs.

A change of trend would mean point 9 or 10 is less than 8. Note there is a decent pullback between point 1 to 8, 2, to 9, and 3 to 10, thus qualifying them as lows and highs. My goal is to be able to scan for such setups as the one below and find out when the trend has changed(meaning we cross point 10 at some point in the example shown).

The days are not important as the low can be on day 2 or 4,5, instead of day 3. The importance is finding a statement that recognizes a clear trend structure of higher highs and higher lows.

What I do not want is the following:
Day 1: High is 15, low is 12
Day 2: High is 14 low is 11
Day 3: High is 17 low is 14


The trend in the above to me is still up. Therefore not making a higher high the following day or making a slightly lower low the following day is not classified as a trend change.

A trend change would likely be a 1-1.5%(some variable that I can change) move or more.
I was thinking of building it through functions such as IF, NOT, Max, Min, Large, Small, but I am unsure exactly how to format it in such a way that it can conform to data. Is it possible to do this? I was thinking that the large and small function would have to adapt to data range based on min max function(something way over my head).

Thanks for any help and if you have any questions feel free to ask. Really not sure where to start.