r -- finding difference between business days

Question

I have several years of data (only for business days (no weekends or holidays)) in an [r] data frame and would like to find the difference between the data on the 2nd and 5th business day of each month. So the solution needs to go thru the list, determine the 2nd and 5th business day, get the data and the full date for the corresponding dates and then find the difference.

the data looks like:

1/19/1990  1.22

1/20/1990  1.25

1/23/1990  1.26   ## (Gap in date is weekend)

...

2/1/1990   1.34

2/2/1990   1.36

2/5/1990   1.22   ## (Gap in date is weekend)

I have tried using dateTime() but it doesn't handicap for weekends and holidays. Any suggestions would be appreciated, thanks.

Answer 1

The basic Date type works for calendar days, but not for business days. You need extra logic to take care of business days. I am aware of two efforts:

1. the timeDate package which is part of rMetrics has a number of calendars

2. my RQuantLib package can do so too by relying in the logic from QuantLib

Here is just two examples from RQuantLib, there are a number of related other functions:

R>        from <- as.Date("2009-04-07")
R>        to <-as.Date("2009-04-14")
R>        getHolidayList("UnitedStates", from, to)
NULL
R>        to <- as.Date("2009-10-7")
R>        getHolidayList("UnitedStates", from, to)
[1] "2009-05-25" "2009-07-03" "2009-09-07"
R>     

and

R>        from <- as.Date("2009-04-07")
R>        to<-as.Date("2009-04-14")
R>        businessDaysBetween("UnitedStates", from, to)
[1] 5
R> 

Answer 2

I assume that by the 2nd and 5th business day you mean that 2nd and 5th day of data that is actually present in the data for each month. If that is the question then its as follows. We read in the data and convert the first column to "Date" class. Then we aggregate the data by month taking the required difference.

Lines <- "1/19/1990 1.22
1/20/1990 1.25
1/23/1990 1.26 
1/24/1990 1.26 
1/25/1990 1.26 
1/26/1990 1.26 
2/1/1990 1.34
2/2/1990 1.36
2/5/1990 1.22 
2/6/1990 1.22 
2/7/1990 1.22 
2/8/1990 1.22"

DF <- read.table(text = Lines, col.names = c("Date", "Value"))
DF$Date <- as.Date(DF$Date, "%m/%d/%Y")
aggregate(DF$Value, list(ym = format(DF$Date, "%Y-%m")), 
   function(x) if (length(x) >= 5) x[5] - x[2] else NA)

Using zoo and chron it can be done entirely via read.zoo:

library(zoo)
library(chron)
read.zoo(text = Lines, FUN = chron, FUN2 = as.yearmon, 
  aggregate =  function(x) if (length(x) >= 5) x[5] - x[2] else NA)

Update Since this was first written the text= argument to read.table and read.zoo was added in R and the answer has been updated to use this.