def notify(self, today): now = datetime.datetime.now() if now.hour > NOTIFY_HOUR: # 下午才会发通知 query_sql = '''select `基金代码`,`基金简称`,`实时价格`,`实时净值`,`溢价率`,`净值日期` from `{}` where `申购状态`='开放' and `申赎状态`='开放' and !(`实时价格`=1 and `涨跌幅`=0 and `成交额-万`=0) order by `溢价率` limit 10'''.format( today) cursor.execute(query_sql) ret = cursor.fetchall() html = '<div><table border="1">' \ '<tr><th>基金代码</th><th>基金简称</th><th>实时价格</th><th>实时净值</th><th>溢价率</th><th>净值日期</th></tr>' for i in ret: html += f'<tr><td>{i[0]}</td><td>{i[1]}</td><td>{i[2]}</td><td>{i[3]}</td><td>{i[4]}</td><td>{i[5]}</td></tr>' html += '</table></div>' query_sql = '''select `基金代码`,`基金简称`,`实时价格`,`实时净值`,`溢价率`,`净值日期` from `{}` where `申购状态`='开放' and `申赎状态`='开放' and `溢价率` is not null and !(`实时价格`=1 and `涨跌幅`=0 and `成交额-万`=0) order by `溢价率` desc limit 10'''.format( today) cursor.execute(query_sql) ret = cursor.fetchall() html += '<div><table border="1">' \ '<tr><th>基金代码</th><th>基金简称</th><th>实时价格</th><th>实时净值</th><th>溢价率</th><th>净值日期</th></tr>' for i in ret: html += f'<tr><td>{i[0]}</td><td>{i[1]}</td><td>{i[2]}</td><td>{i[3]}</td><td>{i[4]}</td><td>{i[5]}</td></tr>' html += '</table></div>' title = f'{today} 基金折溢价' try: send_from_aliyun(title, html, types='html') except Exception as e: logger.error(e) logger.info('发送失败') else: logger.info('发送成功')
def predict_price(): session = requests.Session() session.get(url=home_page, headers=home_headers) r = session.get(url=xueqiu_url, headers=headers) js_data = r.json() quote = js_data.get('data', {}).get('quote') quote['crawltime'] = datetime.datetime.now() doc.insert_one(quote) percent = quote.get('percent') jsl_qdii, est_val_dt = qdii_info() if jsl_qdii: predict_v = round((1 + percent * 0.95 * 0.01) * jsl_qdii, 3) logger.info(f'最新估值{predict_v}') d = {'日期': today, '估值': predict_v} client['db_stock']['huabaoyouqi_predict'].insert_one(d) title = f'华宝估值{predict_v} 净值日期{est_val_dt[5:]}' send_from_aliyun(title, '') else: notify('华宝油气获取估值失败')
def get_price(): session = requests.Session() session.get(url=home_page, headers=home_headers) r = session.get(url=url, headers=headers) js_data = r.json() quote = js_data.get('data', {}).get('quote') quote['crawltime'] = datetime.datetime.now() doc.insert_one(quote) percent = quote.get('percent') ret = qdii_info() if ret: predict_v = round((1+percent*0.95*0.01)*ret,3) logger.info(f'最新估值{predict_v}') d={'日期':today,'估值':predict_v} client['db_stock']['huabaoyouqi_predict'].insert_one(d) title=f'{today}华宝估值{predict_v}' send_from_aliyun(title,'') else: logger.error('获取估值失败')
def cb_info(): con = DB.get_engine('db_stock', 'qq') df = pd.read_sql('tb_bond_jisilu', con=con) df['grade'] = df['评级'].map(lambda x: map_rate(x)) df['可转债综合价格'] = df['可转债价格'] + df['溢价率'] * df['grade'] df = df.sort_values(by='可转债综合价格') df = df[df['强赎日期'].isnull()] df1 = df[['可转债代码', '可转债名称', '可转债综合价格', '可转债价格', '溢价率', '评级']].head(20) df1 = df1.reset_index(drop=True) df2=df1 df2['可转债名称']=df2['可转债名称'].map(lambda x:x.replace('转债','')) send_content = df2.to_html(index=False, border=1, justify='center') send_content=send_content.replace('class', 'cellspacing=\"0\" class') title = '{} 可转债综合价格前20名'.format(datetime.datetime.now().strftime('%Y-%m-%d')) conn2 = DB.get_engine('double_low_full', 'qq') df1.to_sql(f'double_low_{today}', con=conn2, if_exists='replace') try: send_from_aliyun(title, send_content, types='html') except Exception as e: logger.error('报错了') logger.error(e) else: logger.info('发送成功!')
def save_to_dataframe(self, data, index, choice, post_fix): engine = self.DB.get_engine('db_zdt', 'qq') data_len = len(data) filename = os.path.join( self.path, self.today + "_" + post_fix + ".xls") if choice == 1: for i in range(data_len): data[i][choice] = data[i][choice] df = pd.DataFrame(data, columns=index) # 今日涨停 if choice == 1: df['今天的日期'] = self.today df.to_excel(filename, encoding='gbk') try: df.to_sql(self.today + post_fix, engine, if_exists='fail') except Exception as e: self.logger.info(e) # 昨日涨停 if choice == 2: df = df.set_index('序号') formula = lambda x: round(x * 100, 3) df['最大涨幅'] = df['最大涨幅'].map(formula) df['最大跌幅'] = df['最大跌幅'].map(formula) df['今日开盘涨幅'] = df['今日开盘涨幅'].map(formula) df['昨日涨停强度'] = df['昨日涨停强度'].map(lambda x: round(x, 0)) df['今日涨停强度'] = df['今日涨停强度'].map(lambda x: round(x, 0)) try: df.to_sql(self.today + post_fix, engine, if_exists='fail') except Exception as e: self.notify(f'{self.__class__} 出错') self.logger.info(e) avg = round(df['今日涨幅'].mean(), 2) median = round(df['今日涨幅'].median(), 2) min_v = round(df['今日涨幅'].min(), 2) min_index = df['今日涨幅'].argmin() min_percent_name = df.iloc[min_index]['名称'] current = datetime.datetime.now().strftime('%Y-%m-%d') title = '昨涨停今天{}平均涨{}\n'.format(current, avg) content = '<p>昨天涨停今天<font color="red">{}</font></p>' \ '<p>平均涨幅 <font color="red">{}</font></p>' \ '<p>涨幅中位数 <font color="red">{}</font></p>' \ '<p>涨幅最小 <font color="red">{}</font></p>' \ '<p>涨幅最小股 <font color="red">{}</font></p>'.format(current, avg, median, min_v,min_percent_name) try: send_from_aliyun(title, content, types='html') except Exception as e: print(e)
def read_data_source(today): engine = DB.get_engine('db_jisilu','qq') try: df = pd.read_sql('tb_jsl_{}'.format(today),con=engine) except Exception as e: logger.error(e) send_from_aliyun('代码{}出错\n读取表tb_jsl_{}失败'.format('bond_daily',today),'{}'.format(today)) return None else: return df
def download(self, url, data, retry=5): for i in range(retry): try: r = requests.post(url, headers=self.headers, data=data) if not r.text or r.status_code != 200: continue else: return r except Exception as e: self.logger.info(e) send_from_aliyun(title='jisilu可转债', content='异常信息>>>>{}'.format(e)) continue return None
def save_to_dataframe(self, data, indexx, choice, post_fix): engine = self.DB.get_engine('db_zdt', 'qq') if not data: exit() data_len = len(data) if choice == 1: for i in range(data_len): data[i][choice] = data[i][choice] df = pd.DataFrame(data, columns=indexx) filename = os.path.join(self.path, self.today + "_" + post_fix + ".xls") # 今日涨停 if choice == 1: df['今天的日期'] = self.today df.to_excel(filename, encoding='gbk') try: df.to_sql(self.today + post_fix, engine, if_exists='fail') except Exception as e: logger.info(e) # 昨日涨停 if choice == 2: df = df.set_index('序号') df['最大涨幅'] = df['最大涨幅'].map(lambda x: round(x * 100, 3)) df['最大跌幅'] = df['最大跌幅'].map(lambda x: round(x * 100, 3)) df['今日开盘涨幅'] = df['今日开盘涨幅'].map(lambda x: round(x * 100, 3)) df['昨日涨停强度'] = df['昨日涨停强度'].map(lambda x: round(x, 0)) df['今日涨停强度'] = df['今日涨停强度'].map(lambda x: round(x, 0)) try: df.to_sql(self.today + post_fix, engine, if_exists='fail') except Exception as e: logger.info(e) avg = round(df['今日涨幅'].mean(), 2) median = round(df['今日涨幅'].median(), 2) min_v = round(df['今日涨幅'].min(), 2) current = datetime.datetime.now().strftime('%Y-%m-%d') title = '昨天涨停个股今天{}\n的平均涨幅{}\n'.format(current, avg) content = '昨天涨停个股今天{}\n的平均涨幅{}\n涨幅中位数{}\n涨幅最小{}\n'.format( current, avg, median, min_v) try: send_from_aliyun(title, content) except Exception as e: print(e)
def notice_me(self, today): now = datetime.datetime.now() if now.hour > NOTIFY_HOUR: # 下午才会发通知 title = f'{today} 基金折溢价' html = '' html = self.combine_html(html, TODAY) try: send_from_aliyun(title, html, types='html') except Exception as e: self.logger.error(e) self.logger.info('发送失败') else: self.logger.info('发送成功')
def main(send_mail=True): for item in doc.find({'last_resp_date': { '$gt': date }}, { 'html': 0, 'resp': 0, 'content': 0 }): del item['_id'] total_list.append(item) df = pd.DataFrame(total_list) df['createTime'] = pd.to_datetime(df['createTime']) df = df.set_index('createTime', drop=True) new_df = df.resample('W').count() show_data = new_df[['creator']].iloc[:last_time:-1] # print(show_data) # 最大值与 max_index = new_df['creator'].idxmax().to_pydatetime().strftime('%Y-%m-%d') max_v = new_df['creator'].max() current = datetime.datetime.now().strftime('%Y-%m-%d') title = f'jsl一周发帖数量分析 {current}' percentage = np.round( (show_data['creator'].values[:-1] - show_data['creator'].values[1:]) / show_data['creator'].values[1:] * 100, 0) content = '| 日期 | 贴数 | 环比 |\n' # print(percentage) percentage = np.append(percentage, np.nan) start_index = 0 for index, item in show_data.iterrows(): # print(index,item['creator']) py_date = index.to_pydatetime().strftime('%Y-%m-%d') count = item['creator'] content += f'| {py_date} | {count} | {percentage[start_index]}% |\n' start_index += 1 content += f'最大值发生在 {max_index},贴数为 {max_v}\n' logger.info(title) logger.info(content) if send_mail: try: send_from_aliyun(title, content) except Exception as e: logger.error(e)
def save_list_mongo(source_list): current = datetime.datetime.now() title = current.strftime('%Y-%m-%d') + '可转债周涨幅的前10和后10' top10 = source_list[:10] last10 = source_list[-10:] top_str = ['{} : {}'.format(i[0], i[1]) for i in top10] last_str = ['{} : {}'.format(i[0], i[1]) for i in last10[::-1]] top_str = '\n'.join(top_str) last_str = '\n'.join(last_str) content = '跌幅前10:::\n' + last_str + '\n涨幅前10:::\n' + top_str try: send_from_aliyun(title, content) except Exception as e: logger.error(e) d = dict(source_list) d['updated'] = current try: db['db_parker']['kzz_weekly_raise'].insert(d) except Exception as e: logger.error(e)
def main(): result = db['db_parker']['jsl'].find({},{'html':0}).sort('_id',pymongo.DESCENDING).limit(MAX) filter_result = [] for i in result: createTime = i['createTime'] createTime = datetime.datetime.strptime(createTime,'%Y-%m-%d %H:%M') if createTime >= last_day : title = i['title'] creator = i['creator'] resp_count = len(i['resp']) url = i['url'] d = {'title':title,'url':url,'resp_count':resp_count} filter_result.append(d) hot_list = list(sorted(filter_result,key=lambda x:x['resp_count'],reverse=True))[:10] title,html = format_mail(hot_list) try: send_from_aliyun(title,html,types='html') except Exception as e: # logger.error(e) print(e)
engine_daily = DB.get_engine('db_daily', 'qq') engine = DB.get_engine('db_stock', 'qq') jsl_df = pd.read_sql('tb_bond_jisilu', con=engine) basic_df = pd.read_sql('tb_basic_info', con=engine, index_col='index') price_df = pd.read_sql(today, con=engine_daily) INFO = _json_data['mongo']['arm'] host = INFO['host'] port = INFO['port'] user = INFO['user'] password = INFO['password'] connect_uri = f'mongodb://{user}:{password}@{host}:{port}' db = pymongo.MongoClient(connect_uri) except Exception as e: logger.error(e) send_from_aliyun('读取数据库失败', '') exit() # 市值选择 def market_share(zg_df, price_df): if len(zg_df) == 0: return zg_df for index in zg_df.index: price = price_df[price_df['code'] == zg_df.loc[index] ['code']]['trade'].values[0] ltgb = zg_df.loc[index]['outstanding'] total_gb = zg_df.loc[index]['totals'] lt = round(price * ltgb, 1) # 流通市值 zg_df.loc[index, 'outstanding_shizhi'] = lt
def find_zz_zg_diff(): current = datetime.date.today().strftime('%Y-%m-%d') # current ='2019-10-18' if ts.is_holiday(current): logger.info('假期') return con = DB.get_mysql_conn('db_stock', 'qq') cursor = con.cursor() zz_than_zg = 'select count(*) from tb_bond_jisilu WHERE `正股涨跌幅`>=`可转债涨幅` and `正股涨跌幅`<=0' minus_count_cmd = 'select count(*) from tb_bond_jisilu where `可转债涨幅`<0' plug_count_cmd = 'select count(*) from tb_bond_jisilu where `可转债涨幅`>=0' cursor.execute(zz_than_zg) zz_than_zg_count = cursor.fetchone()[0] # zz_than_zg_count_=zz_than_zg_count[0] cursor.execute(minus_count_cmd) minus_count = cursor.fetchone()[0] cursor.execute(plug_count_cmd) plug_count = cursor.fetchone()[0] try: search_sql = 'select `溢价率` from `tb_bond_jisilu`' cursor.execute(search_sql) except Exception as e: logger.error(e) else: content = cursor.fetchall() data = [] for item in content: data.append(item[0]) np_data = np.array(data) max_value = np.round(np_data.max(), 2) min_value = np.round(np_data.min(), 2) mean = np.round(np_data.mean(), 2) median = np.round(np.median(np_data), 2) count = len(np_data) t_value = (current, float(mean), float(max_value), float(min_value), float(median), count) update_sql = 'insert into tb_bond_avg_yjl (Date,溢价率均值,溢价率最大值,溢价率最小值,溢价率中位数,转债数目) values (%s,%s,%s,%s,%s,%s)' try: cursor.execute(update_sql, t_value) con.commit() except Exception as e: logger.error(e) con.rollback() else: logger.info('update') cal_query = 'select `可转债涨幅`,`可转债名称` from tb_bond_jisilu' cursor.execute(cal_query) cal_result = cursor.fetchall() # cal_result_list=[] result_dict = {} for i in cal_result: result_dict[i[1]] = i[0] # cal_result_list.append(i[0]) sort_result = list( sorted(result_dict.items(), key=lambda x: x[1], reverse=False)) fall_name = sort_result[0][0] raise_name = sort_result[-1][0] result_list = list(result_dict.values()) cal_result_np = np.array(result_list) large_than_zero = len(cal_result_np[cal_result_np >= 0]) # small_than_zero = len(cal_result_np[cal_result_np<0]) total_len = len(cal_result_np) raise_ratio = round(large_than_zero / total_len * 100, 2) max_v = cal_result_np.max() min_v = cal_result_np.min() mean = round(cal_result_np.mean(), 2) median = round(np.median(cal_result_np), 2) ripple_ratio = round(cal_result_np.var(), 2) title = '{}转债跌大于正股数:{}'.format(current, zz_than_zg_count) content=f'<p>转债上涨比例:<font color="red">{raise_ratio}</font></p>' \ f'<p>转债跌>正股数: <font color="red">{zz_than_zg_count}</font></p>' \ f'可转债涨幅>=0: <font color="red">{plug_count}</font></p>' \ f'可转债涨幅<0: <font color="red">{minus_count}</font></p>' \ f'涨幅最大值:<font color="red">{max_v}</font> 属于<font color="red">{raise_name}</font></p>' \ f'涨幅最小值:<font color="red">{min_v}</font> 属于<font color="red">{fall_name}</font></p>' \ f'涨幅均值:<font color="red">{mean}</font></p>' \ f'涨幅中位数:<font color="red">{median}</font></p>' \ f'涨幅波动的方差:<font color="red">{ripple_ratio}</font></p>' try: send_from_aliyun(title, content, types='html') except Exception as e: logger.error(e) else: logger.info('发送成功') logger.info(content) # 写入数据库 insert_sql = 'insert into tb_bond_analysis (date,转债跌大于正股数量,可转债涨幅大于0,可转债涨幅小于0) values (%s,%s,%s,%s)' try: cursor.execute(insert_sql, (current, zz_than_zg_count, plug_count, minus_count)) con.commit() except Exception as e: logger.error(e) con.rollback() else: logger.info('入库成功')
def data_parse(self, bond_list, adjust_no_use): cell_list = [] for item in bond_list: cell_list.append(pd.Series(item.get('cell'))) df = pd.DataFrame(cell_list) if adjust_no_use: # 类型转换 部分含有% df['premium_rt'] = df['premium_rt'].map( lambda x: float(x.replace('%', ''))) df['price'] = df['price'].astype('float64') df['convert_price'] = df['convert_price'].astype('float64') df['premium_rt'] = df['premium_rt'].astype('float64') df['redeem_price'] = df['redeem_price'].astype('float64') def convert_float(x): try: ret_float = float(x) except: ret_float = None return ret_float def convert_percent(x): try: ret = float(x) * 100 except: ret = None return ret def remove_percent(x): try: ret = x.replace(r'%', '') ret = float(ret) except Exception as e: ret = None return ret df['put_convert_price'] = df['put_convert_price'].map( convert_float) df['sprice'] = df['sprice'].map(convert_float) df['ration'] = df['ration'].map(convert_percent) df['volume'] = df['volume'].map(convert_float) df['convert_amt_ratio'] = df['convert_amt_ratio'].map( remove_percent) df['ration_rt'] = df['ration_rt'].map(convert_float) df['increase_rt'] = df['increase_rt'].map(remove_percent) df['sincrease_rt'] = df['sincrease_rt'].map(remove_percent) rename_columns = { 'bond_id': '可转债代码', 'bond_nm': '可转债名称', 'price': '可转债价格', 'stock_nm': '正股名称', 'stock_cd': '正股代码', 'sprice': '正股现价', 'sincrease_rt': '正股涨跌幅', 'convert_price': '最新转股价', 'premium_rt': '溢价率', 'increase_rt': '可转债涨幅', 'put_convert_price': '回售触发价', 'convert_dt': '转股起始日', 'short_maturity_dt': '到期时间', 'volume': '成交额(万元)', 'redeem_price': '强赎价格', 'year_left': '剩余时间', 'next_put_dt': '回售起始日', 'rating_cd': '评级', # 'issue_dt': '发行时间', # 'redeem_tc': '强制赎回条款', # 'adjust_tc': '下修条件', 'adjust_tip': '下修提示', # 'put_tc': '回售', 'adj_cnt': '下调次数', # 'ration':'已转股比例' 'convert_amt_ratio': '转债剩余占总市值比', 'curr_iss_amt': '剩余规模', 'orig_iss_amt': '发行规模', 'ration_rt': '股东配售率', 'redeem_flag': '发出强赎公告', 'redeem_dt': '强赎日期', } df = df.rename(columns=rename_columns) df = df[list(rename_columns.values())] df['更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M') df = df.set_index('可转债代码', drop=True) try: df.to_sql('tb_jsl_{}'.format(self.date), self.engine, if_exists='replace', dtype={'可转债代码': VARCHAR(10)}) engine2 = DB.get_engine('db_stock', self.remote) df.to_sql('tb_bond_jisilu'.format(self.date), engine2, if_exists='replace', dtype={'可转债代码': VARCHAR(10)}) except Exception as e: self.logger.info(e) send_from_aliyun(title='jisilu可转债', content='写入数据库出错')
def current_data(self, adjust_no_use=True): post_data = { 'btype': 'C', 'listed': 'Y', 'rp': '50', 'is_search': 'N', } js = self.download(self.url, data=post_data) if not js: return None ret = js.json() bond_list = ret.get('rows', {}) cell_list = [] for item in bond_list: cell_list.append(pd.Series(item.get('cell'))) df = pd.DataFrame(cell_list) # 下面的数据暂时不需要 if adjust_no_use: # del df['active_fl'] # del df['adq_rating'] # del df['list_dt'] # del df['left_put_year'] # del df['owned'] # del df['put_dt'] # del df['real_force_redeem_price'] # del df['redeem_dt'] # del df['apply_cd'] # del df['force_redeem'] # del df['stock_id'] # del df['full_price'] # del df['pre_bond_id'] # del df['ytm_rt'] # del df['ytm_rt_tax'] # del df['repo_cd'] # del df['last_time'] # del df['pinyin'] # del df['put_real_days'] # del df['price_tips'] # del df['btype'] # del df['repo_valid'] # del df['repo_valid_to'] # del df['repo_valid_from'] # del df['repo_discount_rt'] # del df['adjust_tc'] # del df['cpn_desc'] # del df['market'] # del df['stock_net_value'] # 类型转换 部分含有% df['premium_rt'] = df['premium_rt'].map(lambda x: float(x.replace('%', ''))) df['price'] = df['price'].astype('float64') df['convert_price'] = df['convert_price'].astype('float64') df['premium_rt'] = df['premium_rt'].astype('float64') df['redeem_price'] = df['redeem_price'].astype('float64') def convert_float(x): try: ret_float = float(x) except: ret_float = None return ret_float def convert_percent(x): try: ret = float(x) * 100 except: ret = None return ret def remove_percent(x): try: ret = x.replace(r'%', '') ret = float(ret) except Exception as e: ret = None return ret df['put_convert_price'] = df['put_convert_price'].map(convert_float) df['sprice'] = df['sprice'].map(convert_float) df['ration'] = df['ration'].map(convert_percent) df['volume'] = df['volume'].map(convert_float) df['convert_amt_ratio'] = df['convert_amt_ratio'].map(remove_percent) df['ration_rt'] = df['ration_rt'].map(convert_float) df['increase_rt']=df['increase_rt'].map(remove_percent) df['sincrease_rt']=df['sincrease_rt'].map(remove_percent) rename_columns = {'bond_id': '可转债代码', 'bond_nm': '可转债名称', 'price': '可转债价格', 'stock_nm': '正股名称', 'stock_cd': '正股代码', 'sprice': '正股现价', 'sincrease_rt': '正股涨跌幅', 'convert_price': '最新转股价', 'premium_rt': '溢价率', 'increase_rt': '可转债涨幅', 'put_convert_price': '回售触发价', 'convert_dt': '转股起始日', 'short_maturity_dt': '到期时间', 'volume': '成交额(万元)', 'redeem_price': '强赎价格', 'year_left': '剩余时间', 'next_put_dt': '回售起始日', 'rating_cd': '评级', # 'issue_dt': '发行时间', # 'redeem_tc': '强制赎回条款', # 'adjust_tc': '下修条件', 'adjust_tip': '下修提示', # 'put_tc': '回售', 'adj_cnt': '下调次数', # 'ration':'已转股比例' 'convert_amt_ratio': '转债剩余占总市值比', 'curr_iss_amt': '剩余规模', 'orig_iss_amt': '发行规模', 'ration_rt': '股东配售率', } df = df.rename(columns=rename_columns) df = df[list(rename_columns.values())] df['更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M') # dfx = df[['可转债代码', '可转债名称', '可转债涨幅', '可转债价格', '正股名称', '正股代码', # '正股涨跌幅', '正股现价', '最新转股价', '溢价率', '评级', # '转股起始日', '回售起始日', '回售触发价', '剩余时间', # '更新日期']] df = df.set_index('可转债代码', drop=True) try: df.to_sql('tb_jsl_{}'.format(self.date), self.engine, if_exists='replace', dtype={'可转债代码': VARCHAR(10)}) engine2=DB.get_engine('db_stock',self.remote) df.to_sql('tb_bond_jisilu'.format(self.date), engine2, if_exists='replace', dtype={'可转债代码': VARCHAR(10)}) except Exception as e: self.logger.info(e) send_from_aliyun(title='jisilu可转债',content='写入数据库出错')