Crunching the Numbers – Bonkers Portfolio 2

Today we’re going to try to bring the Bonkers Portfolio one step closer to reality, by crunching some price data.

Bonkers Portfolio

A couple of weeks ago we looked at a simple momentum strategy that has produced exceptional results over the long term.

• The key point of interest for me was that it didn’t require me to free up the cash to start a spread betting account

The strategy is to choose the stocks and funds that have increased in price the most during the previous period.

• The original bonkers portfolio consisted of a single fund held for a year
• To make things more interesting for readers of this blog, we will change funds more frequently
• To counteract the increased volatility this will introduce, we’ll use more funds and stocks, and unequal weightings
• We’ll also use a manual override to avoid choosing multiple stocks and funds from the same sector

The portfolio we ended up with looked like this:

1. Four funds from the Fidelity platform I already have an account with, weighted 4:3:2:1
   • buys and sells on this platforms should be free, and carried out  at a mid-price
   • so we will rotate these funds every month
2. Four stocks from the FTSE-100, weighted 4:3:2:1
   • there are trading costs here (commission, stamp duty and spread)
   • so we will rotate these stocks every quarter
3. Four stocks from the AIM-100. weighted 4:3:2:1
   • we hope to benefit from the greater volatility at this end of the market
   • there are trading costs (commission and spread)
   • we will rotate these stocks every quarter
4. The LON:IWFM momentum ETF from iShares
   • this is the buy and hold portion of the portfolio, weighted at 5

So the whole portfolio will add up to 35 units (probably £35K), spit 10:10:10:5 between the four sections.

Now we need to choose our funds and stocks.

FTSE-100 stocks

We’ll start with what should be the easiest section, the FTSE-100.

Getting hold of a list of the FTSE-100 constituents is surprisingly difficult.

• The London Stock exchange (LSE) has one, but it’s split over 6 web pages

I copied and pasted the six pages into a Google Sheet: ((If anyone knows of a better source, please let me know ))

We won’t need column D with yesterday’s price. I added four new columns:

1. Google ticker
2. Today’s price
3. Old price
4. Change in price %

The Google ticker is formed by adding “LON:” to the Stock Exchange ticker:

G-ticker = Concatenate("LON:", ticker)

We also have to get rid of the trailing “.” that is used by the LSE at the end of two-letter tickers, but is not used by Google.

I had hoped that we could do this using the Substitute() function to swap “.” for nothing (“”):

Ticker2 = Substitute ( ticker, ".", "" )

(The extra spaces in the line above are just for clarity – don’t actually use them.)

So the combined function would be:

G-ticker = Concatenate("LON:", Substitute ( ticker, ".", "" ) )

Unfortunately,  the LSE also uses a third format of ticker, exemplified by BT.A

• so we don’t want to replace every “.” with nothing, only the “.” when it appears at the end of a code

Annoyingly,  BT is the only stock in the FTSE-100 with this format.

The way around this is a three-step process, using the Trim() function:

1. replace every “.” with a space (” “)
2. use Trim() to remove trailing spaces
3. convert back any remaining spaces to “.”

Here’s the formula (deep breath):

G-ticker = Concatenate ( "LON:", Substitute ( Trim ( Substitute ( ticker, ".", " " ) ), " ", "." )

It looks more complicated than it is. ((Once again, if anyone can think of a simpler way to do this, please shout ))

Next up is today’s price – we’ve seen this movie before:

P-Today = GoogleFinance ( G-ticker )

The old price from the previous period is slightly more complicated.

• for live data, GoogleFinance() returns a single number
• for historical data it returns a 2 x 2 array

We can get around this using the Index() function, which lets you choose a single cell from an array:

P-Old = Index ( GoogleFinance ( G-ticker, "close", Today()-90 ), 2, 2 )

This chooses the bottom right of the four cells returned as historical data.

• You also need the “close” to tell GoogleFinance() which part of the old date to return
• As written the formula returns the price from 3 months ago

The last of the four columns is the percentage price change:

Change % = ( P-Today - P-Old ) / P-Old

I’ve added conditional formatting here to turn negative percentages red.

Here’s the completed sheet:

The sheet shows old prices from 6 weeks ago, since we are already halfway through the first quarter of 2016, and the first holding period for these stocks will be shortened.

Now all we have to do is sort the sheet in descending order of percentage change:

The first four stocks are all rebounding resources stocks, so our manual over-ride comes into action.

We will be buying:

• 4 units of Anglo-American
• 3 units of Tesco
• 2 units of Rolls-Royce
• 1 unit of Burberry

It doesn’t look too promising, does it?

AIM-100 stocks

The process for the AIM-100 is exactly the same.

• The constituents are found on the LSE site.

Here’s the completed sheet:

And here’s the sheet in descending order of percentage change:

There are no sector clashes amongst the top four here, so we will be buying:

• 4 units of Majestic Wine
• 3 units of Plus500
• 2 units of Pinewood
• 1 unit of Faroe Petroleum

Another scary bunch.

Fidelity Funds

This section of the Bonkers Portfolio is a lot more open to interpretation.

• I’ve decided to start in the simplest way possible, with low-cost index funds.
• We can always complicate matters later.

The Fidelity platform has 12 index funds:

• 3 * UK equity (FTSE-100, FTSE-250, FTSE-All Share)
• 3 * UK bonds (gilts, index-linked and corporate)
• 6 * international equity (Europe, US, Japan, APAC, Emerging, World)

This is a decent starting point, although maybe a China fund, an international bond fund and a commodities fund would help.

Here’s the list of funds in detail:

Even with the platform charge included, they are pretty cheap.

The next step is to get hold of some price data. Luckily, most of the funds are available on GoogleFinance().

Finding the code requires a bit more work:

• you have to use the regular Google Finance UK price screen to look up the name of the fund
• the GoogleFinance() code is shown next to the name
• they are very complicated – you wouldn’t be able to guess them

Here’s the Google Finance graph for the UK index fund:

The syntax for looking up prices is different for funds, too.

• In fact, you don’t check prices, but instead you get the %age return over a given period (1 week, 4 week, 13 week, 52 week and so on).
• I went for 4-week returns

Here’s the syntax:

4wk return = GoogleFinance ( G-Ticker, "return4" )

Here’s the full list of index funds with their codes, prices and returns:

Only the World Index fund is missing.

• This is not much of a loss as I wouldn’t expect the Word Index to be at the top of the charts for much of the time.

Here’s the list sorted in order of descending 4-week returns:

Here’s what we will be buying:

• 4 units of emerging markets
• 3 units of APAC
• 2 units of FTSE-100
• 1 unit of FTSE-All Share

Conclusions

We’ve made good progress today:

1. We’ve put together stock lists for the FTSE-100, AIM-100 and Fidelity index funds
2. We’ve developed the formulae to give automatic price data for these lists
3. We’ve selected our hot four stocks / funds from each of the lists

Over the next few days I’ll actually make the purchases for the Feb/Mar round of the Bonkers Portfolio.

• I’ll re-check the data on the days I make the trades, so it’s possible that this list of 12 stocks / funds (13 with the momentum ETF) may not exactly match what I buy

Then I’ll be back at the end of March to report on the results and reveal the next set of selections.

Until next time.