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
|
{
"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": [
"drange = pd.date_range(\"2019-05-01\", \"2019-05-31\")\n",
"df_balances = (df_balances.\n",
" groupby([\"broker\", \"strategy\"], group_keys=False).\n",
" apply(lambda df: df.reindex(drange, method=\"ffill\")).\n",
" dropna())\n",
"df_balances.index.name='date'\n",
"df_balances.join(df_rates)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from IPython.display import display\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(start_date, end_date)\n",
" rates = df_rates.reindex(drange, method=\"ffill\").values /100 /360\n",
" df = df.reindex(drange, method=\"ffill\") * rates\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='GS',\n",
" description='Broker:',\n",
" disabled=False,\n",
")\n",
"start_date = widgets.DatePicker(\n",
" description='start:',\n",
" disabled=False,\n",
" value=datetime.date(2019, 5, 1)\n",
")\n",
"end_date = widgets.DatePicker(\n",
" description='end:',\n",
" disabled=False,\n",
" value=datetime.date(2019, 5, 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": []
}
],
"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": 2
}
|
