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