def draw_view(url):
try:
html = ''
cur = get_cursor("""
SELECT industry, sub_industry
,round(sum(cb_price2_id + cb_premium_id*100)/count(sub_industry),2) as value
, sum(cb_price2_id + cb_premium_id*100) as sum
, count(*) as count
from changed_bond group by sub_industry
order by industry, value,sub_industry
""")
table = from_db_cursor(cur)
html += generate_chart_html(table, '可转债行业双低值分布',
'/view_industry_double_low_detail.html')
return '可转债行业双低值分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def _get_mid_data():
cur = get_cursor("""
SELECT mid_price, mid_premium, avg_premium
from (
SELECT round(AVG(cb_price2_id), 2) as mid_price, row_number() OVER () as rn
FROM (SELECT cb_price2_id
FROM changed_bond
ORDER BY cb_price2_id
LIMIT 2 - (SELECT COUNT(*) FROM changed_bond) % 2 -- odd 1, even 2
OFFSET (SELECT (COUNT(*) - 1) / 2
FROM changed_bond))) a
left join(
SELECT round(AVG(cb_premium_id) * 100, 2) as mid_premium, row_number() OVER () as rn
FROM (SELECT cb_premium_id
FROM changed_bond
ORDER BY cb_premium_id
LIMIT 2 - (SELECT COUNT(*) FROM changed_bond) % 2 -- odd 1, even 2
OFFSET (SELECT (COUNT(*) - 1) / 2
FROM changed_bond))) b
on a.rn = b.rn,
(select 1 as rn, round(avg(cb_premium_id)*100,2) as avg_premium from changed_bond) c
where a.rn = c.rn
""")
global MID_X, MID_Y
row = cur.fetchone()
MID_X = row[0]
MID_Y = row[1]
avg_premium = row[2]
print('init mid data is successful.MID_X:' + str(MID_X) + ', MID_Y:' +
str(MID_Y))
return row
def update_cb_index():
mid_price, temp, avg_premium = calc_mid_data_with_avg_premium()
# 检查当天记录已经存在, 存在则更新
cur = get_cursor(
"select count(*) from cb_index_history where strftime('%Y-%m-%d', date) = strftime('%Y-%m-%d', date())"
)
one = cur.fetchone()
if one[0] == 0:
count = execute_sql_with_rowcount(
"""insert into cb_index_history(mid_price, avg_premium) values(:mid_price, :avg_premium)""",
{
'mid_price': mid_price,
'avg_premium': avg_premium
})
if count == 1:
print('insert today\'s mid_price is successful.')
else:
count = execute_sql_with_rowcount(
"""update cb_index_history set mid_price=:mid_price, avg_premium=:avg_premium where strftime('%Y-%m-%d', date) = strftime('%Y-%m-%d', date())""",
{
'mid_price': mid_price,
'avg_premium': avg_premium
})
if count == 1:
print('update today\'s mid_price is successful.')
def draw_view():
try:
html = ''
nav_html_list = {}
# 用来画收益曲线图的数据
cur = get_cursor("""
SELECT
date, mid_price, avg_premium
from cb_index_history
order by date desc --limit 2
""")
rows = list(cur)
dict_rows = []
for row in rows:
dict_row = db_utils.get_dict_row(cur, row)
dict_rows.append(dict_row)
html = """
<center>
""" + utils.echarts_html_utils.generate_line_html2(dict_rows) + """<br/>
""" + utils.echarts_html_utils.generate_line_html3(dict_rows) + """
</center>
"""
return '可转债整体估值', '<li><a href="/">Home</a></li>', html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(url):
try:
html = ''
cur = get_cursor("""
SELECT industry, sub_industry
--, industry|| '[' || sub_industry || ']' as 名称
, sub_industry as 名称
,round(sum(cb_premium_id)/count(sub_industry)*100,2) as avg_premium
,round(sum(cb_premium_id)/count(sub_industry)*100,2) || '%' as 溢价率
,round(sum(cb_price2_id)/count(sub_industry),2) as 转债价格
,'0' as bond_code
, sum(cb_premium_id*100) as sum
, count(*) as count
from changed_bond group by sub_industry
order by industry, avg_premium,sub_industry
""")
table = from_db_cursor(cur)
html += '<br/><br/>' + generate_scatter_html_with_one_table(
table, title='可转债行业分布', click_maker=click_maker)
return '可转债行业价格&溢价率分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def get_idx_data():
cur = get_cursor("select date, idx_data from cb_index_history")
idx_rows = cur.fetchall()
idx_data = {}
for idx_row in idx_rows:
ss = idx_row[0].split()
idx_data[datetime.datetime.strptime(ss[0], '%Y-%m-%d')] = idx_row[1]
return idx_data
def calc_yield():
cur = get_cursor("""
SELECT round(sum(c.cb_price2_id*h.hold_amount+h.sum_sell -h.sum_buy), 2) as 累积收益,
round(sum(c.cb_price2_id*h.hold_amount+h.sum_sell -h.sum_buy)/sum(h.sum_buy) * 100, 2) as 累积收益率
from (select hold_amount, sum_buy, hold_owner, bond_code, sum_sell from hold_bond union select hold_amount, sum_buy, hold_owner, bond_code, sum_sell from hold_bond_history) h , changed_bond c
where h.bond_code = c.bond_code and hold_owner='me'
""")
row = cur.fetchone()
return row[0], row[1]
def do_update_stock_theme(driver, task_name):
# 遍历可转债列表
task = None
try:
# 查询可转债
bond_cursor = get_cursor(
"""SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond"""
)
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
i = 0
for bond_row in rows:
bond_code = bond_row[0]
stock_code = bond_row[2]
stock_name = bond_row[3]
process_task_when_normal(task)
theme = fetch_stock_theme(driver, stock_code)
rowcount = execute_sql_with_rowcount(
"""update changed_bond_extend set theme = :theme where bond_code = :bond_code""",
{
'theme': theme,
'bond_code': bond_code
})
if rowcount == 0:
print("not update stock:" + stock_name +
' in changed_bond_extend')
else:
print("update " + stock_name + " is successful. count:" +
str(i + 1))
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
def draw_my_view(url):
try:
html = ''
nav_html_list = {}
# 用来画收益曲线图的数据
cur = get_cursor("""
SELECT
date_string as 时间,
my_today_profit as 我的实盘日收益,
my_real_profit as 我的实盘累积收益,
my_real_yield || '%' as 我的实盘收益率,
my_day_yield || '%' as 我的日收益率,
cb_day_yield || '%' as 可转债指数日收益率,
hs_day_yield || '%' as 沪深300日收益率,
round(my_all_yield-1, 2) as 我的累积收益率,
round(cb_all_yield-1, 2) as 可转债指数累积收益率,
round(hs_all_yield-1, 2) as 沪深300累积收益率
from invest_yield
order by date desc --limit 2
""")
# table_html = table.get_html_string()
table, table_html = utils.table_html_utils.generate_table_html_with_data(None, cur, html, need_title=False,
remark_fields=['我的日收益率', '可转债指数日收益率', '沪深300日收益率'],
ignore_fields=['我的累积收益率', '可转债指数累积收益率', '沪深300累积收益率'],
nav_html_list=nav_html_list, table_width='800px')
rows = []
dict_rows = []
for row in table._rows:
rows.append(row)
dict_row = db_utils.get_dict_row(cur, row)
dict_rows.append(dict_row)
line_html = generate_line_html(dict_rows)
html = """
<center>
""" + line_html + "<br/>" + table_html + """
</center>
"""
return '我的可转债收益情况', views.nav_utils.build_personal_nav_html(url), html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(url):
try:
html = ''
# =========全网可转债涨跌幅柱状图=========
cur = get_cursor("""select case
when cb_mov2_id < -20 then '低于-20%'
when cb_mov2_id > -20 and cb_mov2_id <= -10 then '[-20%~-10%]'
when cb_mov2_id > -10 and cb_mov2_id <= -5 then '[-10%~-5%]'
when cb_mov2_id > -5 and cb_mov2_id <= -1 then '[-5%~-1%]'
when cb_mov2_id > -1 and cb_mov2_id < 0 then '[-1%~0]'
when cb_mov2_id = 0 then '[0]'
when cb_mov2_id > 0 and cb_mov2_id <= 1 then '[0~1%]'
when cb_mov2_id > 1 and cb_mov2_id <= 5 then '[1%,5%]'
when cb_mov2_id > 5 and cb_mov2_id <= 10 then '[5%,10%]'
when cb_mov2_id > 10 and cb_mov2_id <= 20 then '[10~20%]'
when cb_mov2_id > 20 then '超过20%'
end as 范围
, count(id) as 个数, round(min(cb_mov2_id), 2) as min_value, round(max(cb_mov2_id), 2) as max_value
from (SELECT id, cb_mov2_id * 100 as cb_mov2_id, cb_name_id from changed_bond) c
group by case
when cb_mov2_id < -20 then '低于-20%'
when cb_mov2_id > -20 and cb_mov2_id <= -10 then '[-20%~-10%]'
when cb_mov2_id > -10 and cb_mov2_id <= -5 then '[-10%~-5%]'
when cb_mov2_id > -5 and cb_mov2_id <= -1 then '[-5%~-1%]'
when cb_mov2_id > -1 and cb_mov2_id < 0 then '[-1%~0]'
when cb_mov2_id = 0 then '[0]'
when cb_mov2_id > 0 and cb_mov2_id <= 1 then '[0~1%]'
when cb_mov2_id > 1 and cb_mov2_id <= 5 then '[1%,5%]'
when cb_mov2_id > 5 and cb_mov2_id <= 10 then '[5%~10%]'
when cb_mov2_id > 10 and cb_mov2_id <= 20 then '[10~20%]'
when cb_mov2_id > 20 then '超过20%'
end
order by cb_mov2_id""")
rows = cur.fetchall()
html += generate_bar_html(rows, '全网可转债涨跌幅情况')
return '可转债涨跌幅分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(url):
try:
html = ''
# =========全网可转债涨跌幅柱状图=========
cur = get_cursor("""select case
when cb_price2_id < 90 then '低于90元'
when cb_price2_id > 90 and cb_price2_id <= 100 then '[90元~100元]'
when cb_price2_id > 100 and cb_price2_id <= 110 then '[100元~110元]'
when cb_price2_id > 110 and cb_price2_id <= 120 then '[110元~120元]'
when cb_price2_id > 120 and cb_price2_id <= 130 then '[120元~130元]'
when cb_price2_id > 130 and cb_price2_id <= 150 then '[130~150元]'
when cb_price2_id > 150 and cb_price2_id <= 180 then '[150元~180元]'
when cb_price2_id > 180 and cb_price2_id <= 200 then '[180元~200元]'
when cb_price2_id > 200 and cb_price2_id <= 300 then '[200元~300元]'
when cb_price2_id > 300 then '超过300元'
end as 范围
, count(id) as 个数, round(min(cb_price2_id), 2) as min_value, round(max(cb_price2_id), 2) as max_value
from (SELECT id, cb_price2_id, cb_name_id from changed_bond) c
group by case
when cb_price2_id < 90 then '低于90元'
when cb_price2_id > 90 and cb_price2_id <= 100 then '[90元~100元]'
when cb_price2_id > 100 and cb_price2_id <= 110 then '[100元~110元]'
when cb_price2_id > 110 and cb_price2_id <= 120 then '[110元~120元]'
when cb_price2_id > 120 and cb_price2_id <= 130 then '[120元~130元]'
when cb_price2_id > 130 and cb_price2_id <= 150 then '[130~150元]'
when cb_price2_id > 150 and cb_price2_id <= 180 then '[150元~180元]'
when cb_price2_id > 180 and cb_price2_id <= 200 then '[180元~200元]'
when cb_price2_id > 200 and cb_price2_id <= 300 then '[200元~300元]'
when cb_price2_id > 300 then '超过300元'
end
order by cb_price2_id""")
rows = cur.fetchall()
html += generate_bar_html(rows, '全网可转债价格范围分布情况')
return '可转债价格范围分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def modify_data_unit_error():
# 遍历可转债列表
try:
i = 0
stock_cursor = get_cursor(
"""SELECT avg_net_margin, stock_name from stock_report""")
stocks = list(stock_cursor)
for stock in stocks:
avg_net_margin = stock[1]
stock_name = stock[2]
if not isinstance(avg_net_margin,
str) or not avg_net_margin.endswith('万'):
continue
avg_net_margin = change_data_unit(avg_net_margin, no_clean_text)
rowcount = execute_sql_with_rowcount(
"""update stock_report set avg_net_margin = :avg_net_margin where stock_name = :stock_name""",
{
'avg_net_margin': avg_net_margin,
'stock_name': stock_name
})
i += 1
if rowcount == 0:
print("update " + stock_name + " is failure. count:" + str(i))
else:
print("update " + stock_name + " is successful. count:" +
str(i))
print("共处理" + str(i) + "条记录")
except Exception as e:
print("db操作出现异常" + str(e), e)
except TimeoutError as e:
print("网络超时, 请手工重试")
def draw_view(url):
try:
sql = """SELECT cb_name_id, cb_hot from changed_bond where cb_hot > 5 order by cb_hot desc limit 1000"""
cur = get_cursor(sql)
rows = cur.fetchall()
words = []
for row in rows:
words.append((row[0].replace('转债', ''), row[1]))
chart = WordCloud(
opts.InitOpts(height='1000px',
width='1424px',
theme=ThemeType.MACARONS,
chart_id='cb_wordcloud'))
chart.add(
series_name="",
# 添加数据
data_pair=words,
# 字间隙
word_gap=5,
# 调整字大小范围
word_size_range=[5, 100],
# shape="cardioid",
is_draw_out_of_bound=True,
rotate_step=1,
# 选择背景图，也可以不加该参数，使用默认背景
# mask_image='购物车.jpg'
)
chart.set_global_opts(
title_opts=opts.TitleOpts(
title="",
title_textstyle_opts=opts.TextStyleOpts(font_size=23)),
tooltip_opts=opts.TooltipOpts(is_show=True),
)
html = chart.render_embed('template.html', env)
return '可转债热度分析', views.nav_utils.build_analysis_nav_html(url), html
except Exception as e:
print("processing is failure. ", e)
raise e
def query_database_view():
table_html = ''
sql_code = ''
table_height_style = ''
if len(request.form) > 0:
sql_code = request.form['sql_code']
if sql_code is None or sql_code.strip(' ') == '':
raise Exception('SQL不能为空')
if not sql_code.lower().strip().startswith('select'):
raise Exception("仅允许select操作")
cur = get_cursor(sql_code)
table = from_db_cursor(cur)
if table.rowcount > 10:
table_height_style = """style="height:500px" """
table_html = utils.table_html_utils.build_table_html(table)
return render_template("query_database.html", table_html=table_html, sql_code=sql_code, table_height_style=table_height_style)
def update_db(rows):
try:
i = 0
for row in rows:
cur = get_cursor(
"select count(*) from changed_bond where bond_code=:bond_code",
{'bond_code': row['bond_code']})
one = cur.fetchone()
if one[0] == 0:
print("not update cb:" + row['cb_name_id'])
continue
# execute执行脚本
rowcount = execute_sql_with_rowcount(
"""update changed_bond
set cb_price2_id = :cb_price2_id,
cb_mov2_id = :cb_mov2_id,
cb_mov_id = :cb_mov_id,
stock_price_id = :stock_price_id,
cb_premium_id = :cb_premium_id
where bond_code = :bond_code""", {
'cb_price2_id': row.get('cb_price2_id', None),
'cb_mov2_id': row.get('cb_mov2_id', None),
'cb_mov_id': row.get('cb_mov_id', None),
'stock_price_id': row.get('stock_price_id', None),
'cb_premium_id': row.get('cb_premium_id', None),
'bond_code': row['bond_code']
})
if rowcount == 0:
print("not update cb:" + str(row))
else:
print("update cb:" + str(row))
i += 1
print('Successfully updated row count:' + str(i))
except Exception as e:
print("db操作出现异常", e)
raise e
def do_update_stock_sum(driver, task_name):
# 遍历可转债列表
# 打开文件数据库
task = None
try:
# 查询可转债
bond_cursor = get_cursor(
"""SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond"""
)
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
# 当前日月
y = datetime.now().year
m = datetime.now().month
d = datetime.now().day
t = datetime(y, m, d)
# 记录更新时间(秒)
s = (t - datetime(1970, 1, 1)).total_seconds()
i = 0
for bond_row in rows:
process_task_when_normal(task, 1)
stock_code = bond_row[2]
stock_name = bond_row[3]
stock_cursor = get_cursor(
"""SELECT modify_date from stock_report where stock_code = :stock_code""",
{'stock_code': stock_code})
stocks = list(stock_cursor)
if len(stocks) == 0:
continue
# 已经更新了
if stocks[0][0] is not None and stocks[0][0] >= s:
continue
row = get_stock_sum(driver, stock_code)
rowcount = execute_sql_with_rowcount(
"""update stock_report set pe = :pe,
net_asset = :net_asset,
gross_rate = :gross_rate,
avg_market_cap = :avg_market_cap,
avg_roe = :avg_roe,
avg_net_profit_ratio = :avg_net_profit_ratio,
avg_gross_rate = :avg_gross_rate,
avg_pb = :avg_pb,
avg_pe = :avg_pe,
avg_net_margin = :avg_net_margin,
avg_net_asset = :avg_net_asset,
rank_roe = :rank_roe,
rank_net_profit_ratio = :rank_net_profit_ratio,
rank_gross_rate = :rank_gross_rate,
rank_pb = :rank_pb,
rank_pe = :rank_pe,
rank_net_margin = :rank_net_margin,
rank_net_asset = :rank_net_asset,
rank_market_cap = :rank_market_cap,
level_roe = :level_roe,
level_market_cap = :level_market_cap,
level_net_asset = :level_net_asset,
level_net_margin = :level_net_margin,
level_pe = :level_pe,
level_pb = :level_pb,
level_gross_rate = :level_gross_rate,
level_net_profit_ratio = :level_net_profit_ratio,
modify_date = :modify_date where stock_code = :stock_code""",
({
'pe': row['pe'],
'net_asset': row['net_asset'],
'gross_rate': row['gross_rate'],
'avg_market_cap': row['avg_market_cap'],
'avg_roe': row['avg_roe'],
'avg_net_profit_ratio': row['avg_net_profit_ratio'],
'avg_gross_rate': row['avg_gross_rate'],
'avg_pb': row['avg_pb'],
'avg_pe': row['avg_pe'],
'avg_net_margin': row['avg_net_margin'],
'avg_net_asset': row['avg_net_asset'],
'rank_roe': row['rank_roe'],
'rank_net_profit_ratio': row['rank_net_profit_ratio'],
'rank_gross_rate': row['rank_gross_rate'],
'rank_pb': row['rank_pb'],
'rank_pe': row['rank_pe'],
'rank_net_margin': row['rank_net_margin'],
'rank_net_asset': row['rank_net_asset'],
'rank_market_cap': row['rank_market_cap'],
'level_roe': row['level_roe'],
'level_market_cap': row['level_market_cap'],
'level_net_asset': row['level_net_asset'],
'level_net_margin': row['level_net_margin'],
'level_pe': row['level_pe'],
'level_pb': row['level_pb'],
'level_gross_rate': row['level_gross_rate'],
'level_net_profit_ratio': row['level_net_profit_ratio'],
'modify_date': s,
'stock_code': stock_code
}))
if rowcount == 0:
print("not update stock:" + stock_name + ' in stock_report')
else:
print("update " + stock_name + " is successful. count:" +
str(i + 1))
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
raise e
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
raise e
def insertDb(rows):
# 打开文件数据库
try:
for row in rows:
# execute执行脚本
#
get_cursor(
"""insert into changed_bond(cb_num_id,bond_code,cb_name_id,bond_date_id,stock_code,stock_name,industry,sub_industry,cb_price2_id,cb_mov2_id,cb_mov3_id,stock_price_id,cb_mov_id,cb_price3_id,cb_strike_id,cb_premium_id,cb_value_id,cb_t_id,bond_t1,red_t,remain_amount,cb_trade_amount_id,cb_trade_amount2_id,cb_to_share,cb_to_share_shares,market_cap,stock_pb,BT_yield,AT_yield,BT_red,AT_red,npv_red,npv_value,rating,discount_rate,elasticity,cb_ol_value,cb_ol_rank,cb_nl_value,cb_nl_rank,cb_ma20_deviate,cb_hot,duration,enforce_get,buy_back,down_revise,data_id)
values(:cb_num_id,:bond_code,:cb_name_id,:bond_date_id,:stock_code,:stock_name,:industry,:sub_industry,:cb_price2_id,:cb_mov2_id,:cb_mov3_id,:stock_price_id,:cb_mov_id,:cb_price3_id,:cb_strike_id,:cb_premium_id,:cb_value_id,:cb_t_id,:bond_t1,:red_t,:remain_amount,:cb_trade_amount_id,:cb_trade_amount2_id,:cb_to_share,:cb_to_share_shares,:market_cap,:stock_pb,:BT_yield,:AT_yield,:BT_red,:AT_red,:npv_red,:npv_value,:rating,:discount_rate,:elasticity,:cb_ol_value,:cb_ol_rank,:cb_nl_value,:cb_nl_rank,:cb_ma20_deviate,:cb_hot,:duration,:enforce_get,:buy_back,:down_revise,:data_id)""",
{
'cb_num_id': row['cb_num_id'],
'bond_code': row['bond_code'],
'cb_name_id': row['cb_name_id'],
'bond_date_id': row['bond_date_id'],
'stock_code': row['stock_code'],
'stock_name': row['stock_name'],
'industry': row['industry'],
'sub_industry': row['sub_industry'],
'cb_price2_id': row['cb_price2_id'],
'cb_mov2_id': row['cb_mov2_id'],
'cb_mov3_id': row['cb_mov3_id'],
'stock_price_id': row['stock_price_id'],
'cb_mov_id': row['cb_mov_id'],
'cb_price3_id': row['cb_price3_id'],
'cb_strike_id': row['cb_strike_id'],
'cb_premium_id': row['cb_premium_id'],
'cb_value_id': row['cb_value_id'],
'cb_t_id': row['cb_t_id'],
'bond_t1': row['bond_t1'],
'red_t': row['red_t'],
'remain_amount': row['remain_amount'],
'cb_trade_amount_id': row['cb_trade_amount_id'],
'cb_trade_amount2_id': row['cb_trade_amount2_id'],
'cb_to_share': row['cb_to_share'],
'cb_to_share_shares': row['cb_to_share_shares'],
'market_cap': row['market_cap'],
'stock_pb': row['stock_pb'],
'BT_yield': row['BT_yield'],
'AT_yield': row['AT_yield'],
'BT_red': row['BT_red'],
'AT_red': row['AT_red'],
'npv_red': row['npv_red'],
'npv_value': row['npv_value'],
'rating': row['rating'],
'discount_rate': row['discount_rate'],
'elasticity': row['elasticity'],
'cb_ol_value': row['cb_ol_value'],
'cb_ol_rank': row['cb_ol_rank'],
'cb_nl_value': row['cb_nl_value'],
'cb_nl_rank': row['cb_nl_rank'],
'cb_ma20_deviate': row['cb_ma20_deviate'],
'cb_hot': row['cb_hot'],
'duration': row['duration'],
'enforce_get': row.get('enforce_get'),
'buy_back': row.get('buy_back'),
'down_revise': row.get('down_revise'),
'data_id': row['data_id']
})
except Exception as e:
# cur_file.close()
print("db操作出现异常", e)
def fetch_data(task_name):
# 遍历可转债列表
stock_name = ''
stock_code = None
earnings = None
task = None
try:
# 当前报告期
report_date = get_report_date()
# 查询可转债
bond_cursor = get_cursor("""
SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond
""")
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
i = 0
for bond_row in rows:
process_task_when_normal(task, 1)
stock_code = bond_row[2]
stock_name = bond_row[3]
stock_cursor = get_cursor(
"SELECT last_date from stock_report where stock_code = '" +
stock_code + "'")
stocks = list(stock_cursor)
# 还没添加正股财务指标信息
if len(stocks) == 0:
earnings = get_earnings(stock_code)
# 新增
rowcount = db_utils.execute_sql_with_rowcount(
"""insert into stock_report(stock_code,stock_name,
last_date,
revenue,qoq_revenue_rate,yoy_revenue_rate,
net,qoq_net_rate,yoy_net_rate,
margin,qoq_margin_rate,yoy_margin_rate,
roe,qoq_roe_rate,yoy_roe_rate,
al_ratio,qoq_rl_ratio_rate,yoy_al_ratio_rate)
values(:stock_code,:stock_name,
:last_date,
:revenue,:qoq_revenue_rate,:yoy_revenue_rate,
:net,:qoq_net_rate,:yoy_net_rate,
:margin,:qoq_margin_rate,:yoy_margin_rate,
:roe,:qoq_roe_rate,:yoy_roe_rate,
:al_ratio,:qoq_rl_ratio_rate,:yoy_al_ratio_rate)""",
{
'stock_code': bond_row[2],
'stock_name': bond_row[3],
'last_date': earnings.lastDate,
'revenue': earnings.revenue,
'qoq_revenue_rate': earnings.qoqRevenueRate,
'yoy_revenue_rate': earnings.yoyRevenueRate,
'net': earnings.net,
'qoq_net_rate': earnings.qoqNetRate,
'yoy_net_rate': earnings.yoyNetRate,
'margin': earnings.margin,
'qoq_margin_rate': earnings.qoqMarginRate,
'yoy_margin_rate': earnings.yoyMarginRate,
'roe': earnings.roe,
'qoq_roe_rate': earnings.qoqRoeRate,
'yoy_roe_rate': earnings.yoyRoeRate,
'al_ratio': earnings.alRatio,
'qoq_rl_ratio_rate': earnings.qoqAlRatioRate,
'yoy_al_ratio_rate': earnings.yoyAlRatioRate,
})
if rowcount == 0:
print("insert " + stock_name + " is failure. count:" +
str(i + 1))
else:
print("insert " + stock_name + " is successful. count:" +
str(i + 1))
else:
# todo 补充前面几年/季度的财务数据
# fixme 为了解决中途被中断， 继续执行时， 简化处理不更新
# continue
last_date = stocks[0][0]
if last_date != report_date:
earnings = get_earnings(stock_code)
rowcount = db_utils.execute_sql_with_rowcount(
"""update stock_report
set last_date = :last_date,
revenue = :revenue,qoq_revenue_rate = :qoq_revenue_rate,yoy_revenue_rate = :yoy_revenue_rate,
net = :net,qoq_net_rate = :qoq_net_rate,yoy_net_rate = :yoy_net_rate,
margin = :margin,qoq_margin_rate = :qoq_margin_rate,yoy_margin_rate = :yoy_margin_rate,
roe = :roe,qoq_roe_rate = :qoq_roe_rate,yoy_roe_rate = :yoy_roe_rate,
al_ratio = :al_ratio,qoq_rl_ratio_rate = :qoq_rl_ratio_rate,yoy_al_ratio_rate = :yoy_al_ratio_rate
where stock_code = :stock_code""", {
'last_date': earnings.lastDate,
'revenue': earnings.revenue,
'qoq_revenue_rate': earnings.qoqRevenueRate,
'yoy_revenue_rate': earnings.yoyRevenueRate,
'net': earnings.net,
'qoq_net_rate': earnings.qoqNetRate,
'yoy_net_rate': earnings.yoyNetRate,
'margin': earnings.margin,
'qoq_margin_rate': earnings.qoqMarginRate,
'yoy_margin_rate': earnings.yoyMarginRate,
'roe': earnings.roe,
'qoq_roe_rate': earnings.qoqRoeRate,
'yoy_roe_rate': earnings.yoyRoeRate,
'al_ratio': earnings.alRatio,
'qoq_rl_ratio_rate': earnings.qoqAlRatioRate,
'yoy_al_ratio_rate': earnings.yoyAlRatioRate,
'stock_code': stock_code
})
if rowcount == 0:
print("update " + stock_name + " is failure. count:" +
str(i + 1))
else:
print("update " + stock_name +
" is successful. count:" + str(i + 1))
# 暂停3s再执行， 避免被网站屏蔽掉
time.sleep(3)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print(
"db操作出现异常" + str(e) + ', stock_code: ' + stock_code +
', stock_name:' + stock_name + ', earnings:' + str(earnings), e)
process_task_when_error(task, "db操作出现异常")
raise e
return 'OK'
def draw_my_view(is_login_user, url):
# 打开文件数据库
try:
html = ''
nav_html_list = views.nav_utils.build_personal_nav_html_list()
# =========我的强赎=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, enforce_get as 强赎状态,
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率, h.account as 账户, h.memo as 备注
from (select * from changed_bond where enforce_get in ('强赎中')) c, hold_bond h
WHERE c.bond_code = h.bond_code and h.hold_owner = 'me' and h.hold_amount != -1
order by 转债价格 desc
""")
html = generate_table_html("强赎",
cur,
html,
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的回售=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)',
cb_price2_id as 转债价格, round(bt_red * 100,2) as 回售收益率, red_t as 回售年限,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
round(cb_premium_id*100,2) || '%' as 溢价率,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '回售' and h.hold_owner = 'me' and h.hold_amount != -1
order by 回售收益率
""")
html = generate_table_html(
"回售",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '回售年限', '回售收益率'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的低余额策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率, round(bt_yield*100,2) || '%' as 到期收益率, round(cb_price2_id + cb_premium_id * 100,2) as 双低值,
round(cb_to_share_shares * 100,2) as '余额/股本(%)',
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND h.strategy_type = '低余额'
and h.hold_owner = 'me'
and h.hold_amount != -1
order by 转债价格
""")
html = generate_table_html(
"低余额",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '正股涨跌'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的低价高收益策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)',
cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率, round(bt_yield*100,2) || '%' as 到期收益率, round(100- cb_price2_id + BT_yield * 100, 2) as 性价比,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '高收益' and h.hold_owner = 'me' and h.hold_amount != -1
ORDER by 性价比 desc
""")
html = generate_table_html(
"高收益",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的双低策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_price2_id + cb_premium_id * 100,2) as 双低值, round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
round(bt_yield*100,2) || '%' as 到期收益率,round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '双低' and h.hold_owner = 'me' and h.hold_amount != -1
order by 双低值
""")
html = generate_table_html(
"双低",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的双低轮动策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_price2_id + cb_premium_id * 100,2) as 双低值,round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
round(bt_yield*100,2) || '%' as 到期收益率, round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '双低轮动' and h.hold_owner = 'me' and h.hold_amount != -1
order by 双低值
""")
html = generate_table_html(
"双低轮动",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的多因子策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
round(cb_price2_id + cb_premium_id * 100,2) as 双低值,
round(bt_yield*100,2) || '%' as 到期收益率,round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '多因子' and h.hold_owner = 'me' and h.hold_amount != -1
order by 双低值
""")
html = generate_table_html(
"多因子",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的打新策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_price2_id + cb_premium_id * 100,2) as 双低值, round(bt_yield*100,2) || '%' as 到期收益率,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '打新' and h.hold_owner = 'me' and h.hold_amount != -1
order by 转债价格
""")
html = generate_table_html(
"打新",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的网格策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率, round(bt_yield*100,2) || '%' as 到期收益率,
round(cb_price2_id + cb_premium_id * 100,2) as 双低值,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code AND h.strategy_type = '网格' and h.hold_owner = 'me' and h.hold_amount != -1
order by 转债价格
""")
html = generate_table_html(
"网格",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的基本面策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)',
cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率, round(bt_yield*100,2) || '%' as 到期收益率,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度,
h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND h.strategy_type = '基本面' and h.hold_owner = 'me' and h.hold_amount != -1
order by 转债价格
""")
html = generate_table_html(
"基本面",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的猪肉概念=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)',
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率, round(bt_yield*100,2) || '%' as 到期收益率,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度,
h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND h.strategy_type = '猪肉概念' and h.hold_owner = 'me' and h.hold_amount != -1
order by 转债价格
""")
html = generate_table_html(
"猪肉概念",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '正股涨跌'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========我的每周精选策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rating as '信用', duration as 续存期, round(bt_yield*100,2) || '%' as 到期收益率,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比', round(s.net,2) as '净利润(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin || '%' as '利润率(%)', s.yoy_margin_rate || '%' as '利润率同比', s.roe || '%' as 'ROE(%)', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比', s.pe as '市盈率(动)', c.stock_pb as 市净率,
market_cap as '市值(亿元)', remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)',
cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND h.strategy_type = '每周精选' and h.hold_owner = 'me' and h.hold_amount != -1
order by 转债价格
""")
html = generate_table_html("每周精选",
cur,
html,
nav_html_list=nav_html_list,
tables=tables)
# =========我的活性债策略=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率, round(bt_yield*100,2) || '%' as 到期收益率,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度,
h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND h.strategy_type = '活性债' and h.hold_owner = 'me' and h.hold_amount != -1
--and h.hold_owner = '水晶杯'
order by 转债价格
""")
html = generate_table_html(
"活性债",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========其他=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值, round(bt_yield*100,2) || '%' as 到期收益率,
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)', round(cb_to_share_shares * 100,2) || '%' as '余额/股本(%)', cb_trade_amount_id as '成交额(百万)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度,
h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND h.strategy_type = '其他' and h.hold_owner = 'me' and h.hold_amount != -1
order by 双低值
""")
html = generate_table_html(
"其他",
cur,
html,
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
configs = db.session.query(Config).filter(
Config.group == 'my_strategy', Config.is_delete == 0).all()
for config in configs:
cur = get_cursor(config.value)
ext_config = json.loads(config.ext_value)
remark_fields = ext_config['remark_fields']
html = generate_table_html(config.key,
cur,
html,
remark_fields=remark_fields,
nav_html_list=nav_html_list,
tables=tables,
is_login_user=is_login_user)
# =========清仓建议=========
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称,
h.hold_amount as 持有数量,
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)', cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值, round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
round(bt_yield*100,2) || '%' as 到期收益率, round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',
h.account as 账户, h.strategy_type as 策略,
c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code, hold_bond h
WHERE c.bond_code = h.bond_code
AND (
cb_price2_id < 100 and cb_premium_id > 0.9
or cb_price2_id > 130
or cb_price2_id > 100 and cb_price2_id < 110 and cb_premium_id > 0.6
)
and h.hold_owner = 'me' and h.hold_amount != -1
order by cb_price2_id
""")
html = generate_table_html(
"清仓建议",
cur,
html,
subtitle='(价格<100且溢价率>90%或100<价格<110且溢价率>60%或价格>130)',
remark_fields=['盈亏', '溢价率', '可转债涨跌', '到期收益率', '双低值'],
nav_html_list=nav_html_list,
is_login_user=is_login_user)
# 数据汇总
# 用来画统计图的数据
cur = get_cursor("""
SELECT strategy_type as 策略,
count(h.bond_code) as 个数,
sum(h.hold_amount) as 数量,
round(sum(h.sum_buy-h.sum_sell),2) as 投入金额,
round(sum(h.hold_amount * c.cb_price2_id),2) as 市值,
round(sum(c.cb_price2_id*hold_amount + today_sum_sell - today_sum_buy), 2) as '日收益',
round(sum(c.cb_price2_id*hold_amount + today_sum_sell - today_sum_buy)/sum(today_sum_buy)*100,2) || '%' as '日收益率',
round(sum(round(c.cb_price2_id*h.hold_amount+h.sum_sell -h.sum_buy, 3)), 2) as '累积收益',
round(sum(round(c.cb_price2_id*h.hold_amount+h.sum_sell -h.sum_buy, 3)) /sum(h.sum_buy) * 100, 2) || '%' as 累积收益率
from hold_bond h , changed_bond c
where h.bond_code = c.bond_code and hold_owner='me' GROUP by strategy_type order by 投入金额 DESC
""")
rows = []
dict_rows = []
# 增加合计行
total_money = 0
assets_money = 0
total_profit = 0
total_now_profit = 0
total_now_profit_rate = 0
total_amount = 0
total_num = 0
money_rows = []
for row in cur.fetchall():
rows.append(row)
dict_row = db_utils.get_dict_row(cur, row)
dict_rows.append(dict_row)
asset_row = dict_row['市值']
money_row = dict_row['投入金额']
money_rows.append(money_row)
total_money += money_row
assets_money += asset_row
total_profit += dict_row['累积收益']
total_now_profit += dict_row['日收益']
total_num += dict_row['个数']
total_amount += dict_row['数量']
money_rows.reverse()
new_rows = []
for row in rows:
money_row = money_rows.pop()
new_row = row + (str(round(money_row / total_money * 100, 2)) +
'%', )
new_rows.append(new_row)
total_yield = round(total_profit / total_money * 100, 2)
total_now_yield = round(total_now_profit / total_money * 100, 2)
new_rows.append([
'合计', total_num, total_amount,
round(total_money, 2),
round(assets_money, 2),
round(total_now_profit, 2),
str(round(total_now_yield, 2)) + '%',
round(total_profit, 2),
str(total_yield) + '%', '100%'
])
pie_html = utils.echarts_html_utils.generate_pie_html(
dict_rows, '策略', '投入金额')
head_column_link_maker = lambda record, field: '#' + record[
field] if field == '策略' else None
sum_html = utils.table_html_utils.generate_table_html(
"汇总",
cur,
'',
need_title=False,
ext_field_names=['投入占比'],
remark_fields=['日收益', '日收益率', '累积收益率', '累积收益'],
ignore_fields=['投入金额'],
rows=new_rows,
head_column_link_maker=head_column_link_maker)
# 用柱状图从大到小展示持有可转债涨跌幅情况
scatter_html = utils.echarts_html_utils.generate_scatter_html_with_multi_tables(
tables, select=select)
html = """
<center>
""" + sum_html + '<br/>' + pie_html + scatter_html + "<br/>" + """
</center>
""" + html
return '我的策略', views.nav_utils.build_personal_nav_html(
url, nav_html_list), html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(is_login_user, key, rise, url):
try:
html = ''
cur = get_cursor("""
select a.industry, round(b.涨跌幅 / a.个数*100, 2) as 涨跌, 余额
from (SELECT industry, count(industry) as 个数 from changed_bond group by industry) a,
(SELECT industry, sum(cb_mov2_id) as 涨跌幅 from changed_bond group by industry) b,
(SELECT industry, round(sum(remain_amount),2) as 余额 from changed_bond group by industry) c
where a.industry = b.industry
and a.industry = c.industry
order by 涨跌 desc
""")
html += generate_treemap_html(
cur,
'=========可转债涨跌行业分布=========',
'industry',
'余额',
'/view_tree_map_industry.html',
area_data_name='余额',
area_data_unit='亿元',
)
if key is not None and key.strip(' ') != '' and rise is not None:
rise = float(rise)
up = 0
down = 0
if rise >= 0:
up = 10
else:
down = 10
cur = get_cursor(
"""
SELECT DISTINCT d.*,
e.strategy_type as 策略,
e.hold_id,
e.hold_price as 持有成本,
e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
case when cb_mov2_id > 0 then 1 when cb_mov2_id = 0 then 0 ELSE -1 end as _sign,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
cb_mov2_id,
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
cb_price2_id as '转债价格',
round(cb_premium_id * 100, 2) || '%' as 溢价率,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度
from (select *
from (SELECT DISTINCT c.*
from changed_bond c WHERE industry = :industry
order by cb_mov2_id DESC
limit :up_size)
UNION
select *
from (SELECT DISTINCT c.*
from changed_bond_view c WHERE industry = :industry
order by cb_mov2_id ASC
limit :down_size)) c
LEFT join stock_report s on c.stock_code = s.stock_code) d
left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond
where id in (select id
from hold_bond
where id
in (SELECT min(id)
from hold_bond
where hold_owner = 'me' and hold_amount != -1
group by bond_code))
) e
on d.bond_code = e.bond_code
order by _sign desc, abs(cb_mov2_id) DESC
""", {
'industry': key,
'up_size': up,
'down_size': down
})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
is_login_user=is_login_user)
html += "<div id='cb_detail_list'>"
html += generate_scatter_html_with_one_table(
table,
title=key + '行业可转债分布',
sub_title='仅展示涨/跌幅top10的可转债',
use_personal_features=is_login_user)
html += table_html
html += '</div>'
return '可转债涨跌分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(is_login_user, url):
try:
html = ''
# =========全网可转债涨跌TOP20柱状图=========
cur = get_cursor("""
select bond_code, cb_name_id as 名称, round(cb_mov2_id * 100, 2) as 涨跌, cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率
from (SELECT DISTINCT c. * from changed_bond c
order by cb_mov2_id DESC limit 20)
UNION
select bond_code, cb_name_id as 名称, round(cb_mov2_id * 100, 2) as 涨跌, cb_price2_id as 转债价格, round(cb_premium_id*100,2) || '%' as 溢价率
from (SELECT DISTINCT c. * from changed_bond c
order by cb_mov2_id ASC limit 20)
order by 涨跌 desc
""")
rows = cur.fetchall()
html += generate_bar_html(rows, '全网可转债涨跌TOP20')
cur = get_cursor("""
SELECT DISTINCT d.* , e.strategy_type as 策略, case when e.hold_id is not null then '✔️️' else '' END as 持有, e.hold_price as 持有成本, e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称,cb_mov2_id, round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
cb_price2_id as '转债价格', round(cb_premium_id*100,2) || '%' as 溢价率,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌, remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)', cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值, round(bt_yield*100,2) || '%' as 到期收益率,
c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度
from (select *
from (SELECT DISTINCT c. * from changed_bond c
order by cb_mov2_id DESC limit 10)
UNION
select *
from (SELECT DISTINCT c. * from changed_bond_view c
order by cb_mov2_id ASC limit 10) ) c LEFT join stock_report s on c.stock_code = s.stock_code ) d left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond where id in (select id from hold_bond where id
in (SELECT min(id) from hold_bond where hold_owner = 'me' and hold_amount != -1 group by bond_code) )
) e
on d.bond_code = e.bond_code
ORDER by cb_mov2_id DESC
""")
html = utils.table_html_utils.generate_table_html("全网涨跌TOP10", cur, html, need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
is_login_user=is_login_user)
return '可转债涨跌排行', views.nav_utils.build_analysis_nav_html(url), html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(is_login_user, key, start, end, rise, url):
try:
html = ''
cur = get_cursor("""
select i as 价格区间, round(s_m / c_i*100, 2) as 涨跌, c_i as 数量
from (select DISTINCT(_interval) as i, count(_interval) as c_i, sum(cb_mov2_id) as s_m
from (SELECT cb_price2_id,
cb_mov2_id,
case
when cb_price2_id <= 80 then '<=80元'
when cb_price2_id > 80 and cb_price2_id <= 90 then '80~90元'
when cb_price2_id > 90 and cb_price2_id <= 100 then '90~100元'
when cb_price2_id > 100 and cb_price2_id <= 110 then '100~110元'
when cb_price2_id > 110 and cb_price2_id <= 120 then '110~120元'
when cb_price2_id > 120 and cb_price2_id <= 130 then '120~130元'
when cb_price2_id > 130 and cb_price2_id <= 150 then '130~150元'
when cb_price2_id > 150 and cb_price2_id <= 200 then '150~200元'
when cb_price2_id > 200 then '>200元' end
as _interval,
case
when cb_price2_id <= 80 then 1
when cb_price2_id > 80 and cb_price2_id <= 90 then 2
when cb_price2_id > 90 and cb_price2_id <= 100 then 3
when cb_price2_id > 100 and cb_price2_id <= 110 then 4
when cb_price2_id > 110 and cb_price2_id <= 120 then 5
when cb_price2_id > 120 and cb_price2_id <= 130 then 6
when cb_price2_id > 130 and cb_price2_id <= 150 then 7
when cb_price2_id > 150 and cb_price2_id <= 200 then 8
when cb_price2_id > 200 then 9 end
as _interval_idx
from changed_bond)
GROUP by _interval
order by _interval_idx)
""")
html += generate_treemap_html(cur, '=========可转债涨跌价格分布=========',
'价格区间', '数量',
'/view_tree_map_price.html')
if start is not None or end is not None and rise is not None:
rise = float(rise)
up = 0
down = 0
if rise >= 0:
up = 10
else:
down = 10
cur = get_cursor(
"""
SELECT DISTINCT d.*,
e.strategy_type as 策略,
e.hold_id,
e.hold_price as 持有成本,
e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
case when cb_mov2_id > 0 then 1 when cb_mov2_id = 0 then 0 ELSE -1 end as _sign,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
cb_mov2_id,
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
cb_price2_id as '转债价格',
round(cb_premium_id * 100, 2) || '%' as 溢价率,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度
from (select *
from (SELECT DISTINCT c.*
from changed_bond c WHERE cb_price2_id > :start and cb_price2_id <= :end
order by cb_mov2_id DESC
limit :up_size)
UNION
select *
from (SELECT DISTINCT c.*
from changed_bond_view c WHERE cb_price2_id > :start and cb_price2_id <= :end
order by cb_mov2_id ASC
limit :down_size)) c
LEFT join stock_report s on c.stock_code = s.stock_code) d
left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond
where id in (select id
from hold_bond
where id
in (SELECT min(id)
from hold_bond
where hold_owner = 'me' and hold_amount != -1
group by bond_code))
) e
on d.bond_code = e.bond_code
order by _sign desc, abs(cb_mov2_id) DESC
""", {
'start': start,
'end': end,
'up_size': up,
'down_size': down
})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
is_login_user=is_login_user)
html += "<div id='cb_detail_list'>"
html += generate_scatter_html_with_one_table(
table,
title='价格' + key + '的可转债涨跌分布',
sub_title='仅展示涨/跌幅top10的可转债',
use_personal_features=is_login_user)
html += table_html
html += '</div>'
return '可转债涨跌分布', \
views.nav_utils.build_analysis_nav_html(url), \
html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_my_view(user_id, bond_code, url):
is_login_user = user_id is not None
try:
html = ''
cur = None
if bond_code != '':
cur = get_cursor(
"""select h.id,
c.data_id as nid,
c.stock_code,
hold_id,
h.bond_code,
h.cb_name_id as 名称,
price|| (case when amount > 0 then '(<font style=color:green>买入</font>)' else '(<font style=color:red>卖出</font>)' end) as 成交价,
abs(amount) as 成交量,
fee as 费用,
create_date as 成交时间,
account as 交易账号,
strategy_type as 策略类型
from trade_history h,
changed_bond c
where h.bond_code = c.bond_code
and owner_id = :user_id and h.is_delete = 0
and h.bond_code = :bond_code
order by create_date desc
limit 20""", {
'user_id': user_id,
'bond_code': bond_code
})
else:
cur = get_cursor(
"""select h.id,
c.data_id as nid,
c.stock_code,
hold_id,
h.bond_code,
h.cb_name_id as 名称,
price|| (case when amount > 0 then '(<font style=color:green>买入</font>)' else '(<font style=color:red>卖出</font>)' end) as 成交价,
abs(amount) as 成交量,
fee as 费用,
create_date as 成交时间,
account as 交易账号,
strategy_type as 策略类型
from trade_history h,
changed_bond c
where h.bond_code = c.bond_code
and owner_id = :user_id and h.is_delete = 0
order by create_date desc
limit 20""", {'user_id': user_id})
html = table_html_utils.generate_simple_table_html(
cur,
html,
is_login_user=is_login_user,
edit_link_maker=operation_html_content_maker)
html += """
<script>
function undo_trade(cur, id){
var r=confirm("确认要撤回?");
if (r == true){
tr = cur.parentElement.parentElement
$.get("/un_save_trade_data.html/" + id, function (data, status) {
// alert(data)
if (status == 'success') {
alert('undo save is successful')
tr.remove()
} else {
alert("undo trade is failure. id:" + id)
}
}).error(function (XMLHttpRequest, status, error){
// alert("occur exception. not find bond by code:" + $("#bond_code").val())
})
}
}
</script>
"""
return '我的可转债交易记录', views.nav_utils.build_personal_nav_html(url), html
except Exception as e:
print("processing is failure. ", e)
raise e
def do_fetch_data(driver, task_name):
task = None
try:
# 遍历整个可转债列表, 拿到bond_num
bond_cursor = get_cursor(
"""SELECT data_id, bond_code, cb_name_id from changed_bond""")
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
i = 0
j = 0
for bond_row in rows:
process_task_when_normal(task, 1)
num_id = bond_row[0]
bond_code = bond_row[1]
bond_name = bond_row[2]
bond_ex_cursor = get_cursor(
"""SELECT id, bond_name from changed_bond_extend where bond_num = :num_id""",
{
'num_id': num_id,
})
ex_list = list(bond_ex_cursor)
if len(ex_list) == 0:
# 检查是否存在extend信息, 没有则去抓数据
row = getContent(driver, num_id)
# 插入数据
get_cursor(
"""insert into changed_bond_extend(bond_num, bond_code,
interest, ensure, enforce_get_term, buy_back_term, down_revise_term)
values(:bond_num, :bond_code,
:interest, :ensure, :enforce_get_term, :buy_back_term, :down_revise_term)""",
{
'bond_num': num_id,
'bond_code': bond_code,
'interest': row.get('interest'),
'ensure': row.get('ensure'),
'enforce_get_term': row.get('enforce_get_term'),
'buy_back_term': row.get('buy_back_term'),
'down_revise_term': row.get('down_revise_term'),
})
print("insert " + bond_name + " is successful. count:" +
str(i + 1))
i += 1
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
elif ex_list[0][1] is None:
rowcount = execute_sql_with_rowcount(
"""update changed_bond_extend set bond_name = :bond_name where bond_num = :num_id""",
{
'bond_name': bond_name,
'num_id': num_id
})
if rowcount == 0:
print("update " + bond_name + " is failure. count:" +
str(j + 1))
else:
print("update " + bond_name + " is successful. count:" +
str(j + 1))
j += 1
ok_desc = "共处理" + str(i + j) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
raise e
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
raise e
def draw_view(is_login_user, url):
try:
html = ''
# =========我的转债价格TOP20柱状图=========
cur = get_cursor("""
select bond_code, cb_name_id as 名称, cb_price2_id as 转债价格, round(cb_mov2_id * 100, 2) as 涨跌, round(cb_premium_id*100,2) || '%' as 溢价率
from (SELECT DISTINCT c. * from changed_bond c, hold_bond h
where c.bond_code = h.bond_code and h.hold_owner = 'me' and h.hold_amount > 0 order by cb_price2_id DESC limit 20)
UNION
select bond_code, cb_name_id as 名称, cb_price2_id as 转债价格, round(cb_mov2_id * 100, 2) as 涨跌, round(cb_premium_id*100,2) || '%' as 溢价率
from (SELECT DISTINCT c. * from changed_bond c, hold_bond h
where c.bond_code = h.bond_code and h.hold_owner = 'me' and h.hold_amount > 0 order by cb_price2_id ASC limit 20)
order by 转债价格 desc
""")
rows = cur.fetchall()
html += '<br/>' + generate_price_bar_html(rows, '我的可转债价格高低TOP20')
cur = get_cursor("""
SELECT h.id as hold_id, c.data_id as nid, c.bond_code, c.stock_code, c.cb_name_id as 名称,
cb_price2_id as 转债价格, round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
h.hold_price || ' (' || h.hold_amount || ')' as '成本(量)',
round(c.cb_price2_id * h.hold_amount + sum_sell - sum_buy, 2) || '(' || round((c.cb_price2_id - h.hold_price) / c.cb_price2_id * 100, 2) || '%)' as 盈亏,
round(cb_premium_id*100,2) || '%' as 溢价率, round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',round(cb_trade_amount2_id * 100,2) || '%' as '换手率(%)',
h.strategy_type as 策略, c.stock_name as 正股名称, c.industry as '行业', c.sub_industry as '子行业',
round(cb_price2_id + cb_premium_id * 100,2) as 双低值,
round(cb_to_share_shares * 100,2) as '余额/股本(%)',
round(bt_yield*100,2) || '%' as 到期收益率,
rank_gross_rate ||'【' || level_gross_rate || '】' as 毛利率排名,rank_net_margin ||'【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio ||'【' || level_net_profit_ratio || '】' as 利润率排名, rank_roe ||'【' || level_roe || '】' as ROE排名,
rank_pe ||'【' || level_pe || '】' as PE排名, rank_pb ||'【' || level_pb || '】' as PB排名,
rank_net_asset ||'【' || level_net_asset || '】' as 净资产排名, rank_market_cap ||'【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue,2) as '营收(亿元)',s.yoy_revenue_rate || '%' as '营收同比',
gross_rate||'|' || avg_gross_rate as '毛利率|行业均值',
round(s.net,2)||'|' || avg_net_margin as '净利润|均值(亿元)', s.yoy_net_rate || '%' as '净利润同比',
s.margin ||'|' || avg_net_profit_ratio as '利润率|行业均值', s.yoy_margin_rate || '%' as '利润率同比',
s.roe ||'|' || avg_roe as 'ROE|行业均值', s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio,2) || '%' as 负债率, s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe||'|' || avg_pe as 'PE(动)|均值',
c.stock_pb||'|' || avg_pb as 'PB|行业均值',
net_asset||'|' || avg_net_asset as '净资产|行业均值',
market_cap||'|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' || fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用', duration as 续存期, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度, h.account as 账户, h.memo as 备注
from changed_bond c left join stock_report s on c.stock_code = s.stock_code , (select * from (SELECT DISTINCT h. * from changed_bond c, hold_bond h
where c.bond_code = h.bond_code and h.hold_owner = 'me' and h.hold_amount > 0 order by cb_price2_id DESC limit 10)
UNION
select * from (SELECT DISTINCT h. * from changed_bond c, hold_bond h
where c.bond_code = h.bond_code and h.hold_owner = 'me' and h.hold_amount > 0 order by cb_price2_id ASC limit 10)) h
where c.bond_code = h.bond_code order by cb_price2_id desc
""")
html = table_html_utils.generate_simple_table_html(
cur, html, is_login_user=is_login_user)
return '我的可转债价格top列表', views.nav_utils.build_personal_nav_html(
url), html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view(url):
try:
html = ''
ignore_words = (
'转债标的',
'上证180_',
'证金持股',
'预盈预增',
'HS300_',
'上证380',
'中证500',
'深成500',
'MSCI中国',
'沪股通',
'创业板综',
'深股通',
'标准普尔',
'融资融券',
'富时罗素',
'转债标的',
'广东板块',
'机构重仓',
'浙江板块',
'江苏板块',
'基金重仓',
'上海板块',
'北京板块',
'股权激励',
'创投',
'参股银行',
'QFII重仓',
'高送转',
'山东板块',
'深证100R',
'参股券商',
'股权转让',
'茅指数',
'养老金',
'军民融合',
'上证50_',
'京津冀',
'湖北自贸',
'IPO受益',
'AH股',
'央视50',
'央视50_',
'地塞米松',
'长江三角',
'参股保险',
'深圳特区',
'雄安新区',
'ST股',
'',
)
sql = """SELECT theme from changed_bond_extend"""
cur = get_cursor(sql)
rows = cur.fetchall()
word_count = {}
for row in rows:
if row[0] is None:
continue
words = row[0].split(' ')
for word in words:
if word in ignore_words or word.endswith('板块'):
continue
count = word_count.get(word, 0)
word_count[word] = count + 1
new_words = sorted(word_count.items(),
key=lambda x: x[1],
reverse=True)
words = []
for key, value in new_words:
# if value < 3:
# continue
words.append((key, value))
print(words)
chart = WordCloud(
opts.InitOpts(height='1000px',
width='1424px',
theme=ThemeType.MACARONS,
chart_id='cb_wordcloud'))
chart.add_js_funcs("""
chart_cb_wordcloud.on('click', function(x){
if(x.name == undefined) {return true;}
if ($('#cb_detail_list').length==0){
$(document.body).append('<div id=\\'cb_detail_list\\'></div>')
}
$.get('/view_cb_wordcloud_detail.html?key=' + encodeURIComponent(x.name), function(result){
$('#cb_detail_list').html(result)
$('body,html').animate({scrollTop: $('#cb_detail_list').offset().top}, 500);
})
})
""")
chart.add(
series_name="",
# 添加数据
data_pair=words,
# 字间隙
word_gap=5,
# 调整字大小范围
word_size_range=[5, 100],
# shape="cardioid",
is_draw_out_of_bound=True,
rotate_step=90,
# 选择背景图，也可以不加该参数，使用默认背景
# mask_image='购物车.jpg'
)
chart.set_global_opts(
title_opts=opts.TitleOpts(
title="",
title_textstyle_opts=opts.TextStyleOpts(font_size=23)),
tooltip_opts=opts.TooltipOpts(is_show=True),
)
html = """
<center>
<input type='text' id='theme_key_word'> <input type='button' value='查 询' id='btn_query' onclick="find_by_key_word()">
<script>
//回车执行查询事件(执行class='btn-query'的单击事件)
$(document).keydown(function (event) {
if (event.keyCode == "13") {
//回车执行的事件
$("#btn_query").click();
return;
}
});
function find_by_key_word(){
if ($('#cb_detail_list').length==0){
$(document.body).append('<div id=\\'cb_detail_list\\'></div>');
}
$.get('/view_cb_wordcloud_detail.html?key=' + encodeURIComponent($('#theme_key_word').val()),
function(result){
$('#cb_detail_list').html(result);
$('body,html').animate({scrollTop: $('#cb_detail_list').offset().top}, 500);
}
)
}
</script>
</center>""" + chart.render_embed('template.html', env)
return '可转债概念分析', views.nav_utils.build_analysis_nav_html(url), html
except Exception as e:
print("processing is failure. ", e)
raise e
def draw_view():
try:
html = ''
nav_html_list = {}
# 用来画收益曲线图的数据
cur = get_cursor("""
SELECT
date as 时间,
day_rate_1 || '%' as 低溢价率策略日收益率,
day_rate_2 || '%' as 高收益率策略日收益率,
day_rate_3 || '%' as '低余额+双低策略日收益率',
day_rate_4 || '%' as '低溢价率+双低策略日收益率',
--cb_day_yield || '%' as 可转债指数日收益率,
--hs_day_yield || '%' as 沪深300日收益率,
--round(cb_all_yield-1.84, 2) as 可转债指数累积收益率,
--round(hs_all_yield-0.06, 2) as 沪深300累积收益率,
all_rate_1 as 低溢价率策略累积收益率,
all_rate_2 as 高收益率策略累积收益率,
all_rate_3 as '低余额+双低策略累积收益率',
all_rate_4 as '低溢价率+双低策略累积收益率'
from strategy_group_yield
order by date desc
""")
# 日收益率看表格, 累积收益率看折线图
table, table_html = generate_table_html_with_data(
None,
cur,
html,
need_title=False,
remark_fields=[
'可转债指数日收益率', '沪深300日收益率', '低溢价率策略日收益率', '高收益率策略日收益率',
'低余额+双低策略日收益率', '低溢价率+双低策略日收益率'
],
ignore_fields=[
'可转债指数累积收益率', '沪深300累积收益率', '低余额+双低策略累积收益率', '低溢价率+双低策略累积收益率',
'高收益率策略累积收益率', '低溢价率策略累积收益率'
],
nav_html_list=nav_html_list,
table_width='800px')
rows = []
dict_rows = []
for row in table._rows:
rows.append(row)
dict_row = db_utils.get_dict_row(cur, row)
dict_rows.append(dict_row)
line_html = generate_line_html(dict_rows)
html = """<center>
""" + line_html + "<br/><br/><br/>" + table_html + """
</center>
"""
return html
except Exception as e:
print("processing is failure. ", e)
raise e
def update_stock_sum(driver, task_name):
# 遍历可转债列表
task = None
try:
# 查询可转债
bond_cursor = get_cursor(
"""SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond"""
)
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
# 当前日月
y = datetime.datetime.now().year
m = datetime.datetime.now().month
d = datetime.datetime.now().day
t = datetime.datetime(y, m, d)
# 记录更新时间(秒)
s = (t - datetime.datetime(1970, 1, 1)).total_seconds()
i = 0
for bond_row in rows:
process_task_when_normal(task, 1)
stock_code = bond_row[2]
stock_name = bond_row[3]
stock_cursor = get_cursor(
"""SELECT modify_date from stock_report where stock_code = :stock_code""",
{'stock_code': stock_code})
stocks = list(stock_cursor)
if len(stocks) == 0:
continue
# 已经更新了
if stocks[0][0] is not None and stocks[0][0] >= s:
continue
row = get_stock_sum(driver, stock_code)
rowcount = execute_sql_with_rowcount(
"""update stock_report set
stock_total = :stock_total,
stock_level = :stock_level,
trade_suggest = :trade_suggest,
fact_trend = :fact_trend,
fact_money = :fact_money,
fact_news = :fact_news,
fact_industry = :fact_industry,
fact_base = :fact_base,
modify_date = :modify_date where stock_code = :stock_code""", {
'stock_total': row['stock_total'],
'stock_level': row['stock_level'],
'trade_suggest': row['trade_suggest'],
'fact_trend': row['fact_trend'],
'fact_money': row['fact_money'],
'fact_news': row['fact_news'],
'fact_industry': row['fact_industry'],
'fact_base': row['fact_base'],
'modify_date': s,
'stock_code': stock_code
})
if rowcount == 0:
print("not update stock:" + stock_name)
else:
print("update_diagnostic " + stock_name +
" is successful. count:" + str(i + 1))
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(3)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
raise e
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
raise e
def generate_detail(key, is_login_user):
cur = get_cursor(
"""
SELECT DISTINCT d.*,
e.strategy_type as 策略,
e.hold_id,
e.hold_price as 持有成本,
e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
case when cb_mov2_id > 0 then 1 when cb_mov2_id = 0 then 0 ELSE -1 end as _sign,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
round(cb_premium_id * 100, 2) || '%' as 溢价率,
cb_price2_id as '转债价格',
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
remain_amount as '余额(亿元)',
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
cb_trade_amount_id as '成交额(百万)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度
from (select *
from (SELECT DISTINCT c.*
from changed_bond c WHERE sub_industry = :sub_industry)
) c
LEFT join stock_report s on c.stock_code = s.stock_code) d
left join
(select id as hold_id, bond_code, hold_price, hold_amount, strategy_type
from hold_bond
where id in (select id
from hold_bond
where id
in (SELECT min(id)
from hold_bond
where hold_owner = 'me' and hold_amount != -1
group by bond_code))
) e
on d.bond_code = e.bond_code
order by 溢价率
""", {'sub_industry': key})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
ignore_fields=['持有数量'],
is_login_user=is_login_user)
# html = generate_scatter_html_with_one_table(table,
# title=key + '行业可转债',
# use_personal_features=is_login_user)
# html = table_html
return table_html
def generate_detail(key, is_login_user):
cur = get_cursor(
"""
SELECT DISTINCT d.*, e.hold_id, e.hold_amount as 持有数量
FROM (
SELECT c.data_id as nid,
c.bond_code,
c.stock_code,
c.cb_name_id as 名称,
round(cb_premium_id * 100, 2) || '%' as 溢价率,
cb_price2_id as '转债价格',
cb_t_id as 距离转股日,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值,
round(bt_yield * 100, 2) || '%' as 到期收益率,
round(cb_trade_amount2_id * 100, 2) || '%' as '换手率(%)',
round(cb_mov2_id * 100, 2) || '%' as 可转债涨跌,
round(cb_mov_id * 100, 2) || '%' as 正股涨跌,
c.stock_name as 正股名称,
c.industry as '行业',
c.sub_industry as '子行业',
e.theme as 题材概念,
rank_gross_rate || '【' || level_gross_rate || '】' as 毛利率排名,
rank_net_margin || '【' || level_net_margin || '】' as 净利润排名,
rank_net_profit_ratio || '【' || level_net_profit_ratio || '】' as 利润率排名,
rank_roe || '【' || level_roe || '】' as ROE排名,
rank_pe || '【' || level_pe || '】' as PE排名,
rank_pb || '【' || level_pb || '】' as PB排名,
rank_net_asset || '【' || level_net_asset || '】' as 净资产排名,
rank_market_cap || '【' || level_market_cap || '】' as 市值排名,
stock_total as 综合评分,
round(s.revenue, 2) as '营收(亿元)',
s.yoy_revenue_rate || '%' as '营收同比',
gross_rate || '|' || avg_gross_rate as '毛利率|行业均值',
round(s.net, 2) || '|' || avg_net_margin as '净利润|均值(亿元)',
s.yoy_net_rate || '%' as '净利润同比',
s.margin || '|' || avg_net_profit_ratio as '利润率|行业均值',
s.yoy_margin_rate || '%' as '利润率同比',
s.roe || '|' || avg_roe as 'ROE|行业均值',
s.yoy_roe_rate || '%' as 'ROE同比',
round(s.al_ratio, 2) || '%' as 负债率,
s.yoy_al_ratio_rate || '%' as '负债率同比',
s.pe || '|' || avg_pe as 'PE(动)|均值',
c.stock_pb || '|' || avg_pb as 'PB|行业均值',
net_asset || '|' || avg_net_asset as '净资产|行业均值',
market_cap || '|' || avg_market_cap as '市值|均值(亿元)',
remain_amount as '余额(亿元)',
round(cb_to_share_shares * 100, 2) || '%' as '余额/股本(%)',
fact_trend || '|' || fact_money || '|' || fact_news || '|' || fact_industry || '|' ||
fact_base as '技术|资金|消息|行业|基本面',
trade_suggest as 操作建议,
rating as '信用',
duration as 续存期,
cb_ma20_deviate as 'ma20乖离',
cb_hot as 热门度,
cb_trade_amount_id as '成交额(百万)',
e.interest as 各期利息,
case when e.ensure is not null then '有' else '无' END as 担保,
case when e.buy_back_term is not null then e.buy_back_term else '无' END as 回售条款,
case when e.down_revise_term is not null then e.down_revise_term else '无' END as 下修条款,
case when e.enforce_get_term is not null then e.enforce_get_term else '无' END as 强赎条款
from (changed_bond c left join stock_report s on c.stock_code = s.stock_code)
left join changed_bond_extend e on c.bond_code = e.bond_code where theme like :theme
) d
left join
(select id as hold_id, bond_code, cb_name_id, hold_price, hold_amount
from hold_bond
where id in (select id
from hold_bond
where id
in
(SELECT min(id) from hold_bond where hold_owner = 'me' and hold_amount > 0 group by bond_code))
) e
on d.bond_code = e.bond_code
""", {'theme': '%' + key + '%'})
table, table_html = generate_table_html_with_data(
'',
cur,
'',
need_title=False,
remark_fields=['盈亏', '到期收益率', '溢价率', '可转债涨跌', '正股涨跌'],
ignore_fields=['持有数量'],
is_login_user=is_login_user)
html = generate_scatter_html_with_one_table(
table, title=key + '概念相关可转债', use_personal_features=is_login_user)
html += table_html
return html
