4

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

tseries data:

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

I'm looking for a return data frame of the form:

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

Ideally, I'd be able to calculate from the endpoint of the prior month to the endpoint of current month, but I'm supposing partitioning by month is easier as a starting point. I'm looking at packages zoo and xts, but am still stuck. Any takers? Thanks...

Aquarius
  • 262
  • 1
  • 6
  • 20
StatsViaCsh
  • 2,600
  • 10
  • 44
  • 63

5 Answers5

2

Here's one way to do it using plyr and ddply. I use ddply sequentially, first to get the first and last rows of each month, and again to calculate the monthlyReturn. (Perhaps using xts or zoo might be easier, I am 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's the data I used to test it out:

> 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 that helps.

Ram Narasimhan
  • 22,341
  • 5
  • 49
  • 55
1

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

This calculates the monthly return from the daily price of AAPL.

*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*
igr
  • 10,199
  • 13
  • 65
  • 111
Ram
  • 11
  • 1
1

This is a pretty old thread, but for reference, here comes a data.table solution using 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>)

It's essentially a one-liner that uses the data.table::month function:

library(data.table)

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

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

PavoDive
  • 6,322
  • 2
  • 29
  • 55
1

1) no packages Try this:

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 "ts" class, if desired. Assuming no missing months:

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

giving:

            Jan        Feb
2000 0.01000000 0.07843137

2) It's a bit easier if you use the zoo or xts time series packages. fmt and ret are from above:

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 a data.frame DF then the read.zoo statement could be replaced with z <- read.zoo(DF, format = fmt) or omit the format arg if the first column is of "Date" class.

If "ts" class were desired then use as.ts(z.ret)

Note: The input Lines is:

Lines <- "1/1/2000 10.00
1/31/2000 10.10
2/1/2000 10.20
2/28/2000 11.00"
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
0

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

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")
KDJ
  • 83
  • 1
  • 5