from serenitas.utils.db import dbengine, dawn_engine from pandas.tseries.offsets import DateOffset, MonthEnd from serenitas.analytics.yieldcurve import YC from quantlib.time.api import Date, Months, Period from quantlib.indexes.api import USDLibor from quantlib.termstructures.yield_term_structure import YieldTermStructure import pandas as pd import numpy as np import datetime def get_monthly_pnl(group_by=["identifier"], fund="SERCGMAST"): sql_string = "SELECT * FROM pnl_reports where fund = %s" df_pnl = pd.read_sql_query( sql_string, dawn_engine, parse_dates=["date"], index_col=["date"], params=[fund] ) df_pnl["identifier"] = df_pnl.invid.str.replace("_A$", "", regex=True) pnl_cols = [ "bookunrealmtm", "bookrealmtm", "bookrealincome", "bookunrealincome", "totalbookpl", ] monthend_pnl = df_pnl.groupby(pd.Grouper(freq="M"), group_keys=False).apply( lambda df: df.loc[df.index[-1]] ) return monthend_pnl.groupby(["date"] + group_by)[ ["mtd" + col for col in pnl_cols] ].sum() 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, 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, dawn_engine, parse_dates=["periodenddate"], index_col=["periodenddate"], ) df["identifier"] = df.invid.str.replace("_A$", "", regex=True) return df def trade_performance(): sql_string = "SELECT * FROM bonds" df_trades = pd.read_sql_query( sql_string, dawn_engine, parse_dates={"lastupdate": {"utc": True}, "trade_date": {}, "settle_date": {}}, ) df_trades = df_trades[df_trades["asset_class"] == "Subprime"] df_pnl = get_monthly_pnl() df_sell = df_trades[~df_trades.buysell].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].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() init_inv = g.principal_payment + g.accrued_payment init_inv.name = "initialinvestment" first_buy_date = df_buy.groupby("identifier").first().trade_date first_buy_date.name = "firstbuydate" df_all = df_all.join(init_inv, on="identifier") df_all = df_all.join(first_buy_date, on="identifier") df_all["percent_gain"] = df_all.mtdtotalbookpl / df_all.initialinvestment df_all["days_held"] = df_all.trade_date - df_all.firstbuydate df_all = df_all.sort_values("trade_date", ascending=False) return df_all def get_net_navs(): sql_string = "SELECT * FROM valuation_reports" 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() sql_string = "SELECT * FROM subscription_and_fee where fund ='SERCGMAST'" df = pd.read_sql_query( sql_string, dawn_engine, parse_dates=["date"], index_col=["date"] ) df["net_flow"] = df.subscription.fillna(0) - df.redemption.fillna(0) df.index = df.index.to_period("M").to_timestamp("M") df = df.join(nav) df.sort_index(inplace=True) df["begbooknav"] = (df.endbooknav + df.net_flow).shift(1) df.at[("2013-01-31", "begbooknav")] = 12500000 return df def shift_cash(date, amount, df, strat): nav = get_net_navs() df.loc[date, strat] = df.loc[date, strat] - amount / nav.loc[date].endbooknav df.loc[date, "Cash"] = df.loc[date, "Cash"] + amount / nav.loc[date].endbooknav return df def calc_trade_performance_stats(): df = trade_performance().set_index("trade_date") df.days_held = df.days_held.dt.days df["winners"] = df.percent_gain > 0 df["curr_face"] = df.principal_payment / (df.price / 100) index = ["All", "2017", "2016", "2015", "2014", "2013"] results = pd.DataFrame(index=index) win_per = df.winners.mean() loss_per = 1 - win_per for x, df1 in df.groupby("winners"): for y, df2 in df1.groupby(pd.Grouper(freq="A")): y = y.date().year results.loc[y] = df2[df2.days_held.notnull()].mean()[ ["curr_face", "initialinvestment", "days_held"] ] df[df.days_held.notnull()]["days_held"].groupby(pd.Grouper(freq="A")).mean() def hist_pos(asset_class="rmbs", dm=False): end_date = pd.datetime.today() - MonthEnd(1) dates = pd.date_range(datetime.date(2013, 1, 31), end_date, freq="M") pos = [] for d in dates.dt.date: if asset_class == "rmbs": if d == datetime.date(2018, 11, 30): d_1 = datetime.date(2018, 12, 3) elif d == datetime.date(2016, 2, 29): d_1 = datetime.date(2016, 2, 29) else: d_1 = None pos.append(rmbs_pos(d, d_1, dm)) else: pos.append(clo_pos(d)) return pd.concat(pos) def rmbs_pos(date, model_date=None, dm=False): date = date.date() if isinstance(date, pd.Timestamp) else date # just non-zero factor bonds for now, need to incorporate that pos = get_portfolio(date) pos = pos[ (pos.port == "MORTGAGES") & (pos.endbookmv > 0) & (pos.custacctname == "V0NSCLMAMB") & (pos["invid"].str.len() == 9) ] pos = pos[["endbookmv", "endlocalmarketprice", "identifier"]] sql_string = ( "SELECT distinct timestamp FROM priced where " "normalization = 'current_notional' and " "model_version = 1 and " "date(timestamp) < %s and date(timestamp) > %s " "order by timestamp desc" ) 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() yc = YieldTermStructure() yc.link_to(YC(evaluation_date=model_date)) sql_string = ( "SELECT date(timestamp) as timestamp, cusip, model_version, " "pv, moddur, delta_yield, delta_ir " "FROM priced where date(timestamp) = %s " "and model_version <> 2" ) params_list = (model_date,) if date > datetime.date(2017, 9, 30): 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, dawn_engine, parse_dates=["timestamp"], params=params_list ) model = model[model["pv"] != 0] pos = pos.assign(curr_ntl=pos.endbookmv / pos.endlocalmarketprice * 100) comb_g = pos.groupby("identifier").agg({"endbookmv": "sum", "curr_ntl": "sum"}) comb_g["date"] = pd.to_datetime(date) model = pd.merge(comb_g, model, left_on="identifier", right_on="cusip") positions = model.set_index(["cusip", "model_version"]).unstack(1).dropna() v1 = positions.xs(1, level="model_version", axis=1) v3 = positions.xs(3, level="model_version", axis=1) if dm is True: libor1m = USDLibor(Period(1, Months), yc) libor = libor1m.fixing( libor1m.fixing_calendar.adjust(Date.from_datetime(model_date)) ) v3 = v3.assign( b_yield=v3.moddur.apply(lambda x: float(yc.zero_rate(x))) - libor ) else: v3 = v3.assign(b_yield=v3.moddur.apply(lambda x: float(yc.zero_rate(x)))) v3.b_yield += np.minimum( (v1.pv * v1.curr_ntl / v1.endbookmv) ** (1 / v1.moddur) - 1, 1 ).dropna() v3.delta_yield *= v3.endbookmv / v3.pv v3.delta_ir *= ( np.minimum(1, 1 / v3.moddur) * (v3.endbookmv / v3.curr_ntl) / v3.pv * v3.curr_ntl ) return v3.reset_index().set_index("date") def crt_pos(date): pos = get_portfolio(date) pos = pos[pos["strat"].str.contains("CRT")] def clo_pos(date): date = date.date() if isinstance(date, pd.Timestamp) else date df = get_portfolio(date) df = df[ (df.port == "CLO") & (df.endbookmv > 0) & (df.custacctname == "V0NSCLMAMB") & (df["invid"].str.len() >= 9) ] df = df[["endbookmv", "endlocalmarketprice", "identifier"]] if df.empty is True: return df else: sql_string = ( "select distinct cusip, identifier from bonds where asset_class = 'CLO'" ) 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) ) 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 calc_df["date"] = date return calc_df.set_index("date")