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