diff options
| -rw-r--r-- | python/collateral_calc.py | 2 | ||||
| -rw-r--r-- | sql/dawn.sql | 19 |
2 files changed, 19 insertions, 2 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 8750d8b1..fd530f19 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -158,7 +158,7 @@ def baml_collateral(d): index_col=['REDCODE', 'PERIOD']) df = df[df.EODSETTLEMENTPRICE.notnull()] positions = pd.read_sql_query("SELECT security_id, security_desc, folder, notional, currency " - "FROM list_cds_positions_by_strat(%s)", + "FROM list_cds_positions_by_strat_fcm(%s, 'BAML')", dawn_engine, params=(d.date(),), index_col=['security_id']) df_helper = pd.read_sql_query("SELECT redindexcode, upper(tenor::text) AS tenor, " diff --git a/sql/dawn.sql b/sql/dawn.sql index f8d20507..935f666b 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -678,6 +678,23 @@ RETURN QUERY END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_cds_positions_by_strat_fcm(p_date date, + fcm text) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + fixed_rate float, currency currency, folder cds_strat, notional float) AS $$ +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)) + OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code) AS notional + FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND + cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' + AND cds.account_code=fcm::text) + SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION cds_globeop_name(redcode text, fixed_rate float, maturity date, index index_type, tenor tenor) RETURNS text AS $$ @@ -1472,7 +1489,7 @@ CREATE TYPE clearing_cp AS ENUM('ICE-CREDIT', 'NOT CLEARED'); CREATE TYPE trade_type AS ENUM('CREDIT_DEFAULT_SWAP', 'SWAPTION', 'TRANCHE'); CREATE TYPE transaction_status AS ENUM('Bilateral', 'Cleared'); CREATE TYPE calendar AS ENUM('Payment-GB,US', 'Payment-US,GB', 'Payment-EU,GB'); -CREATE TYPE clearing_broker AS ENUM('ML', 'SGFCM', 'BOMLCM'); +CREATE TYPE clearing_broker AS ENUM('ML', 'SGFCM', 'BOMLCM', 'WELLSFCM'); CREATE TYpe frequency AS ENUM('Monthly', 'Quarterly'); CREATE TABLE cds_reports( |