Continuing the safety and cash flow theme, most impartial advisors recommend significant exposure to quality dividend stocks especially when one is in a life situation that requires a stable stream of cash flows. With high quality companies, there is less uncertainty as far as cash flow is concerned, although it must be noted that nothing is guaranteed. A long history of a company paying out dividends through times good and bad would suggest continuation of same.
In the last post, a very simple selection scheme was described, more as an aid to the mechanics of building the planning model than as any investment insight. As usual, the disclaimer will be reaffirmed that nothing in this blog should be considered as recommended investment strategy. In the process of illustrating some techniques, we also used the excellent data on dividend champions etc., maintained here in order to build up the model. Emphasis was on the R Code to process, extract, select and analyze portfolio performance.
Let’s take a more realistic example here, one that can be adapted to any other personal requirements as well. The starting point is the same dividend champions excel spreadsheet which already has made some sort of selection from the literally thousands of stocks out there. While this procedure is useful, it should not been seen as a purely mechanical algorithm. One should also assess the viability of the business from personal experience and use other subjective measures or news that may not be reflected in the data or numbers. Normally, various other financial and valuation ratio are in use by serious practitioners and these involve for the most part what is normally referred to as fundamental analysis. Stock screeners are available to account holders at all brokerage companies, and also free of charge on sites sites such as Yahoo, FinViz etc.,
For example from the free section of Finviz one is treated to this screener – the number of selection criteria is simple overwhelming. A large amount of data needs to be available and accessible, and having this on hand is often the most challenging aspect of implementing these planning tools and models.
For our purposes, we want adequate diversification across all sectors and so need to pick at most 3 stocks per sector say. A certain amount of variability in the allocation could also be used instead of strictly equal sector weighting. As for the screening criteria to use, let us go with the following:
· Dividend yield – minimum 3.5% for companies with a 20+ year dividend history; min 2 % for between 6 and 20 years
· Consistently paid AND raised dividends over past say 10 years
· Free cash flow payout ratio say below 50% (25-75% safe region)
· Dividend growth history – CAGR sustainable between 4-18% or could depend on history (see below)
· Price to TTM earnings < 20 – don’t want to buy overvalued stocks
· Undervalued Valuation ratios – Graham number ; Price not greater than 1.5-1.8 times book value;
· PEG ratio below 1.5 ; div < 0.5 EPS
· For companies with lesser history of earnings, say less than 7 years, lower div yield >1.8 – 2% but require much higher CAGR 5 Yr
· For companies with much longer history of earnings, say > 20 years, higher div yield say > 3.5% but tolerate lower higher CAGR 5 Yr
· Market cap > 5 Bil
· Earnings growth over past 10 years is positive
After stocks have been selected and personal judgment applied, we want to look at how the stocks or portfolio as a whole has performed since then over the years. Necessarily we would have to go back in time. The site does not have much data earlier than 2010 but this should serve our purposes which is really giving ourselves the capability to analyze affairs. So whatever period of time that one can get some good data for can be plugged into the model. The spreadsheet we will use is this one – admittedly this is a snapshot in time. One could always download data for other months and arrive at averages for the metrics we are using, e.g. div yield etc.,
Download the spreadsheet and process as shown below:
#### Processing the Excel sheet from DRIP Investor Site
#### Assuming sheet has been downloaded into file named inFile
inFile = “d:/Temp/DivChamps/DivChamps101130.xls”
### these are columns we are interested in
dvColNames<-c(“Symbol” , “NoYrs” , “Seq” , “Price” , “Yield” , “AnnualDividend” , “PayoutRatio” , “vsGraham” , “TTMPE”
, “PEGRatio” , “MRQPBook”, “MarketCap”, “TweedFactor” , “ChowderRule” , “ConfidFactor” )
# load in an Excel ss
wb <- loadWorkbook(inFile, create = FALSE)
### specify sheets we are interseted in
f3<-(grep(“(Champion)|(Contender)|(Challenger)”, getSheets(wb)))
### read sheet, process data extracting columns needed and cleanup of data
extXL <- c()
## process each of sheets, i.e., Champion | Contender | Challenger
for ( wksheet in f3) {
divXL<-readWorksheet(wb, sheet = wksheet) # to get the specific worksheet
### which cols to select – match names above
colSelector<-which(gsub(“\\.|\\+|\\-|\\/| |%|[:0-9]|(NA)”, “”,
paste(divXL[4,], divXL[5,], sep=“”) ) %in% dvColNames)
#### When spreasheets changes & columns get messed up / missing
## try reverse i.e, which colname was NOT found
colsMissing <- which( !(dvColNames %in% gsub(“\\.|\\+|\\-|\\/| |%|[:0-9]|(NA)”, “”,
paste(divXL[4,], divXL[5,], sep=“”) ) ) )
colnames(divXL)<-c(“CompanyName”, “Ticker”, “Sector”, “Industry”)
# remove all lines upto Name;symbol previous had Company
divXL<-divXL[–(1:c(grep(“Company;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]
### find out any missing columns
idx<-seq(1,length(dvColNames))
idx<-idx[–colsMissing]
colnames(divXL) <- dvColNames[idx]
print (sprintf(” Processing %s records %d”, wksheet, nrow(divXL) ))
## just add to extXL
extXL <- rbind(extXL, divXL)
}
The saved object extXL has the data we need and looks like this:
> extXL[25:36,]
Symbol NoYrs Seq Price Yield AnnualDividend PayoutRatio TTMPE PEGRatio MRQPBook MarketCap
30 CLX 33 76 61.81 3.56 2.20 47.21 13.26 1.64 30.88 8.62B
31 KO 48 12 63.17 2.79 1.76 54.15 19.44 2.13 5.26 146.68B
32 CL 47 16 76.55 2.77 2.12 49.53 17.89 1.77 14.83 36.95B
33 CBSH 42 31 37.55 2.50 0.94 37.60 15.02 1.45 1.55 3.12B
34 CTBI 30 80 27.51 4.43 1.22 60.40 13.62 1.37 1.27 419.53M
35 CTWS 41 33 25.47 3.65 0.93 88.57 24.26 1.47 1.94 220.39M
36 ED 36 61 48.37 4.92 2.38 70.00 14.23 3.32 1.28 14.05B
37 DBD 57 1 31.42 3.44 1.08 71.52 20.81 1.44 1.88 2.06B
38 DOV 55 3 54.81 2.01 1.10 34.59 17.24 1.07 2.36 10.24B
39 EV 30 81 29.72 2.42 0.72 51.80 21.38 1.26 9.26 3.51B
40 EMR 54 8 55.07 2.51 1.38 48.25 19.26 1.13 4.26 41.45B
41 EGN 28 86 43.57 1.19 0.52 13.90 11.65 1.83 1.46 3.13B
The next couple of things we need to do are to get the sector information and also the dividend history. In the last post on sailing the volatility seas, a procedure was provided to download historical dividend and split data for any ticker symbol. Company sector data has been included in the Data resources section. Code below assumes that data has been downloaded and saved accordingly.
### read in the company-sector master list
### and convert to data frame for easy access
allUQ = readLines(“Z:/FinancialData/CompanyData/masterList.csv”)
%>% str_split(pattern=“,”, n=5) %>% do.call(rbind, . )
nuCols = allUQ[1,]
allUQ = as.data.frame(allUQ[2:nrow(allUQ),])
colnames(allUQ) = nuCols
allUQ[1000:1020,]
Exch Symbol Name Sector Industry
1000 NYSE ECL Ecolab Inc. Basic Industries Package Goods/Cosmetics
1001 NYSE EC Ecopetrol S.A. Energy Oil & Gas Production
1002 NYSE EIX Edison International Public Utilities Electric Utilities: Central
1003 NYSE EW Edwards Lifesciences Corporation Health Care Industrial Specialties
1004 NYSE EHIC eHi Car Services Limited Consumer Services Rental/Leasing Companies
1005 NYSE EP^C El Paso Corporation Public Utilities Natural Gas Distribution
1006 NYSE EE El Paso Electric Company Public Utilities Electric Utilities: Central
1007 NYSE ELAN Elanco Animal Health Incorporated Health Care Major Pharmaceuticals
1008 NYSE ESTC Elastic N.V. Technology Computer Software: Prepackaged Software
1009 NYSE EGO Eldorado Gold Corporation Basic Industries Precious Metals
1010 NYSE ELVT Elevate Credit, Inc. Finance Finance: Consumer Services
1011 NYSE LLY Eli Lilly and Company Health Care Major Pharmaceuticals
1012 NYSE ELLI Ellie Mae, Inc. Technology Computer Software: Prepackaged Software
1013 NYSE EFC Ellington Financial LLC Finance Real Estate
1014 NYSE EARN Ellington Residential Mortgage REIT Consumer Services Real Estate Investment Trusts
1015 NYSE AKO.A Embotelladora Andina S.A. n/a n/a
1016 NYSE AKO.B Embotelladora Andina S.A. n/a n/a
1017 NYSE ERJ Embraer S.A. Capital Goods Aerospace
### read in the dividend history file
### this can be downloaded once every few months
### and updated file kept – or downloaded every time
### calculating annual div growth – CAGR 5 Yr
dvHist<-read.csv(file=“Z:/FinancialData/DividendHist/batch_10Jan2019.csv”,head=FALSE,as.is=TRUE,sep=“,”)
colnames(dvHist) = c(“Symbol”, “Date”, “DivPerShare”)
for ( ix in 1:nrow(dvHist) )
dvHist$Year[ix] = as.numeric(str_sub(dvHist$Date[ix], 1,4 ))
dvHist[(dvHist$Symbol==“ED” & dvHist$Year >= 2015),]
Symbol Date DivPerShare Year
25307 ED 2015–02–13 0.650 2015
25308 ED 2015–05–18 0.650 2015
25309 ED 2015–08–17 0.650 2015
25310 ED 2015–11–16 0.650 2015
25311 ED 2016–02–12 0.670 2016
25312 ED 2016–05–16 0.670 2016
25313 ED 2016–08–15 0.670 2016
25314 ED 2016–11–14 0.670 2016
25315 ED 2017–02–13 0.690 2017
25316 ED 2017–05–15 0.690 2017
25317 ED 2017–08–14 0.690 2017
25318 ED 2017–11–14 0.690 2017
25319 ED 2018–02–13 0.715 2018
25320 ED 2018–05–15 0.715 2018
25321 ED 2018–08–14 0.715 2018
25322 ED 2018–11–13 0.715 2018
### retrieve sector & clean up market cap
for ( ix in 1:nrow(extXL) ) {
z = allUQ$Sector[(allUQ$Symbol == extXL$Symbol[ix])]
extXL$Sector[ix] = ifelse ( length(z) == 1 , z, “NODATA”)
### unpack the market Cap
val = as.numeric(str_extract(extXL$MarketCap[ix], “([0-9.]{1,})”) )
mult = str_extract(extXL$MarketCap[ix], “(.$)”)
if (mult != “B”) val = val / 1000
extXL$MarketCap[ix] = val
}
### display updated div data for selection
extXL[25:36,]
Symbol NoYrs Seq Price Yield AnnualDividend PayoutRatio TTMPE PEGRatio MRQPBook MarketCap Sector
30 CLX 33 76 61.81 3.56 2.20 47.21 13.26 1.64 30.88 8.62 Consumer Durables
31 KO 48 12 63.17 2.79 1.76 54.15 19.44 2.13 5.26 146.68 Consumer Non–Durables
32 CL 47 16 76.55 2.77 2.12 49.53 17.89 1.77 14.83 36.95 Consumer Non–Durables
33 CBSH 42 31 37.55 2.50 0.94 37.60 15.02 1.45 1.55 3.12 Finance
34 CTBI 30 80 27.51 4.43 1.22 60.40 13.62 1.37 1.27 0.41953 Finance
35 CTWS 41 33 25.47 3.65 0.93 88.57 24.26 1.47 1.94 0.22039 Public Utilities
36 ED 36 61 48.37 4.92 2.38 70.00 14.23 3.32 1.28 14.05 Public Utilities
37 DBD 57 1 31.42 3.44 1.08 71.52 20.81 1.44 1.88 2.06 Miscellaneous
38 DOV 55 3 54.81 2.01 1.10 34.59 17.24 1.07 2.36 10.24 Technology
39 EV 30 81 29.72 2.42 0.72 51.80 21.38 1.26 9.26 3.51 Finance
40 EMR 54 8 55.07 2.51 1.38 48.25 19.26 1.13 4.26 41.45 Energy
41 EGN 28 86 43.57 1.19 0.52 13.90 11.65 1.83 1.46 3.13 NODATA
#### Now we need to process the dividend history file
#### First aggregate the quarterly dividend to annual by adding them for year
divPaidByYear = aggregate(dvHist$DivPerShare, by = dvHist[c(“Symbol”, “Year”)], FUN=sum)
colnames(divPaidByYear) = c(“Symbol”, “Year”, “AnnualDividend”)
### Want to find the 5 year compound growth rate
### Means have to remove symbols that dont have enough data
require(plyr)
crit = c(“Symbol”)
### group by symbol, get count order by decreasing no of history years
grp=count(divPaidByYear, crit )
grp=grp[order(grp[,2], decreasing = T),]
### lets remove all with less than 6 yrs div history
idx = which(grp[,2] < 6, arr.ind=T)
vdx = unlist(lapply(idx, function(x) which(divPaidByYear$Symbol == grp$Symbol[x], arr.ind=T) ))
## Remove from the dividend history
divPaidByYear = divPaidByYear[–(vdx), ]
#### define the compound growth function
cagr5 <- function(x)
{ as.vector (na.omit ( (ROC(unlist(x), 5, type=“discrete” ) +1) ^ 0.2 )) }
# power of 0.2 is because of 5 yr compounding – i.e., 1/5
### lets make sure years are in ascending order
divPaidByYear = divPaidByYear[with(divPaidByYear, order(Symbol,Year) ),]
### apply 5 year lag to ROC
dv5YrGrth = aggregate(divPaidByYear$AnnualDividend, by = divPaidByYear[c(“Symbol”)], FUN=cagr5 )
#### to get most recent 5 yr growth – will be last in list
dv5YrGrth$x$`0544`[length(dv5YrGrth$x$`0544`)]
[1] 1.093209
##### get most recent 5 yr growth & keep in list
dv5YrGrth$Last = lapply(dv5YrGrth$x, function(z) z[length(z)] )
### create data frame
df5Yr = as.data.frame(cbind(Symbol=dv5YrGrth$Symbol, CAGR5=dv5YrGrth$Last) )
head(df5Yr)
Symbol CAGR5
0001 A 1.044276
0002 AAN 1.116646
0003 AAPL 0.7510579
0004 AAT 1.050997
0005 ABB 1.022867
0006 ABBV 1.175425
### complete our selection matrix with cagr5 yr rate
for ( ix in 1:nrow(extXL) ) {
found = df5Yr$CAGR5[(df5Yr$Symbol == extXL$Symbol[ix])]
extXL$CAGR5Yr[ix] = ifelse( length(found) != 0, found, “NA” )
}
> nrow(extXL)
[1] 416
> extXL[250:280,]
Symbol NoYrs Seq Price Yield AnnualDividend PayoutRatio TTMPE PEGRatio MRQPBook MarketCap Sector CAGR5Yr
312 BMRC 7 324 33.40 1.92 0.64 27.23 14.21 0.94 1.51 0.17598 Finance 1.215894
322 BHB 7 329 27.23 3.89 1.06 40.30 10.35 N/A 0.97 0.10358 Finance 0.9115486
332 BBY 8 273 42.72 1.40 0.60 18.07 12.87 1.04 2.91 17 Consumer Services 1.214934
342 BHP 9 238 82.40 2.18 1.80 39.47 18.07 1.32 4.79 229.25 Basic Industries 1.003425
352 BBL 8 270 71.16 2.53 1.80 39.47 15.61 0.50 4.12 197.98 Energy 1.003425
362 BDMS 7 299 17.90 4.47 0.80 95.24 21.31 – 4.28 0.03312 NODATA 1.019245
372 BWP 5 404 31.00 6.65 2.06 159.69 24.03 4.50 1.87 5.97 NODATA NA
382 BOBE 5 398 31.14 2.57 0.80 41.45 16.13 1.62 1.50 0.94033 NODATA NA
392 BOKF 6 351 46.70 2.14 1.00 29.59 13.82 1.23 1.28 3.18 Finance 1.042909
402 BG 9 234 60.82 1.51 0.92 6.63 4.38 1.71 0.82 8.81 Consumer Non–Durables 1.109888
412 CPB 7 333 33.90 3.42 1.16 47.93 14.01 2.13 12.35 11.39 Consumer Non–Durables 1.183876
422 CASS 9 243 35.36 1.81 0.64 30.92 17.08 N/A 2.28 0.33217 Miscellaneous 1.066286
432 CNP 5 384 15.63 4.99 0.78 74.29 14.89 2.57 2.11 6.61 Public Utilities 1.060052
442 CPK 7 314 37.59 3.51 1.32 49.07 13.97 2.28 1.62 0.35748 Public Utilities 0.9885569
452 CHEV 7 296 8.91 4.94 0.44 183.33 37.13 N/A 1.10 0.07775 NODATA NA
462 CHL 5 400 49.85 3.65 1.82 41.98 11.49 N/A 2.50 200.02 Public Utilities 0.9938379
472 CEO 5 397 215.30 2.51 5.40 37.51 14.95 1.49 3.45 96.17 Energy 1.00799
482 KOF 7 302 80.98 1.43 1.16 27.79 19.47 1.73 2.73 14.95 Consumer Non–Durables 1.028711
492 COLM 5 409 55.64 1.44 0.80 36.87 25.64 2.03 1.80 1.87 Consumer Non–Durables 0.9977925
### Now we need to select stocks according to our criteria
### Could do it in regular base R, but illustrate instead
### another method
### using sqldf package to do more of a natural syntax
### show how we can use this to pick stock satisfying
### different criteria
### the SQL that can be used is pretty basic and in no way
### comes close to the power offered by say any of the other
### database vendors such as Oracle 12c, or DB2 or SQLServer
if(!is.element(“sqldf”, installed.packages()[,1]) ) install.packages(“sqldf”)
require(sqldf)
### clean up number data in our dividend champions list above
### and add new columns needed,
### 5-yr dividend growth & Graham number (a valuation metric)
extXL$CAGR5Yr = round(as.numeric(f1),digits=4)
extXL$Graham = round((22.5 * extXL$MRQPBook / extXL$TTMPE) ^ 0.5, digits=2)
numCols = c(“NoYrs”, “Seq”, “Price”, “Yield”,
“AnnualDividend”, “PayoutRatio”, “TTMPE”,
“PEGRatio”, “MRQPBook”,“Graham”, “MarketCap”)
for (nm in numCols) extXL[, nm] = as.numeric(extXL[,nm])
### Pick stocks with higher than 4.5% dividend yield
### consistent dividend history of 10 years or more
### And at least 5% annual dividend growth
sqldf(“select Symbol, Sector, Yield, Graham, CAGR5Yr
from extXL where Yield > 4.5 and NoYrs > 9
and CAGR5Yr > 1.05 and CAGR5Yr not in (‘NA’)
order by Sector, Yield desc”)
Symbol Sector Yield Graham CAGR5Yr
1 MO Consumer Non–Durables 6.33 4.07 1.1027
2 UVV Consumer Services 4.69 1.75 1.1087
3 MMP Energy 5.32 2.22 1.1258
4 GTY Finance 6.44 2.01 1.1037
5 ORI Finance 5.45 0.20 1.1984
6 CINF Finance 5.31 1.49 1.0508
7 BWL–A NODATA 4.60 1.07 1.0671
8 VVC Public Utilities 5.33 1.52 1.0513
9 ATO Public Utilities 4.52 1.43 1.0687
######################################################
### lower yield, fewer years of dividends
### but higher than 10% annual dividend growth
sqldf(“select Symbol, Sector, Yield, Graham, CAGR5Yr
from extXL where Yield > 2.05 and NoYrs > 4
and CAGR5Yr > 1.10 and CAGR5Yr not in (‘NA’)
order by Sector, Yield desc”)
Symbol Sector Yield Graham CAGR5Yr
1 RPM Basic Industries 4.10 1.88 1.1368
2 IFF Basic Industries 2.06 2.47 1.1423
3 LMT Capital Goods 4.41 3.92 1.1140
4 RTN Capital Goods 3.24 1.99 1.1418
5 NOC Capital Goods 3.05 1.84 1.1458
6 GD Capital Goods 2.54 2.01 1.1666
7 ITT Capital Goods 2.17 1.94 1.1087
8 CAT Capital Goods 2.08 2.09 1.1378
9 WSO Consumer Durables 3.45 1.50 1.3725
10 ODC Consumer Durables 2.86 1.51 1.1986
11 MO Consumer Non–Durables 6.33 4.07 1.1027
12 CPB Consumer Non–Durables 3.42 4.45 1.1839
13 WEYS Consumer Non–Durables 2.64 1.31 1.1100
14 HAS Consumer Non–Durables 2.10 2.42 1.1544
15 ADM Consumer Non–Durables 2.07 1.61 1.1201
16 UVV Consumer Services 4.69 1.75 1.1087
17 R Consumer Services 2.51 1.18 1.1028
18 MMP Energy 5.32 2.22 1.1258
19 ENB Energy 2.98 1.73 1.1037
20 GTY Finance 6.44 2.01 1.1037
21 ORI Finance 5.45 0.20 1.1984
22 ERIE Finance 3.05 2.03 1.1358
23 THFF Finance 3.05 1.34 1.1627
24 EV Finance 2.42 3.12 1.1560
25 THG Finance 2.21 1.16 1.1030
26 AFG Finance 2.11 1.55 1.1978
27 ABT Health Care 3.78 2.22 1.1487
28 MDT Health Care 2.68 2.31 1.1225
29 MMM Health Care 2.50 2.43 1.1645
30 CAH Health Care 2.19 2.02 1.1475
31 NEE Public Utilities 3.95 1.82 1.1096
32 OKE Public Utilities 3.76 1.78 1.1700
33 EIX Public Utilities 3.41 1.65 1.1217
34 MXIM Technology 3.61 1.38 1.1197
35 ITW Technology 2.86 2.02 1.1735
36 MSFT Technology 2.53 3.10 1.1214
######################################################
### lower yield, fewer years of dividends ; but higher growth
### but higher than 10% annual dividend growth
### But check valuation measure – less than 55% overvalued per Graham
sqldf(“select Symbol, Sector, Yield, Graham, CAGR5Yr
from extXL where Yield > 2.05 and NoYrs > 4
and CAGR5Yr > 1.10 and CAGR5Yr not in (‘NA’)
and Graham < 1.55 and Graham not in (‘NA’)
order by Sector, Yield desc”)
Symbol Sector Yield Graham CAGR5Yr
1 WSO Consumer Durables 3.45 1.50 1.3725
2 ODC Consumer Durables 2.86 1.51 1.1986
3 WEYS Consumer Non–Durables 2.64 1.31 1.1100
4 R Consumer Services 2.51 1.18 1.1028
5 ORI Finance 5.45 0.20 1.1984
6 THFF Finance 3.05 1.34 1.1627
7 THG Finance 2.21 1.16 1.1030
8 MXIM Technology 3.61 1.38 1.1197
######################################################
### lower yield, fewer years of dividends ; but higher growth
### but higher than 10% annual dividend growth
### less than a 50% payout – out of EPS (better to use free cash flow but dont have this)
### But check valuation measure – less than 55% overvalued per Graham
sqldf(“select Symbol, Sector, Yield, Graham, CAGR5Yr
from extXL where Yield > 2.05 and NoYrs > 4
and CAGR5Yr > 1.10 and CAGR5Yr not in (‘NA’)
and Graham < 1.55 and Graham not in (‘NA’)
and PayoutRatio < 50
order by Sector, Yield desc”)
### narrows it down further
Symbol Sector Yield Graham CAGR5Yr
1 ODC Consumer Durables 2.86 1.51 1.1986
2 THFF Finance 3.05 1.34 1.1627
3 THG Finance 2.21 1.16 1.1030
######################################################
### lets pick some stocks and run a performance analysis
pk1 = sqldf(“select Symbol, Sector, Yield, Graham, CAGR5Yr
from extXL where Yield > 4.5 and NoYrs > 9
and CAGR5Yr > 1.05 and CAGR5Yr not in (‘NA’)
order by Sector, Yield desc”)
picked = pk1$Symbol
picked = c(picked, “RPM”, “IFF”, “LMT”, “RTN”, “NOC”, “ODC”, “CPB”, “R”, “MMP”, “GTY”, “THFF”, “ABT”, “MMM”, “MXIM”, “ITW”, “MSFT”)
### Details of picked companies by sector & yield
extXL[(extXL$Symbol %in% picked), ] %>% .[order(.$Sector, –.$Yield), ]
Symbol NoYrs Seq Price Yield AnnualDividend PayoutRatio TTMPE PEGRatio MRQPBook MarketCap Sector CAGR5Yr Graham
80 RPM 37 60 20.48 4.10 0.84 61.76 15.06 1.43 2.36 2.64000 Basic Industries 1.1368 1.88
932 IFF 8 271 52.52 2.06 1.08 33.75 16.41 2.05 4.45 4.20000 Basic Industries 1.1423 2.47
1081 LMT 8 279 68.04 4.41 3.00 40.49 9.18 1.23 6.27 24.50000 Capital Goods 1.1140 3.92
142 RTN 6 346 46.25 3.24 1.50 30.93 9.54 1.28 1.68 16.86000 Capital Goods 1.1418 1.99
1241 NOC 7 307 61.68 3.05 1.88 27.49 9.02 0.82 1.36 18.01000 Capital Goods 1.1458 1.84
1301 ODC 8 266 22.41 2.86 0.64 49.23 17.24 NA 1.75 0.15694 Consumer Durables 1.1986 1.51
11 MO 42 30 24.00 6.33 1.52 85.39 13.48 2.04 9.90 50.11000 Consumer Non–Durables 1.1027 4.07
412 CPB 7 333 33.90 3.42 1.16 47.93 14.01 2.13 12.35 11.39000 Consumer Non–Durables 1.1839 4.45
93 UVV 40 37 40.91 4.69 1.92 37.72 8.04 NA 1.09 0.97337 Consumer Services 1.1087 1.75
152 R 6 363 43.11 2.51 1.08 64.67 25.81 1.44 1.61 2.23000 Consumer Services 1.1028 1.18
721 MMP 10 222 56.00 5.32 2.98 105.30 19.79 4.54 4.35 6.30000 Energy 1.1258 2.22
541 GTY 11 207 29.81 6.44 1.92 101.59 15.77 23.15 2.84 0.89254 Finance 1.1037 2.01
70 ORI 29 84 12.66 5.45 0.69 2300.00 422.00 17.78 0.74 3.00000 Finance 1.1984 0.20
27 CINF 50 11 30.15 5.31 1.60 52.63 9.92 2.24 0.98 4.91000 Finance 1.0508 1.49
511 THFF 22 111 30.14 3.05 0.92 47.18 15.46 NA 1.23 0.39637 Finance 1.1627 1.34
7 ABT 38 49 46.51 3.78 1.76 58.09 15.35 1.11 3.36 71.90000 Health Care 1.1487 2.22
6 MMM 52 9 83.98 2.50 2.10 37.10 14.84 1.23 3.88 60.03000 Health Care 1.1645 2.43
19 BWL–A 38 46 13.49 4.60 0.62 172.22 37.47 NA 1.91 0.06942 NODATA 1.0671 1.07
95 VVC 51 10 25.90 5.33 1.38 78.41 14.72 3.26 1.51 2.11000 Public Utilities 1.0513 1.52
161 ATO 23 108 30.07 4.52 1.36 61.82 13.67 3.66 1.25 2.72000 Public Utilities 1.0687 1.43
1091 MXIM 9 235 23.25 3.61 0.84 129.23 35.77 1.23 3.01 6.88000 Technology 1.1197 1.38
51 ITW 47 18 47.63 2.86 1.36 41.85 14.66 0.97 2.65 23.62000 Technology 1.1735 2.02
1121 MSFT 5 412 25.26 2.53 0.64 27.47 10.84 1.04 4.62 216.09000 Technology 1.1214 3.10
### download prices for above stocks
### calculate returns
### also assumed that bencxhmark SP500 has been downloaded
### show basic charts
portfolioPrices <- NULL
for (Ticker in picked)
portfolioPrices <- cbind(portfolioPrices, getSymbols(src=‘tiingo’,Ticker, from=“2010-10-01”, adjust=TRUE, auto.assign=FALSE)[,4])
portfolioPrices <- round(portfolioPrices,2)
#### get adjusted prices col [,4] is close OHLC + Volume 5th col
### using this function ensures only take Close
returnsLog = Return.calculate(portfolioPrices[endpoints(portfolioPrices), on=“months”], method=“discrete”)
### download Benchmark SP500 data from Yahoo website & save on local file
### read back as xts/zoo
GSPC500 = as.xts(read.zoo(“Z:/Downloads/^GSPC.csv”,head=TRUE,as.is=TRUE,sep=“,”,format=“%Y-%m-%d”))
### get monthly returns on adj close
GSPC500MthlyRet = Return.calculate(GSPC500$Adj.Close[(endpoints(GSPC500, on=“months”))])
### check start month dates on portFolioPrices & SP500
### make sure use price data left – discard bmark not in price data
mrg = na.omit(merge.xts(returnsLog, bmark500, join=‘left’))
### 25 is because of nbr – of stocks in portfolio;
### last col is bechmark asset
nbr=ncol(portfolioPrices)
### individual charts – very cluttered
charts.PerformanceSummary(mrg[,1:nbr])
### total pfolio return
charts.PerformanceSummary(Return.portfolio(mrg[,1:nbr]), col=“blue”)
### Relative to benchmark – shows difference only
chart.RelativePerformance(Return.portfolio(mrg[,1:nbr]), Rb=mrg[,(nbr+1)], , col=“red”)
#### if we want to show it one plot
### need to use ggplot together with some data manipulation
### flatten out pfolio return + benchmark
### Also need to keep rolling cumulative return
### in one dataframe, i.e., combine them
### use variable to key in on color
### colr key is 111-Pfolio; 222 – BenchMark
### first get portfolio total return
combo <- cbind( cumprod(1 + na.omit(Return.portfolio(mrg[,1:nbr]))), KEYVAR = 111)
## now add in rows for benchmark
combo <- rbind( combo,
cbind(cumprod(1 + na.omit(Return.portfolio(mrg[,(1+nbr)]))), KEYVAR = 222)
)
colnames(combo) = c(“Cumulative.Return”, “KEYVAR”)
head(combo); tail(combo)
### flattened and combined DF looks like this
Cumulative.Return KEYVAR
2010–11–29 16:00:00 1.0122989 111
2010–11–29 16:00:00 0.9977098 222
2010–12–30 16:00:00 1.0600344 111
2010–12–30 16:00:00 1.0628602 222
2011–01–30 16:00:00 1.0561812 111
2011–01–30 16:00:00 1.0869293 222
Cumulative.Return KEYVAR
2018–11–29 16:00:00 3.455277 111
2018–11–29 16:00:00 2.332683 222
2018–12–30 16:00:00 3.203333 111
2018–12–30 16:00:00 2.118596 222
2019–01–17 16:00:00 3.365169 111
2019–01–17 16:00:00 2.257078 222
### lets remove the timestamp from index
index(combo) = round(index(combo), “day”)
gpl = ggplot(combo, aes(index(combo), Cumulative.Return, colour=as.factor(KEYVAR) ) )
gpl = gpl + geom_line()
gpl = gpl + scale_colour_discrete(labels=c(“Combined Portfolio”, “Benchmark SP500”))
gpl = gpl + labs(colour = “Asset Composition”)
gpl <- gpl + ggtitle(“Portfolio Growth”, subtitle=“Benchmark Comparison”) + xlab(“Date”) + ylab(“Accumulated Return”)
plot(gpl)
Charts produced are as follows:
Thanx for reading
