Calculating mean between two dates

Question

I have a data table with columns date, stock, daily return, start date, and end date.

I'd like to calculate the mean of daily return between start date and end date specific to each stock, where end date=date, and start date=date-1 year. The image is a small part of my data table, which contains 5 different time brackets (2009-2010, 2010-2011...2014-2015).

pic1

Welcome to StackOverflow. In order to ask a better question please read [How to ask a good question](https://stackoverflow.com/help/how-to-ask) and [Minimal, Complete, and Verifiable Example](https://stackoverflow.com/help/mcve) and [How to make a great R reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — Rui Barradas, Jul 12 '18 at 14:33
As in your data the start date values are absent, you can't calcualte any daily return. — Roman, Jul 12 '18 at 14:54
"D" represents the daily return value that I want to find the mean of. So I tried something like dt[,meanreturn:=mean(D), by=stock], but I'm not sure what to put in the mean() to let it know to only find mean for D values between start and end date. — Sunny Chen, Jul 12 '18 at 14:58
Can you be more clear with what you expect? Maybe show us an example of the output desired. — RLave, Jul 12 '18 at 15:02
I think I get what you are asking, but I am unsure of the time-frames you are calculating it for: 2009-2010, what dates specifically? — akash87, Jul 12 '18 at 15:59
I'm looking for a rolling fiscal mean: so I want to take all the daily returns from April 7, 2009 to April 7, 2010 and calculate the mean, then calculate mean(daily returns) for April 8, 2009 to April 8, 2010, etc for the entire dataset. Then assign that mean(daily return) to the corresponding end date. — Sunny Chen, Jul 12 '18 at 17:57

score 0 · Answer 1 · answered Jul 12 '18 at 18:42

Let's first create the dataset:

d1 <- data.frame(Date = seq.Date(as.Date("2009-04-07"), as.Date("2015-04-06"), by = "day"), stock = 60004)
d2 <- data.frame(Date = seq.Date(as.Date("2009-04-07"), as.Date("2015-04-06"), by = "day"), stock = 60005)
d3 <- data.frame(Date = seq.Date(as.Date("2009-04-07"), as.Date("2015-04-06"), by = "day"), stock = 60006)
d4 <- data.frame(Date = seq.Date(as.Date("2009-04-07"), as.Date("2015-04-06"), by = "day"), stock = 60007)

dat <- rbind(d1, d2, d3, d4)

dat$D <- rnorm(dim(dat)[1])
dat$stock <- as.factor(dat$stock)

datzoo$rollmean <- ave(dats$D, datzoo$stock, FUN = function(x) rollmean(x, k = 365, fill = 0, align = "right"))

For ave to work optimally, you should convert stock into a factor, and set the ave function where k is the window size (365 for rolling mean by day); fill is what to fill NA values with; and align is to let the function know which side (left is the same as top and right is the same as bottom of dataset) to calculate your rolling mean from.

Calculating mean between two dates

1 Answers1