xts to.weekly returns both Fridays and Mondays as the end of the week

Question

I don't seem to be able to get the to.weekly and endpoints (which is used by to.weekly) functions in xts to give me the correct end days of weeks for most types of date data. I've had this problem with both the CRAN and R-Forge versions of the xts package.

It seems to be similar but not identical to the problem discussed here: XTS to.weekly returns different weekly endpoints.

For the sample data I have, the to.weekly function Fridays and Mondays for different weeks with the default indexAt="endof", and Tuesdays with indexAt="startof".

I'm using daily returns of the S&P 500 index:

library(quantmod)
getSymbols("^GSPC", from="1961-12-15", to="1962-01-15", src="yahoo")

weekdays(index(to.weekly(GSPC))) # Fridays and mondays
[1] "Monday" "Friday" "Friday" "Monday" "Monday"

I've tried changing the timezone of the object from my timezone to UTC, my system timezone to UTC and my local timezone, and recreating the xts object using the original dates converted to POSIXct. None of my attempts have been successful.

The only way that I've found to get the behavior I expect from to.weekly is to create a vector of dates as character strings, and then convert them to POSIXct rather than Date to use as an index for a new xts object. Unfortunately I haven't been able to get this to work with my actual data.

dates <-
c("1961-12-15","1961-12-18","1961-12-19","1961-12-20","1961-12-21","1961-12-22",
  "1961-12-26","1961-12-27","1961-12-28","1961-12-29","1962-01-02","1962-01-03",
  "1962-01-04","1962-01-05","1962-01-08","1962-01-09","1962-01-10","1962-01-11",
  "1962-01-12","1962-01-15")
data <- rep(1, length(dates))
p <- xts(data, order.by=as.POSIXct(dates))
d <- xts(data, order.by=as.Date(dates))

# Last day in the week, as expected
weekdays(index(to.weekly(p)))
# [1] "Friday" "Friday" "Friday" "Friday" "Friday" "Monday"

# First day in the week, as expected
weekdays(index(to.weekly(p, indexAt="startof")))
# [1] "Friday"  "Monday"  "Tuesday" "Tuesday" "Monday"  "Monday" 

# Mix of first and last days, not expected
weekdays(index(to.weekly(d)))
# [1] "Monday" "Friday" "Friday" "Monday" "Monday"

Since using POSIXct values for the dates seemed to work with character values, I thought I would try it with the price data.

GSPCp <- xts(coredata(GSPC), order.by=as.POSIXct(index(GSPC)))
weekdays(index(to.weekly(GSPCp)))                # Not as expected
[1] "Monday" "Friday" "Friday" "Monday" "Monday"

I suspect that the problem is a timezone (and inexperience) issue, but I've exhausted all of the methods I could think of to get it to return the values from the end each week of in this data series.

I'm currently running xts_0.9.874.

------Edit------

As WaltS points out below, this appears to be a problem with the fact that POSIXct uses Unix time, which starts on 1970-01-01. Strange behavior probably should be expected for dates before this time.

I did some experimentation today by adding 17 years and 4 leap-days to shift the index to 1978-1979, which shares the same days of the week on the same dates as 1961-1962. Sure enough, to.weekly works just fine on the same data shifted to a point after 1970.

d <- GSPC
index(d) <- index(d)+365*17+4 # 1978-1979
weekdays(index(to.weekly(d)))
[1] "Friday" "Friday" "Friday" "Friday" "Friday" "Monday"

Since this seems to be an inherent quality of working with POSIX time, I don't consider this to be a problem with my usage of to.weekly or endpoints any longer, but rather a structural issue with the time format. I'll need to find a different method to identify the endpoints of weeks for dates prior to 1970.

Answer 1

I agree that the Quantmod data seems fine. It also seems that to.weekly() works with xts objects like GSPC. The problem you're having seems to to be that 1970-01-01 is used as the origin of POSIXct times. To better illustrate this, consider the examples

GSPC1970 <- getSymbols("^GSPC", from="1970-12-15", to="1971-03-19", src="yahoo", auto.assign=FALSE)
to.weekly(GSPC1970)
weekdays(index(to.weekly(GSPC1970)))
 [1] "Friday"   "Thursday" "Thursday" "Friday"   "Friday"   "Friday"   "Friday"   "Friday"   "Friday"   "Friday"   "Friday"   "Friday"  
[13] "Friday"   "Friday" 

where the output is as expected. In particular, taking a period crossing 1969 into 1970

GSPC1969 <- getSymbols("^GSPC", from="1969-11-15", to="1970-03-20", src="yahoo", auto.assign=FALSE)
to.weekly(GSPC1969)
weekdays(index(to.weekly(GSPC1969)))
 [1] "Monday" "Monday" "Monday" "Monday" "Monday" "Monday" "Friday" "Friday" "Friday" "Friday" "Friday" "Friday" "Friday" "Friday"
[15] "Friday" "Friday" "Friday" "Friday"

Mondays are returned as the ends of the weeks for the 1969 weeks while Fridays are given for the 1970 dates. The problem is likely with endpoints() for on = "weeks" and possibly other periods. Is it necessary for you to work with data prior to 1970?

----------- update --------------

I think the problem is more with endpoints() or a function it uses rather than something more basic. Anyway, the function to.weekly.df() below is an alternative to to.weekly() which converts the quantmod data from an xts object to a data frame with the date as a column. It also adds a column containing a week number counting weeks from 1970-01-01 and having Sunday as the first day of the week. It uses the week number to split the data frame into weeks and then sapply to get the summary data for each week and returns that as a data frame. There's also a small helper function to convert the xts data to a data.frame.

xts2df <- function(xts_data) data.frame(date=index(xts_data),coredata(cbind(xts_data, week=(as.numeric(index(xts_data))+5)%/%7)))

to.weekly.df <- function(xts_data) {
  df <- xts2df(xts_data)

  weekly <- t(sapply(split(df, df$week), 
                 function(x) c(date=tail(x$date,1), Open=x[1,2], High=max(x[,3]), 
                               Low=min(x[,4]), Close=tail(x[,5], 1),
                               Volume=sum(x[,6]), Adjusted=tail(x[,7],1) ) ) )
 weekly <- data.frame(date=as.Date(weekly[,1]), weekly[,-1])
 return(weekly)
}

This seems to give the same results as to.weekly() for post 1970-01-01 data and the correct results for periods before that. Let me know if you have any questions.