Author: Oscar Cronquist Article last updated on February 08, 2023

 

Stock market trends pivots

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

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.

Back to top

2. Formulas

stock chart - pivots

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:

=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 values. If TRUE change the value to the new high, if FALSE repeat the 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 values. If TRUE change the value to the new low, if FALSE repeat the 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 the highest price point is above buy pivot. IF TRUE display buy pivot price.

Formula in cell O24:

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

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

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

=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")))

BuyPivots

=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")))

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.

stock chart - pivots1

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

stock chart - pivots2

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

stock chart - pivots6

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

stock chart - pivots8

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.."
    stock chart - pivots9
  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

stock chart - pivots11

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"
    stock chart - pivots12
  5. Press with left mouse button on "Solid Line" and pick a color, I chose black.
  6. Press with left mouse button on Close

stock chart - pivots13

Back to top

11. Resize chart

stock chart - pivots14

Back to top