Calculate Double Top Chart Patterns Using Excel

Last Updated on November 28, 2022 by Mark Ursell

Double Top and Double Bottom are classic chart reversal patterns. In this article, I show how you can use Excel to calculate these chart patterns.

Being able to program the patterns into Excel means that you can test them and find out which patterns are most useful in a trading strategy.

Chart Patterns

Many traders are attracted to chart patterns. Chart patterns are a way to make sense of price action and to identify market turning points.  Double tops and bottoms have been used by traders for years. The reason they are so popular is that the psychology of the pattern can be easily understood. They show the market trying and failing twice to get through an area of strong support or resistance.

The double top is bearish pattern signifying that the market might fall. The pattern occurs when the price hits a support level and bounces lower. After the price has fallen from the support level it subsequently rallies back up to the support. The price is still unable to break through support and it falls back.

The double bottom is a bullish pattern signifying that the market might rise. In this pattern, the price falls to a support level and bounces back. The price then falls again to the support level but is unable to get through the support level. It subsequently rises higher.

21 More Technical Indicators

The calculations shown on this page are taken from my eBook: 21 More Technical Indicators. The eBook is available in the Amazon Kindle Store. To get more information about the book check out: Get Better Trading Results using Technical Indicators in Excel.

Calculate Double Tops and Bottoms in Excel

Chart patterns are easy to spot on a chart. But they are much more difficult to program in Excel. I recommend starting with a simple version of the chart pattern. Once this simple version has been successfully programmed it is possible to refine it until it perfectly matches the patterns you see on the chart. You can see me demonstrating this spreadsheet in the Calculation Video at the bottom of the page.

In this method, I calculate the double top in two columns. The first column calculates the left top and the second column calculates the right top.

The High price is in column D. The Low price is in column E. The Close price is in column F. The Left Top calculation is in column G. The Right Top calculation is in column H.

Left Top

The left top should be:

• Higher than the previous 70 bars.
• Lower than the following 4 bars.

When a left top is formed the spreadsheet will store the price value. But in our calculations, we need to have a way of resetting this value. The left top value will be reset when:

• The current close is higher than the top.
• A right top has formed.
• The left top has lasted for a set period of time and no right top has occurred.

Cell G80 =IF(AND($D76>MAX($D77:$D80),$D76>MAX($D5:$D75)),$D76,
IF(OR($F79>G79,G79=G5),,G79))

Right Top

A right top can only occur when we have had a left top. The right top should be:

• Higher than previous 4 bars
• Lower than following 4 bars
• Within a set percentage of the left top price.

Cell H80 =IF(AND(G79>0,G78>0,$D76>MAX($D77:$D80),$D76>MAX($D72:$D75),
$D76>G79-(Results!$C$20$F79),$D76<G79+(Results!$C$20$F79),F80<D76),”double top”,””)

Video

Watch the accompanying video to see me demonstrate the spreadsheet.