diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 21 |
1 files changed, 14 insertions, 7 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index cbd73673..a140672e 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -3,6 +3,7 @@ import os import pandas as pd import datetime from db import dbengine +from pandas.tseries.offsets import MonthEnd import load_globeop_report as load_globeop def get_monthly_pnl(): @@ -21,12 +22,6 @@ def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): df_all = df_positions.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) return df_all -def check_valuation(): - sql_string = "SELECT * FROM valuation_reports" - df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate']) - nav = df_val[df_val.fund == 'SERCGMAST'].groupby('periodenddate')['endbooknav'].sum() - return nav.resample('M').last() - def trade_performance(): sql_string = "SELECT * FROM bonds" df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates= ['lastupdate', 'trade_date','settle_date']) @@ -35,9 +30,12 @@ def trade_performance(): df_sell = df_trades[df_trades.buysell == False].groupby('identifier').last().reset_index() df_sell.identifier = df_sell.identifier.str[:9] + df_sell['trade_pnl_date'] = df_sell.trade_date + MonthEnd(0) df_buy = df_trades[df_trades.buysell == True].groupby('identifier').last().reset_index() df_all = df_sell.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) + df_all = df_all.merge(df_pnl.reset_index()[['date', 'identifier', 'mtdtotalbookpl']], left_on=['trade_pnl_date','identifier'], right_on=['date','identifier'], suffixes=('','_at_trade_month')) + df_all = df_all.drop(['date','trade_pnl_date'], axis = 1) #now build up the table g = df_buy.groupby('identifier').sum() @@ -59,7 +57,16 @@ def trade_performance(): return df_all +def get_net_navs(): + sql_string = "SELECT * FROM valuation_reports" + df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate']) + nav = df_val[df_val.fund == 'SERCGMAST'].groupby('periodenddate')['endbooknav'].sum() + nav = nav.resample('M').last() + df = pd.read_csv('/home/serenitas/edwin/Python/subscription_fee_data.csv', parse_dates=['date'], index_col =['date']) + df.index = df.index.to_period('M').to_timestamp('M') + return df.join(nav) + if __name__=='__main__': - nav = check_valuation() + nav = get_net_navs() df_pnl = trade_performance() df_curr_port = curr_port_PNL() |