diff options
| -rw-r--r-- | python/pnl_explain_old.py | 50 |
1 files changed, 25 insertions, 25 deletions
diff --git a/python/pnl_explain_old.py b/python/pnl_explain_old.py index 25af238e..b41a739a 100644 --- a/python/pnl_explain_old.py +++ b/python/pnl_explain_old.py @@ -1,30 +1,26 @@ import numpy as np import pandas as pd -from db import dbengine +from utils.db import dbengine from dates import bus_day, imm_dates, yearfrac +from pandas.tseries.offsets import BDay -def get_daycount(identifier, engine=dbengine("dawndb")): +def get_daycount(conn, identifier): """ 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 + return c.fetchone() -def pnl_explain(identifier, start_date = None, end_date = None, - engine=dbengine("dawndb")): +def pnl_explain(conn, identifier, start_date = None, end_date = None, + fund="SERCGMAST"): """ 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']) - for key in ['faceamount', 'principal_payment', 'accrued_payment']: - trades.loc[~trades.buysell, key] = -trades[key][~trades.buysell] + trades = pd.read_sql_query("SELECT * FROM bonds where identifier=%s " + "AND fund=%s", + conn, + params=(identifier, fund), + parse_dates=['trade_date', 'settle_date']) + trades.loc[~trades.buysell, ['faceamount', 'principal_payment', 'accrued_payment']] *= -1.0 if start_date is None: start_date = trades.trade_date.min() @@ -32,13 +28,17 @@ def pnl_explain(identifier, start_date = None, end_date = None, trades = (trades. groupby('settle_date')[['faceamount', 'principal_payment', 'accrued_payment']]. sum()) - 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']) + marks = pd.read_sql_query("SELECT * FROM marks where identifier=%s", + conn, + params=(identifier,), + parse_dates=['date'], + index_col='date') + factors = pd.read_sql_query("SELECT * FROM factors_history where identifier=%s", + conn, + 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) - + daycount, delay = get_daycount(conn, identifier) df = (marks[['price']]. join([factors[['prev_cpn_date', 'coupon', 'factor']], trades[['principal_payment', 'accrued_payment', 'faceamount']]], @@ -62,11 +62,11 @@ def pnl_explain(identifier, start_date = None, end_date = None, df[keys2] = df[keys2].fillna(value=0) df.faceamount = df.faceamount.cumsum() keys = keys1 + ['faceamount', 'orig_factor'] - df1 = df.reindex(df.index.union(dates), keys, method='ffill') + df1 = df.reindex(index=df.index.union(dates), method='ffill')[keys] keys = ['losses', 'principal','interest', 'accrued_payment', 'principal_payment'] - df2 = df.reindex(df.index.union(dates), keys, fill_value=0) + df2 = df.reindex(index=df.index.union(dates), fill_value=0)[keys] daily = pd.concat([df1, df2], axis = 1) - daily = daily[(start_date-1):end_date] + daily = daily[(start_date-BDay()):end_date] daily['unrealized_pnl'] = daily.price.diff() * daily.factor.shift()/100 * daily.faceamount daily['clean_nav'] = daily.price/100 * daily.factor * daily.faceamount ## realized pnl due to factor change |