1

I'm trying to reproduce this example which adjusts stock prices for dividends using the tidyquant framework.

Here is the original example:

library(quantmod)
library(tidyquant)
library(timetk)
  
SPY.Close <- Cl(getSymbols("SPY", auto.assign=FALSE))
SPY.Div <- getDividends("SPY", auto.assign=FALSE)

# Within xts framework
SPY <- merge(SPY.Close, SPY.Div)

# now adjust close for dividends
ratios <- adjRatios(dividends=SPY[,"SPY.div"], close=SPY[,"SPY.Close"])
SPY$SPY.Adjusted <- (ratios$Split * ratios$Div) * SPY$SPY.Close
# only keep dates from the original object
SPY <- SPY[index(SPY.Close), ]

Here is my attempt to do this in tidyquant:

#convert xts to tibble
spy.tbl <- tk_tbl(merge(SPY.Close, SPY.Div), preserve_index = TRUE)

#add a splits placeholder because adjRatios() complains if its not there.
spy.tbl$SPY.splits <- 0

spy.adj <- spy.tbl %>% 
  tq_mutate(
    select = c(index, SPY.Close, SPY.div, SPY.splits),
    mutate_fun = adjRatios,
    splits = SPY.splits,
    dividends = SPY.div,
    close = SPY.Close
  )

but this gives the error: Error in fun_transmute(., ...) : unused argument (.)

I've tried various combinations of arguments, but I can't seem to make it work.

LeeAnn
  • 51
  • 4

2 Answers2

1

Just in case anyone searches for this topic, I've solved my own problem with the following code. The benefit of this is that it is done in the tidyverse framework and is easily expanded to many tickers with group_by(ticker).

data is a data frame with Close and previous close:

        Date ticker  Close Cl.prev
1 2017-08-14 SPY_US 246.54  244.12
2 2017-08-15 SPY_US 246.51  246.54
3 2017-08-16 SPY_US 246.94  246.51
4 2017-08-17 SPY_US 243.09  246.94
5 2017-08-18 SPY_US 242.71  243.09
6 2017-08-21 SPY_US 242.90  242.71

div.data is a tibble with only the dividend payments, Date here is the Ex-Div date.

  ticker       Date      div
2 SPY_US 2017-09-15 1.234574
3 SPY_US 2017-12-15 1.351333
4 SPY_US 2018-03-16 1.096775
5 SPY_US 2018-06-15 1.245568

This chain merges the price data into the div.data in order to get the prices to calculate the adjRatio

  div.data <- div.data %>% 
    left_join(., data[, c("Date", "ticker", "Close", "Cl.prev")], by = c("ticker", "Date"))  

This chain calculates the adjRatio:

  div.data <- div.data %>% 
    mutate(ratio = 1-div / Cl.prev) %>% 
    mutate(adjRatio = rev(cumprod(rev(ratio)))) %>% 
    select(-Close, -Cl.prev, -ratio)

This chain merges the div.data back into the price series, propagates the adjRatio and calculates the Adjusted Close:

data.adj <- data %>% 
  left_join(., div.data, by = c("ticker", "Date") ) %>% 
  mutate(adjRatio = dplyr::lead(adjRatio, n=1)) %>% 
  mutate(adjRatio = na.locf(adjRatio, fromLast = TRUE, na.rm = FALSE)) %>% 
  mutate(adjRatio = na.fill(adjRatio, fill = 1.0)) %>% 
  mutate(Cl.adj = Close * adjRatio) %>% 
  select(-Cl.prev, -div, -adjRatio)

Here is the final data:

> head(data.adj)
        Date ticker  Close   Cl.adj
1 2017-08-14 SPY_US 246.54 242.0153
2 2017-08-15 SPY_US 246.51 241.9858
3 2017-08-16 SPY_US 246.94 242.4079
4 2017-08-17 SPY_US 243.09 238.6286
5 2017-08-18 SPY_US 242.71 238.2556
6 2017-08-21 SPY_US 242.90 238.4421
LeeAnn
  • 51
  • 4
0

At the moment, there are only two forms of tq_mutate() and tq_mutate_xy(). The adjRatios() function has 3 inputs, which would require x,y,z.

Matt Dancho
  • 6,840
  • 3
  • 35
  • 26
  • Thanks for your response. Do you have a recommendation for how to adjust prices for discrete dividends? – LeeAnn Aug 10 '18 at 20:56
  • Let me think about this. We can make a special adjustment function that converts tibble to xts, performs the adjustment, then converst back. This is pretty much how tidyquant works. For now it would just be a bandaid and not solve the bigger issue of integrating the adjRatios function. – Matt Dancho Aug 16 '18 at 14:04