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.

Steve,

Looks like a great sheet! I’m definitely going to steal some of your ideas and incorporate them into mine.

I find it too bad that Google doesn’t offer a proper dividend function in sheets, since the Yahoo solutions is rubish for European stocks.

Cheers,

NMW

Steal away! Yes, I wish Google would expand the data that you pull from Google Finance into Google Sheets.

Looks good, here are some formulas I use on mine to automate it more, whether or not its really useful, I added them for fun more than anything:

To calculate GICS classification by stock ticker:

=index(importxml(“https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=”&A2&””,”//div[@class=’sub-heading’]//span[@class=’right’]”),1)

If you cared to calculate the Morningstar Credit rating:

=iferror(index(Morningstar!C:C,match(A3,Morningstar!B:B,0)),”None”)

Payout Ratio (yours has this also)

=index(importhtml(“http://finance.yahoo.com/q/ks?s=”&$A2, “table”,32),7,2)

5 year Div CAGR

=index ( importhtml(“http://www.gurufocus.com/dividend/”&A2&””, “table”, 4), 2, 3)

Quarterly Dividend (this one is nice because not every stock pays out like this, so dividing annual you would need to be careful that you don’t miss a stock that doesn’t do quarterly and calculate wrong.. this sort of eliminates that risk)

=(index((split(ImportXML(“http://finance.google.com/finance?q=” & S2, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)),1,1))

Also have you thought about doing a dividend matrix? It is nice to see what companies are paying on what month and then the expected total for that month. Kind of fun!

Thanks for the additional codes for pulling even more data – it’s fantastic!

I’ll add a dividend matrix into the next version, as I have one in the Google Sheet I use personally for my own holdings.

Thanks for sharing this. While I know my Excel, I know very little about investments and how to integrate that with Excel in terms of formulas.

Glad you found it useful – hit me up if you have any questions or want things clarified.