Backtesting portfolio strategies

bigfreddiel

Have you been looking for a way of backtesting a portfolio strategy?

Well I have, but failed miserably till today.

I've actually been using google drive for a while so had my answer but didn't realise it.

With Google drive you can use the googlefinance functions and on of those retrieves a share price for any particular date.

So there you have it, set one cell to a date in the past, get the share prices based on that date, do the same for today, subtract one from the other and you can easily calculate your portfolios performance.

Then do your what ifs by just changing the date. Simples

No doubt there are other ways, but this is simple.

One more tip, if googlefinance cannot find your equity, try preceding it with LON: or appending it with .L

Cheers fj

Apodemus

I would also add cells for RPI (or CPI if you prefer) at start and finish as a measure of performance against inflation. I appreciate that growth and yield against comparison investment indices is important, but for me the crucial aspect is comparison against life costs and yield against the current value of the sums invested.

bowlhead99

Is there a way for the Google Finance lookup tell you what dividends a stock paid and when? On the google website you can get see it as markers on a time graph, though it isn't clear whether these are payment dates or ex-div dates, and either way I couldn't see 'dividend' as attributes to select when doing a lookup (https://support.google.com/docs/answer/3093281?hl=en )

If you're trying to backtest returns it's obviously important to capture total return, and not just think a share got cheaper due to market noise when actually it got cheaper because it paid out a 4p dividend or whatever. There's no point trying to bring in things like RPI/CPI to figure out real performance if you haven't first addressed the fundamental problem of capturing all the return.

bigfreddiel

bowlhead99 wrote: »

Is there a way for the Google Finance lookup tell you what dividends a stock paid and when? On the google website you can get see it as markers on a time graph, though it isn't clear whether these are payment dates or ex-div dates, and either way I couldn't see 'dividend' as attributes to select when doing a lookup (https://support.google.com/docs/answer/3093281?hl=en )

If you're trying to backtest returns it's obviously important to capture total return, and not just think a share got cheaper due to market noise when actually it got cheaper because it paid out a 4p dividend or whatever. There's no point trying to bring in things like RPI/CPI to figure out real performance if you haven't first addressed the fundamental problem of capturing all the return.

Good point, and one I had thought about. There is an eps earnings per share option, not tried it on uk stock, which could be used I guess. Another thought I had was to use the yield of a share to estimate the affect of divis, not ideal, but a start. Anyone got any other ideas on this?

Cheers fj

bigfreddiel

Scrub all that, good as googlefinance is I've been playing with the portfolio functionality on the Investors Chronicle web.

On their you can create a portfolio, set it to reinvest divis, and set the date you wish to do an add trade to the portfolio. That will then use the price at that date and automatically roll up the divis into more shares, job done.

You can set the portfolio up to manually manage divis, or to park the divis into a cash account. The options would suit every style of portfolio investing strategy.

I think both options googlefinance and/or an IC portfolio have their advantages and disadvantages.

Googlefinance for quick what if analysis, and IC portfolio for a more accurate simulation. And the best of it is both tools are free, only your time is needed.

Cheers fj

Apodemus

bowlhead99 wrote: »

If you're trying to backtest returns it's obviously important to capture total return, and not just think a share got cheaper due to market noise when actually it got cheaper because it paid out a 4p dividend or whatever. There's no point trying to bring in things like RPI/CPI to figure out real performance if you haven't first addressed the fundamental problem of capturing all the return.

I completely agree in terms of calculating total return.

I guess there are many different ways of keeping score, and my main concern is to meet my own goals rather than comparison to the market. My goal is to have investments where the underlying value keeps pace with inflation while returning a 3% annual dividend return on the inflation-adjusted sum invested. I can keep an eye on that by reference only to the most recent dividend data and my RPI-adjusted costs, without the same complexity that would be needed to calculate actual total return.

Lakeuk

I use excel with the free Stock Market Functions add-in, get details from yahoo finance, think it also supports other free data sources but not tried them.