Let’s explore another Stock Trading strategy with Buy Sell signals created on an Excel sheet. This is a semi-automated Excel sheet where you have to manually enter historical EOD data for the selected stock. Once this data is entered the Excel sheet would automatically indicate the Buy/Sell signal. The strategy is based on support and resistance and assumes that 3 consecutive equal lows may cause an upper breakout, and 3 consecutive equal highs may cause a lower breakout. It works well on all the low priced stocks (less than 200 rupees).
Find similar profitable Excel bases strategies here.
Strategy Overview
Paramter | Value |
Preferred Timeframe | EOD |
Indicators Used | None |
Buy Condition | 3 consecutive equal lows in Daily Timeframe. |
Sell Condition | 3 consecutive equal Highs in Daily Timeframe. |
Stop Loss | As per your risk appetite |
Targets | No fixed target. Depends on your money management strategy. |
Excel Sheet
Parameter | Description | |
Worksheet Name | <Stock Name> | |
Inputs | Column A,B,C,D,E,F,G |
Date, Open,High,Low,Close,Volume of the selected stock. It needs to be manually entered. This can be downloaded from Google finance using this example link. (This link is for downloading data for Idea Cellular) |
Outputs | Rounded High/Low (Column G,H) | Rounded high/low values of stock for that particular date. |
Position (Column I) | Indicates whether you should take Buy or Sell position | |
Buy/Sell Price (Column J,K) | Indictaes the price at which Buy/Sell position is taken. It is the Close price of the day when Buy/Sell signal is generated. | |
Buy/Sell (Column L,M) | Proft in % terms since the last Buy/Sell position is taken. |
Screenshot
See the below example for Hindalco. The strategy correctly indicated Buy signal on Feb 26. A position taken on Feb 26 would be in 30% profit on March 23.
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 and manually insert the EOD data for the selected stocks. The sample Excel contains data for Idea,Tata Power and Hindalco.
Step 3: The sheet will automatically indicate Buy/Sell signals and compute your profit/loss%
Superb
Gr8 work
SIR, WILL THIS EXCEL STRATEGY WORK FOR NIFTY & BANK NIFTY ?
It should work. Please paper trade for few days for confirmation.
this is not real time sir.
Yes, unfortunately it is not. You would need to manually update the prices in the Excel sheet.
Dear Sir,
I would like to link NSE and BSE website in a Excel Workbook for getting the corporate action details for the day based on Ex date , Record date and Announcement date for all the listed securities in both the websites.
Would request you to kindly advise me on this
Best Regards,
Jeetesh Jain
Hi Jeetesh,
Is this data available at a single URL in NSE website?
Hello sir,
I entered data manually,but the signals not shown,where anything else to be enter?
Getting #VALUE for CAIRN,PNB,VEDL.
Can you please check / help?
thanks
Sir any backtested results available ?
In Idea buy signal came on 09/06/2016 at 104.80 as on 28/06/2016 close is 104.35.The stock went to 99 in between. I will post the results further
I feel this excel sheet works on normal trend based trading days….stock price fluctations due to external factors doesnt take into consideration.
Why Volume column is placed when that is no where used in the excel formulas..??
Hi Mohan,
It’s just for observation. You can hide that column if you want.
Is this system available in AFL format with scanner option
Hi Is there a way that instead of manual entries for OHLC, it gets updated automatically?
Hi Anup,
Currently, its not possible in this Excel sheet. You would need to add the prices manually.
Sir can i use call and put for making position in this system
Hi Pawan,
Yes you can, but at your own risk. Options are considered to be riskiest instruments.
Can this excel be used to track for other markets as well , FTSE 100, invested in pounds
Yes of course, technical analysis is independent of underlying market
You can feed real time data to this excel sheet by using the Excel Plugin provided by TrueData. Check out the 7 day fee trial along with these excel sheets.
Thanks Kapil!
Once the trial period has expired, for $126.00, what is the price of
the annual subscription?