path: root/python/notebooks/Valuation Backtest.ipynb

{
 "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
}