using quantmod to download stock data in R

Question

I am using quantmod to get stock data and the adjusted closing price is discounted with the stock dividend payout, but it may be not so accurate when it comes to actual PnL calculation as one may not have bought the stock since the IPO date so the PnL shall not include all dividend payout which reflects in the adjusted closing price. Say for example with the below df:

library(quantmod)
getSymbols("TGT",form='2018-01-01',to='2022-10-01')
TGT_dividend<-getDividends("TGT",from='2018-01-20',to='2022-10-01') *#fist dividend pays on 2018-01-20*
TGT$TGT.Close *#closing price, excluding dividend*

Here if I would like to sum up all dividend payout in the data frame (from 2018-01-20 to 2022-10-01) and then compare the closing price (instead of adjusted closing) , that way I can hypothetically calculate how much the PnL is (Traded price + all dividend payment - updated CLOSING price). May I know how should I go about it? Many thanks for reading the post.

Hi guys, thanks for the help from @phiver, I am able to come up with the following codes:

#PnL of div.paying stock
library(quantmod)
calc_pnl<-function(close,dividends,buy_price,buy_date){
  names(close) <- "close"
  names(dividends) <- "div"
  # merge close and cumsum divs from buy_date
  out <- merge(close[paste0(buy_date, "/")], cumsum(dividends[paste0(buy_date, "/")]))
  
  # roll divs forward on the empty values
  out$div <- na.locf(out$div)
  
  # calculate buy_price as close - buy_price + divs
  out$pnl <- out$close - buy_price + out$div
  
  out
}
calc_pnl_max<-function(close,dividends,buy_price,buy_date){
    names(close) <- "close"
    names(dividends) <- "div"
    # merge close and cumsum divs from buy_date
    out <- merge(close[paste0(buy_date, "/")], cumsum(dividends[paste0(buy_date, "/")]))
    
    # roll divs forward on the empty values
    out$div <- na.locf(out$div)
    
    # calculate buy_price as close - buy_price + divs
    out$pnl <- out$close - buy_price + out$div
    
    out
  }
calc_pnl_min<-function(close,dividends,buy_price,buy_date){
    names(close) <- "close"
    names(dividends) <- "div"
    # merge close and cumsum divs from buy_date
    out <- merge(close[paste0(buy_date, "/")], cumsum(dividends[paste0(buy_date, "/")]))
    
    # roll divs forward on the empty values
    out$div <- na.locf(out$div)
    
    # calculate buy_price as close - buy_price + divs
    out$pnl <- out$close - buy_price + out$div
    
    out
  }
calc_pnl_avg<-function(close,dividends,buy_price,buy_date){
    names(close) <- "close"
    names(dividends) <- "div"
    # merge close and cumsum divs from buy_date
    out <- merge(close[paste0(buy_date, "/")], cumsum(dividends[paste0(buy_date, "/")]))
    
    # roll divs forward on the empty values
    out$div <- na.locf(out$div)
    
    # calculate buy_price as close - buy_price + divs
    out$pnl <- out$close - buy_price + out$div
    
    out
  }  
  
start<-'2015-01-01'
end<- '2022-10-21'

symbols<-'0386.HK'
ticker <- na.omit(getSymbols(symbols,from = start, to = end,auto.assign=FALSE)) 
ticker_dividend<-round(getDividends(symbols,from=start,to=end),2)
buy_max<-max(ticker[,4])
buy_min<-min(ticker[,4])
buy_avg<-mean(ticker[,4])

pnl_max<-calc_pnl_max(ticker[,4],ticker_dividend,buy_price = buy_min ,buy_date = start)
pnl_min<-calc_pnl_max(ticker[,4],ticker_dividend,buy_price = buy_max ,buy_date = start) 
pnl_avg<-calc_pnl_max(ticker[,4],ticker_dividend,buy_price = buy_avg ,buy_date = start) 

df<-round(tail(cbind(pnl_max$pnl,pnl_min$pnl,pnl_avg$pnl),8),2)
colnames(df)<-c('maxPnL','minPnL','avgPnL') 

Answer 1

Not sure if I got exactly what you want, but based on the example data, I created a function where you can supply the close price, the dividends, the buy price and the buy date and it will return an xts with the PnL calculated from the buy date.

The function merges the close price with the cumulative dividends, fills in the NA's with the previous div value and then calculates the PnL based on the close price - buy price + all collected dividends.

library(quantmod)

TGT <- getSymbols("TGT",from = '2018-01-01', to = '2022-10-01', auto.assign = FALSE)
TGT_dividend <- getDividends("TGT", from = '2018-01-01', to = '2022-10-01')

calc_pnl <- function(close, dividends, buy_price, buy_date){
  
  # rename columns
  names(close) <- "close"
  names(dividends) <- "div"
  # merge close and cumsum divs from buy_date
  out <- merge(close[paste0(buy_date, "/")], cumsum(dividends[paste0(buy_date, "/")]))
  
  # roll divs forward on the empty values
  out$div <- na.locf(out$div)

  # calculate buy_price as close - buy_price + divs
  out$pnl <- out$close - buy_price + out$div
  
  out
}

pnl <- calc_pnl(TGT$TGT.Close, TGT_dividend, buy_price = 127, buy_date = "2020-08-03")
tail(pnl)

            close div      pnl
2022-09-23 152.61 7.4 33.01000
2022-09-26 148.71 7.4 29.11001
2022-09-27 148.47 7.4 28.87000
2022-09-28 155.87 7.4 36.26999
2022-09-29 151.79 7.4 32.18999
2022-09-30 148.39 7.4 28.79000