diff options
Diffstat (limited to 'python/report_ops/sma.py')
| -rw-r--r-- | python/report_ops/sma.py | 38 |
1 files changed, 23 insertions, 15 deletions
diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py index 375701d0..b1c0322c 100644 --- a/python/report_ops/sma.py +++ b/python/report_ops/sma.py @@ -131,20 +131,6 @@ class BrinkerSMA(SMA, fund="BRINKER"): pass -_sql_query = { - "bond": "SELECT * FROM risk_positions(%s, null, %s) ", - "future": ( - "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures " - "WHERE trade_date <= %s AND fund=%s) " - "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';" - ), - "tranche": "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.* FROM tranche_risk_isosel trb left join cds on trade_id=id WHERE date=%s", - "cdx_swaption": "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", - "ir_swaption": "SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", - "cdx": "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;", - "irs": "SELECT isr.pv, irs.*, accounts2.name FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id LEFT JOIN accounts2 USING (cash_account) WHERE date=%s AND irs.fund=%s;", -} - _fund_custodian = {"BOWDST": "BONY2", "ISOSEL": "NT"} _fund_client = {"BOWDST": "Hedgemark", "ISOSEL": "Innocap"} _fund_fcm = {"BOWDST": "GS_FCM", "ISOSEL": "BOA_FC"} @@ -158,6 +144,28 @@ product_name_mapping = { } +def get_position_query(asset_class): + match asset_class: + case "bond": + return "SELECT * FROM risk_positions(%s, null, %s) " + case "future": + return ( + "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures " + "WHERE trade_date <= %s AND fund=%s) " + "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';" + ) + case "tranche": + return "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.* FROM tranche_risk_master trb left join cds on trade_id=id WHERE date=%s AND trb.fund=%s;" + case "cdx_swaption": + return "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;" + case "ir_swaption": + return "SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;" + case "cdx": + return "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;" + case "irs": + return "SELECT isr.pv, irs.*, accounts2.name FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id LEFT JOIN accounts2 USING (cash_account) WHERE date=%s AND irs.fund=%s;" + + def get_path(cob, fund): match fund: case "ISOSEL": @@ -213,7 +221,7 @@ class PositionReport(Deal, deal_type=None, table_name=None): def __init_subclass__(cls, asset_class, **kwargs): cls.asset_class = asset_class - cls._query = _sql_query[asset_class] + cls._query = get_position_query(asset_class) cls._registry[asset_class] = cls @classmethod |