def execute(self):
cur = self.conn.cursor(MySQLdb.cursors.DictCursor)
sql = """
select * from (
select f.stock_industry,a.stock_no,stock_name,a.avg_price ma5,b.avg_price ma10,c.avg_price ma20,d.avg_price ma60,
(b.avg_price/a.avg_price)-1 gap1, (c.avg_price/a.avg_price)-1 gap2, (d.avg_price/a.avg_price)-1 gap3
from stockprice_ma5 a,stockprice_ma10 b,stockprice_ma20 c,stockprice_ma60 d,robert_stock_list e, stockcode f
where a.stock_no = b.stock_no and a.stock_no = c.stock_no and a.stock_no = e.stock_no and a.stock_no = f.stock_no
and a.stock_no = d.stock_no) aa,
(select (today_borrow_money*1000/stock_num_total)*400 xx,a.stock_no from financing a,(
select stock_no,max(stock_num) stock_num_total from stockholder where level = 17 group by stock_no ) b
where a.stock_no = b.stock_no and (today_borrow_money*1000/stock_num_total)*100 <10) bb,
(select * from stockprice a where a.batch_no = '{data_date}') cc,
(select * from legalperson_daily) dd
where gap1 > -0.01 and gap1 < 0.01
and gap2 > -0.01 and gap2 < 0.01
and gap3 > -0.01 and gap3 < 0.01
and aa.stock_no = bb.stock_no and aa.stock_no = cc.stock_no and cc.stock_buy > 500000
and dd.stock_no = aa.stock_no and in_gap_count > 0.4
"""
sql = sql.format(data_date=self.today)
cur.execute(sql)
rows = cur.fetchall()
msg = "均線糾結，單日成交量大於500張，融資比小於10%，外資連續買進\n"
for row in rows:
print(row)
msg = ""
temp = "{stock_name}({stock_no}) 今日股價{stock_price} 融資比{borrow}"
temp = temp.format(stock_name=row["stock_name"], stock_no=row["stock_no"], stock_price=row["stock_eprice"], borrow=round(row["xx"],2))
msg += temp
lineNotifyMessage(self.token, msg)
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 notice(self):
today = datetime.date.today().strftime('%Y%m%d')
aweek = (datetime.date.today() +
datetime.timedelta(days=7)).strftime('%Y%m%d')
cur = self.conn.cursor(MySQLdb.cursors.DictCursor)
data_date = self.getLastPriceDate()
#殖息率大於6，股價小於當季eps推算便宜價，下周要除息的股票
sql = 'SELECT ROUND((a.money+a.stock)/c.stock_eprice*100,2) dividend,c.stock_eprice,a.*,b.* FROM dividend_notice a,stockcode b,stockprice c WHERE ' \
'c.batch_no={data_date} AND c.stock_no=a.stock_no AND a.stock_no = b.stock_no ' \
'AND c.stock_eprice>0 AND ((eps*60 > c.stock_eprice AND a.money/c.stock_eprice*100>6) OR a.stock_no IN (SELECT stock_no FROM stockflag WHERE ENABLE IS NULL) ) ' \
'AND dividend_date BETWEEN {today} AND {aweek}'
sql = sql.format(data_date=data_date, today=today, aweek=aweek)
cur.execute(sql)
rows = cur.fetchall()
msg = "殖息率大於6，股價小於當季eps推算便宜價，下周要除息的股票\n"
for row in rows:
stock_msg = "{stock_no} {stock_name}({stock_industry}) 除權息日:{dividend_date} 殖息率 :{dividend} 股價:{stock_price}\n"
stock_msg = stock_msg.format(stock_no=row['stock_no'],
stock_name=row['stock_name'],
stock_industry=row['stock_industry'],
dividend_date=row['dividend_date'],
dividend=row['dividend'],
stock_price=row['stock_eprice'])
msg += stock_msg
print(msg)
if len(rows) > 0:
token = "zoQSmKALUqpEt9E7Yod14K9MmozBC4dvrW1sRCRUMOU"
lineNotifyMessage(token, msg)
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 alarm_legalperson_monitor(self):
db = database()
self.conn = db.create_connection()
sql = "select a.stock_no, c.stock_name,a.in_gap_count,increase,round(b.today_borrow_stock/b.today_borrow_money*100,2) financing,a.updated_date from legalperson_daily a, financing b, stockcode c where (a.in_gap_count > 1.5) " \
"and b.today_borrow_stock/b.today_borrow_money*100 <20 and a.stock_no = b.stock_no and a.stock_no = c.stock_no"
self.cur = self.conn.cursor()
self.cur.execute(sql)
rows = self.cur.fetchall()
token = "zoQSmKALUqpEt9E7Yod14K9MmozBC4dvrW1sRCRUMOU"
for row in rows:
stock_no = row[0]
dividend_avg = countAvgDividend(stock_no)
dividend_years = getOffer6YearDividend(stock_no)
today_price = getTodayPrice(self.data_date, stock_no)
dividend_avg_rate = round(dividend_avg/today_price,2)
updated_date = row[5]
#紀錄旗標日, 若已存在就不紀錄
conn = db.create_connection()
cur = conn.cursor()
sql = "SELECT data_date 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:
msg = "【Daily Monitor】觸發旗標日\nStock No :{stock_no}({stock_name})\n累計買超比例超過1.5% : {in_gap_count}%\n連續買超{increase}日\n資券比小於20% : {financing}%\n股息發放年數 : {dividend_years}\n平均股息率 : {dividend_avg_rate}\nUpdated Date: {updated_date}"
msg = msg.format(stock_no=stock_no, stock_name=row[1].encode('utf-8'), in_gap_count=row[2],
increase=row[3], financing=row[4], dividend_years=dividend_years,
dividend_avg_rate=dividend_avg_rate, updated_date=updated_date)
lineNotifyMessage(token, msg)
sf = stockflag()
sf.saveFlagDate(self.data_date,stock_no)
def Message(self):
msg = ""
lists = self.getFlagStock()
ds = DynamicStrategy(self.today)
ss = StaticStrategy()
for list in lists:
msg = ""
stock_no = list['stock_no']
stock_name = list['stock_name']
flagDate = list['data_date']
stock = Stock(stock_no, stock_name, flagDate)
msg = msg + "Stock No : " + stock_no + "(" + stock_name + ")\n"
msg = msg + "旗標日：" + flagDate + "\n"
#條件一
stock.forePercent = ds.Foreign_Percent(stock_no, flagDate)
stock.todayPercent = ds.Today_Foreign_Percent(stock_no)
msg = msg + "外資買超比率 :" + str(stock.forePercent) + "(" + str(
stock.todayPercent) + ")\n"
#條件二
stock.taiexPercent = ds.Taiex_Percent(flagDate)
if stock.taiexPercent >= 5:
result_2 = "Yes"
else:
result_2 = "No"
msg = msg + "大盤漲幅>=5% :" + str(
stock.taiexPercent) + " (" + result_2 + ")\n"
#條件三
stock.investPercent = ds.Invest_Percent(stock_no, flagDate)
if stock.investPercent >= 0.1:
result_3 = "Yes"
else:
result_3 = "No"
msg = msg + "投信買超比率 >= 0.1% :" + str(
stock.investPercent) + " (" + result_3 + ")\n"
#條件四
Over1M = ds.Foreign_Day_Over1M(stock_no, flagDate)
if Over1M == "超一個月":
result_4 = "Yes"
else:
result_4 = "No"
msg = msg + "外資持有時間 :" + Over1M + " (" + result_4 + ")\n"
#條件五 市值<300億
s300 = ss.CompanyStockValue(flagDate, stock_no)
if s300 == "Y":
result_5 = "Yes，漲幅高"
else:
result_5 = "No，漲幅低"
msg = msg + "市值小於300億 :" + result_5 + "\n"
#資券比
financing = ds.Today_Financing_Percent(stock_no)
if financing > 20:
result_7 = "資券比 : " + str(financing) + "%，注意！散戶進場\n"
else:
result_7 = "資券比 : " + str(financing) + "\n"
msg = msg + result_7
#超過240天最高價
stock.mostPrice = ds.MostPrice(stock_no)
stock.currentPrice = ds.CurrentPrice(self.today, stock_no)
msg += "---中長期指標-----------------\n"
msg += "240天最高價 : " + str(stock.mostPrice) + "\n"
#年線乖離率
result_6 = ""
ma240 = ss.Ma240_Flag_Gap(self.today, stock_no)
if ma240 < -20:
result_6 = "年線乖離率 :" + str(ma240) + "，偏離年線很大，股價剛經過一段時間急跌\n"
elif ma240 > -20 and ma240 <= 0:
result_6 = "年線乖離率 :" + str(ma240) + "，等待突破年壓力線\n"
elif ma240 > 0 and ma240 < 20:
result_6 = "年線乖離率 :" + str(ma240) + "，股價具備年線支撐\n"
elif ma240 > 20:
result_6 = "年線乖離率 :" + str(ma240) + "，股價已經上漲了一段時間\n"
msg = msg + result_6
# 用外資買超比率計算預期股價
if stock.forePercent > 0:
stock = self.calculate_stock_price(stock)
msg += "外資比例推算\n"
msg += "機率值90% : " + str(stock.percent_price90) + "\n"
msg += "機率值80% : " + str(stock.percent_price80) + "\n"
msg += "機率值70% : " + str(stock.percent_price70) + "\n"
msg += "機率值50% : " + str(stock.percent_price50) + "\n"
#20日均線買賣訊號
result_8 = ""
MAt = ss.getMa20Value(stock_no)
ma20 = ss.Ma20_Flag_Gap(self.today, stock_no)
# 布林通道
SDt = ss.calculate_SD(stock_no, MAt)
UBt = round(MAt + (SDt * 2), 2)
LBt = round(MAt - (SDt * 2), 2)
PB = round((stock.currentPrice - LBt) / (UBt - LBt) * 100, 2)
result_8 = "---短期指標-------------------\n"
result_8 += "20日均線為" + str(MAt) + "，%B為" + str(PB) + "\n"
if ma20 >= 0 and ma20 <= 3:
result_8 += "，突破20日均線，可買進\n"
elif ma20 < 0 and ma20 >= -3:
result_8 += "，跌破20日均線，要賣出\n"
if PB > 100:
result_8 += "%B>100 建議出脫"
elif PB >= 80:
result_8 += "%B>80 多頭行情加碼"
elif PB < 0:
result_8 += "%B<0 可以考慮買進"
elif PB < 20:
result_8 += "%B<20空頭行情減碼"
if len(result_8) > 0:
msg = msg + result_8 + "\n"
result_9 = "壓力線為" + str(UBt) + "\n"
result_9 += "今日價:" + str(stock.currentPrice) + "\n"
result_9 += "支撐線為" + str(LBt) + "\n"
result_9 += "------------------------------\n"
if len(result_9) > 0:
msg = msg + result_9
# 外資加權平均收盤價
avg_price = ds.Foreign_Avg_Price(stock_no, flagDate)
msg = msg + "外資平均加權價格 :" + str(avg_price) + "\n"
# 關閉旗標日
if stock.forePercent <= 1 and stock.todayPercent < 0:
msg = msg + "不符合預期，該旗標日關閉\n"
self.close_flag(stock_no)
print(msg)
lineNotifyMessage(token, msg)
elif stock.currentPrice < stock.percent_price70 and result_4 == "Yes" and result_3 == "Yes":
print(msg)
lineNotifyMessage(token, msg)
ds.conn_close()
from stock.line import lineNotifyMessage token = "zoQSmKALUqpEt9E7Yod14K9MmozBC4dvrW1sRCRUMOU" lineNotifyMessage(token, "test")