Author: Oscar Cronquist Article last updated on August 06, 2017

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.

The user defined array formula in cell C23:I355:

=YahooStockQuotes(YEAR(D18), MONTH(D18), DAY(D18), YEAR(D19), MONTH(D19), DAY(D19), "m", "^GSPC")

Enter this udf array formula in cell range C23:I355. It fetches data from yahoo and a stock ticker you specify. You can read more about this udf here: Excel udf: Import historical stock prices from yahoo

Formula in cell A23:

=YEAR(C23)

This formula returns the YEAR from a date in cell C23.

Formula in cell B23:

=LEFT(TEXT(C23,"mmm"))

This formula returns the first letter from a month.

Formula in cell K24:

=IF((E24>E23)*(E24>E25),E24,K25)

This formula checks if the high is higher than the previous and next value. If TRUE change the value to the new high, if FALSE repeat previous high value.

Formula in cell L24:

=IF((F24<F23)*(F24<F25),F24,L25)

This formula checks if the low is lower than the previous and next value. If TRUE change the value to the new low, if FALSE repeat previous low value.

Formula in cell M24:

=IF((N24="")*(O24=""),IF(M25="","",M25),IF(N24<>"","B",IF(O24<>"","S","")))

This keeps track of whether a buy or sell was the latest signal.

Formula in cell N24:

=IF((E24>K25)*(E25<K25),K25,"")

Checks if highest price point is above buy pivot. IF TRUE show buy pivot price.

Formula in cell O24:

=IF((F24<L25)*(F25>L25),L25,"")

Checks if lowest price point is below sell pivot. IF TRUE show sell pivot price.

Formula in cell P24:

=IF((N24<>"")*(M25="S"),N24,"")

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:

=IF((O24<>"")*(M25="B"),O24,"")

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.

### Named ranges

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.

MClose

=Pivots!\$G\$23:INDEX(Pivots!\$G:\$G,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

MHigh200

=Pivots!\$E\$23:INDEX(Pivots!\$E:\$E,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

MLow200

=Pivots!\$F\$23:INDEX(Pivots!\$F:\$F,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

BPivotlines

=Pivots!\$K\$23:INDEX(Pivots!\$K:\$K,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

=Pivots!\$P\$23:INDEX(Pivots!\$P:\$P,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

Dates200

=Pivots!\$A\$23:INDEX(Pivots!\$B:\$B,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

Sellm200

=Pivots!\$N\$23:INDEX(Pivots!\$N:\$N,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

SellPivots

=Pivots!\$Q\$23:INDEX(Pivots!\$Q:\$Q,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

SPivotlines

=Pivots!\$L\$23:INDEX(Pivots!\$L:\$L,MAX(IFERROR(IF(Pivots!\$C\$23:\$C\$334<>"#N/A",ROW(Pivots!\$C\$23:\$C\$334),""),"A")))

### Setting up the chart

Insert a new stock chart

1. Go to tab "Insert" on the ribbon.
2. Select cell range E23:G46 on your worksheet
3. Click "Other Charts" button
4. Click "High-Low-Close" chart button
5. Right click on the chart you just created
6. Click "Select Data.."
7. Select "Series1" and click "Edit" button
8. Change Series values to: =Pivots!MHigh200
9. Click OK
10. Select "Series2" and click "Edit" button
11. Change Series values to: =Pivots!MLow200
12. Select "Series3" and click "Edit" button
13. Change Series values to: =Pivots!MClose
14. Click OK
15. Click "Edit" button below Horizontal (Category) Axis Labels
16. Change Axis Label range to: =Pivots!Dates200
17. Click OK
18. Click OK

Reverse horizontal axis

1. Right click on horizontal axis
2. Click "Format Axis..."
3. Enable "Categories in reverse order"
4. Click OK

Add "High" pivots to chart

1. Right click on chart
2. Click "Select Data..."
3. Click "Add" button below Legend Entries (Series)
4. Type Pivots!BPivotlines in Series values
5. Click OK button
6. Click Ok
7. Go to tab "Layout" on the ribbon. If you can't find it make sure you have the chart selected
8. Select chart element "Series 4" in the drop down list
9. Click "Format Selection" button
10. Click "Secondary Axis"
11. Go to "Marker Options" on the menu to the left
12. Click Built-in and select a type and size
13. Click Close button

Reverse Series 4

1. Select Series 4 on the chart
2. Go to tab Layout on the ribbon
3. Click "Axis button, click "Secondary Horizontal Axis" and then click "Show Left to Right"
4. Right click on the secondary horizontal axis
5. Click "Format Axis"
6. Click "Categories in reverse order"
7. Click Close
8. Select secondary horizontal axis again and delete
9. Select the chart legend and delete
10. Select Series 4
11. Go to tab Layout on the ribbon
12. Click button "Axis"
13. Go to secondary vertical axis
14. Click None

1. Repeat above steps described in "Add High pivots to chart" and "Reverse Series 4" with Low Pivots, Buy and Sell signals
2. Here are the named ranges:
Low Pivots - SPivotlines
Sell - SellPivots

Change the marker type

1. Go to tab "Layout" and Select Buy (Series 6)
2. Click "Fomat Selection"
3. Go to "Marker Options" and choose a Built-in one
4. Now select Sell (Series 7)
5. Click "Format Selection" button
6. Go to "Marker Options" and choose a Built-in one

1. Go to tab "Layout" and Select Buy (Series 6)
2. Click "Data Labels" button
3. Click "More Data Label Options.."
4. Make sure "Series Name" and "Above" is enabled, see picture above.
5. Click Close
6. Repeat above steps with Sell (Series 7)

Change minimum and maximum value on vertical axis

1. Right click on vertical axis
2. Click "Format Axis"
3. Change minimum value to 600
4. Change maximum value to 1600

Change bar color

1. Go to tab "Layout" on the ribbon
2. Select "Series 3"
3. Click "Format Selection"
4. Go to "Marker Line Color"
5. Click "Solid Line" and pick a color, I chose black.
6. Click Close

Resize chart