{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils.db import dbconn\n",
    "conn = dbconn('dawndb')\n",
    "import pandas as pd\n",
    "pd.options.display.float_format = \"{:,.2f}\".format\n",
    "df_rates = pd.read_sql_query(\"SELECT date, rate FROM rates where name='FED_FUND'\",\n",
    "                             conn,\n",
    "                             parse_dates=['date'],\n",
    "                             index_col=['date']).sort_index()\n",
    "df_balances = pd.read_sql_query(\"SELECT * FROM strategy_im\",\n",
    "                                conn,\n",
    "                                parse_dates=['date'],\n",
    "                                index_col=['date']).sort_index()\n",
    "df_balances[['broker', 'strategy']] = df_balances[['broker', 'strategy']].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display\n",
    "from pandas.tseries.offsets import BDay\n",
    "import numpy as np\n",
    "\n",
    "def f(df_balances, df_rates, broker, start_date, end_date):\n",
    "    df = (df_balances[df_balances.broker == broker].\n",
    "      set_index(\"strategy\", append=True)[\"amount\"].\n",
    "      unstack(\"strategy\"))\n",
    "    df[df.isnull()] = 0.\n",
    "    drange = pd.date_range(pd.Timestamp(start_date) - BDay(), end_date)\n",
    "    rates = df_rates.reindex(drange, method=\"ffill\") /100 /360\n",
    "    df = df.reindex(drange, method=\"ffill\")\n",
    "    if broker in [\"BAML_ISDA\", \"CITI\"]:\n",
    "        d = {}\n",
    "        for strat in df:\n",
    "            s = df.loc[start_date:, strat]\n",
    "            ir_bal = 0.\n",
    "            for bal, r in zip(s.values, rates.loc[start_date:, 'rate'].values):\n",
    "                bal += ir_bal\n",
    "                ir_bal += bal * r\n",
    "            d[strat] = ir_bal\n",
    "        result = pd.Series(d, name='amount')\n",
    "    else:\n",
    "        result = (df.loc[start_date:] * rates.loc[start_date:].values).sum().to_frame(name='amount')\n",
    "    display(result)\n",
    "    print(result.sum())\n",
    "    \n",
    "from functools import partial\n",
    "f_print = partial(f, df_balances, df_rates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a0d5bf2d610b480f918704e73e02309a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(Dropdown(description='Broker:', index=3, options=('BAML_FCM', 'BAML_ISDA', 'BNP'…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from ipywidgets import widgets, Layout\n",
    "import datetime\n",
    "broker_widget = widgets.Dropdown(\n",
    "    options=df_balances.broker.cat.categories,\n",
    "    value='CITI',\n",
    "    description='Broker:',\n",
    "    disabled=False,\n",
    ")\n",
    "start_date = widgets.DatePicker(\n",
    "    description='start:',\n",
    "    disabled=False,\n",
    "    value=datetime.date(2019, 12, 1)\n",
    ")\n",
    "end_date = widgets.DatePicker(\n",
    "    description='end:',\n",
    "    disabled=False,\n",
    "    value=datetime.date(2019, 12, 31)\n",
    ")\n",
    "output = widgets.interactive_output(f_print, {'broker': broker_widget, 'start_date': start_date, 'end_date': end_date})\n",
    "output.layout= Layout(margin='auto auto auto 90px')\n",
    "widgets.VBox([widgets.HBox([broker_widget, start_date, end_date]), output])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_balances[df_balances.broker=='GS'].loc[\"2019-06-10\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2019-12-02   -8,800,000.00\n",
       "2019-12-03   -8,800,000.00\n",
       "2019-12-04   -8,800,000.00\n",
       "2019-12-05   -8,800,000.00\n",
       "2019-12-06   -8,800,000.00\n",
       "2019-12-09   -8,800,000.00\n",
       "2019-12-10   -8,800,000.00\n",
       "2019-12-11   -9,070,000.00\n",
       "2019-12-12   -9,070,000.00\n",
       "2019-12-13   -9,070,000.00\n",
       "2019-12-16   -9,070,000.00\n",
       "2019-12-17   -9,070,000.00\n",
       "2019-12-18   -9,070,000.00\n",
       "2019-12-19   -9,070,000.00\n",
       "2019-12-20   -9,070,000.00\n",
       "2019-12-23   -9,070,000.00\n",
       "2019-12-26   -9,070,000.00\n",
       "2019-12-27   -9,070,000.00\n",
       "2019-12-30   -9,070,000.00\n",
       "2020-01-01   -9,070,000.00\n",
       "2020-01-02   -9,070,000.00\n",
       "2020-01-03   -9,070,000.00\n",
       "Name: amount, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gs=df_balances[df_balances.broker == \"MS\"]\n",
    "df_gs.groupby(df_gs.index)['amount'].sum()[\"2019-12-01\":]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def f(df_balances, df_rates, broker, start_date, end_date):\n",
    "    df = (df_balances[df_balances.broker == broker].\n",
    "      set_index(\"strategy\", append=True)[\"amount\"].\n",
    "      unstack(\"strategy\"))\n",
    "    df[df.isnull()] = 0.\n",
    "    drange = pd.date_range(pd.Timestamp(start_date) - BDay(), end_date)\n",
    "    rates = df_rates.reindex(drange, method=\"ffill\") /100 /360\n",
    "    df = df.reindex(drange, method=\"ffill\")\n",
    "    if broker in [\"BAML_ISDA\", \"CITI\"]:\n",
    "        d = {}\n",
    "        for strat in df:\n",
    "            s = df.loc[start_date:, strat]\n",
    "            ir_bal = 0.\n",
    "            for bal, r in zip(s.values, rates.loc[start_date:, 'rate'].values):\n",
    "                bal += ir_bal\n",
    "                ir_bal += bal * r\n",
    "            d[strat] = ir_bal\n",
    "        result = pd.Series(d, name='amount')\n",
    "    else:\n",
    "        result = df.loc[start_date:] * rates.loc[start_date:].values\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2019-12-01   -1,217.99\n",
       "2019-12-02   -1,217.99\n",
       "2019-12-03   -1,210.18\n",
       "2019-12-04   -1,210.18\n",
       "2019-12-05   -1,210.18\n",
       "2019-12-06   -1,440.10\n",
       "2019-12-07   -1,440.10\n",
       "2019-12-08   -1,440.10\n",
       "2019-12-09   -1,440.10\n",
       "2019-12-10   -1,440.10\n",
       "2019-12-11   -1,440.10\n",
       "2019-12-12   -1,474.54\n",
       "2019-12-13   -1,474.54\n",
       "2019-12-14   -1,474.54\n",
       "2019-12-15   -1,474.54\n",
       "2019-12-16   -1,484.06\n",
       "2019-12-17   -1,484.06\n",
       "2019-12-18   -1,474.54\n",
       "2019-12-19   -1,474.54\n",
       "2019-12-20   -1,474.54\n",
       "2019-12-21   -1,474.54\n",
       "2019-12-22   -1,474.54\n",
       "2019-12-23   -1,474.54\n",
       "2019-12-24   -1,474.54\n",
       "2019-12-25   -1,474.54\n",
       "2019-12-26   -1,474.54\n",
       "2019-12-27   -1,474.54\n",
       "2019-12-28   -1,474.54\n",
       "2019-12-29   -1,474.54\n",
       "2019-12-30   -1,474.54\n",
       "2019-12-31   -1,474.54\n",
       "Freq: D, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f(df_balances, df_rates, \"GS\", \"2019-12-01\", \"2019-12-31\").sum(axis=1)"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}