# Portfolio Tracking Spreadsheet part 8 plus SmallCap update 11

Today’s post is a catchup on the SmallCap (AIM) portfolio, plus a bit more work on the portfolio tracking spreadsheet.

Contents

###### SmallCap AIM Portfolio

It’s been another interesting couple of weeks in the markets. The FTSE went back up to 6450 but is now falling again and is below 6300 as I write.

My plan is still to put together a watch list for a potential rally into Xmas.

We still have 26 holdings in the SmallCap portfolio, and £4K of the original £30K cash to find a home for (plus an extra £28K of cash in reserve).

###### Performance

The portfolio went up more than 2% this week, and is now up 10.8% over 7 months.

There were no major problems this week.

XL Media took a hit in a read-across from an internet advertising firm (Algorithms?), but has now recovered somewhat to -3.7%.

Stanley Gibbons fell further and is now down 65%.

On the plus side Tristel is now up 63%, and Ab Dynamics, Cohort and FW Thorpe are all up more than 40%.

Thanks to a reader, I discovered that Trifast is not listed on AIM, but on the main market. That’s the danger of converting a SmallCap portfolio to an AIM portfolio.

Trifast hasn’t made much difference to the performance of the portfolio. It’s up 9.8% against the portfolio’s 10.8%. I’ll remove it as part of the next wave of buying between now and Xmas.

The FTSE All-Share was up a net 3.1% on the week, so the portfolio lost 0.8% against it. The index is now down 5.1% and the portfolio’s lead has been cut to 15.9%.

The AIM index only rose by 0.1%, so we gained more than 2% against it. The AIM index is up 2.5% and the portfolio lead is 8.3%.

A good week for us on the whole.

###### Portfolio tracking spreadsheet

Here’s how we left the Dashboard tab of the Portfolio Tracking Spreadsheet:

We have five traffic lights and a score out of 50 for each stock.

Here’s how the Yahoo data tab looks:

Everything to the right of column G (200 Day MA) hasn’t been used yet.

###### Today’s objectives

We have three objectives for today, time permitting:

- add some more traffic lights using the extra Yahoo data
- make a stripped-down Watchlist version of the Portfolio Tracking Spreadsheet
- add in all the hot AIM stocks from Twitter and the blogs and see how they score

After that, the next task will be tobuild a single stock, historic data sheet going back one year. This will be used in future posts within our Technical Analysis series.

###### Extra Yahoo data

Here’s a list of the extra Yahoo data, plus a guess at which metric each piece can be used for:

- Book value per share = price to book
- Earnings per share (historic) = ??? (we already have PE ratio)
- EPS estimate (future) = earnings growth, with historic earnings
- Shares float = free float
- EBITDA = Profit margin (from Revenue, see below)
- PE this year = ??? (we already have PE ratio)
- PE next year = PE change, with PE this year (similar to earnings growth)
- Revenue = price to sales

I will probably average together the changes in EPS and PE to get a single figure for “next year’s growth”.

So that’s another five measures to add to our existing five.

There appeared to be some data issues for certain stocks last week, so the scores may require cleaning up to avoid introducing too much bias. We’ll deal with each measure in turn below.

###### Measures by type

Let’s first group our ten traffic lights by type, to see if we have a fair spread:

- momentum (price action)
- gain / loss %
- position in annual range
- MAs signal

- valuation
- PE
- Price to Book
- Price to Sales

- growth
- blended growth (PE and earnings combined)

- quality
- Profit margin

- fundamentals
- Market Cap
- Free float

So not a bad spread of ten measures across five categories, with the expected bias to value and momentum (which are after all the most useful factors in stock investing).

This isn’t enough detail to find the next AAPL, but is should help us sort the wheat from the chaff down in the AIM casino.

So much for theory, let’s see how it works in practice. We already have our three momentum indicators, so let’s start with valuation.

###### Valuation signals

The first thing I did was add a new conditional format to the Gain / Loss column. Gains over 50% (Tristel) now show as green on bright yellow – the same formatting as used for stocks at 100% of their yearly range (ie. at new highs, like Crystal Amber Fund).

**Price to Book** (PB) can be calculated easily by dividing the price by Yahoo’s “book per share”. Crystal Amber Fund produces an extreme value, but the rest of the value are between 1.1 and 2.3. I decided to add a maximum value for now of 3 (we’ll review this as we value more stocks).

Scoring a range from 1 to 3 out of 10 involves subtracting 1, multiplying by 5 and subtracting the result from 10:

PB Score = 10 - 5 * ( ( MIN ( PB , 3 ) - 1)

This means that a PB of 1 would score 10, and a PB of 3 scores 0. If we come across a PB of less than 1, we’ll use a MAX() function to bring it back up to 1.

Calculating **Price to Sales** is complicated by the Yahoo Revenue function returning a string with an ‘M’ at the end.

This can be fixed using the LEFT() and LEN() functions to take all but one character from the string.

Revenue1 = LEFT( Revenue, LEN(Revenue) - 1)

Helpfully this also converts the value from a string to a number, so we can divide it into the Market Cap.

Doing this highlighted DART group, which has sales of more than £1bn. The trailing character here is B rather than M, so we need to multiply by 1000 if this is the case.

Revenue2 = IF( RIGHT(Revenue, 1) = "B", Revenue1 * 1000, Revenue1 )

Now we can divide Market Cap by this cleaned Revenue number. The “normal” range for Price to Sales (PS) is 0.6 to 6.8, so we can cap the outliers at 7. Revenue Data is not available for a couple of stocks, so we will assign this maximum value here for now.

Scoring a range from 0 to 7 out of 10 involves multiplying by 10/7 and subtracting the result from 10:

PS Score = 10 - 10/7 * ( MIN ( PS,7 ) )

This means that a PS of 0 would score 10 and a PS of 7 would score 0.

Here’s how the dashboard looks with the two extra values:

The scores have been added to the total, but Column E hasn’t had its conditional formatting updated to reflect the fact that the scores are now out of 70. This makes it look like every stock is doing better than it really is.

###### Growth signal

There’s a lot of missing data in the EPS and PE numbers from Yahoo, so some cleanup was needed. If either current or future Earnings data was “N/A”, then Earnings Growth was 0.

Similarly, if either current or future PE was zero, then PE Shrink was 0. The combined Growth figure is just the average of the Earnings Growth and the PE Shrink.

Growth rates varied from -37% (XL Media) up to 149% for Stanley Gibbons (presumably anticipating a rebound from a horrible year). I’ve capped the range at -25% to +25%.

Scoring this range out of 10 means adding 25% and multiplying by 20.

Growth Score = 20 * (Growth + 25%)

Thus a Growth rate of -25% scores 0 and a growth rate of 25% scores 10.

Here’s how the dashboard looks with the growth column added:

The scores have been added to the total, but Column E hasn’t had its conditional formatting updated, so now almost every stock looks like a good ‘un.

###### Quality signal

The EBITDA data comes with trailing Ms, and need cleaning up in the same way as the Revenue figure. Then the two numbers are used to produce a “margin”:

Margin = Clean EBITDA / Clean Revenue

Margin ranges from -92% at Imperial Innovations to 33% at Polar Capital Holdings.

As with Growth rates, I’ve capped the range at -25% to +25%, and added 25% and multiplied by 20 to get a score out of 10.

Margin Score = 20 * (Margin + 25%)

Thus a Margin of -25% scores 0 and a Margin of 25% scores 10.

Here’s how the dashboard looks with the margin column added:

The scores have been added to the total, but until we reformat Column E, all the stocks look good.

###### Fundamentals

To work out the free float, we need to multiply the Yahoo Shares float by the current price, and compare the result to the Market Cap.

Float% = ( Shares Float * Price ) / Market Cap

This Float % can then be multiplied by 10 to give a score:

Float Score = 10 * Float%

That completes the scoring from the Clapham jury. We now have 10 traffic lights, each converted to a score out of 10. These are added together to give a total score out of 100.

The scores range from 48 for Imperial Innovations up to 78 for James Latham, so the range of scores has been compressed down to 31.^{1}

This is something we will investigate next time – there may scope to tweak the scores to give a greater dispersion.

Here’s how the completed Dashboard looks:

The Score column (Column E) has been reformatted to reflect the current range of scores.

###### Building a Watchlist Spreadsheet

The next step is to simplify this work sheet for use as a Watchlist Sheet, and then load into it all of the AIM stocks that my friends on twitter and the blogs have mentioned over the past six months.^{2}

I’ve just checked that list and it has 84 stocks on it. The first task is to check that they are all AIM stocks (see Trifast above).

The first step is to get hold of a list of AIM stocks. I found it on a site called AIM Listing – there are 1289 stocks on their list.

Then I could lookup my list of 84 stocks against their list of 1289. I had to reformat their table to have the EPIC code in the first column, but that was about it.

It turns out that 37 of the stocks are AIM, and 47 have a main listing. We’ll leave the second group for another time, and focus on the AIM stocks for now.

One of the 37 AIM stocks is already in the SmallCap portfolio, but here is the list of the other 36 stocks:

###### Simplifying the Portfolio Tracking Spreadsheet

To create a Watchlist Spreadsheet, we only need three of the tabs from the Portfolio Tracking Spread:

- Stocks, to hold the list of companies and their codes
- Dashboard, where the scores and traffic lights appear
- Yahoo, where the Yahoo data is retrieved

It should be possible to move the data from the Stocks tab directly into the Dashboard at some point. In practice there is a problem in removing the Transactions tab so I will pause here while I debug the error.

We’ll pick up on the Watchlist Sheet, and get started on the Single Stock Historical Data Sheet next week.

Until next time.

*This article is part of a series on the SmallCap AIM portfolio. The rest of the articles – together with a live tracking spreadsheet – can be found here.*

*Share this with , Twitter, Google+, Pinterest, LinkedIn, Tumblr, Reddit and StumbleUpon.*

Hello Mike,

I read the serie of article you wrote about Portfolio Tracking Spreadsheet and I would like to adapt your work for my French Portfolio. Could you please make a copy of the tracking spreadsheet available?

Thank you.

MC

Hi MC,

I emailed you a link to the sheet. Let me know if you have problems.

Mike

I well received your email. Thank you for your quick reply. I will further study and adapt this spreadsheet in the coming weeks. I recently discovered your blog (after reading MMM) and I really appreciate it.

Great.

Let me know if you make any improvements and maybe I can add some of them back into my version.

Hi, your spreadsheets are amazing and been looking at something like that to adapt to my AIM penny stocks trading but I believe yahoo finance has stopped having their API service, are you still in operation? have you got the spreadsheets still working?

Hi Martin,

Yes, yahoo doesn’t work anymore, but Google finance sheets still works.

7 Circles is very much still in business – four new posts a week.

Mike