Daily Geometric Returns from Monthly Observations

Question

I have monthly weight observations and daily returns, and I am trying to compute a geometric return for every day in a month. It might be easier to see the pattern:

How do I reproduce the "desired output" column? Either a solution from the base functions in R or any package suggestions are appreciated!

• Edit 1: Thank you.

Here is some sample data and the solution that I have been working on:

set.seed(33)

z <- c(.35,NA,NA,NA,.2,NA,NA)
z1 <- c(.35,.35,.35,.35,.2,.2,.2)
z2 <- rnorm(7)
zCbind <- data.frame(cbind(z,z1,z2))
colnames(zCbind) <- c("months","na.locf(months)","values")

solution1 <- ifelse(zCbind[,1] == zCbind[,2], 
                zCbind[,1],                                   # if TRUE 
                zCbind[,2]*apply(zCbind[,3],2,cumprod))       # if FALSE

I know my problem is in the false condition. Solutions that I have tried are:

1. replace cumprod with the prod function
2. changed the format of zCbind[,3] by binding or converting it matrix/df
3. this looked promising, but i can't find any more literature on the "cumprod.column" wrappers to the cumprod function: http://braverock.com/brian/R/PerformanceAnalytics/html/cum.utils.html

Answer 1

How about this with plyr::ddply()

I recreated your data to make it more like the original format

sheet<-data.frame(date=as.Date(1:100,origin="2012-01-01"),
                  weight=rep(NA,100),
                  increment=rnorm(100,0,0.5)/100
           )

#get the latest date in each month to replace the NAs
last_days<-ddply(sheet,.(month=format(date,"%Y-%b")),summarise,last_day=max(date))
sheet[sheet$date %in% last_days$last_day,]$weight<-runif(nrow(last_days))/2

#now we have a table which matches your data

#set the NA's to 0
sheet$weight[is.na(sheet$weight)]<-0

# OK so here you add your seed value for the first month (0.4 in this example)
# and shift forward into the last month
sheet$shift<-c(0.4,sheet$weight[1:nrow(sheet)-1])

sheet.out<-
ddply(sheet,
      .(month=format(date,"%Y-%b")),
      summarise,
      date=date,
      inc=increment,       
      output=cumprod(ifelse(shift==0,1+increment,max(shift)*(1+increment)))  #cum product of seed val and day rets
      )

# and lastly update the last days to be the original weight
sheet.out$output<-ifelse(sheet$weight!=0,sheet$weight,sheet.out$output)

head(sheet.out)

#     month       date           inc    output
#1 2012-Apr 2012-04-01  0.0018504578 0.3234371
#2 2012-Apr 2012-04-02  0.0017762242 0.3240116
#3 2012-Apr 2012-04-03  0.0091980829 0.3269919
#4 2012-Apr 2012-04-04 -0.0023334368 0.3262289
#5 2012-Apr 2012-04-05  0.0042003969 0.3275992
#6 2012-Apr 2012-04-06  0.0005409113 0.3277764