{
 "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\",\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",
    "\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\").values /100 /360\n",
    "    df = df.reindex(drange, method=\"ffill\") * rates\n",
    "    df = df.loc[start_date:]\n",
    "    display(df.sum().to_frame(name='amount'))\n",
    "    print(df.sum().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(2019, 6, 1)\n",
    ")\n",
    "end_date = widgets.DatePicker(\n",
    "    description='end:',\n",
    "    disabled=False,\n",
    "    value=datetime.date(2019, 6, 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": [
    "f(df_balances, df_rates, \"MS\", \"2019-06-01\", \"2019-06-30\").sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_balances[df_balances.broker=='MS'].loc[\"2019-06-10\"]"
   ]
  },
  {
   "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": 4
}