diff options
| -rw-r--r-- | python/cds_rebook.py | 88 |
1 files changed, 51 insertions, 37 deletions
diff --git a/python/cds_rebook.py b/python/cds_rebook.py index 767f8d3a..5f8aef7f 100644 --- a/python/cds_rebook.py +++ b/python/cds_rebook.py @@ -1,31 +1,34 @@ -from utils.db import dawn_engine, serenitas_engine +from utils.db import serenitas_pool, dbconn import datetime +import pandas as pd from pandas.tseries.offsets import BDay from pyisda.date import default_accrual, previous_twentieth from analytics.index import CreditIndex from copy import copy -def get_outstanding_positions(trade_date, fcm): - r = dawn_engine.execute( - "SELECT security_id, notional, folder, nextredindexcode, currency, " - "maturity, indexfactor " - "FROM list_cds_positions_by_strat_fcm(%s, %s) a " - "JOIN index_version_markit " - "ON a.security_id=index_version_markit.redindexcode " - "WHERE nextredindexcode IS NOT NULL", - (trade_date, fcm), - ) - return r +def get_outstanding_positions(conn, trade_date, fcm): + with conn.cursor() as c: + c.execute( + "SELECT security_id, notional, folder, nextredindexcode, currency, " + "maturity, indexfactor " + "FROM list_cds_positions_by_strat_fcm(%s, %s) a " + "JOIN index_version_markit " + "ON a.security_id=index_version_markit.redindexcode " + "WHERE nextredindexcode IS NOT NULL", + (trade_date, fcm), + ) + yield from c -def default_adjustment(company_id, seniority, end_date): - r = serenitas_engine.execute( - "SELECT recovery, event_date, auction_date FROM defaulted WHERE id=%s " - "AND seniority=%s", - (company_id, seniority), - ) - recovery, event_date, auction_date = next(r) +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( @@ -42,41 +45,42 @@ PORTFOLIO = { } -def rebook(trade_date, company_id, seniority, fcm): +def rebook(conn, trade_date, company_id, seniority, fcm): + dawndb = dbconn("dawndb") upfront_settle_date = trade_date + 3 * BDay() effective_date = trade_date + datetime.timedelta(days=1) - for r in get_outstanding_positions(trade_date, fcm): - accrual_days, fee = default_adjustment(company_id, seniority, r["maturity"]) + for r in get_outstanding_positions(dawndb, trade_date, fcm): + accrual_days, fee = default_adjustment(conn, company_id, seniority, r.maturity) index_new = CreditIndex( - redcode=r["nextredindexcode"], - maturity=r["maturity"], + redcode=r.nextredindexcode, + maturity=r.maturity, value_date=trade_date, - notional=r["notional"], + notional=r.notional, ) adj = ( (fee - accrual_days * index_new.fixed_rate * 1e-4 / 360) - * r["notional"] - * (r["indexfactor"] - index_new.factor) + * r.notional + * (r.indexfactor - index_new.factor) ) index_new.mark() trade_new = { "action": "NEW", - "portfolio": PORTFOLIO[r["folder"]], - "folder": r["folder"], + "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"], + "maturity": r.maturity, + "currency": r.currency, "payment_rolldate": "Following", - "notional": abs(r["notional"]), + "notional": abs(r.notional), "fixed_rate": index_new.fixed_rate / 100, "day_count": "ACT/360", "frequency": 4, "protection": index_new.direction, - "security_id": r["nextredindexcode"], + "security_id": r.nextredindexcode, "security_desc": f"CDX {index_new.index_type} CDSI S{index_new.series} 5Y", "upfront": index_new.pv, "upfront_settle_date": upfront_settle_date, @@ -88,12 +92,15 @@ def rebook(trade_date, company_id, seniority, fcm): "Seller" if trade_new["protection"] == "Buyer" else "Buyer" ) trade_prev["upfront"] = adj - index_new.pv - trade_prev["security_id"] = r["security_id"] + trade_prev["security_id"] = r.security_id sql_str = ( f"INSERT INTO cds({','.join(trade_new.keys())}) " f"VALUES({','.join(['%s'] * len(trade_new))})" ) - dawn_engine.execute(sql_str, [trade_prev.values(), trade_new.values()]) + with dawndb.cursor() as c: + c.execute(sql_str, [trade_prev.values(), trade_new.values()]) + dawndb.commit() + dawndb.close() def insert_newids(dawndb, d: datetime.date, df: pd.DataFrame): @@ -110,6 +117,7 @@ def insert_newids(dawndb, d: datetime.date, df: pd.DataFrame): if __name__ == "__main__": + conn = serenitas_pool.getconn() # PKD # rebook(datetime.date(2019, 1, 24), 101148) # WINDSSE @@ -120,5 +128,11 @@ if __name__ == "__main__": # 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") + # 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") + # FCA + rebook(conn, datetime.date(2020, 5, 14), 100337, "Senior", "BAML") + serenitas_pool.putconn(conn) |