Live Case: S&P500 (3 of 3)

Aug 10, 2023. -=-

Agenda: Analyzing a particular Sector within the S&P500 Index We have chosen to deeply analyze the HEALTH TECHNOLOGY Sector.

S&P 500 Data - PRELIMINARY SETUP

We will continue our analysis of the S&P 500. Load the data, as described in the chapter Live Case: S&P500 (1 of 3)

# Read S&P500 stock data present in a Google Sheet.
library(gsheet)
prefix <- "https://docs.google.com/spreadsheets/d/"
sheetID <- "11ahk9uWxBkDqrhNm7qYmiTwrlSC53N1zvXYfv7ttOCM"
url500 <- paste(prefix,sheetID) # Form the URL to connect to
sp500 <- gsheet2tbl(url500) # Read it into a tibble called sp500

No encoding supplied: defaulting to UTF-8.

Rename columns, as described in the chapter Live Case: S&P500 (1 of 3).

suppressPackageStartupMessages(library(dplyr))

# Define a mapping of new column names
new_names <- c(
  "Date", "Stock", "StockName", "Sector", "Industry", 
  "MarketCap", "Price", "Low52Wk", "High52Wk", 
  "ROE", "ROA", "ROIC", "GrossMargin", 
  "OperatingMargin", "NetMargin", "PE", 
  "PB", "EVEBITDA", "EBITDA", "EPS", 
  "EBITDA_YOY", "EBITDA_QYOY", "EPS_YOY", 
  "EPS_QYOY", "PFCF", "FCF", 
  "FCF_QYOY", "DebtToEquity", "CurrentRatio", 
  "QuickRatio", "DividendYield", 
  "DividendsPerShare_YOY", "PS", 
  "Revenue_YOY", "Revenue_QYOY", "Rating"
)
# Rename the columns using the new_names vector
sp500 <- sp500 %>% 
  rename_with(~ new_names, everything())

Remove Rows containing no data or Null values, as described in the chapter Live Case: S&P500 (1 of 3).

# Check for blank or null values in the "Stock" column
hasNull <- any(sp500$Stock == "" | is.null(sp500$Stock))
if (hasNull) { 
    # Remove rows with null or blank values from the dataframe tibble
    sp500 <- sp500[!(is.null(sp500$Stock) | sp500$Stock == ""), ]
}

The S&P500 shares are divided into multiple Sectors. Thus, model Sector as a factor() variable, as described in the chapter Live Case: S&P500 (1 of 3).

sp500$Sector <- as.factor(sp500$Sector)

Stock Ratings: The S&P500 shares have Technical Ratings such as {Buy, Sell, ..}. Model the data column Rating as a factor() variable, as described in the chapter Live Case: S&P500 (1 of 3).

sp500$Rating <- as.factor(sp500$Rating)

Low52WkPerc: Create a new column to track Share Prices relative to their 52 Week Low, as described in the chapter Live Case: S&P500 (1 of 3).

sp500 <- sp500 %>% mutate(Low52WkPerc = round((Price - Low52Wk)*100 / Low52Wk,2))
colnames(sp500)

 [1] "Date"                  "Stock"                 "StockName"            
 [4] "Sector"                "Industry"              "MarketCap"            
 [7] "Price"                 "Low52Wk"               "High52Wk"             
[10] "ROE"                   "ROA"                   "ROIC"                 
[13] "GrossMargin"           "OperatingMargin"       "NetMargin"            
[16] "PE"                    "PB"                    "EVEBITDA"             
[19] "EBITDA"                "EPS"                   "EBITDA_YOY"           
[22] "EBITDA_QYOY"           "EPS_YOY"               "EPS_QYOY"             
[25] "PFCF"                  "FCF"                   "FCF_QYOY"             
[28] "DebtToEquity"          "CurrentRatio"          "QuickRatio"           
[31] "DividendYield"         "DividendsPerShare_YOY" "PS"                   
[34] "Revenue_YOY"           "Revenue_QYOY"          "Rating"               
[37] "Low52WkPerc"

Well done! Our data is now ready for analysis!!

Creating a new column MarketCapBillions = MarketCap/1000,000,000

sp500 <- sp500 %>% mutate(MarketCapBillions = MarketCap/ 1000000000)
colnames(sp500)

 [1] "Date"                  "Stock"                 "StockName"            
 [4] "Sector"                "Industry"              "MarketCap"            
 [7] "Price"                 "Low52Wk"               "High52Wk"             
[10] "ROE"                   "ROA"                   "ROIC"                 
[13] "GrossMargin"           "OperatingMargin"       "NetMargin"            
[16] "PE"                    "PB"                    "EVEBITDA"             
[19] "EBITDA"                "EPS"                   "EBITDA_YOY"           
[22] "EBITDA_QYOY"           "EPS_YOY"               "EPS_QYOY"             
[25] "PFCF"                  "FCF"                   "FCF_QYOY"             
[28] "DebtToEquity"          "CurrentRatio"          "QuickRatio"           
[31] "DividendYield"         "DividendsPerShare_YOY" "PS"                   
[34] "Revenue_YOY"           "Revenue_QYOY"          "Rating"               
[37] "Low52WkPerc"           "MarketCapBillions"

SECTOR LEVEL ANALYSIS begins here

Filter the data by sector Health Services, and display the number of stocks in the sector

ts <- sp500 %>%
        filter(Sector=='Health Services')

nrow(ts)

[1] 12

There are 12 number of of stocks in the sector Health Services

Select the Specific Coulumns from the filtered dataframe `ts` (Health Services)

ts2 <- ts %>%
        select(Date, Stock, StockName,Sector,  Industry, MarketCap, Price,Low52Wk, High52Wk,
               ROE, ROA,ROIC,GrossMargin, GrossMargin, 
               NetMargin, Rating)

colnames(ts2)

 [1] "Date"        "Stock"       "StockName"   "Sector"      "Industry"   
 [6] "MarketCap"   "Price"       "Low52Wk"     "High52Wk"    "ROE"        
[11] "ROA"         "ROIC"        "GrossMargin" "NetMargin"   "Rating"

Arrange the Dataframe by ROE

ts3 <- ts2 %>% arrange(desc(ROE))

Top 10 Shares in Sector Health Services Based on ROE

head(ts3,10)

# A tibble: 10 × 15
   Date   Stock StockName Sector Industry MarketCap Price Low52Wk High52Wk   ROE
   <chr>  <chr> <chr>     <fct>  <chr>        <dbl> <dbl>   <dbl>    <dbl> <dbl>
 1 8/15/… DVA   DaVita I… Healt… Medical…   9.89e 9 108.     65.3    117    56  
 2 8/15/… MOH   Molina H… Healt… Managed…   1.84e10 316.    256.     374    28.4
 3 8/15/… UNH   UnitedHe… Healt… Managed…   4.71e11 508.    446.     558    27.2
 4 8/15/… HUM   Humana I… Healt… Managed…   6.12e10 494.    423.     571    20.9
 5 8/15/… IQV   IQVIA Ho… Healt… Service…   4.03e10 220.    166.     249    19.7
 6 8/15/… ELV   Elevance… Healt… Managed…   1.12e11 474.    412      550    17.3
 7 8/15/… CI    The Cign… Healt… Managed…   8.57e10 290.    240.     340    14.6
 8 8/15/… DGX   Quest Di… Healt… Service…   1.52e10 135.    120.     158    12.5
 9 8/15/… UHS   Universa… Healt… Hospita…   8.18e 9 132.     82.5    159    11.6
10 8/15/… CNC   Centene … Healt… Managed…   3.56e10  65.8    61.3     98.5  10.4
# ℹ 5 more variables: ROA <dbl>, ROIC <dbl>, GrossMargin <dbl>,
#   NetMargin <dbl>, Rating <fct>

Mutate a data column called (Low52WkPerc), then show top 10 ROE stocks

ts4 <- ts3 %>% mutate(Low52WkPerc = round((Price - Low52Wk)*100 / Low52Wk,2))
head(ts4[,c(1:3,10,16)],10)

# A tibble: 10 × 5
   Date      Stock StockName                         ROE Low52WkPerc
   <chr>     <chr> <chr>                           <dbl>       <dbl>
 1 8/15/2023 DVA   DaVita Inc.                      56         66.0 
 2 8/15/2023 MOH   Molina Healthcare Inc            28.4       23.2 
 3 8/15/2023 UNH   UnitedHealth Group Incorporated  27.2       14.0 
 4 8/15/2023 HUM   Humana Inc.                      20.9       16.7 
 5 8/15/2023 IQV   IQVIA Holdings, Inc.             19.7       32.6 
 6 8/15/2023 ELV   Elevance Health, Inc.            17.3       15.1 
 7 8/15/2023 CI    The Cigna Group                  14.6       20.4 
 8 8/15/2023 DGX   Quest Diagnostics Incorporated   12.5       12.1 
 9 8/15/2023 UHS   Universal Health Services, Inc.  11.6       59.5 
10 8/15/2023 CNC   Centene Corporation              10.4        7.19

Summary Statistics of ROE

ts3 <- na.omit(ts3)

ROESum <- ts3 %>%
  summarise(
    Mean = mean(ROE),
    Median= sd(ROE),
    Median= median(ROE),
    Q1 = quantile(ROE, probs = 0.25, na.rm = TRUE),
    Q3 = quantile(ROE, probs = 0.75, na.rm = TRUE),
    Min = min(ROE),
    max = max(ROE)
  )

ROESum <- round(ROESum,2)
ROESum

# A tibble: 1 × 6
   Mean Median    Q1    Q3   Min   max
  <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl>
1  21.6   12.5  11.6  19.7  8.31    56

Summary Statistics of All key variables in Sector Health Services

ts3 <- na.omit(ts3)

ROESum <- ts3 %>%
  summarise(
    Mean = mean(ROE),
    Median= sd(ROE),
    Median= median(ROE),
    Q1 = quantile(ROE, probs = 0.25, na.rm = TRUE),
    Q3 = quantile(ROE, probs = 0.75, na.rm = TRUE),
    Min = min(ROE),
    max = max(ROE)
  )

ROESum <- round(ROESum,2)


ROASum <- ts3 %>%
  summarise(
    Mean = mean(ROA),
    Median= sd(ROA),
    Median= median(ROA),
    Q1 = quantile(ROA, probs = 0.25, na.rm = TRUE),
    Q3 = quantile(ROA, probs = 0.75, na.rm = TRUE),
    Min = min(ROA),
    max = max(ROA)
  )

ROASum <- round(ROASum,2)

ROICSum <- ts3 %>%
  summarise(
    Mean = mean(ROIC),
    Median= sd(ROIC),
    Median= median(ROIC),
    Q1 = quantile(ROIC, probs = 0.25, na.rm = TRUE),
    Q3 = quantile(ROIC, probs = 0.75, na.rm = TRUE),
    Min = min(ROIC),
    max = max(ROIC)
  )

ROICSum <- round(ROICSum,2)

GrossMarginSum <- ts3 %>%
  summarise(
    Mean = mean(GrossMargin),
    Median= sd(GrossMargin),
    Median= median(GrossMargin),
    Q1 = quantile(GrossMargin, probs = 0.25, na.rm = TRUE),
    Q3 = quantile(GrossMargin, probs = 0.75, na.rm = TRUE),
    Min = min(GrossMargin),
    max = max(GrossMargin)
  )

GrossMarginSum <- round(GrossMarginSum,2)

NetMarginSum <- ts3 %>%
  summarise(
    Mean = mean(NetMargin),
    Median= sd(NetMargin),
    Median= median(NetMargin),
    Q1 = quantile(NetMargin, probs = 0.25, na.rm = TRUE),
    Q3 = quantile(NetMargin, probs = 0.75, na.rm = TRUE),
    Min = min(NetMargin),
    max = max(NetMargin)
  )

NetMarginSum <- round(NetMarginSum,2)

Metrics <- c("ROE","ROA","ROIC","GrossMargin","NetMargin")

ftab <- rbind(ROESum, ROASum, ROICSum, GrossMarginSum, NetMarginSum)
ftab <- cbind(Metrics, ftab)
ftab

      Metrics  Mean Median    Q1    Q3  Min   max
1         ROE 21.62  12.50 11.60 19.70 8.31 56.00
2         ROA  4.44   4.34  4.18  5.12 2.67  5.91
3        ROIC  5.71   5.98  5.13  6.35 3.74  7.37
4 GrossMargin 23.28  25.55 23.00 27.09 7.94 32.83
5   NetMargin  6.07   5.65  5.01  7.48 3.87  8.33

Summary Statistics of ROE by each Sector of S&P500

SectorROE <- sp500 %>%
  group_by(Sector) %>%
  summarise(
    Mean = mean(na.omit(ROE)),
    Median= sd(na.omit(ROE)),
    Median= median(na.omit(ROE)),
    Q1 = quantile(na.omit(ROE), probs = 0.25, na.rm = TRUE),
    Q3 = quantile(na.omit(ROE), probs = 0.75, na.rm = TRUE),
    Min = min(na.omit(ROE)),
    max = max(na.omit(ROE))
  )

cbind(Sector = SectorROE$Sector, round(SectorROE[,2:7],2))

                   Sector   Mean Median    Q1    Q3      Min    max
1     Commercial Services  37.95  26.40 16.40 43.60     3.50  175.0
2          Communications   8.08   9.05  0.52 16.12    -8.01   23.2
3       Consumer Durables  12.42  17.75  6.87 25.38   -51.40   45.2
4   Consumer Non-Durables 129.59  19.60  6.41 34.60   -11.50 2880.0
5       Consumer Services  31.10   9.41  1.46 42.88  -186.00  360.0
6   Distribution Services  81.08  34.20 22.15 56.45     5.14  371.0
7   Electronic Technology  30.51  18.75  8.12 35.18   -14.20  160.0
8         Energy Minerals  43.11  26.95 23.78 41.45    18.00  230.0
9                 Finance  21.52  10.95  7.64 16.67   -39.20  714.0
10        Health Services  20.63  17.30 12.05 24.05     8.31   56.0
11      Health Technology  20.08  13.10  6.80 23.33   -49.30  173.0
12    Industrial Services  21.04  22.60 10.70 31.10     7.67   36.5
13    Non-Energy Minerals  13.84  13.50  3.40 21.80    -3.83   36.8
14     Process Industries  25.59  18.60 15.35 22.85   -13.20  126.0
15 Producer Manufacturing  24.24  19.40 12.80 29.40   -13.60   95.9
16           Retail Trade -39.08  25.15 14.72 39.38 -1220.00   66.9
17    Technology Services  33.07  17.85 10.70 31.08   -70.60  417.0
18         Transportation  36.32  33.50 20.85 49.08     4.13  104.0
19              Utilities   8.12   8.70  7.66 10.60   -47.60   35.5

ANALYSIS OF HEALTH SERVICES SECTOR

Market Cap of all companies in Sector Health Services

library(janitor)
library(kableExtra)
# Market Cap by Stock
MCap <- ts3 %>%
  group_by(Stock) %>%
  summarise(
    MarketCapCr = sum(na.omit(MarketCap)/10000000))

# Sp500 Market Cap

SP500MarketCap <- sum(ts3$MarketCap/10000000)

# calculating % market cap
PercentMarketCap <- round(MCap$MarketCapCr*100/SP500MarketCap,2)
MCapTab <- cbind(MCap,PercentMarketCap)

# sorting by PercentMarketCap
MCapTab <- MCapTab %>% arrange(desc(PercentMarketCap))


MCapTab <- MCapTab %>%
  adorn_totals("row")

MCapTab <- knitr::kable(MCapTab, "html") %>% kable_styling() 
MCapTab

Stock	MarketCapCr	PercentMarketCap
IQV	4030	43.39
LH	1930	20.78
DGX	1520	16.37
DVA	989	10.65
UHS	818	8.81
Total	9287	100.00

Shares which are most attractively priced in Sector Health Services

AttrShares <- ts4 %>% arrange(Low52WkPerc)
AttrShares <- AttrShares[, c(2:4,7,8,10,11,16)]

AttrShares <- knitr::kable(AttrShares, "html") %>% kable_styling() 
AttrShares

Stock	StockName	Sector	Price	Low52Wk	ROE	ROA	Low52WkPerc
CNC	Centene Corporation	Health Services	65.75	61.3400	10.40	3.31	7.19
DGX	Quest Diagnostics Incorporated	Health Services	134.99	120.4000	12.50	5.91	12.12
UNH	UnitedHealth Group Incorporated	Health Services	508.12	445.6800	27.20	8.27	14.01
ELV	Elevance Health, Inc.	Health Services	474.30	412.0000	17.30	6.10	15.12
HUM	Humana Inc.	Health Services	493.79	423.2900	20.90	6.54	16.66
CI	The Cigna Group	Health Services	289.61	240.5000	14.60	4.55	20.42
MOH	Molina Healthcare Inc	Health Services	315.73	256.1900	28.40	6.98	23.24
LH	Laboratory Corporation of America Holdings	Health Services	218.05	172.0895	8.31	4.18	26.71
IQV	IQVIA Holdings, Inc.	Health Services	219.83	165.7500	19.70	4.34	32.63
HCA	HCA Healthcare, Inc.	Health Services	269.63	178.3200	NA	11.00	51.21
UHS	Universal Health Services, Inc.	Health Services	131.58	82.5000	11.60	5.12	59.49
DVA	DaVita Inc.	Health Services	108.36	65.2800	56.00	2.67	65.99

PROFITABILITY OF HEALTH SERVICES SECTOR

Shares have highest ROE within Sector Technology Services

AttrShares <- ts4 %>% arrange(desc(ROE))
AttrShares <- AttrShares[, c(2:4,7,8,10,11,16)]

AttrShares <- knitr::kable(AttrShares, "html") %>% kable_styling() 
AttrShares

Stock	StockName	Sector	Price	Low52Wk	ROE	ROA	Low52WkPerc
DVA	DaVita Inc.	Health Services	108.36	65.2800	56.00	2.67	65.99
MOH	Molina Healthcare Inc	Health Services	315.73	256.1900	28.40	6.98	23.24
UNH	UnitedHealth Group Incorporated	Health Services	508.12	445.6800	27.20	8.27	14.01
HUM	Humana Inc.	Health Services	493.79	423.2900	20.90	6.54	16.66
IQV	IQVIA Holdings, Inc.	Health Services	219.83	165.7500	19.70	4.34	32.63
ELV	Elevance Health, Inc.	Health Services	474.30	412.0000	17.30	6.10	15.12
CI	The Cigna Group	Health Services	289.61	240.5000	14.60	4.55	20.42
DGX	Quest Diagnostics Incorporated	Health Services	134.99	120.4000	12.50	5.91	12.12
UHS	Universal Health Services, Inc.	Health Services	131.58	82.5000	11.60	5.12	59.49
CNC	Centene Corporation	Health Services	65.75	61.3400	10.40	3.31	7.19
LH	Laboratory Corporation of America Holdings	Health Services	218.05	172.0895	8.31	4.18	26.71
HCA	HCA Healthcare, Inc.	Health Services	269.63	178.3200	NA	11.00	51.21

Shares have highest ROA within Sector Health Services

AttrShares <- ts4 %>% arrange(desc(ROA))
AttrShares <- AttrShares[, c(2:4,7,8,10,11,16)]

AttrShares <- knitr::kable(AttrShares, "html") %>% kable_styling() 
AttrShares

Stock	StockName	Sector	Price	Low52Wk	ROE	ROA	Low52WkPerc
HCA	HCA Healthcare, Inc.	Health Services	269.63	178.3200	NA	11.00	51.21
UNH	UnitedHealth Group Incorporated	Health Services	508.12	445.6800	27.20	8.27	14.01
MOH	Molina Healthcare Inc	Health Services	315.73	256.1900	28.40	6.98	23.24
HUM	Humana Inc.	Health Services	493.79	423.2900	20.90	6.54	16.66
ELV	Elevance Health, Inc.	Health Services	474.30	412.0000	17.30	6.10	15.12
DGX	Quest Diagnostics Incorporated	Health Services	134.99	120.4000	12.50	5.91	12.12
UHS	Universal Health Services, Inc.	Health Services	131.58	82.5000	11.60	5.12	59.49
CI	The Cigna Group	Health Services	289.61	240.5000	14.60	4.55	20.42
IQV	IQVIA Holdings, Inc.	Health Services	219.83	165.7500	19.70	4.34	32.63
LH	Laboratory Corporation of America Holdings	Health Services	218.05	172.0895	8.31	4.18	26.71
CNC	Centene Corporation	Health Services	65.75	61.3400	10.40	3.31	7.19
DVA	DaVita Inc.	Health Services	108.36	65.2800	56.00	2.67	65.99

Shares have highest NetMargin within Sector Health Services

AttrShares <- ts4 %>% arrange(desc(NetMargin))
AttrShares <- AttrShares[, c(2:4,7,8,10,11,14,16)]

AttrShares <- knitr::kable(AttrShares, "html") %>% kable_styling() 
AttrShares

Stock	StockName	Sector	Price	Low52Wk	ROE	ROA	NetMargin	Low52WkPerc
HCA	HCA Healthcare, Inc.	Health Services	269.63	178.3200	NA	11.00	9.320090	51.21
DGX	Quest Diagnostics Incorporated	Health Services	134.99	120.4000	12.50	5.91	8.326307	12.12
IQV	IQVIA Holdings, Inc.	Health Services	219.83	165.7500	19.70	4.34	7.478166	32.63
UNH	UnitedHealth Group Incorporated	Health Services	508.12	445.6800	27.20	8.27	6.056554	14.01
LH	Laboratory Corporation of America Holdings	Health Services	218.05	172.0895	8.31	4.18	5.654312	26.71
UHS	Universal Health Services, Inc.	Health Services	131.58	82.5000	11.60	5.12	5.011623	59.49
DVA	DaVita Inc.	Health Services	108.36	65.2800	56.00	2.67	3.869738	65.99
ELV	Elevance Health, Inc.	Health Services	474.30	412.0000	17.30	6.10	3.867553	15.12
CI	The Cigna Group	Health Services	289.61	240.5000	14.60	4.55	3.576440	20.42
HUM	Humana Inc.	Health Services	493.79	423.2900	20.90	6.54	3.421556	16.66
MOH	Molina Healthcare Inc	Health Services	315.73	256.1900	28.40	6.98	2.807577	23.24
CNC	Centene Corporation	Health Services	65.75	61.3400	10.40	3.31	1.834062	7.19