def cb_info():
con = get_engine('db_stock', 'local')
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['强赎标志'] != 'Y']
df1 = df[['可转债代码', '可转债名称', '可转债综合价格', '可转债价格', '溢价率', '评级']].head(20)
df1 = df1.reset_index(drop=True)
send_content = df1.to_html()
# send_content=send_content+'\n\n默认每周一开盘前发送一次，如果有其他需求请回复。\n'
# send_content = send_content
title = '{} 可转债综合价格前10名'.format(
datetime.datetime.now().strftime('%Y-%m-%d'))
conn2 = get_engine('double_low_full')
df1.to_sql(f'double_low_{today}', con=conn2, if_exists='replace')
try:
send_aliyun(title, send_content, QQ_MAIL, types='html')
except Exception as e:
logger.error('报错了')
logger.error(e)
else:
logger.info('发送成功！')
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_aliyun(title, '', QQ_MAIL)
else:
logger.error('获取估值失败')
def analysis(self, date=None, head=300):
if date is None:
date = datetime.date.today().strftime('%Y-%m-%d')
# date='2019-12-11'
kzz_big_deal_count = []
for code in self.jisilu_df['可转债代码'].values:
kzz_big_deal_count.append(self.get_volume_distribition(code, date))
kzz_big_deal_order = list(
sorted(kzz_big_deal_count, key=lambda x: x[1], reverse=True))
# print(kzz_big_deal_order)
send_content = []
for item in kzz_big_deal_order[:head]:
logger.info('{} ::: 大单出现次数 {}'.format(
self.code_name_dict.get(item[0]), item[1]))
send_content.append('{} ::: 大单出现次数 {}'.format(
self.code_name_dict.get(item[0]), item[1]))
# 入库的
big_deal_doc = self.db['db_stock']['big_deal_logger']
for item in kzz_big_deal_order:
d = {
'Date': date,
'name': self.code_name_dict.get(item[0]),
'times': int(item[1])
}
try:
big_deal_doc.insert_one(d) # 写入mongo
except Exception as e:
logger.error(e)
logger.error(d)
# send_content.append('{} ::: 大单出现次数 {}'.format(self.code_name_dict.get(item[0]),item[1]))
content = '\n'.join(send_content)
title = '{}-大单监控'.format(date)
try:
send_aliyun(title, content, QQ_MAIL)
except Exception as e:
logger.error(e)
else:
logger.info('发送成功')
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_aliyun(title, content,QQ_MAIL)
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)
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[:-5:-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)
try:
send_aliyun(title, content, TO_MAIL_=QQ_MAIL)
except Exception as e:
logger.error(e)
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=get_mysql_conn('db_stock','local')
cursor=con.cursor()
query_cmd = '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(query_cmd)
get_count = cursor.fetchone()
num=get_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=[]
for i in cal_result:
cal_result_list.append(i[0])
cal_result_np=np.array(cal_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,num)
content=f'转债上涨比例：{raise_ratio}\n转债跌>正股数：{num}\n可转债涨幅>=0----{plug_count}\n可转债涨幅<0----{minus_count}\n涨幅最大值：{max_v}\n涨幅最小值：{min_v}\n涨幅均值：{mean}\n涨幅中位数：{median}\n涨幅波动的方差：{ripple_ratio}'
try:
send_aliyun(title,content,QQ_MAIL)
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,num,plug_count,minus_count))
con.commit()
except Exception as e:
logger.error(e)
con.rollback()
else:
logger.info('入库成功')
logger = llogger('log/filter_bond.log')
today = datetime.datetime.now().strftime('%Y-%m-%d')
# today='2020-03-27'
try:
engine_daily = get_engine('db_daily', 'local')
engine = get_engine('db_stock', 'local')
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)
db = pymongo.MongoClient(config.mongodb_host, config.mongodb_port)
except Exception as e:
logger.error(e)
send_aliyun('读取数据库失败', '', QQ_MAIL)
exit()
# 市值选择
def market_share(zg_df, price_df):
if len(zg_df) == 0:
return zg_df
for i in zg_df.index:
p = price_df[price_df['code'] == zg_df.loc[i]
['code']]['trade'].values[0]
ltgb = zg_df.loc[i]['outstanding']
total_gb = zg_df.loc[i]['totals']
lt = round(p * ltgb, 1) # 流通市值
zg_df.loc[i, 'outstanding_shizhi'] = lt