portfolioEditingPage.py

# %%
# 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': "<i>📋</i>"},
                                             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()