{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "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 WHERE fund='SERCGMAST'\",\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": null,
   "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": null,
   "metadata": {},
   "outputs": [],
   "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(2020, 9, 1)\n",
    ")\n",
    "end_date = widgets.DatePicker(\n",
    "    description='end:',\n",
    "    disabled=False,\n",
    "    value=datetime.date(2020, 9, 30)\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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "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": null,
   "metadata": {},
   "outputs": [],
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}