aboutsummaryrefslogtreecommitdiffstats
path: root/python/position_file_isosel.py
diff options
context:
space:
mode:
Diffstat (limited to 'python/position_file_isosel.py')
-rw-r--r--python/position_file_isosel.py289
1 files changed, 289 insertions, 0 deletions
diff --git a/python/position_file_isosel.py b/python/position_file_isosel.py
new file mode 100644
index 00000000..38a4f0b4
--- /dev/null
+++ b/python/position_file_isosel.py
@@ -0,0 +1,289 @@
+import datetime
+import csv
+from io import StringIO
+from serenitas.utils.misc import rename_keys
+from serenitas.utils.remote import SftpClient
+from serenitas.utils.env import DAILY_DIR
+from pandas.tseries.offsets import MonthEnd
+from serenitas.utils.exchange import ExchangeMessage, FileAttachment
+from serenitas.analytics.dates import prev_business_day
+
+
+def process_upload(trades, asset_type, upload):
+ buf = StringIO()
+ csvwriter = csv.writer(buf)
+ csvwriter.writerow(HEADERS[asset_type])
+ csvwriter.writerows(build_line(trade, asset_type) for trade in trades)
+ buf = buf.getvalue().encode()
+ fname = (
+ f"ISOSEL.{datetime.datetime.now():%Y%m%d.%H%M%S}.PositionsAsOf{args.date}.csv"
+ )
+ if upload:
+ innocap_sftp = SftpClient.from_creds("innocap")
+ innocap_sftp.client.chdir("Innocap")
+ innocap_sftp.put(buf, fname)
+ citco_sftp = SftpClient.from_creds("citco")
+ citco_sftp.client.chdir("incoming")
+ citco_sftp.put(buf, fname)
+ base_dir = DAILY_DIR / str(datetime.date.today())
+ base_dir.mkdir(exist_ok=True, parents=True)
+ dest = base_dir / fname
+ dest.write_bytes(buf)
+ return fname, buf
+
+
+def build_line(obj, asset_type):
+ return [obj.get(h, None) for h in HEADERS[asset_type]]
+
+
+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",
+ ],
+}
+
+
+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"
+ rename_keys(
+ obj,
+ {
+ "identifier": "Underlying (ISIN / CUSP / RED CODES)",
+ "description": "SecurityDescription",
+ "notional": "NotionalA",
+ "price": "MarketPrice",
+ "local_market_value": "Local Market Value",
+ "usd_market_value": "MTM Valuation",
+ },
+ )
+ obj["AccountNumber"] = "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"
+ # 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_bowdst trb left join cds on trade_id=id WHERE date=%s",
+ # (date,),
+ # )
+ # 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
+ # 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",
+ # },
+ # )
+ # 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.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"
+ # 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
+
+
+if __name__ == "__main__":
+ import argparse
+ from serenitas.utils.db import dbconn
+
+ parser = argparse.ArgumentParser(
+ description="Generate position files for ISOSEL Street"
+ )
+ parser.add_argument(
+ "date",
+ nargs="?",
+ type=datetime.date.fromisoformat,
+ default=prev_business_day(datetime.date.today()),
+ )
+ parser.add_argument(
+ "--upload",
+ "-u",
+ action="store_true",
+ default=False,
+ help="uploads to citco and innocap",
+ )
+ args = parser.parse_args()
+ conn = dbconn("dawndb")
+ fname, buf = process_upload(positions_otc(conn, args.date), "otc", args.upload)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2026-01-25 11:23:56 +0000