diff options
| -rw-r--r-- | python/position_file_isosel.py | 254 |
1 files changed, 127 insertions, 127 deletions
diff --git a/python/position_file_isosel.py b/python/position_file_isosel.py index 22c8fc4e..d97d86f7 100644 --- a/python/position_file_isosel.py +++ b/python/position_file_isosel.py @@ -97,40 +97,40 @@ def positions_otc(conn, date): obj["DealCurrencyA"] = "USD" obj["Product Type"] = "Bond" yield obj - # c.execute( - # "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures " - # "WHERE fund='ISOSEL' AND trade_date <= %s) " - # "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';", - # (date,), - # ) - # trades = [] - # for row in c: - # obj = row._asdict() - # obj["Client Name"] = "HEDGEMARK" - # obj["Fund Name"] = "BOS_PAT_BOWDOIN" - # rename_keys( - # obj, - # { - # "bbg_ticker": "Underlying (ISIN / CUSP / RED CODES)", - # "notional": "NotionalA", - # "cp_code": "Prime Broker", - # "cash_account": "AccountNumber", - # "security_desc": "SecurityDescription", - # "currency": "DealCurrencyA", - # "maturity": "MaturityDate", - # }, - # ) - # obj["COB Date"] = date - # obj["Product Type"] = "Futures" - # yield obj c.execute( - "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_selene trb left join cds on trade_id=id WHERE date=%s", + "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures " + "WHERE fund='ISOSEL' AND trade_date <= %s) " + "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';", (date,), ) + trades = [] for row in c: obj = row._asdict() obj["Client Name"] = "HEDGEMARK" obj["Fund Name"] = "BOS_PAT_BOWDOIN" + rename_keys( + obj, + { + "bbg_ticker": "Underlying (ISIN / CUSP / RED CODES)", + "notional": "NotionalA", + "cp_code": "Prime Broker", + "cash_account": "AccountNumber", + "security_desc": "SecurityDescription", + "currency": "DealCurrencyA", + "maturity": "MaturityDate", + }, + ) + obj["COB Date"] = date + obj["Product Type"] = "Futures" + yield obj + c.execute( + "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_selene trb left join cds on trade_id=id WHERE date=%s", + (date,), + ) + for row in c: + obj = row._asdict() + obj["Client Name"] = "INNOCAP" + obj["Fund Name"] = "ISOSEL" obj["Product Type"] = "Credit Index Tranche" obj["TransactionIndicator (Buy/Sell)"] = ( "B" if obj["protection"] == "Buyer" else "S" @@ -154,110 +154,110 @@ def positions_otc(conn, date): }, ) yield obj - # c.execute( - # "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, 'ISOSEL') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", - # (date,), - # ) - # for row in c: - # obj = row._asdict() - # obj["Client Name"] = "HEDGEMARK" - # obj["Fund Name"] = "BOS_PAT_BOWDOIN" - # obj["Product Type"] = "CD Swaption" - # obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S" - # obj["PutCall Indicator (Call/Put)"] = ( - # "P" if obj["option_type"] == "PAYER" else "C" - # ) - # obj["Exercise Type"] = "European" - # obj["MTM Currency"] = "USD" - # obj["COB Date"] = date - # rename_keys( - # obj, - # { - # "dealid": "Unique Deal ID", - # "cp_code": "Counterparty", - # "currency": "DealCurrencyA", - # "active_notional": "NotionalA", - # "fixed_rate": "FixedRate", - # "strike": "Strike", - # "annexdate": "EffectiveDate", - # "trade_date": "Start Date", - # "maturity": "Maturity Date", - # "expiration_date": "Underlying Maturity", - # "security_id": "Underlying (ISIN / CUSP / RED CODES)", - # "security_desc": "Underlying Desc", - # "serenitas_nav": "MTM Valuation", - # }, - # ) - # yield obj + c.execute( + "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, 'ISOSEL') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", + (date,), + ) + for row in c: + obj = row._asdict() + obj["Client Name"] = "INNOCAP" + obj["Fund Name"] = "ISOSEL" + obj["Product Type"] = "CD Swaption" + obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S" + obj["PutCall Indicator (Call/Put)"] = ( + "P" if obj["option_type"] == "PAYER" else "C" + ) + obj["Exercise Type"] = "European" + obj["MTM Currency"] = "USD" + obj["COB Date"] = date + rename_keys( + obj, + { + "dealid": "Unique Deal ID", + "cp_code": "Counterparty", + "currency": "DealCurrencyA", + "active_notional": "NotionalA", + "fixed_rate": "FixedRate", + "strike": "Strike", + "annexdate": "EffectiveDate", + "trade_date": "Start Date", + "maturity": "Maturity Date", + "expiration_date": "Underlying Maturity", + "security_id": "Underlying (ISIN / CUSP / RED CODES)", + "security_desc": "Underlying Desc", + "serenitas_nav": "MTM Valuation", + }, + ) + yield obj - # c.execute( - # "SELECT abs(spr.notional) AS active_notional, spr.nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, 'ISOSEL') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", - # (date,), - # ) - # for row in c: - # obj = row._asdict() - # obj["Client Name"] = "HEDGEMARK" - # obj["Fund Name"] = "BOS_PAT_BOWDOIN" - # obj["Product Type"] = "Swaption" - # obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S" - # obj["PutCall Indicator (Call/Put)"] = ( - # "P" if obj["option_type"] == "PAYER" else "C" - # ) - # obj["Exercise Type"] = "European" - # obj["MTM Currency"] = "USD" - # obj["COB Date"] = date + c.execute( + "SELECT abs(spr.notional) AS active_notional, spr.nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, 'ISOSEL') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", + (date,), + ) + for row in c: + obj = row._asdict() + obj["Client Name"] = "HEDGEMARK" + obj["Fund Name"] = "BOS_PAT_BOWDOIN" + obj["Product Type"] = "Swaption" + obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S" + obj["PutCall Indicator (Call/Put)"] = ( + "P" if obj["option_type"] == "PAYER" else "C" + ) + obj["Exercise Type"] = "European" + obj["MTM Currency"] = "USD" + obj["COB Date"] = date - # rename_keys( - # obj, - # { - # "dealid": "Unique Deal ID", - # "cp_code": "Counterparty", - # "currency": "DealCurrencyA", - # "active_notional": "NotionalA", - # "fixed_rate": "FixedRate", - # "strike": "Strike", - # "effectivedate": "Effective Date", - # "trade_date": "Start Date", - # "maturity": "Maturity Date", - # "expiration_date": "Underlying Maturity", - # "security_id": "Underlying (ISIN / CUSP / RED CODES)", - # "security_desc": "Underlying Desc", - # "nav": "MTM Valuation", - # }, - # ) - # yield obj + rename_keys( + obj, + { + "dealid": "Unique Deal ID", + "cp_code": "Counterparty", + "currency": "DealCurrencyA", + "active_notional": "NotionalA", + "fixed_rate": "FixedRate", + "strike": "Strike", + "effectivedate": "Effective Date", + "trade_date": "Start Date", + "maturity": "Maturity Date", + "expiration_date": "Underlying Maturity", + "security_id": "Underlying (ISIN / CUSP / RED CODES)", + "security_desc": "Underlying Desc", + "nav": "MTM Valuation", + }, + ) + yield obj - # c.execute( - # "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, 'ISOSEL') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;", - # (date,), - # ) - # for row in c: - # obj = row._asdict() - # obj["Client Name"] = "HEDGEMARK" - # obj["Fund Name"] = "BOS_PAT_BOWDOIN" - # obj["Counterparty"] = "GS" - # obj["Product Type"] = "Credit Index" - # obj["Unique Deal ID"] = obj["security_id"] - # obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["notional"] > 0 else "S" - # obj["DealCurrencyA"] = "EUR" if obj["index"] in ("EU", "XO") else "USD" - # obj["NotionalA"] = abs(obj["notional"]) * obj["factor"] - # obj["Start Date"] = date - # obj["MTM Currency"] = "USD" - # obj["MTM Valuation"] = obj["clean_nav"] + obj["accrued"] - # obj["COB Date"] = date - # obj["Clearing House Name"] = "ICE" - # obj["FixedRate"] = obj["coupon"] * 100 - # rename_keys( - # obj, - # { - # "effectivedate": "Effective Date", - # "maturity": "Maturity Date", - # "security_id": "Underlying (ISIN / CUSP / RED CODES)", - # "security_desc": "Underlying Desc", - # }, - # ) + c.execute( + "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, 'ISOSEL') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;", + (date,), + ) + for row in c: + obj = row._asdict() + obj["Client Name"] = "HEDGEMARK" + obj["Fund Name"] = "BOS_PAT_BOWDOIN" + obj["Counterparty"] = "GS" + obj["Product Type"] = "Credit Index" + obj["Unique Deal ID"] = obj["security_id"] + obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["notional"] > 0 else "S" + obj["DealCurrencyA"] = "EUR" if obj["index"] in ("EU", "XO") else "USD" + obj["NotionalA"] = abs(obj["notional"]) * obj["factor"] + obj["Start Date"] = date + obj["MTM Currency"] = "USD" + obj["MTM Valuation"] = obj["clean_nav"] + obj["accrued"] + obj["COB Date"] = date + obj["Clearing House Name"] = "ICE" + obj["FixedRate"] = obj["coupon"] * 100 + rename_keys( + obj, + { + "effectivedate": "Effective Date", + "maturity": "Maturity Date", + "security_id": "Underlying (ISIN / CUSP / RED CODES)", + "security_desc": "Underlying Desc", + }, + ) - # yield obj + yield obj if __name__ == "__main__": |