forked from macrochen/cb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcb.sqbpro
108 lines (98 loc) · 8.35 KB
/
cb.sqbpro
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="/Users/macrochen/PycharmProjects/cb/cb.db3" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="1"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="5992"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,12:mainchanged_bond"/><default_encoding codec=""/><browse_table_settings/></tab_browse><tab_sql><sql name="SQL 1">SELECT c.bond_code as 转债编码, c.cb_name_id as 名称, c.stock_code as 正股代码, c.stock_name as 正股名称, c.industry as 行业, c.sub_industry as 子行业,
cb_price2_id as 转债价格, round(cb_premium_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 '负债率同比(%)', last_date as 报告期, stock_pb as PB,
market_cap as '股票市值(亿元)', remain_amount as '转股余额(亿元)', round(cb_to_share_shares * 100,2) as '余额/股本(%)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值
--, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度
from changed_bond c, stock_report s
where c.bond_code = s.bond_code
and duration < 3
-- and cb_price2_id > 108
and cb_price2_id < 120
and roe > 5
and s.net > 0
and s.margin > 10
and cb_t_id = '转股中'
-- and 溢价率 < 20
-- and 双低值 < 120
order by 双低值 ASC
;
INSERT insert into changed_bond(bond_code,cb_name_id,account)values(?,?,)</sql><sql name="SQL 4">SELECT c.bond_code as 转债编码, c.cb_name_id as 名称, c.stock_code as 正股代码, c.stock_name as 正股名称, c.industry as 行业, c.sub_industry as 子行业,
cb_price2_id as 转债价格, round(cb_premium_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 '负债率同比(%)', last_date as 报告期, stock_pb as PB,
market_cap as '股票市值(亿元)', remain_amount as '转股余额(亿元)', round(cb_to_share_shares * 100,2) as '余额/股本(%)',
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值
--, cb_ma20_deviate as 'ma20乖离', cb_hot as 热门度
from changed_bond c, stock_report s, hold_bond h
where c.bond_code = s.bond_code and c.bond_code = h.bond_code
and h.strategy_type is NULL
-- and duration < 3
-- and cb_price2_id > 108
-- and cb_price2_id < 120
-- and roe > 5
-- and s.net > 0
-- and s.margin > 10
-- and cb_t_id = '转股中'
-- and 溢价率 < 20
-- and 双低值 < 120
order by 双低值 ASC;
SELECT cb_num_id, cb_name_id as 名称, rating as 信用, cb_price2_id as 转债价格, bt_yield*100 as 收益率, round(100- cb_price2_id + BT_yield * 100, 2) as 性价比
from changed_bond cb
WHERE
cb.rating in ('AA+', 'AA-', 'AA', 'AAA', 'A', 'A+')
-- cb_name_id not in( '亚药转债' , '本钢转债','搜特转债','广汇转债')
AND bt_yield > -0
and cb_price2_id < 110
order by 转债价格 ASC, 收益率 DESC
limit 30;
SELECT cb_num_id, cb_name_id as 名称, rating as 信用, cb_price2_id as 转债价格, bt_yield*100 as 收益率, round(100- cb_price2_id + BT_yield * 100, 2) as 性价比
from changed_bond cb
WHERE
cb.rating in ('AA+', 'AA-', 'AA', 'AAA', 'A', 'A+')
-- cb_name_id not in( '亚药转债' , '本钢转债','搜特转债','广汇转债')
AND bt_yield > -0
and cb_price2_id < 110
order by 转债价格 ASC, 收益率 DESC
limit 30;
</sql><sql name="SQL 5">select c.bond_code as 转债编码, c.cb_name_id as 名称, c.stock_code as 正股代码, c.stock_name as 正股名称, c.industry as 行业, c.sub_industry as 子行业,
cb_price2_id as 转债价格, round(cb_premium_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 '利润率同比(%)', round(s.roe, 2) as 'ROE(%)', s.yoy_roe_rate as 'ROE同比(%)', round(s.al_ratio,2) as 负债率, s.yoy_al_ratio_rate as '负债率同比(%)', last_date as 报告期, stock_pb as PB,
market_cap as '股票市值(亿元)', remain_amount as '转股余额(亿元)', round(cb_to_share_shares * 100,2) as '余额/股本(%)',
cb_hot as 热度, cb_ma20_deviate as 'ma20乖离率',elasticity as 弹性, cb_t_id as 距离转股日,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值
from changed_bond c, stock_report s, hold_bond h
where c.bond_code = s.bond_code and c.bond_code = h.bond_code and h.strategy_type is null
order by 双低值 ASC</sql><sql name="SQL 7">SELECT c.bond_code as 转债编码, c.cb_name_id as 名称, c.stock_code as 正股代码, c.stock_name as 正股名称, c.industry as 行业, c.sub_industry as 子行业,
cb_price2_id as 转债价格, round(cb_premium_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 '负债率同比(%)', last_date as 报告期, stock_pb as PB,
market_cap as '股票市值(亿元)', remain_amount as '转股余额(亿元)', round(cb_to_share_shares * 100,2) as '余额/股本(%)',
cb_hot as 热度, cb_ma20_deviate as 'ma20乖离率', elasticity as 弹性,cb_t_id as 距离转股日,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值
from changed_bond c, stock_report s
--, hold_bond h
where c.bond_code = s.bond_code
--and c.bond_code = h.bond_code
and c.sub_industry = '通信设备及工程'
-- and industry = '城市公用设施'
order by 正股名称</sql><sql name="SQL 8">SELECT c.bond_code as 转债编码, c.cb_name_id as 名称, c.stock_code as 正股代码, c.stock_name as 正股名称, c.industry as 行业, c.sub_industry as 子行业,
cb_price2_id as 转债价格, round(cb_premium_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 '利润率同比(%)', round(s.roe, 2) as 'ROE(%)', s.yoy_roe_rate as 'ROE同比(%)', round(s.al_ratio,2) as 负债率, s.yoy_al_ratio_rate as '负债率同比(%)', last_date as 报告期, stock_pb as PB,
market_cap as '股票市值(亿元)', remain_amount as '转股余额(亿元)', round(cb_to_share_shares * 100,2) as '余额/股本(%)',
cb_hot as 热度, cb_ma20_deviate as 'ma20乖离率',elasticity as 弹性, cb_t_id as 距离转股日,
round(cb_price2_id + cb_premium_id * 100, 2) as 双低值
from changed_bond c, stock_report s
where c.bond_code = s.bond_code
and duration < 3
and cb_price2_id > 108 and cb_price2_id < 125
--and roe > 5
and s.net > 0
-- and s.margin > 10
and cb_t_id = '转股中'
-- and 溢价率 < 20
and 双低值 < 120
order by 双低值 ASC</sql><sql name="SQL 9"></sql><current_tab id="2"/></tab_sql></sqlb_project>