1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
{
"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
}
|
