diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/pnl_explain.py | 65 |
1 files changed, 59 insertions, 6 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 2c93cbd7..73c76fe6 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -2,9 +2,12 @@ import pandas as pd from functools import reduce from position import get_list from sqlalchemy import create_engine +from dates import bus_day, imm_dates -def pnl_explain(engine, identifier, start_date = None, end_date = None): +def pnl_explain(identifier, start_date = None, end_date = None, + uri = 'postgresql://dawn_user@debian/dawndb'): """ if start_date is None, pnl since inception""" + engine = create_engine(uri) trades = pd.read_sql_query("SELECT * FROM bonds where identifier=%s", engine, params=(identifier,), parse_dates=['trade_date', 'settle_date'], index_col=['settle_date']) @@ -24,7 +27,7 @@ def pnl_explain(engine, identifier, start_date = None, end_date = None): start_date = trades.index.min() if end_date is None: end_date = pd.datetime.today() - dates = pd.bdate_range(start_date, end_date) + dates = pd.date_range(start_date, end_date, freq = bus_day) keys1 = ['price','factor', 'coupon', 'prev_cpn_date'] df[keys1] = df[keys1].fillna(method='ffill') keys2 = ['losses', 'principal','interest', 'faceamount','accrued_payment', 'principal_payment'] @@ -53,8 +56,58 @@ def pnl_explain_list(engine, id_list, start_date = None, end_date = None): return reduce(lambda x,y: x.add(y, fill_value=0), (pnl_explain(engine, identifier, start_date, end_date) for identifier in id_list)) +def cds_explain(engine, index, series, tenor, attach = None, detach = None, + start_date = None, end_date = None): + factors = pd.read_sql_query("SELECT * FROM index_desc WHERE index=%s AND series=%s AND tenor=%s "\ + "ORDER BY lastdate", + engine, parse_dates=['lastdate'], + index_col='lastdate', params = (index, series, tenor)) + if attach is None: + quotes = pd.read_sql_query("SELECT * from index_quotes WHERE index=%s AND series=%s AND tenor=%s " \ + "ORDER BY date", + engine, parse_dates=['date'], + index_col='date', params = (index, series, tenor)) + else: + quotes = pd.read_sql_query("SELECT quotedate, upfront_mid AS closeprice, tranche_spread " \ + "FROM markit_tranche_quotes JOIN index_version " \ + "USING (basketid) WHERE index=%s AND series=%s " \ + "AND tenor=%s AND attach=%s AND detach=%s " \ + "ORDER by quotedate", + engine, parse_dates=['quotedate'], index_col='quotedate', + params = (index, series, tenor, attach, detach)) + if start_date is None: + start_date = quotes.index.min() + if end_date is None: + end_date = pd.datetime.today() + coupon = 0.01 + dates = pd.date_range(start_date, end_date, freq = bus_day) + yearfrac = imm_dates(start_date, end_date) + yearfrac = yearfrac.to_series().reindex(dates, method='ffill') + yearfrac = yearfrac.index-yearfrac + yearfrac = (yearfrac.dt.days+1)/360 + yearfrac.name = 'yearfrac' + quotes = quotes.reindex(dates, method='ffill') + recovery = -factors.indexfactor.diff()-factors.cumulativeloss.diff() + recovery.name = 'recovery' + recovery = recovery.shift(-1)/100 + recovery = recovery.reindex(dates, fill_value=0).shift() + df = (quotes. + join(factors[['indexfactor']], how='left'). + join(recovery).join(yearfrac)) + df.indexfactor = df.indexfactor.bfill()/100 + df.loc[df.indexfactor.isnull(), 'indexfactor'] = factors.indexfactor.iat[-1]/100 + df['unrealized_accrued'] = df.yearfrac*coupon*df.indexfactor + df['accrued'] = df.unrealized_accrued.diff() + df['realized_accrued'] = -df.accrued.where(df.accrued<0, 0) + df.accrued = df.accrued.where(df.accrued>0, df.unrealized_accrued) + df.loc[df.realized_accrued>0, 'realized_accrued'] += df.loc[df.realized_accrued>0, 'accrued'] + df['unrealized_pnl'] = df.closeprice.diff() * df.indexfactor.shift()/100 + df['realized_pnl'] = df.closeprice/100*df.indexfactor.diff()+df.recovery + return df + if __name__=="__main__": - engine = create_engine("postgresql://dawn_user@debian/dawndb") - workdate = pd.datetime.today() - clo_list = get_list(engine, workdate, 'Subprime') - df = pnl_explain_list(engine, clo_list.identifier.tolist(), '2015-10-30', '2015-11-30') + # workdate = pd.datetime.today() + # clo_list = get_list(workdate, 'Subprime') + # df = pnl_explain_list(engine, clo_list.identifier.tolist(), '2015-10-30', '2015-11-30') + engine = create_engine("postgresql://serenitas_user@debian/serenitasdb") + df = cds_explain(engine, 'IG', 9, '10yr') |