{ "cells": [ { "cell_type": "markdown", "id": "60f394d7-82f1-4326-9109-82478dfd9dd9", "metadata": {}, "source": [ "The Portfolio Analysis App demonstrates the powerful [Tabulator](../reference/widgets/Tabulator.ipynb) table that ships with Panel.\n", "\n", "\n", "\n", "This app is heavily inspired by the Dash AG Grid App [here](https://github.com/plotly/dash-ag-grid/blob/dev/docs/demo_stock_portfolio.py). Having both enables you to compare [pros and cons of Panel w. Tabulator versus Dash w. AG Grid](https://github.com/holoviz/panel/issues/4341)." ] }, { "cell_type": "code", "execution_count": null, "id": "06ac7f7d-4966-446e-8b2b-ef47450e0642", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import plotly.express as px\n", "import plotly.graph_objects as go\n", "\n", "import panel as pn\n", "\n", "pn.extension('plotly', 'tabulator')" ] }, { "cell_type": "code", "execution_count": null, "id": "e14ccb07-dcd5-4d09-ae67-1a80f82eb4d4", "metadata": {}, "outputs": [], "source": [ "ACCENT = \"#BB2649\"\n", "RED = \"#D94467\"\n", "GREEN = \"#5AD534\"\n", "\n", "LINK_SVG = \"\"\"\n", "\n", " \n", "\n", "\"\"\"\n", "\n", "CSV_URL = \"https://datasets.holoviz.org/equities/v1/equities.csv\"" ] }, { "cell_type": "markdown", "id": "3c831a15-da71-4371-9f8a-a04dd464019f", "metadata": {}, "source": [ "Lets define our list of equities" ] }, { "cell_type": "code", "execution_count": null, "id": "47e4aac2-a07c-4d6c-91d9-eefa76efbd89", "metadata": {}, "outputs": [], "source": [ "EQUITIES = {\n", " \"AAPL\": \"Apple\",\n", " \"MSFT\": \"Microsoft\",\n", " \"AMZN\": \"Amazon\",\n", " \"GOOGL\": \"Alphabet\",\n", " \"TSLA\": \"Tesla\",\n", " \"BRK-B\": \"Berkshire Hathaway\",\n", " \"UNH\": \"United Health Group\",\n", " \"JNJ\": \"Johnson & Johnson\",\n", "}\n", "EQUITY_LIST = tuple(EQUITIES.keys())" ] }, { "cell_type": "markdown", "id": "22487003-4910-4ae1-95e1-4e871ddd4535", "metadata": {}, "source": [ "## Extract the data\n", "\n", "We would be using *caching* (`pn.cache`) to improve the performance of the app if we where loading data from a live data source like `yfinance`." ] }, { "cell_type": "code", "execution_count": null, "id": "babaf3df-678d-4aea-8dd6-300201f13bf9", "metadata": {}, "outputs": [], "source": [ "@pn.cache(ttl=600)\n", "def get_historical_data(tickers=EQUITY_LIST, period=\"2y\"):\n", " \"\"\"Downloads the historical data from Yahoo Finance\"\"\"\n", " df = pd.read_csv(CSV_URL, index_col=[0, 1], parse_dates=['Date'])\n", " return df\n", "\n", "historical_data = get_historical_data()\n", "historical_data.head(3).round(2)" ] }, { "cell_type": "markdown", "id": "a50540fa-5f12-4b84-95b0-13a935f8319c", "metadata": {}, "source": [ "## Transform the data\n", "\n", "Let us calculate the `summary_data` to show in the Table." ] }, { "cell_type": "code", "execution_count": null, "id": "f3c8cf02-327d-4cbb-abfe-8f9f9d9eac63", "metadata": {}, "outputs": [], "source": [ "def last_close(ticker, data=historical_data):\n", " \"\"\"Returns the last close pricefor the given ticker\"\"\"\n", " return data.loc[ticker][\"Close\"].iloc[-1]\n", "\n", "last_close(\"AAPL\")" ] }, { "cell_type": "code", "execution_count": null, "id": "d3f6da46-3f83-48e6-9060-27751300861d", "metadata": {}, "outputs": [], "source": [ "summary_data_dict = {\n", " \"ticker\": EQUITY_LIST,\n", " \"company\": EQUITIES.values(),\n", " \"info\": [\n", " f\"\"\"\n", "
{LINK_SVG}
\"\"\"\n", " for ticker in EQUITIES\n", " ],\n", " \"quantity\": [75, 40, 100, 50, 40, 60, 20, 40],\n", " \"price\": [last_close(ticker) for ticker in EQUITIES],\n", " \"value\": None,\n", " \"action\": [\"buy\", \"sell\", \"hold\", \"hold\", \"hold\", \"hold\", \"hold\", \"hold\"],\n", " \"notes\": [\"\" for i in range(8)],\n", "}\n", "\n", "summary_data = pd.DataFrame(summary_data_dict)\n", "\n", "def get_value_series(data=summary_data):\n", " \"\"\"Returns the quantity * price series\"\"\"\n", " return data[\"quantity\"] * data[\"price\"]\n", "\n", "summary_data[\"value\"] = get_value_series()\n", "summary_data.head(2)" ] }, { "cell_type": "markdown", "id": "2ddedd48-a25a-4e79-8f7b-720925c47df0", "metadata": {}, "source": [ "## Define the *Summary Table*\n", "\n", "We define the configuration of the Tabulator below." ] }, { "cell_type": "code", "execution_count": null, "id": "b5000956-5189-42fc-b685-00d461db0e65", "metadata": {}, "outputs": [], "source": [ "titles = {\n", " \"ticker\": \"Stock Ticker\",\n", " \"company\": \"Company\",\n", " \"info\": \"Info\",\n", " \"quantity\": \"Shares\",\n", " \"price\": \"Last Close Price\",\n", " \"value\": \"Market Value\",\n", " \"action\": \"Action\",\n", " \"notes\": \"Notes\",\n", "}\n", "frozen_columns = [\"ticker\", \"company\"]\n", "editors = {\n", " \"ticker\": None,\n", " \"company\": None,\n", " \"quantity\": {\"type\": \"number\", \"min\": 0, \"step\": 1},\n", " \"price\": None,\n", " \"value\": None,\n", " \"action\": {\n", " \"type\": \"list\",\n", " \"values\": {\"buy\": \"buy\", \"sell\": \"sell\", \"hold\": \"hold\"},\n", " },\n", " \"notes\": {\n", " \"type\": \"textarea\",\n", " \"elementAttributes\": {\"maxlength\": \"100\"},\n", " \"selectContents\": True,\n", " \"verticalNavigation\": \"editor\",\n", " \"shiftEnterSubmit\": True,\n", " },\n", " \"info\": None,\n", "}\n", "\n", "widths = {\"notes\": 400}\n", "formatters = {\n", " \"price\": {\"type\": \"money\", \"decimal\": \".\", \"thousand\": \",\", \"precision\": 2},\n", " \"value\": {\"type\": \"money\", \"decimal\": \".\", \"thousand\": \",\", \"precision\": 0},\n", " \"info\": {\"type\": \"html\", \"field\": \"html\"},\n", "}\n", "\n", "text_align = {\n", " \"price\": \"right\",\n", " \"value\": \"right\",\n", " \"action\": \"center\",\n", " \"info\": \"center\",\n", "}\n", "base_configuration = {\n", " \"clipboard\": \"copy\"\n", "}" ] }, { "cell_type": "markdown", "id": "e8ba0bd7-a8ae-4a71-888a-94f70538a468", "metadata": {}, "source": [ "Here we define the `summary_table` *widget*." ] }, { "cell_type": "code", "execution_count": null, "id": "6b4ed809-9844-4628-83da-466afc1f60ce", "metadata": {}, "outputs": [], "source": [ "summary_table = pn.widgets.Tabulator(\n", " summary_data,\n", " editors=editors,\n", " formatters=formatters,\n", " frozen_columns=frozen_columns,\n", " layout=\"fit_data_table\",\n", " selectable=1,\n", " show_index=False,\n", " text_align=text_align,\n", " titles=titles,\n", " widths=widths,\n", " configuration=base_configuration,\n", ")\n", "summary_table" ] }, { "cell_type": "markdown", "id": "cb9cc6ff-04b4-485e-848f-cabd2576c0cb", "metadata": {}, "source": [ "Now lets *style* the table using the *Pandas styler* api." ] }, { "cell_type": "code", "execution_count": null, "id": "b2a10653-125d-4735-a202-202a8e384b7a", "metadata": {}, "outputs": [], "source": [ "def style_of_action_cell(value, colors={'buy': GREEN, 'sell': RED}):\n", " \"\"\"Returns the css to apply to an 'action' cell depending on the val\"\"\"\n", " return f'color: {colors[value]}' if value in colors else ''\n", "\n", "summary_table.style.applymap(style_of_action_cell, subset=[\"action\"]).set_properties(\n", " **{\"background-color\": \"#444\"}, subset=[\"quantity\"]\n", ")" ] }, { "cell_type": "markdown", "id": "4cb5379a-6664-4b84-bf37-7c04d7a555ab", "metadata": {}, "source": [ "For later we also need a function to handle when a user edits a cell in the table" ] }, { "cell_type": "code", "execution_count": null, "id": "91b9e20b-2233-41ff-934d-f1702a4b3052", "metadata": {}, "outputs": [], "source": [ "patches = pn.widgets.IntInput(description=\"Used to raise an event when a cell value has changed\")\n", "\n", "def handle_cell_edit(event, table=summary_table):\n", " \"\"\"Updates the `value` cell when the `quantity` cell is updated\"\"\"\n", " row = event.row\n", " column = event.column\n", " if column == \"quantity\":\n", " quantity = event.value\n", " price = summary_table.value.loc[row, \"price\"]\n", " value = quantity * price\n", " table.patch({\"value\": [(row, value)]})\n", "\n", " patches.value +=1" ] }, { "cell_type": "markdown", "id": "9f3e8f0b-70ef-41ee-9259-2a8f82940c76", "metadata": {}, "source": [ "## Define the plots" ] }, { "cell_type": "code", "execution_count": null, "id": "efe47a55-3e77-4b5f-9b43-4d8ea4e8b4ef", "metadata": {}, "outputs": [], "source": [ "def candlestick(selection=[], data=summary_data):\n", " \"\"\"Returns a candlestick plot\"\"\"\n", " if not selection:\n", " ticker = \"AAPL\"\n", " company = \"Apple\"\n", " else:\n", " index = selection[0]\n", " ticker = data.loc[index, \"ticker\"]\n", " company = data.loc[index, \"company\"]\n", "\n", " dff_ticker_hist = historical_data.loc[ticker].reset_index()\n", " dff_ticker_hist[\"Date\"] = pd.to_datetime(dff_ticker_hist[\"Date\"])\n", "\n", " fig = go.Figure(\n", " go.Candlestick(\n", " x=dff_ticker_hist[\"Date\"],\n", " open=dff_ticker_hist[\"Open\"],\n", " high=dff_ticker_hist[\"High\"],\n", " low=dff_ticker_hist[\"Low\"],\n", " close=dff_ticker_hist[\"Close\"],\n", " )\n", " )\n", " fig.update_layout(\n", " title_text=f\"{ticker} {company} Daily Price\",\n", " template=\"plotly_dark\",\n", " autosize=True,\n", " )\n", " return fig\n", "\n", "pn.pane.Plotly(candlestick())" ] }, { "cell_type": "code", "execution_count": null, "id": "9b0fd432-f167-43c7-a5ff-b23ccf427080", "metadata": {}, "outputs": [], "source": [ "def portfolio_distribution(patches=0):\n", " \"\"\"Returns the distribution of the portfolio\"\"\"\n", " data = summary_table.value\n", " portfolio_total = data[\"value\"].sum()\n", "\n", " fig = px.pie(\n", " data,\n", " values=\"value\",\n", " names=\"ticker\",\n", " hole=0.3,\n", " title=f\"Portfolio Total $ {portfolio_total:,.0f}\",\n", " template=\"plotly_dark\",\n", " )\n", " fig.layout.autosize = True\n", " return fig\n", "\n", "pn.pane.Plotly(portfolio_distribution())" ] }, { "cell_type": "markdown", "id": "6e25cad3-c418-4423-8d23-2fc460b4ca10", "metadata": {}, "source": [ "## Bind the widgets and functions" ] }, { "cell_type": "markdown", "id": "19948be6-f4df-4d88-aa58-8f9038e1cf15", "metadata": {}, "source": [ "We want the `candlestick` plot to depend on the selection in `summary_table`" ] }, { "cell_type": "code", "execution_count": null, "id": "dc0f99f9-4cc4-4b89-bd8b-ad143eadc6c7", "metadata": {}, "outputs": [], "source": [ "candlestick = pn.bind(candlestick, selection=summary_table.param.selection)" ] }, { "cell_type": "markdown", "id": "53747c38-38a2-4f7e-891e-02bf6267e482", "metadata": {}, "source": [ "We want the `portfolio_distribution` to be updated when ever a cell value changes in the table" ] }, { "cell_type": "code", "execution_count": null, "id": "e4405258-1671-4ef4-b830-821703b66c30", "metadata": {}, "outputs": [], "source": [ "summary_table.on_edit(handle_cell_edit)\n", "\n", "portfolio_distribution = pn.bind(portfolio_distribution, patches=patches)" ] }, { "cell_type": "markdown", "id": "5024d93f-f17f-4dc8-b251-493bd9f772d7", "metadata": {}, "source": [ "## Test the app" ] }, { "cell_type": "code", "execution_count": null, "id": "9b3f2e30-5a5a-4fc3-a925-b357f236415b", "metadata": {}, "outputs": [], "source": [ "pn.Column(\n", " pn.Row(\n", " pn.pane.Plotly(candlestick), \n", " pn.pane.Plotly(portfolio_distribution)\n", " ),\n", " summary_table,\n", " height=600\n", ")" ] }, { "cell_type": "markdown", "id": "ad8b4f4d-81ff-4f1e-a57a-8f666516d5f5", "metadata": {}, "source": [ "## Layout the app in a nice template\n", "\n", "We will use the `FastGridTemplate` which provides a nice dashboard layout with Panels you can resize and move around interactively." ] }, { "cell_type": "code", "execution_count": null, "id": "128fb9f8-b209-4168-a150-1199cbe4e8f5", "metadata": {}, "outputs": [], "source": [ "template = pn.template.FastGridTemplate(\n", " title=\"Portfolio Analysis\",\n", " accent_base_color=ACCENT,\n", " header_background=ACCENT,\n", " prevent_collision=True,\n", " save_layout=True,\n", " theme_toggle=False,\n", " theme='dark',\n", " row_height=160\n", ")" ] }, { "cell_type": "markdown", "id": "fd56569a-14f6-425c-a049-ad7a6b1bd7d1", "metadata": {}, "source": [ "Lets add the plots and table to the template" ] }, { "cell_type": "code", "execution_count": null, "id": "0de76e59-15f6-4ad7-9379-7b0fde1e0602", "metadata": {}, "outputs": [], "source": [ "template.main[0:3, 0:8] = pn.pane.Plotly(candlestick)\n", "template.main[0:3, 8:12] = pn.pane.Plotly(portfolio_distribution)\n", "template.main[3:5, :] = summary_table" ] }, { "cell_type": "markdown", "id": "a7169d20-4fdc-4040-82c4-1171985c711d", "metadata": {}, "source": [ "The template does not display in a notebook so we only output it when in a *server* context." ] }, { "cell_type": "code", "execution_count": null, "id": "c5a7878f-e4a6-43a6-a1f4-640ca4c6403c", "metadata": {}, "outputs": [], "source": [ "if pn.state.served:\n", " template.servable()" ] } ], "metadata": { "language_info": { "name": "python", "pygments_lexer": "ipython3" } }, "nbformat": 4, "nbformat_minor": 5 }