Rcode Example 2, Download File

library(RODBC)
library(jsonlite)

dbconn<-odbcDriverConnect(“driver={SQL Server};server=localhost\\AMT2016;database=Test;trusted_connection=true”)

read_hist_range<- 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)) {
#for (row in 1:5) {
symbol <- res[row, “SYMBOL”]
try(read_EOD(dbconn, symbol))
}
print(“finish reading the loop!”)
}

read_EOD <- function(dbconn, symbol){

#url = paste(“http://app.quotemedia.com/quotetools/getHistoryDownload.csv?&webmasterId=501&startDay=1&startMonth=0&startYear=2017&endDay=31&endMonth=1&endYear=2019&isRanged=true&symbol=”, symbol, sep = “”)
#url = paste(“http://app.quotemedia.com/quotetools/getHistoryDownload.csv?&webmasterId=501&startDay=25&startMonth=0&startYear=2019&endDay=31&endMonth=0&endYear=2019&isRanged=true&symbol=”, symbol, sep = “”)
url = paste(“http://app.quotemedia.com/quotetools/getHistoryDownload.csv?&webmasterId=501&startDay=12&startMonth=2&startYear=2019&endDay=31&endMonth=2&endYear=2019&isRanged=true&symbol=”, symbol, sep = “”)
print(paste(“now reading”, url))

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

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

sSQL = paste(“INSERT INTO TEOD6 SELECT *, ‘”,symbol,”‘ FROM TEOD5″, sep = “”)
#sSQL = paste(“INSERT INTO TEOD2 SELECT *, ‘”,symbol,”‘ FROM TEOD5″, sep = “”)
print(sSQL)
resx <- sqlQuery(dbconn, sSQL)

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

#read historical
read_hist_range(dbconn, 20190123)
##read_EOD(dbconn, “AAPL”)

#dtstring = format(as.Date(Sys.Date()-1), “%Y%m%d”)

close(dbconn)

Leave a Reply

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