How do I fill in dates without data on xts

Question

Say I have data xts object (data) for 30 June, 31 July, 29 August, and 30 September.

I have a date object (dates) with 30 June, 31 July, 31 August, and 30 September. When I try to do new_data <- data[dates], it just skips the August data.

I would like to use data as EOM data for these four months. What can I do to not skip the August date, and use the 29 August data for 31 August?

Nah, the xts object is much longer. It has data on every day of the month except for holidays, and August just happened to end on a holiday and so there is no data. — John, Dec 06 '21 at 09:27
In that case, could you add an example xts data set to your question and the expected output? — phiver, Dec 06 '21 at 09:32
you could instead of using your dates object (all end of months) use your xts object where you create your list based on last() grouped by month. — Merijn van Tilborg, Dec 06 '21 at 09:41

FXQuantTrader · Answer 1 · 2022-01-14T10:53:20.683

Here I'm assuming "EOM" (end of month) data means the last trading day of the month is included in your time series, which doesnt include weekends (based on your description).

You can split an xts object by month, and take the last value, assuming you're working with daily data (1 bar is 1 day)

x1 <- getSymbols("AAPL", auto.assign = FALSE)
yy2 <- do.call(rbind, lapply(split(x1, by = "months"), tail, n = 1))

tail(yy2, 10)

#AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
#2021-03-31    121.65    123.52   121.15     122.15   118323800      121.5830
#2021-04-30    131.78    133.56   131.07     131.46   109839500      130.8498
#2021-05-28    125.57    125.80   124.55     124.61    71311100      124.2423
#2021-06-30    136.17    137.41   135.87     136.96    63261400      136.5558
#2021-07-30    144.38    146.33   144.11     145.86    70382000      145.4295
#2021-08-31    152.66    152.80   151.29     151.83    86453100      151.6087
#2021-09-30    143.66    144.38   141.28     141.50    88934200      141.2938
#2021-10-29    147.22    149.94   146.41     149.80   124850400      149.5817
#2021-11-30    159.99    165.52   159.92     165.30   174048100      165.3000
#2021-12-31    178.09    179.23   177.26     177.57    64025500      177.5700

How do I fill in dates without data on xts

1 Answers1