---
title: "Personal Financial Status"
author: "Shibo Feng" 
output: 
  flexdashboard::flex_dashboard:
    social: menu
    source_code: embed
    orientation: rows
    storyboard: true
---

```{r setup, include=FALSE}
library(ggplot2)
library(readxl)
library(plotly)
library(dplyr)
library(tidyr)
library(flexdashboard)
library(lubridate)
ANLY_512_Data <- read_excel("C:/Users/efeng/Desktop/ANLY 512 Data.xlsx")

# Data Setup
dataset <- ANLY_512_Data
dataset$Date <- as.Date(dataset$DateValue, origin = "1899-12-30")
dataset$Category <- gsub("Restaurants","Restaurant",dataset$Category)
colnames(dataset)[3] <-  "Amount"

# Group by Category
AmountbyCategory=aggregate(dataset$Amount,list(Category=dataset$Category),sum,na.rm=TRUE)
colnames(AmountbyCategory)[2] <- "Amount"

# Group by Month
dataset$Month <- format(as.POSIXct(dataset$Date),"%Y%m")
dataset$Month <- gsub("201612","201701",dataset$Month)
dataset$Month <- as.numeric(dataset$Month)
dataset$Month <- factor(dataset$Month)
AmountbyMonth <- aggregate(dataset$Amount,list(Month=dataset$Month),sum,na.rm=TRUE)
colnames(AmountbyMonth)[2] <- "Amount"

# Group by Month & Category
AmountbyMC <- aggregate(dataset$Amount,list(Category=dataset$Category,Month=dataset$Month),sum,na.rm=TRUE)
colnames(AmountbyMC)[3] <- "Amount"
test1 <- data.frame("Category"=c("Car Repaire/Gas/Transportation"),"Month"=201701:201712, "Amount"=0)
test2 <- data.frame("Category"=c("Entertainment"),"Month"=201701:201712, "Amount"=0)
test3 <- data.frame("Category"=c("Fitness"),"Month"=201701:201712, "Amount"=0)
test4 <- data.frame("Category"=c("Health Care"),"Month"=201701:201712, "Amount"=0)
test5 <- data.frame("Category"=c("Merchandise & Supplies"),"Month"=201701:201712, "Amount"=0)
test6 <- data.frame("Category"=c("Other"),"Month"=201701:201712, "Amount"=0)
test7 <- data.frame("Category"=c("Restaurant"),"Month"=201701:201712, "Amount"=0)
test8 <- data.frame("Category"=c("Travel"),"Month"=201701:201712, "Amount"=0)
test9 <- data.frame("Category"=c("Utility"),"Month"=201701:201712, "Amount"=0)
test10 <- data.frame("Category"=c("Car Repaire/Gas/Transportation"),"Month"=201801:201806, "Amount"=0)
test11 <- data.frame("Category"=c("Entertainment"),"Month"=201801:201806, "Amount"=0)
test12 <- data.frame("Category"=c("Fitness"),"Month"=201801:201806, "Amount"=0)
test13 <- data.frame("Category"=c("Health Care"),"Month"=201801:201806, "Amount"=0)
test14 <- data.frame("Category"=c("Merchandise & Supplies"),"Month"=201801:201806, "Amount"=0)
test15 <- data.frame("Category"=c("Other"),"Month"=201801:201806, "Amount"=0)
test16 <- data.frame("Category"=c("Restaurant"),"Month"=201801:201806, "Amount"=0)
test17 <- data.frame("Category"=c("Travel"),"Month"=201801:201806, "Amount"=0)
test18 <- data.frame("Category"=c("Utility"),"Month"=201801:201806, "Amount"=0)
test <- rbind(test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11,test12,test13,test14,test15,test16,test17,test18)
test$Month=factor(test$Month)
new <- rbind(AmountbyMC,test)
new1 <- new[!duplicated(new[c(1,2)]),]
```

###Overview of My Finanical Life: 2017-2018

![Family Over Money](C:\Users\efeng\Desktop\family_over_money.png)

***
**INTRODUCTION:**

This report is designed to analysis and visualize my total spending for the past one and a half year. I would like to figure out what category of things that I spent the most and what's my spending behavior. Note that during this period of time, my parents came visit me for a month. I would like to know how their visit actually affected my spending, and how I responsed to that variation.  

**DATA ACQUISITION & MANIPULATION:**

I pulled out all of my credit and debit cards historic data from 01/01/2017-06/30/2018, put it together in Excel and imported into R to do the data manipulation. First of all, I corrected all the texts for Category to make it uniform. Next, I grouped my spending by month, by category etc. After all those, doing the visualization using different kinds of plots

**QUESTIONS:**

Below are the questions I'm going to figure out by differnt kinds of plot and visualization.

- What are the total spending by category?
- What are the total spending by month? What would happen to my total spending if my parents come visit me?
- What are my spending by category each month?
- How did I react to too much spending?
- What is the trending of my spending behavior?
- How much did I spend on merchandise & supplies compare to all other spendings?


### Question 1: What are the total spending by category?


```{r Pie Chart by Category}
p <- plot_ly(AmountbyCategory, labels = ~Category, values = ~Amount, type = 'pie') %>%
  layout(title = 'Spending by Category from Jan 2017 to Jun 2018',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
p
```

***

**1. What are the total spending by category?**
 
As we can see from the pie chart, the top 3 categories of my spending are Merchandise & Supplies, Travel and Entertainment, respectively. Health Care is the category that I spent my money the least, which is a good thing, that means I'm overall healthy. With Merchandise & Supplies and Travel only combined, it exceeds half of my spending, which supprised me a lot. I'm going to figure out what happened next.


### Question 2: What are the total spending by month? What would happen to my total spending if my parents come visit me?

```{r Bar Chart by Month}
q <- ggplot(AmountbyMonth, aes(x=Month, y=Amount))+
  geom_bar(stat="identity", width=.5, fill="tomato3")+ 
  labs(title="Spending by Month From Jan 2017 - Aug 2018", 
         subtitle="aaa", 
         caption="bbb")+
  theme(axis.text.x = element_text(angle=65, vjust=0.6))
ggplotly(q)
```

***

**2. What are the total spending by month? What would happen to my total spending if my parents come visit me?**
 
As we can see from the Bar Chart, I spent a lot around Jan 2017 and Dec 2017, which was the time Christmas and New Year's Day happened. That explained why I spent more. Then next peak of my spending was around Jun 2018, the time I had a vocation in LA. The biggest spending of all is around Aug 2017 and Sep 2017, which looked like almost an outlier to me at the first glance, then I remembered this was the time when my parents came visit me. I bought their flight at Aug and spent a lot on Merchandise on September. Well anyway, I didn't complain a bit, family over money !!!


### Question 3: What are my spending by category each month?

```{r Interactive Plot by Month}
m <- new1 %>%
  plot_ly(x = ~Category, y = ~Amount, frame = ~Month, type="bar", showlegend = F) %>%
  layout(title = "Spending by Month",
         xaxis = list(title = ""),
         yaxis = list(title = "Amount"))
m
```

***

**3.What are the spending by category each month?**

As you move the bottom frame month by month, this chart further comfirms my guess. On Sep 2017, I spent $6,200 on Merchandise only, which accounts for more than 1/3 of my total Merchandise spending for one and a half year. Other than that, all my spending thoughtout the other months are quite stable.


### Question 4: How did I react to too much spending?

```{r Accumulate Chart between Merchandise & Supplies and Entertainment}
accumulate_by <- function(dat, var) {
  var <- lazyeval::f_eval(var, dat)
  lvls <- plotly:::getLevels(var)
  dats <- lapply(seq_along(lvls), function(x) {
    cbind(dat[var %in% lvls[seq(1, x)], ], frame = lvls[[x]])
  })
  dplyr::bind_rows(dats)
}

try <- new1
try1 <- try %>% separate(Month, into = c("year", "month"), sep=4)
try1$time <- as.numeric(try1$year)+(as.numeric(try1$month)-1)/12

d <- try1 %>%
  filter(Category %in% c("Merchandise & Supplies", "Entertainment")) %>%
  accumulate_by(~time)

n <- d %>%
  plot_ly(
    x = ~time, 
    y = ~Amount,
    split = ~Category,
    frame = ~frame, 
    type = 'scatter',
    mode = 'lines', 
    line = list(simplyfy = F)
  ) %>% 
  layout(
    xaxis = list(
      title = "Date",
      zeroline = F
    ),
    yaxis = list(
      title = "Amount",
      zeroline = F
    )
  ) %>% 
  animation_opts(
    frame = 200, 
    transition = 0, 
    redraw = FALSE
  ) %>%
  animation_slider(
    hide = F
  ) %>%
  animation_button(
    x = 1, xanchor = "right", y = 0, yanchor = "bottom"
  )
n

```

***

**4. How did I react to too much spending?**

I consider myself a person who eagers for entertainments. I knew I spent a lot on entertaiments every month. This chart shows the spending comparison between entertainments and merchandise month by month in order to figure out how I responsed to too much spending. As you can see from time to time, whenever my spending on merchandise rise, my entertement spending drops, vice versa. On September 2017, I spent $6,200 on merchandise, in the mean time my entertainment dropped to all time low, almost nothing. At the same month, all other categories dropped to all time low compare to all other months, which means I did realize I spent a lot and responsed to it.


### Question 5: What is the trending of my spending behavior?

```{r Scaled Subplot}
new2 <- new1
new2 <- new2[order(new2$Category),]

l <- new2 %>%
  transform(id = as.integer(factor(Category))) %>%
  plot_ly(x = ~Month, y = ~Amount, color = ~Category, colors="Set1",
          yaxis = ~paste0("y", id)) %>%
  add_lines() %>%
  subplot(nrows = 3, shareX = TRUE)
l
```

***

**5. What is the trending of my spending behavior?**

I ploted all the categories of my spending seperately in one plot in order to figure out the trending of my spending behavior. From the plot, I can see my spending on the Car Repaire/Gas/Transportation, Restaurant, and fitness are all going up. With the exception of the special parents visit month, all other categories are more or less stable. In order to have a more stable and better financial life, I need to control my restaurant spending, try to cook at home more, maybe I need to get rid of the old car in order to reduce the car mantainess expense. More importantly, I need to prepare for my parents next visit, maybe book the flights earlier to get a better pricing and use financing for the big merchandise purchase if possible.


### Question 6: How much did I spend on merchandise & supplies compare to all other spendings?

```{r Horizontal Bar Chart}
compare <- subset(new1, Category == "Merchandise & Supplies")
compare$total_expense <- AmountbyMonth$Amount
compare$gap <- compare$total_expense-compare$Amount
colnames(compare)[3]="Merchandise_Supplies"
colnames(compare)[5]="Other_Expenses"

k <- plot_ly(compare, x = ~Merchandise_Supplies, y = ~Month, type = 'bar', orientation = 'h', name = 'Merchandise & Supplies',
             marker = list(color = 'brown1',
                           line = list(color = 'black',
                                       width = 2))) %>%
  add_trace(x = ~Other_Expenses, name = 'Other Expenses',
            marker = list(color = 'lightblue1',
                          line = list(color = 'black',
                                      width = 2))) %>%
  layout(barmode = 'stack',
         xaxis = list(title = "Amount"),
         yaxis = list(title ="Month"))
k
```

***

**6. How much did I spend on merchandise & supplies compare to all other spendings?**

When I first saw the plot, it really rang the bell. The spending of Merchandise & Supplies ate up my money a lot. I need to figure out a way to increase the quality of my life by cuting the spending of merchandise and use it to fitness and health care. Keep myself as an entertained guy.