diff options
| -rw-r--r-- | python/cds_rebook.py | 123 | ||||
| -rw-r--r-- | python/handle_default.py | 21 |
2 files changed, 80 insertions, 64 deletions
diff --git a/python/cds_rebook.py b/python/cds_rebook.py index 93164cb7..416123a1 100644 --- a/python/cds_rebook.py +++ b/python/cds_rebook.py @@ -1,60 +1,75 @@ -#we want a function that rebook trades -from sqlalchemy import create_engine -import pandas as pd +from db import dawn_engine, serenitas_engine +import datetime from pandas.tseries.offsets import BDay -import pdb +from pyisda.date import default_accrual, previous_twentieth +from analytics.index import CreditIndex +from copy import copy -def get_outstanding_positions(engine): - sqlstr = """WITH current_portfolio AS ( -SELECT security_id, maturity, attach, detach, sum(notional * (CASE WHEN protection='Buyer' THEN -1 ELSE 1 END)) -AS ntl FROM cds -GROUP BY security_id, maturity, attach, detach) -SELECT distinct security_id, lastdate FROM current_portfolio JOIN index_desc -ON current_portfolio.security_id=index_desc.redindexcode -AND index_desc.maturity=current_portfolio.maturity WHERE lastdate!='infinity' AND -ntl !=0 ORDER BY lastdate""" - df = pd.read_sql_query(sqlstr, engine) - return df +def get_outstanding_positions(trade_date): + r = dawn_engine.execute( + "SELECT security_id, notional, folder, nextredindexcode, currency, maturity " + "FROM list_cds_positions_by_strat(%s) a " + "JOIN index_version_markit " + "ON a.security_id=index_version_markit.redindexcode " + "WHERE nextredindexcode IS NOT NULL", + (trade_date,)) + return r -def next_redcode(engine, secid): - sqlstr = """SELECT DISTINCT redindexcode FROM index_desc JOIN - (SELECT DISTINCT index, series, version+1 AS version FROM index_desc WHERE redindexcode=%s) a - USING (index, series, version)""" - conn = engine.raw_connection() - with conn.cursor() as c: - c.execute(sqlstr, (secid,)) - nextredcode, = c.fetchone() - conn.commit() - conn.close() - return nextredcode +def default_adjustment(company_id, end_date): + r = serenitas_engine.execute("SELECT recovery, event_date, auction_date FROM defaulted " + "WHERE id=%s", (company_id,)) + recovery, event_date, auction_date = next(r) + fee = 1 - recovery + start_date = previous_twentieth(event_date) + accrual_days, _ = default_accrual(auction_date, event_date, + start_date, end_date, 1., 1.) + return accrual_days, fee -def rebook(engine, secid, lastdate, already_rebooked): - trades = pd.read_sql("SELECT * from cds WHERE security_id = %s", engine, params=(secid,)) - next_secid = next_redcode(engine, secid) - for i in range(trades.shape[0]): - dealid = trades.at[i,'dealid'] - if (secid, dealid) in already_rebooked: - continue - trade = trades.iloc[[i]] - trade = trade.append(trade, ignore_index=True) - del trade['id'] - del trade['lastupdate'] - trade['trade_date'] = lastdate + BDay(1) - trade['cp_code'] = '999' - trade.set_value(0, 'protection', 'Buyer' if trade.at[0,'protection'] == 'Seller' else 'Seller') - trade.set_value(1, 'security_id', next_secid) - trade.to_sql('cds', engine, if_exists='append', index=False) - return (secid, dealid) +def rebook(trade_date, company_id): + upfront_settle_date = trade_date + 3 * BDay() + effective_date = trade_date + datetime.timedelta(days=1) + for r in get_outstanding_positions(trade_date): + accrual_days, fee = default_adjustment(company_id, r['maturity']) + index_new = CreditIndex(redcode=r['nextredindexcode'], + maturity=r['maturity'], + value_date=trade_date, + notional=-r['notional']) -def rebook_everything(engine): - torebook = get_outstanding_positions(engine) - already_rebooked = set([]) - while not torebook.empty: - pdb.set_trace() - secid , lastdate = torebook.iloc[0].tolist() - already_rebooked.add(rebook(engine, secid, lastdate, already_rebooked)) - torebook = get_outstanding_positions(engine) + if index_new.index_type == "HY": + n_constituents = 100 + elif index_new.index_type in ["EU", "IG"]: + n_constituents = 125 + elif index_new.index_type == "XO": + n_constituents = 75 + adj = (fee - accrual_days * index_new.fixed_rate * 1e-4 /360) \ + * r['notional'] * 1/n_constituents + index_new.mark() + trade_new = {'action': 'NEW', + 'folder': r['folder'], + 'cp_code': 'INTERCO', + 'trade_date': trade_date, + 'effective_date': effective_date, + 'maturity': r['maturity'], + 'currency': r['currency'], + 'payment_rolldate': 'Following', + '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_desc': f"CDX {index_new.index_type} CDSI S{index_new.series} 5Y", + 'upfront': -index_new.pv, + 'upfront_settle_date': upfront_settle_date, + 'swap_type': 'CD_INDEX'} + trade_prev = copy(trade_new) + trade_prev['protection'] = "Seller" if trade_new['protection'] == "Buyer" else "Buyer" + trade_prev['upfront'] = index_new.pv - adj + 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_new.values(), trade_prev.values()]) -if __name__=="__main__": - engine = create_engine("postgresql://dawn_user@debian/dawndb") - rebook_everything(engine) +if __name__ == "__main__": + # PKD + rebook(datetime.date(2019, 1, 24), 101148) diff --git a/python/handle_default.py b/python/handle_default.py index 25f4a7a8..c61f071f 100644 --- a/python/handle_default.py +++ b/python/handle_default.py @@ -1,4 +1,4 @@ -from db import dbconn +from db import serenitas_pool import datetime from sys import argv @@ -44,7 +44,7 @@ def update_indexmembers(newids, company_id, conn): and company_id != %s)""", (newid, oldid, company_id)) conn.commit() -def update_redcodes(index_type): +def update_redcodes(index_type, conn): if index_type == 'HY': index_subfamily = 'CDX.NA.HY' elif index_type == 'IG': @@ -73,11 +73,12 @@ if __name__=="__main__": For instance: python handle_default.py 210065 2015-02-19 15.875 100""") else: - with dbconn('serenitasdb') as serenitasdb: - company_id = int(argv[1]) - lastdate = datetime.datetime.strptime(argv[2], "%Y-%m-%d") - recovery = float(argv[3]) - n_issuers = float(argv[4]) - recordslist = affected_indices(company_id, serenitasdb) - newids = create_newindices(recordslist, lastdate, n_issuers, serenitasdb) - update_indexmembers(newids, company_id, serenitasdb) + conn = serenitas_pool.getconn(__name__) + company_id = int(argv[1]) + lastdate = datetime.datetime.strptime(argv[2], "%Y-%m-%d") + recovery = float(argv[3]) + n_issuers = float(argv[4]) + recordslist = affected_indices(company_id, serenitasdb) + newids = create_newindices(recordslist, lastdate, n_issuers, serenitasdb) + update_indexmembers(newids, company_id, serenitasdb) + serenitas_pool.putconn(conn, __name__) |