Portfolio Tracking Spreadsheet, part 2

by Mike Rawson · Published · Updated
by Mike Rawson · Published August 28, 2015 · Last modified April 13, 2018
by Mike Rawson · Published March 23, 2015 · Last modified October 18, 2016
by Mike Rawson · Published July 22, 2015 · Last modified April 16, 2018
More
Too many people spend money they earned..to buy things they don’t want..to impress people that they don’t like.
Hi Mike, if you want the spreadsheet to perform a quantitative analysis of the portfolio’s holdings then you might pick up some ideas from Richard Beddard’s spreadsheet at:
http://www.iii.co.uk/news-opinion/blogs/share-sleuth/portfolio
Scroll down and there’s an embedded and standalone version of the Google Docs spreadsheet.
And thanks for pointing out that my sheet doesn’t work in Excel 2003… I have been intending to move to Google Docs or similar at some point, so perhaps I should make the move sooner rather than later.
John
Thanks for the tip, John. I’ll take a look at Richard’s sheet next week.
Hi Mike,
Thank you for highlighting my spreadsheet to your audience and also taking the time to provide your takeaway thoughts on it which in turn is helpful feedback.
My spreadsheet was originally created in Excel but I moved to Google Sheets to take advantage of GoogleFinance functions.
1. Yes the idea being the spreadsheet was akin to a trading notebook. Its focus on a previous year and expected returns rather than a full history to which the majority of long term investors consider but is very helpful as an investing checklist. I have no weighting as such but as I search for and manually input in the required figures I start to get a ‘feel’ for the stock. On most occasions I look at the previous financial statements to find the historic figures (or just released on RNS day). My attention in doing this turns to Outlook statements, chairmans view etc. So by the time I have answered all my questions and got to the end of the spreadsheet which can take around 20 – 30 minutes I have a pretty good idea if the company is a suitable investment for further consideration.
2. Thanks for pointing out that historic, expected, just released figures appear to be confusing. I have had my own concerns about how others perceive this and feel I have to agree with you. I like to see that both historic and expected/just released figures are compared for valuation purposes. 12 month trailing, or rolling calculations may be a clearer system.
3. My only irk with Google Sheets is the Conditional Formatting sometimes doesn’t work when copying a and pasting a new line. For instance red may appear when it should be green. I have to sometimes check the conditional formatting was copied across correctly.
4. the sector tab I have been changing to a drop down menu based on the Stockopedia (Thomson Reuters) 10 industry groups.
I wish you well in the construction of your Dashboard spreadsheet. It sounds like a very interesting project and I’ll follow closely with interest. If I may use the idea of colour coding the manual entry fields into my spreadsheet that would be appreciated.
best regards
Ian
Hi Ian,
Thanks for the feedback. It was your spreadsheet that inspired me to look into building one of my own. At the time it was the only UK Google Sheets version I had seen (apart from the Compound Income Scores).
Good luck with the colour coding – I’ve been doing it for so long that I can’t remember where I got the idea from.
Thanks for the link and for the detailed analysis of the different sheets. I look forward to seeing what you finally build!
So if what you’re looking for is a dashboard to evaluate new opportunities, what do people use to track the performance/return of the stocks they have? I haven’t found anything decent that includes dividends in the calculation of returns. So I have my own hacked up Excel sheet which is (just) useful enough, but needs a new tab manually setup for each new stock in the portfolio.
Hi Bob, I think that depends on your objectives for the portfolio. I don’t keep track of dividends seperately, I just re-invest them in the portfolio they came from. I track total return (stock price + dividends) instead.
So during the year I just use the broker accounts and Google Finance (which claims to track dividends and splits). With Google Finance I can track perfomance relative to an index (see here).
Then at the end of the year I just take the values in the accounts to work out my performance (see here).
thanks for highlighting my spreadsheet from Investment Moats. I try to provide two options, Yahoo Finance quotes and Google quotes. It provides more options for international investors.
however for uk it is in pennies instead of pounds, so the interpretation might be an issue.
i also look forward to see your final product. hope you can give me a heads up as well!
Hi Kyith,
Thanks for the the great spreadsheet to build on. I’ve had lots of fun adding to the sheet – there are another six articles in the series so far bayond this one.
I hope to finish the sheet and release it back into the wild next week.
Hi Mike!
I used Kyith’s spreadsheet as the basis for mine as well. I’m more focused on dividend investing but found the spreadsheet can work well for both. I have both a US only and an international version.
Interested in seeing what your finished product looks like.
Here’s my spreadsheets for reference: http://www.twoinvesting.com/investing-spreadsheets/
Forgot to say that I’m especially interested in following your watchlist analysis.
Hi Scott,
Not sure my sheet will ever be finished, but you can see the current version (and the watchlist sheet) here:
SmallCap AIM Portfolio Page