import pandas as pd from db import dbengine from dates import bus_day, imm_dates, yearfrac import numpy as np from psycopg2.extensions import register_adapter, AsIs register_adapter(np.int64, lambda x: AsIs(x)) def get_daycount(identifier, engine = dbengine("dawndb")): """ retrieve daycount and paydelay for a given identifier""" conn = engine.raw_connection() with conn.cursor() as c: c.execute("SELECT day_count, pay_delay FROM securities WHERE identifier=%s", (identifier,)) try: a, b = c.fetchone() except TypeError: conn.commit() return None, None conn.commit() return a, b 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=['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, params=(identifier,), parse_dates = ['last_pay_date', 'prev_cpn_date']) factors = factors.set_index('prev_cpn_date', drop=False) daycount, delay = get_daycount(identifier, engine) for key in ['faceamount', 'principal_payment', 'accrued_payment']: trades.loc[~trades.buysell, key] = -trades[key][~trades.buysell] 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.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) 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['clean_nav'] = daily.price/100 * daily.factor * daily.faceamount daily['realized_pnl'] = (daily.price/100 * daily.factor.diff() + daily.principal/100) * \ daily.faceamount daily['realized_accrued'] = daily.interest/100 * daily.faceamount daily['accrued'] = yearfrac(daily.prev_cpn_date, daily.index.to_series(), daycount) * \ daily.coupon/100*daily.factor * daily.faceamount daily['unrealized_accrued'] = daily.accrued.diff() + daily.realized_accrued # for some reason .shift(0, bus_day) doesn't work (but would work on an index) daily['realized_accrued'] = (daily.loc[daily.realized_accrued>0, 'realized_accrued']. shift(delay, 'D').shift(-1, bus_day).shift(1, bus_day)) daily['realized_pnl'] = (daily.loc[daily.realized_pnl>0, 'realized_pnl']. shift(delay, 'D').shift(-1, bus_day).shift(1, bus_day)) daily[['realized_pnl', 'realized_accrued']] = (daily[['realized_pnl', 'realized_accrued']]. fillna(value=0)) 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 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 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')): 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)) 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 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'], params = (index, series, tenor)) if start_date is None: start_date = quotes.index.min() if end_date is None: end_date = pd.datetime.today() 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') yearfrac = yearfrac.index-yearfrac yearfrac = (yearfrac.dt.days+1)/360 yearfrac.name = 'yearfrac' quotes = quotes.reindex(dates, method='ffill') if factors.shape[0]==1 or dates[-1] > max(factors.lastdate): factors.lastdate.iat[-1] = dates[-1] else: 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() 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() 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 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.isin(strat) & (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(): 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, 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[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) if __name__=="__main__": workdate = pd.datetime.today() engine = dbengine("dawndb") from position import get_list clo_list = get_list(engine, workdate, 'CLO') 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-16', 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')