Merge xts objects with different time intervals

Question

I download quotes from yahoo with quantmod, compute quarterly returns and want to merge the returns and quotes into one xts object. In this new object I would like to see only the date where I have both a return and a quarterly return (typically last day of quarter). What I see however is this:

Here is the code:

library(quantmod)

#Define stocks
tickers<-c("DBK.DE","DTE.DE")
stock<-tickers
StartDate<-'2011-6-25'
EndDate<-'2016-06-25'

PF <- getSymbols(tickers, src='yahoo', from=StartDate, to=EndDate)

# combine the adjusted close values in one (xts) object
dataset <- Ad(get(PF[1]))
for (i in 2:length(PF)) {
  dataset <- merge(dataset, Ad(get(PF[i])))
}

#Getting Quarterly Returns
QReturns<-as.xts(as.data.frame(lapply(dataset,quarterlyReturn)))

#Here is where I suspect the problem
Quarterly_Portfolio<-merge.xts(dataset,QReturns)

Any idea?

Answer 1

While FXQuantTrader's answer solves the problem, it does not correctly diagnose or address the root cause.

There is nothing wrong with merging two xts objects that have different index classes, because all xts objects store the index as POSIXct internally. For example:

d <- Sys.Date()
merge(date=xts(1,d), posixct=xts(2,as.POSIXct(d,tz="UTC")))
             date posixct
# 2016-07-09    1       2

The problem is because your two xts object have different timezones. Note that dataset has a UTC timezone, since a Date index cannot have a timezone.

R> str(dataset)
An ‘xts’ object on 2011-06-27/2016-06-24 containing:
  Data: num [1:1305, 1:2] 33.5 34.3 34.6 35.1 36 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:2] "DBK.DE.Adjusted" "DTE.DE.Adjusted"
  Indexed by objects of class: [Date] TZ: UTC
  xts Attributes:  
List of 2
 $ src    : chr "yahoo"
 $ updated: POSIXct[1:1], format: "2016-07-09 08:21:12"

But QReturns has a 'empty' TZ attribute (which means your local timezone is used).

R> str(QReturns)
An ‘xts’ object on 2011-06-30/2016-06-24 containing:
  Data: num [1:21, 1:2] 0.047 -0.354 0.118 0.267 -0.215 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:2] "quarterly.returns" "quarterly.returns.1"
  Indexed by objects of class: [POSIXct,POSIXt] TZ: 
  xts Attributes:  
 NULL

QReturns looks like this because you called as.xts on a data.frame, and as.xts.data.frame specifies dateFormat = "POSIXct" by default. If you set dateFormat = "Date", you won't have a problem with merging these two objects.

Also note that it's bad practice to call methods directly (merge.xts). You should just call the merge generic and let the S3 system handle method dispatch.

QReturns <- as.xts(as.data.frame(lapply(dataset,quarterlyReturn)),dateFormat="Date")
Quarterly_Portfolio <- merge(dataset,QReturns)
head(Quarterly_Portfolio)
#            DBK.DE.Adjusted DTE.DE.Adjusted quarterly.returns quarterly.returns.1
# 2011-06-27         33.5422           7.820                NA                  NA
# 2011-06-28         34.2747           7.786                NA                  NA
# 2011-06-29         34.6194           7.909                NA                  NA
# 2011-06-30         35.1193           8.085        0.04701838          0.03388747
# 2011-07-01         36.0286           7.992                NA                  NA
# 2011-07-04         35.7787           7.995                NA                  NA

I personally would avoid this all together by not converting from xts to data.frame back to xts when calculating QReturns. You can call lapply on an xts object directly, then merge the results back together using do.call(merge, ...).

QReturns <- do.call(merge, lapply(dataset, quarterlyReturn))
Quarterly_Portfolio <- merge(dataset, QReturns)

Your getSymbols and "combine" steps can also be done more compactly:

PF <- new.env()
getSymbols(tickers, from=StartDate, to=EndDate, env=PF)
# combine the adjusted close values in one (xts) object
dataset <- do.call(merge, eapply(PF, Ad))