I would like to compute the range and the dates of the maximum price and low price of of each variable that is stored in xts format and store the results in a data frame. The result that I look for is a data frame that will contain the variable name,max value date,max value, min value date,min value. I've imported two stocks data by using quantmod package and wrote a function to compute the ranges (yet without the dates of maximum price and low price) but with no succees.
d<-getSymbols(c("ZTS","ZX") , src = 'yahoo', from = '2015-01-01', auto.assign = T)
d<-cbind(ZTS,ZX)
head(d)
ZTS.Open ZTS.High ZTS.Low ZTS.Close ZTS.Volume ZTS.Adjusted ZX.Open ZX.High ZX.Low ZX.Close
2015-01-02 43.46 43.70 43.07 43.31 1784200 43.07725 1.40 1.40 1.21 1.24
2015-01-05 43.25 43.63 42.97 43.05 3112100 42.81864 1.35 1.38 1.24 1.32
2015-01-06 43.15 43.36 42.30 42.63 3977200 42.40090 1.28 1.29 1.22 1.22
2015-01-07 43.00 43.56 42.98 43.51 2481800 43.27617 1.24 1.34 1.24 1.29
2015-01-08 44.75 44.87 44.00 44.18 3121300 43.94257 1.26 1.28 1.17 1.18
2015-01-09 44.06 44.44 43.68 44.25 2993200 44.01220 1.30 1.39 1.22 1.27
ZX.Volume ZX.Adjusted
2015-01-02 20400 1.24
2015-01-05 43200 1.32
2015-01-06 16700 1.22
2015-01-07 6200 1.29
2015-01-08 17200 1.18
2015-01-09 60200 1.27
s<- for (i in names(d[,-1])) function(x) {max(x);min(x) }
> s
NULL
str(d)
An ‘xts’ object on 2015-01-02/2015-08-13 containing:
Data: num [1:155, 1:12] 43.5 43.2 43.2 43 44.8 ...
- attr(*, "dimnames")=List of 2
..$ : NULL
..$ : chr [1:12] "ZTS.Open" "ZTS.High" "ZTS.Low" "ZTS.Close" ...
Indexed by objects of class: [Date] TZ: UTC
xts Attributes:
List of 2
$ src : chr "yahoo"
$ updated: POSIXct[1:1], format: "2015-08-14 18:35:14"
x
I can't tell if you date column is the rownames or an actual column, but the code below should produce your result if the date column is called date in the dataframe.
do.call(rbind, apply(d[,-1], 2, function(col) {
max_ind <- which.max(col)
min_ind <- which.min(col)
list(max=col[max_ind], max_date=d$date[max_ind], min=col[min_ind],
min_date=d$date[min_ind])
}))
and if the date column is the rownames
do.call(rbind, apply(d, 2, function(col) {
max_ind <- which.max(col)
min_ind <- which.min(col)
list(max=col[max_ind], max_date=as.character(index(d))[max_ind], min=col[min_ind],
min_date=as.character(index(d))[min_ind])
}))
The code does an apply over columns finding the index of the maximum and minimum, then returns the values and dates corresponding to these.