def parse(self, response):
if response.text != '': # 有資料才跑，不然會遇到假日全都沒資料
df = pd.read_csv(StringIO(response.text),
header=7).dropna(how='all',
axis=1).dropna(how='any')
try:
r = 0
for index, row in df.iterrows():
if len(clean(row['股票代號'])) == 4:
if r == 0: #有資料才刪除
db = database()
sql = "delete from financing"
db.execute_sql(sql)
item = FinancingItem()
item['data_date'] = self.data_date
item['stock_no'] = clean(row['股票代號']).zfill(6) # 證券代號
item['stock_name'] = clean(row['股票名稱']) # 股票名稱
item['today_borrow_money'] = float(clean(str(
row[6]))) # 融資今日餘額
item['today_borrow_stock'] = float(
clean(str(row[12]))) # 融券今日餘額
yield item
r += 1
except:
pass
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'CREATE TABLE if not exists taiex (data_date varchar(10) NOT NULL,open_index double NOT NULL,high_index double NOT NULL,low_index double NOT NULL,close_index double NOT NULL,created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)'
)
def parse(self, response):
if response.text != '\r\n': # 有資料才跑，不然會遇到假日全都沒資料
db = database()
sql = "delete from legalperson"
db.execute_sql(sql)
# 製作三大法人的DataFrame
try:
df = pd.read_csv(StringIO(response.text), header=1).dropna(how='all', axis=1).dropna(how='any')
for index, row in df.iterrows():
if len(clean(row['證券代號'])) == 4:
item = LegalPersonItem()
item['data_date'] = self.data_date
item['stock_no'] = clean(row['證券代號']).zfill(6)
item['stock_name'] = clean(row['證券名稱'])
item['china_buy'] = float(clean(str(row['外陸資買進股數(不含外資自營商)'])))
item['china_sell'] = float(clean(str(row['外陸資賣出股數(不含外資自營商)'])))
item['china_sum'] = float(clean(str(row['外陸資買賣超股數(不含外資自營商)'])))
item['foreign_buy'] = float(clean(str(row['外資自營商買進股數'])))
item['foreign_sell'] = float(clean(str(row['外資自營商賣出股數'])))
item['foreign_sum'] = float(clean(str(row['外資自營商買賣超股數'])))
item['invest_buy'] = float(clean(str(row['投信買進股數'])))
item['invest_sell'] = float(clean(str(row['投信賣出股數'])))
item['invest_sum'] = float(clean(str(row['投信買賣超股數'])))
item['com_sum'] = float(clean(str(row['自營商買賣超股數'])))
item['hedge_buy'] = float(clean(str(row['自營商買進股數(避險)'])))
item['hedge_sell'] = float(clean(str(row['自營商賣出股數(避險)'])))
item['hedge_sum'] = float(clean(str(row['自營商買賣超股數(避險)'])))
item['legalperson'] = float(clean(str(row['三大法人買賣超股數'])))
yield item
except:
pass
def parse(self, response):
if response.text != '': # 有資料才跑，不然會遇到假日全都沒資料
db = database()
sql = "delete from stockprice where batch_no = '" + self.data_date + "'"
db.execute_sql(sql)
df = pd.read_csv(StringIO(response.text.replace("=", "")),
header=["證券代號" in l for l in response.text.split("\n")].index(True)-1)
for index, row in df.iterrows():
if len(clean(row['證券代號'])) == 4:
item = StockPriceItem()
item['batch_no'] = self.data_date
item['stock_no'] = clean(row['證券代號']).zfill(6)
item['stock_name'] = clean(row['證券名稱'])
item['stock_buy'] = clean(row['成交股數'])
item['stock_num'] = clean(row['成交筆數'])
item['stock_amount'] = clean(row['成交金額'])
item['stock_sprice'] = clean(row['開盤價'])
item['stock_hprice'] = clean(row['最高價'])
item['stock_lprice'] = clean(row['最低價'])
item['stock_eprice'] = clean(row['收盤價'])
item['stock_status'] = clean(row['漲跌(+/-)'])
item['stock_gap'] = row['漲跌價差']
item['stock_last_buy'] = clean(row['最後揭示買價'])
item['stock_last_bnum'] = clean(row['最後揭示買量'])
item['stock_last_sell'] = clean(row['最後揭示賣價'])
item['stock_last_snum'] = clean(row['最後揭示賣量'])
item['stock_value'] = clean(row['本益比'])
yield item
def count_legalperson_price(self, data_date):
db = database()
# 準備好要做累加的資料
self.prepare_count_data()
current_date = data_date # 本次日期
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
sql = "select stock_no,percent from legalperson_price where batch_no = '{current_date}' "
sql = sql.format(current_date=current_date)
self.cur.execute(sql)
rows = self.cur.fetchall()
for row in rows:
percent = float(row[1])
if percent > -0.01: #若賣超不多，則不中斷連續計算
sql = "update legalperson_daily set increase=increase+1, decrease = 0, de_gap_count=0, in_gap_count=IFNULL(in_gap_count,0)+{percent}, updated_date = now() where stock_no = '{stock_no}'"
sql = sql.format(stock_no=row[0], percent=percent)
db.execute_sql(sql)
else:
sql = "update legalperson_daily set increase=0, in_gap_count=0, decrease = decrease+1, de_gap_count=IFNULL(de_gap_count,0)+{percent}, updated_date = now() where stock_no = '{stock_no}'"
sql = sql.format(stock_no=row[0], percent=percent)
db.execute_sql(sql)
# 完成後更新狀態
sql = "update legalperson_date set flag = 'Y' where data_date='{data_date}'"
sql = sql.format(data_date=data_date)
db.execute_sql(sql)
def foundout(self):
db = database()
self.conn = db.create_connection()
sql = "select a.stock_no, c.stock_name,b.increase,b.decrease,b.in_gap_count,b.de_gap_count,b.stock_num_gap,b.updated_date from robert_stock_list a, stockholder_sum_count b, stockcode c " \
"where a.stock_no = c.stock_no and a.stock_no = b.stock_no " \
" and increase > 2 and in_gap_count>2 " \
" and a.stock_no not in (select stock_no from (select stock_no,count(*) from stockholder e where level = 17 group by stock_no having count(*) > 1) a)"
self.cur = self.conn.cursor()
self.cur.execute(sql)
rows = self.cur.fetchall()
token = "zoQSmKALUqpEt9E7Yod14K9MmozBC4dvrW1sRCRUMOU"
for row in rows:
stock_no = row[0]
stock_name = row[1]
in_gap_count = row[4]
de_gap_count = row[5]
stock_num_gap = row[6]
updated_date = row[7]
if int(row[2]) > 0:
times = row[2]
msg = "Stock No :{stock_no}({stock_name})\nCount Gap:{in_gap_count}%\nrise continuously {times} weeks\nStock Amount Changed Gap : {stock_num_gap}\nUpdated Date : {updated_date}"
msg = msg.format(stock_no=stock_no, stock_name=stock_name, in_gap_count=in_gap_count, times=times, stock_num_gap=stock_num_gap, updated_date=updated_date)
self.update_stock_flag(stock_no, in_gap_count) #use percent of legalholder to calculate the up and down of stock
else:
times = row[3]
msg = "Stock No :{stock_no}({stock_name})\nCount Gap:-{de_gap_count}%\nfall continuously {times} weeks\nStock Amount Changed Gap : {stock_num_gap}\nUpdated Date : {updated_date}"
msg = msg.format(stock_no=row[0], stock_name=row[1], de_gap_count=de_gap_count, times=times, stock_num_gap=stock_num_gap, updated_date=updated_date)
self.close_stock_flag(stock_no) #close flag date
lineNotifyMessage(token, msg)
def saveFlagDate(self, data_date, stock_no):
self.create_stock_flag_table()
db = database()
sql = "insert into stockflag(data_date,stock_no,stock_name,stock_lprice,close_index) " \
" (select batch_no,stock_no,stock_name,stock_lprice,close_index from stockprice a, taiex b where a.stock_no = '{stock_no}' and batch_no = '{data_date}' and a.batch_no = b.data_date)"
sql = sql.format(stock_no=stock_no, data_date=data_date)
db.execute_sql(sql)
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'CREATE TABLE if not exists legalperson (data_date varchar(10) NOT NULL,stock_no varchar(10) NOT NULL,stock_name varchar(60) NOT NULL,china_buy double NULL,china_sell double NULL,china_sum double NULL, foreign_buy double NULL,foreign_sell double NULL,foreign_sum double NULL,invest_buy double NULL,invest_sell double NULL,invest_sum double NULL,com_sum double NULL,legalperson double NULL,created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)'
)
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'CREATE TABLE if not exists financing (data_date varchar(10) NOT NULL,stock_no varchar(10) NOT NULL,stock_name varchar(60) NOT NULL,today_borrow_money double NULL,today_borrow_stock double NULL)'
)
def prepare_stock_list_count(self):
sql = self.CREATE_SUM_COUNT_TABLE
db = database()
db.execute_sql(sql)
sql = "insert into stockholder_sum_count(stock_no) " \
" (select stock_no from stockcode a where not exists (select * from stockholder_sum_count b where a.stock_no = b.stock_no))"
db.execute_sql(sql)
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'create table if not exists moneyreport(myear varchar(4), season varchar(2), stock_no varchar(10), revenue bigint, '
' grossprofit float, operprofit float, netprofit float, aftertaxprofit float, '
'created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)')
def insert_data(self, year, stock):
sql = """insert into EPS_2021(year,stock_no,stock_name,eps)
Values('{year}', '{stock_no}', '{stock_name}', {eps})"""
sql = sql.format(year=self.year,
stock_no=stock.stock_no,
stock_name=stock.stock_name,
eps=stock.stock_eps)
db = database()
db.execute_sql(sql)
def combine_legalperson_price(self,data_date):
db = database()
sql = "insert into legalperson_price (" \
"select '{data_date}' nowdate,a.stock_no,a.stock_name,a.china_buy,a.china_sell,a.china_sum," \
"a.foreign_buy,a.foreign_sell,a.foreign_sum,a.invest_buy,a.invest_sell,a.invest_sum,a.com_sum,a.hedge_buy,a.hedge_sell,a.hedge_sum,a.legalperson,b.stock_last_buy,now(),d.stock_num,round((legalperson/d.stock_num*100),2) percent " \
"from legalperson a, stockprice b, robert_stock_list c, stockholder d " \
"where a.stock_no = b.stock_no and a.stock_no = c.stock_no and b.batch_no = a.data_date and d.level=17 and d.stock_no = a.stock_no and a.data_date='{data_date}')"
sql = sql.format(data_date=data_date)
db.execute_sql(sql)
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'create table if not exists stockprice(batch_no varchar(10) NOT NULL,stock_no varchar(10), stock_name varchar(100), '
' stock_buy bigint, stock_num bigint, stock_amount float, stock_sprice float, stock_hprice float, '
' stock_lprice float, stock_eprice float, stock_status varchar(10), stock_gap float, '
' stock_last_buy float, stock_last_bnum bigint, stock_last_sell float, stock_last_snum bigint, stock_value int,'
'created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)')
def save_legalperson_date(self, data_date):
db = database()
# 執行前先清除Table，該次的日期去刪除，所以先前的資料都還在
sql = "delete from legalperson_date where data_date = '{data_date}'"
sql = sql.format(data_date=data_date)
db.execute_sql(sql)
# 將本次整理的資料塞進去
insert_sql = "insert into legalperson_date(data_date) values('{data_date}')"
insert_sql = insert_sql.format(data_date=data_date)
db.execute_sql(insert_sql)
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'create table if not exists stockcode(stock_no varchar(10), stock_name varchar(60), '
'stock_isin varchar(14), stock_createdate varchar(10), stock_type varchar(10),'
'stock_industry varchar(14), stock_cficode varchar(10),'
'created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)')
sql = "delete from stockcode"
db.execute_sql(sql)
def execute(self):
bResult = False
data_date = datetime.date.today().strftime('%Y%m%d')
#data_date = "20191224"
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor()
token = "zoQSmKALUqpEt9E7Yod14K9MmozBC4dvrW1sRCRUMOU"
msg = "【Daily Monitor】"
if self.validate(data_date):
self.cur.execute("SELECT data_date FROM financing where data_date ='" + data_date + "'")
if self.cur.rowcount == 0:
msg_tmp = "\nfinancing{data_date}資料，只會影響每日融資融券異常警訊不會發出"
msg += msg_tmp.format(data_date=data_date)
bResult = True
self.cur.execute("SELECT data_date FROM legalperson where data_date ='" + data_date + "'")
if self.cur.rowcount == 0:
msg_tmp = "\nlegalperson無{data_date}資料，影響legalperson_price也會沒資料"
msg += msg_tmp.format(data_date=data_date)
bResult = True
self.cur.execute("SELECT batch_no FROM stockprice where batch_no ='" + data_date + "'")
if self.cur.rowcount == 0:
msg_tmp = "\nstockprice無{data_date}資料，影響legalperson_price也會沒資料"
msg += msg_tmp.format(data_date=data_date)
bResult = True
self.cur.execute("SELECT batch_no FROM legalperson_price where batch_no ='" + data_date + "'")
if self.cur.rowcount == 0:
msg_tmp = "\nlegalperson_price無{data_date}資料，影響legalperson_daily也會沒資料"
msg += msg_tmp.format(data_date = data_date)
bResult = True
self.cur.execute("SELECT * FROM legalperson_date where flag is null")
if self.cur.rowcount > 0:
msg_tmp = "\nlegalperson_daily沒有跑完，影響legalperson_daily沒有累加資料"
msg += msg_tmp.format(data_date=data_date)
bResult = True
self.cur.execute("SELECT * from (SELECT a.data_date,b.batch_no FROM taiex a LEFT outer JOIN stockprice b ON a.data_date = b.batch_no AND b.stock_no = '000050') a WHERE a.batch_no IS null")
if self.cur.rowcount > 0:
msg_tmp = "\n用taiex比對stockprice發現資料有少"
msg += msg_tmp
bResult = True
if bResult:
lineNotifyMessage(token, msg)
def stockholder_sum_count(self, data_date):
db = database()
# get stock list
self.prepare_stock_list_count()
current_date = data_date # get this time date
self.conn = db.create_connection()
self.cur = self.conn.cursor()
#get last date
sql = "select * from stockholder_date where data_date < '{data_date}' order by data_date desc limit 1"
sql = sql.format(data_date = data_date)
self.cur.execute(sql)
rows = self.cur.fetchall()
if rows:
last_date = rows[0][0] # last data date
sql = "select c.stock_no,c.percent co,l.percent lo,c.stock_num_total ct,l.stock_num_total lt from " \
" (select * from stockholder_sum where data_date = '{current_date}') c, " \
" (select * from stockholder_sum where data_date = '{last_date}') l " \
" where c.stock_no = l.stock_no "
sql = sql.format(current_date=current_date, last_date=last_date)
self.cur.execute(sql)
rows = self.cur.fetchall()
for row in rows:
current_percent = float(row[1])
last_percent = float(row[2])
current_total = 0
if row[3] is not None:
current_total = float(row[3])
last_total = 0
if row[4] is not None:
last_total = float(row[4])
stock_num_gap = current_total - last_total
if current_percent > last_percent:
gap = current_percent - last_percent
sql = "update stockholder_sum_count set increase=increase+1, decrease = 0, de_gap_count=0, in_gap_count=IFNULL(in_gap_count,0)+{gap}, stock_num_gap = {stock_num_gap}, updated_date = now() where stock_no = '{stock_no}'"
sql = sql.format(stock_no=row[0], gap=gap, stock_num_gap=stock_num_gap)
db.execute_sql(sql)
else:
gap = last_percent - current_percent
sql = "update stockholder_sum_count set increase=0, in_gap_count=0, decrease = decrease+1, de_gap_count=IFNULL(de_gap_count,0)+{gap}, stock_num_gap = {stock_num_gap}, updated_date = now() where stock_no = '{stock_no}'"
sql = sql.format(stock_no=row[0], gap=gap, stock_num_gap=stock_num_gap)
db.execute_sql(sql)
# update status when finish
sql = "update stockholder_date set flag = 'Y' where data_date='{data_date}'"
sql = sql.format(data_date=data_date)
db.execute_sql(sql)
def save_stockholder_date(self, data_date):
db = database()
# clear table before execute
sql = "delete from stockholder_date where data_date = '{data_date}'"
sql = sql.format(data_date=data_date)
db.execute_sql(sql)
# put this time result to table
insert_sql = "insert into stockholder_date(data_date) values('{data_date}')"
insert_sql = insert_sql.format(data_date=data_date)
db.execute_sql(insert_sql)
def open_spider(self, spider):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
self.cur.execute(
'create table if not exists stockholder(stock_no varchar(10), level int, '
'stock_num bigint, holder_num bigint, percent float, data_date date, '
'created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)')
self.cur.execute(
'create table if not exists stockholder_hist(stock_no varchar(10), level int, '
'stock_num bigint, holder_num bigint, percent float, data_date date, '
'created_date TimeStamp DEFAULT CURRENT_TIMESTAMP)')
def save_dividend_data(self):
db = database()
self.conn = db.create_connection()
self.cur = self.conn.cursor() # 建立cursor對資料庫做操作
sql = "select stock_no from stockcode a where stock_cficode = 'ESVUFR' and not exists (select * from dividend b where a.stock_no = b.stock_no)"
self.cur.execute(sql)
rows = self.cur.fetchall()
for row in rows:
stock_no = row[0][2:6]
self.get_stock_dividend(stock_no) # 右取四碼
def insert_dividend(self, stock_no, col1, col2, col3, col4, col5, col6):
sql = "insert into dividend(stock_no,year,cash,stock_earn,stock_capital,stock,total) " \
"values('{stock_no}',{year},{cash},{stock_earn},{stock_capital},{stock},{total})"
sql = sql.format(stock_no=stock_no.zfill(6),
year=col1,
cash=col2,
stock_earn=col3,
stock_capital=col4,
stock=col5,
total=col6)
db = database()
db.execute_sql(sql)
def getBorkerList(self):
db = database()
self.conn = db.create_connection()
cur = self.conn.cursor(MySQLdb.cursors.DictCursor)
sql = "select broker_head,broker_no,broker_name from broker_list a where enable ='Y' " \
"and broker_no not in(select distinct broker_no from broker_data where data_date = {data_date})"
sql = sql.format(data_date=self.data_date)
cur.execute(sql)
rows = cur.fetchall()
return rows
def validate(self, data_date):
db = database()
conn = db.create_connection()
cur = conn.cursor()
sql = "SELECT * FROM stockholder_sum where data_date = '{data_date}' "
sql = sql.format(data_date = data_date)
cur.execute(sql)
rows = cur.fetchall()
if len(rows) > 0:
return False
else:
return True
def validate(self, data_date):
db = database()
conn = db.create_connection()
cur = conn.cursor()
sql = "SELECT * FROM legalperson_date where data_date = '{data_date}' and flag ='Y'"
sql = sql.format(data_date=data_date)
cur.execute(sql)
rows = cur.fetchall()
if len(rows) > 0:
return False
else:
return True
def put_into_list(self):
db = database()
self.conn = db.create_connection()
sql = ('select stock_no from stockholder a where level > 11 and level < 16'
' group by stock_no having sum(percent) > {percent} and sum(stock_num) > {stock_num} and sum(holder_num)>{holder_num} and not exists (select * from robert_stock_list where stock_no = a.stock_no)')
sql = sql.format(stock_num = self.CONFIG['stock_num'], percent = self.CONFIG['percent'], holder_num = self.CONFIG['holder_num'])
self.cur = self.conn.cursor()
self.cur.execute(sql)
rows = self.cur.fetchall()
for row in rows:
insert_sql = "insert into {stock_table}(stock_no) values('{stock_no}')"
insert_sql = insert_sql.format(stock_table = self.CONFIG['stock_table'], stock_no = row[0])
db.execute_sql(insert_sql)
def countAvgDividend(stock_no):
sql = "select round(avg(total),2) from dividend where stock_no = '{stock_no}'"
sql = sql.format(stock_no=stock_no)
db = database()
conn = db.create_connection()
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
dividend_avg = 0
if cur.rowcount > 0:
if rows[0][0] > 0:
dividend_avg = rows[0][0]
return dividend_avg
def getTodayPrice(data_date, stock_no):
sql = "select stock_eprice from stockprice where stock_no = '{stock_no}' and batch_no = '{data_date}'"
sql = sql.format(data_date=data_date, stock_no=stock_no)
db = database()
conn = db.create_connection()
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
price = 0
if cur.rowcount > 0:
if rows[0][0] > 0:
price = rows[0][0]
return price
def update_stock_flag(self,stock_no,in_gap_count):
times = 0
if in_gap_count <=1 :
times = 15
elif in_gap_count > 1 and in_gap_count <= 3:
times = 9
elif in_gap_count > 3 and in_gap_count <= 5:
times = 8
elif in_gap_count > 5 and in_gap_count <= 10:
times = 6
elif in_gap_count > 10 and in_gap_count <= 20:
times = 5
elif in_gap_count > 20:
times = 4
percent = in_gap_count * times
percent50 = percent+5
percent80 = percent-5
percent90 = percent-10
db = database()
conn = db.create_connection()
cur = conn.cursor()
sql = "SELECT stock_lprice FROM stockflag where stock_no ='{stock_no}' and enable is null"
sql = sql.format(stock_no=stock_no)
cur.execute(sql)
if cur.rowcount > 0:
rows = cur.fetchall()
for row in rows:
stock_lprice = row[0]
percent_price70 = round(stock_lprice * (1 + (percent / 100)), 1)
percent_price50 = round(stock_lprice * (1 + (percent50 / 100)), 1)
percent_price80 = round(stock_lprice * (1 + (percent80 / 100)), 1)
percent_price90 = round(stock_lprice * (1 + (percent90 / 100)), 1)
sql = "update stockflag set price90 ={percent_price90},price80={percent_price80},price70={percent_price70},price50={percent_price50} where stock_no ='{stock_no}' and enable is null"
sql = sql.format(percent_price90=percent_price90,percent_price80=percent_price80,percent_price70=percent_price70,percent_price50=percent_price50,stock_no=stock_no)
db.execute_sql(sql)
def getOffer6YearDividend(stock_no):
sql = "select total from dividend where stock_no = '{stock_no}' order by stock_no"
sql = sql.format(stock_no=stock_no)
db = database()
conn = db.create_connection()
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
count = 0
for row in rows:
if row[0] > 0:
count += 1
else:
break
return count
#x = getOffer6YearDividend('001101')
#y = countAvgDividend('001101')
