The last several months have witnessed large swings in the market. The so-called VIX index captures these quite well and in an earlier post we had plotted this. Updating the data till 2018 year end we see the following – note the clear spikes in the daily change ratios plotted towards end of year:
As any goggle search will show, investing in dividend paying stocks appears to offer some kind of stability especially when the market appears to enter into so-called correction periods. While these dividend paying companies may experience drop in their stock prices, they for the most part continue to pay out dividends. It is suggested that this is all the more true if these comprise the so-called dividend champions or aristocrats who have very strong dividend records year after year. This site maintains a list of such companies and the December version of this excel sheet is available from this website. This is updated monthly and consists of the top ranked dividend paying companies split into 3 categories, i.e., Champions, Challengers and Contenders. Champions being the list of companies which have paid out dividends for the longest time and on a consistent basis. There are several sheets that can be downloaded. There a couple of interest to us here, first being the complete list of them all, identified by the “All CCC” tag, and the second bearing tag “Historical” which is an almost 20-year data compilation of actual dividends paid out by company.
Our intention here is to develop analytical tools to evaluate whether this widely touted strategy of holding dividend stocks provides adequate performance, especially in a retirement context where periodic or annual/quarterly cash flows are important as is the ability to sustain these cash flows into the future. The idea is to first select stocks in order to build a diversified portfolio, download their adjusted price history and analyse their performance over past historical periods. This can be extended into the future by employing simulation techniques that have been described earlier. The detailed steps are described below followed by actual implementation in R code. As usual, we will keep initial model simple retaining ability to add many bells and whistles later.
· Obtain the data for say yearend from dividend champions site. Summarize and aggregate data needed for the model. The aggregated data will drill down to ranking companies in their individual sectors, ( sectors being e.g., Utilities, Financials, Tech etc.,). There happen to be 11 distinct sectors in the data compilation
· Filter the data to pick a basket of say 20 stocks according to the following criteria: 1) at least one from each sector, some sectors will have more than one; 2) highest dividend yielding stock or highest previous 5 year dividend payout growth rate or some combination from each sector; 3) additional stocks may be picked from sector with most representation; 4) initial investment in the basket will be on an equal weight basis
· For each of the 20 stocks, download adjusted price history for at least the past 10 or 15 years
· Chart historic performance of this basket assuming just a Buy and Hold strategy, i.e., no rebalancing
· Specify an initial annual requirement or withdrawal amount
· Determine annual cash flows generated by our model portfolio taking into account dividend growth
· Evaluate sufficiency of portfolio
Assuming location of downloaded dividend list as inFile = “z:/Downloads/DivChamps/DivChamps181231.xls” we will first separate out data needed into object divXL as below:
require(XLConnect) ## need the excel interface package
### first get list of stocks, sector and div yield
### this is from the All CCC sheet
### also noyrs, CCCseq and divyield
## READ & JUST get those cols
SELWKS = “All CCC”
wb <- loadWorkbook(inFile, create = FALSE) # load in an Excel ss
divXL<-readWorksheet(wb, sheet = SELWKS)
getCols<-c(“Ticker” , “Sector” , “Industry” , “NoYrs” , “CCCSeq” , “DivYield” )
colnames(divXL)<-c(“CompanyName”, “Ticker”)
colSelector<-which(gsub(“\\.|\\+|\\-|\\/| |%|[:0-9]|(Symbol)|(NA)”, “”,
paste(divXL[4,], divXL[5,], sep=“”) ) %in% getCols)
# remove all lines upto Name;symbol
divXL<-divXL[–(1:c(grep(“Name;Symbol”, paste(divXL$CompanyName,divXL$Ticker, sep=“;”) , value=FALSE)) ),]
# remove bottom rows
divXL<-divXL[–c(grep(“(Averages for)|(Average)”, divXL$CompanyName, value=FALSE)[1]:nrow(divXL)),]
divXL <- divXL[,colSelector]
colnames(divXL)<-getCols
> head(divXL)
Ticker Sector Industry NoYrs CCCSeq DivYield
6 SRCE Financials Banks 31 92 2.48
7 MMM Industrials Industrial Conglomerates 60 8 2.86
8 AOS Industrials Building Products 25 128 2.06
9 AAN Consumer Discretionary Specialty Retail 16 222 0.33
10 ABT Health Care Health Care Equipment & Supplies 6 772 1.77
11 ABBV Health Care Biotechnology 7 658 4.64
Next read in the dividend history from the “Historical” worksheet, calculate the 5-year CAGR (Compound Average Growth Rate) of the dividend increases (or decreases) per ticker/stock and just selct the most recent 5-year CAGR. At this juncture, we don’t want all the 5-yar rolling periods but these are easily available if at some point it was necessary to look athe history of increases or decreases. The 5-year growth rate was chosen, but one could just have just have easily chosen a different period – there must be sufficient data available though. Data needed for selecting stocks are consolidated into the “dvGood” object.
SELWKS = “Historical”
CURRYR = as.numeric(format(Sys.Date(),“%Y”))
yrsCols = seq(1990, CURRYR)
histColNames<-c(“Symbol” , as.character(yrsCols) )
## inFile is location of downloaded spreadsheet
monthEnd<-as.Date(paste(“20”, strapplyc(inFile, “.*\\/*(\\d{6})\\.xls”, simplify = TRUE), sep=“”), “%Y%m%d”)
wb <- loadWorkbook(inFile, create = FALSE) # load in an Excel ss
### get the Historical sheet – this has actual dividends
histXL<-readWorksheet(wb, sheet = SELWKS) # to get the “Historical”worksheet
## need to get correct columns – eliminate not needed
## years are repeated – discard the percentages – will calculate later
xln = paste(histXL[4,], histXL[5,], sep = “”)
xln = gsub(“vs\\.[0-9]{4}”, “DISCARD”, xln)
## pick cols – note weird groupings!
colSelector<-which(gsub(“(Ticker)|(NA)|(Amounts.+year)”, “”, xln) %in% histColNames)
colnames(histXL)<-c(“CompanyName”, “Ticker”)
# remove all lines upto Name;symbol
histXL<-histXL[–(1:c(grep(“Name;Symbol”, paste(histXL$CompanyName,histXL$Ticker, sep=“;”) , value=FALSE)) ),]
histXL<- histXL[,colSelector]
### to remove nas – “ext” has all tickers and the previous years dividend payouts
###
ext = cbind(histXL[,1], apply(histXL[,–1], 2, function(x) round(as.numeric(x),2) ) )
### save this in file so we can reuse later
write.table(cbind(as.character(monthEnd), ext), file=“D:/Temp/ExcelLoadCSV/tmpDivHistory.csv”, quote=FALSE,
row.names=FALSE, col.names =FALSE, sep=“;”)
### probably only want the growth CAGR for the past 5 years
### dont need everything in dvGrowth5Yr
dhist<-read.csv(file=“D:/Temp/ExcelLoadCSV/tmpDivHistory.csv”,head=FALSE,as.is=TRUE,sep=“;”)
dvGrLast = apply(dhist[, 3:8], 1, function (x) (–diff(unlist(x), lag=5)+1)^0.2 ) ## dont need transpose /only 1 col
dvGrLast = cbind(dhist[,1:2], dvGrLast )
dvGrLast = dvGrLast[complete.cases(dvGrLast), ] ### throw out NAs
colnames(dvGrLast) = c(“Date”, “Ticker”, “CAGR5Yr”)
### now merge up with div champs list from divXL
dvGood = cbind(divXL,0)
colnames(dvGood) = c(colnames(divXL), “CAGR5Yr”)
## check that no errors
for ( i in 1:nrow(dvGood) ) {
### need to test if found, else the which returns length 0
rn = which(dvGrLast$Ticker == dvGood$Ticker[i], arr.ind=T)
if ( length(rn) != 0) dvGood$CAGR5Yr[i] = dvGrLast$CAGR5Yr[rn]
}
dvGood[101:110,]
Ticker Sector Industry NoYrs CCCSeq DivYield CAGR5Yr
106 B Industrials Machinery 8 499 1.19 1.028347
107 BSET Consumer Discretionary Household Durables 8 518 2.50 1.040572
108 BBT Financials Banks 8 524 3.74 1.084472
109 BDX Health Care Health Care Equipment & Supplies 47 41 1.37 1.159962
110 BMS Materials Containers & Packaging 35 77 2.70 1.037137
111 BBY Consumer Discretionary Specialty Retail 15 237 3.40 1.105342
112 BIG Consumer Discretionary Multiline Retail 5 801 4.15 1.148698
113 BKH Utilities Multi–Utilities 48 33 3.22 1.058694
114 BLK Financials Capital Markets 9 393 3.19 1.379730
115 BA Industrials Aerospace & Defense 8 569 2.56 1.375286
Now we want to see what the distribution of stocks is by sector on the dividend list. From this we can estimate number of stocks to allocated per sector, remembering that a minimum of 1 per sector was a requirement. The package plyr is needed and the “grp” object below shows distribution results. From this theoretical analysis, we see that Fractions are presented, e.g., Consumer Discretionary should have 2, Materials have 1, Industrials 3 and so on with some Comm Services as 0. This would need to be customized.
##### to get at sector/industry stuff
#####
require(plyr)
crit = c(“Sector”, “Industry”) ; or crit = “Sector”
grp=count(divXL, crit )
grpByCount = grp[order(grp[,3], decreasing = T),] ### [,3] because 2 columns used to group, if only one then [,2] see below
grp[order(grp[,3], decreasing = T),][1:20,]
## in 1 line – show say the top 20
grp=count(divXL, crit ); grp[order(grp[,2], decreasing = T),][1:20,] ### show top 20
grp=cbind(grp,0)
colnames(grp) = c(“Sector”, “Freq”, “Fraction”)
grp$Fraction = (grp$Freq/sum(grp$Freq) )
grp[order(grp$Freq, decreasing = T),]
Sector Freq Fraction
5 Financials 289 0.334104046
7 Industrials 124 0.143352601
2 Consumer Discretionary 83 0.095953757
10 Real Estate 75 0.086705202
11 Utilities 62 0.071676301
8 Information Technology 52 0.060115607
3 Consumer Staples 47 0.054335260
9 Materials 47 0.054335260
6 Health Care 38 0.043930636
4 Energy 28 0.032369942
1 Communication Services 19 0.021965318
### If theoretical number of stocks to allocate
### assuming a basket of 20; then this would be
### number of stocks to pick from each sector
grp$Fraction = round((grp$Freq/sum(grp$Freq) * 20 ), 0)
grp[order(grp$Freq, decreasing = T),]
Sector Freq Fraction
5 Financials 289 7
7 Industrials 124 3
2 Consumer Discretionary 83 2
10 Real Estate 75 2
11 Utilities 62 1
8 Information Technology 52 1
3 Consumer Staples 47 1
9 Materials 47 1
6 Health Care 38 1
4 Energy 28 1
1 Communication Services 19 0
Once the first cut at number of stocks per sector derived as in the grp matrix above, this can be refined. Remember we want only 20 stocks in our portfolio, although this can certainly be relaxed. We now need to pick the top performing stocks per our criteria – currently can use either the dividend yield or dividend growth. One point to note is that w are using the data as of Dec 31, 2018 and when we perform the historical backtests, the circumstances as of 2018 yearend would certainly not have been the same many years earlier. But our goal is to construct a model which can certainly be used going forward. If this a very controversial proposition, then we can perhaps try and get data as of a previous year end and apply that for historical performance analysis. The problem is that data from that website goes back only so far, and there have been several intermediary format changes. The underlying model structure however, remains intact.
First a function to sort and pick stocks based on the above is defined. We must also specify a control array to let us know how many stocks to pick in each sector and this is where we can provide our own judgement factor. A sample array, ‘dvsfy’ is shown which shows number for each sector, keyed off the ‘grp’ matrix. Finally we run the sort and pick function and obtain a list of candidate stocks. This has a factor identifier as well so that we can just pick a 20 based on div yield for example.
#################################################
#### Function to Pick top div Yield & growth stocks
#### Default is top 3 & 15 years of dividends
#### Top number controlled by diversification array
#################################################
sortAndPick <- function(sector, NYRS=15, FROMTOP=3) {
out<- c()
FROMTOP = grp$dvsfy[(grp$Sector == sector)]
topPick=sort(dvGood$DivYield[(dvGood$Sector==sector & dvGood$NoYrs > NYRS)] , decreasing = T) [1:FROMTOP]
### Factor =0 for Div Yield; =1 for CAGR
out <- rbind(out , cbind(dvGood[which(dvGood$Sector == sector & dvGood$NoYrs > NYRS & dvGood$DivYield %in% topPick ,arr.ind=T), ] , Factor=0 ) )
colnames(out) = c(colnames(dvGood), “Factor”)
topPick=sort(dvGood$CAGR5Yr[(dvGood$Sector==sector & dvGood$NoYrs > NYRS)] , decreasing = T) [1:FROMTOP]
out <- rbind(out , cbind(dvGood[which(dvGood$Sector == sector & dvGood$NoYrs > NYRS & dvGood$CAGR5Yr %in% topPick ,arr.ind=T), ] , Factor=1 ) )
return(out)
}
##### Mow pick the 20 stocks we need
#### setup control array number of stocks per sector in dvsfy
#### this should be in order of sectors – e.g., as.vector(grp$Sector) – should be in this order
# [1] “Communication Services” “Consumer Discretionary” “Consumer Staples” “Energy” “Financials” “Health Care”
# [7] “Industrials” “Information Technology” “Materials” “Real Estate” “Utilities”
dvsfy = c(1, 2, 2, 2, 2, 2, 3, 2, 1, 2, 1 ) ## check sum to be 20 (our total in portfolio)
grp = cbind(grp, dvsfy)
## apply sort & pick function to all sectors & get list of candidate stocks
pkList = lapply(grp$Sector, sortAndPick)
candStocks = do.call(rbind, pkList)
The ‘candstocks’ data frame has stock picks. Because there may have been identical div yields resulting in multiple matches we may get over 20 stocks in the list. This is in fact what has happened. If we look at this dataframe by Factor=0, i.e. dividend yield we find some similar values e.g. the highlighted Industrials category. Similarly the Financials have multiple similar yileds also. We therefore need to prune this, by removing a few, arriving finally at the 20 stocks that we pick for analysis:
> candStocks[(candStocks$Factor==0),]
Ticker Sector Industry NoYrs CCCSeq DivYield CAGR5Yr Factor
79 T Communication Services Diversified Telecommunication Services 35 80 7.15 1.037137 0
463 LEG Consumer Discretionary Household Durables 47 36 4.24 1.048964 0
749 TGT Consumer Discretionary Multiline Retail 51 23 3.87 1.162163 0
35 MO Consumer Staples Tobacco 49 30 6.48 1.127235 0
812 VGR Consumer Staples Tobacco 20 161 16.44 1.058694 0
255 ENB Energy Oil, Gas & Consumable Fuels 22 149 6.90 1.115859 0
260 EPD Energy Oil, Gas & Consumable Fuels 21 158 7.04 1.069610 0
514 MCY Financials Insurance 32 89 4.85 1.009806 0
610 PBCT Financials Banks 26 106 4.85 1.009806 0
850 WHG Financials Capital Markets 17 196 8.47 1.148698 0
147 CAH Health Care Health Care Providers & Services 23 145 4.27 1.139358 0
428 JNJ Health Care Pharmaceuticals 56 12 2.79 1.139358 0
15 ACU Industrials Commercial Services & Supplies 16 223 3.37 1.026552 0
254 EMR Industrials Electrical Equipment 62 7 3.28 1.055490 0
475 LMT Industrials Aerospace & Defense 16 219 3.36 1.339354 0
537 MSM Industrials Trading Companies & Distributors 16 218 3.28 1.122235 0
410 IBM Information Technology IT Services 23 144 5.52 1.291994 0
649 QCOM Information Technology Semiconductors & Semiconductor Equipment 16 208 4.36 1.179199 0
575 NUE Materials Metals & Mining 46 48 3.09 1.009806 0
712 SON Materials Containers & Packaging 36 74 3.09 1.061859 0
584 OHI Real Estate Equity Real Estate Investment Trusts (REITs) 16 199 7.51 1.130926 0
748 SKT Real Estate Equity Real Estate Investment Trusts (REITs) 25 118 6.92 1.087348 0
630 PPL Utilities Electric Utilities 17 183 5.79 1.040572 0
### by div yield – gives us 23 stocks – we will remove 3 to get final pick
### remove EMR, MCY and NUE
> candStocks$Ticker[(candStocks$Factor==0)]
[1] “T” “LEG” “TGT” “MO” “VGR” “ENB” “EPD” “MCY” “PBCT” “WHG” “CAH” “JNJ” “ACU” “EMR” “LMT” “MSM” “IBM” “QCOM” “NUE” “SON” “OHI”
[22] “SKT” “PPL”
# remove “NUE”, “EMR”, “MCY”
finalPick = finalPick[–which(finalPick %in% c(“NUE”, “EMR”, “MCY”))]
finalPick
[1] “T” “LEG” “TGT” “MO” “VGR” “ENB” “EPD” “PBCT” “WHG” “CAH”
“JNJ” “ACU” “LMT” “MSM” “IBM” “QCOM” “SON” “OHI” “SKT” “PPL”
Now that we have the final 20 stocks to test in our portfolio, we will download adjusted prices from tiingo ( note an api key is required, obtained after free registration with site). Once we download, we’ll save for future analysis. Examination of data reveals some missing prices. We can either decide to ignore the NA values or substitute another ticker symbol. For example WHG appears to have some missing values. Log returns have been calculated on a monthly basis as shown.
> head(portfolioPrices)
T.Close LEG.Close TGT.Close MO.Close VGR.Close ENB.Close EPD.Close PBCT.Close WHG.Close CAH.Close JNJ.Close ACU.Close LMT.Close MSM.Close
2000-01-03 17.30505 10.939917 25.76829 2.010728 1.176710 2.434495 1.495531 1.975643 NA 17.89039 28.87626 0.8546563 13.74908 8.971578
2000-01-04 16.77050 10.455987 24.78749 1.984271 1.116878 2.425814 1.466019 1.934735 NA 18.25738 28.25613 0.8111604 12.49917 9.144108
2000-01-05 16.03639 10.105909 24.58777 2.032065 1.136822 2.434495 1.515471 1.934735 NA 17.98214 27.53678 0.7630860 13.28036 8.281457
2000-01-06 15.96806 9.941167 23.53920 2.005608 1.096933 2.428294 1.515471 1.941243 NA 19.15281 28.00188 0.7630860 13.12412 8.626517
2000-01-07 15.96806 10.038983 22.82589 1.984271 1.127249 2.487824 1.505900 1.941243 NA 20.36753 29.18632 0.7153931 13.59284 8.799048
2000-01-10 16.03615 10.234614 23.71753 2.026944 1.121664 2.542392 1.466019 1.908703 NA 22.01895 29.98008 0.7630860 13.36161 8.322864
IBM.Close QCOM.Close SON.Close OHI.Close SKT.Close PPL.Close
2000-01-03 78.95010 72.77157 11.83298 3.953161 1.864770 4.829492
2000-01-04 80.04545 62.82490 11.44587 3.801117 1.864770 4.696860
2000-01-05 79.30117 58.47323 11.04830 3.877139 1.870211 4.789492
2000-01-06 82.85407 54.30441 11.54003 3.895384 1.904677 4.922124
2000-01-07 82.32745 49.00196 11.86960 4.162983 1.989934 4.882124
2000-01-10 82.32745 59.09490 11.83298 3.819362 2.075191 4.907387
> tail(portfolioPrices)
T.Close LEG.Close TGT.Close MO.Close VGR.Close ENB.Close EPD.Close PBCT.Close WHG.Close CAH.Close JNJ.Close ACU.Close LMT.Close MSM.Close
2019-01-02 28.02516 35.37 65.06 48.91 9.60 30.74 24.53 14.31 33.68 44.12 128.130 13.89414 258.36 75.28277
2019-01-03 29.02816 35.76 66.13 48.80 9.82 31.56 25.00 14.60 35.45 44.57 128.135 14.75756 262.32 74.57854
2019-01-04 29.46083 35.88 66.24 49.49 10.04 32.18 25.87 14.76 35.74 44.62 127.120 15.08507 261.92 75.40179
2019-01-07 30.21800 36.68 66.81 49.68 10.34 33.00 26.83 14.96 36.65 44.92 127.630 15.77000 266.00 77.27000
2019-01-08 30.78834 37.60 70.18 49.26 10.60 33.85 27.49 15.20 37.33 46.04 128.180 15.75000 271.13 77.20000
2019-01-09 30.88000 37.78 69.38 49.19 10.85 34.48 27.75 15.19 37.77 46.80 129.830 15.75000 270.13 76.77000
IBM.Close QCOM.Close SON.Close OHI.Close SKT.Close PPL.Close
2019-01-02 112.01 56.20 52.48 33.88 19.97 28.25
2019-01-03 114.53 55.95 52.18 33.82 20.46 28.11
2019-01-04 114.91 56.50 52.20 34.79 20.57 27.80
2019-01-07 117.50 56.39 51.96 35.20 21.00 28.68
2019-01-08 119.66 56.71 52.40 35.41 21.29 28.95
2019-01-09 120.91 56.26 52.32 36.30 22.03 29.21
returnsLog = Return.calculate(portfolioPrices[endpoints(portfolioPrices), on=”months”], method=”log”)
> returnsLog = Return.calculate(portfolioPrices[endpoints(portfolioPrices), on=“months”], method=“log”)
> tail(returnsLog)
T.Close LEG.Close TGT.Close MO.Close VGR.Close ENB.Close EPD.Close PBCT.Close WHG.Close CAH.Close JNJ.Close
2018-08-31 -0.0059291795 0.04456183 0.076621465 -0.003069579 -0.15032658 -0.008308097 -0.019782009 0.01306497 -0.001739433 0.03732725 0.02403500
2018-09-28 0.0446830755 -0.02824911 0.009859073 0.051298986 -0.06248579 -0.059973227 0.006289329 -0.08578297 -0.097448408 0.03752257 0.02567064
2018-10-31 -0.0701033020 -0.17985884 -0.013668162 0.073802675 -0.03417866 -0.054990156 -0.026870487 -0.06412885 -0.163124599 -0.03702369 0.02078183
2018-11-30 0.0006514658 0.05838750 -0.188734467 -0.158306888 -0.03304181 0.069406669 -0.033650799 0.03863409 -0.111884826 0.06604850 0.03666203
2018-12-31 -0.0722311182 -0.06682891 -0.082383100 -0.113814451 -0.23996930 -0.057801501 -0.098323365 -0.13527934 -0.087791321 -0.21035043 -0.12564030
2019-01-09 0.0945530901 0.05076405 0.060147293 0.002442501 0.11410318 0.103493222 0.141024000 0.06107732 0.072175054 0.05535010 0.01279038
ACU.Close LMT.Close MSM.Close IBM.Close QCOM.Close SON.Close OHI.Close SKT.Close PPL.Close
2018-08-31 -0.031359079 -0.002590781 0.02313133 0.01064917 0.063137336 0.011477261 0.11363130 0.01255247 0.031391182
2018-09-28 0.004146516 0.081818359 0.02466973 0.03665191 0.059297322 -0.007191689 -0.02295434 -0.06617534 -0.017703771
2018-10-31 -0.229604777 -0.160825691 -0.06060317 -0.26053345 -0.128132166 0.000180424 0.04708529 0.03492280 0.056927307
2018-11-30 -0.117716178 0.028772002 0.06643395 0.05606947 -0.077476195 0.031097144 0.12045683 0.02367229 0.004899569
2018-12-31 -0.073260115 -0.137362496 -0.13194517 -0.07092635 -0.008276884 -0.073065170 -0.06139261 -0.12729216 -0.065150875
2019-01-09 0.110491630 0.029986466 0.01168862 0.06474255 -0.015170515 -0.008564140 0.03020364 0.06227146 0.028826445
Performance of the portfolio can be plotted via the PerformanceAnalytics package
For comparison, the performance of a SP500 portfolio for the same period shows this:
We will expand on stock selection scenarios in subsequent posts. But as mentioned above and in earlier posts, acquiring the underlying data to perform such analysis is critical and takes a majority of effort required in financial modeling. Of course, it is always possible to subscribe to a paid service that provide all kinds of data. Here too though, we need to process the data such that it conforms to our particular requirements. For the moment, let us assume that all data we use will be obtained without charge from web-based sources.
For example, we used the excellent (note what I did there!) spreadsheets from this site that conveniently focused in on just the top rated dividend stocks. However, data from the site really does not start till about 2010 and has undergone various formatting and other changes. These constant variations mean that it is difficult to develop a standard few lines of code to capture information. Also, dividend history in terms of actual payouts does not also appear to be sufficient.
To get at dividend yield at some point in time, we need adjusted price at beginning of period and second we need dividend payouts during period. So if adjusted price in say Jan 2011 was $68.00 and we received an annual dividend of $3.00 – usually companies pay out quarterly dividends so this would mean $0.75 per quarter – yield is 3.00/68.00 which works out to 4.41%. Price data in usable downloadable form can easily be obtained from yahoo, tiingo etc., Dividend payouts however are not that easily obtained at least on a free basis. This site which focuses on financial planning using Microsft Excel does have a spreadsheet that provides some help in this regard, although only a limited number of tickers can be downloaded at a time. One has to examine the underlying VBA code to get the details but once done, we can implement this R and remove any scale type restriction. So, once dividend payout has been downloaded, all this can be stored in a database for convenient access later. The R code to get this dividend payout history is provided below and should be self-explanatory, although it covers some arcane areas of http protocols. Data is accessed from a Yahoo site, and as long as Yahoo keeps providing it, we can use it.
############ getting dividend payout history from 1980 onwards#install.packages(“QuantTools”)
require(RCurl)
require(stringr)
curl = RCurl::getCurlHandle( cookiejar = ” )
#### To get cookies AND the crumb to use subsequently
ckie = “https://finance.yahoo.com/lookup?s=bananas”
xyzM = RCurl::getURL( ckie, curl = curl )
crumbM = gsub( ‘^.*crumb”:”\\s*|”.*’, ”, xyzM )
### this is absolutely necessary, since it needs to be passed in subsequent request
crumbM = gsub( ‘^.*crumb”:”\\s*|”.*’, ”, xyzM )
crumbM
##[1] “pXcobcKSFyi”
### save data on output file
fileout = “z:/FinancialData/DividendHist/batch_10Jan2019.csv”
cat(NULL, file=fileout)
options(scipen=999)
### The url template “urlSub” to use in actually getting history data
## period1 & period2 are unix timestamps
### note the crumb variable
urlSub = “https://query1.finance.yahoo.com/v7/finance/download/%TICKER%?period1=%START%&period2=%LAST%&interval=1d&events=dividend&crumb=%CRUMB%”
### to convert date to unix timestamp
startD = as.numeric(as.POSIXct(“1980-01-12”))
lastD = as.numeric(as.POSIXct(“2018-12-31”))
########################################################
##### Function to get dividend history
##### Just needs ticker as input
##### Will use crumb and cookies from above
##### to get all dividend payouts for dates
##### Adds downloaded data to output file
########################################################
getDividendFromYahoo <- function(ticker) {
print (ticker)
urlDiv = gsub(“%TICKER%”, ticker, gsub(“%START%”, startD, gsub( “%LAST%”, lastD, gsub(“%CRUMB%”, crumbM, urlSub))) )
dat = RCurl::getURL(urlDiv, curl = curl)
if ( length(grep(“No data found”, dat) == 1) > 0 ) return()
divd = unlist(str_split(dat,“\n“))
rem = c(“Date”, “Dividends”,“”)
xyz=unlist(str_split(unlist(str_split(dat,“\n“) ), “,”) ) ## bring data in clean form
divd = matrix(xyz[!(xyz %in% rem)], ncol=2, byrow=T) ## remove first & other place in matrix
colnames(divd) = c(“Date”, “DivPaid”)
storage.mode(divd[,“DivPaid”]) = “numeric”
##divd[,”DivPaid”] = round(divd[,”DivPaid”],4)
divd = cbind(ticker, divd) ## identify ticker
divd=divd[order(divd[,“Date”]),] ### sort it in Date sequence
write.table(divd, file=fileout, sep=“,”, quote=F, row.names=FALSE, col.names=FALSE, append=TRUE)
}
#######################################################
### Can repeatedly acll above with any list of required tickers say our FinalPick
### Output file contains data
finalPick = c(“T”, “LEG”, “TGT”, “MO”, “VGR”, “ENB”, “EPD”, “PBCT”, “WHG”, “CAH”,
“JNJ”, “ACU”, “LMT”, “MSM”, “IBM”, “QCOM”, “SON”, “OHI”, “SKT”, “PPL”)
abc = sapply(finalPick, function(tkr) getDividendFromYahoo(tkr) )
#### And that’s all there is to it
#### sample contents output file
OHI,1993–01–27,0.5
OHI,1993–04–27,0.5
OHI,1993–07–27,0.5
OHI,1993–10–27,0.54
OHI,1994–01–27,0.54
OHI,1994–04–26,0.54
OHI,1994–07–27,0.54
OHI,1994–10–27,0.58
OHI,1995–01–27,0.59
OHI,1995–04–26,0.59
OHI,1995–07–31,0.59
Thanx for reading
