CODEWITHSUNDEEP
r
Toolbox
Toolbox

Reputation: 2493

R - Calculate rolling financial balance

I am creating a financial report structure in R. What I am missing is the last part which is where the dataframe needs to be injected with what I call "rolling balance".

Primarily I would want to solve this with base R, avoiding adding yet another R package. If possible the calculation should be utlizing vectorized calculation instead of a loop.

Question: How do I inject the result in a cell, using the cell above and to the left for input to calculate the result.

Here is my R script:

############
# Create df1
############
'date'        <- '2018-10-01'
'product'     <- 0 
'bought'      <- 0
'sold'        <- 0
'profit.loss' <- 0
'comission'   <- 0
'result'      <- 0
'balance'     <- 0

df1 <- data.frame(
  date,
  product,
  bought,
  sold,
  profit.loss,
  comission,
  result,
  balance
    , stringsAsFactors=FALSE)

# Inject initial deposit
df1[1,8] <- 1000

##########################
# Create df2 (copying df1)
##########################
df2 <- df1

# Clean 
df2 <- df2[-c(1), ]     # Removing row 1.
df2[nrow(df2)+3,] <- NA # Create 3 rows.
df2[is.na(df2)] <- 0    # Change NA to zero.

# Populate the dataframe
df2$date      <- c('2018-01-01', '2018-01-02', '2018-01-03')
df2$product   <- c('prod-1', 'prod-2', 'prod-3')
df2$bought    <- c(100, 200, 300)
df2$sold      <- c(210, 160, 300)
df2$comission <- c(10, 10, 10)

# Merge both dataframes
df3 <- rbind(df1, df2)

#######
# Calcs
#######
df3$profit.loss <- df3$sold - df3$bought # calc profit.loss.
df3$result <- df3$profit.loss - df3$comission # calc result.

# [Xxx]# Balance <- Note! This is the specific calc connected to my question.


enter code here

Result after running the R script: 

        date product bought sold profit.loss comission result balance
1 2018-10-01       0      0    0           0         0      0    1000
2 2018-01-01  prod-1    100  210         110        10    100       0
3 2018-01-02  prod-2    200  160         -40        10    -50       0
4 2018-01-03  prod-3    300  300           0        10    -10       0

This is how the calculation of "rolling balance" should behave:

   [Result] [Balance]

row-1: [No value]  [initial capital: 1000]
row-2: [100] [900 / Take value of balance, one row above, subscract left result]   
row-3: [-50] [850 / Take value of balance, one row above, subscract left result]
row-4: [follows the same principal as row-2 and row-3]

Upvotes: 0

Views: 163

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269644

One would refer to this as cumulative rather than rolling at least as it is commonly used with R.

If the initial balance is the scalar b and the results are in vector result then b + cumsum(result) is a vector the same length as result which gives the initial balance plus the cumulative sum of the results. 

b <- 10
result <- c(0, -1, 3)
b + cumsum(result)
## [1] 10  9 12

# same
c(b + result[1], b + result[1] + result[2], b + result[1] + result[2] + result[3])
## [1] 10  9 12

Upvotes: 2

Related Questions