# Calculating intraday returns from imperfect data in R [closed]

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?

• I'm voting to close this question as off-topic because it as a programming question and belongs on Stack Overflow. Feb 23 '18 at 23:30

## 1 Answer

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)