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