Do It Yourself Stock Picking: How To Rank Stocks On Your Own

There are thousands upon thousands of individual securities in the stock market, plenty of ETFs and mutual funds also in the thousands and an almost overwhelming number of stocks to choose from. Yet time is of the essence and what is a good buy now may not be a good buy in a month, or even the next few minutes. If you aren't wanting to pay thousands of dollars for services that force you to react to their information and as a result always lag their signal (and be late to the punch), then you will have to identify the setups yourself in as close to real time as you can. That requires an initial setup time that may be quite a bit of work, but can be very rewarding and help you understand how some other services might rank stocks. Ultimately you have to choose the stocks, so it makes sense that you find the stocks that work according to your style and personality, and risk tolerance and things of this nature. For example, a growth investor and market timer probably will actually often prefer a high P/E that means the company is given a premium because of it's growth prospects and capable of big movements and market technicals that show upward movement. A value investor actually wants the lowest P/E along with other metrics of value such as book value, price to sales, long term history of positive fundamentals, and likely downward, oversold price movement that provides a very affordable price to buy other people's panic.

So to start with you need to compile a list of all the securities you want to look at. I use finviz screener and I filter out ETFs with the idea that I can pick a small basket of stocks in that area if I wanted to go that approach and select them better most of the time.

Once you go there there is a "custom" button that allows you to click on "settings" to provide information that you want about each stock, ranging from fundamentals, technicals and general information about the stock/company. I have a free account so it saves my preferences.

What you will want to do is select all the information you consider relevant to your stock ranking methodology and select it, then scroll down from the custom tab and select export. You will have the opportunity to open or save in excel (or some other spreadsheet software). I choose open as I can save later, OR copy and paste the data into a prior sheet I have saved.

The most difficult part of this process is coming up with a ranking system. While this may be easier to follow if I just gave you my ranking system, in order to truly teach you how to construct your own, I must try to explain the complex details that is more difficult to explain and may get confusing. Fear not, for I will come up with another post later showing you my ranking system entirely... but for now... here comes the complexity.

Ultimately I like everything graded on a scale of 1 to 100. As such I want every component that goes into the ranking ranked on such a scale. Some components will be "better" if the rankings are low. So I will ultimately need a numerator and denominator in the formula that divides the average of all the highest numbers by all the lowest numbers.

To make the spreadsheet efficient you may want to use the IF function in the spreadsheet. IF market cap is greater than 50M, display market cap, If not, display "Too Small" in the cell. Since data is displayed in the millions, I need data greater than 50 in the market cap section.
That formula will look like this
=IF(G2>50,G2,"Too Small")
where G2 is the cell that contains the market cap information.
If you prefer, you can have it display the value 0 and that way you can multiply all the values together and IF that formula equals zero then you can have it display "filter out" so you know not to include the stocks in your rankings.
=IF(G2>50,G2,0)
I also will run things like a liquidity qualifier where the average volume times price (market cap traded per day) must equal more than 200M.
So the formula in excel will look like this
=IF((X2*V2)>200,(X2*V2),0)
where X2*V2 is price times average volume.
Some data will be unacceptable no matter how good the other data is. These are the ones you want to filter out via this method. But most data will be just of "lower ranking" if certain data is bad, which the formula will be able to mostly handle.

You may be tempted to delete the columns you don't use. Be aware that you will have to remove them from the screener, otherwise the next time you import the date it won't align.

Why do I sort and filter data in excel, instead of using a filter on the screener itself?
A couple reasons. First of all, I may not want to filter market caps under $50M out, but $25M or $75M. In this case, there are no values. Secondly, I may want to include those under $50M in the system and provide a different ranking formula, or make exceptions if the other numbers are off the charts.

Ranking data on a scale of 100:
I believe there is a better way to do things, but until I take the time to learn more about excel, I will have to do this somewhat manually. There are a few ways, one where you value according to "percentile" and another where you use a fraction of the best performer. We will first discuss using a fraction of the best performer. So simply sort from largest to smallest, and those with the greatest data such as ROE will be the divisor. Say the largest ROE out of all eligible (those not filtered out) is 917.4% (9.174) You would take the ROI of each cell divided by 9.174 and then multiply the total by 100. You may also use the average of the top 25% as the divisor, and ANYTHING over 100 (by using the IF formula) will be displayed as 100. This method has pros and cons. This more effectively measures the difference between the best ranked and worst ranked and does so to scale, but may still provide a very poor ranking even when the data is well above average. Since ultimately you are coming up with a weighted average, the method of having good results ending up being considered pretty poor could make it a bit difficult to really get a sense of if the numbers are considered "good" or not, and those with one big good number out of all the metrics could skew all the results of every other metric in some cases.

"Percentile ranking" is different because if the top ROE is a million and everything else is below 100, even the second best name would get a real low ranking out of 100 using the above metric (or if the average of the top 25% is dramatically different, same thing). With percentile ranking you are merely sorting and measuring each as 1 out of the number of eligible stocks and providing a percentile, then taking the sum of all those above to give you the top X% or 1 minus that number gives you the percentile. The issue with this is it does a poor job of showing you when some data has a dramatic difference from the other, but may be ranked similarly because of their proximity in the rankings. i.e. if the top 3 results are 100M, 50M, 20M and the next best result is only 20,000... then the 4th result will still be ranked very similar to the 3rd result, even though the results are dramatically different in value. You may consider a weighted average of each outcome to factor in a combination of each of the numbers, but doing so each time for every single measurement you consider relevant to the ranking system is quite a lot of work. In the case where high is an undesirable outcome such as value investors might treat P/E, you can either invert the number (e.g. 1 divided by P/E) before running the rankings, formula, etc , or you can simply take your total 1-100 scale and subtract 100 from the total.

Ultimately you will take all the rankings that are ranked from a scale of 1 to 100 according to your preferences, and multiply each number by a fraction or percentage point that corresponds to the "weight" (importance) of each variable. All of the "weightings" should add up to 1 (100%) so that you get a number where the best possible total if it was #1 in every category would be 100. (Or in some cases, over the threshold in which you determine all numbers above it should be displayed as a score of 100).

If you do this, you should have a spreadsheet that simply requires an export, a copy and paste to update the totals, a sort to check that the highest (or perhaps lowest) numbers haven't changed, and then a sort function on the totals to rank the stocks from best to worst according to your OWN methodology.

Some variables may require a bit more thought. Some may consider an overbought RSI as a bearish signal, where as a high but not overbought signal may represent positive buying and momentum that has not yet reached an extreme. You may want to isolate the sectors and then industries to provide some ranking system for the sector and industry it is in based upon the average performance of them.  That is possible with sorting and a bit more work. I prefer to code the sector and industry.

One might try a VLookup function in order to look up values from data that only contains certain values
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
Or A Pivot Table in order to combine those that contain the same SECTOR and/or Industry and rank the group as a whole either by average or total.
http://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel

I would probably do all the sorting and ranking first, then perhaps modify the weightings or make additional categories for data that applies to the sector. For example, I might be more willing to buy an individual name in an overbought sector or industry than I would in an overbought stock. Thus, I may create a different ranking on scale from 1 to 100 based upon RSI that applies to industry and/or sector differently than it applies to the individual stock.

Hopefully I have provided enough data for you to have a broad idea of how to create your own stock rankings. In a future post I plan to actually go through the busy work of coming up with a ranking and then describing the process.