diff options
| -rw-r--r-- | python/pnl_explain.py | 80 |
1 files changed, 44 insertions, 36 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 7d100053..2b703375 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -63,21 +63,24 @@ def pnl_explain_list(id_list, start_date = None, end_date = None, engine = dbeng return {identifier: pnl_explain(identifier, start_date, end_date, engine) for identifier in id_list} +def compute_tranche_factors(df, attach, detach): + attach, detach = attach/100, detach/100 + df['indexrecovery'] = 1-df.indexfactor-df.cumulativeloss + df = df.assign(tranche_loss = lambda x: (x.cumulativeloss-attach)/(detach-attach), + tranche_recov = lambda x: (x.indexrecovery-(1-detach))/(detach-attach)) + df[['tranche_loss', 'tranche_recov']] = df[['tranche_loss', 'tranche_recov']].clip(lower=0, upper=1) + df['tranche_factor'] = 1-df.tranche_loss - df.tranche_recov + return df + def cds_explain(index, series, tenor, attach = np.nan, detach = np.nan, start_date = None, end_date = None, engine = dbengine('serenitasdb')): - if np.isnan(attach) or np.isnan(detach): + cds_trade = np.isnan(attach) or np.isnan(detach) + if cds_trade: quotes = pd.read_sql_query("SELECT date, (100-closeprice)/100 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)) - sqlstring = "SELECT indexfactor/100 AS indexfactor, coupon, " \ - "cumulativeloss/100 AS cumulativeloss, lastdate " \ - "FROM index_desc WHERE index=%s AND series=%s AND tenor=%s " \ - "ORDER BY lastdate" - factors = pd.read_sql_query(sqlstring, engine, parse_dates=['lastdate'], - index_col='lastdate', - params = (index, series, tenor)) else: #we take the latest version available sqlstring = "SELECT DISTINCT ON (quotedate) quotedate, upfront_mid AS upfront, " \ @@ -87,24 +90,28 @@ def cds_explain(index, series, tenor, attach = np.nan, detach = np.nan, quotes = pd.read_sql_query(sqlstring, engine, parse_dates=['quotedate'], index_col='quotedate', params = (index, series, tenor, int(attach), int(detach))) - sqlstring = "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 " \ + sqlstring = "SELECT indexfactor/100 AS indexfactor, coupon, " \ + "cumulativeloss/100 AS cumulativeloss, lastdate " \ + "FROM index_desc WHERE index=%s AND series=%s AND tenor=%s " \ "ORDER BY lastdate" - factors = pd.read_sql_query(sqlstring, engine, parse_dates=['lastdate'], - index_col='lastdate', - params = (attach, detach, index, series, tenor)) + factors = pd.read_sql_query(sqlstring, engine, parse_dates=['lastdate'], + params = (index, series, tenor)) + if start_date is None: start_date = quotes.index.min() if end_date is None: end_date = pd.datetime.today() - #we use tranche_factor - if not np.isnan(attach): + if not cds_trade: + coupon = quotes.tranche_spread.iat[0]/10000 + factors = compute_tranche_factors(factors, attach, detach) factors['factor'] = factors.tranche_factor + factors['recovery'] = factors.tranche_recov else: + coupon = factors.coupon.iat[0]/10000 factors['factor'] = factors.indexfactor + factors['recovery'] = 1-factors.indexfactor-factors.cumulativeloss + 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') @@ -112,19 +119,19 @@ def cds_explain(index, series, tenor, attach = np.nan, detach = np.nan, 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) - recovery = recovery.reindex(dates, fill_value=0).shift() - df = (quotes. - join(factors[['factor']], how='left'). - join(recovery).join(yearfrac)) - if not np.isnan(attach): - coupon = df.tranche_spread.iat[0]/10000 + + if factors.shape[0]==1 or dates[-1] > max(factors.lastdate): + factors.lastdate.iat[-1] = dates[-1] else: - coupon = factors.coupon.iat[0]/10000 - df.factor = df.factor.bfill() - df.loc[df.factor.isnull(), 'factor'] = factors.factor.iat[-1] + factors = factors.iloc[:-1] + try: + factors = (factors.set_index('lastdate', verify_integrity=True). + reindex(dates, ['factor', 'recovery'], method='bfill')) + except ValueError: + pdb.set_trace() + factors.recovery = factors.recovery.diff() + df = quotes.join([factors[['factor', 'recovery']], yearfrac]) + #df.loc[df.factor.isnull(), 'factor'] = factors.factor.iat[-1] df['clean_nav'] = df.upfront * df.factor df['accrued'] = - df.yearfrac * coupon*df.factor df['unrealized_accrued'] = df.accrued.diff() @@ -138,9 +145,6 @@ def cds_explain(index, series, tenor, attach = np.nan, detach = np.nan, return df[['clean_nav', 'accrued', 'unrealized_accrued', 'realized_accrued', 'unrealized_pnl', 'realized_pnl']] -def convert_to_none(x): - return None if np.isnan(x) else x - def cds_explain_strat(strat, start_date, end_date, engine = dbengine("dawndb")): if not pd.core.common.is_list_like(strat): strat = [strat] @@ -148,7 +152,8 @@ def cds_explain_strat(strat, start_date, end_date, engine = dbengine("dawndb")): parse_dates = ['trade_date', 'upfront_settle_date'], index_col='dealid') cds_positions = cds_positions.ix[cds_positions.folder.isin(strat) & - (cds_positions.upfront_settle_date<=pd.Timestamp(end_date))] + (end_date is None or \ + cds_positions.upfront_settle_date<=pd.Timestamp(end_date))] cds_positions.loc[cds_positions.protection=='Seller', "notional"] *= -1 df = {} for r in cds_positions.itertuples(): @@ -163,7 +168,9 @@ def cds_explain_strat(strat, start_date, end_date, engine = dbengine("dawndb")): if start_date is None or (start_date <= r.trade_date): trade_df.realized_accrued.iat[3] -= trade_df.accrued.iat[0] extra_pnl = trade_df.clean_nav.iat[0] + trade_df.accrued.iat[0] + r.upfront - trade_df.unrealized_pnl.iat[3] += extra_pnl + print(trade_df.accrued.iat[0] + r.upfront) + trade_df.unrealized_pnl.iat[0] = extra_pnl + trade_df.loc[:3, 'unrealized_accrued'] = 0 df[key] = trade_df.add(df.get(key, 0), fill_value=0) return pd.concat(df) @@ -174,6 +181,7 @@ if __name__=="__main__": df = pnl_explain_list(clo_list.identifier.tolist(), None, '2015-11-30', engine) df = pd.concat(df) df_agg = df.groupby(level=1).sum() - #cds_df = cds_explain_strat(['SER_IGINX', 'SER_IGMEZ'], None, '2015-12-08', engine) - cds_df = cds_explain_strat(['SER_HYMEZ'], None, '2015-03-09', engine) + cds_df = cds_explain_strat(['SER_IGINX', 'SER_IGMEZ'], None, '2015-12-08', engine) + #cds_df = cds_explain_strat(['SER_HYMEZ'], None, '2015-03-10', engine) + #cds_df2 = cds_explain_strat('SER_IGCURVE', None, None, engine) #cds_df = cds_explain('HY', 21, '5yr', 25, 35, '2014-07-18') |