diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 25 |
1 files changed, 12 insertions, 13 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 965649e1..4d89f081 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -1,4 +1,4 @@ -from db import dbengine +from db import dbengine, dawn_engine from pandas.tseries.offsets import DateOffset, MonthEnd from yieldcurve import YC from quantlib.time.api import Date, Months, Period @@ -9,12 +9,10 @@ import pandas as pd import numpy as np import datetime -etengine = dbengine('etdb') -dawnengine = dbengine('dawndb') def get_monthly_pnl(group_by=['identifier']): sql_string = "SELECT * FROM pnl_reports" - df_pnl = pd.read_sql_query(sql_string, dawnengine, + df_pnl = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['date'], index_col=['date']) df_pnl['identifier'] = df_pnl.invid.str.replace("_A$", "") @@ -26,11 +24,11 @@ def get_monthly_pnl(group_by=['identifier']): def get_portfolio(report_date=None): if report_date is not None: sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" - df = pd.read_sql_query(sql_string, dawnengine, parse_dates=['periodenddate'], + df = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['periodenddate'], index_col=['periodenddate'], params=[report_date,]) else: sql_string = "SELECT * FROM valuation_reports" - df = pd.read_sql_query(sql_string, dawnengine, parse_dates=['periodenddate'], + df = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['periodenddate'], index_col=['periodenddate']) df['identifier'] = df.invid.str.replace("_A$", "") return df @@ -38,7 +36,7 @@ def get_portfolio(report_date=None): def trade_performance(): sql_string = "SELECT * FROM bonds" - df_trades = pd.read_sql_query(sql_string, dawnengine, + df_trades = pd.read_sql_query(sql_string, dawn_engine, parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}}) @@ -77,7 +75,7 @@ def trade_performance(): def get_net_navs(): sql_string = "SELECT * FROM valuation_reports" - df_val = pd.read_sql_query(sql_string, dawnengine, parse_dates=['periodenddate']) + df_val = pd.read_sql_query(sql_string, dawn_engine, 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', @@ -153,7 +151,7 @@ def rmbs_pos(date, model_date=None, dm=False): "model_version = 1 and " "date(timestamp) < %s and date(timestamp) > %s " "order by timestamp desc") - timestamps = pd.read_sql_query(sql_string, dawnengine, parse_dates=['timestamp'], + timestamps = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['timestamp'], params=[date, date - DateOffset(15, 'D')]) if model_date is None: model_date = (timestamps.loc[0][0]).date() @@ -166,12 +164,12 @@ def rmbs_pos(date, model_date=None, dm=False): "and model_version <> 2") params_list = (model_date,) if date > datetime.date(2017, 9, 30): - r = dawnengine.execute("SELECT latest_sim FROM latest_sim(%s)", + r = dawn_engine.execute("SELECT latest_sim FROM latest_sim(%s)", model_date) model_id, = next(r) sql_string += " AND model_id_sub = %s" params_list += (model_id,) - model = pd.read_sql_query(sql_string, dawnengine, parse_dates=['timestamp'], + model = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['timestamp'], params=params_list) model = model[model['pv'] != 0] pos = pos.assign(curr_ntl = pos.endbookmv/pos.endlocalmarketprice *100) @@ -211,13 +209,14 @@ def clo_pos(date): return df else: sql_string = "select distinct cusip, identifier from bonds where asset_class = 'CLO'" - cusip_map = {r['identifier']: r['cusip'] for r in dawnengine.execute(sql_string)} + cusip_map = {identifier: cusip for cusip, identifier in dawn_engine.execute(sql_string)} df['cusip'] = df['identifier'].replace(cusip_map) cusips = df.loc[[df.index[-1]], 'cusip'] placeholders = ",".join(["%s"] * (1 + len(cusips))) sql_string = f"SELECT * FROM historical_cusip_risk({placeholders})" + etengine = dbengine('etdb') model = pd.read_sql_query(sql_string, etengine, parse_dates=['pricingdate'], - params=(date, *cusips)) + params=(date, *cusips)) model.index = cusips calc_df = df.loc[[df.index[-1]]].set_index('cusip').join(model) calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv |