diff options
| -rw-r--r-- | python/collateral/__main__.py | 2 | ||||
| -rw-r--r-- | python/report_ops/queries.py | 6 | ||||
| -rw-r--r-- | python/report_ops/sma.py | 1 | ||||
| -rw-r--r-- | sql/dawn.sql | 48 |
4 files changed, 38 insertions, 19 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index 2f985c62..327ba97d 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -132,7 +132,7 @@ for fund in funds: ) positions_rates = pd.read_sql_query( "SELECT maturity_date, (fixed_rate /100)::NUMERIC(4, 4) as fixed_rate, float_index, currency, roll_day, notional, folder " - "FROM list_ir_positions(%s, %s) ", + "FROM ir_swap_risk_master WHERE date=%s AND fund=%s", dawn_engine, params=( workdate, diff --git a/python/report_ops/queries.py b/python/report_ops/queries.py index c4955973..c29ec5e4 100644 --- a/python/report_ops/queries.py +++ b/python/report_ops/queries.py @@ -57,8 +57,6 @@ LEFT JOIN index_version_markit ivm ON security_id=redindexcode; """ IRS_QUERY = """ -SELECT * FROM list_ir_positions(%s, %s) a -LEFT JOIN LATERAL (SELECT sum(pv) AS mtm_valuation -FROM ir_swap_risk WHERE swp_id = ANY(a.ids) AND date=a.date) -b ON true; +SELECT * FROM ir_swap_risk_master WHERE +date=%s AND fund=%s """ diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py index 7db30189..764c4a00 100644 --- a/python/report_ops/sma.py +++ b/python/report_ops/sma.py @@ -391,6 +391,7 @@ class IRSPosition(PositionReport, asset_class="irs"): "float_index": "identifier", "cash_account": "account", "clearing_facility": "clearing_house", + "pv": "mtm_valuation", }, ) d["dealid"] = "COMPRESSED" diff --git a/sql/dawn.sql b/sql/dawn.sql index 277ad0d9..cc4fda41 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1922,20 +1922,40 @@ AND fund=p_fund; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION list_ir_positions(p_date date, p_fund fund) - RETURNS TABLE(date date, effective_date date, maturity_date date, fixed_rate double precision, fund fund, folder strategy, cash_account text, float_index cash_rate, currency currency, clearing_facility clearing_cp, roll_day roll_day, notional double precision, ids integer[]) - LANGUAGE plpgsql -AS $function$ -BEGIN - RETURN QUERY SELECT p_date, i.effective_date, i.maturity_date, i.fixed_rate, i.fund, i.folder, i.cash_account, i.float_index, i.currency, i.clearing_facility, i.roll_day, - sum(CASE WHEN payreceive THEN i.notional ELSE -i.notional end) AS notional, array_agg(i.id) - FROM irs i - WHERE i.fund = $2 AND i.trade_date <= $1 - GROUP BY i.effective_date, i.maturity_date, i.fixed_rate, i.fund, i.folder, i.cash_account, i.float_index, i.currency, i.clearing_facility, i.roll_day - HAVING sum(CASE WHEN payreceive THEN i.notional ELSE -i.notional end) != 0; -END; -$function$ -; +CREATE VIEW ir_swap_risk_master AS SELECT + isr.date, + irs.effective_date, + irs.maturity_date, + irs.fixed_rate, + irs.fund, + irs.folder, + irs.cash_account, + irs.float_index, + irs.currency, + irs.clearing_facility, + irs.roll_day, + sum(CASE WHEN irs.payreceive THEN irs.notional ELSE -irs.notional end) AS notional, + sum(pv) AS pv, + sum("DV01") AS "DV01", + sum("IRGamma1bp") AS "IRGamma1bp" +FROM + ir_swap_risk isr +LEFT JOIN + irs ON isr.swp_id=irs.id +GROUP BY + isr.date, + irs.effective_date, + irs.maturity_date, + irs.fixed_rate, + irs.fund, + irs.folder, + irs.cash_account, + irs.float_index, + irs.currency, + irs.clearing_facility, + irs.roll_day +HAVING + sum(CASE WHEN irs.payreceive THEN irs.notional ELSE -irs.notional end) !=0; CREATE OR REPLACE function list_abscds_marks(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) |