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", folder="Innocap") innocap_sftp.put(buf, fname) citco_sftp = SftpClient.from_creds("citco", folder="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( "--no-upload", "-n", 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", not args.no_upload )