aboutsummaryrefslogtreecommitdiffstats
path: root/python/position_file_bowdst.py
diff options
context:
space:
mode:
Diffstat (limited to 'python/position_file_bowdst.py')
-rw-r--r--python/position_file_bowdst.py408
1 files changed, 226 insertions, 182 deletions
diff --git a/python/position_file_bowdst.py b/python/position_file_bowdst.py
index f3dfa90a..905adf03 100644
--- a/python/position_file_bowdst.py
+++ b/python/position_file_bowdst.py
@@ -99,196 +99,240 @@ HEADERS = {
}
-with dawndb.cursor() as c:
- c.execute("SELECT * FROM risk_positions(%s, null, 'BOWDST') ", (date,))
- trades = []
- for row in c:
- obj = row._asdict()
- rename_keys(
- obj,
- {
- "cusip": "CUSIP",
- "description": "SecurityDescription",
- "notional": "Position",
- "price": "MarketPrice",
- "local_market_value": "Local Market Value",
- "usd_market_value": "Base Market Value",
- },
- )
- try:
- obj["Fx Rate"] = obj["Local Market Value"] / obj["Base Market Value"]
- except ZeroDivisionError:
- obj["Fx Rate"] = 1
- obj["AccountNumber"] = "319478"
- obj["Prime Broker"] = "BONY"
- obj["COB Date"] = date
- obj["Currency"] = "USD"
- obj["SecurityType"] = "Bond"
- trades.append(obj)
- process_upload(trades, "bond")
+def positions_bond(positions, conn, date):
+ with conn.cursor() as c:
+ c.execute("SELECT * FROM risk_positions(%s, null, 'BOWDST') ", (date,))
+ trades = []
+ for row in c:
+ obj = row._asdict()
+ rename_keys(
+ obj,
+ {
+ "cusip": "CUSIP",
+ "description": "SecurityDescription",
+ "notional": "Position",
+ "price": "MarketPrice",
+ "local_market_value": "Local Market Value",
+ "usd_market_value": "Base Market Value",
+ },
+ )
+ try:
+ obj["Fx Rate"] = obj["Local Market Value"] / obj["Base Market Value"]
+ except ZeroDivisionError:
+ obj["Fx Rate"] = 1
+ obj["AccountNumber"] = "319478"
+ obj["Prime Broker"] = "BONY"
+ obj["COB Date"] = date
+ obj["Currency"] = "USD"
+ obj["SecurityType"] = "Bond"
+ positions["bond"].append(obj)
+ # quotes['bond']
+ # process_upload(trades, "bond")
- 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='BOWDST' 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()
- rename_keys(
- obj,
- {
- "bbg_ticker": "BBGTicker",
- "notional": "Quantity",
- "cp_code": "Prime Broker",
- "cash_account": "AccountNumber",
- "security_desc": "SecurityDescription",
- "currency": "Currency",
- "maturity": "MaturityDate",
- },
- )
- obj["COB Date"] = date
- obj["SecurityType"] = "Futures"
- trades.append(obj)
- process_upload(trades, "future")
- 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_bowdst trb left join cds on trade_id=id WHERE date=%s",
- (date,),
- )
- otc_trades = []
- for row in c:
- obj = row._asdict()
- obj["Client Name"] = "HEDGEMARK"
- obj["Fund Name"] = "BOS_PAT_BOWDOIN"
- obj["Product Type"] = "Credit Index Tranche"
- obj["TransactionIndicator (Buy/Sell)"] = (
- "B" if obj["protection"] == "Buyer" else "S"
- )
- obj["MTM Currency"] = "USD"
- obj["COB Date"] = date
- obj["Clearing House Name"] = "Bilateral"
- rename_keys(
- obj,
- {
- "dealid": "Unique Deal ID",
- "cp_code": "Counterparty",
- "currency": "DealCurrencyA",
- "active_notional": "NotionalA",
- "fixed_rate": "FixedRate",
- "trade_date": "Start Date",
- "effective_date": "EffectiveDate",
- "maturity": "Maturity Date",
- "security_id": "Underlying (ISIN / CUSP / RED CODES)",
- "security_desc": "Underlying Desc",
- "mtm": "MTM Valuation",
- },
+def positions_future(positions, conn, date):
+ with conn.cursor() as c:
+ 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='BOWDST' 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,),
)
- otc_trades.append(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, 'BOWDST') 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"
+ trades = []
+ for row in c:
+ obj = row._asdict()
+ rename_keys(
+ obj,
+ {
+ "bbg_ticker": "BBGTicker",
+ "notional": "Quantity",
+ "cp_code": "Prime Broker",
+ "cash_account": "AccountNumber",
+ "security_desc": "SecurityDescription",
+ "currency": "Currency",
+ "maturity": "MaturityDate",
+ },
+ )
+ obj["COB Date"] = date
+ obj["SecurityType"] = "Futures"
+ positions["future"].append(obj)
+ # process_upload(trades, "future")
+
+
+def positions_otc(positions, conn, date):
+ with conn.cursor() as c:
+ 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_bowdst trb left join cds on trade_id=id WHERE date=%s",
+ (date,),
)
- obj["Exercise Type"] = "European"
- obj["MTM Currency"] = "USD"
- obj["COB Date"] = date
- obj["Clearing House Name"] = "Bilateral"
- 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",
- },
+ otc_trades = []
+ for row in c:
+ obj = row._asdict()
+ obj["Client Name"] = "HEDGEMARK"
+ obj["Fund Name"] = "BOS_PAT_BOWDOIN"
+ obj["Product Type"] = "Credit Index Tranche"
+ obj["TransactionIndicator (Buy/Sell)"] = (
+ "B" if obj["protection"] == "Buyer" else "S"
+ )
+ obj["MTM Currency"] = "USD"
+ obj["COB Date"] = date
+ obj["Clearing House Name"] = "Bilateral"
+ rename_keys(
+ obj,
+ {
+ "dealid": "Unique Deal ID",
+ "cp_code": "Counterparty",
+ "currency": "DealCurrencyA",
+ "active_notional": "NotionalA",
+ "fixed_rate": "FixedRate",
+ "trade_date": "Start Date",
+ "effective_date": "EffectiveDate",
+ "maturity": "Maturity Date",
+ "security_id": "Underlying (ISIN / CUSP / RED CODES)",
+ "security_desc": "Underlying Desc",
+ "mtm": "MTM Valuation",
+ },
+ )
+ positions["otc"].append(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, 'BOWDST') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
+ (date,),
)
- otc_trades.append(obj)
+ 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
+ obj["Clearing House Name"] = "Bilateral"
+ 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",
+ },
+ )
+ positions["otc"].append(obj)
- c.execute(
- "SELECT abs(spr.notional) AS active_notional, spr.nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, 'BOWDST') 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"
+ c.execute(
+ "SELECT abs(spr.notional) AS active_notional, spr.nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, 'BOWDST') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
+ (date,),
)
- obj["Exercise Type"] = "European"
- obj["MTM Currency"] = "USD"
- obj["COB Date"] = date
- obj["Clearing House Name"] = "Bilateral"
- 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",
- },
+ 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
+ obj["Clearing House Name"] = "Bilateral"
+ 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",
+ },
+ )
+ positions["otc"].append(obj)
+
+ c.execute(
+ "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, 'BOWDST') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;",
+ (date,),
)
- otc_trades.append(obj)
+ 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["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, 'BOWDST') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;",
- (date,),
+ positions["otc"].append(obj)
+ # process_upload(otc_trades, "otc")
+
+
+if __name__ == "__main__":
+ import argparse
+ from serenitas.utils.db import dbconn
+ from serenitas.analytics.bbg_helpers import init_bbg_session, retrieve_data
+
+ parser = argparse.ArgumentParser(
+ description="Generate position files for Bowdoin Street"
)
- 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["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",
- },
- )
+ parser.add_argument(
+ "--date", type=datetime.date.fromisoformat, default=datetime.date.today()
+ )
+ parser.add_argument(
+ "--product",
+ nargs="+",
+ choices=["bond", "future", "otc"],
+ default=["bond", "future", "otc"],
+ help="list of products to generate position files for",
+ )
+ parser.add_argument(
+ "--u",
+ action="store_true",
+ default=False,
+ help="uploads to globeop",
+ )
+ args = parser.parse_args()
+ conn = dbconn("dawndb")
+
+ positions = {"bond": [], "future": [], "otc": []}
+
+ for p in args.product:
+ globals()[f"positions_{p}"](positions, conn, args.date)
- otc_trades.append(obj)
- process_upload(otc_trades, "otc")
+ for asset_type, trades in positions.items():
+ process_upload(trades, asset_type)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-25 02:59:27 +0000