3

I'm working on tick data and want to aggregate my xts irregularly spaced series into a 1 second homogeneous one. I thus use the xts package function to.period :

price_1m <-to.period(price,period="seconds",k=1,OHLC=FALSE)

here is what I get :

2010-02-02 08:00:03 2787
2010-02-02 08:00:04 2786
2010-02-02 08:00:05 2787
2010-02-02 08:00:06 2787
2010-02-02 08:00:07 2786
2010-02-02 08:00:08 2786
2010-02-02 08:00:09 2786
2010-02-02 08:00:10 2787
2010-02-02 08:00:11 2786
2010-02-02 08:00:14 2786
2010-02-02 08:00:16 2786
2010-02-02 08:00:18 2787

My series is aggregated but for example tick data is missing at times 08:00:13 and 08:00:15. What I want is to fill those blanks with previous tick data knowing that the 08:00:13 and 08:00:15 prices are missing in the tick-by-tick xts series. Any idea?

thanks

Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
marino89
  • 899
  • 1
  • 10
  • 16
  • You could try a for loop starting at your first time point by second which would check for existence of the data point and insert the point otherwise. There is probably a faster method than this though – DiscreteCircle Jul 26 '12 at 13:27
  • Related: http://stackoverflow.com/questions/10423551/creating-regular-15-minute-time-series-from-irregular-time-series – Joshua Ulrich Jul 26 '12 at 13:29

3 Answers3

7

You can merge price_1m with an "empty" xts object that contains an index with the regularly-spaced intervals you want, use na.locf on that. For example:

onemin <- seq(start(price_1m),end(price_1m),by="1 s")
Price_1m <- na.locf(merge(price_1m, xts(,onemin)))
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • Great, thanks a lot! Do you think is it a good way of getting a regularly spaced time series? – marino89 Jul 26 '12 at 13:46
  • @user1474263: Yes. I wouldn't have answered if I didn't think it was a good solution. ;-) – Joshua Ulrich Jul 26 '12 at 14:00
  • @user1474263 The difference between this and my answer at http://stackoverflow.com/a/11660324/841830 is I wanted 1m bars for the whole session, and there are often gaps at the start and end of a trading session. The other difference is I keep them as NA bars, at this stage, rather than use na.locf (which won't give you meaingful data if you have OHLCV bars). – Darren Cook Jul 26 '12 at 23:16
  • @ Darren Cook : thanks for your answer. I didn't see that you had added the definition of seq.exclude_final_period.POSIXt.. Thanks a lot guys for your answers. By the way, apart xts and RTAQ, do you know any interesting R package suitable for high frequency data analsysis and modelling? – marino89 Jul 27 '12 at 08:06
1

The MakeStrictlyRegular function in my qmao package will do this for you.

Here is the example from ?MakeStrictlyRegular

x <- align.time(.xts(1:1000, 60*1:1000))[-c(2, 4, 7, 8), ] # remove some rows at the begining
head(x[paste((start.x <- start(x)), "/")])
#                    [,1]
#1969-12-31 18:02:00    1
#1969-12-31 18:04:00    3
#1969-12-31 18:06:00    5
#1969-12-31 18:07:00    6
#1969-12-31 18:10:00    9
#1969-12-31 18:11:00   10
x2 <- MakeStrictlyRegular(x)
#added 4 (0.40%); There are now 1000 total rows.
head(x2[paste(start.x, "/")])
#                    [,1]
#1969-12-31 18:02:00    1
#1969-12-31 18:03:00    1
#1969-12-31 18:04:00    3
#1969-12-31 18:05:00    3
#1969-12-31 18:06:00    5
#1969-12-31 18:07:00    6

For your 1 second data, you'd use by="sec". So, something like 

MakeStrictlyRegular(price, by="sec")
GSee
  • 48,880
  • 13
  • 125
  • 145
  • qmao won't install: install.packages("qmao", repos="http://r-forge.r-project.org") Warning message: package ‘qmao’ is not available (for R version 2.15.2) – Enrico Detoma Jan 19 '13 at 14:53
  • @EnricoDetoma, I have a mirror at github, so you can install it with `library(devtools); install_github("qmao", "gsee")`. If that doesn't work (presumably because you're on Windows), since **qmao** has no compiled code, you can download the zip file, unzip it into your working directory and use `install.packages` with `repos=NULL` and `type="source"`. For a more general way of installing R-Forge packages that fail to build see http://stackoverflow.com/questions/11105131/cannot-install-r-forge-package-using-install-packages/11105132#11105132 – GSee Jan 19 '13 at 15:25
0

You need to sample the data (1 sec bar) : Open/High/Low/Close/Volume. For the volume, you might set the volume to 0 for the "missing" data, then the open is equal to close, high and low

salah
  • 1