Share Sleuth: an investment decision engine

Share Sleuth portfolio spreadsheet

The Share Sleuth spreadsheet is the fount from which (almost) everything else flows. It provides the data for my articles and the decisions that determine the performance of the Share Sleuth portfolio.

In recent months though, I've realised it's better at providing data than it is at helping me make decisions. In fact, its size and complexity, may have been getting in the way of decisions.

The idea that the spreadsheet should help with decisions should have been obvious, but it was only after reading Misbehaving, Richard Thaler's book on behavioural economics, that I realised I had evolved something resembling the chaotic state of my own mind.

It incorporated errors, different standards for different companies, and dozens of half finished and out of date sheets modelling the finances of companies I'd lost interest in.


To impose order on the spreadsheet, I'd manually created a hierarchy on the summary sheet, dictating which companies to add, which to watch, and which to eject.

Unless I tended to this hierarchy every day, it too became out of date and companies were lost in it. In any case, a company's categorisation was decided by my sometimes muddled data and thought processes.

Thaler is famous for inventing (or perhaps popularising, I'm not sure) the 'nudge' concept; that people can be encouraged to make better decisions if organisations make it easier for them.

In investing, working things out is fun, it means asking questions and answering them. The difficult bit is making good decisions. Instead of me telling the spreadsheet which shares to add, watch, or eject, I needed it to nudge me.

You're probably wondering why I labour collecting data only to replicate what stock screening software can do. Well I'm not going to replicate what screening does.

Stock screening and portfolio management software for private investors has advanced enormously in the UK over the last few years, and an individual couldn't hope to match the range of companies and ratios offered in Stockopedia or SharePad, say. I use them to get a quick fix on a company. I don't use them to make decisions because I think my own data is better.

I can collect my own data (mostly from annual reports) because I'm only interested in a few ratios, and a subset of companies, generally those that are stable and have been listed and domiciled in the UK for many years. Preferably, they are either ignored by the City, or being shunned by it. That group of companies is the squad from which I pick the team.

My data is adjusted so it incorporates things commercial data sets leave out. I can capitalise operating leases, ignore acquired intangibles, include defined benefit pension deficits, and build it all into a company's market valuation. I can also incorporate judgement.

To get into the squad, I must believe a company is likely to be as profitable in 10 years time as it is now, so the spreadsheet only needs to tell me if I've discovered any reason that might not be, or that shareholders might not receive the benefit. I've already taken the decision to add a share to the spreadsheet. It needs to tell me if I made a mistake.

Let's take MS International as an example.


The three coloured bars you see in the Risk column are the recommendation of the decision engine. They summarise the valuation risk and investment risk, whether the spreadsheet fed with my data and judgements thinks a share is cheap and the company will remain prosperous, or the share is expensive and the company troubled.

The spreadsheet generates its recommendation, automatically, from the information I put in during analysis. It's uncompromising, bullying me into facing what I might not be able to see in the blizzard data, or what I might not want to acknowledge.

To see the verdict of the decision engine more closely it may help to view the spreadsheet full screen.

The line from the summary sheet for MS International tells a story:


The company's year end is May 2015, so my data is up to date. MSI's earnings yield, a very simple measure of return, is 7 per cent. It's just below 8 per cent, my benchmark for value. MS International is not obviously cheap or expensive so the first coloured box is yellow.

2015 may have been an unusual year, though. MS International's average profitability over many years is much higher, and if it had been that profitable in 2015, it's earnings yield would be a whopping 18 per cent. That's obviously cheap, well over 8 per cent, and so the second box is green.

I reckon MSI is a good business suffering a bout of diminished profitability. Normally I would put more emphasis on the second earnings yield calculation, and assume 2015 was an anomaly. But I'm worried shareholders might not get a fair share of the spoils, so the investment risk box is red.

In the decision engine there's a fourth column, which relates to portfolio risk.

It shows that Share Sleuth's holding in MS International is 5 per cent of the portfolio's total value. To ensure diversity I start wondering whether to reduce a holding at 5 per cent and must take action at 10 per cent. The box in the final column is, therefore, yellow.

In short, I've got serious concerns about MS International, and it's a substantial holding I should be thinking about reducing.

Compare it with Castings.


My words may be a little cryptic (for expanded commentary you can click on the hyperlinked company names in the spreadsheet for my articles), but hopefully you get the picture:

Green is add. Red is bad.

Ultimately I take the decision, but the decision engine makes it easy for me.


An investor is only as good as the decisions he makes, so the spreadsheet is my competitive advantage.

So why give it away? You can read the spreadsheet on the Share Sleuth home page, you can view it full screen, or you can request access to the original in Google (just mail me, with your Google account's email address). From there you can view the formulas and clone your own copy to adjust as you will.

Over the years I have shared the spreadsheet with a grand total of 29 investors. One of them couldn't sleep the night before I started my upgrade project and emailed me at 5am to question two calculations.

I'd like to say I was up, playing the Japanese market, but I picked up his message later that morning and, realising he was right, thanked him profusely, reflected on my good fortune, and set about correcting the errors.

I could have replicated them for every company in the portfolio, and every target. His generosity, and our collaboration, saved me a little embarrassment and, hopefully, a second, corrective, update.

Competition, George Cooper reminded me recently, isn't every man for himself. We collaborate as friends, colleagues, and campaigners and thereby compete more effectively.

My hope is that some people will adapt my spreadsheet, create their own competitive advantages, and maybe some of them will share what they learn with me.

Just remember, there are bound to be mistakes in it. The decision engine may be a computer program, but it's powered by a human.


It may help to have a copy of the spreadsheet open as you read this (see above for how to get it).

There are now two summary sheets, Portfolio and Targets, shares in Share Sleuth, and shares I'd like to add to it.

For a list of all my articles about a company, click on its hyperlinked name in the summary sheets.

The summary sheets also contain more prosaic data; the size of the company (it's enterprise value), the date of last piece of regulatory news published about the company and a link to the story, and the estimated date of its next AGM. You may need to scroll to the right to see this information.

Where I have not yet input at least six years of historical financial data, the average earnings yield column says n/d (no data). For some companies, it may not be necessary to calculate an average. But for some it is, and I'm working on it.

The finances of each company are modelled in its own sheet, which you can get to by clicking on the appropriate ticker codes on the tabs along the bottom. Companies I'm no longer following are removed to a slush pile, which is not publicly available because I'm not keeping the data up to date. Sometimes I may chose to rescue a share from the slush pile, and reinstate it as a portfolio member or target.

I've updated the template, and all the sheets for shares in the Share Sleuth portfolio so that:

The tax rate is the current UK corporate tax rate of 20 per cent

Operating leases are capitalised at seven times annual lease payments, fixing an error in a minority of spreadsheets that capitalised at the wrong rate

The book value of defined benefit pension deficits and non-controlling interests are included in a company's enterprise value.

An error that added a company's net debt to its operating capital nonsensically has been fixed.

I still have to convert some of the target share sheets to the new template, which is why the Target summary sheet looks a little barren. Over time, I will fill it!

I update the spreadsheet and correct errors as soon as possible, but there no guarantees...


The funniest, truest words ever written about the role of spreadsheets in investment were written by Wexboy, a blogger, two years ago. They're halfway down this post (viii Worship the spreadsheet).

I'm hoping my wholesale adoption of conditional formatting takes me into the high priesthood.

Subscribe to Money Observer magazine



Post new comment

The content of this field is kept private and will not be shown publicly.
By submitting this form, you accept the Mollom privacy policy.