Calculating monthly returns in long format

Question

I have daily closing prices data set in long format and I want to calculate monthly returns(arithmetic). It is calculated as

(m1-m0)/m0

Where m1 and m0 are prices on the last days of current and last month respectively.This is a sample of two stocks:

dput(q1) 
structure(list(Date = structure(c(13027, 13028, 13031, 13032,
13034, 13035, 13038, 13039, 13040, 13041, 13042, 13045, 13046,  13047,
13048, 13049, 13052, 13053, 13054, 13055, 13056, 13059,  13060, 13061,
13062, 13063, 13066, 13067, 13069, 13070, 13073,  13074, 13075, 13076,
13077, 13080, 13081, 13082, 13083, 13084,  13087, 13088, 13089, 13094,
13095, 13096, 13097, 13098, 13101,  13103, 13104, 13105, 13108, 13109,
13110, 13111, 13112, 13113,  13115, 13116, 13117, 13118, 13119, 13122,
13123, 13124, 13125,  13126, 13129, 13130, 13131, 13132, 13133, 13136,
13137, 13138,  13139, 13140, 13143, 13144, 13145, 13146, 13147, 13150,
13151,  13152, 13153, 13154, 13157, 13158, 13160, 13161, 13164, 13165,
13166, 13167, 13168, 13171, 13172, 13173, 13175, 13178, 13179,  13180,
13181, 13182, 13185, 13186, 13187, 13189, 13192, 13193,  13194, 13195,
13196, 13199, 13200, 13201, 13202, 13203, 13206,  13207, 13208, 13209,
13210, 13213, 13214, 13215, 13216, 13217,  13220, 13221, 13223, 13224,
13227, 13228, 13229, 13230, 13231,  13234, 13235, 13236, 13237, 13238,
13241, 13242, 13243, 13245,  13248, 13250, 13251, 13255, 13256, 13257,
13258, 13259, 13262,  13263, 13264, 13265, 13266, 13267, 13270, 13271,
13272, 13273,  13276, 13277, 13278, 13279, 13280, 13283, 13284, 13285,
13286,  13287, 13290, 13291, 13292, 13293, 13294, 13297, 13298, 13299,
13300, 13301, 13304, 13305, 13306, 13307, 13308, 13311, 13312,  13313,
13314, 13315, 13318, 13319, 13320, 13321, 13322, 13324,  13325, 13326,
13327, 13328, 13329, 13332, 13333, 13334, 13335,  13336, 13339, 13340,
13341, 13342, 13343, 13346, 13347, 13348,  13349, 13350, 13353, 13354,
13355, 13356, 13357, 13360, 13361,  13362, 13363, 13364, 13367, 13368,
13369, 13370, 13371, 13374,  13376, 13377, 13378, 13381, 13382, 13383,
13384, 13385, 13388,  13389, 13390, 13391, 13027, 13028, 13031, 13032,
13034, 13035,  13038, 13039, 13040, 13041, 13042, 13045, 13046, 13047,
13048,  13049, 13052, 13053, 13054, 13055, 13056, 13059, 13060, 13061,
13062, 13063, 13066, 13067, 13069, 13070, 13073, 13074, 13075,  13076,
13077, 13080, 13081, 13082, 13083, 13084, 13087, 13088,  13089, 13094,
13095, 13096, 13097, 13098, 13101, 13103, 13104,  13105, 13108, 13109,
13110, 13111, 13112, 13113, 13115, 13116,  13117, 13118, 13119, 13122,
13123, 13124, 13125, 13126, 13129,  13130, 13131, 13132, 13133, 13136,
13137, 13138, 13139, 13140,  13143, 13144, 13145, 13146, 13147, 13150,
13151, 13152, 13153,  13154, 13157, 13158, 13160, 13161, 13164, 13165,
13166, 13167,  13168, 13171, 13172, 13173, 13175, 13178, 13179, 13180,
13181,  13182, 13185, 13186, 13187, 13189, 13192, 13193, 13194, 13195,
13196, 13199, 13200, 13201, 13202, 13203, 13206, 13207, 13208,  13209,
13210, 13213, 13214, 13215, 13216, 13217, 13220, 13221,  13223, 13224,
13227, 13228, 13229, 13230, 13231, 13234, 13235,  13236, 13237, 13238,
13241, 13242, 13243, 13245, 13248, 13250,  13251, 13255, 13256, 13257,
13258, 13259, 13262, 13263, 13264,  13265, 13266, 13267, 13270, 13271,
13272, 13273, 13276, 13277,  13278, 13279, 13280, 13283, 13284, 13285,
13286, 13287, 13290,  13291, 13292, 13293, 13294, 13297, 13298, 13299,
13300, 13301,  13304, 13305, 13306, 13307, 13308, 13311, 13312, 13313,
13314,  13315, 13318, 13319, 13320, 13321, 13322, 13324, 13325, 13326,
13327, 13328, 13329, 13332, 13333, 13334, 13335, 13336, 13339,  13340,
13341, 13342, 13343, 13346, 13347, 13348, 13349, 13350,  13353, 13354,
13355, 13356, 13357, 13360, 13361, 13362, 13363,  13364, 13367, 13368,
13369, 13370, 13371, 13374, 13376, 13377,  13378, 13381, 13382, 13383,
13384, 13385, 13388, 13389, 13390,  13391), class = "Date"), Firm =
c("ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE",  "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE",
"ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE", "ADE",  "ADE", "ADE",
"ADE", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP", "AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP",  "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP", "AJP",
"AJP"), Price = c(7.05, 6.81, 6.98, 6.82, 6.81, 6.75, 6.73, 6.71, 
6.6, 6.62, 6.71, 7.01, 6.82, 6.59, 6.25, 6.29, 6.48, 6.43, 6.45, 
6.39, 6.3, 6.39, 6.61, 6.62, 6.52, 6.48, 6.39, 6.31, 6.31, 6.21, 
6.15, 6.14, 6.02, 5.88, 5.91, 5.91, 5.91, 5.95, 5.84, 5.73, 5.83, 
5.95, 5.94, 6.01, 6.05, 5.95, 5.94, 6.02, 6.06, 6.1, 6.52, 6.42, 
6.36, 6.27, 6.29, 6.26, 6.26, 6.28, 6.41, 6.46, 6.34, 6.34, 6.27, 
6.38, 6.46, 6.37, 6.34, 6.38, 6.39, 6.33, 6.31, 6.22, 6.23, 6.28, 
6.27, 6.18, 6.17, 6.09, 5.99, 6.1, 6.01, 6, 5.9, 5.99, 6.06, 
6.1, 6.05, 6.1, 6.11, 6.05, 6.05, 6.03, 6, 6, 6.01, 6.07, 6.14, 
6.02, 6.06, 6.01, 6.01, 6.03, 6, 5.98, 5.81, 5.78, 5.95, 5.86, 
5.77, 5.78, 5.81, 5.8, 5.8, 6, 5.8, 5.75, 5.79, 5.81, 5.75, 5.75, 
5.76, 5.76, 5.79, 5.77, 5.74, 5.71, 5.63, 5.66, 5.69, 5.65, 5.63, 
5.61, 5.58, 5.58, 5.63, 5.58, 5.52, 5.53, 5.76, 5.96, 5.85, 5.94, 
5.88, 5.99, 6.43, 6.38, 6.37, 6.41, 6.31, 6.11, 5.93, 6.02, 6.11, 
6.07, 6.08, 6.02, 5.99, 6, 6.06, 6.02, 5.98, 6.07, 7.29, 8.75, 
10.5, 11.55, 12.53, 12.69, 13.96, 15.36, 16.13, 16.94, 17.66, 
18.55, 18.97, 18.18, 17.28, 16.42, 15.6, 14.88, 15.62, 16.41, 
17.23, 18.09, 19, 18.47, 17.55, 16.68, 15.85, 15.06, 14.3, 13.59, 
12.91, 12.27, 11.66, 12.16, 12.77, 13.41, 14.08, 13.56, 13.67, 
13.86, 13.17, 12.62, 12.45, 12.47, 13, 12.51, 12.36, 12.55, 12.47, 
12.06, 11.77, 11.81, 12.19, 12.32, 12.03, 11.62, 11.43, 11.05, 
11.18, 10.72, 10.74, 10.53, 10.84, 11.39, 11.53, 11.51, 11.24, 
11.36, 11.62, 11.53, 11.45, 11.73, 11.81, 12.96, 13.36, 13.57, 
14.09, 13.41, 13.1, 12.72, 12.52, 12.26, 12, 12.18, 12.52, 12.48, 
12.2, 12, 13.54, 13.97, 13.67, 13.32, 13.24, 12.79, 12.98, 12.73, 
12.45, 12.69, 12.71, 12.61, 12.34, 11.95, 11.36, 11.11, 11.67, 
11.57, 11.61, 11.31, 11.25, 11.24, 11.44, 11.43, 11.21, 11.25, 
10.95, 10.63, 10.59, 10.38, 9.86, 9.91, 9.09, 9.16, 9.17, 9.16, 
9.14, 9.14, 8.96, 8.66, 8.71, 8.86, 8.84, 9.19, 9.46, 9.31, 9.49, 
9.73, 10.71, 11.03, 10.76, 10.73, 10.99, 10.84, 10.81, 10.94, 
10.83, 10.76, 10.61, 10.51, 11.13, 10.91, 10.67, 10.59, 10.68, 
10.62, 10.81, 10.83, 11.03, 11.06, 10.67, 10.59, 10.46, 10.55, 
10.35, 10.41, 10.63, 10.54, 10.23, 10.14, 10.14, 10.2, 10.4, 
10.67, 10.79, 10.97, 10.8, 10.96, 11.4, 11.27, 11.17, 11.81, 
12.07, 11.3, 11.16, 11.53, 11.31, 11.23, 11.24, 11.06, 11.67, 
11.49, 11.23, 10.91, 10.81, 10.81, 10.84, 10.61, 10.68, 10.75, 
11.08, 11.07, 10.78, 10.79, 10.58, 10.48, 10.46, 10.33, 10.35, 
10.31, 10.3, 10.09, 10.1, 9.91, 9.86, 9.9, 9.76, 9.83, 9.58, 
9.61, 9.73, 9.59, 9.48, 9.16, 9.03, 8.95, 9.12, 9.08, 9.02, 9.11, 
8.79, 8.67, 9.14, 9.75, 10.3, 10.19, 10.35, 9.93, 9.98, 9.85, 
9.61, 9.66, 9.71, 9.71, 9.93, 9.81, 9.99, 9.77, 9.69, 9.81, 10.71, 
12.85, 12.76, 12.26, 11.94, 12.51, 12.22, 12.23, 12.32, 11.79, 
11.75, 10.94, 11.09, 11.53, 10.7, 10.3, 9.58, 9.96, 9.9, 9.75, 
10.01, 10.13, 10.05, 9.61, 9.4, 9.04, 9.03, 8.77, 7.89, 7.35, 
8.23, 8.2, 8.05, 8.07, 8.35, 8.7, 9.16, 9.24, 9.37, 9.4, 9.7, 
9.66, 9.37, 9.07, 9.35, 9.52, 9.42, 9.64, 9.47, 9.51, 9.36, 9.06, 
10.49, 10.24, 9.85, 9.93, 9.7, 9.43, 9.57, 9.33, 9.6, 9.43, 9.63, 
9.72, 9.83, 9.69, 9.78, 9.44, 9.39, 9.27, 9.35, 9.21, 9.19, 9.3, 
9.45, 9.73, 10.61, 10.82, 10.62, 10.27, 10.41, 10.31, 10.46, 
10.21, 10.02, 10.24, 10.13, 10.09, 10.07, 9.94)), .Names = c("Date",  "Firm", "Price"), row.names = c(NA, -500L), class = c("tbl_df", 
"tbl", "data.frame"))

I don't know how to do this in long format so I converted it into wide format and got the month end prices

q<-  spread(q1,Firm,Price)  q$Date<-as.Date(q$Date)  rownames(q) = q[[1]]  q<-as.xts(q)  q.m.r<-q[ endpoints(q, on="months", k=1), ]

I also had issues with monthlyReturn function from quantmod so I will calculate returns in long format. I also need the data into the long format for further processing. So, used gather() function from tidyr but it did not work

Error in UseMethod("gather_") : no applicable method for 'gather_' applied to an object of class "c('xts', 'zoo')"

I also found this but I don't know what to put after Price

xts2df <- function(x) {data.frame(date=index(x), coredata(x))}  g<-gather(xts2df(q.m.r), Firm, Price, ?)

I also went through this question and applied

m <- melt(q.m.r,id="Date",variable_name="Firm")  names(m) <- sub("value","Price",names(m))

But it does not stack the columns. Thank you for reading and kindly help me to solve it.

WaltS · Accepted Answer · 2016-06-17T18:06:13.090

2

The xts endpoints function can be used with either base R or dplyr to compute the monthly returns. The two versions are:

# Use xts and base R
  library(xts)
  q2 <- within(q1[endpoints(q1$Date, on="months"),], 
               Return <- ave(Price, Firm, FUN=function(x) c(NA, (diff(x)/head(x,-1)))))[,c("Date", "Firm", "Return")]
  q2 <- na.omit(q2)


# Use xts and dplyr
  library(dplyr)
  library(xts)
  q3 <- q1[endpoints(q1$Date, on="months"),] %>%
                             group_by(Firm) %>%
                             transmute(Date=Date, Return = c(NA, (diff(Price)/head(Price,-1)))) %>%
                             na.omit()

edited Jun 17 '16 at 18:06

answered Jun 17 '16 at 15:01

WaltS

5,410
2
18
24

thank you. I have checked the returns in excel and it matches with the ones in the q2 and its fine. But WHY the prices are there in Index series and value cloumns? Please make it returns only. Thanks again – Polar Bear Jun 17 '16 at 16:13
I've posted simpler versions which don't include the prices in the results. – WaltS Jun 17 '16 at 18:07
Thank you. This is what I need. Just to learn: why was arithmetic=FALSE in diff function?. – Polar Bear Jun 17 '16 at 18:18
1

The earlier version had an `xts` time series object as the argument for `diff` so that the function `diff.xts` was actually used. If `arithmetic = FALSE` in `diff.xts`, the geometric difference, `x[i]/x[i-1]`, is calculated. The financial return is then just `geometric difference - 1`. The base R `diff` function doesn't have this feature so the code has to explicitly divide the `diff` result,`x[i]- x[i-1]`, by `x[i-1]` to calculate the financial return.. – WaltS Jun 17 '16 at 18:56

Calculating monthly returns in long format

1 Answers1