diff options
| -rw-r--r-- | python/process_queue.py | 25 | ||||
| -rw-r--r-- | python/risk/tranches.py | 9 | ||||
| -rw-r--r-- | sql/dawn.sql | 37 |
3 files changed, 47 insertions, 24 deletions
diff --git a/python/process_queue.py b/python/process_queue.py index 73242324..fe701dc2 100644 --- a/python/process_queue.py +++ b/python/process_queue.py @@ -345,16 +345,22 @@ def build_termination( with dawndb.cursor() as c: c.execute( - f"SELECT dealid, cp_code, notional FROM {table} where id=%s", (dealid,) + "SELECT dealid, cp_code, notional, termination_amount, globeop_id " + f"FROM {table} where id=%s", + (dealid,), ) - dealid, cp_code, notional = c.fetchone() + dealid, cp_code, notional, partial_amounts, globeopid = c.fetchone() + remaining_notional = notional - sum(partial_amounts) + termination_amount = termination_amount or remaining_notional c.execute( f"UPDATE {table} " - "SET termination_amount=%s, termination_cp=%s, termination_fee=%s, " - "termination_date=%s " + "SET termination_amount=termination_amount||%s::float8, " + "termination_cp=termination_cp||%s::text, " + "termination_fee=termination_fee||%s::float8, " + "termination_date=termination_date||%s::date " "WHERE dealid=%s", ( - termination_amount or notional, + termination_amount, termination_cp or cp_code, fee, termination_date, @@ -415,17 +421,18 @@ def build_termination( headers += ["InitialMargin", "InitialMarginCurrency"] if termination_cp is None: headers += ["Reserved"] * 4 + ["CreditEventOccured"] - d = { "DealType": deal_type, - "DealId": dealid, + "GoTradeId": int(globeopid[3:9]), "Action": "Update", "Client": "Serenitas", "SubAction": "Termination", - "PartialTermination": "N" if termination_amount is None else "Y", + "PartialTermination": "Y" + if remaining_notional - termination_amount > 0 + else "N", "TerminationAmount": termination_amount, "TerminationDate": termination_date, - "FeesPaid": fee if fee < 0 else None, + "FeesPaid": -fee if fee < 0 else None, "FeesReceived": fee if fee > 0 else None, "FeePaymentDate": (termination_date + 3 * bus_day).date(), } diff --git a/python/risk/tranches.py b/python/risk/tranches.py index 8c41b523..c6b41c3e 100644 --- a/python/risk/tranches.py +++ b/python/risk/tranches.py @@ -10,13 +10,10 @@ def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST"): params = (date, fund) else: sql_string = ( - "SELECT folder, id from cds " - "WHERE orig_attach IS NOT NULL " - "AND (termination_date IS NULL OR termination_date > %s) " - "AND maturity > %s AND trade_date <= %s " - "AND fund = %s ORDER BY trade_date" + "SELECT folder, id from list_cds(%s, %s) " + "WHERE orig_attach IS NOT NULL ORDER BY trade_date" ) - params = (date, date, date, fund) + params = (date, fund) with conn.cursor() as c: c.execute(sql_string, params) trade_ids = [tuple(e) for e in c] diff --git a/sql/dawn.sql b/sql/dawn.sql index f39e01fd..ff6e7de8 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -662,6 +662,7 @@ END; $$ LANGUAGE plpgsql; CREATE TYPE LIST_CDS AS( + id integer, trade_date date, security_id varchar(12), security_desc varchar(32), @@ -675,22 +676,24 @@ CREATE TYPE LIST_CDS AS( orig_detach smallint, attach float, detach float, - fcm text + fcm text, + initial_margin_percentage float ); CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS SETOF LIST_CDS AS $$ BEGIN RETURN QUERY -SELECT trade_date, security_id, security_desc, maturity, fixed_rate, currency, +SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, folder, protection, notional - coalesce(terminated_amount, 0.) AS notional, - orig_attach, orig_detach, attach, detach, account_code + orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage FROM cds LEFT JOIN ( SELECT id, SUM(termination_amount ) AS terminated_amount FROM (SELECT id, unnest(termination_amount) as termination_amount, unnest(termination_date) as termination_date FROM cds) a WHERE termination_date <= p_date GROUP BY id) b USING (id) - WHERE fund=p_fund and notional != 0. and trade_date <= p_date; +WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount + AND trade_date <= p_date AND maturity > p_date; END; $$ LANGUAGE plpgsql; @@ -791,8 +794,7 @@ RETURN QUERY cds.attach, cds.detach, cds.folder, SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder) AS notional - FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND - cds.trade_date <= p_date AND cds.orig_attach is NOT NULL AND cds.fund=p_fund) + 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; END; $$ LANGUAGE plpgsql; @@ -1774,8 +1776,8 @@ FROM swaptions JOIN counterparties ON cp_code = code CREATE OR REPLACE VIEW cds_trades AS SELECT id, dealid, - termination_date as trade_date, - notional, + termination_date AS trade_date, + termination_amount AS notional, security_desc, security_id, CASE WHEN termination_cp=cp_code THEN @@ -1798,7 +1800,24 @@ SELECT id, END AS protection, -termination_fee AS upfront, ref -FROM cds JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL +FROM (select id, + dealid, + unnest(termination_date) AS termination_date, + unnest(termination_amount) AS termination_amount, + security_desc, + security_id, + unnest(termination_cp) AS termination_cp, + folder, + fund, + orig_attach, + orig_detach, + attach, + detach, + protection, + unnest(termination_fee) AS termination_fee, + cp_code, + ref from cds where termination_date is not null) a +left JOIN counterparties on termination_cp=code UNION ALL ( SELECT id, dealid, |