Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
yahoo.r
require(R6)
require(quantmod)
# Db interface
YahooDb = R6Class("YahooDb",
public = list(
initialize = function(path="yahoo.sqlite") {
private$path = path
},
get.symbol = function(symbol, force=F) {
require(RSQLite)
driver = SQLite()
connection = dbConnect(driver, dbname=private$path)
symbol = toupper(symbol)
# Leading '^' (used for indexes, like ^DJI) is dropped when
# quantmod constructs the variable name
db.symbol = gsub('^\\^', '', symbol)
if(!force) {
# Try the database first
rs = dbGetQuery(
connection,
paste(
" select date, open, high, low, close, volume, adjusted from bars ",
" where symbol = '", db.symbol, "'",
" order by date",
sep=""))
if(NROW(rs) == 0) {
rs = NULL
} else {
ss = xts(rs[,2:NCOL(rs)], as.Date(rs[,1]))
colnames(ss) = private$col.names
}
} else {
rs = NULL
}
if(NROW(rs) == 0) {
ss = getSymbols(symbol, from="1900-01-01", auto.assign=F)
ss = adjustOHLC(ss, use.Adjusted=F, adjust="split", symbol.name=symbol)
colnames(ss) = private$col.names
df = cbind(data.frame(symbol=db.symbol), as.character(index(ss)), data.frame(ss))
colnames(df) = c("symbol","date",private$col.names)
dbBegin(connection)
query = paste("delete from bars where symbol='", symbol, "'", sep="")
dbSendQuery(connection, query)
query = paste(" insert or replace into bars (symbol,date,open,high,low,close,volume,adjusted) ",
" values(@symbol,@date,@open,@high,@low,@close,@volume,@adjusted)",
sep="")
RSQLite::dbGetPreparedQuery(connection, query, bind.data=df)
if(db.symbol != symbol) {
# If the database symbol (GSPC) is different than the yahoo symbol (^GSPC),
# write the mapping.
query = paste(" insert or ignore into map (symbol,yahoo_symbol) ",
" values(@symbol,@yahoo_symbol)",
sep="")
RSQLite::dbGetPreparedQuery(connection, query, bind.data=data.frame(symbol=db.symbol,yahoo_symbol=symbol))
}
dbCommit(connection)
}
dbDisconnect(connection)
return(ss)
},
get.symbols = function(symbols, env, force=F) {
require(RSQLite)
driver = SQLite()
connection = dbConnect(driver, dbname=private$path)
symbols = toupper(symbols)
# Leading '^' (used for indexes, like ^DJI) is dropped when
# quantmod constructs the variable name
db.names = gsub('^\\^', '', symbols)
done = FALSE
if(!force) {
# Try the local cache first
for(ss in db.names) {
rs = dbGetQuery(
connection,
paste(
" select date, open, high, low, close, volume, adjusted from bars ",
" where symbol = '",ss, "'",
" order by date",
sep=""))
if(NROW(rs) == 0) {
rs = NULL
} else {
env[[ss]] = xts(rs[,2:NCOL(rs)], as.Date(rs[,1]))
colnames(env[[ss]]) = private$col.names
}
}
# Verify that we got all symbols, in which case we are done
if(!any(is.na(match(db.names, ls(env))))) {
done = TRUE
}
}
if(!done) {
# Either a symbol was not found in the cache, or the caller forced us to download and cache
getSymbols(symbols, env=env, src="yahoo", from="1900-01-01", auto.assign=TRUE)
dbBegin(connection)
for(ss in ls(env)) {
# Adjust only for splits
env[[ss]] = adjustOHLC(env[[ss]], use.Adjusted=F, adjust="split", symbol.name=symbols[as.numeric(match(ss, db.names))])
colnames(env[[ss]]) = private$col.names
# Store into the database
df = cbind(data.frame(symbol=ss), as.character(index(env[[ss]])), data.frame(env[[ss]]))
colnames(df) = c("symbol","date",private$col.names)
query = paste("delete from bars where symbol='", ss, "'", sep="")
dbSendQuery(connection, query)
query = paste(" insert or replace into bars (symbol,date,open,high,low,close,volume,adjusted) ",
" values(@symbol,@date,@open,@high,@low,@close,@volume,@adjusted)",
sep="")
RSQLite::dbGetPreparedQuery(connection, query, bind.data=df)
}
# Write all mappings
for(ii in 1:NROW(symbols)) {
if(db.names[ii] != symbols[ii]) {
# If the database symbol (GSPC) is different than the yahoo symbol (^GSPC),
# write the mapping.
query = paste(" insert or ignore into map (symbol,yahoo_symbol) ",
" values(@symbol,@yahoo_symbol)",
sep="")
RSQLite::dbGetPreparedQuery(
connection,
query,
bind.data=data.frame(symbol=db.names[ii],yahoo_symbol=symbols[ii]))
}
}
dbCommit(connection)
}
dbDisconnect(connection)
},
# Shortcuts
g = function(symbol, force=F) {
return(self$get.symbol(symbol, force))
},
gs = function(symbols, env, force=F) {
return(self$get.symbols(symbols, env, force))
},
store.symbol = function(symbol, data) {
require(RSQLite)
driver = SQLite()
connection = dbConnect(driver, dbname=private$path)
df = cbind(data.frame(symbol=symbol), index(data), data.frame(data))
colnames(df) = c("symbol","date",private$col.names)
dbBegin(connection)
query = paste("delete from bars where symbol='", symbol, "'", sep="")
dbSendQuery(connection, query)
query = paste(" insert or replace into bars (symbol,date,open,high,low,close,volume,adjusted) ",
" values(@symbol,@date,@open,@high,@low,@close,@volume,@adjusted)",
sep="")
RSQLite::dbGetPreparedQuery(connection, query, bind.data=df)
dbCommit(connection)
dbDisconnect(connection)
},
init = function() {
require(RSQLite)
driver = SQLite()
connection = dbConnect(driver, dbname=private$path)
query = paste(" create table if not exists bars ( ",
" symbol varchar(30) not null, ",
" date datetime not null, ",
" open real not null, ",
" high real not null, ",
" low real not null, ",
" close real not null, ",
" volume integer not null, ",
" adjusted real not null) ",
sep="")
dbGetQuery(connection, query)
query = paste(" create unique index if not exists bars_unique ",
" on bars(symbol,date) ",
sep="")
dbGetQuery(connection, query)
query = paste(" create table if not exists map ( ",
" symbol varchar(30) not null, ",
" yahoo_symbol varchar(30) not null) ",
sep="")
dbGetQuery(connection, query)
query = paste(" create unique index if not exists map_unique ",
" on map(symbol) ",
sep="")
dbGetQuery(connection, query)
dbDisconnect(connection)
}
),
private = list(
path = "yahoo.sqlite",
col.names = c("open","high","low","close","volume","adjusted")
)
)
@ttmmghmm

This comment has been minimized.

Copy link

ttmmghmm commented May 21, 2015

I had to add "self$init()" to
initialize = function(path="yahoo.sqlite") {
private$path = path
self$init()
to get this code to work.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.