diff options
| -rw-r--r-- | python/collateral/common.py | 2 | ||||
| -rw-r--r-- | sql/dawn.sql | 19 |
2 files changed, 20 insertions, 1 deletions
diff --git a/python/collateral/common.py b/python/collateral/common.py index d4e9601b..efce18a8 100644 --- a/python/collateral/common.py +++ b/python/collateral/common.py @@ -60,7 +60,7 @@ def compare_notionals(df: pd.DataFrame, positions: pd.DataFrame, fcm: str) -> No def get_bilateral_trades(d: datetime.date, fund: str, engine: Engine) -> pd.DataFrame: df_cds = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * abs(notional) / 100 as IA " - "FROM list_cds(%s::date, %s) " + "FROM list_cds2(%s::date, %s) " "WHERE cp_code IS NOT NULL", # that way we get all tranches + the ABS_CDS engine, params=(d, fund), diff --git a/sql/dawn.sql b/sql/dawn.sql index b2707cc3..fc01d1bd 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1030,6 +1030,25 @@ WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_cds2(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) +-- Do not include unsettled terminations +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.)) * + (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, + cp_code +FROM cds LEFT JOIN ( + SELECT dealid, SUM(termination_amount) AS terminated_amount + FROM terminations WHERE fee_payment_date <= p_date GROUP BY dealid) b USING (dealid) +WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount + AND trade_date <= p_date AND maturity > p_date; +END; +$$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions (p_date date, strat cds_strat DEFAULT NULL::cds_strat, |