[R-SIG-Finance] aligning xts object to regularly spaced time clock

[Achim Zeileis]

On Mon, 21 Jun 2010, Eric Zivot wrote:

> I am trying to align an xts object containing irregularly spaced intra-day
> price data to a regularly spaced time clock so that I can do realized
> variance/covariance calculations. For example, I have two xts objects
> msftTrades and geTrades created by the RTAQ package (the time index variable
> is a timeDate object) :

I suspect that xts has some high-level tools that could be leveraged for 
this. But as a primitive zoo developer, I only know how to do it by hand 
(which is hopefully still useful):
   1. construct regular time index
   2. expand series to regular grid (via merge)
   3. use na.locf() for "last observation carried forward"

I used only a subset of your data:

## data
x <- xts(c(30.62, 30.64, 30.66, 30.71, 30.705, 30.695, 30.725, 30.74),
   timeDate(c("2010-01-04 09:30:00", "2010-01-04 09:30:01",
   "2010-01-04 09:30:02", "2010-01-04 09:30:03", "2010-01-04 09:30:04",
   "2010-01-04 09:30:05", "2010-01-04 09:30:06", "2010-01-04 09:30:07")))
y <- xts(c(15.21, 15.22, 15.23, 15.24), timeDate(c("2010-01-04 09:30:01",
   "2010-01-04 09:30:03", "2010-01-04 09:30:06", "2010-01-04 09:30:07")))

## 1. regular index
ix <- seq(min(c(start(x), start(y))), max(c(end(x), end(y))), by = "1 s")

## 2. merged series on all time stamps
xy <- merge(xts(,ix), x = x, y = y)

## 3. last observation carried forward
xy <- na.locf(xy)

## collect data on regular index
window(xy, ix)

Remarks:
   - Note that this is fully symmetric in x and y.
   - It would work analogously for "zoo" series.
   - For other index classes, only the regular index construction would
     need to be changed (which, I think, is the main reason that this is
     not readily available in "zoo").

hth,
Z

>
>
>> start(msftTrades)
>
> GMT
>
> [1] [2010-01-04 09:30:00]
>
>> end(msftTrades)
>
> GMT
>
> [1] [2010-01-04 16:00:00]
>
>> start(geTrades)
>
> GMT
>
> [1] [2010-01-04 09:30:01]
>
>> end(geTrades)
>
> GMT
>
> [1] [2010-01-04 15:59:59]
>
>
>
>> msftTrades$PRICE[1:10,]
>
>                    PRICE
>
> 2010-01-04 09:30:00 "30.62"
>
> 2010-01-04 09:30:01 "30.64"
>
> 2010-01-04 09:30:02 "30.66"
>
> 2010-01-04 09:30:03 "30.71"
>
> 2010-01-04 09:30:04 "30.705"
>
> 2010-01-04 09:30:05 "30.695"
>
> 2010-01-04 09:30:06 "30.725"
>
> 2010-01-04 09:30:07 "30.74"
>
> 2010-01-04 09:30:08 "30.74"
>
> 2010-01-04 09:30:09 "30.74"
>
>> geTrades$PRICE[1:10,]
>
>                    PRICE
>
> 2010-01-04 09:30:01 "15.21"
>
> 2010-01-04 09:30:03 "15.22"
>
> 2010-01-04 09:30:06 "15.23"
>
> 2010-01-04 09:30:07 "15.24"
>
> 2010-01-04 09:30:08 "15.23"
>
> 2010-01-04 09:30:09 "15.23"
>
> 2010-01-04 09:30:10 "15.22"
>
> 2010-01-04 09:30:11 "15.22"
>
> 2010-01-04 09:30:13 "15.22"
>
> 2010-01-04 09:30:14 "15.21"
>
>
>
> Here, the trade prices are recorded to the nearest second but are
> irregularly spaced and the time clocks for MSFT and GE are different. For
> example, there is no transaction for GE at 09:30:02. I want to do the
> following
>
>
>
> 1.       Create a regularly spaced one second time clock between 9:30 and
> 16:00
>
> 2.       Align the two price series to this regularly spaced time clock
> using the "previous tick" method
>
>
>
> For (1),  I can use the timeDate align() function to get the regularly
> spaced time clock. For example,
>
>
>
>> td1sec = align(index(msftTrades), by="1s")
>
>> start(td1sec)
>
> GMT
>
> [1] [2010-01-04 09:30:00]
>
>> end(td1sec)
>
> GMT
>
> [1] [2010-01-04 16:00:00]
>
>> td1sec[1:5]
>
> GMT
>
> [1] [2010-01-04 09:30:00] [2010-01-04 09:30:01] [2010-01-04 09:30:02]
> [2010-01-04 09:30:03] [2010-01-04 09:30:04]
>
>> length(td1sec)
>
> [1] 23401
>
>
>
> I can't figure out how to do (2). I want to align the data in msftTrades and
> geTrades to the new time index td1sec. For the observations that do not
> occur on a given time stamp, I want to use the previous tick for that
> observation. (In S-PLUS I can easily do this using the align() function).
> The RTAQ package has a function called aggregatets() that almost does what I
> want. It will  do the previous tick aggregation to a one second clock but it
> will omit observations in case an interval is empty.
>
>
>
> For example, what I want for the geTrades data aligned to the one-second
> time clock is the following:
>
>
>
> 2010-01-04 09:30:00  NA
>
> 2010-01-04 09:30:01 "15.21"
>
> 2010-01-04 09:30:02  "15.21"
>
> 2010-01-04 09:30:03 "15.22"
>
> 2010-01-04 09:30:04 "15.22"
>
> 2010-01-04 09:30:05  "15.22"
>
> 2010-01-04 09:30:06 "15.23"
>
> 2010-01-04 09:30:07 "15.24"
>
> 2010-01-04 09:30:08 "15.23"
>
> 2010-01-04 09:30:09 "15.23"
>
> 2010-01-04 09:30:10 "15.22"
>
> 2010-01-04 09:30:11 "15.22"
>
> 2010-01-04 09:30:12 "15.22"
>
> 2010-01-04 09:30:13 "15.22"
>
> 2010-01-04 09:30:14 "15.21"
>
> .
>
>
>
>
>
>
>
>
>
>
>
>
>
> Eric Zivot
>
> Professor and Gary Waterman Distinguished Scholar
>
> Department of Economics
>
> Adjunct Professor of Finance
>
> Adjunct Professor of Statistics
>
> Box 353330                  email:  ezivot at u.washington.edu
>
> University of Washington    phone:  206-543-6715
>
> Seattle, WA 98195-3330
> www:  http://faculty.washington.edu/ezivot
>
>
>
>
> 	[[alternative HTML version deleted]]
>
> _______________________________________________
> R-SIG-Finance at stat.math.ethz.ch 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.
>