A Quick Comparison Plot using LSE Share Prices Sourced from Quandl

If you have read my last blog post you will know that I have now ditched Yahoo Finance as my information source for UK shares because of the data quality problems. Instead I’m using Quandl. The Quandl website has a nice plotting facility but unfortunately only for a single share, making comparisons more difficult. This short R script allows you to obtain data from quandl (for as many securities as you want) and then do a quick comparison plot. You can change the frequency of plotting and the start date. Because it is specifically for comparison, the script discards rows of data where information is missing. Here is the script for ISF (ISH CORE FTSE100 ETF) and CUKX (ISH FTSE100 ACC ETF). If you ever needed an example of the power of reinvesting dividends, this has to be a good one!


tickers <- c("ISF", "CUKX")  ## put as many securities as you want here
mydata <- Quandl(paste0("LSE/", tickers, ".1"), type = "xts", api_key = "XXXXXX")
mydata <- na.omit(mydata)
colnames(mydata) <- tickers

startdate <- "2010-01-01/"  
mydataused <- mydata[startdate]
indx <- xts(apply(mydataused, 2, function(x) x/x[1]), order.by = index(mydataused) )

indx2plt <- indx[endpoints(indx, on="months"),]    # "days", “weeks”, “months”, “quarters” or “years”

chart.TimeSeries(indx2plt, main  =  "Cumulative Returns", legend.loc = "topleft",
                 minor.ticks = F, lwd = 2 )



Time to ditch Yahoo Finance as the go-to source for stock price data?

I am a UK based, private investor. I like to do my own analysis of potential stock purchases and R is my tool of choice for much of this. One of the biggest problems as a private individual is obtaining high quality data – I just can’t justify the cost of a commercial data supplier. The normal approach is to use Yahoo Finance to download the data you need and this can easily be automated into R – either manually or using the quantmod package. However I’m increasingly frustrated by Yahoo finance. In fairness I’m only concerned with shares and ETFs that trade on the London Stock Exchange and it’s entirely possible that Yahoo doesn’t have these problems for US quoted shares. My main concerns are as follows.

1. Data Quality

I’ve written about this before. Here is another example, this time for iShares Core FTSE 100 Dist (ISF.L). I’m assuming the currency keeps changing between  GBX, GBP and either USD or EUR.

2. Dividends

Dividends quoted on Yahoo are sometimes in the wrong currency. (e.g VUSA).

3. Adjusted Close Prices

This is a bit hit and miss. As you can see from the screen shot above the price has been adjusted. Frequently there is no adjustment and no dividends listed when they have been paid.

4. Questions about the long term future of the service

People are frequently predicting the imminent demise of the service. This may or may not be true, but doesn’t fill me with confidence given the other more serious problems listed.

The solution

Enter Quandl and specifically the data provided by the London Stock exchange. R has a specific package (Quandl) which makes downloading data incredibly easy. You can download data for a number of different securities in one go, select what data (columns and date ranges) you actually want and download directly as an xts object. You will need to register to obtain a free api key if you are going to make more than 50 calls per day on the api. (Replace your XXXXXX in my code with your key). The documentation is here and the cran page is here.
## Download all available data for ticker ISF
mydata <- Quandl("LSE/ISF", type = "xts", api_key= XXXXXXX)

                 Price   High    Low  Volume Last Close Change  Var%
2017-06-19 743.8 744.80 740.45 1788670      743.8    5.9  0.80
2017-06-20 738.4 761.45 738.20 2543313      738.4   -5.4 -0.73
2017-06-21 735.7 742.45 732.80 3275787      735.7   -2.7 -0.37
2017-06-22 734.9 735.90 730.95 2692453      734.9   -0.8 -0.11
2017-06-23 733.0 744.80 685.95 4659435      733.0   -1.9 -0.26
2017-06-26 735.2 738.85 735.10 2919664      735.2    2.2  0.30

## Download the first column (close) for ISF and VMID from 2016-12-31 until the end of the data
mydata2 <- Quandl(c("LSE/ISF.1", "LSE/VMID.1"), type = "xts", start_date="2016-12-31", 
                api_key= XXXXXXX)

                 LSE.ISF - Price LSE.VMID - Price
2017-01-03           708.9            29.17
2017-01-04           709.3            29.19
2017-01-05           710.0            29.46
2017-01-06           711.3            29.50
2017-01-09           714.0            29.61
2017-01-10           717.4            29.66

Obtaining the meta data which goes with each data set is a little more tricky as I can’t get the metaData function within the Quandl package to work for these data sets. The approach is therefore to download the xml file that contains the meta data, convert that into a data frame within R and then do some further processing to get the data I actually want.
xml.url <- "https://www.quandl.com/api/v3/datasets/LSE/ISF/metadata.xml?api_key=XXXXXXX"
xmldata <- getURL(xml.url)
xmldf <- xmlToDataFrame(xmldata, stringsAsFactors = F)
stockinfo <- xmldf[, c(2, 4, 8, 7)]
colnames(stockinfo) <- c("Code", "Name", "From", "To")
stockinfo$Currency <- substr(stockinfo$Name, nchar(stockinfo$Name)-2, nchar(stockinfo$Name))
stockinfo$Name <-  substr(stockinfo$Name,1,nchar(stockinfo$Name)-14)

Code  Name                               From          To              Currency
ISF   ISH COREFTSE100 ETF price (ISF)    2006-03-16    2017-06-26      GBX

Issues with Quandl Data

The solution is not perfect
  • You do not get full OHLC data if that is important to you.  However there is high, low, close and volume so you could use the previous close as open
  • There is no index data. Because data for total return indexes are really difficult to find I have always used accumulating ETFs if possible (e.g. CUKX for FTSE100). Personally if I’m using an index for comparison purposes then I would prefer to use something I could actually invest in ie an ETF, rather than a theoretical uninvestible index.
  • No dividends or adjusted close – more on this in a later blog post

The Effect of a Cash Buffer on Returns

Simon Saves over at MyRichFuture.com wrote a fantastic article entitled “Cash Buffer Will Save You In Retirement“. He argues that holding cash (he plans to hold three years worth of expenditure) in addition to investments, means that if the stock market falls you can use the cash buffer rather than being forced to eat into your capital. He illustrates this with an example.  I wanted to look in more detail at what effect holding cash might have and how much it would reduce the chance of running out of money.


My first assumption is that the annual returns in the future will be similar to the past. I have used the UK real returns (i.e. including reinvesting dividends and accounting for inflation) from the Barclay’s Equity Gilt Study. This has 116 years of returns from 1900 to 2015. Rather than just assuming that the returns form a normal distribution, I have sampled from the 116 known returns. This should give a better estimate of the “fat tails” so prevalent in stock market returns. I have not chosen 30 years of sequential returns (as many people like to do) in order to do a larger number of simulations. There is therefore a slightly greater chance of having a fabulous 30 years or an absolutely catastrophic 30 years. However the evidence for auto-correlation in annual returns in the stock market is very weak (i.e. a bad year doesn’t necessarily lead to a good year in the near future) and if each year’s return really is independent of others then that catastrophic 30 year period could be just round the corner!
I’ve assumed that 100% of the investments is in shares and none is in bonds. This might be considered reckless!
I have assumed that the cash buffer has a real return of zero. i.e. it keeps pace with inflation but nothing more.
I’ve assumed transaction costs, taxes etc are zero and that your actual return is the UK real return.

Features of the model

30 year period starting with £500k of capital (stock market investments) and spending £20k pa (4% withdrawal rate).
An additional cash buffer ranging from zero to  5 years of expenditure i.e. from nothing to £100k


Each year
  • Work out the return for that year by randomly picking  a number from the 116 possible returns in the Barclay study.
  • Ideally this growth (and any retained excess from previous years) would be sufficient for our expenditure. Any excess is added to the capital for next year.
  • If there is not enough money in the the capital pot (i.e. it would go below £500k) use the cash buffer to keep it at £500k
  • If the cash buffer runs out, use capital (this will take it below £500k)
  • Pay back money into the cash buffer if the capital exceeds £500k up to the target amount in the buffer
  • Stop iteration if capital falls to zero (This is a failure!)
  • Do the whole process again for each of the 30 years using the new capital  and cash buffer amounts from the end of the previous year.
I then repeated the whole process 10,000 (yes ten thousand!) times to simulate 10,000 possible futures each based on sampling 30 times from the distribution. I also repeated the process for the different cash buffer amounts.

The results

Holding no cash buffer means that there is roughly a 10% chance of failure i.e. running out of money at some point in the  30 year period. Holding a three year cash buffer (£60k in our example) reduces this chance to approximately 1%. You could, of course, argue that I’m not comparing like with like, as in one case I’m starting with £500k and in the other case I effectively have £560k. Rerunning the simulation with a starting capital pot of £560k and no cash buffer gives a failure rate of about 8%. The full results are listed below. Note that the sums of money you could be left with at the end of 30 years are eye-watering. The problem is, that after 30 years the width of the distribution of possible final returns is so wide that the upside is huge.  This seems like a good argument for reducing both  returns and risk by introducing bonds or other less volatile assets.

 Capital remaining after 30 years. All values in £k

Cash Buffer Min. 1st Qu. Median Mean 3rd Qu. Max. s.d. No. of fails
0 Year(s) 0 758 1482 2608 3080 104300 3860 1034
1 Year(s) 0 948 1758 2912 3413 69700 3809 395
2 Year(s) 0 1002 1810 2975 3472 72290 3775 200
3 Year(s) 0 1026 1822 3051 3497 303800 4965 67
4 Year(s) 0 1046 1842 3007 3466 210600 4201 32
5 Year(s) 0 1039 1830 3081 3521 175200 4461 16


Finally this is the R code. Regular readers will know that I’m really only learning R and it’s quite possible the code could be made faster or that I’ve made some huge error. Comments welcome as normal.

mysummary <- function(x){
        s <- as.numeric(summary(x))
        s[7] <- sd(x)
        s[8] <- sum(x <= 0)
        names(s) <-  c("Min." ,   "1st Qu.", "Median" , "Mean"  ,  "3rd Qu.", "Max."  , 
                                          "s.d." ,"No. of fails" )
        round(s, digits = 0)

years <- seq(1900, 2015) 
ret <- c(0.115,-0.035,0.035,0.018,0.123,0.038,0.186,-0.089,0.019,0.109,0.021,-0.015,0,-0.034,-0.005,
         0.169,0.088,0.189,0.114,0.01,-0.304,0.259,0.089,-0.078,0.087, 0.174, -0.004, -0.001)

muret <- mean(ret)
sdret <- sd(ret)

## These are the variables to change

I0 <- 500 ## starting capital
nyrs <- 30 ## Number of years that money needs to last
spend <- 20 ## Annual expenditure
by <- c(0:5) ## buffer as years of expenditure


CBfin <- NULL
Ifin <- NULL
smryI <- NULL
smryCB <- NULL

## ret <- 0.04 For testing only!!

for(k in 1:length(by)){
CB0 <- by[k]*spend
for(j in 1:10000){
        I <- I0
        CB <- CB0
for(i in 1:nyrs){

r <- sample(ret, 1)

## r <- rnorm(1,muret,sdret) ## Alternative way of estimating future return

e <- r*I  ## e = earnings

        I <- I+e-spend
                        CB <- CB-spend
                        I <- I + CB - spend
                        CB <- 0

if((CB < CB0) & (I > I0)){
        CB <- CB+I-I0
        if(CB >= CB0){
                CB <- CB0
                I <- I + CB - CB0

        I <- 0

CBfin[j] <- CB
Ifin[j] <- I


smryI[[k]] <- mysummary(Ifin)
smryCB[[k]] <- mysummary(CBfin)


completesmryI <- do.call("rbind", smryI)
rownames(completesmryI) <- paste(by, "Year(s)")

completesmryCB <- do.call("rbind", smryCB)
rownames(completesmryCB) <- paste(by, "Year(s)")