Latest Posts

How to Add Buy and Sell Alerts to Investment Spreadsheet

In last week’s post, When to Sell Stock, I described a strategy for using dividend yield as an indicator to make buy and sell decisions for dividend stock investments.  Today’s article will explain how to add an automatic buy sell alert column to your Dividend Meter spreadsheet.

When purchasing dividend growth stocks, my first preference is to hold the shares long-term; however, if a stock becomes significantly over-valued, I will consider selling some or all shares of the company, especially if an attractive buying opportunity exists in an alternative investment.

To measure value, I look at a position’s current dividend yield compared to a historical range for that particular stock.  Next week, I’ll explain how I determine the high and low dividend yield values that trigger a buy or sell alert.   But for now, let’s look at an example for COP, ConocoPhillips.  Currently, I’m willing to buy more shares if the dividend yield is greater than 5%, and I would consider selling shares if the dividend yield were ever to fall to less than 3.5%.

In reference to the Dividend Meter screenshot below, here is the formula you need for column J:

=IF(H2>0.05,“BUY”,(IF(H2<0.035,“SELL”,” “)))

Buy Sell Spreadsheet Alert

Next, add conditional formatting, so if the cell indicates “Buy”, it will be highlighted in green or whatever color you prefer to use.   For a “Sell” indicator, you can additional formatting to highlight the cell in red:


Now, when you open your Google Sheet, column J will automatically show a “Buy” indicator if the current stock dividend yield is greater than the value in your formula, or a “Sell” alert if the yield is less than your lower value.   If the dividend yield is somewhere in between the high and low values, the cell will be empty.

Disclosure:  This article is not intended to provide specific recommendations to buy or sell securities.  The author holds a long positions in COP.

When to Sell Stock

When investing in stocks, knowing when to sell shares is frustrating, and very difficult to do well consistently.   The key word here is “knowing”.   Knowing is a fallacy.   Once you realize it’s impossible to always sell stocks at optimum points in time, creating a sell strategy becomes much easier.

In my own personal experience, the difficulty in determining when to sell an investment is the primary reason I’ve adopted a dividend growth strategy, where a stock sale is a rare occurrence.   What if the most important rule of your sell strategy was to never sell the investment?  If you buy shares in a company with the intention of never selling them, then the buying criteria becomes the most important first step of your sell strategy.

Here’s my sell strategy – it starts with several buy rules.  Stocks I buy meet at least four out of the five requirements:

  • Buy companies that provide necessary goods and services for everyday living.   Examples include food companies, utilities, waste disposal, household goods, and oil.  My favorites include General Mills, American Water States, Waste Management, and ConocoPhillips.
  • Buy companies that provide highly desirable or addictive consumable products.  I love drinking Dr. Pepper.  The only way I can experience the taste again after finishing a can is to buy another one.
  • Buy companies that are leaders in their industries with strong brand names.  Think chocolate = Hershey’s and cell phones = Apple.
  • Buy “shareholder friendly” companies.   This is characterized by a long-term history of raising dividends and having a balance sheet that shows growing amounts of treasury stock. Treasury stock are shares of stock bought back by the company, reducing the amount of outstanding shares on the open market.
  • Buy companies led by great people with a vested interest in the success of the company.  Look for high insider ownership of stock and leaders that possess a prior history of success.

After buying a stock that meets the above characteristics, here are the sell rules:

  • Sell the stock if the company cuts the dividend.   All companies go through painful economic downturns and competitive market challenges.   The great companies, the ones worth holding on to, are those that can weather the storms and still maintain or continue to grow their dividends through tough times.
  • Never sell the stock as long as the dividend yield stays within the range of x and y.  With each stock, I establish low (x) and high (y) dividend yield figures, values at which I would consider either selling or buying more shares.   For example, if a stock experiences a significant run-up in price, the dividend yield will fall, and it may be a good time to take some profits and move the proceeds into a different investment.   I establish low-yield sell figures at extreme points – outside of a normal range, to make it difficult to sell the position.   However, occasionally an opportunity arises when a stock’s price has risen abnormally high.  During these over-valued situations, I’ll consider selling some or all of the position, especially if an alternative buying opportunity presents itself.  Be sure to check back next week for my post: How to Add Buy Sell Alerts to Investment Spreadsheet, to learn how you can create a column in your spreadsheet that will help you see opportunities to sell over-valued stocks and buy other under-valued positions.

That’s it – only a couple of sell rules, designed to minimize selling in the first place and keep me invested for the long haul in quality companies with stable, growing dividends.

Disclosure:  This article is not intended to provide specific recommendations to buy or sell securities.  The author holds long positions in GIS, AWR, WM, COP, DPS, HSY, AAPL.


How to Add Dividend Increase Alerts to Spreadsheet

Keeping a transaction journal in my Dividend Meter provides a history of dividend compounding at work over time, so it’s important to record new purchases of stock and dividend increases as they happen.   When new shares of stock have been purchased, it’s easy to determine why the needle on the Meter moved higher because I know what stock I just bought.   However, since Google Sheets automatically updates the cells with each opening of the spreadsheet, it can be difficult to quickly determine why a Dividend Meter reading is higher than the last recorded journal entry.  It’s either an error in the dividend amount, or a dividend increase has occurred, which is always a nice surprise!

Applying conditional formatting is a simple modification you can make to your Dividend Meter spreadsheet to easily determine if a company has increased or decreased their dividend.  Simply click the cell containing the annual dividend figure, select the Format tab, and choose “Conditional formatting…”

Dividend Increase Alert

In the Conditional Formatting pane, click the selection box under the heading “Format cells if…” and select Greater than.  Manually enter the current dividend figure for the stock.  Change the fill color to whatever color you desire – my preference is green for a dividend increase and red for a decrease…

Dividend Increase Value

To add an alert for a dividend cut, click “Add another rule” at the bottom of the Conditional Formatting window…

Add Dividend Cut Alert

To add a dividend cut alert, select Less than…, enter the same dividend figure (should already be filled in), and change the fill color.   Then click Done.   Apply the same steps for each security on your dividend tracking spreadsheet.   Google Sheets will remember the conditional formatting parameters.  Going forward, you’ll only have to change the dividend figure when/if a dividend increase or cut occurs.

Setting Dividend Cut Alert

Now, when you open your Dividend Meter, and the gauge chart shows a different value than your last recorded journal entry, you should be able to quickly identify which stock increased or decreased its dividend.