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.
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.
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.
library(Quandl) ## Download all available data for ticker ISF mydata <- Quandl("LSE/ISF", type = "xts", api_key= XXXXXXX) tail(mydata) 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) head(mydata2) 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.
library(XML) library(RCurl) 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) stockinfo 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