Resampling Tick Data onto a Regular Grid

Article Quant Q&A · Author: XY0

Summary

The document explains how to turn irregular stock trade ticks into a regular time series using previous-tick interpolation. One approach converts epoch milliseconds to POSIX time, creates a five-minute grid, and uses interval lookup to select the latest observation at or before each grid time. The same index maps prices and other fields onto the grid, leaving early grid points without prior observations as missing.

A second approach first groups observations by intraday time, summarizes duplicate timestamps with a median, and then applies a high-frequency data package’s calendar-based aggregation function. Examples show how repeated trades can be consolidated and how sampled values remain unchanged until a later tick becomes available. The examples use a small portion of one trading day and demonstrate ten-second aggregation as well as the requested five-minute concept. Choices about duplicate-tick aggregation, trading hours, time zones, and how to handle missing initial values remain data-preparation decisions.

Key ideas

Convert epoch milliseconds to POSIX timestamps before building a regular calendar-time grid.
Use the latest tick at or before each grid point to implement previous-tick sampling.
Map prices and other fields using the same interval indices used for timestamps.
Consolidate multiple trades at a timestamp with a chosen summary statistic when appropriate.
Sampling results depend on the grid, time-zone handling, trading hours, and duplicate-tick policy.

Tags

High-frequency trading Market microstructure Statistics

Full text

# how to make this time series reguarly spaced


# how to make this time series reguarly spaced












in the picture below we have in the first coloumn the day of the month, in the second coloumn the time in millisecond in epoch time(elapsed from 1 january 1970), third coloumn the stock price and in the last coloumn the volume traded. I want to make this dataset equally spaced with a frequency of 5 minutes using previous tick interpolation. My problem is that the time is in epoch time and I don't know how to apply previous tick interpolation such that any 5 minutes we take the trading instants and the relative price . Moreover in the dataset as displayed in the picture below we have time repetition due to the fact that at the same instants there is more than one transaction. Can someone help to implement it in R or MATLAB?

Being tick data the picture displays only a small parts of the transactions in a trading day

## Answer by Enrico Schumann (score 1)

https://quant.stackexchange.com/a/78297

Here is an example how you can do it in R (without any packages).

Create a regular grid to which to map your data.

```
grid <- seq(from = as.POSIXct("2014-01-31 10:00:00"),
            to   = as.POSIXct("2014-01-31 23:55:00"),
            by = "5 min")
## [1] "2014-01-31 10:00:00 CET" "2014-01-31 10:05:00 CET"
## [3] "2014-01-31 10:10:00 CET" "2014-01-31 10:15:00 CET"
## [5] "2014-01-31 10:20:00 CET" "2014-01-31 10:25:00 CET"
## ....
```

Some example timestamps, taken from your example. R's `POSIXct` datetimes are internally the number of seconds since 1 Jan 1970, so dividing by 1000 makes your timestamps compatible with R's.

```
t <- c(1391160600000/1000, ## "2014-01-31 10:30:00 CET"
       1391160600129/1000, ## "2014-01-31 10:30:00 CET"
       1391160637368/1000, ## "2014-01-31 10:30:37 CET"
       1391160676851/1000, ## "2014-01-31 10:31:16 CET"
       1391183999289/1000, ## "2014-01-31 16:59:59 CET"
       1391184172441/1000) ## "2014-01-31 17:02:52 CET"
```

It remains to map the `t` to the `grid`.

```
i <- findInterval(grid, t)
good <- i > 0

x <- rep(NA, length(grid))
x[good]  <- .POSIXct(t[i[good]])
data.frame(your.time = .POSIXct(x), grid)
##               your.time                grid
## 1                  <NA> 2014-01-31 10:00:00
## 2                  <NA> 2014-01-31 10:05:00
## 3                  <NA> 2014-01-31 10:10:00
## 4                  <NA> 2014-01-31 10:15:00
## 5                  <NA> 2014-01-31 10:20:00
## 6                  <NA> 2014-01-31 10:25:00
## 7   2014-01-31 10:30:00 2014-01-31 10:30:00
## 8   2014-01-31 10:31:16 2014-01-31 10:35:00
## 9   2014-01-31 10:31:16 2014-01-31 10:40:00
## ....
## 83  2014-01-31 10:31:16 2014-01-31 16:50:00
## 84  2014-01-31 10:31:16 2014-01-31 16:55:00
## 85  2014-01-31 16:59:59 2014-01-31 17:00:00
## 86  2014-01-31 17:02:52 2014-01-31 17:05:00
## 87  2014-01-31 17:02:52 2014-01-31 17:10:00
```

Now additionally to timestamps, map price etc, by subsetting the original series with `i[good]`. Suppose you have the following (made-up) prices that go with your timestamps.

```
price <- seq_along(t)
data.frame(time = .POSIXct(t), price)
##                  time price
## 1 2014-01-31 10:30:00     1
## 2 2014-01-31 10:30:00     2
## 3 2014-01-31 10:30:37     3
## 4 2014-01-31 10:31:16     4
## 5 2014-01-31 16:59:59     5
## 6 2014-01-31 17:02:52     6
```

Mapped:

```
grid.price <- rep(NA, length(grid))
grid.price[good]  <- price[i[good]]
data.frame(grid, grid.price)
##                    grid grid.price
## 1   2014-01-31 10:00:00         NA
## 2   2014-01-31 10:05:00         NA
## 3   2014-01-31 10:10:00         NA
## 4   2014-01-31 10:15:00         NA
## 5   2014-01-31 10:20:00         NA
## 6   2014-01-31 10:25:00         NA
## 7   2014-01-31 10:30:00          1
## 8   2014-01-31 10:35:00          4
## 9   2014-01-31 10:40:00          4
## ....
## 83  2014-01-31 16:50:00          4
## 84  2014-01-31 16:55:00          4
## 85  2014-01-31 17:00:00          5
## 86  2014-01-31 17:05:00          6
## 87  2014-01-31 17:10:00          6
```

## Answer by Pleb (score 1)

https://quant.stackexchange.com/a/78570

## Utilizing the highfrequency package:

If you are willing to perform most of your data pre-processing and modelling in R, I strongly suggest familiarizing yourself with the `highfrequency` package. The package offers versatile tools for both cleaning your raw high-frequency data and effectively modeling and managing it thereafter. The package comes with a guide on how to clean and model high-frequency data using the package. Here is a link to the package documentation.

I have presented a simple example where I utilize the `aggregateTS` function from the package to get a 10-second sampling scheme using previous-tick interpolation. The same function can be used for any type of calendar-based sampling scheme (seconds, minutes, hours).

```
library(highfrequency)
library(dplyr)
library(data.table)

# See dataframe (df) below. 

# Convert raw times to posixct 
df$DT <- as.POSIXct(df$DT/1000, format = '01-01-1970')

####### Simple aggregate function using dplyr. #######
#1. Grouping by IntradayTime eg. 10:30:01.
#2. Calculate simple Median over prices and volumes 
#   (other aggregation methods are also welcome). 
#3. Selecting only needed columns. 
#4. Converting back to data.table.

df2 <- df %>% mutate(IntradayTime = format(DT, '%T')) %>% 
  group_by(IntradayTime) %>% 
  summarize(DT = last(DT), PRICE = median(PRICE), SIZE = median(SIZE)) %>% 
  select(DT, PRICE, SIZE) %>% data.table()

# Using the aggregateTS from the highfrequency package. 
# Using 10-second frequency as an example:
agg <- aggregateTS(df2, FUN = "previoustick",alignBy = "seconds",alignPeriod = 10)
```

Giving you the corresponding result:

```
> agg
                       DT PRICE    SIZE
                   <POSc> <num>   <num>
   1: 2014-01-31 10:30:00 37395 17267.5
   2: 2014-01-31 10:30:10 37380     2.0
   3: 2014-01-31 10:30:20 37380     2.0
   4: 2014-01-31 10:30:30 37210   230.0
   5: 2014-01-31 10:30:40 37215   150.0
  ---                                  
2337: 2014-01-31 16:59:20 37240   100.0
2338: 2014-01-31 16:59:30 37240   100.0
2339: 2014-01-31 16:59:40 37240   100.0
2340: 2014-01-31 16:59:50 37240   100.0
2341: 2014-01-31 17:00:00 37400   100.0
```

This answer aims to give you some exposure to the package, as it can be incredibly useful when working with high-frequency data.

#### The dataframe used in the above example

```
# Convert df to data.table: No need for the day of the month. 
# Column names follow the structure of the highfrequency package. 
df <- data.table(
  DT = c(
    1391160600129, 1391160600243, 1391160601160, 1391160601160, 1391160601599, 1391160605189, 1391160622539,
    1391160622539, 1391160623157, 1391160623450, 1391160623458, 1391160626173, 1391160634087, 1391160637108,
    1391160637368, 1391160644507, 1391160644757, 1391160644757, 1391160650867, 1391160650867, 1391160667192,
    1391160676851, 1391160676851, 1391160688360, 1391183999208, 1391183999278, 1391183999289
  ),
  PRICE = c(
    37400, 37390, 37350, 37340, 37290, 37380, 37300, 
    37290, 37300, 37290, 37250, 37210, 37190, 37210, 37220,
    37240, 37200, 37180, 37170, 37210, 37210, 37230, 37230, 37240, 37400, 
    37400, 37390
  ),
  SIZE = c(
    34435, 100, 100, 100, 200, 2, 10, 90, 211, 10, 193, 230, 100, 200, 
    100, 100, 200, 100, 100, 100, 103, 250,
    50, 100, 100, 114, 85
  )
)
```

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.

Resampling Tick Data onto a Regular Grid with Previous-Tick Interpolation

Summary

Key ideas

Tags