diff options
Diffstat (limited to 'python/notebooks/Valuation Backtest.ipynb')
| -rw-r--r-- | python/notebooks/Valuation Backtest.ipynb | 60 |
1 files changed, 41 insertions, 19 deletions
diff --git a/python/notebooks/Valuation Backtest.ipynb b/python/notebooks/Valuation Backtest.ipynb index 5e64c8e0..f7a66681 100644 --- a/python/notebooks/Valuation Backtest.ipynb +++ b/python/notebooks/Valuation Backtest.ipynb @@ -6,7 +6,7 @@ "metadata": {}, "outputs": [], "source": [ - "from utils.db import dbconn, dbengine, serenitas_engine, dawn_engine\n", + "from serenitas.utils.db import dbconn, dbengine, serenitas_engine, dawn_engine\n", "\n", "import datetime\n", "import mark_backtest_underpar as mark\n", @@ -25,9 +25,8 @@ "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)" + "df = mark.get_mark_df('Subprime')\n", + "df = df.append(mark.get_mark_df('CLO'))" ] }, { @@ -150,14 +149,14 @@ "source": [ "#%matplotlib nbagg\n", "#Filtered takes out PB\n", - "to_plot = ['mark_closest_all', 'mark_filtered_mean']\n", - "to_plot1 = ['mark_manager']\n", + "to_plot = ['closest_vendor', 'mean_vendor']\n", + "to_plot1 = ['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", + "plot_df0 = plot_df0.rename(columns = {'closest_vendor': 'Third-pary mark closest to LMCG valuation', \\\n", + " 'mean_vendor': 'Average of all third-party marks'})\n", + "plot_df1 = plot_df1.rename(columns = {'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", @@ -177,7 +176,7 @@ "outputs": [], "source": [ "#Fund cumulative returns from the last 12 months\n", - "results[1]['mark_manager'][-12:]" + "results[1]['manager'][-12:]" ] }, { @@ -190,6 +189,7 @@ "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", + "perf_ann.to_clipboard()\n", "round(perf_ann*100,2)" ] }, @@ -200,7 +200,7 @@ "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']]" + "(results[1]/results[1].shift(1) - 1)[-24:][['manager', 'closest_vendor', 'mean_vendor']]" ] }, { @@ -222,11 +222,11 @@ "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", + "import matplotlib.ticker as mtick\n", + "nav_impact = alt_nav_impact.divide(alt_nav_impact.endbooknav/100, axis=0)\n", + "ax = nav_impact[['closest_vendor', 'mean_vendor']].plot(figsize = [5, 7])\n", + "lgd = ax.legend(loc='upper center', bbox_to_anchor=(0.5, -.1), shadow=True, ncol=3)\n", + "ax.yaxis.set_major_formatter(mtick.PercentFormatter())\n", "ax.figure.savefig(\"/home/serenitas/edwin/PythonGraphs/Valuation_3.png\", bbox_extra_artists=(lgd,), bbox_inches='tight')" ] }, @@ -279,7 +279,7 @@ "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", + "all_trades = pd.read_sql_query(\"SELECT * FROM bond_trades 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", @@ -350,7 +350,29 @@ "execution_count": null, "metadata": {}, "outputs": [], - "source": [] + "source": [ + "#Look at accuracy of CLO marks\n", + "sql_string = \"select date, a.identifier, mark, source from external_marks a \" \\\n", + " \"left join bonds b on a.identifier = b.identifier where asset_class = 'CLO'\"\n", + "clo_marks = pd.read_sql_query(sql_string, dawn_engine,\n", + " parse_dates='date',\n", + " index_col = 'date')\n", + "clo_marks = clo_marks.reset_index().merge(clo_marks.groupby(['date', 'identifier']).mean(), on=['identifier', 'date'])\n", + "\n", + "#filter out crap marks and change labels for brokers, and regroup them \n", + "clo_marks = clo_marks[clo_marks['source'] != 'VENDOR&ORBROKER_AVG']\n", + "clo_marks.loc[clo_marks['source'].str.contains('BROKER'), 'source'] = 'BROKER'\n", + "clo_marks = clo_marks.groupby(['date', 'identifier', 'source']).mean()\n", + "\n", + "clo_marks = clo_marks.loc['2019-01-01':,:,:]\n", + "clo_marks = clo_marks.rename(columns={'mark_y': 'avg_cusip_mark'})\n", + "clo_marks['diff'] = (clo_marks['mark_x'] - clo_marks['avg_cusip_mark'])/clo_marks['mark_x']\n", + "\n", + "g = clo_marks.groupby('source')['diff']\n", + "print('sample size', g.count())\n", + "print('mean diff', g.mean())\n", + "print('std_dev of diff from mean', g.std()/np.sqrt(g.count()))\n" + ] } ], "metadata": { @@ -369,7 +391,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.8.5" + "version": "3.9.1-final" } }, "nbformat": 4, |