How to calculate and plot pivots on an Excel chart

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, especially 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 above 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.

Back to top

2. Formulas

Here is the sheet that made it possible, you can press with left mouse button on to enlarge.

The user defined array formula in cell C23:I355:

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:

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

Formula in cell B23:

This formula returns the first letter from a month.

Formula in cell K24:

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

Formula in cell L24:

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

Formula in cell M24:

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

Formula in cell N24:

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

Formula in cell O24:

Checks if the lowest price point is below sell pivot. IF TRUE display 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.

Back to top

3. 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

MHigh200

MLow200

BPivotlines

BuyPivots

Dates200

Sellm200

SellPivots

SPivotlines

Back to top

4. 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. Press with left mouse button on the "Other Charts" button.
4. Press with left mouse button on the "High-Low-Close" chart button.
5. Press with right mouse button on on the chart you just created.
6. Press with left mouse button on "Select Data..".
7. Select "Series1" and press with left mouse button on the "Edit" button.
8. Change Series values to: =Pivots!MHigh200
9. Press with left mouse button on OK.
10. Select "Series2" and press with left mouse button on the "Edit" button.
11. Change Series values to: =Pivots!MLow200
12. Select "Series3" and press with left mouse button on the "Edit" button.
13. Change Series values to: =Pivots!MClose
14. Press with left mouse button on OK.
15. Press with left mouse button on "Edit" button below Horizontal (Category) Axis Labels.
16. Change Axis Label range to: =Pivots!Dates200.
17. Press with left mouse button on OK.
18. Press with left mouse button on OK.

Back to top

5. Reverse horizontal axis

1. Press with right mouse button on on the horizontal axis
2. Press with left mouse button on "Format Axis..."
3. Enable "Categories in reverse order"
4. Press with left mouse button on OK

Add "High" pivots to chart

1. Press with right mouse button on on chart
2. Press with left mouse button on "Select Data..."
3. Press with left mouse button on "Add" button below Legend Entries (Series)
4. Type Pivots!BPivotlines in Series values
5. Press with left mouse button on OK button
6. Press with left mouse button on 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. Press with left mouse button on "Format Selection" button
10. Press with left mouse button on "Secondary Axis"
    
11. Go to "Marker Options" on the menu to the left
    
12. Press with left mouse button on Built-in and select a type and size
13. Press with left mouse button on Close button

Reverse Series 4

1. Select Series 4 on the chart
2. Go to tab Layout on the ribbon
3. Press with left mouse button on "Axis button, press with left mouse button on "Secondary Horizontal Axis" and then press with left mouse button on "Show Left to Right"
4. Press with right mouse button on on the secondary horizontal axis
   
5. Press with left mouse button on "Format Axis"
6. Press with left mouse button on "Categories in reverse order"
7. Press with left mouse button on 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. Press with left mouse button on button "Axis"
13. Go to secondary vertical axis
14. Press with left mouse button on None

Back to top

6. Add remaining series

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
   Buy - BuyPivots
   Sell - SellPivots

Back to top

7. Change the marker type

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

Back to top

8. Add Data Labels

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

Back to top

9. Change minimum and maximum value on the vertical axis

1. Press with right mouse button on on vertical axis
2. Press with left mouse button on "Format Axis"
3. Change minimum value to 600
4. Change maximum value to 1600

Back to top

10. Change bar color

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

Back to top

11. Resize chart

Back to top

Get the Excel file

Stock-market-trends-PIVOTSv3.xlsm