def moniter(self, pcode=None): msql = md.datamodule() latestday = msql.getlatestday('daily_basic') df_now = msql.pull_mysql(db='daily_basic', date=latestday) df_now.set_index(["ts_code"], inplace=True) df_basic = pd.DataFrame() if pcode != None: try: df_basic = pd.read_csv(self.monitor_csv) if not df_basic.empty: if df_basic.iloc[0]['lastupdate'] == latestday: #已经是最新的数据了,直接返回需要的值 return df_basic[df_basic['ts_code'] == pcode] except: print('没找到:' + self.monitor_csv, '重新建立') #读取过去几年最高价和最低价记录 df_basic = pd.read_csv(self.basic_csv) df_basic.set_index(["ts_code"], inplace=True) for col in self.col_list: collow = col + 'low' colhigh = col + 'high' colrate = col + 'rate' df_basic[col] = df_now[col] df_basic[colrate] = (df_now[col] - df_basic[collow]) / ( df_basic[colhigh] - df_basic[collow]) * 100 df_basic['lastupdate'] = latestday df_basic.to_csv(self.monitor_csv, encoding='utf_8_sig') return df_basic.loc[pcode]
def test_findoneblackswan(self, code='000001.SZ'): msql = md.datamodule() df1 = msql.pull_mysql(db='daily_basic_ts_code', limit=self.blackday, ts_code=code) self._isblackswan(df=df1) print(df1)
def build(self): df_rslt = pd.DataFrame() df_template = pd.read_excel(self.template,sheet_name='估值结论(FCFF)',index_col = 'id') msql = md.datamodule() ts_code_df = msql.getts_code() for i,code in ts_code_df.iterrows(): df1 = self.buildone(df = df_template,code = code['ts_code']) if df1.empty: continue dic = {} dic['ts_code'] = [code['ts_code']] dic['evaluation'] = [df1['y-6'][37]] dic['sus_grow_rate'] = [df1['y-6'][22]] dic['y1_grow_rate'] = [df1['y1'][22]] dic['y2_grow_rate'] = [df1['y2'][22]] dic['y3_grow_rate'] = [df1['y3'][22]] dic['y4_grow_rate'] = [df1['y4'][22]] dic['y5_grow_rate'] = [df1['y5'][22]] dic['y0_grow_rate'] = [df1['y0'][14]] dic['y-1_grow_rate'] = [df1['y-1'][14]] dic['y-2_grow_rate'] = [df1['y-2'][14]] df1 = pd.DataFrame.from_dict(dic) df_rslt = pd.concat([df_rslt,df1.loc[0:0,]],ignore_index = True) df_rslt.to_csv(self.FCFF_csv,encoding='utf_8_sig',index = False)
def monitor(self,pcode = None): msql = md.datamodule() latestday = msql.getlatestday('daily_basic') dfm = pd.DataFrame() if pcode != None : try: dfm = pd.read_csv(self.monitor_csv,index_col = 0) if not dfm.empty: #已经是最新的数据了,直接返回需要的值 if dfm.iloc[0]['lastupdate'] == int(latestday): return dfm.loc[pcode] except: print('没找到:'+self.monitor_csv,'重新建立') df_now = msql.pull_mysql(db = 'daily_basic',date = latestday) df_now.set_index(["ts_code"], inplace=True,drop = True) df_basic = pd.read_csv(self.FCFF_csv,index_col = 0) df_basic['lastupdate'] = latestday df_basic['total_mv'] = df_now['total_mv']*10000 df_basic['市场高估比率'] = (df_now['total_mv']*10000- df_basic['evaluation'])/(df_now['total_mv']*10000) df_basic = msql.joinnames(df_basic) df_basic.sort_values('市场高估比率',ascending = True) df_basic.to_csv(self.monitor_csv,encoding='utf_8_sig',,index = False) if pcode != None: return df_basic.loc[pcode]
def build(self): msql = md.datamodule() ts_code_df = msql.getts_code() df = pd.DataFrame() latestday = msql.getlatestday('daily_basic') df_now = msql.pull_mysql(db='daily_basic', date=latestday) df_now.set_index(["ts_code"], inplace=True) for i, code in ts_code_df.iterrows(): #取到的数据默认按照日期排序,最近的排在前面 df1 = msql.pull_mysql(db='dividend', ts_code=code['ts_code']) if not df1.empty: closenow = df_now['close'][code][0] df1['dividenrate'] = df1['cash_div_tax'] * 100 / closenow df1.sort_values('end_date', ascending=False) dftemp = df1.loc[(df1['end_date'] > '20151231')] sum = dftemp.agg({'dividenrate': ['sum']}) df1.reindex() df1['sum'] = sum['dividenrate']['sum'] df = pd.concat([df, df1], ignore_index=True) if not df.empty: df = msql.joinnames(df) #df = df.sort_values('dividenrate',ascending = False) df.to_csv(self.dividend_csv, index=False, encoding='utf_8_sig') else: str = 'error' print(str) return
def sub_getlaterprice(self): msql = md.datamodule() tradeday_list = msql.gettradedays() df = pd.read_csv(self.blackswan_csv1) df['onemonth'] = 0 df['twomonth'] = 0 df['threemonth'] = 0 df['onedate'] = 0 df['twodate'] = 0 df['threedate'] = 0 total = len(df) for i, row in df.iterrows(): trade_day = str(row['trade_date']) lestdays = len(tradeday_list) try: d = tradeday_list.index(trade_day) except: print("err:trade day not in list" + trade_day) continue if d + 90 <= lestdays: date_treemonth = tradeday_list[d + 90] else: date_treemonth = tradeday_list[lestdays - 1] if d + 60 <= lestdays: date_twoonth = tradeday_list[d + 60] else: date_twoonth = tradeday_list[lestdays - 1] if d + 30 < lestdays: date_onemonth = tradeday_list[d + 30] else: date_onemonth = tradeday_list[lestdays - 1] df_onemonth = msql.pull_mysql(db='daily_basic_ts_code', date=date_onemonth, ts_code=row['ts_code']) df_twomonth = msql.pull_mysql(db='daily_basic_ts_code', date=date_twoonth, ts_code=row['ts_code']) df_threemonth = msql.pull_mysql(db='daily_basic_ts_code', date=date_treemonth, ts_code=row['ts_code']) if not df_onemonth.empty: df.at[i, 'onemonth'] = df_onemonth.loc[0]['total_mv'] df.at[i, 'onedate'] = df_onemonth.loc[0]['trade_date'] if not df_twomonth.empty: df.at[i, 'twomonth'] = df_twomonth.loc[0]['total_mv'] df.at[i, 'twodate'] = df_twomonth.loc[0]['trade_date'] if not df_threemonth.empty: df.at[i, 'threemonth'] = df_threemonth.loc[0]['total_mv'] df.at[i, 'threedate'] = df_threemonth.loc[0]['trade_date'] df.to_csv(self.blackswan_csv1, index=False) return
def builddf(self): msql = md.datamodule() ts_code_df = msql.getts_code() for i, code in ts_code_df.iterrows(): df1 = msql.pull_mysql(db='daily_basic_ts_code', limit=self.periodbyday, ts_code=code['ts_code']) if not df1.empty: for c in self.col_list: self._appendcol(df=df1, by=c, r=code['ts_code']) self.df.to_csv(self.basic_csv, encoding='utf_8_sig')
def sub_findall_blackswan(self): msql = md.datamodule() ts_code_df = msql.getts_code() for i, code in ts_code_df.iterrows(): df1 = msql.pull_mysql(db='daily_basic_ts_code', ts_code=code['ts_code']) self.sub_findblackswan(df=df1) df = self.removedupdate(self.df) df.to_csv(self.blackswan_csv, index=False) shutil.copy(self.blackswan_csv, self.blackswan_csv1) return
def _getdata(self,ts_code,template): msql = md.datamodule() df = msql.pull_mysql(db = 'income',ts_code = ts_code) df_cash = msql.pull_mysql(db = 'cashflow',ts_code = ts_code) df_blc = msql.pull_mysql(db = 'balancesheet',ts_code = ts_code) if df.empty or df_cash.empty or df_blc.empty : return pd.DataFrame() for i in range(self.thisyear - 7,self.thisyear): end_date = str(i)+'1231' end_date1 = str(i+1)+'1231' y = 'y'+str(i - self.thisyear+1) #8 :净利润:income:n_income df1 = df[df['end_date'] == end_date] if not df1.empty: template.at[8,y] = df1.iloc[0]['n_income'] #9 +折旧和摊销cashflow,depr_fa_coga_dpba + amort_intang_assets + lt_amort_deferred_exp df1 = df_cash[df_cash['end_date'] == end_date] if not df1.empty: #可能会因为数据缺失导致异常 try: template.at[9,y] = df1.iloc[0]['depr_fa_coga_dpba'] + df1.iloc[0]['amort_intang_assets'] + df1.iloc[0]['lt_amort_deferred_exp'] #10 +支付的利息 cashflow:c_pay_dist_dpcp_int_exp template.at[10,y] = df1.iloc[0]['c_pay_dist_dpcp_int_exp'] #11 -资本性支出:cashflow:c_pay_acq_const_fiolta template.at[11,y] = df1.iloc[0]['c_pay_acq_const_fiolta']*(-1) #12 营运资金变动:balancesheet:y0(total_cur_assets - total_cur_liab)- y1(total_cur_assets - total_cur_liab) df1 = df_blc[df_blc['end_date'] == end_date] df2 = df_blc[df_blc['end_date'] == end_date1] if (not df1.empty) and (not df2.empty) : template.at[12,y] = df1.iloc[0]['total_cur_assets'] - df1.iloc[0]['total_cur_liab'] -(df2.iloc[0]['total_cur_assets'] - df2.iloc[0]['total_cur_liab']) except: print(ts_code+'数据缺失') return pd.DataFrame() #22永续增长率设为gdp template.at[22,'y-6'] = SUSTAINABLE_GROWTH_RATE #32,33,34带息负债,非经营负债,少数股东权益设为0 template.at[32,'y-6'] = 0 template.at[33,'y-6'] = 0 template.at[34,'y-6'] = 0 return template
def moniter(self): msql = md.datamodule() ts_code_df = msql.getts_code() df = pd.DataFrame() for i, code in ts_code_df.iterrows(): #取到的数据默认按照日期排序,最近的排在前面 df1 = msql.pull_mysql(db='daily_basic_ts_code', limit=self.blackday, ts_code=code['ts_code']) if not df1.empty: rate = self._isblackswan(df=df1) if rate > self.droprate: df1.at[0, 'droprate'] = rate df = pd.concat([df, df1.loc[0:0, ]], ignore_index=True) if not df.empty: str = util.dftostring(df) df = msql.joinnames(df=df) df.to_csv(self.moniter_csv, index=True) else: str = '没发现黑天鹅事件!' print(str) return
df1 = pd.concat([df1, df.loc[lenth - 1:lenth - 1, ]], ignore_index=True) return df1 def test_findoneblackswan(self, code='000001.SZ'): msql = md.datamodule() df1 = msql.pull_mysql(db='daily_basic_ts_code', limit=self.blackday, ts_code=code) self._isblackswan(df=df1) print(df1) if __name__ == '__main__': #d = blackswan() m = md.datamodule() m.updatalldb() "d.sub_findblackswan('20150101','20190312')" "d.sub_getlaterprice()" #d.moniter() #d.train() #d.test_findoneblackswan('000023.SZ') #d.test_findoneblackswan('600519.SH') #d.test_findoneblackswan('600887.SH') #df = pd.read_csv(d.blackswan_csv) #df = d.removedupdate(df) #df.to_csv(d.blackswan_csv1,index=False) #d.sub_getlaterprice() #util.sendmail(mailcontent = 'monitor finished')
def testlatestday(self): msql = md.datamodule() latestday = msql.getlatestday('daily_basic_ts_code') print(latestday)
colhigh = by + 'high' d = df.agg({by: ['min', 'max']}) self.df.at[r, collow] = d.loc['min'][by] self.df.at[r, colhigh] = d.loc['max'][by] def testlatestday(self): msql = md.datamodule() latestday = msql.getlatestday('daily_basic_ts_code') print(latestday) if __name__ == '__main__': b = basicvalue() msql = md.datamodule() #msql.push_daily_basic(start='19950101',end='20190324',firsttime = 1) #df = msql.pull_mysql(db = 'daily_basic_ts_code',limit = b.periodbyday,ts_code = '300750.SZ') #b._appendcol(df,by = 'close',r = '300750.SZ') #b.testlatestday() #msql._createdb('daily_basic') msql._push_mysql(database='daily_basic', start='20190310', end='20190330', firsttime=0) #msql.fix_db(db = 'balancesheet') #df.to_csv(b.recommand_basic) #b.builddf() #b.moniter() #b.recommand()