def get_industry_index(nlist=[3,7,14,31,100],key='行业')->pd.DataFrame:
def find_growth_index(x:pd.DataFrame,nlist=nlist):
x=x.copy()
x.sort_values(by='日期',ascending=False)
x=x[0:2*max(nlist)].copy()
x.reset_index(inplace=True)
res=pd.DataFrame()
for n in nlist:
str1=str(n)+'日涨跌幅'
res.loc[0,str1]=((x.loc[0,'收盘价'])-(x.loc[n,'收盘价']))/x.loc[n,'收盘价']
xc = x.shift(n)
str2 = str(n) + '日最大涨幅'+str(2*max(nlist))+'日内'
str3 = str(n) + '日最小涨幅'+str(2*max(nlist))+'日内'
up_down=((xc['收盘价']-x['收盘价'])/x['收盘价'])[n:]
print(up_down)
print(max((up_down)),min(up_down))
res.loc[0, [str2,str3]]=[max((up_down)),min(up_down)]
# res.loc[0,str2],res.loc[0,str3]= \
# max((up_down)), min(up_down)
return res
sql='select * from tb_ak_industry_index' if key=='行业' else 'select * from tb_ak_concept_index'
engine=configger.getEngine()
data=pd.read_sql(con=engine,sql=sql)
result=data.groupby(key).apply(lambda x:find_growth_index(x))
result.index.names=[key,'序号']
result.reset_index(level=1,drop=True,inplace=True)
path=os.path.join(configger.default_save_path,key+'信息.xlsx')
result.to_excel(path)
return result
def select_forecat_report_data(date='') -> pd.DataFrame:
def find_net_profit(x):
if (x['预测指标'].find('净利润') > 0
and (x['code'].find('sh') >= 0 or x['code'].find('sz') >= 0)):
return True
else:
return False
# pd.set_option('display.max_columns', 50)
engine = configger.getEngine()
if (date == ''):
date = datetime.datetime.now().date()
sql = 'SELECT * FROM tb_ak_bi_forecast_report where date ="{}"'.format(
timeUtil.get_last_end_quarter_day(date))
else:
sql = 'SELECT * FROM tb_ak_bi_forecast_report where date ="{}"'.format(
date)
print(sql)
data = pd.read_sql(sql=sql,
con=engine)[['code', 'date', '预测指标', '业绩变动幅度', '预测数值']]
data = data.loc[data.apply(lambda x: find_net_profit(x), axis=1)]
data = data.groupby('code').apply(lambda x: find_max(x, column='业绩变动幅度'))
data.reset_index(drop=True, inplace=True)
data.dropna(subset=['code'], inplace=True)
return data
def get_last_season_net_profit():
engine = configger.getEngine()
lastquater = timeUtil.get_last_end_quarter_day(
datetime.datetime.now().date())
last_two_quater = timeUtil.get_last_end_quarter_day(lastquater)
print(
lastquater, last_two_quater,
timeUtil.get_this_end_quarter_day(lastquater.replace(day=1, month=1)))
last_data = select_forecat_report_data(lastquater)
last_two_data = pd.read_sql(
'select * from tb_ak_bi_yjbb where date="{}"'.format(
last_two_quater.date()),
con=engine)
last_two_data = last_two_data[['code', '净利润-净利润']]
print(last_data)
print(last_two_data)
last_data = pd.merge(left=last_data, right=last_two_data, how='inner')
if (lastquater != timeUtil.get_this_end_quarter_day(
lastquater.replace(day=1, month=1))):
#todo 预测数值单季度计算
last_data['净利润'] = last_data['预测数值'] - last_two_data['净利润-净利润']
# last_data['净利润']=last_data['预测数值']-last_two_data['预测数值']
else:
last_data['净利润'] = last_data['预测数值']
last_data['净利润同比增长率'] = (
last_data['净利润'] - last_two_data['净利润-净利润']) / last_two_data['净利润-净利润']
# print(last_data[['code','净利润','净利润环比增长率']])
last_data.to_excel('a.xlsx')
return last_data
def save_index_data(index_table='tb_ak_index_names',
index_content='tb_ak_index_content',
index_his='tb_ak_his_data'):
configger.init()
engine = configger.getEngine()
all_index = query_all_index_spot()
all_index_clone = a_dot_to_code(all_index.copy(), 'code')
datalist = [i for i in all_index['code']]
content_df = pd.DataFrame()
all_index_clone.to_sql(index_table, con=engine, if_exists='replace')
for i in datalist:
try:
c = query_index_content(i)
print(c)
except:
continue
if (content_df.empty):
content_df = c
else:
content_df = content_df.append(c)
content_df.to_sql(index_content, con=engine, if_exists='replace')
his_df = pd.DataFrame()
for i in datalist:
try:
his = query_his_index_by_code(i)
print(his)
except:
continue
if (his_df.empty):
his_df = his
else:
his_df = his_df.append(his)
his_df.to_sql(index_content, con=engine, if_exists='replace')
configger.colseEngine(engine)
def send_recent_forecat_by_sql(days=[1,3]):
resstr='老大\r\n'
for day in days:
sql="select * from tb_ak_bi_forecast_report where" \
"(code like 'sh.%%' or code like 'sz.%%') and" \
" TIMESTAMPDIFF(DAY ,公告日期,(select max(公告日期) from tb_ak_bi_forecast_report )) <={}" .format(day)
print(sql)
engine=configger.getEngine()
data=pd.read_sql(sql=sql,con=engine)
data['业绩变动幅度'].fillna(0,inplace=True)
data.sort_values(by=['业绩变动幅度'],inplace=True,ascending=False)
data.to_excel(os.path.join(configger.default_save_path,'近{}日业绩报告.xlsx'.format(day)))
count=data['业绩变动幅度'].count()
print(count)
data['temp']=data['股票简称']+'('+data['code']+')'
str1=','.join(data[0:10]['temp'].tolist())
resstr+='今天(%{}),近{}天更新的业绩共有{}条，详细如下:\r\n'.format(datetime.datetime.now().date(),day,count)
resstr+=str1
data['res']=data['temp']+':'+data['业绩变动幅度'].apply(str)
res=data[0:3]
str2='，'.join(res['res'].tolist())
resstr+='业绩增幅前三的股票有{}'.format(str2)
print(resstr)
ding.send_text(msg=resstr)
resstr='老大\r\n'
def select_growth_data(code='', n=12):
configger.init()
engine = configger.getEngine()
sql = configger.all_growthSQL
if (code == ''):
data = pd.read_sql(sql=sql, con=engine)
recent_data = data.groupby('code').apply(lambda x: getRecnetN(x, n))
recent_data.index.names = ['code', 'index']
recent_data.reset_index(inplace=True)
recent_data.drop(columns='index', inplace=True)
print(recent_data)
return recent_data