diff options
Diffstat (limited to 'python/cds_rebook.py')
| -rw-r--r-- | python/cds_rebook.py | 123 |
1 files changed, 69 insertions, 54 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) |