Unreliable Yahoo downloads, so possibly save/reread using MySQL?

Hi Cedric,
   I wanted to once again say thanks. I'm now downloading data into
MySQL nightly using cron and I wouldn't have gotten there without your
example. There's a still a few curiousities:

1) Why can't I get setDefaults(getSymbols.MySQL to use .my.cnf instead
of having to put a password in this example
2) Why does getSymbols reading MySQL require
c("o","h","l","c","v","a") instead of the names that dbWriteTable put
in?

but other than that it all works.

   Like you I cringed about that big ALTER TABLE command. I've not
figured out how to get rid of the row_names to Date rename but the
rest of it is easy to do in a colname rename as shown here. I think it
makes the code more readable, at least to my eyes. I broke the ALTER
TABLE stuff into multiple strings as I was studying the problem so
that I could run multiple ALTER TABLE commands more easily but in the
end ended up with only the one and didn't simplify here.

   I also put in a rm(list=ls()) command to ensure what I was seeing
was really coming from MySQL.

   It also seems possible to use dbReadTable instead of getSymbols.
That might be another option for fewer ALTER requirements.

   Anyway, thanks to you and to Mark Breman for your help.

Cheers,
Mark

##EOD Downloader file, triggered by cron nightly after 8pm.. Define
your symbols in a vector
library(quantmod)
library(RMySQL)

MySymbols = c("MRK","GOOG","AAPL")
dbname = "Cedric"
getSymbols(MySymbols, src="yahoo")

dbc = dbConnect(MySQL(), dbname=dbname)
for(i in 1:length(MySymbols)) {
  df1 = as.data.frame(get(MySymbols[i]))
  #Change column names in database of getSymbols.MySQL won't work...
  #Simplifies ALTER TABLE command later
  colnames(df1) = c("o","h","l","c","v","a")
  dbWriteTable(dbc, name=MySymbols[i],value=df1, overwrite=TRUE)

  Str1 = paste0("ALTER TABLE ", MySymbols[i])
  Str2 = paste0("CHANGE COLUMN row_names Date DATE NULL DEFAULT NULL")
  StrOut = paste(Str1, Str2)
  print(StrOut)
  res = dbSendQuery(dbc, StrOut)
  dbClearResult(res)
}

#As a test, get rid of everything so far to
#ensure data read back is from MySQL
Sys.sleep(2)
rm(list=ls())
Sys.sleep(2)

#Now, from quantmod:
#setDefaults(getSymbols.MySQL, dbname="Cedric")
setDefaults(getSymbols.MySQL, user="mark", password="password", dbname="Cedric")

MySymbols = c("MRK","GOOG","AAPL")

for (i in 1:(length(MySymbols))){
  getSymbols(MySymbols[i], src="MySQL")
}

On Thu, Dec 5, 2013 at 4:59 PM, Cedrick Johnson

Unreliable Yahoo downloads, so possibly save/reread using MySQL?

Thread (5 messages)