R: manipulate date column and correct Non-NYSE trading date to the closest trading date-CodePudding

I have a set of stock and event announcement date (showdate) data, and I was looking for the price of each stock around the date in my data.

The idea is I want to check for /-1 day (testdate) prices to see if there is any price changes. So

df%>%
 mutate(testdate=ymd(showdate) days(1))

But when I join table to search for the price information by testdate, the issue is the testdate after the /-1 day manipulation might not be a trading day for NYSE, it could be a weekend, a holiday or the stock was delisted at that date.

Question, how can I manipulate the showdate column /-1 that the most correct date will be retained, whereas if the calculated date lands on a weekend or non-trading day, it gets changed to the previous closest date. For example, if the 1 day is a Sunday for one particular row/stock, I want the date changed to the Friday.

I dont mind do the 1 day first, then do the -1 day in the next batch. The idea will be expanded to investigate 20, -20 days etc....

example set of showdate for you:

> dput(t)
structure(c(18857, 18368, 17487, 17248, 16934, 17081, 17000, 
16994, 16993, 16917, 16910, 18822, 18456, 18194, 16959, 17805, 
17757, 17511, 17178, 18883, 18858, 18842, 18837, 18836, 18835, 
18831, 18821, 18815, 18814, 18808, 18800, 18795, 18792, 18773, 
18752, 18745, 18744, 18740, 18738, 18731, 18722, 18717, 18662, 
18661, 18659, 18649, 18648, 18647, 18646, 18642, 18618, 18611, 
18597, 18596, 18589, 18577, 18576, 18575, 18570, 18565, 18562, 
18561, 18558, 18556, 18555, 18548, 18547, 18542, 18528, 18519, 
18514, 18498, 18494, 18492, 18486, 18480, 18473, 18472, 18470, 
18466), class = c("IDate", "Date"))

CodePudding user response：

You can use the RQuantLib library for this. Run install.packages("RQuantLib") to install it, and then you can try this:

library(RQuantLib)
library(dplyr)
library(lubridate)

showdate <- structure(c(18857, 18368, 17487, 17248, 16934, 17081, 17000, 
            16994, 16993, 16917, 16910, 18822, 18456, 18194, 16959, 17805, 
            17757, 17511, 17178, 18883, 18858, 18842, 18837, 18836, 18835, 
            18831, 18821, 18815, 18814, 18808, 18800, 18795, 18792, 18773, 
            18752, 18745, 18744, 18740, 18738, 18731, 18722, 18717, 18662, 
            18661, 18659, 18649, 18648, 18647, 18646, 18642, 18618, 18611, 
            18597, 18596, 18589, 18577, 18576, 18575, 18570, 18565, 18562, 
            18561, 18558, 18556, 18555, 18548, 18547, 18542, 18528, 18519, 
            18514, 18498, 18494, 18492, 18486, 18480, 18473, 18472, 18470, 
            18466), class = c("IDate", "Date"))

df <- tibble(my_date = showdate) %>% 
  mutate(testdate = adjust(
    calendar = "UnitedStates/NYSE",
    dates = ymd(showdate) days(1)
  ))

This gives a data frame with the column testdate that is either the following day, or if this falls on a non-trading day, the following trading day. For example 2017-11-18 was a Saturday, so this is moved to 2017-11-20:

# A tibble: 80 x 2
   my_date    testdate  
   <date>     <date>    
 1 2021-08-18 2021-08-19
 2 2020-04-16 2020-04-17
 3 2017-11-17 2017-11-20
 4 2017-03-23 2017-03-24
 5 2016-05-13 2016-05-16
 6 2016-10-07 2016-10-10
 7 2016-07-18 2016-07-19
 8 2016-07-12 2016-07-13
 9 2016-07-11 2016-07-12
10 2016-04-26 2016-04-27
# ... with 70 more rows