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

stock price pivots

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.

S&P500 pivots

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.

stock chart - pivots

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:


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 value. If TRUE change the value to the new high, if FALSE repeat previous high value.

Formula in cell L24:


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:


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.

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.



















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

stock chart - pivots1

Reverse horizontal axis

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

stock chart - pivots2

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
    stock chart - pivots3
  9. Click "Format Selection" button
  10. Click "Secondary Axis"
    stock chart - pivots4
  11. Go to "Marker Options" on the menu to the left
    stock chart - pivots5
  12. Click Built-in and select a type and size
  13. Click Close button

stock chart - pivots6

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
    stock chart - pivots7
  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

stock chart - pivots8

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

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

Add Data Labels

  1. Go to tab "Layout" and Select Buy (Series 6)
  2. Click "Data Labels" button
  3. Click "More Data Label Options.."
    stock chart - pivots9
  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

stock chart - pivots11

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"
    stock chart - pivots12
  5. Click "Solid Line" and pick a color, I chose black.
  6. Click Close

stock chart - pivots13

Resize chart

stock chart - pivots14

Download excel *.xlsm file

Stock market trends - PIVOTS.xlsm