def downPledgeStat(ts_code):
"""获取股权质押统计数据
:param ts_code:
:return:
"""
pro = ts.pro_api()
df = pro.pledge_stat(ts_code=tsCode(ts_code))
writeSQL(df, 'pledgestat')
def downDailyRepair():
"""修复日K线"""
# stocks = readStockList()
sql = ('select ts_code from stock_basic'
' where ts_code not in (select distinct ts_code from daily);')
stocks = pd.read_sql(sql, engine)
pro = ts.pro_api()
for ts_code in stocks.ts_code.to_list():
print('下载日K线：', ts_code)
df = pro.daily(ts_code=ts_code)
writeSQL(df, 'daily')
def downStockQuarterData(table, ts_code, start_date, fields='', replace=False):
print(f'downStockQuarterData table:{table}, ts_code:{ts_code}')
logging.debug(f'downStockQuarterData table:{table}, ts_code:{ts_code}')
pro = ts.pro_api()
fun = getattr(pro, table)
kwargs = dict(ts_code=ts_code, start_date=start_date, fields=fields)
df = fun(**kwargs)
if df.empty:
return False
else:
writeSQL(df, table, replace=replace)
return True
def _download(args):
"""下载非定期更新数据"""
table, pertimes, limit = args
fields = ''
if table == 'dividend':
fields = readTableFields(table)
stocks = readStockList()
total = len(stocks)
cnt = 1
downloader = DownloaderMisc(pertimes, limit)
for ts_code in stocks.ts_code:
print(f'下载{table} {cnt}/{total}: {ts_code}')
df = downloader.run(table, ts_code=ts_code, fields=fields)
writeSQL(df, table, replace=True)
cnt += 1
def downIncome(ts_code, startDate='', endDate=''):
"""下载tushare利润表
:param ts_code: str, 股票代码
:param startDate: str, 开始日期, yyyymmdd
:param endDate: str, 结束日期, yyyymmdd
:return:
"""
if len(ts_code) == 6:
ts_code = tsCode(ts_code)
pro = ts.pro_api()
df = pro.income(ts_code=tsCode(ts_code),
start_date=startDate,
end_date=endDate)
print(df)
writeSQL(df, 'income')
def downIndexDailyBasic():
"""
下载指数每日指标
000001.SH 上证综指
000005.SH 上证商业类指数
000006.SH 上证房地产指数
000016.SH 上证50
000300.SH 沪深300
000905.SH 中证500
399001.SZ 深证成指
399005.SZ 中小板指
399006.SZ 创业板指
399016.SZ 深证创新
399300.SZ 沪深300
399905.SZ 中证500
:return:
"""
pro = ts.pro_api()
codeList = [
'000001.SH',
'000005.SH',
'000006.SH',
'000016.SH',
'000905.SH',
'399001.SZ',
'399005.SZ',
'399006.SZ',
'399016.SZ',
'399300.SZ',
]
for code in codeList:
sql = (f'select max(trade_date) from index_dailybasic'
f' where ts_code="{code}"')
result = engine.execute(sql).fetchone()[0]
startDate = None
if isinstance(result, dt.date):
result = result + dt.timedelta(days=1)
startDate = result.strftime('%Y%m%d')
# startDate = '20040101'
# endDate = '20080101'
df = pro.index_dailybasic(ts_code=code, start_date=startDate)
print('index_dailybasic:', startDate)
print('ts_code:', code)
print(df.head())
writeSQL(df, 'index_dailybasic')
def downAdjFactor(trade_date, retry=3):
"""
下载复权因子
前复权 = 当日收盘价 × 当日复权因子 / 最新复权因子 qfq
后复权 = 当日收盘价 × 当日复权因子 hfq
:return:
"""
logging.debug(f'下载复权因子: {trade_date}')
pro = ts.pro_api()
for _ in range(retry):
try:
df = pro.adj_factor(trade_date=trade_date)
writeSQL(df, 'adj_factor')
except (socket.timeout, ConnectTimeout):
continue
else:
break
def downIndexBasic():
"""
从tushare下载指数基本信息
市场代码 说明
MSCI MSCI指数
CSI 中证指数
SSE 上交所指数
SZSE 深交所指数
CICC 中金指数
SW 申万指数
OTH 其他指数
:return:
"""
pro = ts.pro_api()
df_index_basic_sh = pro.index_basic(market='SSE')
df_index_basic_sz = pro.index_basic(market='SZSE')
writeSQL(df_index_basic_sh, 'index_basic')
writeSQL(df_index_basic_sz, 'index_basic')
def testChigu():
# youzhiSelect()
# inFilename = './data/chiguts_code.txt'
# outFilename = './data/chiguguzhi.csv'
# testStockList = ['600519', '600999', '000651', '000333']
# testStockList = sqlrw.readStockListFromFile(inFilename)
stockList = sqlrw.loadChigu()
# print testStockList
df = calGuzhi(stockList)
# df = calGuzhi()
# dfToCsvFile(df, outFilename)
# df.to_csv(outFilename)
sqlrw.engine.execute('TRUNCATE TABLE chiguguzhi')
# df.index.name = 'ts_code'
# clearStockList()
# df.set_index('ts_code', inplace=True)
# print df.head()
sqlrw.writeSQL(df, 'chiguguzhi')
def downloaderStock(tablename, stocks, perTimes=0, downLimit=0):
"""tushare用的下载器，可限制对tushare的访问量
tushare下载限制，每perTimes秒限制下载downLimit次
本函数只适用于股票类表格
:return:
"""
pro = ts.pro_api()
times = []
cnt = len(stocks)
# tablename = 'income'
for i in range(cnt):
nowtime = dt.datetime.now()
delta = dt.timedelta(seconds=perTimes)
if perTimes > 0 and 0 < downLimit <= i and (
nowtime < times[i - downLimit] + delta):
_timedelta = nowtime - times[i - 50]
sleeptime = 60 - _timedelta.seconds
print(f'******暂停{sleeptime}秒******')
time.sleep(sleeptime)
nowtime = dt.datetime.now()
times.append(nowtime)
print(f'第{i}个，时间：{nowtime}')
ts_code = stocks[i]
print(ts_code)
flag = True
df = None
fun = getattr(pro, tablename)
while flag:
try:
# 下载质押统计表
# df = downPledgeStat(ts_code)
# 下载利润表
# df = downIncome(ts_code)
df = fun(ts_code=ts_code, stocks=stocks)
flag = False
except Exception as e:
print(e)
time.sleep(10)
# print(df)
time.sleep(1)
if df is not None:
writeSQL(df, tablename)
def downDaily(trade_date=None):
"""下载日K线数据
:param trade_date:
:return:
"""
pro = ts.pro_api()
dates = []
if trade_date is None:
sql = 'select max(trade_date) from daily'
startDate = engine.execute(sql).fetchone()[0]
assert isinstance(startDate, dt.date), 'startDate应为date类型'
startDate += dt.timedelta(days=1)
endDate = dt.datetime.now().date()
dates = dateStrList(startDate.strftime('%Y%m%d'),
endDate.strftime('%Y%m%d'))
else:
dates.append(trade_date)
for d in dates:
logging.debug(f'下载日线:{d}')
df = pro.daily(trade_date=d)
writeSQL(df, 'daily')
def downDailyBasic(ts_code=None, tradeDate=None, startDate=None, endDate=None):
"""
从tushare下载股票每日指标
:param ts_code: 股票代码
:param tradeDate: 交易日期
:param startDate: 开始日期
:param endDate: 结束日期
:return:
"""
pro = ts.pro_api()
df = None
if ts_code is not None and startDate is not None:
df = pro.daily_basic(ts_code=tsCode(ts_code),
start_date=startDate,
end_date=endDate)
elif tradeDate is not None:
df = pro.daily_basic(trade_date=tradeDate)
if isinstance(df, pd.DataFrame):
# df.rename(columns={'ts_code': 'ts_code', 'trade_date': 'date'},
# inplace=True)
# df['ts_code'] = df['ts_code'].str[:6]
df.set_index(keys=['ts_code'], inplace=True)
writeSQL(df, 'daily_basic')
return df
def calpfnew(date, replace=False):
""" 根据各指标计算评分，分别写入文件和数据库
新版，支持按指定日期计算评分，评分结果写入带日期的新表
:param date: str
'YYYYmmdd'格式的日期
:return:
"""
# stocks = readStockListDf()[:10]
if not replace:
sql = f'select count(1) from valuation where date="{date}"'
result = engine.execute(sql).fetchone()[0]
if result > 0:
return
stocks = readStockList()
# print(stocks)
# 低市盈率
peDf = readLastTTMPEs(stocks.ts_code.tolist(), date)
if peDf is None:
return
stocks = pd.merge(stocks, peDf, on='ts_code', how='inner')
stocks['lowpe'] = stocks.apply(lowpe, axis=1)
# 市盈率低于行业平均
sql = 'select ts_code, classify_code from classify_member;'
classifyDf = pd.read_sql(sql, engine)
stocks = pd.merge(stocks, classifyDf, on='ts_code', how='left')
classifyPEDf = classifyanalyse.getClassifyPE(date)
if classifyPEDf is None or classifyPEDf.empty:
classifyanalyse.calClassifyPE(date)
classifyPEDf = classifyanalyse.getClassifyPE(date)
stocks = pd.merge(stocks, classifyPEDf, on='classify_code', how='left')
stocks['lowhype'] = stocks.apply(lowhype, axis=1)
# 过去6个季度利润稳定增长
sectionNum = 6 # 取6个季度
incDf = sqlrw.readLastTTMProfits(stocks.ts_code.tolist(), sectionNum, date)
stocks = pd.merge(stocks, incDf, on='ts_code', how='left')
stocks['avg'] = incDf.mean(axis=1).round(2)
stocks['std'] = incDf.std(axis=1).round(2)
stocks['wdzz'] = incDf.apply(wdzz, axis=1)
# 利润增长的平均标准差与平均增长率的比值， 小于1时判断为增长稳定
stocks['wdzz1'] = incDf.apply(wdzz1, axis=1)
# 根据过去6季度TTM利润平均增长率与TTMPE计算PEG
stocks['peg'] = stocks['pe'] / stocks['avg']
stocks['peg'] = stocks['peg'].round(2)
stocks['lowpeg'] = stocks.apply(lowPEG, axis=1)
# 200天Z值小于-1
stocks['pez200'] = stocks.apply(peZ, axis=1, args=(200, date))
stocks['pez200'] = stocks['pez200'].round(2)
stocks['lowpez200'] = stocks.apply(lowPEZ200, axis=1)
# 1000天Z值小于-1
stocks['pez1000'] = stocks.apply(peZ, axis=1, args=(1000, date))
stocks['pez1000'] = stocks['pez1000'].round(2)
stocks['lowpez1000'] = stocks.apply(lowPEZ1000, axis=1)
# return stocks
# 计算pe200与pe1000
# stocks['pe200'] = analyse.peHistRate(stocks.ts_code.tolist(), 200)
# stocks['pe1000'] = analyse.peHistRate(stocks.ts_code.tolist(), 1000)
df = analyse.report.peHistRate(stocks.ts_code.tolist(), 200, date)
stocks = pd.merge(stocks, df, on='ts_code', how='left')
df = analyse.report.peHistRate(stocks.ts_code.tolist(), 1000, date)
stocks = pd.merge(stocks, df, on='ts_code', how='left')
# 计算总评分
stocks['pf'] = stocks.lowpe
stocks['pf'] += stocks.lowhype
stocks['pf'] += stocks.wdzz1
stocks['pf'] += stocks.lowpeg
stocks['pf'] += stocks.lowpez200
stocks['pf'] += stocks.lowpez1000
stocks = stocks.sort_values(by='pf', ascending=False)
# 设置输出列与列顺序
# guzhiDf = guzhiDf[['ts_code', 'name', 'pe',
# 'incrate0', 'incrate1', 'incrate2',
# 'incrate3', 'incrate4', 'incrate5',
# 'avgrate', 'madrate', 'stdrate', 'pe200', 'pe1000'
# ]]
# mystocks = ['002508', '600261', '002285', '000488',
# '002573', '300072', '000910']
# mystockspf = stocks[stocks['ts_code'].isin(mystocks)]
# mystockspf.set_index(['ts_code'], inplace=True)
# mystockspf.to_csv('./data/valuationmystocks.csv')
# 保存评价结果
stocks.set_index(['ts_code'], inplace=True)
# stocks.to_csv('./data/valuation.csv')
pfFilename = f'valuations{date}.xlsx'
stocks.to_excel(os.path.join('data', pfFilename))
# 将评分发送到邮箱
cf = Config()
pushflag = cf.pushData
if pushflag:
mailTitle = f'评分{date}'
pushdata.push(mailTitle, pfFilename)
# print stocks
# if initsql.existTable('valuation'):
# engine.execute('TRUNCATE TABLE valuation')
stocks = stocks.dropna()
stocks['date'] = date
# 当计算peg时，如果平均增长率为0，则结果为inf
# 将inf替换为-9999
stocks.replace([np.inf, -np.inf], -9999, inplace=True)
# print(stocks)
# return
# if replace:
# for index, row in stocks.iterrows():
# sql = (('replace into valuation'
# '(ts_code, date, totalshares) '
# 'values("%s", "%s", %s)')
# % (row['ts_code'], row['date'], row['totalshares']))
# engine.execute(sql)
# else:
# sqlrw.writeSQL(stocks, 'valuation')
sqlrw.writeSQL(stocks, 'valuation', replace)
# stocks.to_sql('valuation', engine, if_exists='append')
return stocks
def downStockList():
""" 更新股票列表与行业列表
"""
pro = ts.pro_api()
df = pro.stock_basic()
writeSQL(df, 'stock_basic')
def downIndexWeight():
"""
下载指数成份和权重
000001.SH 上证综指
000005.SH 上证商业类指数
000006.SH 上证房地产指数
000016.SH 上证50
000300.SH 沪深300
000905.SH 中证500
399001.SZ 深证成指
399005.SZ 中小板指
399006.SZ 创业板指
399016.SZ 深证创新
399300.SZ 沪深300
399905.SZ 中证500
:return:
"""
pro = ts.pro_api()
codeList = [
'000001.SH',
'000005.SH',
'000006.SH',
'000010.SH',
'000016.SH',
'000905.SH',
'399001.SZ',
'399005.SZ',
'399006.SZ',
'399016.SZ',
'399300.SZ',
]
times = []
cur = 0
perTimes = 60
downLimit = 70
for code in codeList:
sql = (f'select max(trade_date) from index_weight'
f' where index_code="{code}"')
initDate = engine.execute(sql).fetchone()[0]
if initDate is None:
initDate = dt.date(2001, 1, 1)
assert isinstance(initDate, dt.date)
while initDate < dt.datetime.today().date():
nowtime = dt.datetime.now()
delta = dt.timedelta(seconds=perTimes)
if (perTimes > 0 and 0 < downLimit <= cur
and (nowtime < times[cur - downLimit] + delta)):
_timedelta = nowtime - times[cur - downLimit]
sleeptime = perTimes - _timedelta.seconds
print(f'******暂停{sleeptime}秒******')
time.sleep(sleeptime)
startDate = initDate.strftime('%Y%m%d')
initDate += dt.timedelta(days=30)
endDate = initDate.strftime('%Y%m%d')
initDate += dt.timedelta(days=1)
print(f'下载{code},日期{startDate}-{endDate}')
df = pro.index_weight(index_code=code,
start_date=startDate,
end_date=endDate)
writeSQL(df, 'index_weight')
nowtime = dt.datetime.now()
times.append(nowtime)
cur += 1
def testShaixuan():
stockList = sqlrw.readStockList().ts_code.values
df = calGuzhi(stockList)
df = df.dropna()
sqlrw.engine.execute('TRUNCATE TABLE guzhiresult')
sqlrw.writeSQL(df, 'guzhiresult')
def downTradeCal(year):
pro = ts.pro_api()
df = pro.trade_cal(exchange='SSE', start_date=f'{year}0101')
writeSQL(df, 'trade_cal')
