📊 Report Objective: This comprehensive analysis evaluates S&P 500 stocks from 2020-2024 to identify “Compounders” - exceptional companies demonstrating consistent earnings growth and stable return on equity over time.

1 🎯 Executive Dashboard

# Load analytical toolkit
suppressPackageStartupMessages({
  library(tidyverse)
  library(plotly)
  library(DT)
  library(scales)
  library(knitr)
  library(kableExtra)
})

# Import dataset
stock_data <- read.csv("sp500_sampled_data_2020_2024.txt", stringsAsFactors = FALSE)
<div class="metric-label">Total Companies</div>
<div class="metric-value">20</div>
<div class="metric-label">Growing Stocks</div>
<div class="metric-value">19</div>
<div class="metric-label">Average ROE</div>
<div class="metric-value">26.3%</div>
<div class="metric-label">Analysis Period</div>
<div class="metric-value">2020-2024</div>

2 📐 Data Engineering Pipeline

2.1 1.1 Raw Data Structure

Sample: Raw Dataset (Wide Format)
Symbol Company Sector P/E ROE 2020 ROE 2024 EPS 2020 EPS 2024
AAPL Apple Inc.  Technology 28.5 0.73 1.45 3.20 6.5
MSFT Microsoft Corp.  Technology 35.2 0.35 0.44 5.10 11.1
AMZN Amazon.com Inc.  Consumer Disc. 75.0 0.20 0.15 1.15 2.8
GOOGL Alphabet Inc.  Technology 24.5 0.18 0.27 2.80 6.1
TSLA Tesla Inc.  Consumer Disc. 68.5 0.05 0.21 0.25 3.1
JPM JPMorgan Chase Financials 11.5 0.12 0.17 8.50 14.5
JNJ Johnson & Johnson Health Care 15.8 0.22 0.26 6.50 8.5
PG Procter & Gamble Consumer Staples 24.5 0.28 0.32 4.80 6.4

⚠️ Challenge: Data is stored in wide format with years as separate columns (ROE_2020, ROE_2021, etc.). This structure prevents effective time-series visualization and trend analysis.

2.2 1.2 Transformation Strategy: Pivot Operation

🔄 TASK 1: DATA RESHAPING

Objective: Transform wide-format data into long-format (tidy data) to enable temporal analysis and visualization.

Method: tidyr::pivot_longer()
Benefit: Enables ggplot2 to plot ROE/EPS trends across multiple years efficiently.

# ============================================================
# TRANSFORMATION 1: ROE Time Series
# ============================================================
roe_timeseries <- stock_data %>%
  pivot_longer(
    cols = starts_with("ROE_"),           # Select ROE columns
    names_to = "Year",                     # Create Year column
    values_to = "ROE",                     # Create ROE value column
    names_prefix = "ROE_"                  # Remove "ROE_" prefix
  ) %>%
  mutate(Year = as.integer(Year))          # Convert Year to numeric

# ============================================================
# TRANSFORMATION 2: EPS Time Series
# ============================================================
eps_timeseries <- stock_data %>%
  pivot_longer(
    cols = starts_with("EPS_"),
    names_to = "Year",
    values_to = "EPS",
    names_prefix = "EPS_"
  ) %>%
  mutate(Year = as.integer(Year))

# ============================================================
# MERGE: Complete Financial Time Series
# ============================================================
financial_timeseries <- roe_timeseries %>%
  inner_join(
    eps_timeseries %>% select(Symbol, Year, EPS),
    by = c("Symbol", "Year")
  )

2.2.1 Transformation Results

✅ Transformed Dataset (Long Format) - Ready for Analysis
Symbol Company Sector Year ROE EPS
AAPL Apple Inc.  Technology 2020 0.73 3.20
AAPL Apple Inc.  Technology 2021 0.85 4.50
AAPL Apple Inc.  Technology 2022 1.20 5.60
AAPL Apple Inc.  Technology 2023 1.35 6.10
AAPL Apple Inc.  Technology 2024 1.45 6.50
GOOGL Alphabet Inc.  Technology 2020 0.18 2.80
GOOGL Alphabet Inc.  Technology 2021 0.25 4.10
GOOGL Alphabet Inc.  Technology 2022 0.22 4.50
GOOGL Alphabet Inc.  Technology 2023 0.24 5.20
GOOGL Alphabet Inc.  Technology 2024 0.27 6.10
MSFT Microsoft Corp.  Technology 2020 0.35 5.10
MSFT Microsoft Corp.  Technology 2021 0.38 6.20
MSFT Microsoft Corp.  Technology 2022 0.40 8.05
MSFT Microsoft Corp.  Technology 2023 0.42 9.60
MSFT Microsoft Corp.  Technology 2024 0.44 11.10

✅ Success: Data successfully transformed from 14 columns to a tidy format with 100 observations across 5 years.

3 💡 Financial Intelligence Layer

3.1 2.1 Growth Classification Algorithm

📈 TASK 2: GROWTH STATUS CLASSIFICATION

Business Rule:
IF EPS_2024 > EPS_2020 → “Growing”
ELSE → “Declining”

Implementation: dplyr::mutate() + ifelse()

# ============================================================
# FINANCIAL METRICS ENGINE
# ============================================================
stock_metrics <- stock_data %>%
  mutate(
    # ========== CORE CLASSIFICATION (TASK 2) ==========
    Growth_Status = ifelse(
      EPS_2024 > EPS_2020, 
      "Growing", 
      "Declining"
    ),
    
    # ========== ADVANCED ANALYTICS ==========
    
    # 5-Year EPS CAGR
    EPS_Growth_Rate = ((EPS_2024 - EPS_2020) / abs(EPS_2020)) * 100,
    
    # Average ROE (5-year mean)
    ROE_Mean = rowMeans(select(., starts_with("ROE_")), na.rm = TRUE),
    
    # ROE Volatility (standard deviation)
    ROE_Volatility = NA_real_,
    
    # EPS Trend Direction
    EPS_Trend = case_when(
      EPS_2024 > EPS_2023 & EPS_2023 > EPS_2022 ~ "Accelerating",
      EPS_2024 > EPS_2020 ~ "Growing",
      TRUE ~ "Declining"
    )
  )

# Calculate ROE volatility (standard deviation across years)
for(i in 1:nrow(stock_metrics)) {
  roe_vector <- as.numeric(stock_metrics[i, c("ROE_2020", "ROE_2021", "ROE_2022", "ROE_2023", "ROE_2024")])
  stock_metrics$ROE_Volatility[i] <- sd(roe_vector, na.rm = TRUE)
}

# ============================================================
# COMPOUNDER QUALITY SCORE
# ============================================================
stock_metrics <- stock_metrics %>%
  mutate(
    # Normalize scores (0-1 scale)
    ROE_Score = scales::rescale(ROE_Mean, to = c(0, 1)),
    Stability_Score = scales::rescale(-ROE_Volatility, to = c(0, 1)),  # Invert: lower volatility = better
    Growth_Score = scales::rescale(
      ifelse(is.infinite(EPS_Growth_Rate), NA, EPS_Growth_Rate), 
      to = c(0, 1)
    ),
    
    # Weighted composite score
    Quality_Score = (ROE_Score * 0.35) + (Stability_Score * 0.35) + (Growth_Score * 0.30)
  ) %>%
  mutate(
    # Quality Rating
    Quality_Rating = case_when(
      Quality_Score >= 0.75 ~ "⭐⭐⭐ Exceptional",
      Quality_Score >= 0.60 ~ "⭐⭐ Strong",
      Quality_Score >= 0.40 ~ "⭐ Average",
      TRUE ~ "Below Average"
    )
  )

3.2 2.2 Classification Results

📊 Growth Classification Summary
Growth_Status Companies Avg ROE Avg Growth Avg P/E Quality Score Percentage
Declining 1 11.6% -111.1% 85.0 0.30 5%
Growing 19 27.1% 279% 31.4 0.49 95%

💼 Investment Insight: 19 companies demonstrate positive earnings trajectory, representing 95% of the portfolio.

4 📊 Visual Analytics Suite

📈 TASK 3: INTERACTIVE DASHBOARD COMPONENTS

Requirements:
✅ Individual company ROE trend visualization
✅ Market-wide P/E vs Growth scatter analysis
✅ Interactive filtering and exploration capabilities

4.1 3.1 Company Deep-Dive: Apple Inc. (AAPL)

4.1.1 Performance Metrics Dashboard

<div style="font-size: 12px; opacity: 0.9;">P/E RATIO</div>
<div style="font-size: 28px; font-weight: bold; margin: 10px 0;">28.5</div>
<div style="font-size: 12px; opacity: 0.9;">AVG ROE (5Y)</div>
<div style="font-size: 28px; font-weight: bold; margin: 10px 0;">111.6%</div>
<div style="font-size: 12px; opacity: 0.9;">EPS GROWTH</div>
<div style="font-size: 28px; font-weight: bold; margin: 10px 0;">+103.1%</div>
<div style="font-size: 12px; opacity: 0.9;">STATUS</div>
<div style="font-size: 24px; font-weight: bold; margin: 10px 0;">Growing</div>

4.1.2 📈 Return on Equity (ROE) - 5 Year Evolution

📊 Trend Analysis: ROE increased from 73% (2020) to 145% (2024), demonstrating volatile capital efficiency with volatility of 0.313.

4.1.3 💰 Earnings Per Share (EPS) - Growth Trajectory

💎 Earnings Insight: EPS expanded from $3.2 to $6.5, representing a 103.1% cumulative growth rate over the 5-year period.

4.2 3.2 Portfolio-Wide Valuation Analysis

4.2.1 💎 P/E Ratio vs EPS Growth - Value Discovery Matrix

🎯 Strategic Insight: Companies in the lower-right quadrant (high growth, low P/E) represent potential value opportunities. Look for large bubbles (high ROE) with green coloring (high quality score) for optimal investment candidates.

4.2.2 🎪 ROE Consistency Analysis - Quality Assessment

✨ Quality Indicators: Companies in the upper-right quadrant (high ROE, low volatility) represent consistent performers - true “quality compounders” with predictable returns and stable business models.

4.3 3.3 Interactive Stock Screener

5 📈 Sector Intelligence

5.1 Sector Performance Comparison

5.2 Detailed Sector Metrics

📊 Comprehensive Sector Analysis
Sector # Cos Avg ROE (%) Avg Growth (%) Avg P/E Growing (%) Quality
Technology 6 43.3 203.1 47.0 83.3 0.47
Consumer Staples 2 35.0 44.4 23.0 100.0 0.51
Consumer Disc. 3 27.5 444.2 55.3 100.0 0.53
Health Care 2 22.5 22.5 14.2 100.0 0.45
Communication 3 18.7 277.7 32.0 100.0 0.51
Energy 1 13.2 1460.0 10.2 100.0 0.62
Financials 2 12.6 63.9 11.2 100.0 0.47
Industrials 1 -26.0 83.3 NaN 100.0 0.18

6 🏆 Elite Performers

6.1 Top 5 by Quality Score

🌟 Top 5 Highest Quality Compounders
Symbol Company Sector P/E Avg ROE Growth Score Rating
XOM Exxon Mobil Energy 10.2 13.2% 1460% 0.623 ⭐⭐ Strong
TSLA Tesla Inc.  Consumer Disc. 68.5 17.2% 1140% 0.620 ⭐⭐ Strong
NVDA NVIDIA Corp.  Technology 95.0 45% 962.5% 0.564 ⭐ Average
HD Home Depot Consumer Disc. 22.5 50.4% 49% 0.543 ⭐ Average
MSFT Microsoft Corp.  Technology 35.2 39.8% 117.6% 0.531 ⭐ Average

7 📚 Technical Documentation

7.1 Analytical Methodology

7.1.1 Phase 1: Data Transformation

  • Technique: tidyr::pivot_longer()
  • Input: Wide-format panel data (years as columns)
  • Output: Long-format time series (years as rows)
  • Purpose: Enable temporal visualization and trend analysis

7.1.2 Phase 2: Classification Logic

  • Technique: dplyr::mutate() + conditional logic
  • Rule: Binary classification based on EPS trajectory
  • Formula: Growth_Status = ifelse(EPS_2024 > EPS_2020, "Growing", "Declining")

7.1.3 Phase 3: Quality Scoring

  • Components:
    1. ROE Score (35% weight): Normalized average ROE
    2. Stability Score (35% weight): Inverse of ROE volatility
    3. Growth Score (30% weight): Normalized EPS growth rate
  • Aggregation: Weighted average → Quality Score ∈ [0, 1]

7.1.4 Visualization Framework

  • Library: plotly (interactive HTML widgets)
  • Features: Hover tooltips, zoom, pan, responsive design
  • Accessibility: Mobile-optimized, colorblind-friendly palettes

7.2 Key Findings Summary

📊 Analysis Summary

✅ Dataset: 20 S&P 500 companies
✅ Growing Stocks: 19 (95%)
✅ Average ROE: 26.3%
✅ Average Growth: 259.5%
✅ Leading Sector: Technology
✅ Top Compounder: Exxon Mobil

7.3 Investment Framework

🎯 Selection Criteria for Quality Compounders:

1️⃣ High ROE: > 15% average over 5 years
2️⃣ Stable Performance: ROE volatility < 0.10
3️⃣ Growing Earnings: Positive EPS trajectory
4️⃣ Reasonable Valuation: P/E ratio aligned with growth rate
5️⃣ Sector Leadership: Competitive positioning in industry

7.4 Disclaimer

⚠️ Important Notice: This analysis is for educational and informational purposes only. It does not constitute investment advice, financial guidance, or a recommendation to buy or sell securities. Past performance does not guarantee future results. All investing involves risk, including the possible loss of principal. Consult with a qualified financial advisor before making investment decisions.

📈 Analysis Complete

Generated: December 23, 2025 at 10:04 PM
Framework: R 4.x | Tidyverse Ecosystem
Report Type: Interactive HTML Dashboard