diff options
Diffstat (limited to 'python/notebooks/Allocation Reports.ipynb')
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 71 |
1 files changed, 65 insertions, 6 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index 1a30f348..b20f77d0 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -14,6 +14,9 @@ "import numpy as np\n", "\n", "from db import dbengine\n", + "from yieldcurve import YC\n", + "from quantlib.termstructures.yield_term_structure import YieldTermStructure\n", + "\n", "engine = dbengine('dawndb')\n", "Sengine = dbengine('serenitasdb')" ] @@ -53,8 +56,19 @@ "#Input latest NAVS to: '/home/serenitas/edwin/Python/subscription_fee_data.csv'\n", "pnl_alloc = m_pnl.groupby(['date', 'pnl']).sum()\n", "pnl_alloc = pnl_alloc.join(nav.begbooknav)\n", - "pnl_alloc['strat_return'] = pnl_alloc.mtdtotalbookpl / pnl_alloc.begbooknav\n", - "pnl_alloc_last_month = pnl_alloc.xs(report_date)" + "pnl_alloc['strat_return'] = pnl_alloc.mtdtotalbookpl / pnl_alloc.begbooknav" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#rolling 12 months PNL per strategy - copy to RiskMonitor\n", + "start_date = report_date - pd.tseries.offsets.MonthEnd(11)\n", + "rolling_return = pnl_alloc[start_date:report_date].groupby('pnl').sum()['strat_return']\n", + "rolling_return.to_clipboard()" ] }, { @@ -64,6 +78,7 @@ "outputs": [], "source": [ "#Plot this month's PNL\n", + "pnl_alloc_last_month = pnl_alloc.xs(report_date)\n", "ax = pnl_alloc_last_month['strat_return'].plot(kind='bar', figsize = (6,6), width = .35)\n", "ax.set_xlabel('Strategy')\n", "ax.set_ylabel('Return (%)')\n", @@ -154,6 +169,15 @@ "metadata": {}, "outputs": [], "source": [ + "turnover.to_clipboard(sep='\\t')" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ "#Number of bond positions by strategy by month\n", "df = go.get_portfolio()\n", "df = df[(df.custacctname == 'V0NSCLMAMB') &\n", @@ -265,6 +289,16 @@ "metadata": {}, "outputs": [], "source": [ + "a.to_clipboard()\n", + "#b.to_clipboard()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ "#RMBS Risk - need RMBS Positions and Risks\n", "sql_string = \"select date, duration, series from on_the_run where index = 'HY'\"\n", "duration = pd.read_sql_query(sql_string, Sengine, parse_dates=['date'], index_col=['date'])\n", @@ -288,10 +322,11 @@ " right_index=True,\n", " by='cusip')\n", "df_with_trades['hold'] = (df_with_trades.index - df_with_trades.trade_date2).dt.days/365\n", - "holding_period = df_with_trades.groupby('timestamp').apply(lambda df: sum(df.endbooknav * df.hold)/sum(df.endbooknav))\n", + "holding_period = df_with_trades.groupby('timestamp').apply(lambda df: sum(df.endbookmv * df.hold)/sum(df.endbookmv))\n", "ax = holding_period.plot(legend=False, title='Average Holding Period')\n", "ax.set_xlabel('date')\n", - "ax.set_ylabel('Years')" + "ax.set_ylabel('Years')\n", + "holding_period[-1]" ] }, { @@ -300,7 +335,31 @@ "metadata": {}, "outputs": [], "source": [ - "engine.dispose()" + "#Calculate Interests allocation \n", + "sql_string = \"select periodenddate, strat, sum(endqty) as bal, counterparty \" \\\n", + " \"from valuation_reports where invid = 'USDLOAN' and \" \\\n", + " \"extract(month from periodenddate) = %s and \" \\\n", + " \"extract(year from periodenddate) = %s \" \\\n", + " \"group by periodenddate, strat, counterparty \" \\\n", + " \"order by periodenddate desc\"\n", + "df = pd.read_sql_query(sql_string, dbengine('dawndb'), \n", + " parse_dates=['periodenddate'],\n", + " index_col=['strat', 'counterparty'],\n", + " params=[report_date.month, report_date.year])\n", + "df['day_frac'] = -(df.groupby(level=['strat','counterparty'])['periodenddate'].transform(lambda s:\n", + " s.diff().astype('timedelta64[D]') / 360)).astype(float)\n", + "df = df.fillna(0)\n", + "r = {}\n", + "yc = YieldTermStructure()\n", + "for t in df['periodenddate'].unique():\n", + " yc.link_to(YC(evaluation_date=pd.Timestamp(t)))\n", + " r[pd.Timestamp(t)] = (float(yc.zero_rate(.083333)))\n", + "rates = pd.DataFrame.from_dict(r, orient='index')\n", + "df = df.reset_index().set_index('periodenddate', drop=False).join(rates)\n", + "df = df.rename(columns={0: 'rate'})\n", + "df = df.set_index(['strat','counterparty'], append=True)\n", + "df['interest'] = df['rate'] * df['day_frac'] * df['bal']\n", + "interests = df['interest'].groupby(level=['counterparty','strat']).sum()" ] }, { @@ -327,7 +386,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.6.6" + "version": "3.7.1" } }, "nbformat": 4, |