quantmod - getSymbols.MySQL format

2
votes

I query a MySQL database using quantmod getSymbols with the following:

pot<-getSymbols(Symbols="pot",src='MySQL',user='root',password='root',dbname='data',
   adjust=TRUE,db.fields=c("date","open","high","low","close","volume","time"),
   auto.assign=FALSE,
   field.names=c("ticker","date","time","open","high","low","close","volume"),
   format="%Y%m%d")

Returns: Messing up the date conversion.

      pot.Open  pot.High  pot.Low   pot.Close pot.Volume pot.Adjusted

7058-02-20 "40.1700" "40.1900" "40.1000" "40.1500" " 104900" "09:30:59"
7058-02-20 "40.0900" "40.1725" "40.0900" "40.1725" " 13200" "09:31:53"
7058-02-20 "40.1900" "40.3394" "40.1800" "40.2900" " 16500" "09:32:57"
7058-02-20 "40.3000" "40.3700" "40.2400" "40.2400" " 36500" "09:33:58"
7058-02-20 "40.2600" "40.3000" "40.2000" "40.2500" " 6700" "09:34:59"
7058-02-20 "40.2600" "40.3100" "40.2000" "40.2000" " 13900" "09:35:59"

I would like it to look like this:

               pot.Open  pot.High  pot.Low   pot.Close pot.Volume pot.Adjusted

2012-10-31 09:30:59 "40.1700" "40.1900" "40.1000" "40.1500" " 104900" "09:30:59"
2012-10-31 09:31:53 "40.0900" "40.1725" "40.0900" "40.1725" " 13200" "09:31:53"
2012-10-31 09:32:57 "40.1900" "40.3394" "40.1800" "40.2900" " 16500" "09:32:57"
2012-10-31 09:33:58 "40.3000" "40.3700" "40.2400" "40.2400" " 36500" "09:33:58"
2012-10-31 09:34:59 "40.2600" "40.3000" "40.2000" "40.2500" " 6700" "09:34:59"
2012-10-31 09:35:59 "40.2600" "40.3100" "40.2000" "40.2000" " 13900" "09:35:59"

I believe it has to do with the date format in the table being (%Y%m%d) ex. 20121031 as opposed to the normal (%Y-%m-%d) ex.2012-10-31 format quantmod usually expects. The documentation says this can be changed via setDefaults(getSymbols.MySQL,...) but doesn't say how. Any thoughts?

1
rquantmod

1 Answers

0
votes

Sorry, I'm not allowed to comment, but isn't the problem that your return column names includes "ticker", and the db.fields doesn't specify this, causing a misalignment between the two?

As you can see from the example I'm including, the actual table's name should be the same as the ticker. So there shouldn't be a "ticker" column.

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)
}