aboutsummaryrefslogtreecommitdiffstats
path: root/python
diff options
context:
space:
mode:
Diffstat (limited to 'python')
-rw-r--r--python/position_file_isosel.py235
1 files changed, 87 insertions, 148 deletions
diff --git a/python/position_file_isosel.py b/python/position_file_isosel.py
index d97d86f7..ccf880d4 100644
--- a/python/position_file_isosel.py
+++ b/python/position_file_isosel.py
@@ -9,11 +9,11 @@ from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from serenitas.analytics.dates import prev_business_day
-def process_upload(trades, asset_type, upload):
+def process_upload(trades, upload):
buf = StringIO()
csvwriter = csv.writer(buf)
- csvwriter.writerow(HEADERS[asset_type])
- csvwriter.writerows(build_line(trade, asset_type) for trade in trades)
+ csvwriter.writerow(ISOSEL_HEADERS)
+ csvwriter.writerows(build_line(trade) for trade in trades)
buf = buf.getvalue().encode()
fname = f"Innocap_ISOSEL_positions_{args.date}.csv"
if upload:
@@ -28,58 +28,67 @@ def process_upload(trades, asset_type, upload):
return fname, buf
-def build_line(obj, asset_type):
- return [obj.get(h, None) for h in HEADERS[asset_type]]
+def build_line(obj):
+ return [obj.get(h, None) for h in ISOSEL_HEADERS]
-HEADERS = {
- "otc": [
- "Client Name",
- "Fund Name",
- "Counterparty",
- "AccountNumber",
- "COB Date",
- "SecurityDescription",
- "Prime Broker",
- "Product Type",
- "Unique Deal ID",
- "TransactionIndicator (Buy/Sell)",
- "PutCall Indicator (Call/Put)",
- "CapFloorIndicator",
- "CurrencyPair",
- "DealCurrencyA",
- "DealCurrencyB",
- "NotionalA",
- "NotionalB",
- "OriginalPrice",
- "Strike",
- "FixedRate",
- "Quantity",
- "Start Date",
- "Effective Date",
- "Maturity Date",
- "Underlying Maturity",
- "RecPayFixed",
- "Underlying (ISIN / CUSP / RED CODES)",
- "Underlying Desc",
- "Exercise Type",
- "MTM Currency",
- "MTM Valuation",
- "MarketPrice",
- "COB Date",
- "Clearing House Name",
- ],
+ISOSEL_HEADERS = [
+ "Client Name",
+ "Fund Name",
+ "Counterparty",
+ "AccountNumber",
+ "SecurityDescription",
+ "Prime Broker",
+ "Product Type",
+ "Unique Deal ID",
+ "TransactionIndicator (Buy/Sell)",
+ "PutCall Indicator (Call/Put)",
+ "CapFloorIndicator",
+ "CurrencyPair",
+ "DealCurrencyA",
+ "DealCurrencyB",
+ "NotionalA",
+ "NotionalB",
+ "OriginalPrice",
+ "Strike",
+ "FixedRate",
+ "Quantity",
+ "Start Date",
+ "Effective Date",
+ "Maturity Date",
+ "Underlying Maturity",
+ "RecPayFixed",
+ "Underlying (ISIN / CUSP / RED CODES)",
+ "Underlying Desc",
+ "Exercise Type",
+ "MTM Currency",
+ "MTM Valuation",
+ "MarketPrice",
+ "COB Date",
+ "Clearing House Name",
+]
+_sql_query = {
+ "bond": "SELECT * FROM risk_positions(%s, null, 'ISOSEL') ",
+ "future": (
+ "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';"
+ ),
+ "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_selene 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, 'ISOSEL') 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, 'ISOSEL') 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, 'ISOSEL') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;",
}
-def positions_otc(conn, date):
- with conn.cursor() as c:
- c.execute("SELECT * FROM risk_positions(%s, null, 'ISOSEL') ", (date,))
- trades = []
- for row in c:
- obj = row._asdict()
- obj["Client Name"] = "INNOCAP"
- obj["Fund Name"] = "ISOSEL"
+def _base_attrs(asset_class, date, obj):
+ obj["Client Name"] = "INNOCAP"
+ obj["Fund Name"] = "ISOSEL"
+ obj["COB Date"] = date
+ obj["Product Type"] = asset_class
+ obj["MTM Currency"] = "USD"
+ match asset_class:
+ case "bond":
rename_keys(
obj,
{
@@ -91,23 +100,10 @@ def positions_otc(conn, date):
"usd_market_value": "MTM Valuation",
},
)
- obj["AccountNumber"] = "NT"
+ obj["Account Number"] = "NT"
obj["Prime Broker"] = "NT"
- obj["COB Date"] = 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"
+ case "future":
rename_keys(
obj,
{
@@ -120,23 +116,10 @@ def positions_otc(conn, date):
"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"
+ case "tranche":
obj["TransactionIndicator (Buy/Sell)"] = (
"B" if obj["protection"] == "Buyer" else "S"
)
- obj["MTM Currency"] = "USD"
- obj["COB Date"] = date
rename_keys(
obj,
{
@@ -153,23 +136,7 @@ def positions_otc(conn, date):
"mtm": "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
+ case "ir_swaption" | "cdx_swaption":
rename_keys(
obj,
{
@@ -179,72 +146,28 @@ def positions_otc(conn, date):
"active_notional": "NotionalA",
"fixed_rate": "FixedRate",
"strike": "Strike",
- "annexdate": "EffectiveDate",
+ "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",
- "serenitas_nav": "MTM Valuation",
+ "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
-
- 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"
+ case "cdx":
+ obj["Counterparty"] = "BOA_FC"
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(
@@ -256,8 +179,26 @@ def positions_otc(conn, date):
"security_desc": "Underlying Desc",
},
)
+ return obj
+
- yield obj
+def main(conn, date, upload):
+ trades = []
+ with conn.cursor() as c:
+ for asset_class in (
+ "bond",
+ "future",
+ "tranche",
+ "cdx_swaption",
+ "ir_swaption",
+ "cdx",
+ ):
+ c.execute(_sql_query[asset_class], (date,))
+ for row in c:
+ obj = row._asdict()
+ obj = _base_attrs(asset_class, date, obj)
+ trades.append(obj)
+ process_upload(trades, upload=True)
if __name__ == "__main__":
@@ -282,6 +223,4 @@ if __name__ == "__main__":
)
args = parser.parse_args()
conn = dbconn("dawndb")
- fname, buf = process_upload(
- positions_otc(conn, args.date), "otc", not args.no_upload
- )
+ main(conn, args.date, not args.no_upload)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-24 03:44:02 +0000