[R-SIG-Finance] [xts] merge function weird behaviour
Jeff Ryan
jeff.a.ryan at gmail.com
Fri Dec 10 14:27:19 CET 2010
Anass,
You're issue is indeed that the times aren't originally aligned, so
to.daily is just aligning to the end of the day as it sees it.
The simple solution is to re-index your data with "Date" times - since
that is really the end result of your to.daily processing expectation
(though not always what one would want, hence the additional step)
index(t1) <- as.Date(index(t1))
index(t2) <- as.Date(index(t2))
merge(t1,t2)
t1.Close t2.Close
2007-01-03 94.59 97.87
2007-01-04 94.38 97.32
2007-01-05 92.55 96.04
2007-01-05 92.37 95.97
2007-01-08 92.23 95.69
2007-01-09 92.89 96.23
to.period could probably automatically alter your index if days or
lower frequency is selected (e.g. period="days" or to.daily/monthly
etc is called), though I'll have to think about the full implication
of this (i.e. automagically or optionally).
Best,
Jeff
On Fri, Dec 10, 2010 at 5:00 AM, Anass Mouhsine
<anass.mouhsine at gmail.com> wrote:
> Thks all,
>
> Robert,
> My packages are updated. I'll update my R version to be sure.
>
> Santosh,
> I'll try your methodology as well.
>
> On 10/12/2010 11:51, Santosh Srinivas wrote:
>>
>> Hi Anass,
>>
>> I like zoo more so here is what you need I think.
>>
>> require(zoo)
>>
>> f2 <- function(d, t, format = "%Y-%m-%d %H:%M:%S") {
>>
>> as.POSIXct(strptime(paste(d, t),format=format))
>>
>> }
>>
>> t1=read.csv("AUDJPY.csv", sep=";")
>>
>> t1.z <- read.zoo(t1, index=list(1,2), FUN =f2)
>>
>> t2=read.csv("CHFJPY.csv", sep=";")
>>
>> t2.z <- read.zoo(t2, index=list(1,2), FUN =f2)
>>
>> head(t1.z)
>>
>> head(t2.z)
>>
>> t1.z.cl <- Cl(x=to.daily(t1.z$Close))
>>
>> t2.z.cl <- Cl(x=to.daily(t2.z$Close))
>>
>> t1.z.cl <- t1.z.cl[,4]
>>
>> t2.z.cl <- t2.z.cl[,4]
>>
>> head(t1.z.cl)
>>
>> head(t2.z.cl)
>>
>> t3 <- merge.zoo(t1.z.cl,t2.z.cl)
>>
>> head(t3)
>>
>> (there should be something similar for xts)
>>
>> HTH.
>>
>> *From:*r-sig-finance-bounces at r-project.org
>> [mailto:r-sig-finance-bounces at r-project.org] *On Behalf Of *Anass Mouhsine
>> *Sent:* 10 December 2010 14:50
>> *To:* Mark Breman; r-sig-finance at r-project.org
>> *Subject:* Re: [R-SIG-Finance] [xts] merge function weird behaviour
>>
>> Here,
>>
>> you will find attached data used to illustrate the issue.
>> here is the code used as well
>>
>> #reading file
>> t1=read.csv("AUDJPY.csv", sep=";")
>> rownames(t1)<-t1[,"Index"];t1<-t1[,-1]
>> #constructing xts object
>> t1<-as.xts(t1,tzone="America/New_York", src="csv", updated=Sys.time())
>>
>> t2=read.csv("CHFJPY.csv", sep=";")
>> rownames(t2)<-t2[,"Index"];t2<-t2[,-1]
>> t2<-as.xts(t2,tzone="America/New_York", src="csv", updated=Sys.time())
>>
>> # requires quantmod
>> library(quantmod)
>>
>> #constructing daily close series
>> t1<-Cl(to.daily(t1))
>> t2<-Cl(to.daily(t2))
>>
>> #trying to merge
>> merge(t1,t2)
>>
>> #......and result....sadly...is
>> > merge(t1,t2)
>> t1.Close t2.Close
>> 2007-01-02 NA 97.87
>> 2007-01-02 94.59 NA
>> 2007-01-03 94.38 97.32
>> 2007-01-04 92.55 96.04
>> 2007-01-05 NA 95.97
>> 2007-01-05 92.37 NA
>> 2007-01-07 NA 95.69
>> 2007-01-07 92.23 NA
>> 2007-01-08 92.89 96.23
>>
>>
>>
>> On 10/12/2010 10:04, Mark Breman wrote:
>>
>> Hi Anass,
>>
>> Could it be the series have a (invisible) different time part, or the
>> timezone is different.
>>
>> Regards,
>>
>> -Mark-
>>
>> 2010/12/10 Anass Mouhsine <anass.mouhsine at gmail.com
>> <mailto:anass.mouhsine at gmail.com>>
>>
>> Hi all,
>>
>> I encountered a weird behaviour of merge function while using it with
>> xts objects.
>> I have multiple xts objects (10 timeseries) that I want to merge in
>> order to conduct a multivariate analysis.
>> Here is an example with only two timeseries
>>
>> #Let us define the time series
>> > t1
>> Close
>> 2007-01-02 97.87
>> 2007-01-03 97.32
>> 2007-01-04 96.04
>> 2007-01-05 95.97
>> 2007-01-07 95.69
>> 2007-01-08 96.23
>> > t2
>> Close
>> 2007-01-02 94.59
>> 2007-01-03 94.38
>> 2007-01-04 92.55
>> 2007-01-05 92.37
>> 2007-01-07 92.23
>> 2007-01-08 92.89
>> > c(class(t1),class(t2))
>> [1] "xts" "zoo" "xts" "zoo"
>>
>> #I do the merge using merge.xts
>> > t<-merge(t1,t2)
>> > t
>> Close Close
>> 2007-01-02 97.87 NA
>> 2007-01-02 NA 94.59
>> 2007-01-03 97.32 94.38
>> 2007-01-04 96.04 92.55
>> 2007-01-05 95.97 NA
>> 2007-01-05 NA 92.37
>> 2007-01-07 95.69 NA
>> 2007-01-07 NA 92.23
>> 2007-01-08 96.23 92.89
>>
>> # I don't see where the problem lays since both xts objects have the
>> same index
>> > index(t1)
>> [1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-07"
>> "2007-01-08"
>> > index(t2)
>> [1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-07"
>> "2007-01-08"
>>
>> # I tried using join but with no satisfying result
>> >t<-merge(head(t1),head(t2),join='inner')
>> > t
>> CHFJPY..2007.....Close AUDJPY..2007.....Close
>> 2007-01-03 97.32 94.38
>> 2007-01-04 96.04 92.55
>> 2007-01-08 96.23 92.89
>>
>> I am really out of tricks here :-[ .
>>
>> So if someone is kind enough as to point me towards the right
>> direction, it would make my day.
>>
>> Thx in advance,
>>
>> Anass
>>
>> _______________________________________________
>> R-SIG-Finance at r-project.org <mailto:R-SIG-Finance at r-project.org>
>> mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
>> -- Subscriber-posting only. If you want to post, subscribe first.
>> -- Also note that this is not the r-help list where general R
>> questions should go.
>>
>
>
> [[alternative HTML version deleted]]
>
> _______________________________________________
> R-SIG-Finance at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions should go.
>
--
Jeffrey Ryan
jeffrey.ryan at lemnica.com
www.lemnica.com
More information about the R-SIG-Finance
mailing list