def _append_index_component_daily(self, trade_date):
"""
保存或追加某日指数的成分股
每分钟最多访问该接口70次
:param trade_date: str
example: 20190101
"""
trade_date_tmp = trade_date
if isinstance(trade_date, str):
trade_date_tmp = dtu.tsformat_to_datetime(trade_date)
if isinstance(trade_date, datetime):
trade_date_tmp = dtu.datetime_to_dbformat(trade_date)
table_index_comp = 'index_comp'
sql_index_comp = "SELECT count(1) AS count FROM index_comp WHERE trade_date='%s'" % trade_date_tmp
try:
# check if data exists
res = dbu.read_df(sql_index_comp)
count = res['count'].iloc[0]
if count > 0:
print("Daily index component exists: %s" % trade_date)
return
# load date
index_comp = tsu.query_index_component_daily(trade_date)
# save
if index_comp.shape[0] > 0:
dbu.save_df(index_comp, table_index_comp)
print("Successful load daily index component: %s" % trade_date)
else:
print("No data: %s" % trade_date)
time.sleep(2)
except Exception as e:
print("Failed to load daily index component: %s" % trade_date)
raise e
def save_index_list(self):
"""
保存中证、上交所、深交所指数列表
与已存在的数据合并，保持一份最新
"""
table_index_list = "index_basic"
sql_delete_index_basic = 'DELETE FROM %s' % table_index_list
try:
index_list_csi = tsu.query_index_list("CSI")
index_list_sse = tsu.query_index_list('SSE')
index_list_szse = tsu.query_index_list('SZSE')
index_list_msci = tsu.query_index_list('MSCI')
index_list_cicc = tsu.query_index_list('CICC')
index_list_sw = tsu.query_index_list('SW')
index_list_cni = tsu.query_index_list('CNI')
index_list_oth = tsu.query_index_list('OTH')
index_list = index_list_csi.append([
index_list_sse, index_list_szse, index_list_msci,
index_list_cicc, index_list_sw, index_list_cni, index_list_oth
])
index_list['base_date'] = dtu.tsformat_col_to_datetime(
index_list['base_date'])
index_list['list_date'] = dtu.tsformat_col_to_datetime(
index_list['list_date'])
index_list['exp_date'] = dtu.tsformat_col_to_datetime(
index_list['exp_date'])
dbu.save_df(index_list, table_index_list, if_exists='replace')
print("Successfully load index list")
except Exception as e:
print("Failed to load index list")
raise e
def _append_daily_info(self, trade_date):
"""
保存每日行情数据和指标
:param trade_date: str
example: 20190101
"""
trade_date_tmp = trade_date
table_stock_price = 'stock_price'
sql_stock_price = "SELECT count(1) AS count FROM stock_price WHERE trade_date='%s'" % trade_date_tmp
try:
# check if data exists
res = dbu.read_df(sql_stock_price)
count = res['count'].iloc[0]
if count > 0:
print("Daily price exists: %s" % trade_date)
return
# load date
price_daily = tsu.query_stock_price_daily(trade_date)
basic_daily = tsu.query_stock_basic_daily(trade_date)
info_daily = pd.merge(price_daily,
basic_daily,
how='left',
on=['ts_code', 'trade_date'])
# save
dbu.save_df(info_daily, table_stock_price)
print("Successful load daily price: %s" % trade_date)
time.sleep(2)
except Exception as e:
print("Failed to load daily price: %s" % trade_date)
raise e
def save_stock_list(self):
"""
保存上市股票信息
该接口只有上市股票数据，没有退市股票数据
保留旧记录，增加新记录
"""
table_stock_basic = 'stock_basic'
try:
# ts_code to drop
stock_exist = dbu.read_df("SELECT ts_code FROM %s" %
table_stock_basic)
ts_code_drop = stock_exist['ts_code']
# load data from tushare
stock_list = tsu.query_stock_list()
# difference set
flag = stock_list['ts_code'].isin(ts_code_drop)
diff_flag = [not f for f in flag]
stock_list = stock_list[diff_flag]
# type conversion
stock_list.replace(to_replace=enum_to_int, inplace=True)
stock_list['list_date'] = dtu.tsformat_col_to_datetime(
stock_list['list_date'])
stock_list['delist_date'] = dtu.tsformat_col_to_datetime(
stock_list['delist_date'])
# insert new records
dbu.save_df(stock_list, table_stock_basic)
print("Successfully load stock list: %d" % stock_list.shape[0])
except Exception as e:
print(e)
def index_pe_ttm(self, index_codes, start_date, end_date):
"""
给定index集合，计算从start_date至end_date的pe
:param index_codes: list / str
:param start_date: str
起始日期，yyyymmdd
:param end_date: str
结束日期，yyyymmdd
"""
if isinstance(index_codes, str):
index_codes = [index_codes]
curr_date = start_date
last_stock_pettm = None
last_index_comp = None
hist_index_pettm = pd.DataFrame(columns=['index_code', 'pe_ttm', 'trade_date'])
while(dtu.tsformat_compare(curr_date, end_date) <= 0):
if self._market_info.is_trade_date(curr_date):
curr_stock_pettm = self._daily_stock_pettm(last_stock_pettm, curr_date)
curr_index_comp = self._daily_index_comp(index_codes, last_index_comp, curr_date)
curr_index_pettm = self._daily_index_pettm(curr_index_comp, curr_stock_pettm)
curr_index_pettm['trade_date'] = dtu.tsformat_to_dbformat(curr_date)
hist_index_pettm = hist_index_pettm.append(curr_index_pettm, ignore_index=True)
last_stock_pettm = curr_stock_pettm
last_index_comp = curr_index_comp
curr_date = dtu.get_next_day(curr_date)
dbu.save_df(hist_index_pettm, 'index_pe', if_exists='replace')
def save_stock_profit(self, start_date, end_date):
"""
按个股保存利润数据
个股列表来自数据库
:param start_date: str
如'20181201'
:param end_date: str
如'20181201'
"""
start_date_tmp = dtu.tsformat_to_datetime(start_date)
start_date_tmp = dtu.datetime_to_dbformat(start_date_tmp)
end_date_tmp = dtu.tsformat_to_datetime(end_date)
end_date_tmp = dtu.datetime_to_dbformat(end_date_tmp)
sql_stock_list = 'SELECT DISTINCT ts_code FROM stock_price'
sql_stock_profit = 'SELECT ann_date, f_ann_date, end_date, report_type, 1 AS flag_col FROM stock_profit ' \
'WHERE ts_code=\'%s\' AND ann_date BETWEEN \'' + start_date_tmp + \
'\' AND \'' + end_date_tmp + '\''
table_stock_profit = 'stock_profit'
try:
# load stock list
stock_list = dbu.read_df(sql_stock_list)
# iterate
for stock_code in stock_list['ts_code']:
stock_profit = self._get_stock_profit(stock_code,
start_date=start_date,
end_date=end_date)
# insert latest records
if stock_profit.shape[0] > 0:
stock_profit_exist = dbu.read_df(sql_stock_profit %
stock_code)
if stock_profit_exist.shape[0] > 0:
stock_profit_exist[
'ann_date'] = dtu.datetime_col_to_tsformat(
stock_profit_exist['ann_date'])
stock_profit_exist[
'f_ann_date'] = dtu.datetime_col_to_tsformat(
stock_profit_exist['f_ann_date'])
stock_profit_exist[
'end_date'] = dtu.datetime_col_to_tsformat(
stock_profit_exist['end_date'])
stock_profit_exist['report_type'] = stock_profit_exist[
'report_type'].apply(lambda x: str(x))
stock_profit = pd.merge(stock_profit,
stock_profit_exist,
how='left',
on=[
'ann_date', 'f_ann_date',
'end_date', 'report_type',
'end_date'
])
stock_profit = stock_profit[pd.isnull(
stock_profit['flag_col'])]
stock_profit = stock_profit.drop(['flag_col'], axis=1)
if stock_profit.shape[0] > 0:
# type conversion
stock_profit['ann_date'] = dtu.tsformat_col_to_datetime(
stock_profit['ann_date'])
stock_profit['f_ann_date'] = dtu.tsformat_col_to_datetime(
stock_profit['f_ann_date'])
stock_profit['end_date'] = dtu.tsformat_col_to_datetime(
stock_profit['end_date'])
dbu.save_df(stock_profit, table_stock_profit)
print("Successfully load stock profit: %s, num: %d" %
(stock_code, stock_profit.shape[0]))
else:
print("No stock profit: %s" % stock_code)
except Exception as e:
print(e)