106

Trading Range Breakout on Daily timeframe: Excel Sheet

Most of the traded securities in capital market moves in a range for around 80% of time and trends higher or lower for 20% of the time. And when it breaks this range with high volume, a very good buying or selling opportunity is signalled. Since the inception of technical analysis, range breakout trading systems coupled with money management are considered very useful and profitable. This excel sheet will illustrate one such range breakout setup. The logic behind this setup was originally developed by Sir Tony Grabel.

Please find the AFL code of this same strategy here.

Strategy overview:

As per this range breakout method, a trade should be initiated when the price moves higher or lower than the sum of current day’s opening price and 5 days average price range.

Buy Logic: Current Price>Open Price+Average price range of last 5 days
Sell Logic: Current price<Open Price-Average price range of last 5 days
Target and Stop Loss: Indicated in Excel Sheet

Excel Sheet:

Worksheet Name:

Breakout Trading

Inputs:

  1. DATE,OPEN,HIGH,LOW,CLOSE: Open,High,Low,Close values of last five days for the selected security.
  2. Today’s Open: Today’s price at market open.

Outputs:

  1. RANGE: Average price range of last 5 days.
  2. BUY ABOVE: The system signals to buy when price crosses above this value.
  3. SELL BELOW: The system signals to sell when price crosses below this value.
  4. TARGET 1,2,3,4,5,6: Different profit booking targets based on your money management rules. It’s advisable to book partial profit at each target level.
  5. STOPLOSS: Price level at which one should exit if trade goes in opposite direction.

Screenshot:

Range Breakout Excel

Download Link:

Trading Range Breakouts on Daily timeframe

Summary:

We have tested this trading system on many different securities. It seems highly profitable on liquid stocks and indices like Nifty, Banknifty, SBI, ITC etc. For these, the average trading volume is usually high compared to others. There are many different variations of this strategy on intraday timeframe. We’ll try to post them all going forward. Kindly download the excel sheet and explore this strategy yourself. Fell free to post any queries.


Spreadsheets for Success

Related Posts

106 Comments

    • Hello Francis,
      We don’t have any backtesting result yet. We’ll try to convert this into AFL and post backtesting results soon.

    • Hello Ramesh,
      This strategy works on all instruments with heavy volume and liquidity, and crude oil futures pass this criterion. So you may try this in Crude oil futures, but we would recommend doing paper trading for few days.

  1. sir , i just filled the prices & some fields H-O ,,O-L ,,MINIMUM show blank ,,any reason

  2. Sir , in this can we trade vice versa if SL is hit !! Like for any share buy above level is 400 & SL is 392 ,, so can take sell side position if it breaks 392 or 391 ??

    • in past comment i forgot to mention that buy above level is 400 & buy is activated & later it hit SL ,,SO sl HIT MEANS WE CAN TAKE SELL SIDE POSITINS ??????

    • Hi Kuldeep,
      No, you should not take opposite position when SL is hit. It’s not a stop and reverese strategy.

  3. This ks a brilliant website .. Keep rocking 🙂 Pretty uselfull stuff :

    Ravi

  4. Sir,
    Good strategy. Can you please help me for how to trade using this xl sheet. Wheather going to trade in the opening of market otherwise any timeframe maintain for going to take trade. And also after take trade, suppose sell side is break we will take sell trade then it will be hit the stoploss without hit any target. In this case what can I do? Take the buy order on buy side or wait. Please tell me.
    You also said that in summary “There are many different variations of this strategy on intraday timeframe. We’ll try to post them all going forward” Please post this different variations of this strategy on intraday timeframe.
    Thanks & regards

    • Hi,

      Please see my replies below:-
      1) For Buy trade should be entered when Current Price>Open Price+Average price range of last 5 days, For sell trade should be entered when Current price

  5. Respected Sir how can I know average price of 5 day range give with example of nifty or any script thanks good morning

  6. Please post some of the excel sheet related to pair trading and Vega ratio trading

  7. Sir,
    If my buy sl Hit and sell activates automatically.
    my doubt is whether I want to go for sell r close the trade with loss.
    and if market opens near 3 trgt r 4tg how should we trade.
    r take enty.

    pl guide us.

    • Hi Senthil,

      If your buy sl Hit and sell activates automatically, you should Short. If market opens near Target 3 or 4 you should keep away from Trading for that day.

  8. dear sir,
    should we enter open price from chart or from the nse website,especially in case of nifty.?

  9. 5 DAY AVERAGE IS ENOUGH FOR THE TRADING? IN SOME WEBSITE I SEE 10 DAY AVERAGE TAKE TO CALCULATE BREAKOUT. NEED UR HELP SIR

    • Hi,

      It depends from stock to stock actually. For low volatile stocks 5 days is more than enough

  10. wonderful strategies in form of Tuitions . i wonder how i could i missed this website all these days in google search .
    Thanks,
    Naidu.

  11. dear sir your idea is very good,thaks .but here i m a new comer in trading i want to learn afl coding what should i do and how to connect afl(excel) to amibrokar .help me pleasessssssssssss

  12. You r doing fantastic & tremondous service to traders with backtested results,thankyou & hope many traders find your site & read & get educated about trading.Again thank a million & continue to post new articles.

  13. Great work Admin. Keep it up. Have you prepared afl for this strategy and also back test results ?

  14. Sir,
    In the trading range breakout xl sheet, what’s the need of entering the close price value for the 5 days, if we not entering the close price value in the column also the xl sheet calculates the RANGE. Pls clarify me and pls correct me if I am wrong. THANKYOU.

  15. Sir,
    Sorry for my query again, In the trading range breakout XL sheet, after entering the open, high, and low price value for 5 days ( I keep the close price column as blank for all the 5 days and not entering any value) the xl sheet calculates the breakout range value in the RANGE column, then what’s the need to enter the close price for 5 days in the close price column. Please clarify me sir.
    THANKYOU

    • Yes you are correct. Breakout is calculated on the basis of Open, High and Low values. So close price is not required. Sorry for the confusion earlier.

  16. Hi Sir do we have to manually input the Values of is there a way that the it picks automatically like your other sheets

  17. I downloaded excel. Further what I do in it? Why do you give useless things here and waste time of others. 18-Dec-15
    17-Dec-15
    16-Dec-15
    15-Dec-15
    14-Dec-15
    It is showing these dates. But today is 07 March 2017?
    BUY ABOVE 7766.34

    But now nifty is 9000.

    Are you joking for free or what?

    • Please read the instructions carefully before you ask questions here. The data needs to be updated manually in the sheet.

  18. Great work Admin
    I make this sheet in to Auto Updatable for Nifty Index but I can’t same for Nifty future.
    Any website for Nifty Future daily data which can link with exel .

      • Hi Bharat,

        Unfortunately there is no way currently to auto-update this sheet. If we find something, we’ll definitely keep you posted

      • request you to share email id where i can share autoupdate GOOGLE SHEET

  19. has anyone tried this in nse stock future segment if yes what is the accuracy rate and what are the scripts

  20. Thanks for the awesome work Admin, backtested manually and it seems to give good profits if we follow the system daily

  21. hi.. its good strategy. I have use it well. my question is eg. what to do when stock is already gone buy above price say eg stock is near to 1tgt ..

  22. Hi, I will try your all excel sheet don’t know result yet. but u r great at least u shared your idea with people.

  23. Sir, try to recreate this excel sheet, by adding automatic features of GET DATA, so that Nifty past figures will be automatically feeded, what say, please try to do this for us.
    And did you tried Backtesting this strategy, please try to do and share with us
    And Thanks 🙂

  24. Hi admin,
    I tried back testing on crude oil , nickel and zinc by applying 5 days average but it failed or mostly calls not hitting the target within intraday. We need to do apply any other changes in the formula specific for mcx? Please advise on this.

  25. Sir. I have backtested for 2 weeks in all the mcx segments. Is 5 days average enough sir for mcx intraday timeframe?

    • Hi Karthik,

      5 Days is standard, however, you need to do paper trading before executing real trades.

  26. Dear Sir,
    can you please explain about minimum column (H column) why considering low value only, what is the reason, please explain me?
    if it’s any wrong please excuse me.

  27. please check. your range breakout excel for nifty spot is not working. it always showing buy and sell near 7000 range. is this accurate.

    • Hi Raj,

      You need to manually update the OHLC prices for the current date, then the buy/sell levels would change

    • Hi Tej,

      If you enter intraday price levels in the excel, it would give you intraday signals

      • sir,
        Intraday price level means, i am not getting pls expain with a example

        thank u

  28. Hello Trading Tutions Team!
    First of all Great work Guys! You are doing a great job!
    Query1: Can I use this for doing intraday on all NSE FO stocks?
    Query 2: Can I use this sheet to enter the OHLC prices for previous months to get the trading range for the current month to execute my Swing trades.
    Thanks You!

    • Hi Avinash,

      Yes, for both the questions. However I would recommend to trade stocks with high volume for accurate results

      • Thank you for the prompt response. Have backtested it for intraday and it works with almost 95% success rate. Have to backtest for Swing though!

  29. Hi Admin,
    Have you been able to generate AFL for this strategy and backtest it ?

  30. Dear Admin,
    Please Make This Excel sheet for auto updation for closing Price??
    Also for intraday which OHLC value should we add??
    Plaese Make AFL for this stratagy.
    Good Work..

  31. Hello Admin,
    Since you come across as a genuine person, I want you to remove this misleading excel sheet. The reason being:
    1) If you make volatility based breakout AFL on a daily chart it will show the same good results.

    2) But the big problem which most people here are not understanding is the market does not move like that in reality.

    For example :
    a) Nifty hits top side entry level Buy trade is opened by the user.
    b) Nifty hits botton side entry level Short trade is opened by the user.
    c) Nifty hits the top side entry level and moves upwards to the higher targets.

    So in effect on 50% of the days only if the stock/index moves more than 200% of the range will you make your loss back.

    In AFL or backtesting on daily bars this will show as a loss of 1 x Range when in reality it was 2 x Range or more depending on how many times it chops back and forth.

    • I am not sure I understood you completely. Range breakout trading is a proven concept and many people I know make a living out of it.

  32. sir , can u please update this Sheet to latest version , bcos its not opening in latest version of excelword

  33. Is this strategy for swing or intraday? If swing, do we have to directly buy at buy levels or buy only if day closes above that buy levels (at close price)?

    • Hi Jayesh,

      It’s a swing trading strategy. I would recommend to buy as soon as the price crosses the buy level, however some conservative traders buy only after verifying day’s close price.

  34. Dear Sir, this sheet works quite well, one has to apply common rules to this application also, one can not make a profitable trade, by just filling buy or sell order with the BuyAbove or SellBelow readings, instead one has to wait for at least one or two 1 minute candles to close above or below the resistance or support price respectively, also one should take trade only after 15 or 30 minutes of market opening

  35. Hii admn,
    Strategy is goood.But excel chart doesn’t refresh,every time showing same chart.How could we find out the current buy,sell stocks etc.

  36. Dear Admin!
    First of all My Gratitude your team for doing a great job!
    Can I use this for doing Intraday for MCX,Banknifty

    Thanks You!

  37. dear admin thanks sir

    execel sheet working well sir, decent profit 10 point or 14 point daily back tested so thanks admin brother ,

  38. sir,this strategy looks good
    is there any auto fill calculation EOD of of nifty 50 equity stocks
    plz guide me how to make auto filling on daily basis

    • Hi Gangadhar,

      Not yet, but let us see if we can automate it. Will keep you updated

Leave a Reply

Your email address will not be published.