Skip to content
All library documents

Computing Intraday Returns with Missing Trade Minutes

Article Quant Q&A · Author: stollenm
Read the original

Summary

The document describes a way to calculate minute returns when a price table contains rows only for minutes with trades. It recommends building a complete minute-by-minute time grid, placing observed prices at their timestamps, and carrying the last available price forward into empty minutes. Returns are then calculated from this regularized price series, making unchanged prices across no-trade intervals produce zero returns.

The example sketches this workflow in R with time-grid, time-series, and last-observation-carried-forward tools. It favors the explicit construction because it makes the treatment of missing timestamps clear, though it notes simpler approaches may suit the specific sample. This method assumes that carrying the last traded price is appropriate for the intended return series; it does not itself resolve stale-price effects, market closures, or whether trade prices are the desired input rather than quotes.

Key ideas

  • Create a complete regular time grid before computing interval returns.
  • Place observed prices at their matching timestamps and leave no-trade intervals missing initially.
  • Carry the most recent observed price forward across missing minutes.
  • Compute returns from the completed price series so unchanged intervals contribute zero returns.
  • The carried-price convention can create stale observations and should fit the analysis objective.

Tags

EquitiesStatisticsExecutionMarket microstructure
Full text
# Calculating intraday returns from imperfect data in R


# Calculating intraday returns from imperfect data in R












The aim is to calculate minute returns in R. Given is minute price data in a `tbl_df`. A row was only added if there actually were trades.

```
  datetime              close
1 1998-01-02 08:31:00   0.484
2 1998-01-02 08:41:00   0.436
3 1998-01-02 08:44:00   0.436
4 1998-01-02 09:02:00   0.436
5 1998-01-02 09:15:00   0.440
6 1998-01-02 09:20:00   0.440
7 1998-01-02 09:26:00   0.437
```

Is there a preprogrammed function which automatically fills in a return of `0` for minutes without a trades? If not, which is the best way to do it?

## Answer by Enrico Schumann (score 3, accepted)

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

Create a new price series that has a value for every minute, e.g. by carrying the last observation forward. Then compute returns from this new price series.

(There are simpler approaches for this particular case, but I'd prefer the one outlined above as it is conceptually clear.)

A sketch in R. (Disclosure: I am the maintainer of packages PMwR, from which I use function `returns`, and package `datetimeutils`, from which I use function `timegrid`.)

```
library("PMwR")   ## https://github.com/enricoschumann/PMwR
library("zoo")
library("datetimeutils")

## the example data
timestamp <- c("1998-01-02 08:31:00",
               "1998-01-02 08:41:00",
               "1998-01-02 08:44:00",
               "1998-01-02 09:02:00",
               "1998-01-02 09:15:00",
               "1998-01-02 09:20:00",
               "1998-01-02 09:26:00")   
timestamp <- as.POSIXct(timestamp)

p <- c(0.484, 0.436, 0.436, 0.436, 0.440, 0.440, 0.437)

## create a new series with NA when
## there is no price
start <- as.POSIXct("1998-01-02 08:30:00")
end   <- as.POSIXct("1998-01-02 09:30:00")

all_times <- timegrid(start, end, interval = "1 min")
all_p <- rep(NA, length(all_times))

i <- match(timestamp, all_times, nomatch = 0L)
all_p[i] <- p[i > 0]

## create a zoo series and replace
## missing values with the previous
## price
P <- zoo(all_p, all_times)
P <- na.locf(P)
returns(P)
```

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.