Extracting Apple’s Financial Data from S&P Capital IQ

Load Libraries and Excel Data

# Load Required Libraries
library(dplyr)
library(ggplot2)
library(tidyr)
library(readxl)

# Load Excel Data
Apple_Inc <- read_excel("~/Downloads/Apple Inc NasdaqGS AAPL Financials (1).xls")

Extract Profitability Data

#Extract Total Revenue, Gross Profit and EBITDA
total_revenue <- as.numeric(Apple_Inc [11, 2:5])
gross_profit <- as.numeric(Apple_Inc [14, 2:5])
EBITDA <- as.numeric(Apple_Inc [17, 2:5])

#Create a data frame for the selected metrics
financial_summary <- data.frame(
  Metric = c("Total Revenue", "Gross Profit", "EBITDA"),
  'TTM Q3/20' = c(total_revenue[[1]], gross_profit[[1]], EBITDA[[1]]),
  'TTM Q3/21' = c(total_revenue[[2]], gross_profit[[2]], EBITDA[[2]]),
  'TTM Q3/22' = c(total_revenue[[3]], gross_profit[[3]], EBITDA[[3]]),
  'TTM Q3/23' = c(total_revenue[[4]], gross_profit[[4]], EBITDA[[4]])
)

Preview of data

head(Apple_Inc)

## # A tibble: 6 × 9
##   Apple Inc. (NasdaqGS:AAPL) >…¹ ...2  ...3  ...4  ...5  ...6  ...7  ...8  ...9 
##   <chr>                          <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA>                           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 2 In Millions of the trading cu… Curr… Trad…       Conv… Toda… <NA>  <NA>  <NA> 
## 3 <NA>                           Orde… Late…       Unit… S&P … <NA>  <NA>  <NA> 
## 4 <NA>                           Deci… Capi…       Dilu… Basic <NA>  <NA>  <NA> 
## 5 <NA>                           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## 6 <NA>                           <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
## # ℹ abbreviated name: ¹​`Apple Inc. (NasdaqGS:AAPL) > Financials > Key Stats`

Print Financial Summary

# Print the financial summary to review the data
print(financial_summary)

##          Metric TTM.Q3.20 TTM.Q3.21 TTM.Q3.22 TTM.Q3.23
## 1 Total Revenue    274515    365817    394328    383285
## 2  Gross Profit    104956    152836    170782    169148
## 3        EBITDA     77344    120233    130541    125820

Create a Line Plot for Profitability Data

# Reshape the data using pivot_longer for easy plotting with ggplot2
financial_long <- financial_summary %>%
  pivot_longer(cols = starts_with("TTM"), names_to = "Year", values_to = "Amount")

# Create a Line Plot showing the trend over time
ggplot(financial_long, aes(x = Year, y = Amount, color = Metric, group = Metric)) +
  geom_point() +   # Add points to mark the actual data
  geom_line() + # Add lines connecting the point
  theme_minimal() + # Use a clean, minimal theme
  labs(title = "Apple's Financial Performance (2020-2024)", x = "Year", y = "Amount ($ in millions)") +
  scale_y_continuous(labels = scales::comma)  # Format y-axis with commas for readability