R/load_cc_data.R

In meerawhy/financeTracker:

path_to_cc_excel <- "/Users/meerapatel/Desktop/Personal/MASTER Credit Card.xlsx"
data <- read_entire_excel(path_to_excel)

##Making sure the DATE columns are formatted correctly
for (i in 1:length(data)) {
        if ("DATE" %in% colnames(data[[i]])) {
                output <-
                        data[[i]] %>%
                        dplyr::mutate(DATE = lubridate::ymd(DATE))

                if (any(is.na(output$DATE))) {
                        typewriteR::tell_me("Please QA this column with mirCat::qa_date_column.")
                        typewriteR::stop_and_enter()
                }
        }
}


##Imputing spend data if needed
entry_date_as_df <-
        data$SPEND %>%
        dplyr::select(DATE) %>%
        dplyr::distinct()

cc_ids <- unique(data$CREDIT_CARD$CC_ID)
output <- list()
for (i in 1:length(cc_ids)) {
        output[[i]] <-
                entry_date_as_df %>%
                dplyr::left_join(data$SPEND %>%
                                         dplyr::filter(CC_ID == cc_ids[i]),
                                 by = "DATE") %>%
                dplyr::mutate_all(caterpillaR::carry_forward)
}
output <- dplyr::bind_rows(output)

if (nrow(dplyr::setdiff(output, data$SPEND))) {
        typewriteR::tell_me("SPEND tab needs to be updatd with imputed carry overs:")
        print(dplyr::setdiff(output, data$SPEND))
        typewriteR::tell_me("MASTER will be overwritten with the addition of these new records.")
        typewriteR::stop_and_enter()
        data$SPEND <- output
        openxlsx::write.xlsx(data, file = path_to_excel)
        rm(output)
} else {
        rm(output)
}

##Imputing limit data if needed
entry_date_as_df <-
        data$LIMIT %>%
        dplyr::select(DATE) %>%
        dplyr::distinct()

output <- list()
for (i in 1:length(cc_ids)) {
        output[[i]] <-
                entry_date_as_df %>%
                dplyr::left_join(data$LIMIT %>%
                                         dplyr::filter(CC_ID == cc_ids[i]),
                                 by = "DATE") %>%
                dplyr::mutate_all(caterpillaR::carry_forward)
}
output <- dplyr::bind_rows(output)

if (nrow(dplyr::setdiff(output, data$LIMIT))) {
        typewriteR::tell_me("LIMIT tab needs to be updatd with imputed carry overs:")
        print(dplyr::setdiff(output, data$LIMIT))
        typewriteR::tell_me("MASTER will be overwritten with the addition of these new records.")
        typewriteR::stop_and_enter()
        data$LIMIT <- output
        openxlsx::write.xlsx(data, file = path_to_excel)
        rm(output)
} else {
        rm(output)
}

limit_changes_df <-
data$LIMIT %>%
        dplyr::group_by(DATE) %>%
        dplyr::mutate(LIMIT = as.double(LIMIT)) %>%
        dplyr::summarise(TOTAL_LIMIT = sum(LIMIT)) %>%
        dplyr::group_by(TOTAL_LIMIT) %>%
        dplyr::filter(row_number() == 1) %>%
        dplyr::ungroup()

spend_trend_df <-
        data$SPEND %>%
        dplyr::mutate(DATE = as.Date(DATE)) %>%
        dplyr::mutate(BALANCE = as.double(BALANCE)) %>%
        dplyr::group_by(DATE) %>%
        dplyr::summarise(TOTAL_DEBT = sum(BALANCE)) %>%
        dplyr::ungroup() %>%
        dplyr::mutate(TOTAL_LIMIT = "")

for (i in 1:nrow(limit_changes_df)) {
        if (i == 1) {
                cutoff_date <- limit_changes_df$DATE[i + 1]
                total_limit <- limit_changes_df$TOTAL_LIMIT[i]

                spend_trend_df <-
                        spend_trend_df %>%
                        dplyr::mutate(TOTAL_LIMIT = ifelse(DATE < cutoff_date, total_limit, TOTAL_LIMIT))
        } else if (i == nrow(limit_changes_df)) {
                cutoff_date <- limit_changes_df$DATE[i]
                total_limit <- limit_changes_df$TOTAL_LIMIT[i]
                spend_trend_df <-
                        spend_trend_df %>%
                        dplyr::mutate(TOTAL_LIMIT = ifelse(DATE > cutoff_date, total_limit, TOTAL_LIMIT))
        } else {
                start_date <- limit_changes_df$DATE[i]
                cutoff_date <- limit_changes_df$DATE[i+1]

                total_limit = limit_changes_df$TOTAL_LIMIT[i]
                spend_trend_df <-
                        spend_trend_df %>%
                        dplyr::mutate(TOTAL_LIMIT = ifelse((DATE >= start_date) & (DATE <= cutoff_date),
                                                           total_limit,
                                                           TOTAL_LIMIT))
        }
}

to_plot <-
spend_trend_df %>%
        dplyr::mutate(CREDIT_UTIL_PERCENT = round(100*(TOTAL_DEBT/(as.double(TOTAL_LIMIT)))))

dplyr::left_join(
data$APR %>%
        somersaulteR::filter_most_recent_obs_by_group(CC_ID,
                                                      index_time_col = DATE,
                                                      time_format = "date") %>%
        dplyr::select(-DATE),
data$SPEND %>%
        somersaulteR::filter_most_recent_obs_by_group(CC_ID,
                                                      index_time_col = DATE,
                                                      time_format = "date") %>%
        dplyr::select(-DATE)
) %>%
        inner_join(data$CREDIT_CARD) %>%
        dplyr::mutate_at(vars(APR, BALANCE), as.double) %>%
        dplyr::mutate(ESTIMATED_INTEREST = BALANCE*((APR/100)/365)*BILLING_CYCLE_DAYS)



ggplot(data = to_plot,
       aes(x = DATE, y = CREDIT_UTIL_PERCENT)) +
        geom_smooth()

#Average Daily Balance = Total Daily Balances (Principal + Previous Day’s Interest + New Purchases) / Number of Days in the Billing Cycle

#Daily Periodic Rate = APR /365

#Interest Charges = (Average Daily Balance * Daily Periodic Rate) * Number of Days in the Billing Cycle

test %>%
        dplyr::mutate_all(caterpillaR::carry_forward)

load_cc_data <-
        function(path_to_excel) {
                data <- read_entire_excel(path_to_excel)
        }