Click here to go to Sell Alert spreadsheet - Need Microsoft Excel to run this spreadsheet

The decision to sell doesn't have to be as difficult as many say. Ultimately, all you really need to do is put yourself in the place of the person who might buy any shares you choose to sell.

If you conclude that this theoretical buyer will be excited about the great opportunity he or she is being offered, it may be a sign you shouldn't rush to kick the stock out of your portfolio. If, on the other hand, you believe your imaginary trading partner will be unimpressed, perhaps it would be wise for you to get rid of that stock before too many real trading partners catch on.

The hardest part of all this is time management. You can't review every stock every day. To help alert you to the need to review stocks you already own, we offer six "Sell Alert" screens. We also provide you with an Excel spreadsheet (which includes the results of all six sell alert screens) to help you match the screens against the stocks you own, as well as scores based on how many alerts are generated and how much emphasis you should give to each category of alert.


The sell alert screens
The idea behind these screens is to identify evidence that important Wall Street constituencies have become less enthusiastic about the stock.

The six sell alert screens are as follows.

	Rating Downgrades: This screen looks at the average analyst rating (1.00=Strong Buy, 5.00=Sell) and seeks companies for which the rating increased (i.e. became less bullish or more bearish) in the past four weeks.
	Estimate Revision: This screen seeks companies for which, during the past week, analysts reduced EPS estimates for the current fiscal year or the next fiscal year.
	Short Selling: This screen seeks companies where short interest increased over the past month. Note, though, that some short transactions relate more to hedging strategies than bona fide bearish sentiment. We try to filter that out by adding a test requiring that short interest be at a high level in general--at or above 3 percent.
	Institutional Selling: This is a very simple screen that seeks companies for which institutions sold more shares than they bought in the most recent reporting period.
	Relative Price Deterioration: This screen seeks companies whose shares have under-performed their respective industry averages by at least 35 percent.
	Losing Relative Price Momentum: This is an especially sensitive screen (i.e., a very early signal). It starts with relative-share-price performance (company-share-price change minus industry-average-share-price change) for the past four- and 13-week periods. It seeks stocks for which the relative four-week performance is at least 35 percent below the relative 13-week performance. This signal is "early" because it could call your attention stocks that are still performing quite well. To avoid information overload, use this screen wisely: Set a modest importance threshold in the ticker-matching spreadsheet.

These screens are designed to call your attention to stocks you own that ought to be reviewed. This is not the same thing as a sell recommendation. Whether you sell will depend on what you determine after you refresh you analysis of the stock based on the most up-to-date information. The function of these screens, in other words, is to prompt you to do an updated analysis.


Using the spreadsheet template
The tickers from the sell alert screens have already been placed in columns K through P.

Paste tickers in Column A, starting in cell A8. The template can handle up to 100 stocks.

In row 2, paste your priority scores in columns D through I (i.e. cells D2, E2, F2, G2, H2, and I2). These are the scores you're using to determine how much importance, if any, to place on each sell alert test. By default, each priority score is set to 5. If you want to give one particular test--say increased short interest--twice as much importance, change its score to 10. If you want to give another test only half as much importance, change its score to 2.5. It doesn't matter what numbers you use. All that counts is that the relationships among the scores reflect their relative importance to you.

Column B contains, for each ticker, a Total Alert Score. The greater the number of alert tests the stock triggers and/or the higher the priority you place on the tests it triggers, the higher the score will be--and thus the more important it is that you review the holding.

Chances are that most of your stocks will have above-zero scores every time you update the template. After all, no stock is perfect.

There is no hard-and-fast rule about when a score is low enough to warrant no review. For example, you might have a 25-stock list with a 20, two 15s, and a 12.5, with ten more evenly divided between 7.5 and 5, and the rest all zeroes. The stock with the score of 20 is clearly the top priority for review. You might also choose to review the 15s and the 12.5s, but you may opt to pass on the others except for a quick glance to see if anything jumps out at you.

The next section is designed for those who wish to know the details of how the template does its job. Those who simply want answers without peeking under the hood can simply download the spreadsheet and start using it.


Under the hood
The formulas in the main scoring area (columns D through I, rows 8 through 107) do the grunt work for the screen. Each formula uses Excel's matching function to determine whether a ticker in Column A (where you listed your portfolio) matches a ticker appearing on a sell alert screen.

For example, let's consider cell D8, which contains the following formula:

=IF(ISNA(MATCH($A8,K$8:K$10007,0))," ",D$2)
The phrase MATCH($A8,K$8:K$10007,0) asks whether the item in cell A8 matches up with any item in Column K; rows 8 through 10,007. Normally, if there is match, Excel will provide a number indicating how many rows down we must count in order to find the match. If there is no match, Excel will return a #NA error message.

The error messages keep us from doing necessary additional calculations. We solve that by placing the MATCH function inside a bigger expression: ISNA(MATCH($A8,K$8:K$10007,0)). This bigger function goes one step further and provides a true-or-false answer about whether or not any match exists. We adapt these true-false answers in our work. We ask Excel to put a blank space if the ticker in A8 doesn't match any in Column K, rows 8 through 10,007. If there is a match, we ask Excel to place, in cell D8, the priority score established in cell D2.

Columns E8 through I8 are similar. In cell E8, we check to see if the ticker in A8 matches any in column L, rows 8 through 10,007--and so on for the other columns. Ultimately, cells D8, E8, F8, G8, H8 and I8 will contain blanks if there are no ticker matches, and if matches occur, the cells will have the priority scores you assigned to each column.

Cell B8 contains a formula that adds up all the priority scores, if any, in row 8. (Blank cells are treated as being equal to zero.)

^ back to top ^