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, upload): buf = StringIO() csvwriter = csv.writer(buf) 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: 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): return [obj.get(h, None) for h in ISOSEL_HEADERS] ISOSEL_HEADERS = [ "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", ] _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_isosel 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 _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, { "identifier": "Underlying (ISIN / CUSP / RED CODES)", "description": "SecurityDescription", "notional": "NotionalA", "price": "MarketPrice", "local_market_value": "Local Market Value", "usd_market_value": "MTM Valuation", }, ) obj["Account Number"] = "NT" obj["Prime Broker"] = "NT" obj["DealCurrencyA"] = "USD" case "future": 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", }, ) case "tranche": obj["TransactionIndicator (Buy/Sell)"] = ( "B" if obj["protection"] == "Buyer" else "S" ) 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", }, ) case "ir_swaption" | "cdx_swaption": 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", }, ) 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" 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 Valuation"] = obj["clean_nav"] + obj["accrued"] 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", }, ) return 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__": 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") main(conn, args.date, not args.no_upload)