diff options
| -rw-r--r-- | python/analytics/tranche_basket.py | 3 | ||||
| -rw-r--r-- | python/risk/tranches.py | 44 | ||||
| -rw-r--r-- | sql/dawn.sql | 17 |
3 files changed, 33 insertions, 31 deletions
diff --git a/python/analytics/tranche_basket.py b/python/analytics/tranche_basket.py index 0e431173..53303cc6 100644 --- a/python/analytics/tranche_basket.py +++ b/python/analytics/tranche_basket.py @@ -178,7 +178,8 @@ class DualCorrTranche: "WHERE redindexcode=%s AND maturity = %s", (redcode, maturity), ) - index_type, series, tenor = c.fetchone() + index_type, series, tenor = c.fetchone() + serenitas_pool.putconn(conn) self._index = BasketIndex(index_type, series, [tenor], value_date=value_date) self.index_type = index_type diff --git a/python/risk/tranches.py b/python/risk/tranches.py index 93c433f4..ecd4672a 100644 --- a/python/risk/tranches.py +++ b/python/risk/tranches.py @@ -9,38 +9,34 @@ def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST", **kwargs sql_string = "SELECT * from list_tranche_positions_by_strat(%s, %s)" params = (date, fund) else: - sql_string = ( - "SELECT folder, id from list_cds(%s, %s) " - "WHERE orig_attach IS NOT NULL ORDER BY trade_date" - ) + sql_string = "SELECT * FROM list_cds(%s, %s) WHERE orig_attach IS NOT NULL " params = (date, fund) with conn.cursor() as c: c.execute(sql_string, params) - trade_ids = [tuple(e) for e in c] + trade_ids = list(c) + + portf = Portfolio( + [ + DualCorrTranche( + redcode=t.security_id, + maturity=t.maturity, + notional=t.notional, + tranche_running=t.fixed_rate * 100, + attach=t.orig_attach, + detach=t.orig_detach, + corr_attach=None, + corr_detach=None, + ) + for t in trade_ids + ] + ) if by_strat: - portf = Portfolio( - [ - DualCorrTranche( - redcode=t.security_id, - maturity=t.maturity, - notional=t.notional, - tranche_running=t.fixed_rate * 100, - attach=t.orig_attach, - detach=t.orig_detach, - corr_attach=None, - corr_detach=None, - ) - for t in trade_ids - ] - ) portf.trade_ids = [ (tid.folder, f"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}") for tid, t in zip(trade_ids, portf.trades) ] else: - portf = Portfolio( - [DualCorrTranche.from_tradeid(dealid) for _, dealid in trade_ids], trade_ids - ) + portf.trade_ids = [(t.folder, t.id) for t in trade_ids] portf.value_date = date portf.mark(**kwargs) return portf @@ -50,6 +46,7 @@ def insert_tranche_portfolio(portf, conn): cols = [ "date", "tranche_id", + "notional", "clean_nav", "accrued", "duration", @@ -90,6 +87,7 @@ def insert_tranche_portfolio(portf, conn): ( trade.value_date, trade_id, + trade.notional, trade.clean_pv, trade.accrued, trade.duration, diff --git a/sql/dawn.sql b/sql/dawn.sql index ad552352..e55ee7bb 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -647,7 +647,7 @@ DECLARE BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, -cds.fixed_rate, cds.currency, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN 1 ELSE -1 END)) +cds.fixed_rate, cds.currency, %s SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach) AS notional FROM list_cds($1, $2) cds WHERE %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; @@ -691,7 +691,9 @@ RETURNS SETOF LIST_CDS AS $$ BEGIN RETURN QUERY SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, - folder, protection, notional - coalesce(terminated_amount, 0.) AS notional, + folder, protection, (notional - coalesce(terminated_amount, 0.)) * + (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END) + AS notional, orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage, cpty_id FROM cds LEFT JOIN ( @@ -729,7 +731,7 @@ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder) AS notional FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS')) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; @@ -744,7 +746,7 @@ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code) AS notional FROM list_cds(p_date) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fcm=fcm)) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; @@ -798,7 +800,7 @@ RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.orig_attach, cds.orig_detach, cds.attach, cds.detach, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder) AS notional FROM list_cds(p_date, p_fund) cds WHERE cds.orig_attach is NOT NULL) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.orig_attach, tmp.folder) * FROM tmp WHERE tmp.notional!=0; @@ -955,6 +957,7 @@ $$ LANGUAGE plpgsql; CREATE TABLE tranche_risk( date date, tranche_id integer REFERENCES cds(id), + notional float, clean_nav float, accrued float, duration float, @@ -1732,8 +1735,8 @@ $$ LANGUAGE plpython3u; CREATE OR REPLACE VIEW globeop_tranche_risk AS SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, - orig_detach, CASE protection WHEN 'Buyer' THEN notional ELSE -notional END AS notional, - endqty as globeop_notional, + orig_detach, tranche_risk.notional, + globeop_notional, clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, globeop_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, globeop_accrued, |