Hello this is Mark and welcome to this video on how to calculate PSAR indicator using Excel.
So we've got a fairly typical situation with the PSAR on the screen.
We have a downtrend here.
Followed by an uptrend.
During the downtrend, the PSAR is predominantly above the price and during the uptrend, the
PSAR is below the price.
I'm going to show you how you can calculate this indicator for yourself using Excel.
But if you are not interested in actually learning how to calculate it.
I've got a shortcut.
I have an eBook available in the Amazon Kindle Bookstore.
Now there is a link on the screen if you would like more information.
And in this I show how to calculate the PSAR, MACD, RSI, ATR, 21 Technical Indicators and
it comes with a free spreadsheet so you can start straight away testing indicators and
backtesting strategies.
Now, to get on with showing you how you can calculate it for yourself I'm going to put
in, based on this historic data some titles.
We have the Extreme Point.
Acceleration, We've got here the PSAR minus the EP*Acc.
This is the amount the PSAR will change.
We have here and initial PSAR and a PSAR and a trend.
Now I'll just briefly explain why we use an Initial PSAR.
The reason is that the PSAR unlike most other technical indicators changes directions as
soon as the point that would have been the PSAR, so to extrapolate down to here.
As soon as this point is touched by the price, we immediately flip over here.
So this means we have a value for the PSAR which is current providing it's not touched.
But when it is touched we have to use a different value.
OK, so this is going to be the actual PSAR and we also have to identify the trend.
OK, so the extreme point is always on the opposite site of the price of the PSAR.
So these dots are the PSAR, the Extreme Point is the lowest point on the other side, so
from this point onward this is the Extreme Point.
We remain in the downtrend so this becomes Extreme Point, then this point and sure enough,
this is the new PSAR from this Extreme Point.
So, I'm going to start it off by just saying it's the low.
If we want to take the reverse of that as the PSAR.
This is just an initial value, it doesn't matter which direction you start off in because
the PSAR will quickly correct itself.
So we'll take the high point.
Well we've decided we are going to start off in a downtrend.
So I'm going to refer to this as falling.
The next thing I'm going to look at is the acceleration.
You've probably seen these two numbers before.
0.02 and 0.2.
Now 0.02 you might have heard explained before is the initial acceleration factor and the
increment.
And 0.2 is the maximum acceleration.
So the acceleration factor is how much we are going to move the PSAR.
We are going to start of at 0.02, if the trend is continued, in other words, if we make a
new extreme point the acceleration factor is increased.
And it is increased by the increment.
So we start off at 0.02, we go to 0.04 and so on.
Until our maximum, which is 0.2.
So anyway we can start off with as simple one at the beginning, 0.02.
And we can also now calculate the amount we are going to move, which is our PSAR value
minus the Extreme Point multiplied by our acceleration factor.
OK, so we've done the first line.
Now we are going to have to look at the logic the PSAR.
So we are going to start a formula with = and we are going to use and IF statement.
So I'm going to put =IF So if we are in a downtrend, if the preceding trend is a downtrend
then take this and subtract this.
So we've already said that this is the amount that the PSAR is going to move.
So if we are in a downtrend we are going to move the point, this further forward.
However, there is another rule for the PSAR which is that when we are in a downtrend our
PSAR cannot be lower than the two preceding high points.
And I'm going to program that and put this in Excel using MAX of either that, this or
the previous day's or period's high.
The opposite situation, if the preceding trend is an uptrend if its rising, this time we
work out the minimum of this minus this.
The low and the previous low.
OK, so there we have the calculation for the initial PSAR.
Now we can work out the actual PSAR for this period.
We do that by using an IF AND statement.
In fact there are 4 different scenarios that can occur for the PSAR.
And we are going to use 4 different IF AND logical statements.
The first of these is if we are in a downtrend.
So if L5 = "falling" So what we've said here is, preceding trend is a downtrend and our
high is not higher than our initial PSAR.
So we know what to do in this situation which is to use that and make that the actual PSAR
value.
now from that we can just take the opposite and say we are in an uptrend.
L5 is rising, now this time we've checked that the low is still greater than the initial
PSAR.
And if that is the case well again we can just use the calculated value and make it
the actual value.
We have 2 other scenarios that we need to account for.
The first of these is, again if we are in a downtrend.
this time we have to look at whether or not the high point, the price, has penetrated
or gone past the initial PSAR.
And if it has then we have to flip over.
We are going into a different trend, we are changing the direction, and we do that by
taking the Extreme Point.
And then we can do
the reverse of that.
So this time we've worked out if we are in an uptrend, the low is lower than or equal
to the initial PSAR.
In which case we take the Extreme Point again.
So there we go, we've calculated the 4 different scenarios which make up the PSAR.
OK now we can have a look at the trend.
OK, so we are just doing a very simple IF Statement here.
If the closing price is greater than the PSAR, then the trend is falling.
OK, so now we have to back and have a look at the Extreme Point.
How do we calculate this?
Again we are going to rely on the trend.
So now what we've said is when the price is falling.
So here we have the price is falling.
We now we've made a new Extreme Point if we've gone lower than the previous value.
So we are in a downtrend, we are looking at the minimum value of either this or our new
low.
If we are rising, if we are in an uptrend, now we have to look at the MAX of either this,
the previous value or our new high.
So there we have looked at all the different scenarios.
We've looked at if we are falling or if we are rising.
It will only be one or the other.
OK, getting close now.
Now we need to workout when we have to change the acceleration factor.
So we know we are initially 0.02.
And we know that if we make a new Extreme Point and the trend remains the same then
we need to increase the acceleration factor up to a maximum of this value.
So we are = IF AND What have I said here?I've said that if the trend remains the same, so
if L6=L5 we haven't changed direction.
And then we are going to check at.
We are going to assume in the first one that we have made a new Extreme Point that we have
gone lower in a downtrend.
And we need to check that this remains lower than our acceleration our maximum rather.
If all these are the case then we take our existing, our current acceleration factor
and we add to it the increment.
OK, so the next one we need to look at is if we have a trend.
So we have a trend, so we are saying the trend is still the same, however, our Extreme Point
has not changed.
In this scenario, what do we do?
Well we keep the acceleration factor the same.
OK, so what is the other option that could happen.
Well the other option is that we could have a change of trend.
So in this case the trend has changed, these are not the same values.
And in which case well we know we can always use our initial value.
0.02 in this case.
In all other situations we use the maximum.
And then we can close it.
And there we have it, we can just drag this down here.
And now we have our, the amount that is going to move by.We can highlight these 6 cells.
Double-click here and then we have all the calculations below.
Now, as I said, if enjoyed this video but you are not interested in doing the calculations
yourself please check out my eBook.
Available in the Amazon Kindle Store.
And please Subscribe if you would like more videos of this type about calculating technical
indicators and backtesting trading strategies.
And for more information about the financial markets and using Excel.
Please go to www.tradinformed.com