Hello, this is Mark from Tradinformed.com and welcome to this video on How to Calculate
Fibonacci Retracements Automatically Using Excel.
This is the first of a two video series of videos.
And in this video I'm going to look at the Fibonacci sequence and use this to calculate
retracement levels.
And I'm going to look at the charts to show how these Fibonacci retracements should look.
I'm going to show how you can calculate these automatically using Excel.
In the second video in this sequence I am going to look at putting all this together.
And creating a trading strategy.
So Fibonacci numbers, the Fibonacci sequence, the most popular mathematical sequence of
numbers out there.
Each number is the sum of the two preceding numbers.
You can calculate this quite easily using Excel.
0,1,1,2,3,5,8,13... etc.
These numbers are quite familiar to most traders.
We can also use these numbers to calculate our retracements levels.
I'm going to do this by taking one of the numbers in the sequence and dividing it by
the number 1, 2, 3 places ahead of it in the sequence.
And if I do this again this time I divide another number by the number two places ahead
of it in the sequence.
I get 38.2%.
And finally I am going to divide this number by the number 1 place ahead of it in the sequence.
So we've got there the three key Fibonacci retracement levels.
And we need to add to this the 50% level, which is another commonly used number.
And we have probably the four most significant Fibonacci retracement levels.
What do they look like on the chart?
We have here an upward sloping trend.
As a trend trader I am looking for trades to the upside.
Get a Fibonacci retracement, stick it on here and we can see that in this case, we go from
a significant low to a significant high.
And in this case we have a retracement.
A classic retracement, we get exactly, in this case, almost exactly to the 50% level
and then we continue in the direction of the previous dominant trend.
And this works exactly the same if we are looking at short trends.
I can show you here, this time we go from a significant high to a significant low.
In this case we are identifying the trend to the downside.
And we can see that the price retraced upwards, this time it retraced very nicely to the 38.2%
Fibonacci level.
Before continuing in the original direction of the dominant trend.
Now there are a few things that we need to get out of the chart before we can calculate
this automatically.
Now the first of these is, we can see quite easily on the chart what the dominant trend
is.
Particularly when we are doing it in this case, when we are looking at prices that have
already occurred.
But what would we do if we didn't know what the dominant trend is, or we're not sure how
to identify it.
The way I'm going to demonstrate in this video is using another technical indicator.
Which is really a statistical tool actually.
We've got here standard deviation channel and what I'm looking at is the central line
here.
Now the central line is a Linear Regression line.
The linear regression is really a statistical tool to identify what is the best fit straight
line through all this data.
So my view would be that if the linear regression line is pointing upwards, we are in an uptrend.
And we should be look for retracements and trends to the long side.
Whereas if I shift this over here, the market had changed by this time.
We are looking, the linear regression line is pointing downwards.
We should in this case be looking for trades to the short side.
The other significant piece of information that we can identify quickly by looking at
the chart.
High - Low.
But we need to identify what makes these significant.
Well visually we can see that there is a big blank space to the left of this point here.
And equally there is quite a big blank space to the left of this here.
So we could say that these are significant highs and lows.
When it comes to calculating these in Excel, we need to be specific.
And I'll show you how I've done that in this example.
So here is my spreadsheet calculating Fibonacci retracements.
I should say before I start looking at the formulas that this spreadsheet is available
for free to download.
If you'd like to have a look at Tradinformed.com.
There are lots of resources and information there about, particularly about trading analysis
using Excel and also the financial markets in general.
You want to click on the link and sign up for the Tradinformed newsletter.
You will get an email that will show you how you can download this spreadsheet absolutely
for free.
So, how have I calculated it?
Well , I'm using colour here to identify our long Fibonacci retracements in green and the
short ones in red.
So I have the linear regression slope here as well.
So how do we go about starting with the long linear regression analysis?
Well the first thing is that this column, the recent highs is the most significant.
This is a thing that we always need to be aware of.
Are we in a long trend?
Are we making new highs?
We can see we are in a long trend here and Yes, we are making new highs.
This is almost the very definition of, are we in a Trend?
Are we making new highs?
So the very first IF Statement is checking if we've made a new low.
If we've made a new low, we are arguably not in a trend at all.
So we're basically making the recent high a blank.
And this is what the first part of the formula says.
It says that if we've made a new low, then enter a blank here.
So if not, we are going to look at what the data, or the latest data says.
Basically, we are looking at this high here.
This most recent high of the immediately preceding the period.
Is greater than the MAX which means the highest value of the preceding number of bars.
We can set this to whatever value we like but it is basically we've got a new recent
high.
If we have made a new recent high, then we want to enter this value as the new recent
high.
This is good we are in a long trade, and we've made.
A long, market that is going higher.
We've made a new high.
Great, we want to record this value.
If we've not made a new recent high, everything else being equal.
We just want to take the previous value.
OK, so lows are also significant because we need to go.
We need to retrace from a low to a high, so we need to have a value here as well.
We've got a slightly simpler formula here.
And all we are doing here is pretty much the reverse of for the highs.
We are looking the lows of, if I double-click in here we are looking at the lows column.
If we've made a new recent low then we enter the value of this recent low.
This is what the formula is saying.
If not, then we take the previous value.
OK, so this third column is actually the value we are going to use.
Now, I'm using a variable column here.
I've set it to 38.2% but I could equally set this to 50%, 61.8% or whatever value we want.
I'll set it back to 38.%.
And the second thing we have to take awareness of is what direction is the linear regression
slope?
Now I've used Conditional Formatting here to colour this in this pinky-red colour.
If we are sloping downwards.
If I scroll down further.
You can see that eventually we start sloping upwards and we go green.
So we have a nice visual clue to tell us, should we be looking for trades to the long
side?
Or should we be looking for trades to the short side?
OK, now we have a formula that, in the formula bar here that may look complicated but in
fact it is very simple.
We go an IF-AND Statement.
We are looking for all of these conditions to be correct.
To be True.
And all we are saying is that we need a new recent high, we need a new recent low.
And we need the linear regression slope to be pointing upwards.
If all these are the case then we calculate, in here, we calculate the 38.2 Fibonacci retracement
level.
Exactly, the formula is the high point, minus the percentage, 38.2% difference between the
high and the low.
If these conditions aren't true then we leave this as blank or 0.
So there we have it, this was quite straight forward, we could look at the example of the
short ones but it is exactly the same but done in reverse.
If you'd like to download this formula as I've said just sign up at Tradinformed.com
for the newsletter.
And please look out for the new video that I'm going to be posting, showing how we can
use this information as part of a trading strategy.
And for all other information about the financial markets, about using Excel and backtesting
using Excel please go to www.Tradinformed.com