Seeking Alpha
Long-term horizon, research analyst, dividend growth investing
Profile| Send Message| ()  

A few weeks ago I introduced Seeking Alpha readers to My Mad Method for picking stocks from my watchlist to add to my expanding dividend growth portfolio. I use this data-centered approach to sift through and narrow down the 30 or so stocks that I actively track on my watchlist to a few that I can seriously consider for purchasing once I have enough nickels and dimes collected to make such a move. I've gotten a lot of great feedback and response to these articles, including a good number of folks asking for a copy of my spreadsheet so they can start adapting it to their own set of metrics with which to rate and rank the stocks they want to track.

But what about that stocks that I've already purchased? They need to be monitored as well, at least I think so. To do that I employ the same 15 metrics that I use in My Mad Method on my watchlist on the stocks in my portfolio to come up with a ranking by which to keep track of those stocks. But I've found that what works for picking stocks to add to my portfolio isn't quite enough to help me track how those stocks are performing now that they've made it into my portfolio. So in addition to the My Mad Method Rank, or MMM Rank (not to be confused with 3M, which is a great company), I also rank the stocks in my portfolio on a few more metrics, and then display the final ranking on my tracking spreadsheet.

A Few Words About Spreadsheet Organization…

Since I've received so many questions about using a spreadsheet to track stocks, I'm going to take a moment to discuss a bit about my approach. To be precise, I have multiple worksheets on the Microsoft Excel spreadsheet I use to track my portfolio on which I enter and/or display different data, depending on the nature of that data. (I also save a new copy of the spreadsheet at the end of every year so that I have a year-end record of where my positions stood frozen in time, and then continue to enter data and manage my portfolio on the current year's version of the spreadsheet.) For most of my data entry I use a worksheet called "Positions", but that has a little too much detail on it to get a good picture of how the portfolio is performing overall, so to provide me with a clearer, more concise picture of my holdings I reference that worksheet from a worksheet called "Dashboard", and whenever I've finished inputting the most recent data I turn to the Dashboard to give me an overall picture of how things stand.

For example, if I've purchased multiple lots of the same stock over time, each of those lots will have its own line on the Positions worksheet, but there will be a single line on the Dashboard for that stock where the various rows from the Positions worksheet are either totaled up, averaged, or their tracking metric calculated anew based on the aggregate numbers of other columns on that stock's row on the Dashboard.

Because there are presently 15 metrics involved in determining the MMM Rank, that data, too, is on a separate worksheet, with the appropriate entries for each position mapped to that worksheet from the Dashboard worksheet, such as the Company name, Ticker symbol and Current Price. This reduces the need for manual entry, and allows me to set the reference formula for one row in a column and then "copy down" that column for all the rows that are on the Dashboard to then be populated on the MMM Ranking worksheet. The same approach works for any other worksheet that references another, different worksheet's data.

The Final Rankings

For each aggregate position on my Dashboard, I display its current MMM Rank, which is mapped from the MMM Ranking worksheet, in the fourth column, column D. One of the metrics that I calculate on the Dashboard is the Percentage Gain or Loss of that position, which is the current value divided by the cost basis. I then rank this number and display that rank in column B as the Gain/Loss Rank.

The next ranking is based on the total annual projected dividends that I expect to earn from each position. This is calculated using the current annual dividends per share multiplied by the total number of shares that I hold for each stock. This number is then ranked and displayed in column C as the "Projected Dividends Rank", or "Prjctd Divs Rank" on the example below.

Finally, in a separate column way off to the right of all of my columns of data, I sum up all three of these rankings, the MMM Rank, the Gain/Loss Rank and the Projected Dividends Rank, and then rank and display that number in column A as the "Combination Rank", or "Comb Rank". The results are shown in the following table:

Gain

Prjctd

As Of:

Comb

Loss

Divs

MMM

5/4/2012

Rank

Rank

Rank

Rank

Company

Ticker

18

3

24

20

Apple

(AAPL)

6

5

16

13

Abbott Laboratories

(ABT)

15

12

25

8

Alamos Gold

(AGIGF.PK)

24

4

26

29

Central Fund of Canada

(CEF)

18

2

27

18

Cirrus Logic, Inc.

(CRUS)

10

10

12

19

Crescent Point Energy Corp

(CSCTF.PK)

9

14

8

16

Duet Group

(OTCPK:DUETF)

13

22

15

6

Exelon Corp

(EXC)

22

1

27

24

Endeavour Silver Corp

(EXK)

28

25

23

22

Ford Motor Company

(F)

8

21

5

10

Freehold Royalties, Ltd.

(OTCPK:FRHLF)

17

23

22

1

Corning, Inc.

(GLW)

20

9

14

25

Kimberly Clark Corp

(KMB)

1

6

3

12

Keppel Corp

(OTCPK:KPELF)

26

17

19

27

McDonald's Corp

(MCD)

21

24

21

4

Medtronic

(MDT)

3

8

19

2

Microsoft

(MSFT)

2

19

1

5

Annaly Capital Mgmt

(NLY)

11

26

9

7

National Presto Industries

(NPK)

23

16

13

26

PetroChina Co

(OTC:PCCYF)

27

27

17

21

Peyto Exploration & Development Corp

(OTCPK:PEYUF)

11

28

7

7

RWE AG NPV

(OTC:RWNFF)

4

11

4

15

SeaDrill, Ltd.

(SDRL)

15

20

11

14

Statoil

(STO)

5

18

2

11

StoneMor Partners, L.P.

(STON)

6

7

10

17

AT&T

(T)

14

15

6

23

Telstra Corp

(OTCPK:TTRAF)

24

13

18

28

United Parcel Service

(UPS)

If you are using Microsoft Excel 2007 or higher, then you can do what I do with the ranking columns, and that is to employ Conditional Formatting on them so that the lower numbers show up in progressively greener colors, scaling to the highest number in dark red, such as this:

That makes it a lot easier to spot stocks that are performing particularly well in one or more categories, as well as those that may not be doing so well overall, at least based on the metrics from My Mad Method plus these other two rankings that I've described here. For example, Annaly Capital Management isn't doing too well in terms of Gain/Loss, but otherwise is scoring very high overall, whereas Endeavour Silver, which has seen the best overall performance in terms of the increase in its share price since it hit my portfolio in July, 2010, doesn't stack up very well to the rest of the portfolio in any of the other categories.

Based on the above information, things don't look too good for Ford, do they? Am I prepared to jettison F from my portfolio, then? Not on your life! Ford is one of my growth stocks (hopefully), a company that I think has done extremely well turning itself around in recent years, especially for not having to have declared bankruptcy like its Detroit neighbors General Motors (GM) and Chrysler. That F has recently restarted its dividend is a plus, icing on the cake. I plan on holding F for a long time, and hopefully adding to my position in the future.

Likewise, I'm not too pleased with the performance of Peyto Exploration & Development, which is a hold-over selection from my former high-priced broker. Peyto is primarily a natural gas outfit, and the current slump in NatGas prices has hurt its position. Its price has dropped so low, and I believe that NatGas prices will eventually recover, that I'm going to wait and see whether Peyto can recover as well before I cut it from my portfolio and use the proceeds for something with better potential.

On the other hand, I'm pretty excited about how #1 ranked Keppel Corporation has performed. While it comes out a little above average in the MMM Rank, it's appreciated nicely in the 195 days since I aquired it (up 16.62 percent) and is well placed in terms of how much I expect to receive in terms of annual dividends (sorry, not going to disclose that amount to you), despite having to pay foreign taxes on those anticipated dividends. This one is definitely a keeper!

These rankings and more help me keep an eye on an expanding portfolio of stocks that may otherwise be too numerous to effectively track. It doesn't just make for a pretty picture, either; I have in fact acted on what I've seen in the past, although since those stocks are no longer with me, you don't see them in the examples above.

I hope this helps you further develop your own methods of tracking your holdings. As always, I appreciate comments and feedback, which you can enter below. Thanks for taking the time to read this article, and best of luck with your investing endeavors!

Source: My Mad Method: Ranking My Portfolio

Additional disclosure: I am not a professional investment advisor or financial analyst; I’m just a guy who likes to crunch numbers and can make an Excel spreadsheet do pretty much whatever I want it to do, and I’m doing my best to manage my own portfolio. This article is in no way an endorsement of any of the stocks discussed in it, and as always, you need to do your own research and due diligence before you decide to trade any securities or other products.