How to Calculate the MFE and MAE in Excel

click to begin

Youtube

Hello this is Mark from Tradinformed.com and welcome to this video on How to Calculate the
Maximum Favorable Excursion and Maximum Adverse Excursion using Excel in this
video I will show you how you can calculate them in an Excel spreadsheet
these metrics and also how you can make them a bit more useful by plotting them
in a chart so like most people I tend to stick to a favored few number of metrics
a number of ways of analyzing a trading a strategy and you can see on the screen
at the moment these are some of my favorites in particular: drawdown, win
percentage, and profit factor as well as the capital graph but it is always
useful to think about other metrics, other things that could be influencing
not just how our trading strategy performs but how it makes us feel and
MFE and MAE are a way of analyzing each individual trade we want to limit our
adverse excursion because on a day-to-day basis if we have trades that
are going 5 - 10 % into loss before maybe improving or even turning into a profit
this might be difficult for us to handle and we may find that we end up
overriding our strategy making mistakes likewise if we're getting a particularly
large favorable excursion maybe we can capture more of this profit
and equally when we're trading it and we see what looks like a windfall profit we
might be tempted again to override our strategy and grab the profits so it's a
good thing to work out and I'm going to show you how you can do this quite
easily in excel on the screen at the moment this is a Tradinformed backtest
model and it's a way that anybody can test their own trading strategies it's
built in Excel and it uses Excel spreadsheet formulas this particular one
in fact is based on a video that I recorded recently it is a simple swing
trading strategy. There's a link to that video on the screen if you want to have
a look at that and find out more about this particular spreadsheet. So in this
analysis all I've done is I've created some new columns to calculate the two
metrics and I'm going to start off by using an IF Statement which is the most
basic building block of all logic that we use in Excel and the first thing that
I want to check is whether there is a trade running because if there is not a
trade running then we don't want to do anything
so in a Tradinformed back test model we always look at this column
here because this tells us if we have a trade or not so this column is greater
than zero then we do have a trade so we want to to do something here and what
we're going to do is calculate first of all the maximum favorable excursion
based on the intraday high point so I'm going to go over here and I'm going to
take the high points of this particular day and I'm going to subtract from that
our entry point which again is always here in a Tradinformed backtest model
now because I'm doing this over a number of years I'm going to use percentages to
calculate the metrics here so I divide this value by the entry point
so there we have the basic form of the formula but the way I'm going to
calculate this is for each individual trade I want to get the final version as
the trade is closing so I'm gonna just add a little bit more logic in the form of
another IF Statement which I'm going to sneak in here so equals if this that
we've just calculated for our metric on this day is greater than the previous
value then what I want it to do is use this value and if it is not greater than
that value what I want it to do is take the previous value and close up that IF
Statement and press Enter now we have got formula here double
click here and it copies it down to all the cells below and you'll notice what
I've done if I scroll down here is that the maximum favorable excursion only
gets higher as the trade goes on or rather it only gets higher or it stays
the same I'm going to use exactly the same formula but in reverse for the
adverse excursion and I'll show you how I calculated it first of all never do
the work twice if you don't have to so we can see which bits of this we can
copy from here so we can just put dollar signs which are very useful if you don't
use them there are a way of freezing a particular cell, particular row or column
so I'm gonna put dollar signs on the columns but I want to stay the same
and then I can just copy it across like so and all I need to do then is change
the direction here now we can see we have the maximum adverse excursion both
of these is practically identical formula I just click in here just to
explain what I've done this time we're using the intraday low so the intraday
low how far this is away from our entry points expressed as a percentage is the
value that we have here I said at the beginning of this video I want to make
this more useful it is nice having it in a formula here but what we really want
is to have it as a trade list or rather that's the next step so I've already
done that I've used put some new columns in a in the trade analysis part of this
Tradinformed spreadsheet all this formula is doing is pulling through the
value of the two metrics here and now we can compare it as a row entry price, exit,
profit and loss and we can compare each one directly to our MFE and MAE we can
do all sorts of things we can analyze this we can look at how profit and loss
compares to our these two metrics directly in this spreadsheet but the
next thing I'm going to show you is how we can put this into a chart here so
I've got a chart if I click on it here you can see it is showing the cumulative
percentage gain and loss of this trading strategy and I can quite quickly put
this information into this and we can have a visual guide to what's going on
so very easy to add more data to a Excel chart and what I've done here is I've
already created two named ranges this is not an essential step we could easily
just add it directly but if you want to have
a spreadsheet that is adaptable when you add more data to it you want to create
named ranges because these will be dynamic and adjust so I've created a
named range this is just basically here I'll just show you what it is this is
the maximum favorable excursion you can see if I click on here you can see this
is the range okay now we can add that to this spreadsheet here and I'm gonna call
it MFE delete all this we want this tab and I'm
just gonna put MFE there add another one and this one gonna call MAE, delete
this stuff click on this tab and okay so we have first of all what we had before
and secondly at the bottom here the lines showing the two new metrics but
they're obviously not much use like this so what we want to do then is we want to
have a look at these in more detail now we can format this data series the first
thing we might want to do is plot them on a secondary axis I can get through
to this other one I can plot that on here now okay well we have something
useful and I can make it bigger here rearrange these a bit and so we can see
what we've got - is a bit more useful so I can also do is have a look at the
different options here we can make this
change the chart series type so at the moment it's defaulted to a line type but
I think we might want to have this as a scatter plot
got a pretty useful thing there the only thing that I do notice is that our
greens are going down and our Reds are going up and just for the sake of
prettiness we might well want to do it the
other way round and we can see we've got
something that looks a lot better here
so there are loads of things that we can do with Excel and we can do that to make
our information and analysis much clearer to us and visually we get a
different sense of what is going on we can see here I can tell you this is the
financial crisis and we can see that we've got a lot of bad trades during
this time we can see this whole section here but equally we had some quite
staggering trades as well on the other side so we can see at this time of
maximum stress in the market we had good and bad trades most of the time we're a
lot calmer and we are clustering very closely around the mean so there we have
it MFA and MAE plotted in Excel I've shown
you the calculations that you can use and also how you can put this
information in a trade list and also plot it on a chart now I hope you found
this analysis useful please remember if you liked this video please hit the Like
button also Subscribe to this channel for more videos like this and for more
information about trading the financial markets and analyzing the markets please
go to www.tradinformed.com
A Simple Swing Trading Strategy Followup - reupload How to Change the Backtest Strategy How to Trade the S&P 500 using VIX Volality A Simple Renko Strategy Using Excel How to Import and Store Live Price Data in Google Sheets Backtest of a Double Top & Double Bottom Trading Strategy A Simple Way to Test a Candlestick Trading Strategy How to Calculate Renko Charts How to Trade Bitcoin using a Breakout Strategy A Simple Way to Use Excel to Set Up a Monte Carlo Test

How to Calculate the MFE and MAE in Excel

Latest Posts