Author Archives: admin

TEsting R. out

R version 3.5.1 (2018-07-02) — “Feather Spray”
Copyright (C) 2018 The R Foundation for Statistical Computing
Platform: x86_64-w64-mingw32/x64 (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type ‘license()’ or ‘licence()’ for distribution details.

Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type ‘contributors()’ for more information and
‘citation()’ on how to cite R or R packages in publications.

Type ‘demo()’ for some demos, ‘help()’ for on-line help, or
‘help.start()’ for an HTML browser interface to help.
Type ‘q()’ to quit R.

[Previously saved workspace restored]

> ##
> ##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)
[1] “now reading http://regsho.finra.org/FNSQshvol20190315.txt”
Error in file(file, “rt”) : cannot open the connection
Calls: read_shorts -> read.csv -> read.table -> file
In addition: Warning message:
In file(file, “rt”) :
InternetOpenUrl failed: ‘The server name or address could not be resolved’
Execution halted
Warning message:
closing unused RODBC handle 1

R Example, testing old version

Leave a reply

##
##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, “TShort5”, 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, “TShort5”, 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, “TShort5”, rownames =NULL, append= TRUE )

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

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

close(dbconn)

Testjson Version 2 example

Leave a reply

#read json file and change it into flattern tabular format
require(RJSONIO)
library(dplyr)
json_data = NULL
json_data <- fromJSON(file= “https://api.iextrading.com/1.0/stock/aapl/chart/date/20181123” )
print(length(json_file))
#df <- do.call(“rbind.fill”, lapply(json_data, as.data.frame))
df <- bind_rows(json_data)
#df <- as_data_frame(jsd)
View(df)
remove(json_data)

################################———————————–
library(rjson)

# You can pass directly the filename
my.JSON <- fromJSON(file=”c:/xyu_docs/Projects/Claims/testdata.json”)

##$df <- lapply(my.JSON, function(play) # Loop through each “play”
##$ {
# Convert each group to a data frame.
# This assumes you have 6 elements each time
##$ data.frame(matrix(unlist(play), ncol=21, byrow=T))
##$ })
data.frame(ncol=21, byrow=T)

# Now you have a list of data frames, connect them together in
# one single dataframe
df <- do.call(rbind, df)

# Make column names nicer, remove row names
colnames(df) <- names(my.JSON[[1]][[1]])
rownames(df) <- NULL
View(df)

Save Json example

Leave a reply

library(RODBC)
library(jsonlite)
dbconn<-odbcDriverConnect(“driver={SQL Server};server=localhost\\AMT2016;database=Test;trusted_connection=true”)
dt2 <- fromJSON(“https://api.iextrading.com/1.0/stock/chk/chart/date/20181218”)
sqlSave(dbconn, dt2, “TIntra5”, rownames =NULL, append= TRUE )

library(RODBC)
DBHANDLE<-odbcDriverConnect(‘driver={SQL Server};server=localhost\\AMT2016;database=Adventureworks2016;trusted_connection=true’)

fileds<-sqlQuery(DBHANDLE,’SELECT ProductID,LineTotal FROM [AdventureWorks2016].[Sales].[SalesOrderDetail]’)
View(fileds)
library(ggplot2)
ggplot(fileds,aes(x=fileds$ProductID,y=fileds$LineTotal,color=factor(fileds$ProductID)))+geom_boxplot()+facet_grid(fileds$ProductID~.)
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet; #read input data
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Ages); #plot the histogram
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
#file() opens a file, in this case the image. rb = read binary
#readBin() reads binary data. what = described the mode of the data. In this case, it”s raw data. n = maximum number of records to read.
#data.frame converts the data to a data frame, which is required as output by SQL Server. The result is written to the OutputDataset variable.
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT Ages = DATEDIFF(MM,[ModifiedDate],GETDATE()) FROM [AdventureWorks2016].Sales.Customer;’
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));

inputDataSet<-sqlQuery(DBHANDLE,’SELECT Ages = DATEDIFF(MM,[ModifiedDate],GETDATE()) FROM [AdventureWorks2016].Sales.Customer’)
df <- inputDataSet; #read input data
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Ages); #plot the histogram
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
#file() opens a file, in this case the image. rb = read binary
#readBin() reads binary data. what = described the mode of the data. In this case, it”s raw data. n = maximum number of records to read.
#data.frame converts the data to a data frame, which is required as output by SQL Server. The result is written to the OutputDataset variable.
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
#housing example
library(plotly)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
ggplotly(g, tooltip = c(“city”))
library(ggplot2); #import ggplot2 library
qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
xlab = “Sepal Length”, ylab = “Petal Length”,
main = “Sepal vs Petal Length in Fisher”s Iris data”);

Rcode Sample, Try Catch

Leave a reply

read_shorts <- function(conn, dtstring){

###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, “TShort5”, rownames =NULL, append= TRUE )

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

dtstring = “20180724”
try(read_shorts(dbconn, dtstring))
print(“non-stop”)
close(dbconn)

Rcode Sample Read old data

Leave a reply

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 <‘2018-07-25’ 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){

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, 367)
#dtstring = format(as.Date(Sys.Date()-1), “%Y%m%d”)
#read_shorts(dbconn, dtstring)

close(dbconn)

Rcode Sample, Testing Json

Leave a reply

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)

Rcode Sample, Realtime check

Leave a reply

library(RODBC)
library(jsonlite)

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

read_hist_day <- function(dbconn, dtstring){

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)

R Code Sample 1

Leave a reply

read_shorts <- function(conn, dtstring){

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)

Rcode Example 2, Download File

Leave a reply

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)

RStudio FAQs and Tutorials

Just another Real Solutions to Real Problems site

Author Archives: admin

TEsting R. out

R Example, testing old version

Testjson Version 2 example

Save Json example

Rcode Sample, Try Catch

Rcode Sample Read old data

Rcode Sample, Testing Json

Rcode Sample, Realtime check

R Code Sample 1

Rcode Example 2, Download File