A Profitable Intraday Trading system: Excel Sheet

In this post, we’ll explore a profitable Intraday Trading system. An automated excel sheet is created for the same which can be downloaded at the end of this post. This excel sheet will automatically download the data for Nifty50 stocks and indicate Buy/Sell signals. Also, it will show the profit % and stop loss value. This strategy is 80% accurate in all market conditions if applied on liquid stocks and commodities.

Check out below link for Live signals based on this strategy (would not require downloading Excel sheet)

Intraday Open High Low Strategy -Live Signals

Strategy Overview

Paramter Value
Preferred Timeframe Intraday 5 minutes
Indicators Used None
Buy Condition Open=Low for the current day. Buy after half an hour of market open
Sell Condition Open=High for the current day. Sell after half an hour of market open
Stop Loss Indicated in Excel Sheet
Targets As per one’s risk appetite or End of the day

Excel Sheet

Parameter Description
Worksheet Name Intraday Trading System
Inputs Column C,D,E,F,G Symbol name and Price for the day. It’s not needed to manually enter anything.

Sheet will be updated automatically every 1 minute.

Outputs Position (Column H) BUY/SELL signals for the particular stock. If this field is blank then don’t take any trade.
Price (Column I) Price above/below which Buy/Sell orders should be placed.
Stop Loss (Column J) Stop Loss Price.
Profit/Loss at EOD (Column K) Profit/Loss % at the end of the day based on the entry price.

Screenshot

How to use this Excel Sheet

Step 1: Download the sheet from the end of this post to your local computer.

Step 2: Open the sheet. It will throw few warnings. Press Ok for all.

Step 3: Make sure that you are connected to the internet. The sheet will update automatically every minute as per the live market data.

Step 4: After half an hour of market open time, check the sheet for Buy/Sell signals. Place your order above/below ‘Price’ column in the excel sheet.

Step 5: Place stop loss as indicated in the Excel sheet.

Step 6: Book profit/loss based on your risk appetite and make sure that your entire position is squared off at the end of the day. This is an Intraday only strategy.

Download Link

Intraday Trading System

Download Amibroker AFL for this strategy here.

 

168 Comments

  1. Really wondering, how you could bring down live data on Excel sheet. Simply wonderful!! Probably you should start step by step tutorials on Excel also. It will help immensely as it helped in afl.
    Request: Can you add Index values into this sheet?

    • Glad to hear that you liked this post. We were actually thinking to start a series on Excel tutorial. Stay tuned.
      We will try to add Index data in this Excel sheet and let you know.

  2. do u have for usdinr also as iam trading in USDINR, eurinr.. Pl email such things to track positional BUY sell calls & intraday BUY sell calls — my mob -9820414831

  3. Hi is it possible for u to post the back tested results of the above system for the past one year . Also if u can test it post one hour from the open

    • Hi Viral,

      This strategy cannot be tested in Excel sheet. We will try to backtest it in Amibroker and post the results soon.

  4. Very Nice Sir… Do you have any excel base strategy for commodities ?

    • Hi Raj,
      In this strategy, stop loss is dependent on open price every day. So it won’t change during trading hours.

  5. Hi
    I had knowledge about this…but failed to take advantage of it.Well, I have downloaded it…hope it will work fine!! And thank you so much for your great work!! Keep it up!!!

  6. Dear Admin,
    I am a small trader and at present trading mostly in the options segment,off course due to small trading capital. From my side, I want to request you if you could kindly try to post something like this fantastic excel sheet you had posted.

    • Hi Pankaj,
      Thanks for your kind words. Please subscribe to our posts. You’ll always find something interesting to read and learn.

  7. some error msg is showing like from nseindia site not datas can be retrived bro.

    • Hi Ganesh,
      Can you check if your firewall or ISP is blocking access to NSE Website?

  8. I am using this fantastic excel sheet & finding it quite useful. Please keep it up and from my side again I would like to request you such a sheet for NSE stock options segment please!!! Thank you so much again!!

  9. Sir as requested above, please try and post back tested results for this strategy. It will be really helpful.

  10. Hello Sir, is it possible to have excel for all the stocks in nse
    could you please let me know how to do this

  11. Hello Sir,

    The excel sheet is amazing, Thanks for this great sharing. Note that the sheet has a little error. HDFC prices shown are retrieved from HDFCBANK and LT prices shown are retrieved from BOSCHLTD. Appreciate correcting and uploading a fresh file. Many thanks

  12. Can we get 1min data for crude and Natural gas, is it OK even if they’re global data…

    • Hi Harsha,
      Commodity trading is not our niche. And it’s actually hard to get commodity intraday data free of cost.

  13. What is Best time to take a Position?Is at after 15 mins after Day’s Open or is there any time you suggest

      • In my opinion, use one min chart, trade second candle either buy or sell as per signal in scrip here. Because 95% trading takes place in premarket i.e from 9.00 to 9.07am. Rest of the trading finishes upto 9.30 or 9.45 am.
        Date 11 Nov 2016. Sell in ACC @1443 (as per excel sheet here) At 10 am ACC price was 1403.
        How can any one sell ACC at 1443 on 11 Nov 2016 at 10 am?
        Price of ACC was 1443 on 11 Nov 2015 at exactly 9.16 am.

        • Hi Pablo,

          I agree with your analysis and it makes sense for this particular stock on Nov 11. But before posting any strategy we backtest it on multiple markets and instruments and suggest the best possible parameters. In this case entering after half an hour of market open has the best success ratio and profitability for most of the stocks (backtested on around 10 years of data). But at the end its upto you how you want to modify the strategy rules.

      • Here you are giving the price at which scrip is to be bought or sold. The time at which that price is available for entering the trade is the time. Not 10 am or other. Given price is crucial.

      • मेरे एक बात समझ में नहीं आ रही है. यहाँ एंट्री प्राईस दी गयी है. तो जो “समय” में यहाँ की एंट्री प्राईस चार्ट के ऊपर आ जाए उसी “समय” एंट्री करना है या 10 बजे चाहे जो भी प्राईस हो उस पर एंट्री करना है. दोनों में से एक हो सकता. या तो एंट्री प्राईस या तो 10 बजे चाहे जो प्राईस हो. सुसायडल है भाई.

  14. Hi Admin,
    Can you guide me regarding the best time frame for intraday & swing trading.I mean it should be 3min,5min,15min or more.Please reply!Thanks!

    • Hi Pankaj,
      The timeframe totally depends on your personality and your emotion handling capabilities. Practically 1 minute is the best TF for intraday, but most of the people could not find it suitable due to high risk associated with it. So they switch to 5 minutes. I would suggest you to start with 5 minutes for Intraday and 30 minutes for Swing.

  15. Sir it is great to watch this excel sheet strategy for nfo stocks.Pl tell me is there any possibility to plot these technic in amibroker and by the by it can be linked to robo system and work will be done automatically.

  16. sir you mentioned on 14th march that you have developed excel strategy for crude and you have given a link also. But when that link is opened that shows only nse stocks. Please clarify

  17. Sir, my 2 cents on this strategy – First this strategy assumes that the volatility ends after the first 30 or 45 minutes of the day & stock may then move in a trend which may not be the case. A backtest will reveal a lot of stocks which hit the stop (open price) during the day after the first 30-45 minutes. Second the data taken from different sources like Google Data / Sharekhan TT terminal / NEST terminal / Zerodha PI will differ & hence backtest results will also differ.

    • Hi Rajen,
      Yes,you are correct. A quick backtest would reveal that number of losses is way higher than number of profits. However, it all depends on your money management. A good risk-reward ratio would keep you profitable in the long term. And this strategy ensures a good risk-reward ratio if you maintain SL strictly. We are also working on an AFL to further optimize this system. Thanks for your suggestions!

  18. Sir in an afl how to give a command for profit booking. In automated robo system which i am using there is common profit and loss setting and i am unable to fix p/l for individual scripts

    • Hi Mustak,
      You can use ApplyStop function in Amibroker for setting up profit targets. But the interface between Amibroker and your robo system should understand this function. Please contact your robo system provider for details.

      • under analyisi , only chart opened are tested is it correct? how to get analysis ofall stocks in favourate list pl? thanks

        • Hi Kamal,
          I hope you are talking about Amibroker backtesting. To backtest on all your favourite stocks you first have to create a watchlist and add all those stocks. Then in the analysis window you would get an option to select that watchlist.

          • Hi….can u explain more about that….
            I want use this exel file for other stock…how can i do..
            Plz explain step by step

          • Hi Amirpal,
            Right now this sheet is automated for just Nifty50 stocks. You would need to manually enter data for other scrips.

  19. Thanks for providing such a wonderful help to the trading community.

  20. SIR, LT IS SHOWING OPEN VALUE 3229.70 WHERE AS TODAY’S OPEN IS 1244.65, KINDLY CHECK AND REMOVE THE ERROR IF ANY, THANKS

  21. SIR, XL SHEET IS SHOWING ANY DATA IN CAIRN, PNB & VEDL, KINDLY CHECK AND DO THE REQUIRED SO THAT THERE IS DATA FOR THESE 3 SCRIPS ALSO, THANKS

    • Hi Karan,
      Please download the updated sheet we shared. Errors have been resolved.

  22. sir i think it will be useful if u add a date column in your Profitable Intraday Trading system: Excel Sheet

    • Hi Mustak,
      Thanks for your suggestion! We would try to add date column in the sheet.

  23. Hello Sir,
    Thank you for correcting it. Please explain us how to add other stocks to the same excel sheet and get live price with buy/sell signals. Many thanks

    • Hi Nasrudeen,
      For now, this excel sheet is automated for just Nifty 50 stocks. We’ll try to develop another excel sheet where you can add any other stock.

  24. Hi sir ,
    I gon througw all the post and its wonderful experience to read about yours excellent work .
    Just i want to know that ” is pullying live data in xls from any sources like nseindia / google finance or etc is legal ?
    Thanks

    • Hi Optimus,
      Thank you! Yes,it’s totally legal to pull live data from NSE or any other websites. However, it may not be legal to buy Historical data products from NSE and distribute it to others without NSE’s consent.

      • thanks for yours reply, do you have any like where i can refer for NSE legal police about live data pulling ,

        and do you have any system for stock options intraday trading .

        Thanks & regards
        Optimus

  25. Sir, can you help to share trading setup for intra n postional trading

  26. i dont know how to apply excel sheets in my computer. pls explain me. step by step. how to install excel sheet in my stsem

    • Hi Chandrasekaran,
      Please install Microsoft Office in your system. Then you can easily access this Excel sheet.

  27. Thanks a lot for sharing such valuable strategies on excel set up,looking forward for options trading system

    • Hi Umesh,
      Thank you! Right now we are developing strategies only for Futures and Spot, will soon start Options section in our website. Let me know if you have anything specific in mind.

  28. Hello Sir,
    Firstly thanks for your nice creation. I am from Bangladesh. I want to use it to our stock market “Dhaka Stock Exchange”. Please guid me to for the way of use it. Thanks in advance.

    • Hi Ruhul,
      Thanks for your kind words. I am not sure if it is possible to get live data from Dhaka Stock Exchange. Please send me the URL of its website. I will try to look into it.

  29. sir ur really great. Thank you for helping us.
    sir if it’s possible plz upload this excel sheet with mcx live commodity data.

  30. Dear Admin,
    This is an amazing formula excel. You are the master. I was searching something gr8 tool from months and finally got it from you. You did and doing a fantastic job :) God bless you and wish your all dreams comes true.
    If you could have also added target levels in excel that would have been wonderful for safe trader like us
    Happyzz helping !!!!

    • Thanks Javed. Target levels can be based on your risk appetite. Try doing profit slicing for two different target levels.

      • Thank Admin for reply. Have you success in creating Future options excel as well ?

  31. Dear admin,
    Good job done by you. Keep it up.
    It would be great if you develop excel sheet for stock futures.

  32. Hello Bhai, Thanks a lot for this !
    Can you please share a sheet with other stocks apart from nifty50, for ex: fno stocks

  33. hello,
    i have a request to make can i have few selected stocks in excel sheet or for online view for this link http://tradingtuitions.com/intraday-open-high-low-strategy-live-signals/.
    And i would also want to tell that excel sheet is getting downloaded but the data is not getting automatically updated therefor i have even checked the firewall or isp both are not blocking any website.
    Please let me know why is this happening and at last i am really very thankful for your hardwork may god give you grace and prosperity.
    Thank you.

    • Hi Vivek,

      We can add the requested stocks in the Live Excel sheet. Please feel free to post the stock names in Comments. Can you please try downoading the Excel sheet in some other machine to figure out if there is any problem in your Excel installation. Till now, it’s working for me and most of the other readers here.

  34. Appreciating you for your superb indicator… I am not experiment yet and just downloaded and found some errors like BOSCHLTD & LT same market data and CAIRN, PNB & VEDL not updating with market showing #VALUE! kindly check and modify it. and Can u tell me the success rate of this indicator or can u post 1 month result or any backtest…

  35. Hi, After a long search i found this website and it is amazing. Thanks for all the hard work you have done in creating these sheets. I have one request only. Is it possible to make a sheet for nifty 50 stocks with only todays open price, todays high, todays low, previous days high and previous days low refreshing every one minute. This would be a big help. Thanks a lot.

  36. Yes we can,i am using it now, i have taken service from Truedata for live streaming of quotes and my excel shows me all nifty 50 data gives me buy or sell signal using the strategy i have finalized.

  37. Hi,
    This is list of f&o stocks but price of equity, it would be good if you update it with f&o price.

    • Check if the Excel App in your phone supports real-time data connections and macros.

  38. Hi, downloaded excel file, but frequently receiving following error message : “unable to open file //www.nseindia.com/live_market/dynacontent/live_watch/stock_watch/niftystockwatch.json cannot download the information you requested” Please help resolve this issue

  39. Awesome tool brother I m searching like this tool,got it finally can I add some more stocks here if possible tell me how ?

  40. Hello sir,
    Just downloaded the excel utility, and it looks good as well,But i am getting an error in CARIN, PNB, and VEDL scripts. do let me know what needs to be done.
    thanks a ton for sharing this utility
    God Bless you

  41. Hello Sir,

    Thanks a lot for making such wonderful website & various tools.

    I am having query regarding “Profitable Intraday Trading system: Excel Sheet”. The excel file available to download is only having Nifty 50 stock list. Can you please share excel file with F&O Stocks & Index.

    Regards,
    Amit C.

  42. Also unable to pull data from the NSE website, any help would be really appreciated. Great sheets btw, thanks a lot.

  43. Hello Sir,

    I am getting the error as unable to open the nseindia website and Cannot download the information you requested when try to open the downloaded excelsheet. Please assist.

  44. Hi, could you please send me the excel sheet of F&O – intraday open high low – to my mail ID – thanks in advance

  45. hi kindly sent your excel sheet of F&O OPEN -HIGH-LOW with live rates up dation link or auto up dation from nse site plz i need it urgently.
    my emila id – pradeepsarin1960@gmail.com
    mobile 08791479781 plz sent & oblige me

  46. Hi Sir,
    How can use this. I need to download the AFL for this strategy. Kindly Help me.

  47. Hi guys,
    I was reviewing the trading rules and have a question.
    you buy if the Open is the low of the day, however, how can you know if it is H or L if the day has not ended.

    • Hi Javeir,

      The rule is to buy when Open=Low for first half an hour of trading. So there is no need to wait till EOD.

      • Oh, ok, so the L is only the L of the first half hour, and then you buy at whatever price is available after that, thank you!

  48. Dear Sir,
    Many thanks for you to develop a website like this. My doubt is about the entry point. If Open= low ,can we buy the stock on spot rate at 9.45 AM or can we wait till the market reaches the point which mentioned in the ‘Price’ Column? Please advice.
    Hazeer

      • Dear Sir,

        Many thanks for the update. Could you please explain about how i can use this method in commodity trading?

  49. Sir, Very useful stuff for a trader. I found data is updating as per the equity market. My query is that is this strategy works in futures also? or any other sheet for futures trade. Thanks

  50. Dear Sir,

    Many thanks for the update. Could you please explain about how can I use this method in commodity trading?

    • Hi Hazeer,

      The method remains the same for equity or commodity, but I fear we do not have any ready-made excel sheet for commodities yet.

    • Hi Geetha,

      Target depends on your risk appetite. I would recommend to use trailing stop loss.

  51. also please suggest if we buy stock after the price mentioned for the buy signal stocks… can we make profit for sure?

    • Hi Geetha,

      All profit/loss calculations in the excel sheet are based on the price column, and you can see it makes decent profits for most of the stocks. But still perform paper trading for few days before going live with this.

  52. Dear Admin, the downloadable excel sheet doesn’t have India VIX, Bank nifty and Nifty signal feeds. Also kindly add few more popular stacks which trades with high volume apart from Nifty stocks

  53. I downloaded excel sheet. It did not ask me Step 2: Open the sheet. It will throw few warnings. Press Ok for all. I downloaded at 15.43 on 07/10/16.
    ACC price being shown is Rs.1395. Present price on NSE is 1624. Guide me please.

  54. Yes. It is working. Please help me. It is showing error in values of CAIRN, PNB and VEDL. What can I do? Can you please tell me how to trade using it? Stop loss I understood. What is “price”? I have buy or sell at this “price”?

  55. Can you share only 5 liquid stocks from nifty50 priced low buy sell price and sl for intraday?

  56. how do i get open-high-low-close information for entire NIFTY stocks? not just NIFTY50.
    What changes do I need to make in Cell E1 of your excel?

  57. Dear M/s, This has reference to your “download link- Intraday trading system” below your “how to use excel sheet step1-6”.
    I downloaded it. It is showing very old price of ACC scrip. How come? Please make it up to date. Or stop writing here down load- Intraday trading system.

  58. Admin ..this setup is great..plz add One more column…Prev Day Close…its for sureshot and choose own stop loss….

  59. Buy Condition Open=Low for the current day. Buy after half an hour of market open
    Sell Condition Open=High for the current day. Sell after half an hour of market open
    Open = low of the day? day starts at 9. closes at 3.15pm . how i will now open = high or low of the day at 9.45 am? how ridiculous? high can be any time in the day or at the end of the day and same for low. how difficult the things are to understand? please explain with and example.

    • ADMIN kindly answe the question for PABLO..NOV 5th..

      Open = low of the day? day starts at 9. closes at 3.15pm . how i will now open = high or low of the day at 9.45 am? how ridiculous? high can be any time in the day or at the end of the day and same for low. how difficult the things are to understand? please explain with and example.

      • Hi Mani,

        The primary assumption of this system is to take positions when Open=High or Open=Low till first 30 minutes of trading hours. Please read the instruction and other comments carefully to understand it better.

  60. Sir,
    I have error on the following line live-updating excel sheet, Please solve it
    Run-time error ‘1004’:
    The file could not be accessed. Try one of the following
    . Make sure the specified folder exists
    . Make sure the folder that contain the file is not read only.
    . Make sure the file name does not contain any of the following characters: ? [ ] : l *
    . Make sure the file/path name doesn’t contain more than 218

  61. Hello Sir,

    I find the strategy interesting, I have been looking for such a one. Thank you.

    Could you get me the excel sheet for this, I tried downloading the excel sheet from the link in this page but values arent getting updated.

  62. Hi, great work. I have downloaded the file however few stocks are there where data doesn’t get populated. Ex- Cairn, PNB and VEDL

  63. Hi Admin,
    In my excel sheet for some symbols all the values are showing as just #VALUE!, can you please check from your end.

Leave a Reply

Your email address will not be published. Required fields are marked *