def createHY():
sql = ('CREATE TABLE hangyestock('
'ts_code VARCHAR(6),'
'hyid VARCHAR(8),'
'PRIMARY KEY ( ts_code ),'
'KEY `hyid` (`hyid`)); ')
result = engine.execute(sql)
return result
def getStockProfitsIncRate(ts_code, quarter):
sql = ('select incrate from ttmprofits '
'where ts_code="%(ts_code)s" and date="%(quarter)s";' % locals())
result = engine.execute(sql).fetchone()
if result is not None:
return result[0]
else:
return None
def getHYStockCount(hyID):
""" 返回4级行业下的股票数量
"""
sql = ('select count(1) from hangyestock where hyid="%(hyID)s";' %
locals())
result = engine.execute(sql).fetchone()
if result is not None:
return result[0]
def updateTradeCal():
"""更新交易日历
"""
sql = 'select year(max(cal_date)) from trade_cal'
lastYear = engine.execute(sql).fetchone()[0]
if lastYear is None:
downTradeCal('1990')
elif lastYear < dt.datetime.today().year:
downTradeCal(str(int(lastYear) + 1))
def createHYProfitsTable():
sql = ('CREATE TABLE classify_profits('
'hyid VARCHAR(8),'
'date INT(11),'
'profitsInc DOUBLE,'
'profitsIncRate DOUBLE,'
'PRIMARY KEY ( hyid, date)); ')
result = engine.execute(sql)
return result
def getHYName(hyID):
print('getHYName(hyID):hyID: ', hyID)
sql = (f'select name from classify where code="{hyID}";')
result = engine.execute(sql).fetchone()
if result is None:
return None
else:
hyName = result[0]
return hyName
def getClassify(ts_code):
""" 当查询指定股票的4级行业的代码
"""
sql = f'select classify_code from classify_member where ts_code="{ts_code}";'
result = engine.execute(sql).fetchone()
if result is None:
return None
else:
return result[0]
def getHYProfitsIncRate(hyID, quarter):
sql = (f'select profitsIncRate from classify_profits '
f'where code="{hyID}" and date="{quarter}";')
print(sql)
result = engine.execute(sql).fetchone()
if result is None:
return None
else:
return result[0]
def createGuzhiTable():
sql = ('CREATE TABLE guzhi('
'ts_code VARCHAR(6),'
'peg DOUBLE,'
'next1YearPE DOUBLE,'
'next2YearPE DOUBLE,'
'next3YearPE DOUBLE,'
'PRIMARY KEY ( ts_code )); ')
result = engine.execute(sql)
return result
def createGubenTable():
sql = ("CREATE TABLE `guben` ("
"`ts_code` varchar(6) NOT NULL,"
" `date` date NOT NULL,"
" `totalshares` double DEFAULT NULL,"
" PRIMARY KEY (`ts_code`,`date`),"
" KEY `ix_guben_ts_code` (`ts_code`),"
" KEY `ix_guben_date` (`date`)"
") ENGINE=InnoDB DEFAULT CHARSET=utf8;")
result = engine.execute(sql)
return result
def createPEHistory():
sql = ("CREATE TABLE `pehistory` ("
"`name` varchar(45) NOT NULL,"
"`date` date NOT NULL,"
"`pe` decimal(10,2) NOT NULL,"
"PRIMARY KEY (`name`,`date`),"
"KEY `ix_name` (`name`),"
"KEY `ix_date` (`date`)"
") ENGINE=InnoDB DEFAULT CHARSET=utf8;")
result = engine.execute(sql)
return result
def calClassifyPE(date):
""" 计算所有行业在指定日期的市盈率
"""
# if date is None:
# date = dt.datetime.today() - timedelta(days=1)
# date = date.strftime('%Y%m%d')
logging.debug(f'update hangyepe: {date}')
sql = (f'replace into classify_pe(code, date, pe)'
f' select classify_code, "{date}",'
f' round(sum(b.total_mv) / sum(b.profits_ttm), 2) pe_ttm '
f' from classify_member a left join'
f' (select ts_code, trade_date, pe_ttm, total_mv,'
f' round(total_mv / pe_ttm, 2) as profits_ttm'
f' from daily_basic where trade_date="{date}" and pe_ttm > 0) b'
f' on a.ts_code = b.ts_code group by classify_code;')
print(sql)
try:
engine.execute(sql)
except Exception as e:
logging.error(f'failed to read hangyepe for date:{date}:', e)
def calAllHYTTMProfits(date):
""" 计算各级行业TTM利润，依次计算第4、3、2、1级
"""
for level in range(4, 0, -1):
sql = 'select code from hangyename where hylevel=%(level)s;' % locals()
result = engine.execute(sql)
hyIDList = result.fetchall()
hyIDList = [i[0] for i in hyIDList]
print(hyIDList)
for hyID in hyIDList:
print(hyID)
calHYTTMProfits(hyID, date)
def updateDailybasic():
"""更新每日指标
"""
sql = 'select max(trade_date) from daily_basic'
lastdate = engine.execute(sql).fetchone()[0]
lastdate += dt.timedelta(days=1)
startDate = lastdate.strftime('%Y%m%d')
endDate = dt.datetime.today().date() - dt.timedelta(days=1)
endDate = endDate.strftime('%Y%m%d')
dates = dateStrList(startDate, endDate)
for d in dates:
downDailyBasic(tradeDate=d)
def getStockListForHY(hyID):
""" 返回指定行业的所有股票代码列表
"""
# levelNum = len(hyID) / 2
# levels = ['level1', 'level2', 'level3', 'level4']
# level = levels[levelNum - 1]
sql = f'select ts_code from classify_member where classify_code="{hyID}";'
result = engine.execute(sql)
# stockList = result.fetchall()
stockList = [i[0] for i in result.fetchall()]
# print len(stockList), stockList
return stockList
def createPELirunIncreaseTable():
""" 各字段定义 ：
integrity： BOOL类型, 过去3年数据是否完整
seculargrowth： BOOL类型, 是否保持持续增长，当某季TTM利润增长率为负时，该值为否
growthmadrate： FLOAT类型, 利润增长平均离差率
"""
sql = ('CREATE TABLE pelirunincrease('
'ts_code VARCHAR(6),'
'date DATE,'
'pe FLOAT,'
'lirunincrease FLOAT,'
'PRIMARY KEY (date, ts_code)); ')
result = engine.execute(sql)
return result
def getHYPE(hyID, date, reset=False):
""" 计算行业在指定日期的市盈率
:param hyID:
:param date:
:param reset: 为True时用于重算某行业的PE
:return:
"""
sql = f'select hype from hangyepe where hyid="{hyID}" and date="{date}"'
result = engine.execute(sql).fetchone()
if not reset and result is not None:
return result[0]
ts_codes = getStockListForHY(hyID)
valueSum = 0
profitSum = 0
for ts_code in ts_codes:
sql = (f'select date, totalmarketvalue, ttmprofits '
f'from klinestock where ts_code="{ts_code}" and date<="{date}"'
f'order by `date` desc limit 1;')
result = engine.execute(sql).fetchone()
if result is not None:
# value, profit = result.fetchone()
# result = result.first()
value = result[1]
profit = result[2]
# ttmpe = result[3]
if profit is None or profit < 0 or value is None:
continue
# print ts_code, result[0], result[1], result[2], result[3]
valueSum += value
profitSum += profit
if profitSum != 0:
pe = round(valueSum / profitSum, 2)
# print 'htHYPE', date, valueSum, profitSum, pe
return pe
def createTable():
"""读取createtable文件夹tablename.xlsx文件中的表名，
根据相应表格格式的xlsx文件创建mysql表格
:return:
"""
# 读表名
tablenameDf = pd.read_excel('createtable/tablename.xlsx')
tablenames = tablenameDf['table']
# 读类型对应关系
# typedf = pd.read_excel('createtable/typetrans.xlsx')
# typedf.set_index('tusharetype', inplace=True)
for tablename in tablenames:
if existTable(tablename):
continue
# 读字段名
df = pd.read_excel(f'createtable/{tablename}.xlsx')
# 表头
sql = f'CREATE TABLE `{tablename}` ('
# 字段
flag = False
for _, row in df.iterrows():
rowList = row.to_list()
field = rowList[0]
dataType = rowList[1]
if flag:
sql += ', '
else:
flag = True
sql += f'`{field}` {dataType} DEFAULT NULL'
# 表尾
sql += ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;'
print(sql)
engine.execute(sql)
def createHYName():
sql = ('CREATE TABLE hangyename('
'hyid VARCHAR(8),'
'hyname VARCHAR(50),'
'hylevel INT,'
'hylevel1id VARCHAR(2),'
'hylevel2id VARCHAR(4),'
'hylevel3id VARCHAR(6),'
'PRIMARY KEY ( hyid ),'
'KEY `hyid` (`hyid`),'
'KEY `hylevel1id` (`hylevel1id`),'
'KEY `hylevel2id` (`hylevel2id`),'
'KEY `hylevel3id` (`hylevel3id`)); ')
result = engine.execute(sql)
return result
def getSubHY(hyID, subLevel):
""" 查询指定行业包含的下级行业代码
"""
level = len(hyID) // 2
sql = ('select hyid from hangyename '
'where hylevel%(level)sid="%(hyID)s" and '
'hylevel="%(subLevel)s";') % locals()
# print sql
result = engine.execute(sql)
result = result.fetchall()
# print 'getSubHY:', result
if result is None:
return None
else:
return [i[0] for i in result]
def stocknormaltest(ts_code, startDate='20090101', endDate='20191231'):
"""正态分布检验
ttmpe的分布情况，计算均值及+/-2倍标准差
取对数后的分布情况，计算均值及+/-2倍标准差，还原后的数值
"""
sql = (f'select pe_ttm from daily_basic where ts_code="{ts_code}"'
f' and trade_date>="{startDate}" and trade_date<="{endDate}"')
result = engine.execute(sql).fetchall()
data = np.reshape(result, -1)
if len(data) < 1000:
return None, None
# mu = np.mean(data)
# sigma = np.std(data)
p1 = _normaltest(data)
# print(data)
# print(f'均值:{mu:.2f}, 低值:{mu - 2 * sigma:.2f}, 高值:{mu + 2 * sigma:.2f}')
# gs = gridspec.GridSpec(1, 2, hspace=0.8, wspace=0.65)
# ax1 = plt.subplot(gs[0, 0])
# n, bins, patches = ax1.hist(data, bins=20)
# ax1twin = ax1.twinx()
# y = norm.pdf(bins, mu, sigma)
# ax1twin.plot(bins, y, 'r--')
data1 = [log(i) for i in data]
# mu1 = np.mean(data1)
# sigma1 = np.std(data1)
p2 = _normaltest(data1)
# print(f'mu1={mu1:.2f}, sigma1={sigma1:.2f}')
# print(f'对数均值:{mu1:.2f}, ',
# f'低值:{mu1 - 2 * sigma1:.2f}, ',
# f'高值:{mu1 + 2 * sigma1:.2f}')
# print(f'还原均值:{exp(mu1):.2f}, ',
# f'低值:{exp(mu1) - 2 * exp(sigma1):.2f}, '
# f'高值:{exp(mu1) + 2 * exp(sigma1):.2f}')
# print(f'还原均值:{exp(mu1):.2f}, ',
# f'低值:{exp(mu1 - 2 * sigma1):.2f}, '
# f'高值:{exp(mu1 + 2 * sigma1):.2f}')
# ax2 = plt.subplot(gs[0, 1])
# n, bins, patches = ax2.hist(data1, bins=20)
# n, bins, patches = ax.hist(data, cumulative=True)
# ax2twin = ax2.twinx()
# y = norm.pdf(bins, mu1, sigma1)
# ax2twin.plot(bins, y, 'r--')
# plt.show()
return (p1, p2)
def createGuzhiHistoryStatusTable():
""" 各字段定义 ：
integrity： BOOL类型, 过去3年数据是否完整
seculargrowth： BOOL类型, 是否保持持续增长，当某季TTM利润增长率为负时，该值为否
growthmadrate： FLOAT类型, 利润增长平均离差率
"""
sql = ('CREATE TABLE guzhihistorystatus('
'ts_code VARCHAR(6),'
'date INT(11),'
'integrity BOOL,'
'seculargrowth BOOL,'
'growthmadrate FLOAT,'
'averageincrement FLOAT,'
'PRIMARY KEY ( ts_code, date)); ')
result = engine.execute(sql)
return result
def testoutlier(end_date=2017):
"""迭代过滤奇异值"""
sql = (f'select dt_netprofit_yoy from fina_indicator '
f' where end_date="{end_date}1231"')
result = engine.execute(sql).fetchall()
data = np.reshape(result, -1)
results = []
for i in range(10):
data, dic = _testoutlier(data)
results.append(dic)
df = pd.DataFrame(results)
print(df)
df.to_excel('../data/testoutlier.xlsx')
plt.hist(data, bins=30)
plt.show()
def updateAdjFacotr():
"""
下载复权因子
前复权 = 当日收盘价 × 当日复权因子 / 最新复权因子 qfq
后复权 = 当日收盘价 × 当日复权因子 hfq
:return:
"""
sql = 'select max(trade_date) from adj_factor'
result = engine.execute(sql).fetchone()
if result is None or result[0] is None:
startDate = '20090101'
else:
startDate = result[0].strftime('%Y%m%d')
endDate = dt.datetime.today().strftime('%Y%m%d')
dateList = readCal(startDate=startDate, endDate=endDate)
for d in dateList:
downAdjFactor(d)
def stockinc(ts_code):
"""一段时期内股票分段涨跌幅度
20090804 起点，高位
20140619 低位
20150612 高位
20160128 低位
20180124 高位
20190103 低位
20190419 高位
"""
dates = [
'20090804',
'20140619',
'20150612',
'20160128',
'20180124',
'20190103',
'20190419',
]
results = []
for d in dates:
sql = f"""select (a.close * b.adj_factor) s_close
from daily a, adj_factor b
where a.ts_code="{ts_code}" and a.trade_date=
(select max(trade_date) from daily
where ts_code="{ts_code}" and trade_date<="{d}")
and a.ts_code=b.ts_code and a.trade_date=b.trade_date
"""
result = engine.execute(sql).fetchone()
if result is None or result[0] is None:
row = dict(trade_date=d, s_close=np.nan)
else:
row = dict(trade_date=d, s_close=result[0])
results.append(row)
dfstock = pd.DataFrame(results)
dfstock['s_inc'] = dfstock.s_close / dfstock.s_close.shift(1) * 100 - 100
dic = {'ts_code': ts_code}
for i in range(len(dates)):
dic[f'close{dates[i]}'] = dfstock[dfstock.trade_date ==
dates[i]].s_close.values[0]
dic[f'inc{dates[i]}'] = dfstock[dfstock.trade_date ==
dates[i]].s_inc.values[0]
# print(dfstock)
# print(dfstock.s_close)
return dic
def createIndexKline():
sql = ("CREATE TABLE `indexkline` ("
"`ts_code` varchar(9) COLLATE utf8mb4_bin NOT NULL,"
"`date` date NOT NULL,"
"`close` double DEFAULT NULL,"
"`open` double DEFAULT NULL,"
"`high` double DEFAULT NULL,"
"`low` double DEFAULT NULL,"
"`pre_close` double DEFAULT NULL,"
"`change` double DEFAULT NULL,"
"`pct_chg` double DEFAULT NULL,"
"`vol` double DEFAULT NULL,"
"`amount` double DEFAULT NULL,"
"PRIMARY KEY (`ts_code`,`date`),"
"KEY `ix_ts_code` (`ts_code`) /*!80000 INVISIBLE */,"
"KEY `ix_date` (`date`)"
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;")
result = engine.execute(sql)
return result
def _linearProfits(ts_code, startQuarter, fig):
"""
对单一自变量进行线性回归，返回（截距， 系数，平均方差）
:return:
"""
pass
sql = (f'SELECT ttmprofits FROM stockdata.ttmprofits'
f' where ts_code="{ts_code}" and date>={startQuarter};')
result = engine.execute(sql).fetchall()
cnt = len(result)
if cnt < 10:
return (None, None, None)
y = [i[0] / 10000 / 10000 for i in result]
x = np.array(range(cnt))
x = x[:, np.newaxis]
# 拟合
regr = linear_model.LinearRegression()
regr.fit(x, y)
intercept = regr.intercept_
coef = regr.coef_[0]
# print(f'截距:{intercept}, 系数:{coef}')
# 计算平均残差
Y = [intercept + i * coef for i in x]
try:
cha = sum(map(lambda a, b: abs(sqrt((a - b)**2) / a), y, Y)) / cnt
except ZeroDivisionError:
return (None, None, None)
# 绘图
# ax = plt.subplot()
ax = fig.add_subplot()
ax.scatter(x, y)
ax.plot(x, Y, color='r')
name = getStockName(ts_code)
plt.title(f'{ts_code} {name}', fontproperties='simsun', fontsize=26)
# plt.show()
filename = f'../data/linear_img/{ts_code[:6]}.png'
plt.savefig(filename)
plt.clf()
return (intercept, coef, cha)
def updatePf():
""" 重算评分
"""
# valuation.calpfnew()
sql = 'select max(date) from valuation'
result = engine.execute(sql).fetchone()
if result is None:
startDate = '20090101'
else:
startDate = result[0] + dt.timedelta(days=1)
startDate = startDate.strftime('%Y%m%d')
endDate = dt.datetime.today() - dt.timedelta(days=1)
endDate = endDate.strftime('%Y%m%d')
pro = ts.pro_api()
df = pro.trade_cal(exchange='', start_date=startDate, end_date=endDate)
dateList = df['cal_date'].loc[df.is_open == 1].tolist()
# print(type(dateList))
# print(dateList)
for date in dateList:
print('计算评分：', date)
valuation.calpfnew(date)
def createHangyePE():
sql = ("""CREATE TABLE `hangyepe` (
`hyid` varchar(8) NOT NULL,
`date` date NOT NULL,
`hype` float DEFAULT NULL,
PRIMARY KEY (`hyid`,`date`),
KEY `index_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (year(`date`))
(PARTITION p00 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p01 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN (2002) ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN (2004) ENGINE = InnoDB,
PARTITION p05 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p06 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p07 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p08 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p09 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p21 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (2022) ENGINE = InnoDB,
PARTITION p23 VALUES LESS THAN (2023) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (2024) ENGINE = InnoDB,
PARTITION p25 VALUES LESS THAN (2025) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (2026) ENGINE = InnoDB,
PARTITION pnow VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
""")
result = engine.execute(sql)
return result
def createChiguGuzhiTable():
sql = ('CREATE TABLE chiguguzhi('
'ts_code VARCHAR(6),'
'name VARCHAR(40),'
'pe DOUBLE,'
'peg DOUBLE,'
'next1YearPE DOUBLE,'
'next2YearPE DOUBLE,'
'next3YearPE DOUBLE,'
'incrate0 DOUBLE,'
'incrate1 DOUBLE,'
'incrate2 DOUBLE,'
'incrate3 DOUBLE,'
'incrate4 DOUBLE,'
'incrate5 DOUBLE,'
'avgrate DOUBLE,'
'madrate DOUBLE,'
'stdrate DOUBLE,'
'pe200 DOUBLE,'
'pe1000 DOUBLE,'
'PRIMARY KEY ( ts_code )); ')
result = engine.execute(sql)
return result
