diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/pnl_explain.py | 39 |
1 files changed, 23 insertions, 16 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 642fb175..7d100053 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -13,7 +13,7 @@ def pnl_explain(identifier, start_date = None, end_date = None, """ if start_date is None, pnl since inception""" trades = pd.read_sql_query("SELECT * FROM bonds where identifier=%s", engine, params=(identifier,), parse_dates=['trade_date', 'settle_date'], - index_col=['trade_date']) + index_col=['settle_date']) marks = pd.read_sql_query("SELECT * FROM marks where identifier=%s", engine, params=(identifier,), parse_dates = ['date'], index_col='date') factors = pd.read_sql_query("SELECT * FROM factors_history where identifier=%s", engine, @@ -23,11 +23,12 @@ def pnl_explain(identifier, start_date = None, end_date = None, for key in ['faceamount', 'principal_payment', 'accrued_payment']: trades.loc[~trades.buysell, key] = -trades[key][~trades.buysell] - df = (marks[['price']].join(factors, how='outer'). - join(trades[['principal_payment', 'accrued_payment', 'faceamount']], how='outer')) + df = (marks[['price']]. + join([factors, trades[['principal_payment', 'accrued_payment', 'faceamount']]], + how='outer')) df.sort_index(inplace=True) if start_date is None: - start_date = trades.index.min() + start_date = trades.trade_date.ix[trades.index.min()] if end_date is None: end_date = pd.datetime.today() dates = pd.date_range(start_date, end_date, freq = bus_day) @@ -132,7 +133,7 @@ def cds_explain(index, series, tenor, attach = np.nan, detach = np.nan, 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['unrealized_pnl'] = df.upfront.diff() * df.factor.shift() df['realized_pnl'] = df.upfront/100*df.factor.diff()+df.recovery return df[['clean_nav', 'accrued', 'unrealized_accrued', 'realized_accrued', 'unrealized_pnl', 'realized_pnl']] @@ -141,32 +142,38 @@ 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] cds_positions = pd.read_sql_table("orig_cds", engine, parse_dates = ['trade_date', 'upfront_settle_date'], index_col='dealid') - cds_positions = cds_positions.ix[(cds_positions.folder == strat) & + cds_positions = cds_positions.ix[cds_positions.folder.isin(strat) & (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(): key = (r.index, r.series, r.tenor) + if start_date is not None: + start_date = max(r.trade_date, pd.Timestamp(start_date)) + else: + start_date = r.trade_date trade_df = cds_explain(r.index, r.series, r.tenor, r.attach, r.detach, - max(r.trade_date, pd.Timestamp(start_date)), end_date, - engine) - trade_df = r.notional*trade_df - if pd.Timestamp(start_date) <= r.trade_date: - extra_pnl = trade_df.clean_nav.iat[0]+trade_df.accrued.iat[2] + r.upfront - trade_df.unrealized_pnl.iat[2] = extra_pnl - trade_df = trade_df.iloc[2:] - df[key] = df.get(key, 0) + trade_df + start_date, end_date, engine) + trade_df = r.notional * trade_df + 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 + df[key] = trade_df.add(df.get(key, 0), fill_value=0) return pd.concat(df) if __name__=="__main__": 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 = 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_IGMEZ', '2014-09-18', '2015-12-08', 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-09', engine) #cds_df = cds_explain('HY', 21, '5yr', 25, 35, '2014-07-18') |