{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from db import dbengine\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",
    "\n",
    "engine = dbengine('dawndb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = datetime.date.today() - pd.tseries.offsets.MonthEnd(1)"
   ]
  },
  {
   "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', '2018-12-01', 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": [
    "#Now Calculate alternate valuation methodologies\n",
    "df = mark.get_mark_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mark.count_sources(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#difference by source\n",
    "nav = ops.get_net_navs()['endbooknav']\n",
    "difference = mark.diff_by_source_percentage(df)\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()"
   ]
  },
  {
   "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",
    "round(mark.annual_performance(results[1])*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",
    "df = mark.alt_nav_impact()\n",
    "round(pd.DataFrame(df.iloc[-1]/df.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 = df.divide(df.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",
    "nav_impact.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Portfolio MTM Gains/Loss/Net\n",
    "df_pnl = ops.get_monthly_pnl()[:date][['mtdbookunrealmtm', 'mtdbookrealmtm']].sum(axis=1)\n",
    "df_pnl.name = 'mtm'\n",
    "r=[]\n",
    "for d, g in df_pnl.reset_index('identifier').groupby(pd.Grouper(freq='M')):\n",
    "    sql_string = \"SELECT * FROM risk_positions(%s, 'Subprime') WHERE notional > 0\"\n",
    "    pos = pd.read_sql_query(sql_string, engine, params=[g.index[-1].date()])\n",
    "    pos.identifier = pos.identifier.str[:9]\n",
    "    pos = pos.join(df_pnl.groupby('identifier').cumsum().loc[g.index[-1]],\n",
    "                   on='identifier')['mtm'] / nav.loc[d]\n",
    "    r.append([g.index[-1], pos[pos>=0].sum(), pos[pos<0].sum()])\n",
    "summary = pd.DataFrame.from_records(r, index='date', columns=['date','gains','loss'])\n",
    "summary['Net'] = summary.gains + summary.loss\n",
    "summary.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary.iloc[-1]"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}