reading daily time series data in R using xts, error message

Question

Dear Stackoverflow community,

I have been trying to read these set of daily stock market data using xts object and been getting different types of error messages, listed below. The dataset contains 5030 observations, from 4/01/2000-22/07/2019.

1. I have checked for NAs in the dataset, and there are none
2. I have tried changing the format of the dataset from dd/mm/yyyy to yyyy/mm/dd, it doesnt seem to work
3. i checked to see if I change it to quarterly and then try to read it if it works, and it does. So I think there is a problem with the code that I am using to read the daily data.
4. The dataset is the package SystemicR author's dataset called data_stock_returns, and im trying to recreate the results before I try my own dataset. Below is the dataset and the code I tried. Would really appreciate it if someone in the community could help out with this problem. Thank You

Date	SXXP	STJ	ISP	INGA	Index
4/01/2000	0	0	-0.0209	-0.0274	1
5/01/2000	0	-0.02484	-0.0020	-0.00854	2
6/01/2000	0	0.0995	-0.0212	-0.00689	3
7/01/2000	0	0.061	0.02303	0.01961	4
10/01/2000	-0.00147	-0.0456	-0.0172	0.00119	5
..........	........	.......	.......	........	....
22/07/2019	0	-0.0127	0.00124	0.0029756	5030

df_my_data <- read.csv(('C:/Users/s/Desktop/R/intro/data/data_stock_returns.csv'), sep = ";")

str(df_my_data)

'data.frame':   5030 obs. of  74 variables:
 $ Index           : int  1 2 3 4 5 6 7 8 9 10 ...
 $ SXXP     : num  0 0 0 0 0 ...
 $ STJ   : num  0 -0.0248 0.0995 0.0611 -0.0456 ...
 $ ISP   : num  -0.021 -0.0021 -0.0212 0.023 -0.0173 ...

xts(df_my_data, order.by = as.Date(rownames(df_my_data$Date), "%d/%m/%Y"))

df_my_data$Date <- as.Date(df_my_data$Date)

I get the below 2 error message

Error in $<-.data.frame(*tmp*, Date, value = numeric(0)) : replacement has 0 rows, data has 5030

Error in xts(df_my_data, order.by = as.Date(rownames(df_my_data), "%d/%m/%Y")) : 'order.by' cannot contain 'NA', 'NaN', or 'Inf'

df_my_data$Date_xts <- as.xts(df_my_data[, -1], order.by = (df_my_data$Date))

I get another error message

Error in xts(x, order.by = order.by, frequency = frequency, ...) : order.by requires an appropriate time-based object

library(SystemicR)
l_result<- f_CoVaR_Delta_CoVaR_i_q(data_stock_returns)

Answer 1

Note that questions to SO should show the data in reproducible form using dput as discussed at the top of the r tag home page.

As this was not done and since the .csv input was not shown we will assume that the data shown is a data frame df as in the Note at the end. If that is not what you have then you will need to fix the question. If that is what you have then the problems with the code in the question are discussed in the following.

xts

Regarding converting df to an xts object we have these problems with the code in the question:

1. The use of row names. The data shown in the question does not have row names.
2. The code in the question is passing the index to both the x and order.by arguments of xts in th e first attempt. It should only be passed to the order.by argument. In the second attempt it has not converted the Date column to Date class.

The code would have worked with minor changes:

library(xts)
xts(df[-1], as.Date(df[[1]], "%d/%m/%Y"))  # df in Note at tend

however, we we can avoid picking df apart and instead use the whole object approach by reading it into a zoo object and then converting it to xts.

library(xts)
z <- read.zoo(df, format = "%d/%m/%Y") # df in Note at end
x <- as.xts(z)

f_CoVaR_Delta_CoVaR_i_q

The help file for this function says its argument is a data frame, not an xts object. Using df from the Note at the end we have

library(SystemicR)
df2 <- transform(df, Date = as.Date(Date,  "%d/%m/%Y"))
f_CoVaR_Delta_CoVaR_i_q(df2)

giving:

$CoVaR_i_q
              [,1]         [,2]          [,3]
[1,] -0.0018355914 -0.002255029  0.0002579912
[2,] -0.0008255504 -0.001121190 -0.0011822728

$Delta_CoVaR_i_q
[1] -0.001010041 -0.001133839  0.001440264

Note

df <- structure(list(Date = c("4/01/2000", "5/01/2000", "6/01/2000", 
  "7/01/2000", "10/01/2000"), SXXP = c(0, 0, 0, 0, -0.00147), STJ = c(0, 
  -0.02484, 0.0995, 0.061, -0.0456), ISP = c(-0.0209, -0.002, -0.0212, 
  0.02303, -0.0172), INGA = c(-0.0274, -0.00854, -0.00689, 0.01961, 
0.00119)), class = "data.frame", row.names = c(NA, -5L))

which looks like this:

> df
        Date     SXXP      STJ      ISP     INGA
1  4/01/2000  0.00000  0.00000 -0.02090 -0.02740
2  5/01/2000  0.00000 -0.02484 -0.00200 -0.00854
3  6/01/2000  0.00000  0.09950 -0.02120 -0.00689
4  7/01/2000  0.00000  0.06100  0.02303  0.01961
5 10/01/2000 -0.00147 -0.04560 -0.01720  0.00119

Answer 2

Using your first two rows:

df <- data.frame(Date = c('4/01/2000', '5/01/2000'), SXXP=c(0,0), STJ=c(0,-0.02484),  ISP=c(-0.0209,-0.0020),  INGA=c(-0.0274, -0.00854))
df
       Date SXXP      STJ     ISP     INGA
1 4/01/2000    0  0.00000 -0.0209 -0.02740
2 5/01/2000    0 -0.02484 -0.0020 -0.00854

I imagine you'll want to do some further analysis and want SXXP & etc as numeric

ts_working <- xts(x = df[, 2:5], order.by=(as.POSIXlt(df$Date, format = '%d/%m/%Y')))
ts_working
           SXXP      STJ     ISP     INGA
2000-01-04    0  0.00000 -0.0209 -0.02740
2000-01-05    0 -0.02484 -0.0020 -0.00854

if you put xts(x=df...

ts_working <- xts(x = df, order.by=(as.POSIXlt(df$Date, format = '%d/%m/%Y'))) ts_working
           Date        SXXP STJ        ISP       INGA      
2000-01-04 "4/01/2000" "0"  " 0.00000" "-0.0209" "-0.02740"
2000-01-05 "5/01/2000" "0"  "-0.02484" "-0.0020" "-0.00854"

which is likely not what you want, so subset your df to the $date part, and the df[, want_this:to_this_part]. You've checked for embedded NA(s). The as.POSIXlt is just one of the time formats recognized and makes no particular magic here. And while they 'look' like 'rownames', they're not

str(ts_working)
An ‘xts’ object on 2000-01-04/2000-01-05 containing:
  Data: num [1:2, 1:4] 0 0 0 -0.0248 -0.0209 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:4] "SXXP" "STJ" "ISP" "INGA"
  Indexed by objects of class: [POSIXlt,POSIXt] TZ: 
  xts Attributes:  
 NULL