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

[Mark Knecht]

On Thu, Dec 5, 2013 at 4:59 PM, Cedrick Johnson
<cjohnson at backstopsolutions.com> wrote:
> Mark-
> Here's what I do in a automated fashion for downloading data from Yahoo post close (and storing into a mysql DB). The code may be a bit crummy (I cringe at my ALTER TABLE query but it works well for the 100 or so things I download every evening) but this should be a good starting point for refining.
>
> ##EOD Downloader file, triggered by cron nightly after 8pm.. Define your symbols in a vector
> library(quantmod)
> library(RMySQL)
>
> MySymbols = c("MRK","GOOG","AAPL")
>
> getSymbols(MySymbols, src="yahoo")
> dbc = dbConnect(MySQL(), user="youruser", pass="yourpass", host="yourhost", dbname="yourdb")
> for(I in 1:length(MySymbols) {
>         dbWriteTable(dbc, name=MySymbols[i],value=as.data.frame(get(MySymbols[i])), overwrite=TRUE)
>         res = dbSendQuery(dbc, paste("ALTER TABLE `yourtable`.`",MySymbols[i],
>                 "` CHANGE COLUMN `row_names` `Date` DATE NULL DEFAULT NULL,
>                 CHANGE COLUMN `", MySymbols [i],"_Open` `o` DOUBLE NULL DEFAULT NULL,
>                 CHANGE COLUMN `", MySymbols [i],"_High` `h` DOUBLE NULL DEFAULT NULL,
>                 CHANGE COLUMN `", MySymbols [i], "_Low` `l` DOUBLE NULL DEFAULT NULL,
>                 CHANGE COLUMN `", MySymbols [i], "_Close` `c` DOUBLE NULL DEFAULT NULL,
>                 CHANGE COLUMN `", MySymbols [i], "_Volume` `v` DOUBLE NULL DEFAULT NULL,
>                 CHANGE COLUMN `",MySymbols[i],"_Adjusted` `a` DOUBLE NULL DEFAULT;", sep=""))
>         dbClearResult(res)
> }
>
> Now, from quantmod:
> setDefaults(getSymbols.MySQL, user="youruser", password="yourpass", host="yourhost",port=3306, dbname="yourdb")
>
> getSymbols("MRK", src="MySQL")
>
>
> Hope this helps,
> CJ
>

Hi Cedrick,
   Thanks very much for this. It looks like it's probably very close
to what I'm looking for.

   One question as I don't know very much about MySQL but the database
does need to actually exist and have the correct columns, etc., before
running this code, correct? I'll have to figure out how to create that
from scratch and then it would appear that I'm probably good to go.

Cheers,
Mark