{
"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",
"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
}