Author Archives: admin

Rcode Example, Set up short List T1

/****** Script for SelectTopNRows command from SSMS ******/
select * from Tshort3
where symbol like ‘AAPL’

create table TDates(TDate Date, Tflag bit default 1);

select * from Tdates

DECLARE @i int = 0
WHILE @i < 450
BEGIN
INSERT INTO TDates(TDate, Tflag)
SELECT
DateAdd(d, -1 * @i, getdate())
, CASE
WHEN datepart(WEEKDAY, DateAdd(d, -1 * @i, getdate())) =1 THEN 0
WHEN datepart(WEEKDAY, DateAdd(d, -1 * @i, getdate())) =7 THEN 0
ELSE 1
END AS Tflag
SET @i = @i + 1
END

select Convert(DATE, CAST (“Date” AS VARCHAR(8)), 0) from Tshort3

select datepart(WEEKDAY, getdate())

alter table Tshort3 drop column Tdate

—get the day ready
select Year(Tdate)*10000+ Month(Tdate)*100+ Day(TDate) dt from dbo.TDates where Tflag =1 and TDate >= DATEADD(d, -100, GetDate())

——————- anla
select TOP 20 * From Tshort5
WHERE Symbol like ‘CHK’

select count(*) from Tshort5

select * from Tshort5 a join Tshort5 b
ON a.”Date” = b.”Date” and a.Symbol = b.Symbol
where a.Market =’N’ and b.Market =’O’

/*
–truncate table Tshort5

update TDates set Tflag =0 where datediff(d, Tdate, ’12-05-2018′)=0
update TDates set Tflag =0 where datediff(d, Tdate, ’11-22-2018′)=0

*/

RCode Sample, Read Short list

##
##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;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())”)
sSQL = paste(“select Year(Tdate)*10000+ Month(Tdate)*100+ Day(TDate) dt from dbo.TDates where Tflag =1 and TDate < ‘5/30/2019’ 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”]
try(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, 540)

##$$ 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, Read EOD

library(RODBC)
library(jsonlite)

dbconn<-odbcDriverConnect(“driver={SQL Server};server=localhost;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=01&startMonth=0&startYear=2017&endDay=30&endMonth=4&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, 20190507)
##read_EOD(dbconn, “AAPL”)

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

close(dbconn)

Sample Data Source

http://app.quotemedia.com/quotetools/getHistoryDownload.csv?&webmasterId=501&startDay=3&startMonth=0&startYear=2019&endDay=31&endMonth=9&endYear=2019&isRanged=true&symbol=AAPL
https://cloud.iexapis.com/v1?format=csv&symbols=fb
https://cloud.iexapis.com/1.0/stock/aapl/chart/date/20181123

https://api.iextrading.com/1.0/stock/aapl/chart/date/20181123

https://cloud.iexapis.com/v1/stock/AAPL/financials/2?token=pk_4ea9af9ceb2f42ceb9e34cb3144f9598&period=daily
https://cloud.iexapis.com/v1/stock/aapl/financials?period=annual

monthly short sale trans files
https://www.finra.org/filing-reporting/trf/trf-regulation-sho-2010

directly listings Sec filings

https://www.sec.gov/Archives/edgar/monthly/

egar index archived
https://www.sec.gov/Archives/edgar/full-index/

Start Up Shiny Server Error

how to start:

sudo start shiny-server

tail /var/log/shiny-server.log

[INFO] shiny-server – Starting listener on http://[::]:3838
[ERROR] shiny-server – HTTP server error (http://[::]:3838): listen EAFNOSUPPORT :::3838
[INFO] shiny-server – Shutting down worker processes
HTTP server error (http://[::]:3838): listen EAFNOSUPPORT

 

change the shiny-server config

/etc/shiny-server/shiny-server.conf

change:

server {
listen 3838;

to

server {
listen 3838 0.0.0.0;

..

 

start again and it will work.