Data Transformation Deliverable

Organizing Data

Loading and Adding Required Libraries

library(tidyverse)

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

Reading in the file

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>, …

Examining Data

blackrock_etf_screener |> #find ESG funds
  group_by(sustainable_classification) |> 
  summarize(
    # how many funds?
    n_funds = n(),
    # how much money?
    assets_usd = sum(net_assets_usd, na.rm = TRUE)
    ) |> 
# arrange in descending order
arrange(assets_usd |> desc())

# A tibble: 5 × 3
  sustainable_classification n_funds assets_usd
  <chr>                        <int>      <dbl>
1 <NA>                           384    2.55e12
2 Uplift                          30    5.14e10
3 Thematic                         6    3.43e 9
4 Screened                         3    3.83e 8
5 Impact                           1    3.35e 8

New DataFrame

blackrock_etf_screener_w_new_features <- blackrock_etf_screener |> 
  mutate(
    # if the sustainable_classification column is NA, then the fund is not an ESG fund.
    standard_or_esg = if_else(
      condition = is.na(sustainable_classification),
      true = "Standard",
      false = "ESG"
    ),
    # lubridate::year() extracts the year from a date
    inception_year = lubridate::year(incept_date),
    
    # Change to a meaningful magnitude for the data. In asset management, billions is a good default. 
    net_assets_bn_usd = net_assets_usd/10^9,
    
    # let's put our new variables at the front so we can see them easily.
    .before = everything()
  ) |>
  rename(MSCI_WACI = msci_weighted_average_carbon_intensity_tons_co2e_m_sales)

blackrock_etf_screener_w_new_features

# A tibble: 424 × 21
   standard_or_esg inception_year net_assets_bn_usd ticker name                 
   <chr>                    <dbl>             <dbl> <chr>  <chr>                
 1 Standard                  2000             399.  IVV    iShares Core S&P 500…
 2 Standard                  2012             107.  IEFA   iShares Core MSCI EA…
 3 Standard                  2003             101.  AGG    iShares Core U.S. Ag…
 4 Standard                  2000              82.1 IWF    iShares Russell 1000…
 5 Standard                  2000              78.2 IJR    iShares Core S&P Sma…
 6 Standard                  2000              77.1 IJH    iShares Core S&P Mid…
 7 Standard                  2012              73.9 IEMG   iShares Core MSCI Em…
 8 Standard                  2000              67.7 IWM    iShares Russell 2000…
 9 Standard                  2000              55.4 IWD    iShares Russell 1000…
10 Standard                  2002              52.2 TLT    iShares 20+ Year Tre…
# ℹ 414 more rows
# ℹ 16 more variables: incept_date <dttm>, gross_expense_ratio_percent <dbl>,
#   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_WACI <dbl>, msci_esg_percent_coverage <dbl>, …

Homework problem 1: Specific tibble

large_esg_funds <- blackrock_etf_screener_w_new_features %>%
  dplyr::filter(standard_or_esg == "ESG") %>%
  group_by(sustainable_classification) %>%
  arrange(desc(net_assets_usd)) %>%
  summarize(
    fund_type = "ESG",
    largest_fund_name = first(name),
    largest_fund_net_assets = first(net_assets_usd),  
    total_assets_category = sum(net_assets_usd),
    percent_in_category = (largest_fund_net_assets / total_assets_category) * 100,
  ) %>%
  mutate(rank = rank(percent_in_category * -1)) %>%
  arrange(desc(percent_in_category))
# To view the resulting tibble
print(large_esg_funds)

# A tibble: 4 × 7
  sustainable_classification fund_type largest_fund_name  largest_fund_net_ass…¹
  <chr>                      <chr>     <chr>                               <dbl>
1 Impact                     ESG       iShares USD Green…             335216065.
2 Thematic                   ESG       iShares Global Cl…            3057666513.
3 Screened                   ESG       iShares ESG Scree…             182340754.
4 Uplift                     ESG       iShares ESG Aware…           13385805595.
# ℹ abbreviated name: ¹​largest_fund_net_assets
# ℹ 3 more variables: total_assets_category <dbl>, percent_in_category <dbl>,
#   rank <dbl>

Homework problem 2:

#?fct_reorder

ten_small <- blackrock_etf_screener_w_new_features %>%
  filter(standard_or_esg == "ESG") %>%
  arrange(net_assets_bn_usd) %>%
  head(10)

ten_small$name <- fct_reorder(ten_small$name,ten_small$net_assets_bn_usd)

ggplot(ten_small) +
  aes(x= net_assets_bn_usd ,y=name) +
  geom_bar(stat= "identity") +
  labs(title="10 Smallest ETFs in Net Assets (BN Dollars)",
       x="Net Assets (in billion USD)",
       y="ETF Name")+
  theme_classic()

Homework 3: Find the funds

Find the funds in the bottom quintile of the MSCI ESG quality score. calculate the number of funds by total assets by asset class and sub asset class.

bottom_quintile_threshold <- quantile(blackrock_etf_screener_w_new_features$msci_esg_quality_score_0_10, 0.2, na.rm = TRUE)

# Filter the funds in the bottom quintile
bottom_quintile_funds <- blackrock_etf_screener_w_new_features %>%
  filter(msci_esg_quality_score_0_10 <= bottom_quintile_threshold) %>%
  group_by(asset_class, sub_asset_class) %>%
  summarize(
    number_of_funds = n(),
    total_assets_usd = sum(net_assets_usd),
    .groups = "drop",
  )

# view tibble
print(bottom_quintile_funds)

# A tibble: 9 × 4
  asset_class  sub_asset_class        number_of_funds total_assets_usd
  <chr>        <chr>                            <int>            <dbl>
1 Equity       All Cap                             23     95486052602.
2 Equity       Large Cap                            1      1852506812.
3 Equity       Large/Mid Cap                       10     42421984678.
4 Equity       Small Cap                           17    187292555389.
5 Fixed Income Corporates                           1        41339182.
6 Fixed Income Credit                               1       408038372.
7 Fixed Income Government                           3     17560711395.
8 Fixed Income High Yield                          16     38239624578.
9 Real Estate  Real Estate Securities               4      7145439523.

Homework 4

Graph 1 Asset Class Distribution of ESG Funds

# Calculate the number of funds issued per year for each fund type (Standard and ESG)
funds_issued_per_year <- blackrock_etf_screener_w_new_features %>%
  group_by(inception_year, standard_or_esg, asset_class) %>%
  summarise(n_funds = n(), .groups = 'drop')

ggplot(funds_issued_per_year, aes(x = inception_year, y = n_funds,  fill = standard_or_esg)) +
  geom_bar(stat = "identity") +
  labs(title = "Number of Standard and ESG Funds Issued Per Year",
       x = "Year",
       y = "Number of Funds",
       fill = "Fund Type") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Improve readability

Graph 2

# Calculate the average net and gross expense ratios for each year, for standard and ESG funds
expense_ratios_by_year <- blackrock_etf_screener_w_new_features %>%
  group_by(inception_year, standard_or_esg) %>%
  summarize(
    avg_net_expense_ratio = mean(net_expense_ratio_percent, na.rm = TRUE),
    avg_gross_expense_ratio = mean(gross_expense_ratio_percent, na.rm = TRUE),
    .groups = 'drop'
  )

# Reshape for plotting
expense_ratios_long <- expense_ratios_by_year %>%
  pivot_longer(cols = starts_with("avg_"), names_to = "expense_type", values_to = "average_ratio") %>%
  mutate(expense_type = factor(expense_type, levels = c("avg_net_expense_ratio", "avg_gross_expense_ratio"), labels = c("Net Expense Ratio", "Gross Expense Ratio")))

ggplot(expense_ratios_long, aes(x = inception_year, y = average_ratio, color = expense_type, group = interaction(standard_or_esg, expense_type))) +
  geom_line() +
  facet_wrap(~standard_or_esg, scales = "free_y") +
  labs(title = "Comparison of Average Net and Gross Expense Ratios by Fund Type Over Years",
       x = "Year",
       y = "Average Expense Ratio (%)",
       color = "Expense Type") +
  theme_minimal()

Graph 3 Net Assets USD Composition Comparison: Standard vs. ESG Funds

ggplot(blackrock_etf_screener_w_new_features, aes(x=standard_or_esg, y=net_assets_usd, fill=asset_class)) +
  geom_bar(stat="summary", fun="sum", position="dodge") +
  labs(title="Net Assets USD Composition Comparison: Standard vs. ESG Funds",
       x="Fund Type", y="Net Assets (USD)", fill="Asset Class") +
  scale_y_log10() + #log10 to improve readability but don't know how to make the x axis num show
  theme_minimal() +
  theme(axis.text.x = element_text(angle=45, hjust=1)) # Improve x-axis labels readability

Graph 4 Share of Funds by Fund Type and Region

# Aggregating net_assets_usd by standard_or_esg and region
funds_aggregated <- blackrock_etf_screener_w_new_features %>%
  group_by(standard_or_esg, region) %>%
  summarize(total_net_assets_usd = sum(net_assets_usd, na.rm = TRUE), .groups = 'drop')

# Calculate the proportion for the pie chart
funds_aggregated <- funds_aggregated %>%
  mutate(proportion = total_net_assets_usd / sum(total_net_assets_usd))

ggplot(funds_aggregated, aes(x="", y=proportion, fill=factor(paste(standard_or_esg, region)))) +
  geom_bar(width = 1, stat = "identity", color = "white") +
  coord_polar("y", start=0) +
  scale_fill_brewer(palette="Set3", name="Fund Type and Region") +
  labs(title="Share of Funds by Standard/ESG and Region, Weighted by Net Assets", x="", y="") +
  theme_void() +
  theme(legend.title = element_text(size=10), legend.text = element_text(size=9))

Graph 5 Average MSCI Weighted Average Carbon Intensity Over Years by Fund Type

#blackrock_etf_screener_w_new_features <- blackrock_etf_screener_w_new_features %>%
 # rename(MSCI_WACI = msci_weighted_average_carbon_intensity_tons_co2e_m_sales) added to top

# Calculate the average MSCI_WACI per year for standard and ESG funds
avg_MSCI_WACI_per_year <- blackrock_etf_screener_w_new_features %>%
  group_by(inception_year, standard_or_esg) %>%
  summarize(average_MSCI_WACI = mean(MSCI_WACI, na.rm = TRUE), .groups = 'drop')

ggplot(avg_MSCI_WACI_per_year, aes(x =inception_year, y = average_MSCI_WACI, color = standard_or_esg)) +
  geom_point() +
  geom_smooth(method = "lm") +
  labs(title = "Average MSCI WACI Over Years by Fund Type",
       x = "Year", y = "Average MSCI WACI (tCO2e/$M sales)",
       color = "Fund Type") +
  theme_minimal()

`geom_smooth()` using formula = 'y ~ x'

Graph 6 Bonue Graph: Average MSCI WACI Over Years by ESG Classification

# Filter for ESG funds and calculate the average MSCI_WACI per year for each ESG classification
avg_MSCI_WACI_per_year_esg <- blackrock_etf_screener_w_new_features %>%
  filter(standard_or_esg == "ESG") %>%
  group_by(inception_year, sustainable_classification) %>%
  summarize(average_MSCI_WACI = mean(MSCI_WACI, na.rm = TRUE), .groups = 'drop')

ggplot(avg_MSCI_WACI_per_year_esg, aes(x = inception_year, y = average_MSCI_WACI, color = sustainable_classification)) +
  geom_point() +
  geom_smooth(method = "lm") +
  labs(title = "Average MSCI WACI Over Years by ESG Classification",
       subtitle = "Note that we don't yet have enough datapoints for this attempt",
       x = "Year", y = "Average MSCI WACI",
       color = "ESG Classification") +
  theme_minimal()

`geom_smooth()` using formula = 'y ~ x'