to.period function keeps the 'missing values'

Question

Very new to R, so I hope I don't frustrate anyone. Putting together pieces from online searches and using quantmode and purr packages, I have the following code to create an xts data frame called stocks:

symbols <- c("RYCVX","AJA","IEMG")
start <- as.Date("2006-06-22")
end <- as.Date("2020-07-30")

# collect adjusted column of all symbols in one matrix
stocks <- getSymbols(symbols,src = "yahoo", from = start, to = end, 
                     auto.assign = TRUE, 
                     warnings = FALSE) %>% 
  map(~Ad(get(.))) %>% 
  reduce(merge) %>%
  `colnames<-`(symbols)

This is daily, but I want to have a monthly matrix, yet still keep the NA fields. I tried this line of code:

mstocks <- to.monthly(stocks, indexAt = "last", OHLC = FALSE)

but my resulting data frame is shrunk down to the symbol with the least amount of data, since any row with any missing value is omitted, so I end losing data on the more historically rich symbol.

Is there a way I could keep the missing values and have monthly data that, like my daily data, has rows where one symbol is NA?

So here is what I get:

                    RYCX        AJA        IEMG
2018-12-30        29.3045     4.5523     33.2045     <- first date all symbols have data
...
2020-07-30        34.2344     5.6664     12.2234

What I get now with Walts's help:

                     V1
2006-06-30           NA
...
2020-07-29        52.66000

What I need:

                   RYCX        AJA        IEMG
2006-06-30        29.3045      NA           NA
....
2020-07-30        34.2344     5.6664     12.2234

All prices are made up

Can you give an example of what you get versus what you want? — Michelle, Aug 01 '20 at 05:07

WaltS · Accepted Answer · 2020-08-05T11:33:13.100

0

I'll assume that by monthly data, you mean the adjusted-close price on the last trading day of the month. To simply the code a bit, I've used auto.assign = FALSE so that getSymbols returns an xts time-series object rather than placing it in the environment. I've also used the function setNames rather than colnames<-(symbols) which works but is somewhat opaque. To convert to monthly, use apply.monthly(last) which takes the last day of each month in the time series. Data for all months is returned including those with NA in some of the time series.

library(tidyverse)
library(quantmod) 
symbols <- c("RYCVX","AJA","IEMG")
start <- as.Date("2006-06-22")
end <- as.Date("2020-07-30") 
stocks <- symbols %>% map( ~Ad(getSymbols(.x, src = "yahoo", from = start, to = end,
                             auto.assign = FALSE,
                             warnings = FALSE))) %>%
        reduce(merge) %>% 
        setNames(symbols) %>% 
        apply.monthly(last)

which gives:

> stocks
                 RYCVX    AJA     IEMG
 2006-06-30  21.901295  5.810       NA
 2006-07-31  21.892862  6.260       NA
 2006-08-31  22.643713 18.400       NA
 2006-09-29  23.732025 19.250       NA
 2006-10-31  25.284351  6.160       NA
 2006-11-30  25.908657  6.960       NA
 2006-12-29  26.817636 20.900       NA

edited Aug 05 '20 at 11:33

answered Aug 01 '20 at 16:30

WaltS

5,410
2
18
24

Thanks. Your trick certainly cleaned up my environment, but the result is only one column. I need a column for each symbol – Siamak Nayeri Aug 04 '20 at 20:54
I've updated my post to include the first few lines of the output variable `stocks`. I don't see how you would be getting only one column with the default column name `V1`. Are you making any changes to the code I posted? – WaltS Aug 05 '20 at 11:36
If you like this answer, you might click accept to let others know that it's a solution to the problem you posted – WaltS Aug 08 '20 at 17:40
To this day, sometimes your code shrunk my results to one column. I figured it must be in "apply.monthly(last)" part of the code, so with help from [here](https://stackoverflow.com/questions/7286585/how-to-use-apply-monthly-in-a-zoo-object-with-several-columns) , I modified the line to apply.monthly(apply,2,last) and now it works every time. Don't ask me how – Siamak Nayeri Sep 01 '20 at 00:07

to.period function keeps the 'missing values'

1 Answers1