[R-SIG-Finance] AsOf join in R

[Robert A'gata]

Hi Garrett - Yes. I'm very sure. This type of operation occurs a lot
when manipulating high-frequency financial data. I think what RTAQ
offers is good. I can use that as a template to start with. Thank you.

On Wed, Oct 5, 2011 at 9:38 PM, G See <gsee000 at gmail.com> wrote:
> Robert,
> Check the data you gave us:
>> A <- xts(c(10,15,20,25),
> +
> order.by=as.POSIXct(c("2011-09-01","2011-09-09","2011-09-10","2011-09-15")))
>> A
>            [,1]
> 2011-09-01   10
> 2011-09-09   15
> 2011-09-10   20
> 2011-09-15   25
>> B <- xts(c(1.1,1.5,1.3,1.7),
> +
> order.by=as.POSIXct(c("2011-08-31","2011-09-09","2011-09-11","2011-09-12")))
>> B
>            [,1]
> 2011-08-31  1.1
> 2011-09-09  1.5
> 2011-09-11  1.3
> 2011-09-12  1.7
> Both A and B have a value for '2011-09-09'
>> cbind(A,B)
>            ..1 ..2
> 2011-08-31  NA 1.1
> 2011-09-01  10  NA
> 2011-09-09  15 1.5
> 2011-09-10  20  NA
> 2011-09-11  NA 1.3
> 2011-09-12  NA 1.7
> 2011-09-15  25  NA
> Surely you don't want to replace the 1.5 with 1.1?
> see ?na.locf if you haven't already.
> Regards,
> Garrett
> On Wed, Oct 5, 2011 at 8:11 PM, Robert A'gata <rhelpacc at gmail.com> wrote:
>>
>> Hi Brian,
>>
>> Thanks for getting back. I'm afraid it's not doing what I intend. If
>> you see value of B after asof join on 2011-09-09, cbind and na.locf
>> will give you 1.5 as opposed to 1.1. I would like to have latest value
>> available prior to 2011-09-09 from B which will be 1.1 on 2011-09-09.
>> It's a strict inquality for this operation. Would you have any other
>> recommendation? Thank you again.
>>
>> Robert.
>>
>> On Wed, Oct 5, 2011 at 6:21 AM, Brian G. Peterson <brian at braverock.com>
>> wrote:
>> > On Tue, 2011-10-04 at 23:41 -0400, Robert A'gata wrote:
>> >> AsOf(A,B) should return
>> >>
>> >>                     A       B
>> >> 2011-09-01    10     1.1
>> >> 2011-09-09    15     1.1     #  (because latest value B prior to
>> >> 2011-09-09 is 1.1)
>> >> 2011-09-10    20     1.5
>> >> 2011-09-15    25     1.7
>> >>
>> >> How do I write the above AsOf function in R? The merge function does
>> >> not do what I want because it will align points that have the same
>> >> time stamp together while what I want is actually latest value prior
>> >> to timestamp in A. Any example would be greatly appreciated. Thank
>> >> you.
>> >
>> > A <- xts(c(10,15,20,25),
>> >
>> > order.by=as.POSIXct(c("2011-09-01","2011-09-09","2011-09-10","2011-09-15")))
>> > B <- xts(c(1.1,1.5,1.3,1.7),
>> >
>> > order.by=as.POSIXct(c("2011-08-31","2011-09-09","2011-09-11","2011-09-12")))
>> > AsOf<-function(a,b) {
>> >   x<-cbind(a,b)
>> >   x[,2]<-na.locf(x[,2])
>> >   x[!is.na(x[,1])]
>> > }
>> > AsOf(A,B)
>> >
>> > #####################
>> > #           ..1 ..2
>> > #2011-09-01  10 1.1
>> > #2011-09-09  15 1.5
>> > #2011-09-10  20 1.5
>> > #2011-09-15  25 1.7
>> > #####################
>> >
>> >
>> > --
>> > Brian G. Peterson
>> > http://braverock.com/brian/
>> > Ph: 773-459-4973
>> > IM: bgpbraverock
>> >
>> >
>>
>> _______________________________________________
>> 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.
>
>