0

This is my first R script ever. It's working to pull in stock quote data for some specific ticker symbols. Since data is only available for week days I don't have continuous dates in my output. How can I add Saturday and Sunday to my data and fill in the nulls with data from the prior friday?

    library(BatchGetSymbols)
#df.SP500 <- GetSP500Stocks()
tickers <- c('AAPL','AMZN','ARLO','AUY','BP','CCL','CNK','CVX','DAL','DFS','DIS','EADSY','ENB','FB','GOOGL','HD','INTC','JNJ','JPM','KHC','KO','LUV','MRNA','MSFT','NET','NFLX','NVDA','PCG','PLAY','QCOM','RCL','RDS-B','RTX','SCHB','SCHH','SHV','SIX','SO','SPHD','SPLV','SPY','SPYD','T','TGT','TSLA','VNQ','VOO','VTI','VYM','VYMI','WFC','XBI','XOM')

# set dates
#first_date <- Sys.Date()-5*365
first_date <- Sys.Date()-365
last_date <- Sys.Date()
thresh_bad_data <- 0.95   # sets percent threshold for bad data
bench_ticker <- '^GSPC'   # set benchmark as ibovespa


l_out <- BatchGetSymbols(tickers = tickers,
                         first.date = first_date,
                         last.date = last_date,
                         bench.ticker = bench_ticker,
                         thresh.bad.data = thresh_bad_data,
                         do.cache=FALSE)
result <- as.data.frame(l_out$df.tickers)
glimpse(l_out$df.tickers)
Johnson Jason
  • 671
  • 1
  • 13
  • 29
  • I actually feel like the answer is on this page but I am so new to R that I can't figure out what to do. This is my 3rd day using R so I am sorry for the noobish question https://blog.exploratory.io/populating-missing-dates-with-complete-and-fill-functions-in-r-and-exploratory-79f2a321e6b5 – Johnson Jason Aug 19 '20 at 03:49

1 Answers1

1

You can use complete to create dates which are missing and fill to fill the NA values from previous non-NA value for each ticker.

library(dplyr)
library(tidyr)

result %>%
  arrange(ticker, ref.date) %>%
  group_by(ticker) %>% 
  complete(ref.date = seq(min(ref.date), max(ref.date), by = 'day')) %>%
  fill(everything())
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • This is exactly what I want to do but for some reason it's not working. Probably because I am super noob, 3rd day only using R :( – Johnson Jason Aug 19 '20 at 04:05
  • 2
    What do you meany by not working, returns an error, gives incorrect result anything else? BTW you need to assign the result back to a new object `result1 <- result %>% arrange(ticker, ref.date)......` and check `result1`. – Ronak Shah Aug 19 '20 at 04:08
  • OMG that did the trick.. Thank you so much. That's what I was doing wrong. I was assigning it back to the same object. (years of VB has ruined me) LOL – Johnson Jason Aug 19 '20 at 04:15