Maintaining database of price file using R
Source: http://www.thertrader.com/2015/12/13/maintaining-a-database-of-price-files-in-r/
Steps
- Prepare a list of securities (onetime)
- Setup initial data file (onetime)
- Update data files (periodic)
1. Prepare a list of securities
One time setup. If changed on demand then will have to call initial data setup.
File: /Users/rdebnath/robin/ticker_data/r-input/listOfInstruments.R
theInstruments = c("AMD","NVDA", "QQQ", "DDM", "EFA", "EEM", "EWJ", "AAPL")
2. Setup initial data file
Download initial data
File:/Users/rdebnath/robin/ticker_data/r-input/downloadDataOfInstruments.R
library(quantmod) startDate = "2010-01-01" thePath = "/Users/rdebnath/robin/ticker_data/" source(paste(thePath,"r-input/listOfInstruments.R",sep="")) for (ii in theInstruments){ print(ii) print(paste(thePath,ii,".csv",sep="")) data = getSymbols(Symbols = ii, src = "google", from = startDate, auto.assign = FALSE) colnames(data) = c("open","high","low","close","volume","adj.") print(paste(thePath,ii,".csv",sep="")) write.zoo(data,paste(thePath,ii,".csv",sep=""),sep=",",row.names=FALSE) }
3. Update data files
File: /Users/rdebnath/robin/ticker_data/r-input/updateData.R
library(quantmod) lookback = 60 startDate = Sys.Date() - lookback thePath = "/Users/rdebnath/robin/ticker_data/" theFiles = list.files(path=thePath,pattern=".csv") for (ii in theFiles){ tryCatch(read.table(x, header = TRUE, sep = '|'), error=function(e) NULL) data = read.csv(paste(thePath,ii,sep="")) if(data == NULL) next data = xts(data[,c("open","high","low","close","volume")], order.by = as.Date(data[,"Index"],format="%Y-%m-%d")) lastHistoricalDate = index(data[nrow(data),]) recent = getSymbols(Symbols = substr(ii,1,nchar(ii)-4), src = "google", from = startDate, auto.assign = FALSE) colnames(recent) = c("open","high","low","close","volume") pos = match(as.Date(lastHistoricalDate,format="%Y-%m-%d"),index(recent)) if (!is.na(pos)){ if (pos == nrow(recent)) print("File already up-to-date") if (pos < nrow(recent)){ dt = NULL dt = rbind(data,recent[(pos+1):nrow(recent),]) write.zoo(dt,paste(thePath,ii,sep=""),sep=",",row.names=FALSE) } } if (is.na(pos)) print("Error: dates do not match") }
Automation is required to download new data or to freshen up the securities data file using the script above.
Add below to cron?
R CMD BATCH --vanilla --slave "/Users/rdebnath/robin/ticker_data/r-input/updateData.R" "/Users/rdebnath/robin/ticker_data/r-input/updateLog.txt"