I’ve had some people ask me recently about how I manage my investment watchlist and my holdings. Specifically, they wanted to know how to import data automagically so that most of the process was automated. Well, I suppose you might be interested in this as well so I’ve decided to build a template from scratch and share with you the basic foundations of how my own spreadsheet runs. I’ve migrated away from using Excel to Google Sheets, so this template is a Google Sheets template. The beauty of Google Sheets is that it is fairly simple to import data from Google Finance. The downside is that the data you can import easily is limited. Alas, this is a fairly basic spreadsheet so we won’t worry about that for now.
Here is what the spreadsheet looks like:
In this sample spreadsheet, I’ve broken the investment components into the 10 major industry sectors and placed some of the largest market cap companies into them – 10 sectors, 10 companies (please note, this is not a recommendation to purchase ownership in any of these companies).
The first thing I do with my own watchlist is to put all earnings on even footing so I can compare the net earnings and dividends amongst all the different companies. Why? Because I’m interested in business ownership and what each piece of ownership provides in net earnings and dividends.
The way I do this is to put a hypothetical $1,000 investment into each company and calculate the number of shares that $1,000 will buy. Of course, the number of shares stretches to the umpteenth decimal place, but for simplicity sake I round it up to the nearest number (don’t worry, the figure in the cell actually represents the number of shares to the umpteenth decimal place, so the corresponding net earnings column accurately reflects what a hypothetical $1,000 investment buys you in earnings and dividends).
The only place where the numbers are highlighted are the net earnings and dividend columns as this is what’s important: what your ownership in these businesses will generate for you in net earnings and cash in hand via dividends.
There’s also columns that calculate a quick-and-dirty dividend payout and retention of net earnings. This is to give you a general idea of what the businesses payout in cash to you and what they retain to grow the business (or return via buybacks, but that’s beyond the scope of this spreadsheet).
The spreadsheet will automatically grab the earnings per share figure through Google Finance. It will also grab the dividend amount and yield through Yahoo Finance. Look at the coding in either when you go to change up the template to your customization and I think it should be fairly straight forward on what you need to change to get the data you are looking for (all you have to do in the Yahoo Finance codes are to find the ticker symbol and replace it with what you want).
Word of caution: sometimes, Google Finance and Yahoo Finance do not display correct figures. Most of the time they do. Just keep that in mind and always double check with actual earnings and dividend figures reported directly by the businesses.
Finally, there’s a little column that will track the day’s shift in the stock price from the previous close, turning green if it is positive and red if it is negative.
So this is a pretty basic, bare-bones foundation to start with for an investment watchlist. There are some other features I’m thinking of incorporating into this, so be on the lookout sometime next week for version 2.0.