%Off High that keeps changing

[tugga82]

Hello everyone,
I am a novice in R, so wasn't sure where to post this exactly but I still wanted to pose this question so one of you might have an answer. Here is what I am trying to do:

• I have the stock price data for any ticker, say for example, AAPL for the past 10 years, daily data with these columns - date, open, high, low, close, volume.
• I want to include a column to this dataset, which calculates the %Off High = (close/Max(high) - 1)*100
  The catch here is the high is reset every time as you go row by row. To explain, in the first row, the calculation is straight forward, when I go to the second row, i.e. the next day, the high will be the max high of those 2 days in the calculation, and if I go to the 10th day for the calculation, the high will be the max of those 10 days.
  In the end, I'd like to chart this daily %offHigh column data.
  Anything you can do to help will be appreciated.
  Thanks in advance.

[FJCC]

I would use the cummax() function. The example below is not exactly like your data but I think you will get the idea.

set.seed(1)
DF <- data.frame(High = c(runif(10,20,40)))
DF
#>        High
#> 1  25.31017
#> 2  27.44248
#> 3  31.45707
#> 4  38.16416
#> 5  24.03364
#> 6  37.96779
#> 7  38.89351
#> 8  33.21596
#> 9  32.58228
#> 10 21.23573
library(dplyr)
DF <- DF %>% mutate(CumMax = cummax(High), PercOffHigh = (CumMax - High)/CumMax*100)
DF
#>        High   CumMax PercOffHigh
#> 1  25.31017 25.31017   0.0000000
#> 2  27.44248 27.44248   0.0000000
#> 3  31.45707 31.45707   0.0000000
#> 4  38.16416 38.16416   0.0000000
#> 5  24.03364 38.16416  37.0256249
#> 6  37.96779 38.16416   0.5145197
#> 7  38.89351 38.89351   0.0000000
#> 8  33.21596 38.89351  14.5976802
#> 9  32.58228 38.89351  16.2269367
#> 10 21.23573 38.89351  45.4003305

^{Created on 2020-04-11 by the reprex package (v0.3.0)}

[tugga82]

wow. That works like a charm when I tested for a sample data frame. Thanks a ton. I shall try incorporating this with one of these finance packages say quantmod and see if I get this in.