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
(m1-m0)/ mo
Where m1 and m0 are prices on the last days of current and last month respectively.This is a sample of two stocks:
其中m1和m0分别是当前和上个月的最后几天的价格。这是两种股票的样本:
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
我在quantmod的月度退货功能上也有问题，所以我将以长期格式计算退货。我还需要数据的长格式，以便进一步处理。因此，使用了来自tidyr的gather()函数，但它不起作用
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.
但它不堆叠列。谢谢您的阅读，请帮助我解决。
1 个解决方案
#1
2
The xts endpoints function can be used with either base R or dplyr to compute the monthly returns. The two versions are:
xts端点函数可以与base R或dplyr一起使用，以计算每月的收益。两个版本是:
# 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()
#1
2
The xts endpoints function can be used with either base R or dplyr to compute the monthly returns. The two versions are:
xts端点函数可以与base R或dplyr一起使用，以计算每月的收益。两个版本是:
# 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()