CODEWITHSUNDEEP
rdplyr
AaronT86
AaronT86

Reputation: 53

Setting Initial Value for Stock and Rolling Multiple with dplyr::mutate

Beginning with this dataset:

df <- data.frame(
  year = 2007,
  month = c('Jan', 'Feb', 'Mar', 'April'),
  percent_change = c(0.0314, 0.0073, 0.0135, -0.0144),
  to_multiply = c(1.0314, 1.0073, 1.0135, .9856)
)

I would like to produce the following dataset.

year month percent_change to_multiply dollar_value
2007   Jan         0.0314      1.0314     103.1400
2007   Feb         0.0073      1.0073     103.8929
2007   Mar         0.0135      1.0135     105.2955
2007 April        -0.0144      0.9856     103.7792

I am wondering how to programmatically assign an initial value and create a new column displaying the value of a stock after each month. This is easy to do in a spreadsheet, but I'd like to stay away from that so I can rapidly chart different start dates and/or allocations using the same initial start value.

I have tried mutate with a lag wrapper, but I couldn't get that to work. I looked at RCCPRoll, but also couldn't make that work.

Upvotes: 0

Views: 56

Answers (1)

geneorama
geneorama

Reputation: 3720

The thing that would help you is cumprod, but can I suggest using data.table?

dt <- data.table(year = 2007,
                 month = c('Jan', 'Feb', 'Mar', 'April'),
                 percent_change = c(0.0314, 0.0073, 0.0135, -0.0144),
                 to_multiply = c(1.0314, 1.0073, 1.0135, .9856))
dt[ , newvalue := cumprod(to_multiply) * 100]
dt
#    year month percent_change to_multiply newvalue
# 1: 2007   Jan         0.0314      1.0314 103.1400
# 2: 2007   Feb         0.0073      1.0073 103.8929
# 3: 2007   Mar         0.0135      1.0135 105.2955
# 4: 2007 April        -0.0144      0.9856 103.7792

Actually, a shorter version:

dt <- data.table(year = 2007,
                 month = c('Jan', 'Feb', 'Mar', 'April'),
                 percent_change = c(0.0314, 0.0073, 0.0135, -0.0144))
dt[ , newvalue := 100 * cumprod(1 + percent_change)]
dt
#    year month percent_change newvalue
# 1: 2007   Jan         0.0314 103.1400
# 2: 2007   Feb         0.0073 103.8929
# 3: 2007   Mar         0.0135 105.2955
# 4: 2007 April        -0.0144 103.7792

Upvotes: 1

Related Questions