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()
