{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from utils.db import dbconn, dbengine, serenitas_engine, dawn_engine\n", "\n", "import datetime\n", "import mark_backtest_underpar as mark\n", "import globeop_reports as ops\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", "import numpy as np\n", "import math" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "date = datetime.date.today() - pd.tseries.offsets.MonthEnd(1)\n", "navs = ops.get_net_navs()\n", "subprime = mark.get_mark_df('Subprime')\n", "clo = mark.get_mark_df('CLO')\n", "df = subprime.append(clo)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#exclude sell price that are over 200\n", "df_long = mark.back_test('2013-01-01', date, sell_price_threshold = 200)\n", "df_long = df_long[df_long.source != 'PB']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#%matplotlib nbagg\n", "%matplotlib inline\n", "mark.pretty_plot(df_long)\n", "#file saved in serenitas shared drive/edwin/" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#exclude trades that are over 5x mark for purpose of regression\n", "diff_threshold = 5\n", "results = mark.stats(df_long, diff_threshold)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Regression Intercept\n", "round(results[0],1)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Sale Difference\n", "round(results[1],3)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#source counts\n", "g2 = df.set_index(\"source\", append=True).groupby(level=[\"date\", \"source\"])\n", "g2[\"mark\"].count().unstack(-1).plot()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#difference by source\n", "difference = mark.diff_by_source(df)\n", "difference = difference.join(navs['endbooknav'])\n", "difference = difference.apply(lambda x: (x / x.endbooknav), axis=1)\n", "del difference[\"endbooknav\"]\n", "#difference.to_clipboard()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#plot\n", "ax = difference.plot(kind = 'bar', legend = True, figsize = [10, 3.5])\n", "\n", "visible = ax.xaxis.get_ticklabels()[::6]\n", "for label in ax.xaxis.get_ticklabels():\n", " if label not in visible:\n", " label.set_visible(False)\n", " \n", "ax.xaxis.set_major_formatter(plt.FixedFormatter(difference.index.to_series().dt.strftime(\"%b %Y\")))\n", "ax.set_ylabel('NAV Impact vs. Fund Policy (%)')\n", "vals = ax.get_yticks()\n", "ax.set_yticklabels(['{:3.0f}%'.format(x*100) for x in vals])\n", "ax.set_xlabel('')\n", "ax.grid(False, which='major',axis = 'x')\n", "lgd = ax.legend(loc='upper center', bbox_to_anchor=(0.5, -.3), shadow=True, ncol=5)\n", "ax.figure.savefig(\"/home/serenitas/edwin/PythonGraphs/Valuation_2.png\", bbox_extra_artists=(lgd,), bbox_inches='tight')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "results = mark.alt_navs(df)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#%matplotlib nbagg\n", "#Filtered takes out PB\n", "to_plot = ['mark_closest_all', 'mark_filtered_mean']\n", "to_plot1 = ['mark_manager']\n", "plot_df0 = results[1][to_plot]\n", "plot_df1 = results[1][to_plot1]\n", "\n", "plot_df0 = plot_df0.rename(columns = {'mark_closest_all': 'Third-pary mark closest to LMCG valuation', \\\n", " 'mark_filtered_mean': 'Average of all third-party marks'})\n", "plot_df1 = plot_df1.rename(columns = {'mark_manager': 'Marks per fund valuation policy'})\n", "\n", "ax = plot_df0.plot(figsize = [10, 3.5])\n", "ax = plot_df1.plot(marker = 'o', ax = ax)\n", "plt.rcParams[\"font.family\"] = \"sans-serif\"\n", "ax.set_xlabel('')\n", "ax.set_ylabel('NAV', weight = 'bold')\n", "ax.set_title('Fund Return Using Different Valuation Methods', weight = 'bold')\n", "ax.xaxis.grid(False)\n", "lgd = ax.legend(loc='upper center', bbox_to_anchor=(0.5, -.1), shadow=True, ncol=3)\n", "ax.figure.savefig(\"/home/serenitas/edwin/PythonGraphs/Valuation_1.png\", bbox_extra_artists=(lgd,), bbox_inches='tight')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Fund cumulative returns from the last 12 months\n", "results[1]['mark_manager'][-12:]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Annual Return using different methodology\n", "perf = results[1].groupby(pd.Grouper(freq=\"A\")).last()\n", "perf_ann = perf / perf.shift(1) - 1\n", "perf_ann[\"2013\"] = perf[\"2013\"] / 100 - 1\n", "round(perf_ann*100,2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Return using different methodology - Same calulation as above but monthly \n", "(results[1]/results[1].shift(1) - 1)[-24:][['mark_manager', 'mark_closest_all', 'mark_filtered_mean']]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#A positive impact % means the alternative methodology results in a higher NAV than the fund's valuation policy.\n", "alt_nav_impact = mark.calc_mark_diff(df)\n", "alt_nav_impact = alt_nav_impact.join(navs.endbooknav)\n", "round(pd.DataFrame(alt_nav_impact.iloc[-1]/alt_nav_impact.iloc[-1]['endbooknav'])*100,2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#% impact historical: positive % means the alternative methodology results in a higher NAV\n", "nav_impact = alt_nav_impact.divide(alt_nav_impact.endbooknav, axis=0)\n", "to_plot = ['mark_closest_all', 'mark_filtered_mean']\n", "nav_impact = nav_impact[to_plot].rename(columns={'mark_closest_all': 'mark to closest', \n", " 'mark_filtered_mean': 'mark to mean'})\n", "ax = nav_impact.plot()\n", "ax.figure.savefig(\"/home/serenitas/edwin/PythonGraphs/Valuation_3.png\", bbox_extra_artists=(lgd,), bbox_inches='tight')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Portfolio MTM Gains/Loss/Net each month\n", "df_pnl = ops.get_monthly_pnl()\n", "df_pnl_temp = df_pnl[:date][['mtdbookunrealmtm', 'mtdbookrealmtm']].sum(axis=1)\n", "df_pnl_temp.name = 'mtm'\n", "r={}\n", "for d, g in df_pnl_temp.reset_index('identifier').groupby(pd.Grouper(freq='M')):\n", " p = []\n", " for a in ['Subprime', 'CLO', 'CSO', 'CRT']:\n", " sql_string = \"SELECT * FROM risk_positions(%s, %s) WHERE notional > 0\"\n", " p.append(pd.read_sql_query(sql_string, dawn_engine, params=[d.date(), a], index_col = ['identifier']))\n", " r[d]= pd.concat(p).merge(g.groupby('identifier').sum(), left_index=True, right_index=True)\n", "pos = pd.concat(r, names=['date','identifier'])\n", "pos = pos.groupby('date').sum().merge(navs, left_index=True, right_index=True)\n", "pos['mtm_ret'] = pos['mtm']/pos['endbooknav']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#MTM in each portfolio\n", "df_pnl.groupby(['identifier']).cumsum()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "################################## How much unrealized gains in bonds?\n", "#1) sum up all the (paid-down adjusted proceeds) of all buy trades up to the point of where current face = sum of all traded notionals (FIFO)\n", "#2) (paid down adjusted proceeds) of each trade = principal_paid - paydown since purchase of that bond to the date\n", "#3) cost basis = sum of all (paid down adjusted proceeds)\n", "#4) Doesn't work with CSOs - their cashflows are not correctly recorded in bloomberg\n", "fund = 'SERCGMAST'\n", "globeop_val_rep = ops.get_portfolio()\n", "navs = ops.get_net_navs()\n", "all_cf = pd.read_sql_query(\"SELECT * FROM cashflow_history\", dawn_engine,\n", " parse_dates=['date'],\n", " index_col=['date']).sort_index()\n", "all_trades = pd.read_sql_query(\"SELECT * FROM bonds where fund = %s order by trade_date desc\", dawn_engine,\n", " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", " index_col = 'trade_date', params=[fund])\n", "all_trades['curr_notional'] = all_trades['principal_payment']/all_trades['price'] *100\n", "\n", "bonds = {}\n", "monthend_val_rep = globeop_val_rep.groupby(pd.Grouper(freq=\"M\"), group_keys=False).apply(lambda df: df.loc[df.index[-1]])\n", "for position_date, go_val in monthend_val_rep.groupby('periodenddate'):\n", " for a in [\"Subprime\", \"CRT\", \"CLO\"]:\n", " bonds[position_date, a] = pd.read_sql_query(\"select * from risk_positions(%s, %s, %s)\", dawn_engine,\n", " params=[position_date.date(), a, fund], index_col = 'identifier').sort_index()\n", "\n", "bonds = pd.concat(bonds, names=['periodenddate','asset_class', 'identifier'])\n", "bonds['curr_notional'] = bonds['notional'] * bonds['factor']\n", "monthend_val_rep = monthend_val_rep.groupby(['periodenddate', 'identifier'])['endbookmv'].sum()\n", "bonds = bonds.reset_index().merge(monthend_val_rep, \n", " left_on=['periodenddate','identifier'], \n", " right_on=['periodenddate','identifier'],\n", " validate='1:1',\n", " how='left')\n", "\n", "#can also use endbookmv in field to use official globeop values, but having a Repo looks like a loss \n", "def gains_calc(pos, trades, cf, field='usd_market_value'):\n", " bond_trade = trades[(trades.identifier == pos.identifier) & (trades.buysell==True)]\n", " cost_basis = 0\n", " for i, bt in bond_trade.iterrows():\n", " bond_cf_hist = cf[cf.identifier==pos.identifier]\n", " bond_cf_hist['beg_principal_bal'] = bond_cf_hist['principal_bal'].shift(1)\n", " bond_cf_hist = bond_cf_hist[bt.settle_date:pos.periodenddate]\n", " paydown = 0\n", " if ~bond_cf_hist.empty:\n", " bt.orig_curr_notional = bt.curr_notional\n", " for i, bond_cf in bond_cf_hist.iterrows():\n", " paydown = bt.curr_notional/bond_cf.beg_principal_bal * bond_cf.principal if bond_cf.beg_principal_bal > 0 else 0\n", " bt.curr_notional -= paydown\n", " bt.curr_notional = bt.orig_curr_notional\n", " if len(pos.identifier) == 11:\n", " ratio = np.max([0,np.min([1.0, pos.notional/bt.faceamount])])\n", " pos.notional -= bt.faceamount\n", " else:\n", " ratio = np.max([0,np.min([1.0, pos.curr_notional/bt.curr_notional])]) if bt.curr_notional > 0 else 0\n", " pos.curr_notional -= bt.curr_notional\n", " adj_principal_payment = np.max([0,bt.principal_payment - paydown])\n", " cost_basis += adj_principal_payment * ratio\n", " return pos[field] - cost_basis\n", " \n", "bonds['gains'] = bonds.apply(lambda position: gains_calc(position, all_trades, all_cf), axis=1)\n", "navs = navs.merge(bonds.groupby('periodenddate')['gains'].sum(), left_index=True, right_index=True)\n", "navs['mtm_in_portf'] = navs['gains']/navs['begbooknav']\n", "navs['mtm_in_portf'].plot()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#Trade winners/performance\n", "df = ops.trade_performance()\n", "df = df.set_index('trade_date')\n", "df.days_held = df.days_held.dt.days\n", "winner = df[df.percent_gain > 0]\n", "df[df.days_held.notnull()].groupby(pd.Grouper(freq='A')).mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.5" } }, "nbformat": 4, "nbformat_minor": 4 }