library('pacman') p_load('stringr') p_load('httr') p_load('crayon') p_load('plyr') p_load('colorout') p_load(kableExtra) p_load(devtools) p_load(htmlTable) p_load(knitr) p_load(formattable) p_load(dplyr) year=commandArgs(TRUE)[1] years = (2018:2018) alltime = data.frame() df <- read.csv("2018.csv", header=FALSE, sep=",", row.names=NULL) df = df[c(-2,-6,-7,-8)] colnames(df) <- c("dates", "description", "amount", "balance") df$dates = as.Date(df$dates, "%d/%m/%Y") df$amount = abs(as.numeric(df$amount)) df$balance = as.numeric(df$balance) df$category <- NA month = subset(df, format.Date(dates, "%m")==sprintf("%02d",12) & format.Date(dates, "%Y")==2018) month = month[ order(month$date , decreasing = FALSE ),] wage = round(sum(month[month$counterparty == "SCOTCH PARTN",]$amount),digits=0) balance = min(month$balance) file_list <- list.files(path=gsub(" ","",paste('./',2018,'/',sprintf("%02d",12),'/tagged/')), pattern="*.csv") df <- do.call("rbind", lapply(file_list, function(x) read.csv(paste('./',2018,'/',sprintf("%02d",12),'/tagged/', x, sep=''), stringsAsFactors = FALSE))) data <- data.frame( year = 2018, month = sprintf("%02d",12), rent = sum(df[df$category == "RENT",]$amount,na.rm=TRUE), food = round(sum(df[df$category == "FOOD",]$amount,na.rm=TRUE),digits=0), other = round(sum(df[df$category == "OTHER",]$amount,na.rm=TRUE),digits=0), cash = round(sum(df[df$category == "CASH",]$amount,na.rm=TRUE),digits=0), od = round(sum(df[df$category == "OVERDRAFT",]$amount,na.rm=TRUE),digits=0)) data$expenses = data$rent + data$food + data$other + data$cash data$net_wage = wage data$profit = data$net_wage - data$expenses data$balance = balance data$rentpct = round(data$rent / (data$net_wage/100),digits=0) data$spendpct = round(data$expenses /(data$net_wage/100),digits=0) alltime <- rbind(alltime,data) #print(alltime[seq(dim(alltime)[1],1),],row.names = FALSE) options(knitr.table.format = "html") alltime %>% mutate( profit = cell_spec(profit, color = ifelse(profit < 0, "red", "black")), expenses = color_tile("white", "red")(expenses), savings = color_tile("white", "white")(balance), ) %>% select(year,month,rent,food,other,expenses,net_wage,profit,balance) %>% kable(escape = F) %>% kable_styling("striped", full_width = F)