R Code Sample 1

##
##read shorts info for TD
#http://regsho.finra.org/FNSQshvol20181212.txt
#http://regsho.finra.org/FNYXshvol20181212.txt
#http://regsho.finra.org/FORFshvol20181212.txt
library(RODBC)
dbconn<-odbcDriverConnect(“driver={SQL Server};server=localhost\\AMT2016;database=Test;trusted_connection=true”)

read_hist_loop <- function(dbconn, daysBack){
dtMin = -1 * daysBack
sSQL = paste(“select Year(Tdate)*10000+ Month(Tdate)*100+ Day(TDate) dt from dbo.TDates where Tflag =1 and TDate < /*GetDate()*/ DATEADD(d, -15, GetDate()) and TDate >= DATEADD(d, “, dtMin, “,GetDate())”)
print(sSQL)
res <- sqlQuery(dbconn, sSQL)
print(nrow(res))
for (row in 1:nrow(res)) {
dtstring <- res[row, “dt”]
read_shorts(dbconn, dtstring)
}
print(“finish reading the loop!”)
}

read_shorts <- function(conn, dtstring){

##print(paste(toString(Sys.time), “start reading for date “,dtstring))
###First page
url = paste(“http://regsho.finra.org/FNSQshvol”, dtstring, “.txt”, sep = “”)
print(paste(“now reading”, url))

dtX = read.csv(url, header = TRUE, sep = “|”)
dt2 <- head(dtX, -1)
sqlSave(dbconn, dt2, “TShort6”, rownames =NULL, append= TRUE )

###2nd page
url = paste(“http://regsho.finra.org/FNYXshvol”, dtstring, “.txt”, sep = “”)
print(paste(“now reading”, url))

dtY = read.csv(url, header = TRUE, sep = “|”)
dt2 <- head(dtY, -1)
sqlSave(dbconn, dt2, “TShort6”, rownames =NULL, append= TRUE )

###Third page
url = paste(“http://regsho.finra.org/FORFshvol”, dtstring, “.txt”, sep = “”)
print(paste(“now reading”, url))

dtZ = read.csv(url, header = TRUE, sep = “|”)
dt2 <- head(dtZ, -1)
sqlSave(dbconn, dt2, “TShort6”, rownames =NULL, append= TRUE )

##print(paste(toString(Sys.time), “finished reading for date “,dtstring))
}

#read historical
#read_hist_loop(dbconn, 349)
if(weekdays(Sys.Date())==”Monday”) dtstring = format(as.Date(Sys.Date()-3), “%Y%m%d”) else dtstring = format(as.Date(Sys.Date()-1), “%Y%m%d”)

read_shorts(dbconn, dtstring)
##read_shorts(dbconn, “20190214”)
close(dbconn)

Leave a Reply

Your email address will not be published. Required fields are marked *