Page Loading ...

How to Calculate the MFE and MAE in Excel

How to Calculate the MFE and MAE in Excel
    Watch the video

    click to begin


    Hello this is Mark from 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
    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