[R-SIG-Finance] Unreliable Yahoo downloads, so possibly save/reread using MySQL?

[Mark Knecht]

Hi,
   I read an ETF rotation paper a few weeks ago on Seeking Alpha and
thought I'd play with the ideas in R. Without pointing any fingers (at
Yahoo) I find that downloading adjusted data to be highly unreliable,
especially in the middle of the work day. The following code snippet
is, at the time I'm writing this, failing on one or another of the 5
downloads nearly 100% of the time. It's not always the same symbols
that fail. That seems random to me. However if I change MyAdjust to
FALSE then the downloads seem to work perfectly. My experience is that
after the market closes the adjusted data works most of the time.

   The sort of failure I see looks like the following (which did not
use a to=Date_End) just as a test. In this case it was VNQ/REM that
failed. I checked the Yahoo site and was able to see the data by hand:

> getSymbols(MySymbols[[1]], from = Date_Start, adjust = MyAdjust, src = SymbolSrc)
[1] "VTI" "VEU"
> getSymbols(MySymbols[[2]], from = Date_Start, adjust = MyAdjust, src = SymbolSrc)
[1] "HYG" "CIU"
> getSymbols(MySymbols[[3]], from = Date_Start, adjust = MyAdjust, src = SymbolSrc)
Error in download.file(paste(yahoo.URL, Symbol.name, "&a=", from.m, "&b=",  :
  cannot open URL
'http://ichart.finance.yahoo.com/x?s=VNQ&a=0&b=01&c=2000&d=11&e=05&f=2013&g=v&y=0&z=30000'
In addition: Warning message:
In download.file(paste(yahoo.URL, Symbol.name, "&a=", from.m, "&b=",  :
  cannot open: HTTP status was '0 (null)'
> getSymbols(MySymbols[[4]], from = Date_Start, adjust = MyAdjust, src = SymbolSrc)
[1] "GLD" "TLT"
> getSymbols(MySymbols[[5]], from = Date_Start, adjust = MyAdjust, src = SymbolSrc)
[1] "SHY"

   Anyway, to implement my reading of this paper, I really need
adjusted data to take dividends into account and it seems silly to me
to keep downloading the same data over and over from Yahoo as I play
with this R code. To that end I'm wondering if anyone has a code
example for saving Yahoo data once to MySQL and then (presumably)
changing what I call SymbolSrc=MySQL for subsequent runs when I work
on the R code? I've been searching around in StackOverflow as well as
Google and haven't yet found anything very executable.

   Please note that I'm a retired investor, not a trader, and seldom
work with data faster than 30 minute bars and even then almost never
real-time data, at least in terms of what I would store in MySQL so
I'm not overly concerned right now as to whether MySQL is fast or
slow. I already run MySQL for a couple of other reasons on my Gentoo
box where I'm working in R and would prefer not run another database
right now unless _necessary_.

   Clearly, if there's some stupid issue in my code that fixes this
that would be great to know about also! :-)

Thanks in advance,
Mark



library(quantmod)

Date_Start = "2000-01-01"
Date_End = format(Sys.time(), "%Y-%m-%d")
Sys.setenv(TZ = "UTC")
SymbolSrc = "yahoo"
MyAdjust = TRUE

MySymbols = list()
MySymbols[[1]]= c("VTI","VEU")
MySymbols[[2]]= c("HYG","CIU")
MySymbols[[3]]= c("VNQ","REM")
MySymbols[[4]]= c("GLD","TLT")
MySymbols[[5]]= c("SHY")

getSymbols(MySymbols[[1]], from = Date_Start, to = Date_End, adjust =
MyAdjust, src=SymbolSrc)
getSymbols(MySymbols[[2]], from = Date_Start, to = Date_End, adjust =
MyAdjust, src=SymbolSrc)
getSymbols(MySymbols[[3]], from = Date_Start, to = Date_End, adjust =
MyAdjust, src=SymbolSrc)
getSymbols(MySymbols[[4]], from = Date_Start, to = Date_End, adjust =
MyAdjust, src=SymbolSrc)
getSymbols(MySymbols[[5]], from = Date_Start, to = Date_End, adjust =
MyAdjust, src=SymbolSrc)