0

I have a dataframe of financial data. I am trying to make a more accurate representation of current balance from it. The issue is the data has a "balance" that is only good at the month end. I need to adjust the balance on a daily basis off of that days transactions.

In order to get the start balance, I created a variable that is the lagged value of ending balance. Then I made a variable, calculated balance to get the "correct" balance But since the start balance was already made, it keeps going back to square 1. Example shown below:

| Date       Name    Start Balance  Adjustment  End Balance   Calculated Balance |  |
+--------------------------------------------------------------------------------+--+
| 6/30/2020  X       80             20          100           100                |  |
| 7/1/2020   X       100            10          100           110                |  |
| 7/2/2020   X       100            10          100           110                |  |
+--------------------------------------------------------------------------------+--+

data <- tibble(
  Date = c("2020-06-29", "2020-06-30", "2020-07-01", "2020-07-02"),
  Name = c("X", "X", "X", "X"),
  Start_Balance = c(80, 80, 100, 100),
  Adjustment = c(0, 20, 10, 10),
  End_Balance = c(80, 100, 100, 100),
  Calc_Balance = c(80, 100, 110, 110),
  What_I_Need = c(80, 100, 110, 120)
)

What would be the correct way to get this to work

Ethan James
  • 41
  • 10
  • 2
    Please provide more easily consumed data as well as your expected output. By "more easily", I mean this: often we can highlight/copy a section of a table and use `read.table` to convert into a `data.frame`, but embedded spaces and lines (`|`, `+`, `--`) disrupt that process. The gold-standards are either `dput(x)` (where `x` is a small *sample* of your real data, sampling rows and/or columns); or `data.frame(...)`, building the data programmatically. – r2evans Jul 30 '20 at 21:07
  • `read.csv(text=" ...")` also works for readable/reproducible examples. https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – Ben Bolker Jul 30 '20 at 21:26
  • Edited to include dataframe – Ethan James Jul 30 '20 at 21:38
  • `Start_Balance + cumsum(Adjustment)` ? – Ben Bolker Jul 30 '20 at 21:45
  • ben, sometimes the "start balance" and "End Balance" would change but so that wont work – Ethan James Jul 30 '20 at 21:49
  • hi Ethan, it is not clear which are the fixed inputs and which are the variables that you can tweak to get the What_I_Need column – chinsoon12 Jul 30 '20 at 23:31

0 Answers0