tables.qmd

---
title: "Tables for Personal Finance (Ebenezer)"
author: "Deepansh Khurana"
format:
  html:
    code-fold: true
theme:
  - cosmo
  - style/custom.scss
editor: visual
---

::: {style="text-align: justify;"}
When I made the [Hrafnagud](https://github.com/DeepanshKhurana/Hrafnagud-Dynamo "Hrafnagud-Dynamo") project I recently talked about at **ShinyConf** (and am about to talk about at **useR!** in July and **EARL** in September), I also made several apps for myself to consume each API. The app and API for tracking my finances is called **Ebenezer**, named after the miserly Scrooge. My goal was to take each kind of financial instrument and make a table that fits well for it. Bonds work differently than Equity stocks, for example, and therefore, should be represented differently. The result was the following tables, which are now a part and parcel of my application.
:::

#### Key Features

::: {style="text-align: justify;"}
One of the key features of these tables are the way the colors are assigned automatically. This is done using a *get_css_class()* function which basically assigns the different colors and highlights wherever required.

Another important bit is how the tables themselves are constructed. Processing functions are written in the *cell* parameter of the *reactable()* call. The function then takes up a concatenated string and builds up a *div* structure that is styled with a custom scss file.
:::

## Setup

Here we setup some libraries, functions and load the dataframes for these examples.

```{r "setup"}

suppressPackageStartupMessages(library(dplyr))
suppressPackageStartupMessages(library(glue))
suppressPackageStartupMessages(library(lubridate))
suppressPackageStartupMessages(library(reactable))
suppressPackageStartupMessages(library(reactablefmtr))
suppressPackageStartupMessages(library(shiny))

source("utils/data_formatting.R")

files <- list.files("data", "*.csv")
paths <- paste("data", files, sep = "/")
data <- lapply(
  paths,
  read.csv
)

names(data) <- gsub(".csv", "", files)
```

Now that the data is loaded, we can take each table at a time.

### Ticker

::: {style="text-align: justify;"}
The ticker is a simple table that needs a few key pieces of information. The most important out of which is the correct semantic colour for rise and fall of a stock.

**Note:** While I usually consume this data from a custom API, for the sake of illustration and reproducibility, I have included a static file here as a .csv.

Let's try to make a Ticker Table.
:::

```{r "ticker"}

ticker_data <- data$ticker_data

colnames(ticker_data) <- c(
  "Code", "Stock", "Price", "Change", "Percent"
)

ticker_data$Stock <- paste(
  ticker_data$Code,
  ticker_data$Stock,
  sep = "_"
)

ticker_data$Price <-
  paste(ticker_data$Price,
    ticker_data$Change,
    ticker_data$Percent,
    sep = "_"
  )

ticker_data <- ticker_data[, c("Stock", "Price")] |>
  na.omit()

reactable(
  data = ticker_data,
  pagination = FALSE,
  sortable = FALSE,
  searchable = TRUE,
  columns = list(
    Stock = colDef(
      align = "left",
      cell = function(value) {
        value <- strsplit(value, "_")[[1]]
        div(
          class = "stock-name-row",
          div(
            class = "stock-code",
            value[1]
          ),
          div(
            class = "stock-name",
            value[2]
          )
        )
      }
    ),
    Price = colDef(
      align = "right",
      cell = function(value) {
        value <- strsplit(value, "_")[[1]]

        price <- as.numeric(value[1])
        change <- as.numeric(value[2])
        percent <- as.numeric(value[3])

        indicator <- get_css_class(change, type = "all")

        div(
          class = "stock-price-row",
          div(
            class = "stock-price",
            style = list(
              fontSize = "1.5em",
              fontWeight = 600
            ),
            paste0(
              "$",
              sub(
                "\\.0+$",
                "",
                format_price(price)
              )
            )
          ),
          div(
            class = "stock-change-block",
            div(
              class = paste("stock-percent",
                indicator$text,
                indicator$highlight,
                sep = " "
              ),
              paste0(sub("-", "", preserve_zero(percent)), "%")
            ),
            div(
              class = paste("stock-change",
                indicator$text,
                sep = " "
              ),
              sub("-", "", format_price(change))
            )
          ),
        )
      }
    )
  )
)
```

### Stocks

::: {style="text-align: justify;"}
Alright, now that we have data for the ticker, we can create a stocks table. The table below gives you the portfolio with different fields such as Invested Value, Quantity, Average Price on the left, and Unrealized P&L, Realized P&L, Current Price on the right. If a user hovers on the icons, they will find these labels as well.
:::

```{r "stocks"}

portfolio <- data$stocks_data |>
        mutate(
          unrealized_percent =
            true_round(
              unrealized * 100 / holding_value, 2
            )
        ) |>
        mutate(
          left =
            paste(
              stock_symbol,
              name,
              holding_value,
              quantity,
              avg_price,
              sep = "_"
            ),
          right = paste(
            unrealized,
            unrealized_percent,
            realized,
            current_price,
            change_percent,
            sep = "_"
          )
        ) |>
        select(left, right)

reactable(
  data = portfolio,
  pagination = FALSE,
  searchable = TRUE,
  columns = list(
    left = colDef(
      align = "left",
      cell = function(value) {
        value <- strsplit(value, "_")[[1]]
        div(
          class = "stock-left-area",
          div(
            class = "stock-name-row",
            div(
              class = "stock-code",
              value[1]
            ),
            div(
              class = "stock-name",
              value[2]
            )
          ),
          div(
            class = "stock-holding-row",
            div(
              class = "stock-market-value small-row-left",
              title = "Invested Value",
              icon(name = "vault", class = "label"),
              p(class = "value", format_price(value[3], 2))
            ),
            div(
              class = "stock-quantity small-row-left",
              title = "Quantity",
              icon(name = "hashtag", class = "label"),
              p(class = "value", value[4])
            ),
            div(
              class = "stock-avg-price small-row-left",
              title = "Average Price",
              icon(name = "barcode", class = "label"),
              p(class = "value", paste0("₹", true_round(value[5], 2)))
            )
          )
        )
      }
    ),
    right = colDef(
      align = "right",
      cell = function(value) {
        value <- strsplit(value, "_")[[1]]

        unrealized_class <- get_css_class(value[2], type = "text")
        realized_class <- get_css_class(value[3], type = "text")
        current_price_class <- get_css_class(value[5], type = "text")

        div(
          class = "stock-right-area",
          div(
            class = "stock-unrealized-row small-row-right",
            title = "Unrealized P&L",
            icon(
              name = "money-bill-transfer",
              class = paste("stock-current-price label",
                unrealized_class,
                sep = " "
              )
            ),
            p(
              class = paste("stock-unrealized value",
                unrealized_class,
                sep = " "
              ),
              format_price(value[1])
            ),
            p(
              class = paste("stock-unrealized-percent value",
                unrealized_class,
                sep = " "
              ),
              paste0("(", value[2], "%)")
            )
          ),
          div(
            class = "stock-realized small-row-right",
            title = "Realized P&L",
            icon(
              name = "receipt",
              class = paste("stock-current-price label",
                realized_class,
                sep = " "
              )
            ),
            p(
              class = paste("value",
                realized_class,
                sep = " "
              ),
              format_price(value[3])
            )
          ),
          div(
            class = "stock-current-price-row small-row-right",
            title = "Current Price",
            icon(
              name = "comment-dollar",
              class = paste("stock-current-price label",
                current_price_class,
                sep = " "
              )
            ),
            div(
              p(
                class = paste(
                  "stock-current-price value",
                  current_price_class,
                  sep = " "
                ),
                value[4]
              )
            ),
            p(
              class = paste(
                "stock-current-price-percent value",
                current_price_class,
                sep = " "
              ),
              paste0("(", value[5], "%)")
            )
          )
        )
      }
    )
  )
)
```

### Bonds

::: {style="text-align: justify;"}
Between Stocks and Bonds, something changes. Most stock data is current. You need the most crucial information immediately. With bonds, the information needed is a bit different. You need to know the current amount, the amount it will total to when the time period passes and the progress. So, with the help of *reactablefmtr*, I added a progress bar to the tables.

Another thing to note is that the progress bar color changes according to a palette. This gives the user visual feedback for how far their bonds have progressed to maturity. Darker is "matures soon".
:::

```{r "bonds"}

bonds <- data$sgb_data

today <- Sys.Date()

bonds$info <- paste(
  bonds$name,
  bonds$invested_date,
  bonds$maturity_date,
  bonds$category,
  bonds$quantity,
  bonds$unit_price,
  bonds$total,
  sep = "_"
)

bonds$progress <-
  1 - as.numeric(
    difftime(
      format_date(bonds$maturity_date),
      as.Date(today),
      units = "days"
    )
  ) / (8 * 365.25)

bonds <- bonds |>
  select(info, progress) |>
  rowwise() |>
  mutate(
    color = get_progress_bar_colour(
      progress,
      c(
        "#a3b18a",
        "#588157",
        "#3a5a40",
        "#344e41"
      )
    )
  ) |>
  ungroup()

reactable(
  bonds,
  class = "sgb-reactable",
  columns = list(
    info = colDef(
      name = "Bond",
      cell = function(string) {
        string <- strsplit(string, "_")[[1]]
        div(
          class = "sgb-row",
          div(
            class = "sgb-name",
            gsub(
              "BOND NAME",
              "/",
              string[1]
            )
          ),
          div(
            class = "sgb-timeline",
            year(format_date(string[2])),
            icon("arrow-right"),
            year(format_date(string[3]))
          ),
          div(
            class = "sgb-metrics-container",
            div(
              class = "sgb-category metric",
              title = "Category",
              icon("receipt", class = "label"),
              p(string[4], class = "value")
            ),
            div(
              class = "sgb-quantity metric",
              title = "Quantity",
              icon("hashtag", class = "label"),
              p(string[5], class = "value")
            ),
            div(
              class = "sgb-price metric",
              title = "Unit Price",
              icon("barcode", class = "label"),
              p(format_price(string[6]), class = "value")
            ),
            div(
              class = "sgb-total metric",
              title = "Total Invested",
              icon("vault", class = "label"),
              p(format_price(string[7]), class = "value")
            ),
          )
        )
      }
    ),
    progress = colDef(
      cell = data_bars(
        bonds,
        max_value = 1,
        fill_color_ref = "color",
        background = "#f1f3f4",
        round_edges = TRUE,
        bar_height = 20,
        number_fmt = scales::label_percent()
      )
    ),
    color = colDef(
      show = FALSE
    )
  )
)
```

### Funds

::: {style="text-align: justify;"}
Funds work similar to the bonds with slight variation. In some ways, they are a mixture between Stocks and Bonds. But with all the tables covered above, this one requires no explanation. This is how we can reuse most of our CSS here as well.

The key here is a column called *is_sip_ongoing,* which is used to split the tables into two, with slightly different structures.
:::

#### SIPs

::: {style="text-align: justify;"}
For SIPs, we have a simple reminder for each row which tells the date for the installment and the amount.
:::

```{r "funds_sip"}

sips <- data$funds_data |>
  mutate(
    left = paste(
      provider,
      name,
      total_invested,
      unit_balance,
      sep = "_"
    ),
    right = paste(
      current_value,
      profit,
      change_percent,
      sep = "_"
    )
  ) |>
  select(
    c(
      left,
      right,
      is_sip_ongoing,
      sip_date,
      sip_amount
    )
  ) |>
  filter(is_sip_ongoing == 1) |>
  mutate(
    left = paste(
      left,
      sip_date,
      sip_amount,
      sep = "_"
    )
  ) |>
  select(
    -c(
      is_sip_ongoing,
      sip_date,
      sip_amount
    )
  )

reactable(
  sips,
  searchable = TRUE,
  columns = list(
    left = colDef(
      name = "Invested",
      align = "left",
      cell = function(string) {
        string <- strsplit(string, "_")[[1]]

        div(
          class = "sip-table",
          div(
            class = "fund-holding-container stock-left-area",
            div(
              class = "stock-name-row",
              div(
                class = "stock-code",
                string[1]
              ),
              div(
                class = "stock-name",
                string[2]
              )
            ),
            div(
              class = "stock-holding-row",
              div(
                class = "fund-invested small-row-left",
                title = "Invested Value",
                icon(name = "vault", class = "label"),
                p(class = "value", format_price(string[3], 2))
              ),
              div(
                class = "fund-units small-row-left",
                title = "Total Units",
                icon(name = "hashtag", class = "label"),
                p(class = "value", true_round(string[4], 2))
              )
            ),
            p(
              class = "fund-sip-information",
              glue("${format_price(string[6], 0)} on the {string[5]}th")
            )
          )
        )
      }
    ),
    right = colDef(
      name = "Current",
      align = "right",
      cell = function(string) {
        string <- strsplit(string, "_")[[1]]
        price_class <- get_css_class(string[2], type = "text")

        div(
          class = "sip-table stock-right-area",
          div(
            class = "fund-current small-row-right",
            icon(
              name = "money-bill-transfer",
              title = "Current Value",
              class = paste("stock-current-price label",
                price_class,
                sep = " "
              )
            ),
            p(
              format_price(string[1]),
              title = "Change %",
              class = paste("stock-current-price value",
                price_class,
                sep = " "
              )
            )
          ),
          div(
            class = "fund-profit small-row-right",
            icon(
              name = "comment-dollar",
              class = paste("stock-current-price label",
                price_class,
                sep = " "
              )
            ),
            p(
              glue("{string[3]}%"),
              class = paste("stock-current-price value",
                price_class,
                sep = " "
              )
            )
          )
        )
      }
    )
  )
)
```

#### Lumpsum

::: {style="text-align: justify;"}
The Lumpsum funds look very, very similar to the stocks interface and this is by design.
:::

```{r "funds_lumpsum"}

funds <- data$funds_data |>
  mutate(
    left = paste(
      provider,
      name,
      total_invested,
      unit_balance,
      sep = "_"
    ),
    right = paste(
      current_value,
      profit,
      change_percent,
      sep = "_"
    )
  ) |>
  filter(is_sip_ongoing != 1) |>
  select(
    c(
      left,
      right
    )
  )

reactable(
  funds,
  searchable = TRUE,
  columns = list(
    left = colDef(
      name = "Invested",
      align = "left",
      cell = function(string) {
        string <- strsplit(string, "_")[[1]]
  
        div(
          class = "sip-table",
          div(
            class = "fund-holding-container stock-left-area",
            div(
              class = "stock-name-row",
              div(
                class = "stock-code",
                string[1]
              ),
              div(
                class = "stock-name",
                string[2]
              )
            ),
            div(
              class = "stock-holding-row",
              div(
                class = "fund-invested small-row-left",
                title = "Invested Value",
                icon(name = "vault", class = "label"),
                p(class = "value", format_price(string[3], 2))
              ),
              div(
                class = "fund-units small-row-left",
                title = "Total Units",
                icon(name = "hashtag", class = "label"),
                p(class = "value", true_round(string[4], 2))
              )
            )
          )
        )
      }
    ),
    right = colDef(
      name = "Current",
      align = "right",
      cell = function(string) {
        string <- strsplit(string, "_")[[1]]
        price_class <- get_css_class(string[2], type = "text")
  
        div(
          class = "sip-table stock-right-area",
          div(
            class = "fund-current small-row-right",
            icon(
              name = "money-bill-transfer",
              title = "Current Value",
              class = paste("stock-current-price label",
                price_class,
                sep = " "
              )
            ),
            p(
              format_price(string[1]),
              title = "Change %",
              class = paste("stock-current-price value",
                price_class,
                sep = " "
              )
            )
          ),
          div(
            class = "fund-profit small-row-right",
            icon(
              name = "comment-dollar",
              class = paste("stock-current-price label",
                price_class,
                sep = " "
              )
            ),
            p(
              glue("{string[3]}%"),
              class = paste("stock-current-price value",
                price_class,
                sep = " "
              )
            )
          )
        )
      }
    )
  )
)
```