diff options
| -rw-r--r-- | python/load_globeop_report.py | 37 |
1 files changed, 25 insertions, 12 deletions
diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py index 6b926ba7..6fd998b9 100644 --- a/python/load_globeop_report.py +++ b/python/load_globeop_report.py @@ -51,25 +51,38 @@ def pnl_reports(): if date in df: print(date) df[date] = pd.read_csv(f) - df = pd.concat(df) + df = pd.concat(df, names=['date', 'to_drop']) + df.reset_index(level='to_drop', drop=True, inplace=True) + df.Strat = df.Strat.str.replace("^SERCGMAST__M_", "", 1) for col in ['Fund', 'Strat', 'Port', 'LongShortIndicator', 'InvCcy']: df[col] = df[col].astype('category') df.to_hdf('globeop.hdf', 'pnl', format='table', complib='blosc') +def ts(s): + return pd.Timestamp(s) + +def monthly_pnl_bycusip(df, strats): + df = df[(df.Strat.isin(strats)) & (df.CustAcctName=='V0NSCLMAMB')] + pnl_cols = ['BookUnrealMTM', 'BookRealMTM', 'BookRealIncome', 'BookUnrealIncome', + 'TotalBookPL'] + return df.groupby('InvId').resample('M', 'last')[['MTD '+col for col in pnl_cols]] if __name__=='__main__': - valuation_reports() - pnl_reports() + #valuation_reports() + #pnl_reports() df_val = pd.read_hdf('globeop.hdf', 'valuation_report') df_pnl = pd.read_hdf('globeop.hdf', 'pnl') nav = df_val[df_val.Fund=='SERCGMAST'].groupby('PeriodEndDate')['EndBookNAV'].sum() - subprime_strats = ['SERCGMAST__M_MTG_GOOD', 'SERCGMAST__M_MTG_RW', - 'SERCGMAST__M_MTG_IO','SERCGMAST__M_MTG_THRU', - 'SERCGMAST__M_MTG_B4PR'] - clo_strats = ['SERCGMAST__M_CLO_BBB', 'SERCGMAST__M_CLO_AAA', 'SERCGMAST__M_CLO_BB20'] - subprime = df_pnl[df_pnl.Strat.isin(subprime_strats)] - subprime_monthly_pnl = subprime.groupby(level=0).sum()['MTD TotalBookPL'].resample('M', how='last') - clo = df_pnl[df_pnl.Strat.isin(clo_strats)] - clo_monthly_pnl = clo.groupby(level=0).sum()['MTD TotalBookPL'].resample('M', how='last') + subprime_strats = ['MTG_GOOD', 'MTG_RW', 'MTG_IO','MTG_THRU', 'MTG_B4PR'] + clo_strats = ['CLO_BBB', 'CLO_AAA', 'CLO_BB20'] + + ## daily pnl by cusip + #subprime_daily_pnl = daily_pnl_bycusip(df_pnl, subprime_strats) + + df_monthly = monthly_pnl_bycusip(df_pnl, subprime_strats) + #df_monthly.loc[idx[ts('2015-01-01'):ts('2015-01-31'),:],:] + + # clo = df_pnl[df_pnl.Strat.isin(clo_strats)] + # clo_monthly_pnl = clo.groupby(level=0).sum()['MTD TotalBookPL'].resample('M', how='last') - clo.groupby(level=0).sum()['2015-12-01':'2015-12-31'] + # clo.groupby(level=0).sum()['2015-12-01':'2015-12-31'] |