Rcode Sample, Testing Json

library(RODBC)
library(jsonlite)

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 DISTINCT SYMBOL From TShort6(NOLOCK) where “Date” >= (select max(“Date”) from TShort6(NOLOCK)) and ShortVolume>=50000’)
print(sSQL)
res <- sqlQuery(dbconn, sSQL)
print(nrow(res))

for (row in 1:nrow(res)) {
dtstring <- res[row, “SYMBOL”]
url = paste(“https://api.iextrading.com/1.0/stock/chk/chart/date/20181218”, dtstring, “.txt”, sep = “”)
print(paste(“now reading”, url))
read_intra(dbconn, url)
}
print(“finish reading the loop!”)
}

read_hist_day <- function(dbconn, dtstring){

#sSQL = paste(‘select DISTINCT SYMBOL From TShort6(NOLOCK) where “Date” >= (select max(“Date”) from TShort6(NOLOCK)) and ShortVolume>=50000’)
sSQL = paste(‘select DISTINCT SYMBOL From TShort6(NOLOCK) where “Date” = ‘, dtstring, ‘ and ShortVolume>=50000’)
print(sSQL)
res <- sqlQuery(dbconn, sSQL)
print(nrow(res))

for (row in 1:nrow(res)) {
symbol <- res[row, “SYMBOL”]
url = paste(“https://api.iextrading.com/1.0/stock/”, symbol,”/chart/date/”, dtstring, sep = “”)
print(paste(“now reading”, url))
try(read_intra(dbconn, url, symbol))
}
print(“finish reading the loop!”)
}

read_intra <- function(dbconn, url, symbol){

sSQL = paste(‘truncate table TIntra5’)
print(sSQL)
res <- sqlQuery(dbconn, sSQL)

dt2 <- fromJSON(url)
sqlSave(dbconn, dt2, “TIntra5”, rownames =NULL, append= TRUE )

sSQL = paste(‘INSERT INTO TIntra6 SELECT *, ‘,symbol,’ FROM TIntra5′)
print(sSQL)
resx <- sqlQuery(dbconn, sSQL)

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

#read historical
read_hist_day(dbconn, 20190123)
#dtstring = format(as.Date(Sys.Date()-1), “%Y%m%d”)
#read_shorts(dbconn, dtstring)

close(dbconn)

Leave a Reply

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