def readKlineDf(ts_code, days):
sql = ('select date, open, high, low, close, ttmpe '
'from klinestock where ts_code="%(ts_code)s" '
'order by date desc limit %(days)s;' % locals())
result = engine.execute(sql).fetchall()
stockDatas = [i for i in reversed(result)]
# klineDatas = []
dateList = []
openList = []
closeList = []
highList = []
lowList = []
peList = []
indexes = list(range(len(result)))
for i in indexes:
date, _open, high, low, close, ttmpe = stockDatas[i]
dateList.append(date.strftime("%Y-%m-%d"))
# QuarterList.append(date)
openList.append(_open)
closeList.append(close)
highList.append(high)
lowList.append(low)
peList.append(ttmpe)
klineDf = pd.DataFrame({'date': dateList,
'open': openList,
'close': closeList,
'high': highList,
'low': lowList,
'pe': peList})
# print(klineDf)
# for i in reversed(result):
# print(i)
# plotDf(klineDf)
return klineDf
def analysePEHist(ts_code,
startDate,
endDate,
dayCount=200,
lowRate=20,
highRate=80):
"""分析指定股票一段时期内PE水平，以折线图展示
:param ts_code:
:param startDate:
:param endDate:
:param lowRate:
:param highRate:
:return:
"""
sql = (f'select date, ttmpe from klinestock where ts_code={ts_code}'
f' and date>="{startDate}" and date<="{endDate}";')
result = engine.execute(sql).fetchall()
tmpDates, tmpPEs = zip(*result)
tmpDates = list(tmpDates)
tmpPEs = list(tmpPEs)
dates = []
PEs = []
lowPEs = []
highPEs = []
cnt = len(result)
lowPos = dayCount * lowRate // 100 - 1
highPos = dayCount * highRate // 100 - 1
for i in range(dayCount - 1, cnt):
dates.append(tmpDates[i])
PEs.append(tmpPEs[i])
start = i - dayCount + 1
end = i + 1
_tmpPEs = tmpPEs[start:end]
_tmpPEs.sort()
lowPE = _tmpPEs[lowPos]
highPE = _tmpPEs[highPos]
lowPEs.append(lowPE)
highPEs.append(highPE)
df = pd.DataFrame({
'date': dates,
'pe': PEs,
'lowpe': lowPEs,
'highpe': highPEs
})
print(df)
return df
# timec = dt.datetime.now()
# ts_code = '000651'
# startDate = '20130101'
# endDate = '20191231'
# df = analysePEHist(ts_code, startDate, endDate, dayCount=1000)
# timed = dt.datetime.now()
def downHYList():
"""
更新行业列表数据
读取行业表中的股票代码，与当前获取的股票列表比较，
如果存在部分股票未列入行业表，则更新行业列表数据
"""
sql = ('select ts_code from stock_basic'
' where ts_code not in (select ts_code from classify_member)'
' and list_status="L" or list_status="P"')
result = engine.execute(sql).fetchall()
# 股票列表中上市日期不为0，即为已上市
# 且不在行业列表中，表示需更新行业数据
if result:
HYDataFilename = downHYFile()
writeHYToSQL(HYDataFilename)
writeHYNameToSQL(HYDataFilename)
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 scatter(startDate, endDate):
dateList = dateStrList(startDate, endDate)
for date in dateList:
print(date)
sql = ('select pe, lirunincrease from pelirunincrease '
f'where date="{date}";')
result = engine.execute(sql)
peList = []
incrateList = []
for pe, lirunincrease in result.fetchall():
if pe is not None and lirunincrease is not None:
peList.append(pe)
incrateList.append(lirunincrease)
if not peList:
continue
plt.scatter(incrateList, peList)
plt.axes().set_xlim((-200, 200))
plt.axes().set_ylim((-200, 200))
filename = './data/plot/%(date)s.png' % locals()
plt.savefig(filename)
plt.clf()
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 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