# %% # load portfolio import panel as pn from utils import create_stocks_entry_from_excel, style_number, create_share_changes_report import datetime as dt import pytz import io 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 from pipeline import update_portfolio_profile_to_db import table_schema import pipeline db_url = 'sqlite:///instance/local.db' pn.extension() pn.extension('tabulator') pn.extension('plotly') pn.extension('floatpanel') # %% # the width of iphone se MIN_COMPONENT_WIDTH = 375 MAX_COMPONENT_WIDTH = 600 # %% def app(): # load portfolio df with create_engine(db_url).connect() as conn: p_profile = pd.read_sql_table(table_schema.PORTFOLIO_TABLE, con=conn) p_profile.date = pd.to_datetime(p_profile.date) 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 = pd.read_sql_table(table_schema.STOCKS_DETAILS_TABLE, con=conn) 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='local', # 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': "📋"}, hidden_columns=hidden_column, height_policy='max', titles=col_to_titles) # 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() 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) # 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( ) history_tabulator.patch({ 'change_saved': [(index[0], False)] }, as_index=True) # 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) # mark synced_to_db to false when editing or select not synced_to_db # if dt is not None and df.date[0] in history_tabulator.value.date.values: # history_tabulator.stream(df[['date','sync_to_db','change_saved']], follow=True) # update mark all synced_to_db to true when update def delete_stock(row): '''delete a stock entry''' stock_column.remove(row) 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') mean_price_input = pn.widgets.FloatInput( name='平均成本', value=ave_price, step=0.01, start=0, sizing_mode='stretch_width') row = pn.Row( delete_btn, ticker_selector, share_input, mean_price_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) def _get_stocks_price(df): '''return a df with latest stock price added the new portfolio entry''' stock_price = api.fetch_stocks_price( security=df.ticker.to_list(), end_date=df.date[0], count=1, frequency='minute', ) stock_price.rename(columns={'time': 'stock_price_ts'}, inplace=True) merged_df = df.merge( stock_price[['ticker', 'stock_price_ts', 'close']], on='ticker', how='left') return merged_df def _calculate_weigth(df): ''' calculate weight on new portfolio entry ''' df['total_value'] = df.shares * df.close df['weight'] = df.total_value / df.total_value.sum() def update_profile_tabulator(e): '''add all stocks entry to ui''' new_entry = [dict(ticker=row[1].value, shares=row[2].value, ave_price=row[3].value, date=datetime_picker.value) for row in stock_column] if len(new_entry) == 0: print("no entry added") return new_profile = pipeline.create_portfolio_profile_df(new_entry) # calculate share changes tmp_profile = pd.concat([p_profile, new_profile], ignore_index=True) tmp_profile.sort_values(by='date', inplace=True) tmp_profile['share_changes'] = tmp_profile.groupby('ticker')[ 'shares'].diff() tmp_profile['share_changes'] = tmp_profile['share_changes'].fillna( tmp_profile['shares']) new_profile = new_profile.merge(tmp_profile[[ 'ticker', 'date', 'share_changes', 'change_saved']], on=['ticker', 'date'], how='left') # fill emtpy change_saved to False new_profile['change_saved'] = new_profile['change_saved'].fillna(False) new_profile['sync_to_db'] = True # calculate cash and weight new_profile['cash'] = new_profile.shares * new_profile.ave_price new_profile['weight'] = new_profile.cash / new_profile.cash.sum() # update history tabulator _update_history_tabulator('append', new_profile) _stream_to_portfolio_tabulator(new_profile) def add_new_stock(e): row = create_new_stock_entry() stock_column.append(row) def _stream_to_portfolio_tabulator(entry): if len(portfolio_tabulator.value) == 0: portfolio_tabulator.value = entry else: portfolio_tabulator.stream(entry, follow=True) 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) def handle_sync_to_db(e): # TODO: change to use profile df instead, because tabulator might not contain all entry '''sync selected entry to db''' new_portfolio = portfolio_tabulator.value # TODO when initially df is empty, there is a 0 row in df as place holder # only update selected row to db selected_portfolio = new_portfolio[new_portfolio['sync_to_db']] successed = update_portfolio_profile_to_db(selected_portfolio) # update history tabulator and portfolio tabulator if successed: # 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') def handle_edit_portfolio_tabulator(e): date = portfolio_tabulator.value.iloc[e.row]['date'] _update_history_tabulator(df=date, action='edit') print(date) # %% # 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 ) portfolio_tabulator.on_edit(handle_edit_portfolio_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, file_input, pn.widgets.TooltipIcon( value="用于更新修改持仓信息,默认股票为最近持仓，默认时间为目前北京时间，点击增加新股票按钮,输入股票代码和持仓选择日期(北京时间),点击预览,确认无误后点击保存到数据库。或者直接拖拽excel文件到下方上传按钮"), stock_column, width=MIN_COMPONENT_WIDTH, height_policy='max') # tooltip toolTip2 = pn.widgets.TooltipIcon( value="持仓总结,每一行的已同步到数据库代表所做更改是否已同步到数据库,点击保存到数据库将上传所有更改。点击右侧📋按钮查看详细持仓变化报告") return 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编辑') template.main.append(app()) template.servable()