In rkuttner7/homeBudget: Maintain records and report on spending, income, and savings

Income Statement

The income statement (or profit & loss) measures the profitability of the home by comparing the income to the associated costs.

knitr::opts_knit$set(root.dir = (normalizePath(getwd())) )

knitr::opts_chunk$set(fig.width = 10, fig.asp = 1/2)
require('magrittr') # load for "%>%" used to format DT tables

ggplot2::theme_set(ggthemes::theme_clean())

# time frame of report, reference point for setting the quarterly periods
qrt_int <- homeBudget::quarterly_intervals(end = as.Date("2019-12-31"),
                                           cutoff = Sys.Date())

timePeriods <- data.table::data.table(interval_start = lubridate::int_start(qrt_int), 
                                      interval_end = lubridate::int_end(qrt_int))
timePeriods[ , ':='(period = data.table::frank(interval_start, 
                                               ties.method = "dense"))]

# Print P&L table for each period
incomeTable <- function(mytable, myperiod){
  mytablePeriod <- mytable[period==myperiod]

  if(nrow(mytablePeriod) == 0) return("No Data")

  tableTitle <- sprintf("P&L Quarter %i, %s to %s", 
                    unique(mytablePeriod$period), 
                    unique(mytablePeriod$interval_start),
                    unique(mytablePeriod$interval_end))

  incomeLength <- nrow(incomeDataSum[period==myperiod])
  costLength <- nrow(costDataSum[period==myperiod])

  knitr::kable(mytablePeriod[ , .(category, Amount)], 
             align = c('l', 'r'),
             col.names = NULL, 
             format = "html", 
             caption = tableTitle) %>%
    kableExtra::pack_rows("Revenue", start_row = 1, 
                        end_row = incomeLength )  %>%
    kableExtra::row_spec(row = incomeLength,
                       bold = FALSE, 
                   underline = TRUE)  %>%
    kableExtra::add_indent(positions = incomeLength)  %>%
    kableExtra::pack_rows("Expenses", 
                        start_row = incomeLength+1, 
                        end_row = (incomeLength+1)+
                          (costLength-1)) %>%
    kableExtra::row_spec(row = (incomeLength+1)+
                          (costLength-1), bold = FALSE, 
                       underline = TRUE, )  %>%
    kableExtra::add_indent(positions = (incomeLength+1)+
                          (costLength-1))  %>%
    kableExtra::pack_rows("", 
                        start_row = nrow(mytablePeriod), 
                        end_row = nrow(mytablePeriod)) %>%
    kableExtra::row_spec(row = nrow(mytablePeriod), bold = TRUE, 
                       underline = TRUE)
}

   incomeDataPeriods <- homeBudget::quarterly_income(homeBudget::incomeExample,
                                                     qrt_int)

  # load data
  costData <- data.table::copy(homeBudget::creditCardExample)

  costData[ , ':='(category = homeBudget::addCategories(Description = Description,
                              myCategories = homeBudget::categoryPatternsExample))]

  # add information about the quarter in which the cost transaction occured
  # convert date to POSIXct to match the type in timePeriods
  costData[ , ':='(PostingDate = lubridate::as_datetime(PostingDate))]
  costData[ , ':='(dummy = PostingDate)]
  data.table::setkey(costData, PostingDate, dummy)
  data.table::setkey(timePeriods, interval_start, interval_end)

  costDataPeriods <- data.table::foverlaps(x = costData, 
                                           y = timePeriods, 
                                           nomatch = 0L)
  costData[ , ':='(dummy = NULL)]

  # Summation per quarter
  costDataSum <- data.table::rbindlist(l = list(costDataPeriods[ , 
                                                          .(Amount = sum(Amount)), 
                                              by = c("category",
                                                     "interval_start",
                                                     "interval_end", 
                                                     "period")],
                                    costDataPeriods[ , .(category = "Subtotal",
                                                  Amount = sum(Amount)),
                                              by = c("interval_start",
                                                     "interval_end", 
                                                     "period")]),
                                    use.names = TRUE, fill = TRUE)

  incomeDataSum <- data.table::rbindlist(l = list(
    incomeDataPeriods[ , .(Amount = sum(Amount)), 
                by = c("category",
                       "interval_start",
                       "interval_end", 
                       "period")],
    incomeDataPeriods[ , .(category = "Subtotal", Amount = sum(Amount)), 
                by = c("interval_start",
                       "interval_end", 
                       "period")]),
                                    use.names = TRUE, fill = TRUE)
  # stack income and cost to calculate net profit
  # -- set the sign of the Amount in cost negative to reflect losses.
  stackSubtotals <- 
    data.table::rbindlist(l = list(incomeDataSum[category == "Subtotal"],
                                      costDataSum[category == "Subtotal"
                                                  ][ , c(list(Amount = -1*Amount),
                                                           .SD),
                               .SDcols = setdiff(names(costDataSum),                                                                          "Amount")]),
                               use.names = TRUE, fill = TRUE)

  netProfit <- stackSubtotals[, .(category = "Net Profit", 
                                  Amount = sum(Amount)),
                              by = c("interval_start",
                                     "interval_end", 
                                     "period")]

  # order from smallest to largest so that "Subtotal" is the final entry
  # per quarter
  data.table::setorder(x = costDataSum, 
                       interval_start, interval_end, period,
                       Amount
                       )
  data.table::setorder(x = incomeDataSum, 
                       interval_start, interval_end, period,
                       Amount
                       )
 mytable <- data.table::rbindlist(l = list(incomeDataSum, 
                                 costDataSum,
                                 netProfit), use.names = TRUE)
  mytable[ , ':='( Amount = scales::dollar_format(negative_parens = TRUE
                                                )(mytable$Amount))]