Calculate Fibonacci Retracements Automatically

Last Updated on March 9, 2021 by Mark Ursell

This article shows you how to calculate Fibonacci Retracements in Excel.

Like many traders, I love using Fibonacci Retracements on my charts. They are an excellent way to make sense of what the market is doing. They can be used for entry points, exit points and setting stop-loss levels.

However, it is easy to use these retracements without knowing whether one level is better than another. Different levels might perform better based on the type of strategy and the market being traded

This article is for traders who are interested in quantitatively analysing their trading strategies to identify the best possible trading levels.

My preferred platform for analysing the markets is Excel. In this article I show a method for calculating Fibonacci Retracements automatically in Excel.

This article is the first in a two-part series. The next article will show how to use Fibonacci Retracements in a trading strategy.

Video – Fibonacci Retracement Calculation

For those of you who would like a visual explanation, I have recorded a YouTube video demonstrating the spreadsheet.

Fibonacci Retracements

I have previously discussed How to Calculate and Use Fibonacci Retracements. Check out this article if you would like a refresher on Fibonacci levels and calculations.

Market Trend

We cannot calculate a Fibonacci retracement without first knowing the dominant trend. We need this because the retracement is always relative to this trend.

The chart to the left shows a downward trend. In this case we are looking at a retracement in the upward direction. This retracement stops at the 38.2% Fib level and the market continues back down.

How to Identify the Trend using Excel

We can tell the direction of the trend simply by looking at a chart. However, when we are using Excel we need to have a way of calculating the trend direction. One of my favourite methods is the slope of a linear regression line.

The linear regression line is a statistical tool that shows the best fit straight line through a data series. Many traders will be familiar with linear regression lines because they are the central line of Standard Deviation Channels.

In this case I am only interested in the slope of the line. If the slope is positive, I assume the market trend is upwards. If the slope is negative, I assume the market trend is downwards.

Significant Highs and Lows

Fibonacci Retracements are drawn from a significant high to a significant low. Like the trend, significant highs and lows are easy to spot on the chart. I look for high and low points with a large amount of blank space to left of them.

How to Calculate Significant Highs and Lows

To calculate them using Excel we can use the MAX and MIN functions. The formula =MAX(highs) identifies the high point for a set number of periods. The formula =MIN(lows) identifies the low point for a set number of periods.

How to Calculate Fibonacci Retracements

In this example I am using Excel to calculate the retracements. You can download this Excel file for Free. Simply sign up for the Tradinformed Newsletter and you will receive an email with the link.

I am going to use the example of an upward trend. You can see the spreadsheet in the image above.

Recent High

In an upward trend, the high point is a 0% retracement and the low point is a 100% retracement. One of the defining traits of an upward trend is a market that is making new highs and NOT making new lows. So the formula that we will use in Column H is:

=IF(I26<I25,,IF($D25>MAX($D6:$D24),$D25,H25))

The first IF statement checks whether we have made a new low. If so then set the value to 0 because this is probably not a long trend.

The next IF statement checks whether the previous period’s high is greater than the recent highs. If so then it becomes the new high point. If not then take the previous value.

Recent Low

The formula for the recent low is:

=IF($E25<MIN($E6:$E24),$E25,I25)

The IF statement checks whether the previous period’s high is lower than the recent lows. If so then it becomes the new low point. If not then take the previous value.

How to Calculate the Fibonacci Retracement Level

I am using an absolute reference cell to contain the Fibonacci Retracement percentage. In this spreadsheet it is in Cell J2.

The formula for the Fibonacci level is:

=IF(AND(N59>0,I59>0,H59>0),$H59-(($H59-$I59)*$J$2),)

This formula is really quite simple. It checks whether we have a high point, a low point and whether the linear regression line is positive. If so then we take the distance between the high point and the low point and multiply it by the Fib Retracement percentage. Then we subtract this from the high point.

How to Calculate the Linear Regression Line

Excel has a built-in function to calculate the linear regression. The function is LINEST. To get the slope of the line we combine it with INDEX to get the formula =INDEX(LINEST(prices),1). In this example, I am using the closing price for the previous 50 periods. The formula is:

=INDEX(LINEST(F10:F59),1)