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
|
BOND_QUERY = "SELECT * FROM risk_positions(%s, null, %s) "
FUTURE_QUERY = """
WITH tmp AS (
SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell,
sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional
FROM futures
WHERE trade_date <= %s AND fund=%s
)
SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell
FROM tmp
LEFT JOIN accounts USING (fund)
WHERE tmp.notional != 0 AND account_type='Future';
"""
TRANCHE_QUERY = """
SELECT trm.trade_id,
trm.serenitas_clean_nav + trm.serenitas_accrued AS mtm,
trm.notional * trm.tranche_factor AS active_notional,
cds.*,
b.redindexcode
FROM tranche_risk_master trm
LEFT JOIN cds ON trm.trade_id = cds.id
LEFT JOIN LATERAL (
SELECT INDEX, series, redindexcode
FROM index_version iv
WHERE iv.series = trm.series
AND iv.index = trm.index
AND lastdate >= trm.date
) b ON TRUE
WHERE trm.date = %s
AND cds.fund = %s
"""
CDX_SWAPTION_QUERY = """
SELECT abs(spr.notional) AS active_notional,
spr.serenitas_nav,
swaptions.*,
ivm.annexdate
FROM list_swaption_positions_and_risks(%s, %s) spr
LEFT JOIN swaptions ON spr.deal_id = swaptions.dealid
LEFT JOIN index_version_markit ivm ON swaptions.security_id = ivm.redindexcode;
"""
IR_SWAPTION_QUERY = """
SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate
FROM list_ir_swaption_positions(%s, %s) spr
LEFT JOIN swaptions ON deal_id=dealid
LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;
"""
CDX_QUERY = """
SELECT cds.*, ivm.effectivedate
FROM list_cds_marks(%s, null, %s) cds
LEFT JOIN index_version_markit ivm ON security_id=redindexcode;
"""
IRS_QUERY = """
SELECT isr.pv, irs.*, accounts2.name
FROM ir_swap_risk isr
LEFT JOIN irs ON id=swp_id
LEFT JOIN accounts2 USING (cash_account)
WHERE date=%s AND irs.fund=%s;
"""
|
