Live Case: S&P500 (2 of 3)

Aug 7, 2023

ISSUE: Understanding the S&P500 as a whole Aug 06, 2023 -=- This chapter is being heavily edited

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

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!!

TODO: Create a new column MarketCapBillions = MarketCap/1000,000,000

ANALYSIS OF S&P500 SECTORS

TODO: Use addmargins() and kableextra etc code to show a table, listing each sector name; no of shares within each sector; Grand Total below showing 502

TODO: Count Shares by Sector, use table(), use addmargins()
Visuaize using Bar Chart (counts), horizontal 19 bars, with display of counts
TODO: Count Shares by Rating
Visualize using Pie Chart – 5 pies split across 500 companies

TODO: Count Shares by Sector*Rating ftable()

The table() function allows us to count how many stocks have each Rating.

use ggpie(), ggbarplot(), table(), addmargins(), ftable()

MARKET CAP

TODO: Work in Billions; 1. Market Cap of all companies by Sector

library(janitor)  # This package helps us auto generate the total at the bottom of a table. 
library(kableExtra)

# Market Cap by Sector
MCap <- sp500 %>%
  group_by(Sector) %>%
  summarise(
    MarketCapCr = sum(na.omit(MarketCap)/10000000))

# Total Market Cap of the entire S&P 500 (in Millions)
SP500MarketCap <- sum(sp500$MarketCap/10000000)

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

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

# Use package janitor to add sums at the bottom of the table
MCapTab <- MCapTab %>%   
  adorn_totals("row")

# Use package knittr to format the appearance of the table
MCapTab <- knitr::kable(MCapTab, "html") %>% kable_styling() 
MCapTab

Sector	MarketCapCr	PercentMarketCap
Technology Services	938967	22.63
Electronic Technology	688189	16.59
Finance	481787	11.61
Health Technology	390467	9.41
Retail Trade	308419	7.43
Consumer Non-Durables	213055	5.14
Producer Manufacturing	140697	3.39
Consumer Services	140352	3.38
Energy Minerals	136410	3.29
Commercial Services	127259	3.07
Consumer Durables	108698	2.62
Utilities	98002	2.36
Health Services	94370	2.27
Process Industries	79145	1.91
Transportation	70013	1.69
Industrial Services	40570	0.98
Communications	39800	0.96
Distribution Services	31000	0.75
Non-Energy Minerals	21747	0.52
Total	4148947	100.00

TODO: Work in Billions; Show “Sum”, “Median”, “Mean” Delete Q1, Q3 all others 2. Summary Statistics of Market Cap (in Cr of USD) by each Sector of S&P500

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

tab <- cbind(Sector = SectorMC$Sector, round(SectorMC[,2:7],2))

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

Sector	Mean	Median	Q1	Q3	Min	max
Commercial Services	9789.15	3110	1630.0	7210.0	799	48500
Communications	13266.67	13800	11950.0	14850.0	10100	15900
Consumer Durables	9058.17	1915	1455.0	4412.5	783	80800
Consumer Non-Durables	6872.74	4370	2000.0	6245.0	719	37000
Consumer Services	4839.72	2150	1500.0	5330.0	781	21200
Distribution Services	3444.44	3250	2180.0	3890.0	1040	7450
Electronic Technology	14044.67	4370	1940.0	7370.0	919	301000
Energy Minerals	8525.62	4825	2527.5	6002.5	1260	42400
Finance	5236.82	2560	1615.0	4870.0	486	76900
Health Services	7864.17	3775	1705.0	7687.5	854	46900
Health Technology	8307.81	3700	1890.0	10700.0	551	44400
Industrial Services	4507.78	3880	3530.0	4770.0	2870	8200
Non-Energy Minerals	3106.71	2970	2270.0	3745.0	667	6080
Process Industries	3297.71	1815	1340.0	3895.0	642	18600
Producer Manufacturing	4538.61	3430	1380.0	5600.0	440	14600
Retail Trade	13409.52	3690	1970.0	9760.0	416	131000
Technology Services	18779.34	3415	1665.0	11100.0	410	243000
Transportation	4667.53	2850	1805.0	5895.0	603	15800
Utilities	3161.35	2390	1785.0	3510.0	860	14200

Top 10 companies having highest Market Cap

Top10 <- sp500 %>% arrange(desc(MarketCap)) %>% head(10)
Top10 <- Top10[,c(1:4, 6,10:13)]

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

Date	Stock	StockName	Sector	MarketCap	ROE	ROA	ROIC	GrossMargin
8/7/2023	AAPL	Apple Inc.	Electronic Technology	3.01e+12	146.00	27.600	57.177930	43.18103
8/7/2023	MSFT	Microsoft Corporation	Technology Services	2.43e+12	38.80	18.600	28.062340	68.92009
8/7/2023	GOOG	Alphabet Inc.	Technology Services	1.62e+12	23.30	16.500	21.184760	55.37940
8/7/2023	GOOGL	Alphabet Inc.	Technology Services	1.62e+12	23.30	16.500	21.184760	55.37940
8/7/2023	AMZN	Amazon.com, Inc.	Retail Trade	1.31e+12	2.98	0.981	1.581103	44.72763
8/7/2023	NVDA	NVIDIA Corporation	Electronic Technology	1.10e+12	18.90	10.700	13.098850	56.31424
8/7/2023	TSLA	Tesla, Inc.	Consumer Durables	8.08e+11	28.00	15.400	25.481890	21.48615
8/7/2023	META	Meta Platforms, Inc.	Technology Services	8.06e+11	17.40	12.000	14.518990	79.42716
8/7/2023	BRK.B	Berkshire Hathaway Inc. New	Finance	7.69e+11	1.43	0.735	1.143973	19.49809
8/7/2023	V	Visa Inc.	Commercial Services	4.85e+11	43.60	18.600	28.104180	76.95643

PRICE RELATIVE TO 52 WEEK LOW

Summary Statistics of Low52WkPerc by Sector

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

tab <- cbind(Sector = SM$Sector, round(SM[,2:7],2))

tab <- tab %>% arrange(Median)

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

Sector	Mean	Median	Q1	Q3	Min	Max
Communications	6.03	5.23	5.02	6.65	4.80	8.07
Utilities	16.13	13.69	6.99	17.31	1.06	58.39
Consumer Non-Durables	19.43	16.04	9.46	30.20	0.35	59.45
Health Services	27.11	18.76	13.33	35.51	8.30	64.45
Health Technology	28.40	22.86	13.80	39.69	0.17	111.43
Retail Trade	26.64	23.90	17.26	34.77	6.53	57.65
Finance	27.26	24.67	17.13	34.60	1.03	98.69
Transportation	36.79	27.79	17.34	53.84	8.28	86.95
Process Industries	28.55	28.07	20.78	39.65	2.62	56.08
Energy Minerals	31.46	29.77	23.82	38.72	10.09	54.33
Industrial Services	41.68	30.48	24.23	68.84	8.78	75.34
Distribution Services	33.82	34.99	28.41	42.30	6.66	55.92
Technology Services	45.16	35.26	21.77	55.13	0.73	255.51
Commercial Services	34.66	35.48	29.81	40.59	9.60	69.34
Consumer Services	50.27	35.97	25.94	58.61	1.91	190.10
Electronic Technology	48.08	38.84	17.85	60.86	1.62	311.70
Consumer Durables	58.17	42.84	18.51	79.84	15.13	150.04
Producer Manufacturing	50.00	43.22	29.05	62.26	5.62	132.17
Non-Energy Minerals	44.31	51.04	35.05	57.78	7.74	65.74

Sector Communications and Utilities are closest to its 52 week low.

Box Plot for Low52WkPerc by Sector TODO: Truncate at 100; Rotate by 90 degrees; Sort Sectors by Median(Low52WkPerc)

library(ggplot2)

ggplot(sp500, aes(Sector, Low52WkPerc)) + geom_boxplot() +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1))

PROFITABILITY BY SECTOR

ROE

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.60  26.40 13.40 43.60    3.50  175.0
2          Communications   8.08   9.05  0.52 16.12   -8.01   23.2
3       Consumer Durables  13.56  17.75  8.11 25.38  -51.40   45.2
4   Consumer Non-Durables 132.99  18.15  7.45 32.12  -10.80 2880.0
5       Consumer Services  25.25  10.90  1.82 38.68 -186.00  360.0
6   Distribution Services  81.18  39.15 32.17 62.62   13.50  295.0
7   Electronic Technology  31.39  18.90  9.63 35.40  -14.20  157.0
8         Energy Minerals  90.99  33.80 26.62 41.25   19.30  954.0
9                 Finance  22.29  10.80  7.64 15.97  -30.00  714.0
10        Health Services  20.26  16.10 11.82 19.65    8.31   60.0
11      Health Technology  23.04  13.30  6.89 24.25  -51.00  253.0
12    Industrial Services  20.71  22.50  9.42 31.10    7.67   36.3
13    Non-Energy Minerals  13.64  13.50  2.69 21.80   -3.83   36.8
14     Process Industries  27.85  18.00 13.85 26.80  -11.70  151.0
15 Producer Manufacturing  23.96  18.00 12.80 29.40  -13.60   95.9
16           Retail Trade  30.05  25.50 15.10 40.00    2.98   66.9
17    Technology Services  53.07  18.85 11.17 31.83  -70.60  844.0
18         Transportation  37.20  34.60 19.90 50.20    4.13  104.0
19              Utilities   7.96   8.70  7.64 10.60  -45.60   44.6

Box Plot for ROE by Sector

library(ggplot2)

ggplot(sp500, aes(Sector, ROE)) + geom_boxplot() +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1))

ROA

Summary Statistics of ROA by each Sector of S&P500

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

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

                   Sector  Mean Median    Q1    Q3    Min   max
1     Commercial Services 10.10   6.60  4.61 18.10   1.74 27.20
2          Communications  2.14   2.93  0.41  4.27  -2.11  5.61
3       Consumer Durables  9.10   9.73  1.54 15.53  -8.81 27.90
4   Consumer Non-Durables  7.37   6.81  3.63 11.07  -5.57 18.90
5       Consumer Services  5.90   2.83  1.06 11.72 -10.80 29.00
6   Distribution Services 10.74   6.49  4.93 17.48   1.08 24.50
7   Electronic Technology  9.52   8.75  5.00 13.18  -6.73 27.60
8         Energy Minerals 15.90  15.80 13.65 18.35   7.39 23.70
9                 Finance  3.21   2.38  1.10  4.08  -2.94 24.70
10        Health Services  5.75   5.52  4.30  6.36   2.96 11.00
11      Health Technology  7.46   7.07  3.71 12.70 -33.10 28.90
12    Industrial Services  6.48   5.47  3.71  8.99   3.30 10.60
13    Non-Energy Minerals  7.01   4.46  1.08 11.72  -2.18 21.20
14     Process Industries  7.24   6.92  5.14  7.69  -4.31 25.40
15 Producer Manufacturing  8.93   8.69  5.15 10.90  -3.10 25.10
16           Retail Trade  9.99   9.01  5.12 13.45   0.98 24.20
17    Technology Services  8.66   8.09  5.54 13.13 -35.40 41.50
18         Transportation  8.91   7.20  3.93 10.90   1.08 26.20
19              Utilities  2.32   2.55  2.00  3.21  -6.25  6.49

Box Plot for ROA by Sector

library(ggplot2)

ggplot(sp500, aes(Sector, ROA)) + geom_boxplot() +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1))

Live Case: S&P500 (2b of 3)

Aug 06, 2023 -=- This chapter is being heavily edited

ISSUE: Analysis of a particular SECTOR We have chosen to deeply analyze the HEALTH TECHNOLOGY Sector

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/7/2… DVA   DaVita I… Healt… Medical…   9.26e 9 102.     65.3    107   60   
 2 8/7/2… MOH   Molina H… Healt… Managed…   1.77e10 303.    256.     374   28.4 
 3 8/7/2… IQV   IQVIA Ho… Healt… Service…   3.95e10 216.    166.     249   19.7 
 4 8/7/2… HUM   Humana I… Healt… Managed…   6.05e10 484.    423.     571   19.5 
 5 8/7/2… ELV   Elevance… Healt… Managed…   1.10e11 467.    412      550   17.3 
 6 8/7/2… CI    The Cign… Healt… Managed…   8.49e10 287.    240.     340   14.9 
 7 8/7/2… DGX   Quest Di… Healt… Service…   1.51e10 135.    120.     158   12.5 
 8 8/7/2… UHS   Universa… Healt… Hospita…   8.54e 9 136.     82.5    159   11.6 
 9 8/7/2… CNC   Centene … Healt… Managed…   3.60e10  66.4    61.3     98.5 10.4 
10 8/7/2… LH    Laborato… Healt… Service…   1.9 e10 214.    172.     226    8.31
# ℹ 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/7/2023 DVA   DaVita Inc.                                60           56.4
 2 8/7/2023 MOH   Molina Healthcare Inc                      28.4         18.2
 3 8/7/2023 IQV   IQVIA Holdings, Inc.                       19.7         30.2
 4 8/7/2023 HUM   Humana Inc.                                19.5         14.4
 5 8/7/2023 ELV   Elevance Health, Inc.                      17.3         13.5
 6 8/7/2023 CI    The Cigna Group                            14.9         19.3
 7 8/7/2023 DGX   Quest Diagnostics Incorporated             12.5         12.0
 8 8/7/2023 UHS   Universal Health Services, Inc.            11.6         64.4
 9 8/7/2023 CNC   Centene Corporation                        10.4          8.3
10 8/7/2023 LH    Laboratory Corporation of America Holdings  8.31        24.4

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  22.4   12.5  11.6  19.7  8.31    60

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 22.42  12.50 11.60 19.70 8.31 60.00
2         ROA  4.50   4.34  4.18  5.12 2.96  5.91
3        ROIC  5.81   5.98  5.14  6.35 4.20  7.37
4 GrossMargin 23.27  25.55 22.96 27.09 7.94 32.83
5   NetMargin  6.15   5.65  5.01  7.48 4.29  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.60  26.40 13.40 43.60    3.50  175.0
2          Communications   8.08   9.05  0.52 16.12   -8.01   23.2
3       Consumer Durables  13.56  17.75  8.11 25.38  -51.40   45.2
4   Consumer Non-Durables 132.99  18.15  7.45 32.12  -10.80 2880.0
5       Consumer Services  25.25  10.90  1.82 38.68 -186.00  360.0
6   Distribution Services  81.18  39.15 32.17 62.62   13.50  295.0
7   Electronic Technology  31.39  18.90  9.63 35.40  -14.20  157.0
8         Energy Minerals  90.99  33.80 26.62 41.25   19.30  954.0
9                 Finance  22.29  10.80  7.64 15.97  -30.00  714.0
10        Health Services  20.26  16.10 11.82 19.65    8.31   60.0
11      Health Technology  23.04  13.30  6.89 24.25  -51.00  253.0
12    Industrial Services  20.71  22.50  9.42 31.10    7.67   36.3
13    Non-Energy Minerals  13.64  13.50  2.69 21.80   -3.83   36.8
14     Process Industries  27.85  18.00 13.85 26.80  -11.70  151.0
15 Producer Manufacturing  23.96  18.00 12.80 29.40  -13.60   95.9
16           Retail Trade  30.05  25.50 15.10 40.00    2.98   66.9
17    Technology Services  53.07  18.85 11.17 31.83  -70.60  844.0
18         Transportation  37.20  34.60 19.90 50.20    4.13  104.0
19              Utilities   7.96   8.70  7.64 10.60  -45.60   44.6

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	3950	43.22
LH	1900	20.79
DGX	1510	16.52
DVA	926	10.13
UHS	854	9.34
Total	9140	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	66.430	61.3400	10.40	3.31	8.30
DGX	Quest Diagnostics Incorporated	Health Services	134.830	120.4000	12.50	5.91	11.99
UNH	UnitedHealth Group Incorporated	Health Services	503.250	445.6800	NA	8.27	12.92
ELV	Elevance Health, Inc.	Health Services	467.445	412.0000	17.30	6.10	13.46
HUM	Humana Inc.	Health Services	484.165	423.2900	19.50	6.16	14.38
MOH	Molina Healthcare Inc	Health Services	302.885	256.1900	28.40	6.98	18.23
CI	The Cigna Group	Health Services	286.890	240.5000	14.90	4.65	19.29
LH	Laboratory Corporation of America Holdings	Health Services	214.060	172.0895	8.31	4.18	24.39
IQV	IQVIA Holdings, Inc.	Health Services	215.880	165.7500	19.70	4.34	30.24
HCA	HCA Healthcare, Inc.	Health Services	269.820	178.3200	NA	11.00	51.31
DVA	DaVita Inc.	Health Services	102.100	65.2800	60.00	2.96	56.40
UHS	Universal Health Services, Inc.	Health Services	135.670	82.5000	11.60	5.12	64.45

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	102.100	65.2800	60.00	2.96	56.40
MOH	Molina Healthcare Inc	Health Services	302.885	256.1900	28.40	6.98	18.23
IQV	IQVIA Holdings, Inc.	Health Services	215.880	165.7500	19.70	4.34	30.24
HUM	Humana Inc.	Health Services	484.165	423.2900	19.50	6.16	14.38
ELV	Elevance Health, Inc.	Health Services	467.445	412.0000	17.30	6.10	13.46
CI	The Cigna Group	Health Services	286.890	240.5000	14.90	4.65	19.29
DGX	Quest Diagnostics Incorporated	Health Services	134.830	120.4000	12.50	5.91	11.99
UHS	Universal Health Services, Inc.	Health Services	135.670	82.5000	11.60	5.12	64.45
CNC	Centene Corporation	Health Services	66.430	61.3400	10.40	3.31	8.30
LH	Laboratory Corporation of America Holdings	Health Services	214.060	172.0895	8.31	4.18	24.39
HCA	HCA Healthcare, Inc.	Health Services	269.820	178.3200	NA	11.00	51.31
UNH	UnitedHealth Group Incorporated	Health Services	503.250	445.6800	NA	8.27	12.92

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.820	178.3200	NA	11.00	51.31
UNH	UnitedHealth Group Incorporated	Health Services	503.250	445.6800	NA	8.27	12.92
MOH	Molina Healthcare Inc	Health Services	302.885	256.1900	28.40	6.98	18.23
HUM	Humana Inc.	Health Services	484.165	423.2900	19.50	6.16	14.38
ELV	Elevance Health, Inc.	Health Services	467.445	412.0000	17.30	6.10	13.46
DGX	Quest Diagnostics Incorporated	Health Services	134.830	120.4000	12.50	5.91	11.99
UHS	Universal Health Services, Inc.	Health Services	135.670	82.5000	11.60	5.12	64.45
CI	The Cigna Group	Health Services	286.890	240.5000	14.90	4.65	19.29
IQV	IQVIA Holdings, Inc.	Health Services	215.880	165.7500	19.70	4.34	30.24
LH	Laboratory Corporation of America Holdings	Health Services	214.060	172.0895	8.31	4.18	24.39
CNC	Centene Corporation	Health Services	66.430	61.3400	10.40	3.31	8.30
DVA	DaVita Inc.	Health Services	102.100	65.2800	60.00	2.96	56.40

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.820	178.3200	NA	11.00	9.320090	51.31
DGX	Quest Diagnostics Incorporated	Health Services	134.830	120.4000	12.50	5.91	8.326307	11.99
IQV	IQVIA Holdings, Inc.	Health Services	215.880	165.7500	19.70	4.34	7.478166	30.24
UNH	UnitedHealth Group Incorporated	Health Services	503.250	445.6800	NA	8.27	6.056554	12.92
LH	Laboratory Corporation of America Holdings	Health Services	214.060	172.0895	8.31	4.18	5.654312	24.39
UHS	Universal Health Services, Inc.	Health Services	135.670	82.5000	11.60	5.12	5.011623	64.45
DVA	DaVita Inc.	Health Services	102.100	65.2800	60.00	2.96	4.289510	56.40
ELV	Elevance Health, Inc.	Health Services	467.445	412.0000	17.30	6.10	3.867553	13.46
CI	The Cigna Group	Health Services	286.890	240.5000	14.90	4.65	3.693735	19.29
HUM	Humana Inc.	Health Services	484.165	423.2900	19.50	6.16	3.256937	14.38
MOH	Molina Healthcare Inc	Health Services	302.885	256.1900	28.40	6.98	2.807577	18.23
CNC	Centene Corporation	Health Services	66.430	61.3400	10.40	3.31	1.834062	8.30