---
title: "Quantified Self: Personal Spending Dashboard"
author: "Thanh Ha Ho | ANLY 512"
date: "`r format(Sys.Date(), '%B %d, %Y')`"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: fill
    theme: yeti
    source_code: embed
---

<style>
.value-box .value,
.value-box .caption,
.value-box .icon {
  color: #0d3b66 !important;
}
</style>

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE, warning = FALSE, message = FALSE)

library(flexdashboard)
library(tidyverse)
library(lubridate)
library(plotly)
library(leaflet)
library(DT)
library(scales)

# ---------------------------------------------------------------
# 1. LOAD DATA
# ---------------------------------------------------------------
raw <- readxl::read_excel("Credit card transaction.xlsx")

df <- raw %>%
  rename(
    txn_date  = `Transaction Date`,
    post_date = `Posted Date`,
    card_no   = `Card No.`,
    description = Description,
    location  = Location,
    lat       = Latitude,
    lon       = Longitude,
    category  = Category,
    amount    = Amount
  )

# ---------------------------------------------------------------
# 2. STANDARDIZE LOCATION TEXT, THE "ONLINE" LABEL, AND FIX THE
#    (0,0) COORDINATE BUG
# ---------------------------------------------------------------
# A few rows used the literal text "0" in Location (and "0"/0 in
# Latitude/Longitude) instead of "Online" -- same meaning, different
# label. There's also inconsistent whitespace on some city names
# (" Boston, MA" vs "Boston, MA", "Malden, MA " vs "Malden, MA"),
# which would otherwise silently split one real city into two
# separate map markers. str_squish() fixes that before anything
# else runs. Standardizing "Online" first, THEN converting lat/lon
# to real NA (not the number zero) is essential: (0,0) is a real,
# valid coordinate in the middle of the Atlantic Ocean, so leaving
# it as literal 0 would silently put every online purchase on the
# map at the wrong place instead of correctly excluding them.
df <- df %>%
  mutate(
    location = str_squish(location),
    location = if_else(location == "0" | is.na(location) & str_detect(description, "E-COMMERCE|CLAUDE SUB"),
                        "Online", location),
    lat = suppressWarnings(as.numeric(lat)),
    lon = suppressWarnings(as.numeric(lon)),
    lat = if_else(location == "Online" | (lat == 0 & lon == 0), NA_real_, lat),
    lon = if_else(is.na(lat), NA_real_, lon)
  )

# ---------------------------------------------------------------
# 3. TAG TRANSACTION TYPE AND SPENDING TYPE
# ---------------------------------------------------------------
df <- df %>%
  mutate(
    transaction_type = case_when(
      category == "Payment/Credit" ~ "Payment",
      amount < 0                   ~ "Refund",
      TRUE                         ~ "Purchase"
    ),
    spend_type = case_when(
      category %in% c("Dining", "Entertainment", "Merchandise", "Other Travel", "Computer") ~ "Discretionary",
      category == "Payment/Credit" ~ NA_character_,
      TRUE ~ "Fixed / Necessary"
    )
  )

# ---------------------------------------------------------------
# 4. DATES
# ---------------------------------------------------------------
df <- df %>%
  mutate(
    txn_date  = as_date(txn_date),
    weekday   = wday(txn_date, label = TRUE, abbr = TRUE),
    is_weekend = weekday %in% c("Sat", "Sun"),
    month     = floor_date(txn_date, "month")
  )

purchases <- df %>% filter(transaction_type %in% c("Purchase", "Refund"))

n_missing_location <- sum(is.na(purchases$location) | purchases$location == "")
n_online <- sum(purchases$location == "Online", na.rm = TRUE)
n_mapped <- sum(!is.na(purchases$lat))

date_range <- range(df$txn_date)
```

# Overview

## Row {data-height=140}

### Transactions {.value-box}

```{r}
valueBox(nrow(df), icon = "fa-receipt", caption = "Total Transactions", color = "primary")
```

### Total Spent {.value-box}

```{r}
total_spent <- purchases %>% filter(transaction_type == "Purchase") %>% pull(amount) %>% sum()
valueBox(dollar(total_spent), icon = "fa-dollar-sign", caption = "Total Spent (Purchases)", color = "info")
```

### Date Range {.value-box}

```{r}
valueBox(paste(format(date_range[1], "%b %Y"), "-", format(date_range[2], "%b %Y")),
         icon = "fa-calendar", caption = "Date Range", color = "success")
```

### Cities Mapped {.value-box}

```{r}
valueBox(n_distinct(purchases$location[!is.na(purchases$lat)]),
         icon = "fa-map-marker-alt", caption = "Distinct Cities Mapped", color = "warning")
```

## Row {data-height=860}

### About this dashboard

This dashboard is a **Quantified Self (QS) project**: an analysis of
my own credit card spending over `r format(date_range[1], "%B %Y")`
through `r format(date_range[2], "%B %Y")`, covering
**`r nrow(df)` transactions**.

**Data collection.** The raw data came from my Credit Card CSV export, 
downloaded directly from the online
banking portal. The export only includes a `Description`, `Category`,
and split `Debit`/`Credit` columns -- no location field of any kind.
Since a geographic map was a project requirement and nothing in the
raw export was mappable, I manually reviewed the ~100 most frequent
merchants and added `Location`, `Latitude`, and `Longitude` columns
by hand, drawing on merchant descriptions that already named a town
(e.g. `MACYS BURLINGTON (MA)`) and my own knowledge of which specific
branch of each recurring merchant (BJ's, gas stations, etc.) I
actually use.

**Why this method, not an automated one.** An automated geocoder run
directly on merchant names would have geocoded chains like "BJ'S
WHOLESALE CLUB" to a generic corporate address rather than the
specific store I actually shop at, producing a map that looked
precise but was factually wrong. Manual tagging is slower but
produces a map that reflects where I actually was, which matters
more for a *personal* dataset than for a public one.

**Tools used.** R, `tidyverse` for cleaning, `plotly` for interactive
charts, `leaflet` for the map, and `flexdashboard` to assemble
everything into one HTML file, per the assignment's R Markdown
requirement.

**The four questions this dashboard answers:**

1. Does my spending shift between weekdays and weekends?
2. Which spending category is the most volatile month to month?
3. Where does my spending cluster geographically, and does it reveal
   travel outside my normal routine?
4. How has the balance between discretionary and fixed/necessary
   spending shifted over the seven months of data?

**Design choices.** Following the course's dashboarding-theory
material, each tab here is scoped to a single screen, and this
project counts as an *Analytical Dashboard* in that framework: it
shows data over time with comparative ability, and prioritizes
context over raw content. The map follows the course's map-design
material as well -- it uses scaled circle markers (graduated
symbols) rather than a choropleth, since this is point-location data
(specific merchants) rather than data tied to a polygon boundary like
a zip code or county.

# Data Quality

## Row {data-height=500}

### How the location gap was handled

There is no location field anywhere in the raw Capital One export.
The chart below shows how the `r nrow(purchases)` purchase/refund
transactions break down by whether a real location was available.

```{r}
loc_summary <- purchases %>%
  mutate(loc_status = case_when(
    location == "Online" ~ "Online",
    is.na(lat)           ~ "Not yet tagged",
    TRUE                  ~ "Real location tagged"
  )) %>%
  count(loc_status) %>%
  mutate(pct = round(100 * n / sum(n), 1),
         loc_status = factor(loc_status, levels = loc_status[order(n)]),
         bar_color = case_when(
           loc_status == "Real location tagged" ~ "#2980b9",
           loc_status == "Online" ~ "#95a5a6",
           TRUE ~ "#c0392b"
         ))

plot_ly(loc_summary,
        x = ~n, y = ~loc_status, type = "bar", orientation = "h",
        text = ~n, textposition = "outside", textfont = list(size = 14, color = "gray30"),
        marker = list(color = ~bar_color),
        hovertext = ~paste0(loc_status, ": ", n, " transactions (", pct, "%)"),
        hoverinfo = "text") %>%
  layout(title = "Location status by transaction",
         xaxis = list(title = "Transactions", range = c(0, max(loc_summary$n) * 1.2)),
         yaxis = list(title = ""),
         margin = list(t = 50))
```

### Data quality notes

```{r}
DT::datatable(
  data.frame(
    Issue = c("Online purchases initially coded as coordinate (0, 0)",
              "Inconsistent 'Online' labeling (literal '0' vs 'Online')",
              "Transactions with no location tagged yet"),
    Fix = c("Recoded to true NA and excluded from the map -- (0,0) is a real ocean coordinate, not a null value, so leaving it in would have shown a false cluster",
            "Standardized all online/no-location merchants to one consistent 'Online' label",
            paste0(n_missing_location, " transactions -- kept in every chart except the map, and excluded there rather than guessed"))
  ),
  options = list(dom = "t", paging = FALSE), rownames = FALSE
)
```

## Row {data-height=450}

### Transaction type breakdown

`Payment/Credit` rows are the monthly bill payment, not spending, and
are excluded from every spending chart in this dashboard.
`transaction_type` also separately flags **refunds** (a negative
amount inside a normal spending category, like a return) so they net
against that category rather than get miscounted as a purchase.

```{r}
DT::datatable(
  df %>% count(transaction_type, category, sort = TRUE) %>% rename(Count = n),
  options = list(pageLength = 6), rownames = FALSE
)
```

# Spending Patterns

## Row {data-height=480}

### Weekday vs. weekend spending

```{r}
wk <- purchases %>% filter(transaction_type == "Purchase")

p <- ggplot(wk, aes(x = if_else(is_weekend, "Weekend", "Weekday"), y = amount,
                     fill = if_else(is_weekend, "Weekend", "Weekday"),
                     text = paste0("$", round(amount, 2)))) +
  geom_boxplot(width = 0.35, alpha = 0.85, outlier.alpha = 0.4, linewidth = 0.6) +
  scale_y_log10(labels = dollar) +
  scale_x_discrete(expand = expansion(mult = c(0.6, 0.6))) +
  scale_fill_manual(values = c("Weekday" = "#2980b9", "Weekend" = "#e67e22")) +
  labs(x = NULL, y = "Amount (log scale)",
       title = "Weekend transactions skew higher",
       subtitle = "Log scale used so a few large one-off purchases don't flatten the boxes") +
  theme_minimal(base_size = 16) +
  theme(legend.position = "none",
        plot.title = element_text(size = 18),
        plot.subtitle = element_text(size = 12, color = "gray40"),
        axis.text = element_text(size = 13),
        panel.grid.minor = element_blank())

ggplotly(p, tooltip = "text", height = 420) %>%
  layout(margin = list(t = 70))
```

## Row {data-height=170}

### My take {data-width=100}

**My take:** my weekday and weekend spending look pretty different in
character. On weekends I tend to spend a bit more per transaction on
average -- the kind of thing that fits with slower, more deliberate
purchases like a real grocery run or eating out, rather than
grabbing something quick. Weekdays are the opposite: mostly small,
frequent purchases (coffee, gas, little things), but they're also
where my biggest one-off purchases show up. That's the part I didn't
expect -- I assumed a big purchase would land on a weekend when I
have more time, but the data says otherwise. It's probably just a
handful of specific events (like a BJ's membership renewal) landing
on a weekday by chance, not an actual pattern in how I shop.

## Row {data-height=480}

### Spending by category over time

```{r}
top5 <- wk %>% count(category, sort = TRUE) %>% slice_head(n = 5) %>% pull(category)

cat_month <- wk %>%
  filter(category %in% top5) %>%
  group_by(month, category) %>%
  summarise(total = sum(amount), .groups = "drop") %>%
  tidyr::complete(month, category, fill = list(total = 0))

p <- ggplot(cat_month, aes(x = month, y = total, color = category,
                            text = paste0(category, " - ", format(month, "%b %Y"), ": ", dollar(total)))) +
  geom_line(linewidth = 1.1) +
  geom_point(size = 2.2) +
  scale_y_continuous(labels = dollar) +
  scale_color_brewer(palette = "Set1") +
  labs(x = NULL, y = "Total Spent", color = NULL,
       title = "Top 5 categories, month over month") +
  theme_minimal(base_size = 12) +
  theme(legend.position = "bottom")

ggplotly(p, tooltip = "text", height = 380) %>%
  layout(legend = list(orientation = "h", y = -0.2))
```

### Category totals

```{r}
cat_totals <- wk %>%
  group_by(category) %>%
  summarise(total = sum(amount), n = n(), .groups = "drop") %>%
  arrange(desc(total))

p <- ggplot(cat_totals, aes(x = reorder(category, total), y = total,
                             text = paste0(category, ": ", dollar(total), " across ", n, " purchases"))) +
  geom_col(fill = "#2980b9") +
  coord_flip() +
  scale_y_continuous(labels = dollar) +
  labs(x = NULL, y = "Total Spent",
       title = paste0(cat_totals$category[1], " leads total spend")) +
  theme_minimal(base_size = 12)

ggplotly(p, tooltip = "text", height = 380)
```

## Row {data-height=170}

### My take on the trend

Merchandise is clearly my least predictable category -- it spikes
some months and nearly disappears in others, which tracks with it
being big-ticket, one-off purchases rather than something I buy on a
schedule. Groceries and Dining, by contrast, show up every single
month in a fairly narrow band, which makes sense since those are
genuinely routine.

### My take on the totals

I honestly expected Dining to be my #1 category before I built this
-- it's the one that feels the biggest day to day. Seeing Merchandise
and Groceries actually outrank it was a genuine surprise, and a good
reminder that gut feeling about my own spending isn't that reliable
without actually looking at the numbers.

# Discretionary vs. Fixed

## Row {data-height=650}

### How the discretionary/fixed balance has shifted month to month

Discretionary = Dining, Entertainment, Merchandise, Other Travel,
Computer. Fixed/Necessary = Groceries, Gas/Automotive, Phone/Cable,
Insurance, Internet, Health Care, Other Services, Library.

```{r}
comp <- wk %>%
  filter(!is.na(spend_type)) %>%
  group_by(month, spend_type) %>%
  summarise(total = sum(amount), .groups = "drop")

p <- ggplot(comp, aes(x = month, y = total, fill = spend_type,
                       text = paste0(spend_type, " - ", format(month, "%b %Y"), ": ", dollar(total)))) +
  geom_col(position = "fill") +
  scale_y_continuous(labels = percent) +
  scale_fill_manual(values = c("Discretionary" = "#e67e22", "Fixed / Necessary" = "#2980b9")) +
  labs(x = NULL, y = "Share of Monthly Spend", fill = NULL,
       title = "Discretionary spending as a share of the month, over time") +
  theme_minimal(base_size = 12)

ggplotly(p, tooltip = "text", height = 480)
```

## Row {data-height=250}

### My take

**My take:** what jumps out to me is how much this swings -- some
months I'm barely above 20% discretionary, other months I'm pushing
75%. February is the extreme case, and once I connected it to the
Airbnb and Uber charges that month, it stopped looking like a mystery
and started looking like a specific, explainable event rather than a
spending problem. If I only looked at my average discretionary
percentage across the whole 7 months, I'd have missed this entirely
-- the month-by-month view is what actually tells me something
useful for budgeting.

# Geographic Map

## Row {data-height=700}

### Where the spending happens

```{r}
map_data <- purchases %>%
  filter(!is.na(lat), transaction_type == "Purchase") %>%
  group_by(location, lat, lon) %>%
  summarise(total = sum(amount), n = n(), .groups = "drop")

pal <- colorNumeric(palette = "YlOrRd", domain = map_data$total)

leaflet(map_data) %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  addCircleMarkers(
    lng = ~lon, lat = ~lat,
    radius = ~rescale(n, to = c(6, 20)),
    color = ~pal(total),
    stroke = FALSE, fillOpacity = 0.85,
    popup = ~paste0("<b>", location, "</b><br>",
                     "Total spent: ", dollar(total), "<br>",
                     "Transactions: ", n)
  ) %>%
  addLegend("bottomright", pal = pal, values = ~total,
            title = "Total Spent ($)", opacity = 0.85) %>%
  setView(lng = -71.15, lat = 42.4, zoom = 10)
```

## Row {data-height=200}

### What the map shows

The map reflects three routine hubs, not random locations: **Waltham**
is home, the tight Boston-metro cluster (Medford, Boston, Malden,
Burlington, Cambridge) is everyday errands and commuting, and the
smaller **Leominster** cluster is spending near my office. The map
defaults to a zoomed-in view of this Massachusetts cluster since
that's where nearly all the mapped spending sits.

**Zoom out to see a fourth, separate cluster in central Pennsylvania**
(Harrisburg, Mechanicsburg) -- this is HU's trip to campus for the
executive class, visible as two separate visit
windows: **January 9-11** and **May 15-16**. The **"Other Travel"**
category also shows charges in February (Airbnb, an Uber trip) and
July (a hotel booking) that never appear on the map at all, since
they were booked online with no physical location to tag -- likely
tied to the same campus visits rather than unrelated travel. Reading
the map and the category data together tells a fuller, more accurate
story than either one alone.

# Conclusions & References

## Row

### Key findings {data-width=650}

- **Weekends have fewer transactions but higher-value ones.** The
  weekday/weekend box plot shows weekend purchases skew larger,
  consistent with less frequent but more deliberate weekend spending
  (bigger grocery runs, dining out) versus small daily weekday
  purchases (coffee, gas).
- **Groceries is now the single largest spending category** once
  separated out from general Merchandise, ahead of Dining and
  Merchandise -- a distinction that would have been invisible if
  Groceries purchases had stayed lumped into the generic
  "Merchandise" bucket. Merchandise still shows the most
  month-to-month volatility of the top categories, consistent with
  irregular big-ticket purchases rather than a steady recurring cost.
- **The map surfaces a pattern the category data alone would miss:
  three routine location hubs, not scattered spending.** Home
  (Waltham), office (Leominster), and university campus (Harrisburg,
  PA, visited January and May) each show up as their own distinct
  cluster. February's spike to 77% discretionary spending -- the
  highest of any month -- lines up with Airbnb and Uber charges that
  never appear on the map at all, since they were booked online;
  likely tied to a campus visit rather than unrelated travel. Neither
  chart alone tells the full story; together they do.
- **Discretionary spending's share of the month is not flat** --
  it swings from under 25% to over 75% depending on the month, which
  is a far more useful signal for budgeting than a single average
  would be.

**Need for further work.** A handful of transactions still don't
have a tagged location, and could be filled in for a fully complete
map. A longer data window (a full year instead of ~7 months) would
also make the month-to-month category volatility finding more
reliable, since 7 months is enough to spot a pattern but not enough
to rule out normal noise.

### References {data-width=350}

**Buckley, A. (2012).** Five principles of effective maps:
legibility, visual contrast, figure-ground organization, hierarchical
organization, and balance -- applied to the marker sizing and basemap
choice on the Geographic Map tab.

**Few, S.** On dashboards: *"A visual display of the most important
information needed to achieve one or more objectives, consolidated
and arranged on a single screen so the information can be monitored
at a glance."* The standard this dashboard's single-screen-per-tab
layout is built against.