def caculation(self):
df_t = pd.read_sql(self.today, con=self.engine1)
# df_t = ts.get_today_all()
for i in self.df['代码'].values:
self.df.loc[self.df['代码'] == i, '当前日期'] = self.today
pchange = df_t.loc[df_t['code'] == i, 'changepercent'].values[0]
self.df.loc[self.df['代码'] == i, '今日涨幅'] = pchange
trade = df_t[df_t['code'] == i]['trade'].values[0]
self.df.loc[self.df['代码'] == i, '当前价格'] = trade
current_profit = (trade - self.df[self.df['代码'] == i]['买入价格'].values[0]) / \
self.df[self.df['代码'] == i]['买入价格'].values[0]
self.df.loc[self.df['代码'] == i,
'目前盈亏'] = round(current_profit * 100, 2)
self.df.to_excel(self.name, encoding='utf-8', index=None)
self.df.to_sql('tb_simulation', self.engine, if_exists='replace')
# ali_engine = get_engine('',False)
# self.df.to_sql('tb_simulation',ali_engine,if_exists='replace')
del self.df['买入理由']
df_str = self.df.to_html()
# print(df_str)
sender_139('模拟盘 {}'.format(self.today), df_str, types='html')
def caculation(self):
df_t = pd.read_sql(self.today,con=self.engine1)
# df_t = ts.get_today_all()
for i in self.df['代码'].values:
self.df.ix[self.df['代码'] == i, '当前日期'] = self.today
pchange = df_t.ix[df_t['code'] == i, 'changepercent'].values[0]
self.df.ix[self.df['代码'] == i, '今日涨幅'] = pchange
trade = df_t[df_t['code'] == i]['trade'].values[0]
self.df.ix[self.df['代码'] == i, '当前价格'] = trade
current_profit = (trade - self.df[self.df['代码'] == i]['买入价格'].values[0]) / \
self.df[self.df['代码'] == i]['买入价格'].values[0]
self.df.ix[self.df['代码'] == i, '目前盈亏'] = round(current_profit * 100, 2)
self.df.to_excel(self.name, encoding='utf-8',index=None)
self.df.to_sql('tb_simulation',self.engine,if_exists='replace')
# ali_engine = get_engine('',False)
# self.df.to_sql('tb_simulation',ali_engine,if_exists='replace')
del self.df['买入理由']
df_str = self.df.to_html()
# print(df_str)
sender_139('模拟盘 {}'.format(self.today),df_str,types='html')
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:
logger.info(e)
sender_139('jisilu可转债', '异常信息>>>>{}'.format(e))
continue
return None
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:
logger.info(e)
sender_139('jisilu可转债', '异常信息>>>>{}'.format(e))
continue
return None
def save_to_dataframe(self, data, indexx, choice, post_fix):
engine = setting.get_engine('db_zdt')
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:
sender_139(title, content)
except Exception as e:
print(e)
def getdata(self, url, headers, retry=5):
for i in range(retry):
try:
resp = requests.get(url=url, headers=headers)
content = resp.text
md_check = re.findall('summary|lasttradedate', content)
if content and len(md_check) > 0:
return content
else:
time.sleep(60)
logger.info('failed to get content, retry: {}'.format(i))
continue
except Exception as e:
sender_139('获取涨跌停数据出错', '{}'.format(e))
logger.error(e)
time.sleep(60)
continue
return None
def save_to_dataframe(self, data, indexx, choice, post_fix):
engine = setting.get_engine('db_zdt')
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)
current = datetime.datetime.now().strftime('%Y-%m-%d')
title = '昨天涨停个股今天{}\n的平均涨幅{}\n'.format(current, avg)
try:
sender_139(title, title)
except Exception as e:
print(e)
def convert_json(self, content):
p = re.compile(r'"Data":(.*)};', re.S)
if len(content) <= 0:
logger.info('Content\'s length is 0')
exit(0)
result = p.findall(content)
if result:
try:
# print(result)
t1 = result[0]
t2=re.sub('[\\r\\n]', '', t1)
t2=re.sub(',,',',0,0',t2)
t2 = re.sub('Infinity','-1',t2)
t2 = list(eval(t2))
return t2
except Exception as e:
sender_139('获取涨跌停数据出错','e{}'.format(e))
logger.info(e)
return None
else:
return None
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:
sender_139(title, content)
except Exception as e:
print(e)
d = dict(source_list)
d['updated'] = current
try:
db['db_parker']['kzz_weekly_raise'].insert(d)
except Exception as e:
print(e)
def analysis(self,date=None,head=300):
if date is None:
date=datetime.date.today().strftime('%Y-%m-%d')
# date='2019-05-08'
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]:
self.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(d)
except Exception as e:
self.logger.error(e)
self.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:
sender_139(title,content)
except Exception as e:
self.logger.error(e)
else:
self.logger.info('发送成功')
def caculation(self):
df_t = ts.get_today_all()
for i in self.df[u'代码'].values:
self.df.ix[self.df[u'代码'] == i, u'当前日期'] = self.today
pchange = df_t.ix[df_t['code'] == i, 'changepercent'].values[0]
self.df.ix[self.df[u'代码'] == i, u'今日涨幅'] = pchange
trade = df_t[df_t['code'] == i]['trade'].values[0]
self.df.ix[self.df[u'代码'] == i, u'当前价格'] = trade
current_profit = (trade - self.df[self.df[u'代码'] == i][u'买入价格'].values[0]) / \
self.df[self.df[u'代码'] == i][u'买入价格'].values[0]
self.df.ix[self.df[u'代码'] == i,
u'目前盈亏'] = round(current_profit * 100, 2)
self.df.to_excel(self.name, encoding='utf-8')
self.df.to_sql('tb_simulation', self.engine, if_exists='replace')
ali_engine = get_engine('', False)
self.df.to_sql('tb_simulation', ali_engine, if_exists='replace')
df_str = self.df.to_html()
sender_139('模拟盘 {}'.format(self.today), df_str, types='html')
def pledge_weekly_update():
url = 'http://www.chinaclear.cn/cms-rank/downloadFile?queryDate={}&type=proportion'
crawl_time = datetime.datetime.now().strftime('%Y.%m.%d')
current = datetime.datetime.strptime('{}'.format(crawl_time), '%Y.%m.%d')
now = current + datetime.timedelta(days=-3)
now_str = now.strftime('%Y.%m.%d')
logger.info('当前日期 >>>> {}'.format(now_str))
filename = 'gpzyhgmx_' + now_str + '.xls'
try:
r = requests.get(url.format(now_str), headers=headers)
except Exception as e:
sender_139('股权质押爬取异常 {}', '异常信息{}'.format(now_str, e))
return
# 保存到本地
full_path = os.path.join('D:\OneDrive\Stock_Data\pledge', filename)
with open(full_path, 'wb') as f:
f.write(r.content)
# 写入数据集
updat_db(full_path)
def weekly_update():
# 第一次循环获取所有的数据
crawl_time = datetime.datetime.now().strftime('%Y-%m-%d')
current = datetime.datetime.strptime('{}'.format(crawl_time), '%Y-%m-%d')
columns = [
'新增投资者数量', '新增投资者数量-自然人', '新增投资者数量-非自然人', '期末投资者数量', '期末投资者数量-自然人',
'已开立A股账户投资者-自然人', '已开立B股账户投资者-自然人', '期末投资者数量-非自然人', '已开立A股账户投资者-非自然人',
'已开立B股账户投资者-非自然人', '期末持仓投资者数量', '期末持仓投资者数量-A股', '期末持仓投资者数量-B股',
'期间参与交易的投资者数量', '期间参与交易的投资者数量-A股', '期间参与交易的投资者数量-B股'
]
doc = db['db_parker']['investor_trend_2015_05_after']
# 2017.02.10 后面的只有10项
t = -4
now = current + datetime.timedelta(days=t)
now_str = now.strftime('%Y.%m.%d')
if now_str == '2015.05.01':
exit()
logger.info('当前日期 >>>> {}'.format(now_str))
data = {
'dateType': '',
'dateStr': now_str,
'channelIdStr': value,
}
try:
s = session.post(url=home_page, headers=headers, data=data)
except Exception as e:
logger.error('请求出错{}'.format(e))
sender_139('<开户数> 爬取异常 {}', '异常信息{}'.format(crawl_time, e))
return
if '没有找到相关信息' in s.text:
logger.info('没有找到相关信息')
return
root = etree.HTML(s.text)
content = root.xpath('string(.)')
content = re.search('新增投资者数(.*)', content, re.S).group(1)
# 共有10个数字，分 别对应网站上的
num_list = re.findall('\s*([\d*\.*,*\-*]+)\s*\d*、*', content)
# num_list = re.findall('>([\d+\.,]+\S?)<', s.text)
logger.info('列表数据 {}'.format(num_list))
l = len(num_list)
if l != 10:
logger.warning('length not equal 10')
logger.warning('实际长度为{}'.format(l))
d = {}
d['publish_date'] = now_str
for idx, name in enumerate(columns):
# 避免17年2月后长度只有10的时候越界
if l == 10 and idx == 10:
break
try:
logger.info('{}\t{}'.format(name, num_list[idx]))
except Exception as e:
logger.error('index出错')
continue
try:
item = re.sub(',', '', num_list[idx])
if len(item.split('.')[1]) > 2:
item = item[:len(item) - 1]
except Exception as e:
d[name] = 0
else:
d[name] = item
d['crawl_time'] = crawl_time
content = []
for k, v in d.items():
content.append('{}:{}'.format(k, v))
# try:
# sender_139('{} 新增投资者信息'.format(crawl_time), '\n'.format(content))
# except Exception as e:
# logger.error('发送邮件出错')
# 写入mongo
doc.insert(d)
del d['_id']
d_list = [d]
# df = pd.DataFrame.from_dict(d,orient='index').T
df = pd.DataFrame(d)
# df['publish_date']
try:
df.to_sql('tb_chinaclear', con=engine, if_exists='append', index=None)
except Exception as e:
sender_139('<开户数> 入库异常', '异常信息{} -{}'.format(e, crawl_time))
logger.error('异常 >>>{}'.format(e))
def find_zz_zg_diff():
current = datetime.date.today().strftime('%Y-%m-%d')
if ts.is_holiday(current):
logger.info('假期')
return
# engine=get_engine('db_stock','local')
# df = pd.read_sql('tb_bond_jisilu',con=engine)
# df[(df['正股涨跌幅']<=0) & (df['正股涨跌幅']>=df['可转债涨幅'])]
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)
max_v = cal_result_np.max()
min_v = cal_result_np.min()
mean = round(cal_result_np.mean(), 2)
median = np.median(cal_result_np)
title = '{}转债跌》正股数：{}'.format(current, num)
content = f'转债跌>正股数：{num}\n可转债涨幅>=0----{plug_count}\n可转债涨幅<0----{minus_count}\n涨幅最大值：{max_v}\n涨幅最小值：{min_v}\n涨幅均值：{mean}\n涨幅中位数：{median}'
try:
sender_139(title, content)
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('入库成功')
