diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/pnl_explain.py | 107 |
1 files changed, 69 insertions, 38 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 73c76fe6..360dc45f 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -1,13 +1,12 @@ import pandas as pd from functools import reduce from position import get_list -from sqlalchemy import create_engine +from db import dbengine from dates import bus_day, imm_dates def pnl_explain(identifier, start_date = None, end_date = None, - uri = 'postgresql://dawn_user@debian/dawndb'): + engine = dbengine("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']) @@ -44,42 +43,61 @@ def pnl_explain(identifier, start_date = None, end_date = None, daily['clean_nav'] = daily.price/100 * daily.factor * daily.faceamount daily['realized_accrued'] = daily.interest/100 * daily.faceamount days_accrued = daily.index - daily.prev_cpn_date - daily['unrealized_accrued'] = days_accrued.dt.days/360*daily.coupon/100*daily.factor*daily.faceamount + daily['accrued'] = days_accrued.dt.days/360*daily.coupon/100*daily.factor*daily.faceamount extra_pnl = daily.clean_nav.diff() - daily.principal_payment daily.loc[daily.principal_payment>0 , 'unrealized_pnl'] += extra_pnl[daily.principal_payment>0] daily.loc[daily.principal_payment<0, 'realized_pnl'] += extra_pnl[daily.principal_payment<0] daily['realized_accrued'] -= daily.accrued_payment - daily['accrued'] = daily.unrealized_accrued.diff() + daily.realized_accrued - return daily[['unrealized_pnl', 'realized_pnl', 'unrealized_accrued', 'realized_accrued', 'accrued']].iloc[1:,] + daily['unrealized_accrued'] = daily.accrued.diff() + daily.realized_accrued + return daily[['clean_nav', 'accrued', 'unrealized_pnl', 'realized_pnl', 'unrealized_accrued', + 'realized_accrued']].iloc[1:,] -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 pnl_explain_list(id_list, start_date = None, end_date = None, engine = dbengine("dawndb")): + return {identifier: pnl_explain(identifier, start_date, end_date, engine) + 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)) +def cds_explain(index, series, tenor, attach = None, detach = None, + start_date = None, end_date = None, engine = dbengine('serenitasdb')): 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", + quotes = pd.read_sql_query("SELECT date, (100-closeprice) AS upfront 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)) + factors = pd.read_sql_query(""" +SELECT indexfactor/100 AS indexfactor, cumulativeloss/100 AS cumulativeloss +lastdate FROM index_desc WHERE index=%s AND series=%s AND tenor=%s ORDER BY lastdate +""", + engine, parse_dates=['lastdate'], index_col='lastdate', + params = (attach, detach, 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 " \ + #we take the latest version available + quotes = pd.read_sql_query("SELECT DISTINCT ON (quotedate) quotedate, upfront_mid AS upfront, "\ + "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", + "ORDER by quotedate, version desc", engine, parse_dates=['quotedate'], index_col='quotedate', params = (index, series, tenor, attach, detach)) + factors = pd.read_sql_query(""" +SELECT tranche_factor(%s::smallint, %s::smallint, indexfactor, cumulativeloss/100), +indexfactor/100 AS indexfactor, cumulativeloss/100 AS cumulativeloss, lastdate +FROM index_desc WHERE index=%s AND series=%s AND tenor=%s ORDER BY lastdate +""", + "postgresql://serenitas_user@debian/serenitasdb", + parse_dates=['lastdate'], index_col='lastdate', + params = (attach, detach, index, series, tenor)) if start_date is None: start_date = quotes.index.min() if end_date is None: end_date = pd.datetime.today() - coupon = 0.01 + + #we use tranche_factor + if attach: + factors['factor'] = factors.tranche_factor + else: + factors['factor'] = factors.indexfactor + 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') @@ -89,25 +107,38 @@ def cds_explain(engine, index, series, tenor, attach = None, detach = None, quotes = quotes.reindex(dates, method='ffill') recovery = -factors.indexfactor.diff()-factors.cumulativeloss.diff() recovery.name = 'recovery' - recovery = recovery.shift(-1)/100 + recovery = recovery.shift(-1) recovery = recovery.reindex(dates, fill_value=0).shift() df = (quotes. - join(factors[['indexfactor']], how='left'). + join(factors[['factor']], 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 + if attach: + coupon = df.tranche_spread.iat[0]/10000 + else: + coupon = factors.coupon.iat[0]/10000 + df.indexfactor = df.indexfactor.bfill() + df.loc[df.indexfactor.isnull(), 'indexfactor'] = factors.factor.iat[-1] + df['clean_nav'] = df.upfront*df.factor + df['accrued'] = df.yearfrac*coupon*df.factor + df['unrealized_accrued'] = df.accrued.diff() + df['realized_accrued'] = -df.unrealized_accrued.where(df.unrealized_accrued.isnull() | + (df.unrealized_accrued<0), 0) + df['unrealized_accrued'] = df.unrealized_accrued.where(df.unrealized_accrued.isnull()| + (df.unrealized_accrued>0), df.accrued) + df.loc[df.realized_accrued>0, 'realized_accrued'] += df.loc[df.realized_accrued>0, 'unrealized_accrued'] + df['unrealized_pnl'] = df.upfront.diff() * df.factor.shift()/100 + df['realized_pnl'] = df.upfront/100*df.indexfactor.diff()+df.recovery return df + +def cds_explain2(dealid): + pass + if __name__=="__main__": - # 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') + workdate = pd.datetime.today() + engine = dbengine("dawndb") + clo_list = get_list(engine, workdate, 'CLO') + df = pnl_explain_list(clo_list.identifier.tolist(), '2015-10-30', '2015-11-30', engine) + df = pd.concat(df) + df_agg = df.groupby(level=1).sum() + cds_df = cds_explain('HY', 21, '5yr', 25, 35, '2014-07-18') |