How to calculate the monthly rate of change from the daily time series in R?

Question

I'm starting to wet my feet with R, and I'm new to time series concepts. Can someone point me in the right direction to calculate the monthly change% based on the daily data point? I want a change between the first and last data points of each month. For instance:

tseries data:

1/1/2000 10.00 ... 1/31/2000 10.10 2/1/2000 10.20 ... 2/28/2000 11.00 

I am looking for a return frame of form data:

 1/31/2000 .01 2/28/2000 .0784 

Ideally, I could calculate from the endpoint of the previous month to the endpoint of the current month, but I assume that breaking down by months is easier as a starting point. I am looking at zoo and xts packages, but I'm still stuck. Any takers? Thanks...

Answer 1

Here is one way to do this with plyr and ddply . I use ddply sequentially, first to get the first and last rows of each month, and again to calculate monthReturn. (Perhaps using xts or zoo might be easier, I'm not sure.)

 #Using plyr and the data in df df$Date <- as.POSIXlt(as.Date(df$Date, "%m/%d/%Y")) df$Month <- (df$Date$mon + 1) #0 = January sdf <- df[,-1] #drop the Date Column, ddply doesn't like it library("plyr") #this function is called with 2 row data frames monthlyReturn<- function(df) { (df$Value[2] - df$Value[1])/(df$Value[1]) } adf <- ddply(sdf, .(Month), function(x) x[c(1, nrow(x)), ]) #get first and last values for each Month mon.returns <- ddply(adf, .(Month), monthlyReturn) 

Here is the data I used to verify:

 > df Date Value 1 1/1/2000 10.0 2 1/31/2000 10.1 3 2/1/2000 10.2 4 2/28/2000 11.0 5 3/1/2000 10.0 6 3/31/2000 24.1 7 5/10/2000 510.0 8 5/22/2000 522.0 9 6/04/2000 604.0 10 7/03/2000 10.1 11 7/30/2000 7.2 12 12/28/2000 11.0 13 12/30/2000 3.0 > mon.returns Month V1 1 1 0.01000000 2 2 0.07843137 3 3 1.41000000 4 5 0.02352941 5 6 0.00000000 6 7 -0.28712871 7 12 -0.72727273 

Hope this helps.

Answer 2

Here is another way to do this (using the quantmod package):

It calculates the monthly income from the daily AAPL price.

 *library(quantmod) # load the quantmod package getSymbols("AAPL") # download daily price for stock AAPL monthlyReturn = periodReturn(AAPL,period="monthly") monthlyReturn2014 = periodReturn(AAPL,period="monthly",subset='2014:') # for 2014* 

Answer 3

This is a pretty old thread, but for reference: here is the data.table solution using the same data as @Ram:

 structure(list(Date = structure(c(10957, 10987, 10988, 11015, 11017, 11047, 11087, 11099, 11112, 11141, 11168, 11319, 11321), class = "Date"), Value = c(10, 10.1, 10.2, 11, 10, 24.1, 510, 522, 604, 10.1, 7.2, 11, 3)), .Names = c("Date", "Value"), row.names = c(NA, -13L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x00000000001b0788>) 

This is essentially a single line that uses the data.table::month function:

 library(data.table) setDT(df)[ , diff(Value) / Value[1], by= .(month(Date))] 

This will result in a change with respect to the first recorded day in each month. If the change relative to last day is preferred, then the expression in the middle should be changed to diff(Value) / Vale[2] .

Answer 4

1) no packages Try the following:

 DF <- read.table(text = Lines) fmt <- "%m/%d/%Y" ym <- format(as.Date(DF$V1, format = fmt), "%Y-%m") ret <- function(x) diff(range(x))/x[1] ag <- aggregate(V2 ~ ym, DF, ret) 

giving:

 > ag ym V2 1 2000-01 0.01000000 2 2000-02 0.07843137 

We could convert this to the "ts" class if necessary. Assuming no missing months:

 ts(ag$V2, start = 2000, freq = 12) 

giving:

  Jan Feb 2000 0.01000000 0.07843137 

2) This is a little easier if you use zoo or xts time series packages. fmt and ret are on top:

 library(zoo) z <- read.zoo(text = Lines, format = fmt) z.ret <- aggregate(z, as.yearmon, ret) 

giving:

 > z.ret Jan 2000 Feb 2000 0.01000000 0.07843137 

If you already have data.frame DF , then the read.zoo can be replaced with z <- read.zoo(DF, format = fmt) or omit the format argument if the first column has the "Date" class.

If the "ts" class was desired, use as.ts(z.ret)

Note: Input Lines :

 Lines <- "1/1/2000 10.00 1/31/2000 10.10 2/1/2000 10.20 2/28/2000 11.00" 

Answer 5

The ROC function in the TTR packet will do this. You can use to.monthly or endpoints () ( From daily time series to weekly time series in the R xts object ) first if you look only at monthly behavior.

 library(TTR) # data.monthly <- to.monthly( data, indexAt='periodEnd' ) # if OHLC data # OR data.monthly <- data[ endpoints(data, on="months", k=1), ] data.roc <- ROC(data.monthly, n = 1, type = "discrete")