Maintaining database of price file using R

Source: http://www.thertrader.com/2015/12/13/maintaining-a-database-of-price-files-in-r/

Steps

1. Prepare a list of securities (onetime)
2. Setup initial data file (onetime)
3. 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"