Download - Stock screener using Microsoft Excel based on comparative relative strength indicator method

Comparative Relative Strength study compares two stocks to show how the stocks are performing relative to each other. Comparative Relative Strength Study should not be confused with the Relative Strength Index of J. Welles Wilder Jr. It is one of the most simplest and powerful way to analyse the stock markets.

I have created a simple Excel file where I have taken four different “base dates” and compared it with current market price of stocks.  Just for ease of calculation, I have taken previous 3, 6, 9, 12 month’s prices as “base date”, to see the rate of change (momentum) during these timeframes. Therefore we can easily compare stocks across the market, to find an outperforming or underperforming stock.

You can download the relative performance rank calculation file, free from here.

Screenshot of screener below


How to update the data?
You need to follow the steps given under to update the data, to reach the desired results.

1) Update the data in "Grey cells" only, which you will find in all the sheets except the first sheet “Screener”

2) Download the end of day (EOD) data from National Stock Exchange site, for this sheet I have downloaded 3, 6, 9, 12 month’s data. Alternatively one can choose specific day’s data as base date for example a significant high (05-11-2010) or an important low (26-08-2011). It’s up to you to select different time frames. The minimum timeframe to compare,  that I have used is 3 month’s data, some people use 1 week and 1 month’s data as well to gauge short term performance.

3) Copy the data in the same sequence as I have done in different worksheets i.e. “Latest” “3 month” “6 month” “9 month” “12 month”. In case you choose different data set, keep the latest data first and oldest in the last sheet otherwise “Gain %” in first worksheet will be calculated erroneously.

4) That’s all about updating data. Once all the data is copied everything will be updated automatically in the first sheet “Screener”. Now you can apply excel filter by selecting top row. You can check the performance of stocks across different timeframes by using “Sort largest to smallest” feature. On top you will get the outperforming stocks (part of “buy only” watch list) and the bottom of the table will give you the list of underperforming stocks (part of “sell only” watch list). To further arrange the data for your convenience you can uncheck the "#N/A" when you sort the data.


A Few things to take care of:
1) Updating this stock screener is a little tricky, at least for someone who is new to Microsoft Excel. People familiar with Microsoft Excel will find it very easy. It’s just a simple copy paste task in the appropriate sheets.

2) The data that we download from NSE is not reliable at times. Old data is not split adjusted so you will get erroneous result with this screener, especially with the stock that had a split, bonus or rights issue in the past one year. If you can import data from a reliable software or a website, it will work like a charm.

3) Always verify the results of the screener with a chart. That’s what I do to verify the results of any kind of screener.

4) You need to copy the formulas in the “Screener” worksheet if new data is added in second work sheet “Latest”. This can be done easily by selecting the last row in the first worksheet “Screener” and dragging it down.


Screening and trading
This excel file will just help you to find an outperforming or underperforming stock. It tells you, where you should allocate your money. It doesn’t ensures that you will be profitable trading a particular stock. Profitability will depend on your trading system and trading discipline.

This screener essentially helps you in finding a momentum stock. Momentum trading is not easy for everyone.  It is based on “buy high, sell higher” (greater fool theory) way of trading in other words breakout trading.  One can always wait for some kind of a retracement to enter a momentum trade. But the stock chart will always give an impression that the stock has moved “significantly” from a base. Most of the stocks that you will find using this screener will be “fundamentally over valued”.

If you face any problems using this screener you can send me a mail or put your query below in the comments section (I prefer comments as it helps everyone). Hope that you find the screener useful in your trading. Good luck :)

14 comments:

  1. Good Morning AJ! Thanks for the screener.

    ReplyDelete
  2. @rm
    Good Afternoon, can you share the order flow chart for today,if possible. I want to see how the 'speed breaker" pattern looks on order flow chart. Does it work better than a moving average?

    ReplyDelete
  3. lot of thanks for stock screener.

    ReplyDelete
  4. @AJ

    Sorry I did not check mail earlier, else i would have forwarded you a screenshot of the OF chart. Will request Shai to mail me the OF chart and forward to you as soon as i get the reply.

    ReplyDelete
  5. @Anonymous ji
    You're welcome

    @r m
    Thanks :)

    ReplyDelete
  6. One of the mails that I have received has a question
    ******************************
    Question - You mentioned that it is good for a Momentum Trader, Is there anyway in which an Investor can use this data and analyze the data to narrow down on some interesting picks?


    Answer - Thanks, yes very easily. Though I don't know your style of investing, as an investor you must be looking at certain data like P/E , P/B, P/S etc. So once you get the list of potential investing ideas then you see their rank using the excel sheet and commit money first in the stock that ranks higher.
    Lets say you have two stocks with same P/E then buy the stock that ranks higher ( outperformer ) in the above sheet.

    Alternatively you can look at fundamental data after you identify a stock from the excel sheet as well. You will see that stocks that are currently ranking higher are reporting better sales and profit in their results. This is the most simple way to identify a stock or filter a stock using price performance, once filtered you can apply other studies fundamental or technical to come with final list of stock to invest

    All the best
    Anuj :)

    ReplyDelete
  7. Nice blog good information abouts commodity. I really appreicate your post and you explain and every point very well.

    ReplyDelete
  8. Hi.. the screener being mentioned is not available now. Can you please re-post the same?

    ReplyDelete
  9. Hi.. the screener being mentioned is not available now. Can you please re-post the same?

    ReplyDelete
  10. Wow! I have been reading about Comparative Relative Strength a lot lately, and my plan was also to rank the stocks based on RS. Your blog made it easier to understand, however the file shared is no longer available. Is there any way I can get that Excel file.

    Thanks.

    ReplyDelete