1. Data Loading & Preparation

# Load required libraries
library(tidyverse)

## Warning: package 'tidyverse' was built under R version 4.5.2

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.1     ✔ stringr   1.5.2
## ✔ ggplot2   4.0.0     ✔ tibble    3.3.0
## ✔ lubridate 1.9.4     ✔ tidyr     1.3.1
## ✔ purrr     1.1.0     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(plotly)

## 
## Attaching package: 'plotly'
## 
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## 
## The following object is masked from 'package:stats':
## 
##     filter
## 
## The following object is masked from 'package:graphics':
## 
##     layout

library(DT)
library(knitr)

# Read the data
stock_data <- read.csv("C:/Users/dell/Downloads/sp500_sampled data_2020_2024.txt", 
                       header = TRUE,
                       stringsAsFactors = FALSE)

# Display first few rows
kable(head(stock_data, 5), caption = "Sample of S&P 500 Stock Data")

Sample of S&P 500 Stock Data

Symbol	Company	Sector	PE_Current	ROE_2020	ROE_2021	ROE_2022	ROE_2023	ROE_2024	EPS_2020	EPS_2021	EPS_2022	EPS_2023	EPS_2024
AAPL	Apple Inc.	Technology	28.5	0.73	0.85	1.20	1.35	1.45	3.20	4.5	5.60	6.1	6.5
MSFT	Microsoft Corp.	Technology	35.2	0.35	0.38	0.40	0.42	0.44	5.10	6.2	8.05	9.6	11.1
AMZN	Amazon.com Inc.	Consumer Disc.	75.0	0.20	0.22	0.05	0.12	0.15	1.15	2.1	0.50	1.5	2.8
GOOGL	Alphabet Inc.	Technology	24.5	0.18	0.25	0.22	0.24	0.27	2.80	4.1	4.50	5.2	6.1
TSLA	Tesla Inc.	Consumer Disc.	68.5	0.05	0.15	0.25	0.20	0.21	0.25	0.8	1.90	2.4	3.1

---

2. Task 1: Data Wrangling (Pivot Wide to Long Format)

# Pivot ROE columns from wide to long format
roe_long <- stock_data %>%
  pivot_longer(
    cols = starts_with("ROE_"),
    names_to = "Year",
    values_to = "ROE",
    names_prefix = "ROE_"
  ) %>%
  mutate(Year = as.numeric(Year))

# Pivot EPS data
eps_long <- stock_data %>%
  pivot_longer(
    cols = starts_with("EPS_"),
    names_to = "Year",
    values_to = "EPS",
    names_prefix = "EPS_"
  ) %>%
  mutate(Year = as.numeric(Year))

# Display sample of pivoted data
kable(head(roe_long, 10), caption = "ROE Data in Long Format (First 10 Rows)")

ROE Data in Long Format (First 10 Rows)

Symbol	Company	Sector	PE_Current	EPS_2020	EPS_2021	EPS_2022	EPS_2023	EPS_2024	Year	ROE
AAPL	Apple Inc.	Technology	28.5	3.2	4.5	5.60	6.1	6.5	2020	0.73
AAPL	Apple Inc.	Technology	28.5	3.2	4.5	5.60	6.1	6.5	2021	0.85
AAPL	Apple Inc.	Technology	28.5	3.2	4.5	5.60	6.1	6.5	2022	1.20
AAPL	Apple Inc.	Technology	28.5	3.2	4.5	5.60	6.1	6.5	2023	1.35
AAPL	Apple Inc.	Technology	28.5	3.2	4.5	5.60	6.1	6.5	2024	1.45
MSFT	Microsoft Corp.	Technology	35.2	5.1	6.2	8.05	9.6	11.1	2020	0.35
MSFT	Microsoft Corp.	Technology	35.2	5.1	6.2	8.05	9.6	11.1	2021	0.38
MSFT	Microsoft Corp.	Technology	35.2	5.1	6.2	8.05	9.6	11.1	2022	0.40
MSFT	Microsoft Corp.	Technology	35.2	5.1	6.2	8.05	9.6	11.1	2023	0.42
MSFT	Microsoft Corp.	Technology	35.2	5.1	6.2	8.05	9.6	11.1	2024	0.44

---

3. Task 2: Financial Logic Calculations

# Calculate Growth Status and Financial Metrics
stock_summary <- stock_data %>%
  mutate(
    Growth_Status = ifelse(EPS_2024 > EPS_2020, "Growing", "Declining"),
    EPS_Growth_5Y = ((EPS_2024 - EPS_2020) / abs(EPS_2020)) * 100,
    EPS_Growth_5Y = ifelse(is.infinite(EPS_Growth_5Y), NA, EPS_Growth_5Y),
    ROE_Avg = (ROE_2020 + ROE_2021 + ROE_2022 + ROE_2023 + ROE_2024) / 5,
    ROE_StdDev = apply(select(., starts_with("ROE_")), 1, sd, na.rm = TRUE),
    Compounder_Score = case_when(
      Growth_Status == "Growing" & ROE_StdDev < 0.15 & ROE_Avg > 0.20 ~ "Strong Compounder",
      Growth_Status == "Growing" & ROE_Avg > 0.15 ~ "Moderate Compounder",
      TRUE ~ "Not a Compounder"
    )
  )


# Display calculated metrics
stock_summary %>%
  select(Symbol, Company, Growth_Status, EPS_Growth_5Y, ROE_Avg, ROE_StdDev, Compounder_Score) %>%
  mutate(
    EPS_Growth_5Y = round(EPS_Growth_5Y, 1),
    ROE_Avg = round(ROE_Avg * 100, 1),
    ROE_StdDev = round(ROE_StdDev, 3)
  ) %>%
  kable(caption = "Financial Metrics Summary")

Financial Metrics Summary

Symbol	Company	Growth_Status	EPS_Growth_5Y	ROE_Avg	ROE_StdDev	Compounder_Score
AAPL	Apple Inc.	Growing	103.1	111.6	0.313	Moderate Compounder
MSFT	Microsoft Corp.	Growing	117.6	39.8	0.035	Strong Compounder
AMZN	Amazon.com Inc.	Growing	143.5	14.8	0.068	Not a Compounder
GOOGL	Alphabet Inc.	Growing	117.9	23.2	0.034	Strong Compounder
TSLA	Tesla Inc.	Growing	1140.0	17.2	0.077	Moderate Compounder
JPM	JPMorgan Chase	Growing	70.6	14.8	0.019	Not a Compounder
JNJ	Johnson & Johnson	Growing	30.8	24.0	0.016	Strong Compounder
PG	Procter & Gamble	Growing	33.3	30.0	0.016	Strong Compounder
XOM	Exxon Mobil	Growing	1460.0	13.2	0.119	Not a Compounder
NVDA	NVIDIA Corp.	Growing	962.5	45.0	0.158	Moderate Compounder
PFE	Pfizer Inc.	Growing	14.3	21.0	0.083	Strong Compounder
KO	Coca-Cola Co.	Growing	55.6	40.0	0.038	Strong Compounder
HD	Home Depot	Growing	49.0	50.4	0.036	Strong Compounder
BAC	Bank of America	Growing	57.1	10.4	0.015	Not a Compounder
VZ	Verizon Comm.	Growing	19.5	25.6	0.015	Strong Compounder
CSCO	Cisco Systems	Growing	28.6	28.8	0.008	Strong Compounder
INTC	Intel Corp.	Declining	-111.1	11.6	0.131	Not a Compounder
DIS	Walt Disney	Growing	662.5	5.0	0.054	Not a Compounder
NFLX	Netflix Inc.	Growing	151.0	25.6	0.025	Strong Compounder
BA	Boeing Co.	Growing	83.3	-26.0	0.192	Not a Compounder

---

4. Task 3: Visualization & Analysis

4.1 Individual Company Analysis: Apple Inc. (AAPL)

# ROE Trend for Apple
aapl_roe <- roe_long %>% filter(Symbol == "AAPL")


p1 <- ggplot(aapl_roe, aes(x = Year, y = ROE)) +
  geom_line(color = "#1f77b4", linewidth = 1.5) +
  geom_point(color = "#1f77b4", size = 4) +
  geom_hline(yintercept = 0, linetype = "dashed", color = "red", alpha = 0.5) +
  scale_y_continuous(labels = scales::percent) +
  labs(
    title = "Apple Inc. (AAPL): 5-Year ROE Trend",
    x = "Year",
    y = "Return on Equity (ROE)",
    caption = "Higher and stable ROE indicates efficient capital use"
  ) +
  theme_minimal() +
  theme(plot.title = element_text(face = "bold", size = 14))




ggplotly(p1)

# EPS Trend for Apple
aapl_eps <- eps_long %>% filter(Symbol == "AAPL")

p2 <- ggplot(aapl_eps, aes(x = Year, y = EPS)) +
  geom_line(color = "#2ca02c", linewidth = 1.5) +
  geom_point(color = "#2ca02c", size = 4) +
  geom_hline(yintercept = 0, linetype = "dashed", color = "red", alpha = 0.5) +
  labs(
    title = "Apple Inc. (AAPL): 5-Year EPS Trend",
    x = "Year",
    y = "Earnings Per Share (EPS)",
    caption = "Growing EPS indicates increasing profitability"
  ) +
  theme_minimal() +
  theme(plot.title = element_text(face = "bold", size = 14))

ggplotly(p2)

4.2 Market Overview: P/E Ratio vs. EPS Growth

# P/E vs EPS Growth Scatter Plot
filtered_data <- stock_summary %>%
  filter(!is.na(PE_Current), !is.na(EPS_Growth_5Y))

p3 <- ggplot(filtered_data, aes(x = PE_Current, y = EPS_Growth_5Y, 
                                color = Growth_Status,
                                text = paste0(
                                  "Company: ", Company, "\n",
                                  "Sector: ", Sector, "\n",
                                  "P/E: ", round(PE_Current, 1), "\n",
                                  "EPS Growth: ", round(EPS_Growth_5Y, 1), "%"
                                ))) +
  geom_point(size = 5, alpha = 0.7) +
  geom_hline(yintercept = 0, linetype = "dashed", alpha = 0.5) +
  geom_vline(xintercept = 20, linetype = "dashed", alpha = 0.5, color = "gray") +
  scale_color_manual(values = c("Growing" = "#2ca02c", "Declining" = "#d62728")) +
  labs(
    title = "Value Screener: P/E Ratio vs. 5-Year EPS Growth",
    subtitle = "Ideal stocks: Low P/E + High EPS Growth (Top-Left Quadrant)",
    x = "Current P/E Ratio (Lower is Better)",
    y = "5-Year EPS Growth (%)",
    color = "Growth Status"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    legend.position = "bottom"
  )

ggplotly(p3, tooltip = "text")

Investment Insight: Look for companies in the top-left quadrant - these represent potential value plays with low valuations (P/E) and high growth.

---

5. Compounder Ranking Table

# Create ranking table
compounder_ranking <- stock_summary %>%
  filter(Growth_Status == "Growing") %>%
  select(Symbol, Company, Sector, PE_Current, EPS_Growth_5Y, 
         ROE_Avg, ROE_StdDev, Compounder_Score) %>%
  arrange(desc(EPS_Growth_5Y)) %>%
  mutate(
    PE_Current = round(PE_Current, 1),
    EPS_Growth_5Y = round(EPS_Growth_5Y, 1),
    ROE_Avg = round(ROE_Avg * 100, 1),
    ROE_StdDev = round(ROE_StdDev, 3)
  ) %>%
  rename(
    `P/E` = PE_Current,
    `EPS Growth (%)` = EPS_Growth_5Y,
    `Avg ROE (%)` = ROE_Avg,
    `ROE Stability` = ROE_StdDev,
    `Rating` = Compounder_Score
  )

datatable(compounder_ranking, 
          caption = "Top Compounders: Growing Earnings + Stable ROE",
          options = list(pageLength = 15, scrollX = TRUE),
          filter = 'top')

---

6. Sector Analysis

# Average metrics by sector
sector_summary <- stock_summary %>%
  group_by(Sector) %>%
  summarise(
    Companies = n(),
    Avg_PE = mean(PE_Current, na.rm = TRUE),
    Avg_EPS_Growth = mean(EPS_Growth_5Y, na.rm = TRUE),
    Avg_ROE = mean(ROE_Avg, na.rm = TRUE),
    Strong_Compounders = sum(Compounder_Score == "Strong Compounder", na.rm = TRUE)
  ) %>%
  arrange(desc(Avg_EPS_Growth)) %>%
  mutate(
    Avg_PE = round(Avg_PE, 1),
    Avg_EPS_Growth = round(Avg_EPS_Growth, 1),
    Avg_ROE = round(Avg_ROE * 100, 1)
  )

kable(sector_summary, caption = "Performance Metrics by Sector")

Performance Metrics by Sector

Sector	Companies	Avg_PE	Avg_EPS_Growth	Avg_ROE	Strong_Compounders
Energy	1	10.2	1460.0	13.2	0
Consumer Disc.	3	55.3	444.2	27.5	1
Communication	3	32.0	277.7	18.7	2
Technology	6	47.0	203.1	43.3	3
Industrials	1	NaN	83.3	-26.0	0
Financials	2	11.2	63.9	12.6	0
Consumer Staples	2	23.0	44.4	35.0	2
Health Care	2	14.2	22.5	22.5	2

# Sector performance visualization
p4 <- ggplot(sector_summary, aes(x = reorder(Sector, Avg_EPS_Growth), y = Avg_EPS_Growth, fill = Sector)) +
  geom_col(alpha = 0.8) +
  coord_flip() +
  labs(
    title = "Average 5-Year EPS Growth by Sector",
    x = "Sector",
    y = "Average EPS Growth (%)"
  ) +
  theme_minimal() +
  theme(legend.position = "none",
        plot.title = element_text(face = "bold", size = 14))

ggplotly(p4)

---

7. Multi-Company Comparison

# Compare top 5 growers
top_5_companies <- stock_summary %>%
  filter(Growth_Status == "Growing") %>%
  arrange(desc(EPS_Growth_5Y)) %>%
  head(5) %>%
  pull(Symbol)

# ROE comparison
roe_comparison <- roe_long %>%
  filter(Symbol %in% top_5_companies)

p5 <- ggplot(roe_comparison, aes(x = Year, y = ROE, color = Symbol, group = Symbol)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  scale_y_continuous(labels = scales::percent) +
  labs(
    title = "ROE Comparison: Top 5 Growing Companies",
    x = "Year",
    y = "Return on Equity (ROE)"
  ) +
  theme_minimal() +
  theme(plot.title = element_text(face = "bold", size = 14),
        legend.position = "bottom")

ggplotly(p5)

# EPS comparison
eps_comparison <- eps_long %>%
  filter(Symbol %in% top_5_companies)



p6 <- ggplot(eps_comparison, aes(x = Year, y = EPS, color = Symbol, group = Symbol)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  labs(
    title = "EPS Comparison: Top 5 Growing Companies",
    x = "Year",
    y = "Earnings Per Share (EPS)"
  ) +
  theme_minimal() +
  theme(plot.title = element_text(face = "bold", size = 14),
        legend.position = "bottom")

ggplotly(p6)

---

8. Key Findings

# Calculate key statistics
total_companies <- nrow(stock_summary)
growing_companies <- sum(stock_summary$Growth_Status == "Growing")
strong_compounders <- sum(stock_summary$Compounder_Score == "Strong Compounder", na.rm = TRUE)
avg_growth <- mean(stock_summary$EPS_Growth_5Y[stock_summary$Growth_Status == "Growing"], na.rm = TRUE)

Summary Statistics:

• Total Companies Analyzed: 20
• Companies with Growing EPS: 19 (95%)
• Strong Compounders: 10 companies
• Average EPS Growth (Growing Companies): 279%

Investment Recommendations:

1. Strong Compounders show consistent ROE above 20% with low volatility
2. Companies in the top-left quadrant of the P/E vs Growth chart offer potential value
3. Technology sector shows the highest average EPS growth
4. Monitor companies with declining ROE trends despite positive EPS growth

---

Conclusion

This analysis demonstrates the power of combining fundamental metrics (ROE, EPS) with valuation ratios (P/E) to identify quality growth stocks. The “Compounder” framework helps investors focus on companies with sustainable competitive advantages.

Disclaimer: This analysis is for educational purposes only and should not be considered investment advice.

---

Generated on 2025-12-09 | Data Source: S&P 500 Historical Data (2020-2024)