Spaces:
Sleeping
Sleeping
File size: 19,656 Bytes
976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 6c25b38 29f1ee3 cd1bf03 588011f 976166f bc13daa 976166f 29f1ee3 976166f bc13daa 29f1ee3 bc13daa 29f1ee3 bc13daa 976166f 29f1ee3 976166f 6c25b38 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 bc13daa 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 576c56b 976166f 576c56b 976166f 29f1ee3 bc13daa 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 bc13daa 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f bc13daa 976166f 687ad2b 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 976166f 29f1ee3 cd1bf03 976166f |
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 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 |
# %%
# load portfolio
import time
import panel as pn
from utils import create_stocks_entry_from_excel, style_number, create_share_changes_report
import datetime as dt
from bokeh.models.widgets.tables import CheckboxEditor, NumberEditor, SelectEditor
from utils import time_in_beijing
import api
import pandas as pd
from sqlalchemy import create_engine
import table_schema
import db_operation as db
import pipeline
from sidebar import SideNavBar
db_url = 'sqlite:///instance/local.db'
pn.extension()
pn.extension('tabulator')
pn.extension('plotly')
pn.extension('floatpanel')
pn.extension(notifications=True)
# %%
# the width of iphone se
MIN_COMPONENT_WIDTH = 375
MAX_COMPONENT_WIDTH = 600
def create_portfolio_stream_entry(stocks, portfolio_df):
# create entry with ticker, date and details
stream_entry = pd.DataFrame(stocks)
# if have duplicate ticker raise error
if stream_entry.ticker.duplicated().any():
raise Exception('VALIDATION_ERROR: plase remove duplicate ticker')
# raise error if portfolio already have same ticker with same date
date = stream_entry.date[0]
selected_df = portfolio_df[portfolio_df.date == date]
tickers = stream_entry.ticker.tolist()
filter_out_ticker = selected_df[selected_df.ticker.isin(tickers)].ticker
if len(filter_out_ticker) > 0:
raise Exception(f'VALIDATION_ERROR: {" ".join(filter_out_ticker)}在{date}已存在,请先删除再添加')
stream_entry = pipeline.add_details_to_stock_df(stream_entry)
# calculate share changes, use tmp_df to save intermediate result
tmp_df = pd.concat([stream_entry, portfolio_df], ignore_index=True, join='outer')
tmp_df.sort_values(by='date', inplace=True)
tmp_df['share_changes'] = tmp_df.groupby('ticker').shares.diff()
# for ticker previous not existing use shares as share_changes
tmp_df.share_changes = tmp_df.share_changes.fillna(tmp_df.shares)
# add share_chagnes back to stream_entry
stream_entry = stream_entry.merge(
tmp_df[['ticker','date','share_changes','change_saved']],
on=['ticker','date'],
how='left'
)
# indicate not saved
stream_entry['change_saved'] = False
# indicate sync to db
stream_entry['sync_to_db'] = True
# fill empty ave_price with latest closing price
# TODO: for now all ave_price is fetching from api
ticker = stream_entry.ticker.tolist()
close_price = api.fetch_stocks_price(security=ticker, end_date=date, frequency='minute', count=1)[['ticker','close']]
close_price.rename(columns={'close':'ave_price'}, inplace=True)
stream_entry = stream_entry.merge(close_price, on='ticker', how='left')
# calculate cash(mkt_value) and weight
stream_entry['cash'] = stream_entry.shares * stream_entry.ave_price
stream_entry['weight'] = stream_entry.cash / stream_entry.cash.sum()
return stream_entry
def notify(func):
def wrapper(*args, **kwargs):
try:
notifications = func(*args, **kwargs)
if notifications is not None:
for notification in notifications:
duration = notification.get('duration', 4000)
if notification['type'] == 'success':
pn.state.notifications.success(
notification['description'], duration=duration)
elif notification['type'] == 'error':
pn.state.notifications.error(
notification['description'], duration=duration)
elif notification['type'] == 'warning':
pn.state.notifications.warning(
notification['description'], duration=duration)
elif notification['type'] == 'info':
pn.state.notifications.info(
notification['description'], duration=duration)
else:
raise Exception('unknow notification type')
except Exception as e:
pn.state.notifications.error(str(e), duration=0)
return wrapper
def app():
# load portfolio df
p_profile = db.get_all_portfolio_profile()
p_profile.sort_values(by=['date'], inplace=True)
# change in shares for same ticker
p_profile['share_changes'] = p_profile.groupby(['ticker'])[
'shares'].diff()
p_profile['share_changes'] = p_profile['share_changes'].fillna(
p_profile['shares'])
# indicate if change is saved
p_profile['change_saved'] = True
p_profile['sync_to_db'] = True
# get all stocks ticker for auto fill
stock_details = db.get_all_stocks()
all_tickers = stock_details.ticker.to_list()
# get most recent portfolio for auto generate entry
most_recent_portfolio = None
if len(p_profile) == 0:
most_recent_portfolio = p_profile
else:
most_recent_portfolio = p_profile[p_profile.date == max(
p_profile.date)]
# create portfolio table tabulator
hidden_column = ['index', 'sector', 'name']
col_to_titles = {'ticker': '证劵代码', 'weight': '权重',
'date': '时间', 'aggregate_sector': '分类',
'display_name': '名称',
'shares': '持仓', 'change_saved': '已同步',
'sync_to_db': '存入', 'share_changes': '持仓变化',
'cash': '现金', 'ave_price': '平均成本',
}
# styling
tabulator_formatters = {
# 'float': {'type': 'progress', 'max': 10},
'sync_to_db': {'type': 'tickCross'},
'change_saved': {'type': 'tickCross'},
}
bokeh_editors = {
'ticker': SelectEditor(options=all_tickers),
'shares': NumberEditor(),
}
# frozen_columns = ['date','ticker','display_name','shares','sync_to_db','change_saved']
portfolio_tabulator = pn.widgets.Tabulator(p_profile,
layout='fit_columns',
height_policy='max',
# width=1000,
groupby=['date'],
hidden_columns=hidden_column, titles=col_to_titles,
formatters=tabulator_formatters,
editors=bokeh_editors,
pagination='remote',
# page_size=25,
# frozen_columns=frozen_columns
)
portfolio_tabulator.style.apply(style_number, subset=['share_changes'])
# history tabulator
history_dt = p_profile[['date', 'sync_to_db', 'change_saved']].copy()
history_dt = history_dt.groupby('date').agg({
"sync_to_db": lambda x: all(x),
'change_saved': lambda x: all(x),
})
history_dt['date'] = history_dt.index
history_dt.reset_index(drop=True, inplace=True)
history_tabulator = pn.widgets.Tabulator(history_dt,
formatters=tabulator_formatters,
buttons={'detail': "<i>📋</i>"},
hidden_columns=hidden_column,
height_policy='max',
titles=col_to_titles)
# perform calculation btn
force_recalculate_btn = pn.widgets.Button(
name='重新计算', button_type='primary', sizing_mode='stretch_width')
# create component
new_stock_btn = pn.widgets.Button(
name='增加新股票', button_type='primary', sizing_mode='stretch_width')
preview_btn = pn.widgets.Button(
name='预览', button_type='primary', sizing_mode='stretch_width')
file_input = pn.widgets.FileInput(
accept='.xlsx', sizing_mode='stretch_width')
# strip timezone info
datetime_picker = pn.widgets.DatetimePicker(name='Datetime Picker',
value=time_in_beijing().replace(tzinfo=None),
sizing_mode='stretch_width')
upload_to_db_btn = pn.widgets.Button(
name='保存到数据库', button_type='warning', sizing_mode='stretch_width')
# emtpy stock_column to display new entires
stock_column = pn.Column(
width_policy='max', height_policy='max', scroll=True)
# floating window row
floating_windows = pn.Row()
@notify
def _update_history_tabulator(action, df=None):
'''handle update history tabulator'''
# handle add new entires to view
if action == 'append' and df is not None:
index = history_tabulator.value[history_tabulator.value.date ==
df.date[0]].index.to_list()
if len(index) == 0:
# drop duplicate date in df
df = df.drop_duplicates(subset='date', keep='first')
# if not in history tabulator add new entry
selected_df = df[['date', 'sync_to_db', 'change_saved']]
# if stream to empty tabulator, index will be mismatched
if (len(history_tabulator.value) == 0):
history_tabulator.value = selected_df
else:
history_tabulator.stream(
df[['date', 'sync_to_db', 'change_saved']], follow=True)
else:
# if in history tabulator patch change_saved to false
history_tabulator.patch({
'change_saved': [(index[0], False)]
}, as_index=True)
yield {'type': 'warning', 'description': '添加成功请保存'}
# hanlde editing portoflio tabulator
elif action == 'edit':
# mark synced_to_db to false when entry is edited
date = df
index = history_tabulator.value[history_tabulator.value.date == date].index.to_list(
)
# check if all change saved
all_saved = all(
portfolio_tabulator.value[portfolio_tabulator.value.date == date]['change_saved'])
history_tabulator.patch({
'change_saved': [(index[0], all_saved)]
}, as_index=True)
yield {'type': 'warning', 'description': '修改成功请保存'}
# handle sync to db
elif action == 'sync':
# patch all synced_to_db to true
indices = history_tabulator.value[
~history_tabulator.value['change_saved']].index.to_list()
# add an offset to address the issue when df is empty index start from 1
history_tabulator.patch({
'change_saved': [(index, True) for index in indices]
}, as_index=True)
yield {'type': 'success', 'description': '同步成功以更新'}
@notify
def delete_stock(row):
'''delete a stock entry'''
stock_column.remove(row)
yield {'type': 'success', 'description': '删除成功'}
def create_new_stock_entry(ticker=None, shares=0, ave_price=0.0, disable_ticker=True):
'''create a new new stock entry'''
delete_btn = pn.widgets.Button(
name='❌', width=50, height=60, sizing_mode='fixed')
ticker_selector = pn.widgets.AutocompleteInput(
value=ticker,
name='证劵代码',
sizing_mode='stretch_width',
options=all_tickers,
placeholder='input ticker',
)
share_input = pn.widgets.IntInput(
name='持仓',
value=shares,
step=1,
start=0,
sizing_mode='stretch_width')
row = pn.Row(
delete_btn,
ticker_selector,
share_input,
width_policy='max',
)
delete_btn.on_click(lambda _, row=row: delete_stock(row))
return row
def update_stock_column(xlsx_file=None):
stock_entries = []
if xlsx_file is None:
for ticker, shares in most_recent_portfolio[['ticker', 'shares']].values:
stock_entries.append(create_new_stock_entry(
ticker=ticker, shares=shares))
# create from xlsx_file
else:
stocks_list = create_stocks_entry_from_excel(xlsx_file)
for entry in stocks_list:
stock_entries.append(create_new_stock_entry(
ave_price=entry['mean_price'],
ticker=entry['ticker'],
shares=entry['shares']))
# modify time
datetime_picker.value = stocks_list[0]['date']
file_input.value = None
# update
stock_column.clear()
stock_column.extend(stock_entries)
@notify
def update_profile_tabulator(e):
'''add all stocks entry to ui'''
# TODO: make this idempotent
new_entries = [dict(ticker=row[1].value,
shares=row[2].value,
date=datetime_picker.value) for row in stock_column]
try:
new_profile = create_portfolio_stream_entry(new_entries, portfolio_tabulator.value)
# update history tabulator
_update_history_tabulator('append', new_profile)
_stream_to_portfolio_tabulator(new_profile)
yield {'type': 'success', 'description': f'已添加{len(new_entries)}条新股票,请保存'}
except Exception as e:
raise Exception(e)
def add_new_stock(e):
row = create_new_stock_entry()
stock_column.append(row)
@notify
def _stream_to_portfolio_tabulator(entry):
# not using stream because it will cause index mismatch
if len(portfolio_tabulator.value) == 0:
portfolio_tabulator.value = entry
else:
portfolio_tabulator.stream(entry, follow=True)
yield {'type': 'success', 'description': f'添加{len(entry)}条股票'}
def handle_click_on_history_tabulator(e):
'''handle click click on history tabulator'''
if e.column == 'detail':
row_index = e.row
date = history_tabulator.value.iloc[row_index]['date']
date_str = date.strftime("%Y-%m-%d : %H:%M:%S")
record_df = portfolio_tabulator.value[portfolio_tabulator.value.date == date]
floatpanel = pn.layout.FloatPanel(create_share_changes_report(
record_df), name=date_str, margin=20, position='right-top')
floating_windows.append(floatpanel)
@notify
def handle_sync_to_db(e):
# TODO: change to use profile df instead, because tabulator might not contain all entry (currently have no problem)
'''sync selected entry to db'''
new_portfolio = portfolio_tabulator.value
# only update selected row to db
selected_portfolio = new_portfolio[new_portfolio['sync_to_db']]
try:
pipeline.update_portfolio_profile_to_db(selected_portfolio)
except Exception as e:
raise Exception(f'同步到数据库失败,错误信息:{e}')
# update history tabulator and portfolio tabulator
# mark changes as saved
indices = selected_portfolio[~selected_portfolio['change_saved']].index.to_list(
)
portfolio_tabulator.patch({
'change_saved': [(index, True) for index in indices]
}, as_index=True)
_update_history_tabulator('sync')
yield {'type': 'success', 'description': '保存成功'}
def handle_edit_portfolio_tabulator(e):
date = portfolio_tabulator.value.iloc[e.row]['date']
_update_history_tabulator(df=date, action='edit')
def hanlde_edit_history_tabulator(e):
# toggle sync on all entry on a date
if e.column == 'sync_to_db':
date = history_tabulator.value.iloc[e.row]['date']
# index of all entry on portfolio tabulator
indices = portfolio_tabulator.value[portfolio_tabulator.value.date.between(
date.replace(microsecond=0), date.replace(microsecond=999999))].index.to_list()
# patch all indices on sync_to_db to e.value
portfolio_tabulator.patch({
'sync_to_db': [(index, e.value) for index in indices]
}, as_index=True)
@notify
def handle_force_recalculation(e):
try:
yield {'type': 'info', 'description': "开始重新计算可能会花费1分钟以上", 'duration': 0}
# fill missing benchmark profile
yield {'type': 'info', 'description': "正在获取benchmark数据", 'duration': 0}
pipeline.left_fill_benchmark_profile()
# fill missing stock price
yield {'type': 'info', 'description': "正在更新股票数据", 'duration': 0}
pipeline.left_fill_stocks_price()
# recalculate
yield {'type': 'info', 'description': "正在重新计算权重", 'duration': 0}
pipeline.batch_processing()
yield {'type': 'info', 'description': '完成✅', 'duration': 0}
except Exception as e:
raise Exception(f'重新计算失败,错误信息:{e}')
# register event handler
upload_to_db_btn.on_click(handle_sync_to_db)
preview_btn.on_click(update_profile_tabulator)
new_stock_btn.on_click(add_new_stock)
history_tabulator.on_click(
handle_click_on_history_tabulator
)
force_recalculate_btn.on_click(handle_force_recalculation)
portfolio_tabulator.on_edit(handle_edit_portfolio_tabulator)
history_tabulator.on_edit(hanlde_edit_history_tabulator)
# create handler component to add to panel so can be listened to
upload_xlsx_handler = pn.bind(update_stock_column, file_input)
# layout
editor_widget = pn.Column(floating_windows, datetime_picker, upload_to_db_btn, new_stock_btn,
preview_btn, force_recalculate_btn, file_input, pn.widgets.TooltipIcon(
value="用于更新修改持仓信息,默认股票为最近持仓,默认时间为目前北京时间,点击增加新股票按钮,输入股票代码和持仓选择日期(北京时间),点击预览,确认无误后点击保存到数据库。或者直接拖拽excel文件到下方上传按钮"),
stock_column, width=MIN_COMPONENT_WIDTH, height_policy='max')
# tooltip
toolTip2 = pn.widgets.TooltipIcon(
value="持仓总结,每一行的已同步到数据库代表所做更改是否已同步到数据库,点击保存到数据库将上传所有更改。点击右侧📋按钮查看详细持仓变化报告")
return pn.Column(
pn.Row(
pn.layout.HSpacer(),
editor_widget,
pn.Spacer(width=10),
history_tabulator,
pn.Spacer(width=10),
portfolio_tabulator,
pn.Spacer(width=10),
upload_xlsx_handler,
pn.layout.HSpacer(),
height=1500,
# width_policy='max', height_policy='max')
# sizing_mode='stretch_both',
))
# app
template = pn.template.FastListTemplate(
title='portfolio编辑', sidebar_width=200, collapsed_sidebar=True)
template.sidebar.append(SideNavBar())
template.main.append(app())
template.servable()
|