from exchange import ExchangeMessage, FileAttachment from io import BytesIO from utils.db import serenitas_pool, dbconn import datetime import pandas as pd from pandas.tseries.offsets import BDay from psycopg2.extras import execute_values from pyisda.date import cds_accrued, default_accrual, previous_twentieth from analytics.index import CreditIndex from copy import copy def get_outstanding_positions(conn, trade_date, fcm, fund="SERCGMAST"): with conn.cursor() as c: c.execute( "SELECT security_id, notional, folder, b.redindexcode, currency, " "maturity, b.indexfactor " "FROM list_cds_positions_by_strat_fcm(%s, %s, %s) a " "LEFT JOIN index_version_markit b " "ON a.security_id=b.prevredindexcode " "LEFT JOIN index_version_markit c " "ON a.security_id=c.redindexcode " "WHERE b.redindexcode IS NOT NULL AND b.activeversion", (trade_date, fcm, fund), ) yield from c def new_version_quotes( conn, auction_date: datetime.date, recovery, accrual_days, coupon=0.05 ): adj_recovery = 100 * ( recovery + accrual_days * coupon / 360 - cds_accrued(auction_date, coupon) ) with conn.cursor() as c1, conn.cursor() as c2: c1.execute( "SELECT index, series, old.version, old.indexfactor AS oldfactor, " "new.indexfactor AS newfactor FROM index_version old " "LEFT JOIN index_version new USING (index, series) " "WHERE old.lastdate=%s AND new.version=old.version+1", (auction_date,), ) for index, series, version, oldfactor, newfactor in c1: c2.execute( "INSERT into index_quotes_pre(date, index, series, version, " "tenor, close_price, source)" "SELECT date, index, series, version+1, " "tenor, (%s*close_price + %s)/%s, 'MKIT'" "FROM index_quotes_pre " "WHERE index=%s AND series=%s AND version=%s AND date=%s AND source='MKIT'", ( oldfactor, (newfactor - oldfactor) * adj_recovery, newfactor, index, series, version, auction_date, ), ) conn.commit() def default_adjustment(conn, company_id, seniority, end_date): with conn.cursor() as c: c.execute( "SELECT recovery, event_date, auction_date FROM defaulted WHERE id=%s " "AND seniority=%s", (company_id, seniority), ) recovery, event_date, auction_date = next(c) fee = 1 - recovery start_date = previous_twentieth(event_date) accrual_days, _ = default_accrual( auction_date, event_date, start_date, end_date, 1.0, 1.0 ) return accrual_days, fee PORTFOLIO = { "HYOPTDEL": "OPTIONS", "HEDGE_MBS": "MORTGAGES", "HYINX": "TRANCHE", "SER_IGCURVE": "CURVE", "HEDGE_CLO": "CLO", "HEDGE_MAC": "HEDGE_MAC", "XOINX": "TRANCHE", } def rebook(conn, trade_date, company_id, seniority, fcm, fund="SERCGMAST"): dawndb = dbconn("dawndb") upfront_settle_date = trade_date + 3 * BDay() effective_date = trade_date + datetime.timedelta(days=1) for r in get_outstanding_positions(dawndb, trade_date, fcm, fund): accrual_days, fee = default_adjustment(conn, company_id, seniority, r.maturity) index_old = CreditIndex( redcode=r.security_id, maturity=r.maturity, value_date=trade_date, notional=-r.notional, freeze_version=True, ) adj = ( (fee - accrual_days * index_old.fixed_rate * 1e-4 / 360) * r.notional * (r.indexfactor - index_old.factor) ) index_old.mark() trade_old = { "fund": fund, "action": "NEW", "portfolio": PORTFOLIO[r.folder], "folder": r.folder, "cp_code": "CONTRA", "custodian": "NONE", "trade_date": trade_date, "effective_date": effective_date, "maturity": r.maturity, "currency": r.currency, "payment_rolldate": "Following", "notional": abs(r.notional), "fixed_rate": index_old.fixed_rate / 100, "day_count": "ACT/360", "frequency": 4, "protection": index_old.direction, "security_id": r.security_id, "security_desc": f"CDX {index_old.index_type} CDSI S{index_old.series} 5Y", "upfront": index_old.pv, "upfront_settle_date": upfront_settle_date, "swap_type": "CD_INDEX", "account_code": fcm, } trade_new = copy(trade_old) trade_new["protection"] = ( "Seller" if trade_old["protection"] == "Buyer" else "Buyer" ) trade_new["upfront"] = -adj - index_old.pv trade_new["security_id"] = r.redindexcode sql_str = f"INSERT INTO cds({','.join(trade_new.keys())}) VALUES %s" with dawndb.cursor() as c: execute_values( c, sql_str, [tuple(trade_old.values()), tuple(trade_new.values())] ) dawndb.commit() dawndb.close() def insert_newids(dawndb, d: datetime.date, df: pd.DataFrame): with dawndb.cursor() as c: c.execute( "INSERT INTO id_mapping ( " " SELECT %s, 'CDS', serenitas_id, new_id FROM ( " " SELECT * FROM unnest(%s, %s) AS t(globeop_id, new_id) " " LEFT JOIN id_mapping USING (globeop_id)) a" ")", (d, df.old_ids.tolist(), df.new_ids.to_list()), ) dawndb.commit() def send_csv(dawndb, d: datetime.date, ticker): columns = [ "dealid", "folder", "cp_code", "trade_date", "effective_date", "maturity", "currency", "payment_rolldate", "notional", "fixed_rate", "day_count", "frequency", "protection", "security_id", "security_desc", "upfront", "upfront_settle_date", "swap_type", "account_code", "portfolio", "fund", "indexfactor", "version", ] buf = BytesIO() with dawndb.cursor() as c: sql_str = ( f"COPY (SELECT {','.join(columns)} " "FROM cds " "JOIN index_version " "ON security_id=redindexcode " f"WHERE fund='BOWDST' AND cp_code='CONTRA' AND trade_date='{d}')" " TO STDOUT WITH (FORMAT CSV, HEADER)" ) c.copy_expert(sql_str, buf) dawndb.commit() buf = buf.getvalue() em = ExchangeMessage() body = f"""Hello, These are the the rebookings due to {ticker} credit event on {d:%m/%d}. Let me know if you have any questions. Thanks, Guillaume""" em.send_email( f"{ticker} credit event", body, to_recipients=( "caagtradecapture@bnymellon.com", "hm-operations@bnymellon.com", "julie.picariello@bnymellon.com", ), cc_recipients=( "sa1futures.optionsprocessing@bnymellon.com", "bowdoin-ops@lmcg.com", "Viraphong.Douangmany@BNYMellon.com", ), attach=(FileAttachment(name=f"{ticker}_rebooking.csv", content=buf),), ) if __name__ == "__main__": conn = serenitas_pool.getconn() dawndb = dbconn("dawndb") # PKD # rebook(datetime.date(2019, 1, 24), 101148) # WINDSSE # rebook(datetime.date(2019, 4, 8), 36806879) # WFT # rebook(datetime.date(2019, 7, 26), 103633, "WF") # rebook(datetime.date(2019, 7, 26), 103633, "BAML") # DF # rebook(datetime.date(2019, 12, 11), 154954, "Senior", "BAML") # MNI # rebook(datetime.date(2020, 3, 13), 100957, "Senior", "BAML") # rebook(datetime.date(2020, 3, 13), 100957, "Senior", "WF") # WLL # rebook(datetime.date(2020, 5, 7), 8240322, "Senior", "BAML") # rebook(datetime.date(2020, 5, 7), 8240322, "Senior", "WF") # rebook(conn, datetime.date(2020, 5, 7), 8240322, "Senior", "GS", "BOWDST") # FCA # rebook(conn, datetime.date(2020, 5, 14), 100337, "Senior", "BAML") # rebook(conn, datetime.date(2020, 5, 14), 100337, "Senior", "WF") # rebook(conn, datetime.date(2020, 5, 14), 100337, "Senior", "GS", "BOWDST") # DO # rebook(conn, datetime.date(2020, 5, 26), 171248, "Senior", "BAML") # rebook(conn, datetime.date(2020, 5, 26), 171248, "Senior", "WF") # rebook(conn, datetime.date(2020, 5, 26), 171248, "Senior", "GS", "BOWDST") # NMG # rebook(conn, datetime.date(2020, 5, 29), 101056, "Senior", "BAML") # rebook(conn, datetime.date(2020, 5, 29), 101056, "Senior", "WF") # rebook(conn, datetime.date(2020, 5, 29), 101056, "Senior", "GS", "BOWDST") # JCP # rebook(conn, datetime.date(2020, 6, 9), 101154, "Senior", "BAML") # rebook(conn, datetime.date(2020, 6, 9), 101154, "Senior", "WF") # rebook(conn, datetime.date(2020, 6, 9), 101154, "Senior", "GS", "BOWDST") # HTZ # rebook(conn, datetime.date(2020, 6, 24), 9833831, "Senior", "BAML") # rebook(conn, datetime.date(2020, 6, 24), 9833831, "Senior", "WF") # rebook(conn, datetime.date(2020, 6, 24), 9833831, "Senior", "GS", "BOWDST") # CRC # rebook(conn, datetime.date(2020, 7, 7), 39035708, "Senior", "BAML") # rebook(conn, datetime.date(2020, 7, 7), 39035708, "Senior", "WF") # rebook(conn, datetime.date(2020, 7, 7), 39035708, "Senior", "GS", "BOWDST") # CHK # rebook(conn, datetime.date(2020, 8, 4), 117019, "Senior", "BAML") # rebook(conn, datetime.date(2020, 8, 4), 117019, "Senior", "WF") # rebook(conn, datetime.date(2020, 8, 4), 117019, "Senior", "GS", "BOWDST") # HEMA # rebook(conn, datetime.date(2020, 9, 8), 40678877, "Senior", "BAML") # rebook(conn, datetime.date(2020, 9, 8), 40678877, "Senior", "GS", "BOWDST") # send_csv(dawndb, datetime.date(2020, 9, 8), "HEMA") # NE # rebook(conn, datetime.date(2020, 9, 10), 105641, "Senior", "BAML") # rebook(conn, datetime.date(2020, 9, 10), 105641, "Senior", "GS", "BOWDST") # send_csv(dawndb, datetime.date(2020, 9, 10), "NE") # MATAFIN # rebook(conn, datetime.date(2020, 9, 15), 15334701, "Subordinated", "BAML") # rebook(conn, datetime.date(2020, 9, 15), 15334701, "Senior", "GS", "BOWDST") # send_csv(dawndb, datetime.date(2020, 9, 15), "MATAFIN") # PIZZAFI # rebook(conn, datetime.date(2020, 10, 1), 41488375, "Senior", "BAML") # rebook(conn, datetime.date(2020, 10, 1), 41488375, "Senior", "GS", "BOWDST") # send_csv(dawndb, datetime.date(2020, 10, 1), "PIZZAFI") # SELEGRO # rebook(conn, datetime.date(2020, 10, 20), 40769171, "Senior", "BAML") rebook(conn, datetime.date(2020, 10, 20), 40769171, "Senior", "GS", "BOWDST") send_csv(dawndb, datetime.date(2020, 10, 20), "SELEGRO") serenitas_pool.putconn(conn)