Data Transformation HW

load data

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.4.4     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.0
✔ purrr     1.0.2     
── 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(dplyr)
library(ggplot2)
library(readr)
library(forcats)
options(scipen = 999)

blackrock_etf_screener<- read_csv("https://raw.githubusercontent.com/t-emery/sais-susfin_data/main/datasets/ishares_etf_screener_as_of_2023-12-27.csv")

Rows: 424 Columns: 18
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr  (10): ticker, name, asset_class, sub_asset_class, region, market, locat...
dbl   (6): gross_expense_ratio_percent, net_expense_ratio_percent, net_asset...
dttm  (2): incept_date, net_assets_as_of

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

blackrock_etf_screener

# A tibble: 424 × 18
   ticker name                        incept_date         gross_expense_ratio_…¹
   <chr>  <chr>                       <dttm>                               <dbl>
 1 IVV    iShares Core S&P 500 ETF    2000-05-15 00:00:00                   0.03
 2 IEFA   iShares Core MSCI EAFE ETF  2012-10-18 00:00:00                   0.07
 3 AGG    iShares Core U.S. Aggregat… 2003-09-22 00:00:00                   0.03
 4 IWF    iShares Russell 1000 Growt… 2000-05-22 00:00:00                   0.19
 5 IJR    iShares Core S&P Small-Cap… 2000-05-22 00:00:00                   0.06
 6 IJH    iShares Core S&P Mid-Cap E… 2000-05-22 00:00:00                   0.05
 7 IEMG   iShares Core MSCI Emerging… 2012-10-18 00:00:00                   0.09
 8 IWM    iShares Russell 2000 ETF    2000-05-22 00:00:00                   0.19
 9 IWD    iShares Russell 1000 Value… 2000-05-22 00:00:00                   0.19
10 TLT    iShares 20+ Year Treasury … 2002-07-22 00:00:00                   0.15
# ℹ 414 more rows
# ℹ abbreviated name: ¹gross_expense_ratio_percent
# ℹ 14 more variables: net_expense_ratio_percent <dbl>, net_assets_usd <dbl>,
#   net_assets_as_of <dttm>, asset_class <chr>, sub_asset_class <chr>,
#   region <chr>, market <chr>, location <chr>, investment_style <chr>,
#   msci_esg_fund_rating_aaa_ccc <chr>, msci_esg_quality_score_0_10 <dbl>,
#   msci_weighted_average_carbon_intensity_tons_co2e_m_sales <dbl>, …

Question 1

blackrock_etf_screener_esg <- blackrock_etf_screener |> 
   mutate(
    standard_or_esg = if_else(
      condition = is.na(sustainable_classification),
      true = "ESG",
      false = "Standard"
    ))|> 
    group_by(sustainable_classification) |> 
# Summarize to find the largest fund by net assets in each classification
    summarize(
      largest_fund_name = name[which.max(net_assets_usd)],
      largest_fund_net_assets = max(net_assets_usd),
      total_assets_in_classification = sum(net_assets_usd),
      percent_of_assets = (largest_fund_net_assets / total_assets_in_classification) * 100
  ) |> 
  # Rank the classifications by the % of assets in its largest fund
  mutate(rank = dense_rank(desc(percent_of_assets))) |> 
  # Arrange by the rank in descending order
  arrange(desc(rank))

blackrock_etf_screener_esg

# A tibble: 5 × 6
  sustainable_classification largest_fund_name            largest_fund_net_ass…¹
  <chr>                      <chr>                                         <dbl>
1 <NA>                       iShares Core S&P 500 ETF              399312542067.
2 Uplift                     iShares ESG Aware MSCI USA …           13385805595.
3 Screened                   iShares ESG Screened S&P 50…             182340754.
4 Thematic                   iShares Global Clean Energy…            3057666513.
5 Impact                     iShares USD Green Bond ETF               335216065.
# ℹ abbreviated name: ¹largest_fund_net_assets
# ℹ 3 more variables: total_assets_in_classification <dbl>,
#   percent_of_assets <dbl>, rank <int>

Question 2

# Find the 10 smallest ETFs by net assets
smallest_etfs <- blackrock_etf_screener %>%
  arrange(net_assets_usd) %>%
  head(10)

ggplot(smallest_etfs, aes(x = fct_reorder(name, net_assets_usd), y = net_assets_usd)) +
  geom_bar(stat = "identity", fill = "Pink") +
   coord_flip() +  
  labs(title = "10 Smallest ETFs",
       x = "ETF Name",
       y = "Net Assets (USD)",
       caption = "Zixi Fan") +
  theme_minimal() +
  scale_y_continuous(labels = scales::label_dollar(scale = 1))

Question 3

# Find the bottom quintile threshold for the MSCI ESG quality score
bottom_quintile_threshold <- quantile(blackrock_etf_screener$msci_esg_quality_score_0_10, 0.4, na.rm = TRUE)

# Find the funds in the bottom quintile of the MSCI ESG quality score
bottom_quintile_funds <- blackrock_etf_screener %>%
  filter(msci_esg_quality_score_0_10 <= bottom_quintile_threshold)

bottom_quintile_funds

# A tibble: 152 × 18
   ticker name                        incept_date         gross_expense_ratio_…¹
   <chr>  <chr>                       <dttm>                               <dbl>
 1 AGG    iShares Core U.S. Aggregat… 2003-09-22 00:00:00                   0.03
 2 IJR    iShares Core S&P Small-Cap… 2000-05-22 00:00:00                   0.06
 3 IJH    iShares Core S&P Mid-Cap E… 2000-05-22 00:00:00                   0.05
 4 IEMG   iShares Core MSCI Emerging… 2012-10-18 00:00:00                   0.09
 5 IWM    iShares Russell 2000 ETF    2000-05-22 00:00:00                   0.19
 6 TLT    iShares 20+ Year Treasury … 2002-07-22 00:00:00                   0.15
 7 IEF    iShares 7-10 Year Treasury… 2002-07-22 00:00:00                   0.15
 8 SHY    iShares 1-3 Year Treasury … 2002-07-22 00:00:00                   0.15
 9 IUSB   iShares Core Total USD Bon… 2014-06-10 00:00:00                   0.07
10 GOVT   iShares U.S. Treasury Bond… 2012-02-14 00:00:00                   0.05
# ℹ 142 more rows
# ℹ abbreviated name: ¹gross_expense_ratio_percent
# ℹ 14 more variables: net_expense_ratio_percent <dbl>, net_assets_usd <dbl>,
#   net_assets_as_of <dttm>, asset_class <chr>, sub_asset_class <chr>,
#   region <chr>, market <chr>, location <chr>, investment_style <chr>,
#   msci_esg_fund_rating_aaa_ccc <chr>, msci_esg_quality_score_0_10 <dbl>,
#   msci_weighted_average_carbon_intensity_tons_co2e_m_sales <dbl>, …

# Calculate the number of funds and the total assets, grouped by asset class and sub-asset class
funds_summary <- bottom_quintile_funds %>%
  group_by (asset_class, sub_asset_class) %>%
  summarize(
    number_of_funds = n(),
    total_assets = sum(net_assets_usd, na.rm = TRUE)
  )

`summarise()` has grouped output by 'asset_class'. You can override using the
`.groups` argument.

funds_summary

# A tibble: 15 × 4
# Groups:   asset_class [4]
   asset_class  sub_asset_class        number_of_funds  total_assets
   <chr>        <chr>                            <int>         <dbl>
 1 Equity       All Cap                             40 124665679945.
 2 Equity       Large Cap                            3   6868475033 
 3 Equity       Large/Mid Cap                       15  49976527609.
 4 Equity       Mid Cap                              4  93098049659.
 5 Equity       Mid/Small Cap                        1    987699241.
 6 Equity       Small Cap                           18 188148191582.
 7 Fixed Income Corporates                           1     41339182.
 8 Fixed Income Credit                               2   1994922916.
 9 Fixed Income Government                          24 220879023443.
10 Fixed Income High Yield                          20  40239808172.
11 Fixed Income Inflation                            4  27172554003.
12 Fixed Income Multi Sectors                       11 139515488512.
13 Multi Asset  Static Allocation                    2    870450502.
14 Multi Asset  Target Date                          1      2711720.
15 Real Estate  Real Estate Securities               6  11753595625.

Question 4

#1 Show the distribution of funds across different asset classes.

asset_class_distribution <- blackrock_etf_screener %>%
  count(asset_class) %>%
  arrange(desc(n))

ggplot(asset_class_distribution, aes(x = reorder(asset_class, n), y = n, fill = asset_class)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Distribution of iShares ETFs by Asset Class",
       x = "Asset Class",
       y = "Number of Funds") +
  theme_minimal()

#2 Identify and visualize the top 10 largest ETFs by total assets.

top_10_etfs <- blackrock_etf_screener %>%
  arrange(desc(net_assets_usd)) %>%
  slice_head(n = 10)

ggplot(top_10_etfs, aes(x = reorder(name, net_assets_usd), y = net_assets_usd)) +
  geom_bar(stat = "identity", fill = "blue") +
  coord_flip() +
  labs(title = "Top 10 Largest iShares ETFs by Total Assets",
       x = "ETF Name",
       y = "Total Assets (USD)") +
  theme_minimal()

#3 Explore the distribution of MSCI ESG Quality Scores across all ETFs.

ggplot(blackrock_etf_screener, aes(x = msci_esg_quality_score_0_10)) +
  geom_histogram(binwidth = 0.1, fill = "green", color = "Black") +
  labs(title = "Distribution of MSCI ESG Quality Scores Among iShares ETFs",
       x = "ESG Quality Score",
       y = "Frequency") +
  theme_minimal()

Warning: Removed 44 rows containing non-finite values (`stat_bin()`).

#4 Compare the average MSCI ESG Quality Score across different asset classes.

avg_esg_by_class <- blackrock_etf_screener %>%
  group_by(asset_class) %>%
  summarize(average_esg_score = mean(msci_esg_quality_score_0_10, na.rm = TRUE))

ggplot(avg_esg_by_class, aes(x = asset_class, y = average_esg_score, fill = asset_class)) +
  geom_col() +
  coord_flip() +
  labs(title = "Average MSCI ESG Quality Score by Asset Class",
       x = "Asset Class",
       y = "Average ESG Score") +
  theme_minimal()

Warning: Removed 1 rows containing missing values (`position_stack()`).

#5 Assess the total assets by ESG score quintiles to see if higher ESG scores correlate with higher total assets.

blackrock_etf_screener$esg_quintile <- cut(blackrock_etf_screener$msci_esg_quality_score_0_10, breaks = quantile(blackrock_etf_screener$msci_esg_quality_score_0_10, probs = 0:5/5, na.rm = TRUE), include.lowest = TRUE, labels = FALSE)

assets_by_esg <- blackrock_etf_screener %>%
  group_by(esg_quintile) %>%
  summarize(total_assets = sum(net_assets_usd, na.rm = TRUE))

ggplot(assets_by_esg, aes(x = as.factor(esg_quintile), y = total_assets, fill = as.factor(esg_quintile))) +
  geom_col() +
  labs(title = "Total Assets by ESG Score Quintile",
       x = "ESG Score Quintile",
       y = "Total Assets (USD)") +
  theme_minimal()

The echo: false option disables the printing of code (only output is displayed).