I can't use to.weekly() to bind more than a financial series

Question

I have tried to transform daily prices to weekly prices of more than one financial asset and then put them together, but I cannot.

When I join both weekly series, there are days that do not coincide, causing that the weekly frequency is not respected.

Example:

I download the data using quantmod

   getSymbols('^FCHI', from = '2005-01-06', to= "2022-03-18")
   Y<-Cl(to.weekly(FCHI))

   getSymbols("^GDAXI", from = '2005-01-06', to= "2022-03-18")
   O<-Cl(to.weekly(GDAXI))

   i<-cbind(Y,O)

I get this data:

structure(c(3803.719971, 3794.439941, NA, 3912.72998, NA, 3936.330078, 
4045.139893, 3954.379883, 3820.780029, 3739.459961, 5756.290039, 
5831.209961, 5957.439941, NA, 5957.430176, NA, 6037.609863, 5875.970215, 
5695.319824, 5608.790039), class = c("xts", "zoo"), src = "yahoo", updated = structure(1648424873.12071, class = c("POSIXct", 
"POSIXt")), na.action = structure(c(528L, 1120L, 2567L), class = "omit", index = c(1325462400, 
1398902400, 1577232000)), index = structure(c(1260489600, 1261094400, 
1261526400, 1261612800, 1262131200, 1262217600, 1262908800, 1263513600, 
1264118400, 1264723200), tzone = "UTC", tclass = "Date"), .Dim = c(10L, 
2L), .Dimnames = list(NULL, c("FCHI.Close", "GDAXI.Close")))


              FCHI.close    GDAXI.close
2009-12-11    3803.72       5756.29
2009-12-18    3794.44       5831.21
2009-12-23    NA            5957.44
2009-12-24    3912.73       NA
2009-12-30    NA            5957.43
2009-12-31    3936.33       NA

Even if I replace these missings with the last value, I would have a problem, since the weekly frequency would be lost since two successive dates are generated

How can i fix this? Thanks in advance and sorry for my bad english

Answer 1

Stock market databases tend to have missing values and missing dates for a number of reasons. For France and Germany these tend to be days, like easter monday, second Christmas day (boxing day) or any other holiday where the stockmarket is closed locally.

Especially before all the European financial markets were synchronized for opening days. Second missing dates are dates that are not recorded in the database, like Christmas day. Now if this wasn't a Friday you wouldn't notice, but the to.period function takes the last day it finds in a week. If these are different between timeseries, you have different last days of the week for that week.

This shows itself in December 2009 where you have 23 (DAX) and 24 (CAC40) as the last day of the week. Merging this will give you a 2 days for that week as seen in your example.

So there are 2 things you need to do. First synchronize all the dates in the timeseries, a.k.a. insert all missing dates in all timeseries. Secondly, fill the NA's with the information from the previous day(s) and then you can use the to.period functions.

Below is some code to handle this:

#Create date sequence
dates <- seq.Date(from = as.Date('2005-01-06'), to= as.Date("2022-03-18"), by = 1)
# remove Saturday and Sunday
dates <- dates[lubridate::wday(dates, week_start = 1) %in% (1:5)]

# merge dates with timeseries
FCHI <- merge(FCHI, dates)
GDAXI <- merge(GDAXI, dates)

# fill in the NA's with the previous value
FCHI <- na.locf(FCHI)
GDAXI <- na.locf(GDAXI)

Y <- Cl(to.weekly(FCHI))
O <- Cl(to.weekly(GDAXI))

#merge CAC40 and DAX
i <- merge(Y, O)

# data for December 2009
i["2009-12"]
           FCHI.Close GDAXI.Close
2009-12-04    3846.62     5817.65
2009-12-11    3803.72     5756.29
2009-12-18    3794.44     5831.21
2009-12-25    3912.73     5957.44

As you can see, for December 2009 the dates are now aligned. You might argue dat 2009-12-25 should be 2009-12-24, in that case adjust the dates sequence by removing the 25th of December(s) before doing the rest.