Today I was checking to see if the balance on some accounts would be positive in the future given some transactions that I had planned. My bank does not provide this information (probably because they fear that not all relevant transactions are given as input and therefore the final balances will be incorrect).
Therefore I used the following R script that handles an input sheet as displayed here:
# packages used (but not loaded)
# library(magrittr)
# library(readxl)
# library(cellranger)
# library(dplyr)
# library(tidyr)
path = 'Rekeningen.xlsm'
sheet ='liquidity'
# columns to use for transaction and balance data
trans_cols = "A:D"
balance_cols = "F:I"
`%>%` = magrittr::`%>%`
# read expected transactions
transactions = readxl::read_excel(path,
sheet = sheet,
range = cellranger::cell_cols(trans_cols)) %>%
dplyr::mutate(date=as.Date(date))
# read starting balances on relevant accounts
saldi = readxl::read_excel(path,
sheet = sheet,
range = cellranger::cell_cols(balance_cols)) %>%
dplyr::mutate(date=as.Date(date)) %>%
dplyr::select(alias,date,balance)
# create credit transactions
mut_c = transactions %>%
dplyr::select(alias=to,date,balance=amount)
# create debet transactions
mut_d = transactions %>%
dplyr::mutate(balance=0-amount) %>%
dplyr::select(alias=from,date,balance)
# combine starting balances and transactions
saldi2 = rbind(saldi,mut_c,mut_d)
# create zero transactions for all combinations of accounts and dates
cuad = saldi2 %>% tidyr::expand(alias, date) %>%
dplyr::mutate(balance=0)
# combine with actual transactions and sum by account and date
# (we now have a transaction for each combination of account and date
# even when there is no actual transaction for that combination)
saldi3 = rbind(saldi2,cuad) %>%
dplyr::group_by(alias,date) %>%
dplyr::summarise(balance=sum(balance))
# determine the current balance for each date
# and create a column for each account (and remove 'exit' account)
saldi4 = saldi3 %>%
dplyr::mutate(balance=cumsum(balance)) %>%
tidyr::spread(alias,balance) %>%
dplyr::select(-exit)
print(saldi4)
# A tibble: 6 x 4
date account1 account2 account3
<date> <dbl> <dbl> <dbl>
1 2019-03-18 3 4 12
2 2019-03-26 3 10 6
3 2019-03-28 2 8 6
4 2019-03-30 0 8 6
5 2019-04-01 4 4 6
6 2019-04-28 4 2 6
Session Info
R version 3.6.0 (2019-04-26)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18362)
Matrix products: default
locale:
[1] LC_COLLATE=English_United States.1252
[2] LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C
[5] LC_TIME=English_United States.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] HOQCutil_0.1.10 jsonlite_1.6 glue_1.3.1 purrr_0.3.2
[5] xml2_1.2.2 ggspatial_1.0.3 ggplot2_3.2.1 sf_0.7-7
[9] dplyr_0.8.3 stringr_1.4.0 osmdata_0.1.1
loaded via a namespace (and not attached):
[1] tidyselect_0.2.5 xfun_0.8 lattice_0.20-38
[4] vctrs_0.2.0 colorspace_1.4-1 htmltools_0.3.6
[7] utf8_1.1.4 rlang_0.4.0 e1071_1.7-0
[10] pillar_1.4.2 withr_2.1.2 DBI_1.0.0
[13] sp_1.3-1 readxl_1.3.1 lifecycle_0.1.0
[16] plyr_1.8.4 cellranger_1.1.0 munsell_0.5.0
[19] blogdown_0.15 gtable_0.3.0 rvest_0.3.4
[22] evaluate_0.14 knitr_1.24 prettymapr_0.2.2
[25] curl_4.0 class_7.3-15 fansi_0.4.0
[28] highr_0.8 Rcpp_1.0.2 KernSmooth_2.23-15
[31] backports_1.1.4 scales_1.0.0 classInt_0.3-3
[34] captioner_2.2.3 fs_1.3.1 png_0.1-7
[37] digest_0.6.20 stringi_1.4.3 rosm_0.2.5
[40] grid_3.6.0 cli_1.1.0 rgdal_1.4-4
[43] tools_3.6.0 magrittr_1.5 lazyeval_0.2.1
[46] tibble_2.1.3 zeallot_0.1.0 tidyr_1.0.0
[49] crayon_1.3.4 pkgconfig_2.0.2 lubridate_1.7.4
[52] assertthat_0.2.1 rmarkdown_1.15 httr_1.4.1
[55] R6_2.4.0 units_0.6-2 compiler_3.6.0