quantmod R getsymbols.MySQL modification

Question

I have already studied the case Quantmod: Error loading symbols from MySQL DB and already try to fix the getSymbols.MySQL function in R

However, I found that my database just contain date, open, high, low, close, volume (without the close.adj column). So, if I want to further modify the getSymbols.MySQL function, what can I do?

I have tried to use 'fix(getSymbols.MySQL)' to fix the function. However, it returns

Error in colnames<-(*tmp*, value = c("H0001.Open", "H0001.High", "H0001.Low", : length of 'dimnames' [2] not equal to array extent

when I connect to my database.

function (Symbols, env, return.class = "xts", db.fields = c("date", 
"o", "h", "l", "c", "v", "a"), field.names = NULL, user = NULL, 
password = NULL, dbname = NULL, host = "localhost", port = 3306, 
...) 
{
importDefaults("getSymbols.MySQL")
this.env <- environment()
for (var in names(list(...))) {
    assign(var, list(...)[[var]], this.env)
}
if (!hasArg(verbose)) 
    verbose <- FALSE
if (!hasArg(auto.assign)) 
    auto.assign <- TRUE
if (!requireNamespace("DBI", quietly = TRUE)) 
    stop("package:", dQuote("DBI"), "cannot be loaded.")
if (!requireNamespace("RMySQL", quietly = TRUE)) 
    stop("package:", dQuote("RMySQL"), "cannot be loaded.")
if (is.null(user) || is.null(password) || is.null(dbname)) {
    stop(paste("At least one connection argument (", sQuote("user"), 
        sQuote("password"), sQuote("dbname"), ") is not set"))
}
con <- DBI::dbConnect("MySQL", user = user, password = password, 
    dbname = dbname, host = host, port = port)
db.Symbols <- DBI::dbListTables(con)
if (length(Symbols) != sum(Symbols %in% db.Symbols)) {
    missing.db.symbol <- Symbols[!Symbols %in% db.Symbols]
    warning(paste("could not load symbol(s): ", paste(missing.db.symbol, 
        collapse = ", ")))
    Symbols <- Symbols[Symbols %in% db.Symbols]
}
for (i in 1:length(Symbols)) {
    if (verbose) {
        cat(paste("Loading ", Symbols[[i]], paste(rep(".", 
            10 - nchar(Symbols[[i]])), collapse = ""), sep = ""))
    }
    query <- paste("SELECT ", paste(db.fields, collapse = ","), 
        " FROM ", Symbols[[i]], " ORDER BY date")
    rs <- DBI::dbSendQuery(con, query)
    fr <- DBI::fetch(rs, n = -1)
    fr <- xts(as.matrix(fr[, -1]), order.by = as.Date(fr[, 
        1], origin = "1970-01-01"), src = dbname, updated = Sys.time())
    colnames(fr) <- paste(Symbols[[i]], c("Open", "High", 
        "Low", "Close", "Volume", "Adjusted"), sep = ".")
    fr <- convert.time.series(fr = fr, return.class = return.class)
    if (auto.assign) 
        assign(Symbols[[i]], fr, env)
    if (verbose) 
        cat("done\n")
}
DBI::dbDisconnect(con)
if (auto.assign) 
    return(Symbols)
return(fr)
}

I think the problem is the function was designed to read 7 column of data rather than 6 column of data. Hope someone can help.

Answer 1

Here's a patch that should allow you to do what you want. I'm unable to test because I don't have a MySQL installation to test against. Please let me know whether or not it works.

diff --git a/R/getSymbols.R b/R/getSymbols.R
index 0a2e814..7a9be66 100644
--- a/R/getSymbols.R
+++ b/R/getSymbols.R
@@ -634,9 +634,9 @@ function(Symbols,env,return.class='xts',
             fr <- xts(as.matrix(fr[,-1]),
                       order.by=as.Date(fr[,1],origin='1970-01-01'),
                       src=dbname,updated=Sys.time())
-            colnames(fr) <- paste(Symbols[[i]],
-                                  c('Open','High','Low','Close','Volume','Adjusted'),
-                                  sep='.')
+            if(is.null(field.names))
+              field.names <- c('Open','High','Low','Close','Volume','Adjusted')
+            colnames(fr) <- paste(Symbols[[i]], field.names, sep='.')
             fr <- convert.time.series(fr=fr,return.class=return.class)
             if(auto.assign)
               assign(Symbols[[i]],fr,env)

Then your function call should be:

getSymbols.MySQL("H0001", env, return.class = 'xts',
  db.fields = c("date", "open", "high", "low", "close", "volume"),
  field.names = c("date", "open", "high", "low", "close", "volume"),
  user = 'xxxx', password = 'xxxx', host='xxxx', dbname = 'xxxx')