How do I retain all the columns while using tq_transmute() function?

Question

I am trying to replicate a trading strategy and backtest in R. However, I am having a slight problem with the tq_transmute() function. Any help would be appreciated.

So, I have the following code that I have written until now:

       #Importing the etfs data
        symbols<- c("SPY","XLF","XLE")
        start<-as.Date("2000-01-01")
        end<- as.Date("2018-12-31")
        
        price_data<- lapply(symbols, function(symbol){
              etfs<-as.data.frame(getSymbols(symbol,src="yahoo", from=start, to= end, 
                                             auto.assign = FALSE))
              colnames(etfs)<- c("Open", "High","Low","Close","volume","Adjusted")
              etfs$Symbol<- symbol
              etfs$Date<- rownames(etfs)
              etfs
            })
    
     # Next, I used do.call() with rbind() to combine the data into a single data frame   
        
        etfs_df<- do.call(rbind, price_data)
    
        #This because of POSIXct error
        daily_price<- etfs_df %>%
                  mutate(Date=as.Date(Date, frac=1)) 
# I have deleted some columns of the table as my work only concerned the "Adjusted" column. 
#So, until now we have:
 
        head(daily_price)
    
          Adjusted Symbol       Date
        1 98.14607    SPY 2000-01-03
        2 94.30798    SPY 2000-01-04
        3 94.47669    SPY 2000-01-05
        4 92.95834    SPY 2000-01-06
        5 98.35699    SPY 2000-01-07
        6 98.69440    SPY 2000-01-10
    
        #Converting the daily adjusted price to monthly adjusted price
    
        monthly_price<- 
      tq_transmute(daily_price,select = Adjusted, mutate_fun = to.monthly, indexAt = "lastof")
    
    head(monthly_price)
    
    # And now, I get the following table: 
    
    # A tibble: 6 x 2
      Date       Adjusted
      <date>        <dbl>
    1 2000-01-31     16.6
    2 2000-02-29     15.9
    3 2000-03-31     17.9
    4 2000-04-30     17.7
    5 2000-05-31     19.7
    6 2000-06-30     18.6

So, as you can see, the Date and Adjusted prices have been successfully converted to monthly figures but my Symbol column has disappeared. Could anyone please tell me why did that happen and how do I get it back?

Thank you.

score 0 · Answer 1 · answered Feb 22 '21 at 01:38

group the data by Symbol and apply tq_transmute.

library(dplyr)
library(quantmod)
library(tidyquant)

monthly_price <- daily_price %>%
                  group_by(Symbol) %>%
                  tq_transmute(daily_price,select = Adjusted, 
                               mutate_fun = to.monthly, indexAt = "lastof")

#  Symbol Date       Adjusted
#   <chr>  <date>        <dbl>
# 1 SPY    2000-01-31     94.2
# 2 SPY    2000-02-29     92.7
# 3 SPY    2000-03-31    102. 
# 4 SPY    2000-04-30     98.2
# 5 SPY    2000-05-31     96.6
# 6 SPY    2000-06-30     98.5
# 7 SPY    2000-07-31     97.0
# 8 SPY    2000-08-31    103. 
# 9 SPY    2000-09-30     97.6
#10 SPY    2000-10-31     97.2
# … with 674 more rows

score 0 · Answer 2 · answered Nov 17 '22 at 22:58

I would do it like this:

symbols <- c("SPY", "XLF", "XLE")
start <- as.Date("2000-01-01")
end <- as.Date("2018-12-31")

# Environment to hold data
my_data <- new.env()

# Tell getSymbols() to load the data into 'my_data'
getSymbols(symbols, from = start, to = end, env = my_data)

# Combine all the adjusted close prices into one xts object
price_data <- Reduce(merge, lapply(my_data, Ad))

# Remove "Adjusted" from column names
colnames(price_data) <- sub(".Adjusted", "", colnames(price_data), fixed = TRUE)

# Get the last price for each month
monthly_data <- apply.monthly(price_data, last)

# Convert to a long data.frame
long_data <- fortify.zoo(monthly_data,
    names = c("Date", "Symbol", "Adjusted"), melt = TRUE)

How do I retain all the columns while using tq_transmute() function?

2 Answers2