import pandas as pd from functools import reduce from position import get_list from db import dbengine from dates import bus_day, imm_dates import numpy as np from psycopg2.extensions import register_adapter, AsIs register_adapter(np.int64, lambda x: AsIs(x)) def pnl_explain(identifier, start_date = None, end_date = None, engine = dbengine("dawndb")): """ 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']) 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, params=(identifier,), parse_dates = ['last_pay_date', 'prev_cpn_date'], index_col=['last_pay_date']) 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.sort_index(inplace=True) if start_date is None: start_date = trades.index.min() if end_date is None: end_date = pd.datetime.today() 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'] df[keys2] = df[keys2].fillna(value=0) df.faceamount = df.faceamount.cumsum() keys = keys1 + ['faceamount'] df1 = df.reindex(dates, keys, method='ffill') keys = ['losses', 'principal','interest', 'accrued_payment', 'principal_payment'] df2 = df.reindex(dates, keys, fill_value=0) daily = pd.concat([df1, df2], axis = 1) daily['unrealized_pnl'] = daily.price.diff() * daily.factor.shift()/100 * daily.faceamount daily['realized_pnl'] = (daily.price/100 * daily.factor.diff() + daily.principal/100) * \ daily.faceamount 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['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['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(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(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): 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, " \ "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, version desc" 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 " \ "ORDER BY lastdate" factors = pd.read_sql_query(sqlstring, engine, 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() #we use tranche_factor if not np.isnan(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') 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) 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 else: coupon = factors.coupon.iat[0]/10000 df.factor = df.factor.bfill() 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() 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.factor.diff()+df.recovery 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")): 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.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) 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 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 = 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('HY', 21, '5yr', 25, 35, '2014-07-18')