If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart bar is higher than the previous bar and the following bar, specially in a monthly bar chart. Excel is a fantastic tool as you know, it can help us identify these major turning points or pivots.
The picture below shows the simple logic, a bar that is lower than the previous and next bar is interesting to analyze if the stock market is going up (bull market).
Likewise if a bar is higher than the previous and the following one, keep an eye on these pivots if the market is going down.
Major turning points often happen when the price moves past a pivot. See the picture below.
The chart above shows the S&P500 during the house market crash 2007-2009, Each pivot excel identifies has a dotted line and if it is breached a buy or sell signal is generated. Subsequent signals of the same kind are not shown in the chart.
A blue dotted line shows "high" pivots and a red dotted line shows "low" pivots, if a new pivot is found the old one's dotted line ends.
Here is the sheet that made it possible, you can click to enlarge.
This keeps track of whether a buy or sell was the latest signal.
Formula in cell N24:
Checks if highest price point is above buy pivot. IF TRUE show buy pivot price.
Formula in cell O24:
Checks if lowest price point is below sell pivot. IF TRUE show sell pivot price.
Formula in cell P24:
Returns the buy pivot price if the last signal was a sell signal. This avoids subsequent buy signals of the same kind.
Formula in cell Q24:
Returns the sell pivot price if the last signal was a buy signal. This avoids subsequent sell signals of the same kind.
Copy cells and paste below as far as needed.
The purpose of these named ranges is that if you change the date range the chart will automatically use the new range. Basically, it looks for the last row not equal to "#N/A" in column C and returns a cell range we can use in the chart.