R: Panel into timeseries data

Question

I have daily panel data and need to use the Performance Analytics Package in R. For that reason, as far as I understood, I need to convert my data into timeseries data as the Performance Analytics Package mainly works with timeseries data.

The panel data looks as following:

structure(list(Product = c("A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
"B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
"B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
"B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "C", "C", "C", 
"C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", 
"C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", 
"C", "C"), Date = c("2018-08-01", "2018-08-02", "2018-08-03", 
"2018-10-02", "2018-10-03", "2018-10-04", "2018-10-05", "2018-10-08", 
"2018-10-09", "2018-10-10", "2018-10-11", "2018-10-12", "2018-10-15", 
"2019-03-28", "2019-03-29", "2019-04-01", "2019-04-02", "2019-04-03", 
"2019-04-04", "2019-04-05", "2019-04-08", "2019-04-09", "2019-04-10", 
"2019-04-11", "2019-05-08", "2019-05-10", "2019-06-26", "2019-06-27", 
"2019-06-28", "2019-07-01", "2019-07-02", "2019-07-03", "2019-07-04", 
"2019-07-26", "2019-07-29", "2019-07-30", "2019-07-31", "2019-08-01", 
"2019-09-24", "2019-09-25", "2019-09-26", "2019-12-09", "2019-12-10", 
"2019-12-18", "2019-12-19", "2019-12-20", "2019-12-23", "2019-12-27", 
"2019-12-30", "2019-12-31", "2018-08-01", "2018-08-02", "2018-08-03", 
"2018-09-12", "2018-09-25", "2018-09-26", "2018-09-27", "2018-09-28", 
"2018-10-01", "2018-10-02", "2018-10-03", "2018-10-04", "2019-02-08", 
"2019-02-11", "2019-02-12", "2019-02-13", "2019-02-14", "2019-02-15", 
"2019-02-19", "2019-02-20", "2019-05-14", "2019-05-15", "2019-05-16", 
"2019-05-17", "2019-06-11", "2019-07-09", "2019-07-10", "2019-07-11", 
"2019-07-12", "2019-07-15", "2019-07-16", "2019-09-17", "2019-09-18", 
"2019-09-19", "2019-09-20", "2019-09-23", "2019-09-24", "2019-09-25", 
"2019-09-26", "2019-09-27", "2019-09-30", "2019-12-24", "2019-12-27", 
"2019-12-30", "2019-12-31", "2018-08-01", "2018-08-02", "2018-08-03", 
"2019-02-04", "2019-02-05", "2019-02-06", "2019-02-07", "2019-02-08", 
"2019-04-18", "2019-04-23", "2019-05-17", "2019-05-20", "2019-05-21", 
"2019-05-22", "2019-05-23", "2019-05-24", "2019-05-27", "2019-05-28", 
"2019-05-29", "2019-05-31", "2019-06-03", "2019-06-04", "2019-06-05", 
"2019-06-06", "2019-12-18", "2019-12-19", "2019-12-20", "2019-12-23", 
"2019-12-27", "2019-12-30", "2019-12-31"), `Daily Price` = c(172.32, 
168.73, 169.72, NA, 168.06, 167.26, 165.25, 162.53, 162.92, 162.3, 
NA, 158.82, 159.13, 151.04, 151.99, 154.43, 154.98, 157.76, 157.73, 
158.03, 158.28, 157.43, 157.62, 158.64, 156.24, 153.91, 151.7, 
152.45, NA, 154.64, 153.3, 154.6, 155.28, 150.5, 149.8, 146.86, 
146.82, 145.49, 145.24, 144.02, 143.76, 157.18, 156.54, 163.4, 
NA, 162.98, 163.16, 164.6, 164.36, 164.65, 26.84, 26.45, 26.59, 
26.28, 27.01, 26.84, 26.74, 26.42, 26.39, 26.29, 26.33, 26.03, 
23.34, 23.44, 23.66, 23.76, 23.71, 24.04, 24.18, 24.4, 24.12, 
NA, 24.45, 24.34, 24.54, 24.69, 24.82, 24.82, 24.86, 24.9, 24.89, 
24.75, 24.85, 25.03, 24.97, 24.75, 24.71, 24.52, 24.59, 24.65, 
24.63, 26.76, 26.95, 26.87, 26.84, 35.81, 35.37, 35.46, 30.84, 
31.15, 31.36, 30.98, 30.33, 33.51, NA, 31.98, 31.75, 32.07, 31.77, 
31.49, 31.74, 31.67, 31.67, 31.05, 30.93, 31.05, 31.46, 31.75, 
31.81, 35.07, 35.02, 35.2, 35.28, 35.81, 35.81, 35.7)), class = "data.frame", row.names = c(NA, 
-126L))

I came across the following question and answer: How to convert dataframe into time series? However, they do not use panel data but only have data in the format of a dataframe.

Could someone help me with the code on how I can convert the panel data into time series for each of the product? I tried to start with

Data.split <- split(Data, Data$Product)

By doing so, I have can seperate the different products. I could then use the xts package and create a time series, e.g. for Product A:

Data.split$A$Date <- as.Date(Data.split$A$Date)
Data.TS.A <- xts(Data.split$A$`Daily Price`, Data.split$A$Date)

In my real data, I have over 300 Products. So this would be very time-consuming. Is there a code that is much faster than this?

Which function of the R package PerformanceAnalytics do you want to use? — danlooo, Apr 04 '22 at 09:33
I would like to use the Return.annualized function as well as the SharpeRatio.annualized funciton. — remo, Apr 04 '22 at 09:37

score 0 · Answer 1 · answered Apr 04 '22 at 09:59

The xts time series object should only contain numeric values, so we can select just the price:

library(zoo)
#> 
#> Attaching package: 'zoo'
#> The following objects are masked from 'package:base':
#> 
#>     as.Date, as.Date.numeric
library(PerformanceAnalytics)
#> Loading required package: xts
#> 
#> Attaching package: 'PerformanceAnalytics'
#> The following object is masked from 'package:graphics':
#> 
#>     legend

data <- structure(list(Product = c(
  "A", "A", "A", "A", "A", "A", "A",
  "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
  "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
  "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
  "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B",
  "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
  "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B",
  "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "C", "C", "C",
  "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C",
  "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C", "C",
  "C", "C"
), Date = c(
  "2018-08-01", "2018-08-02", "2018-08-03",
  "2018-10-02", "2018-10-03", "2018-10-04", "2018-10-05", "2018-10-08",
  "2018-10-09", "2018-10-10", "2018-10-11", "2018-10-12", "2018-10-15",
  "2019-03-28", "2019-03-29", "2019-04-01", "2019-04-02", "2019-04-03",
  "2019-04-04", "2019-04-05", "2019-04-08", "2019-04-09", "2019-04-10",
  "2019-04-11", "2019-05-08", "2019-05-10", "2019-06-26", "2019-06-27",
  "2019-06-28", "2019-07-01", "2019-07-02", "2019-07-03", "2019-07-04",
  "2019-07-26", "2019-07-29", "2019-07-30", "2019-07-31", "2019-08-01",
  "2019-09-24", "2019-09-25", "2019-09-26", "2019-12-09", "2019-12-10",
  "2019-12-18", "2019-12-19", "2019-12-20", "2019-12-23", "2019-12-27",
  "2019-12-30", "2019-12-31", "2018-08-01", "2018-08-02", "2018-08-03",
  "2018-09-12", "2018-09-25", "2018-09-26", "2018-09-27", "2018-09-28",
  "2018-10-01", "2018-10-02", "2018-10-03", "2018-10-04", "2019-02-08",
  "2019-02-11", "2019-02-12", "2019-02-13", "2019-02-14", "2019-02-15",
  "2019-02-19", "2019-02-20", "2019-05-14", "2019-05-15", "2019-05-16",
  "2019-05-17", "2019-06-11", "2019-07-09", "2019-07-10", "2019-07-11",
  "2019-07-12", "2019-07-15", "2019-07-16", "2019-09-17", "2019-09-18",
  "2019-09-19", "2019-09-20", "2019-09-23", "2019-09-24", "2019-09-25",
  "2019-09-26", "2019-09-27", "2019-09-30", "2019-12-24", "2019-12-27",
  "2019-12-30", "2019-12-31", "2018-08-01", "2018-08-02", "2018-08-03",
  "2019-02-04", "2019-02-05", "2019-02-06", "2019-02-07", "2019-02-08",
  "2019-04-18", "2019-04-23", "2019-05-17", "2019-05-20", "2019-05-21",
  "2019-05-22", "2019-05-23", "2019-05-24", "2019-05-27", "2019-05-28",
  "2019-05-29", "2019-05-31", "2019-06-03", "2019-06-04", "2019-06-05",
  "2019-06-06", "2019-12-18", "2019-12-19", "2019-12-20", "2019-12-23",
  "2019-12-27", "2019-12-30", "2019-12-31"
), `Daily Price` = c(
  172.32,
  168.73, 169.72, NA, 168.06, 167.26, 165.25, 162.53, 162.92, 162.3,
  NA, 158.82, 159.13, 151.04, 151.99, 154.43, 154.98, 157.76, 157.73,
  158.03, 158.28, 157.43, 157.62, 158.64, 156.24, 153.91, 151.7,
  152.45, NA, 154.64, 153.3, 154.6, 155.28, 150.5, 149.8, 146.86,
  146.82, 145.49, 145.24, 144.02, 143.76, 157.18, 156.54, 163.4,
  NA, 162.98, 163.16, 164.6, 164.36, 164.65, 26.84, 26.45, 26.59,
  26.28, 27.01, 26.84, 26.74, 26.42, 26.39, 26.29, 26.33, 26.03,
  23.34, 23.44, 23.66, 23.76, 23.71, 24.04, 24.18, 24.4, 24.12,
  NA, 24.45, 24.34, 24.54, 24.69, 24.82, 24.82, 24.86, 24.9, 24.89,
  24.75, 24.85, 25.03, 24.97, 24.75, 24.71, 24.52, 24.59, 24.65,
  24.63, 26.76, 26.95, 26.87, 26.84, 35.81, 35.37, 35.46, 30.84,
  31.15, 31.36, 30.98, 30.33, 33.51, NA, 31.98, 31.75, 32.07, 31.77,
  31.49, 31.74, 31.67, 31.67, 31.05, 30.93, 31.05, 31.46, 31.75,
  31.81, 35.07, 35.02, 35.2, 35.28, 35.81, 35.81, 35.7
)), class = "data.frame", row.names = c(
  NA,
  -126L
))





data_xts <- xts(data[["Daily Price"]], order.by = as.Date(data$Date))
Return.annualized(data_xts, scale = 1)
#>                       [,1]
#> Annualized Return 54.61664

^{Created on 2022-04-04 by the reprex package (v2.0.0)}

Thank you danlooo for the quick response! Maybe it was a bit unclear in the question but I would like to have the annualized return for each of the three products and not the total annualized return of all products together. How do I need to adjust the code then? — remo, Apr 04 '22 at 10:07
Do `Return.annualized(data[data$Product == "A",][,"Daily Price"], scale = 1)` instead — danlooo, Apr 04 '22 at 11:11

R: Panel into timeseries data

1 Answers1