[R-SIG-Finance] !SPAM: Re: aligning xts object to regularly spaced time clock
Brian G. Peterson
brian at braverock.com
Tue Jun 22 03:25:34 CEST 2010
(to.period isn't the correct function for this)
Typically, on tick data, I will use align.time to get everything on an even
index, like seconds. This will only align on a period where you have an
observation, as you've noticed.
Then, to get to precisely what you want, you'd do:
#this will insert NA's on the missing time stamps
index(geTrades) <- td1sec
# then this fills it in
geTrades <- na.locf(geTrades)
Regards,
- Brian
On 06/21/2010 07:51 PM, Eric Zivot wrote:
> Brian
> I saw this function but wasn't sure how it worked. Unfortunately, it doesn't
> seem to do the trick (see below)
>
>> geTrades[1:10,]
> SYMBOL EX PRICE SIZE COND CR G127
> 2010-01-04 09:30:01 "GE" "T" "15.21" "67099" "" " 0" "0"
> 2010-01-04 09:30:03 "GE" "Z" "15.22" "100" "F" " 0" "0"
> 2010-01-04 09:30:06 "GE" "P" "15.23" "3950" "F" " 0" "0"
> 2010-01-04 09:30:07 "GE" "T" "15.24" "1500" "F" " 0" "0"
> 2010-01-04 09:30:08 "GE" "D" "15.23" "8000" "F" " 0" "0"
> 2010-01-04 09:30:09 "GE" "P" "15.23" "1300" "F" " 0" "0"
> 2010-01-04 09:30:10 "GE" "Z" "15.22" "11800" "F" " 0" "0"
> 2010-01-04 09:30:11 "GE" "D" "15.22" "5000" "F" " 0" "0"
> 2010-01-04 09:30:13 "GE" "Z" "15.22" "1200" "F" " 0" "0"
> 2010-01-04 09:30:14 "GE" "N" "15.21" "11900" "F" " 0" "0"
>> geNew = align.time(geTrades, 1)
>> geNew[1:10,]
> SYMBOL EX PRICE SIZE COND CR G127
> 2010-01-04 01:30:02 "GE" "T" "15.21" "67099" "" " 0" "0"
> 2010-01-04 01:30:04 "GE" "Z" "15.22" "100" "F" " 0" "0"
> 2010-01-04 01:30:07 "GE" "P" "15.23" "3950" "F" " 0" "0"
> 2010-01-04 01:30:08 "GE" "T" "15.24" "1500" "F" " 0" "0"
> 2010-01-04 01:30:09 "GE" "D" "15.23" "8000" "F" " 0" "0"
> 2010-01-04 01:30:10 "GE" "P" "15.23" "1300" "F" " 0" "0"
> 2010-01-04 01:30:11 "GE" "Z" "15.22" "11800" "F" " 0" "0"
> 2010-01-04 01:30:12 "GE" "D" "15.22" "5000" "F" " 0" "0"
> 2010-01-04 01:30:14 "GE" "Z" "15.22" "1200" "F" " 0" "0"
> 2010-01-04 01:30:15 "GE" "N" "15.21" "11900" "F" " 0" "0"
>> align.time(index(geTrades), 1)
> Error in UseMethod("align.time") :
> no applicable method for 'align.time' applied to an object of class
> "timeDate"
>
> -----Original Message-----
> From: Brian G. Peterson [mailto:brian at braverock.com]
> Sent: Monday, June 21, 2010 5:22 PM
> To: Eric Zivot
> Cc: r-sig-finance at stat.math.ethz.ch
> Subject: Re: [R-SIG-Finance] aligning xts object to regularly spaced time
> clock
>
> The function you want is align.time()
>
> It is written to do what you're asking for.
>
> Regards,
>
> - Brian
>
> On 06/21/2010 07:09 PM, 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) :
>>
>>
>>
>>> 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.
>
>
--
Brian G. Peterson
http://braverock.com/brian/
Ph: 773-459-4973
IM: bgpbraverock
More information about the R-SIG-Finance
mailing list