library(tidyverse)
## Warning: package 'dplyr' was built under R version 4.2.3
## Warning: package 'stringr' was built under R version 4.2.3
## ── 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)
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>, …

Homework problem #1 i) only contains ESG funds (no standard funds); ii)ontains the largest fund from each sustainable classification; iii)shows the percent of assets that the top fund comprises for its category (e.g. the top Thematic fund is x% of the total assets of all thematic funds). iv)ranks each sustainable classification by the % of assets in its largest fund, and arranges them in descending order.

blackrock_etf_screener_esgfunds<- na.omit(blackrock_etf_screener)
blackrock_etf_screener_esgfunds |> 
  distinct(sustainable_classification)
## # A tibble: 4 × 1
##   sustainable_classification
##   <chr>                     
## 1 Uplift                    
## 2 Thematic                  
## 3 Impact                    
## 4 Screened
largest_funds <- blackrock_etf_screener_esgfunds |>
  group_by(sustainable_classification) |>
  mutate(total_assets = sum(net_assets_usd)) |> 
  slice_max(order_by = net_assets_usd, n = 1)  |>
  ungroup() |>
  mutate(percent_of_total_assets = (net_assets_usd / total_assets) * 100) |> 
  select(ticker, name, sustainable_classification, net_assets_usd, total_assets, percent_of_total_assets) # Select relevant columns

largest_funds <- largest_funds |> 
  mutate(fund_rank = rank(desc(percent_of_total_assets)))

Homework Problem #2

Create a sorted bar chart of the 10 smallest ETFs.

smallest10_etfs <- blackrock_etf_screener |> 
  slice_min(order_by = net_assets_usd, n = 10)|>
  rename(Name=name)

ggplot(smallest10_etfs, aes(x=Name, y=net_assets_usd/1e6, fill=Name)) +
  geom_col() +
  labs(title = "10 Smallest ETFs", x = "ETF Name", y = "Net Assets(Millions of USD)") +
  coord_flip() +
  theme(plot.title = element_text(face = "bold"))

Homework problem #3: Find the funds in the bottom quintile of the MSCI ESG quality score.

bottom_quintile <-quantile(blackrock_etf_screener$msci_esg_quality_score_0_10, 0.2,na.rm=TRUE)
bottom_quintile_funds <- blackrock_etf_screener |> 
  filter(msci_esg_quality_score_0_10 <= bottom_quintile)

For the funds in the bottom quintile of MSCI ESG quality scores, calculate the number of funds, and the total assets, grouped by asset class (e.g. Equity, Fixed Income) and sub-asset class (e.g. Emerging Markets)

count(bottom_quintile_funds)
## # A tibble: 1 × 1
##       n
##   <int>
## 1    76
bottom_quintile_funds |>
  group_by(asset_class) |>
  summarize(total_assets=sum(net_assets_usd))
## # A tibble: 3 × 2
##   asset_class   total_assets
##   <chr>                <dbl>
## 1 Equity       327053099481.
## 2 Fixed Income  56249713527.
## 3 Real Estate    7145439523.
bottom_quintile_funds |>
  group_by(sub_asset_class) |>
  summarize(total_assets=sum(net_assets_usd))
## # A tibble: 9 × 2
##   sub_asset_class         total_assets
##   <chr>                          <dbl>
## 1 All Cap                 95486052602.
## 2 Corporates                 41339182.
## 3 Credit                    408038372.
## 4 Government              17560711395.
## 5 High Yield              38239624578.
## 6 Large Cap                1852506812.
## 7 Large/Mid Cap           42421984678.
## 8 Real Estate Securities   7145439523.
## 9 Small Cap              187292555389.

Homework problem #4: Present 5 repeatable factoids or data visualizations. As always, make sure your data visualizations have meaningful titles and labels.

blackrock_etf_screener_ESG_vs_standard <- blackrock_etf_screener |> 
  mutate(standard_or_esg = if_else(condition = is.na(sustainable_classification),
      true = "Standard",false = "ESG"),
    net_assets_bn_usd = net_assets_usd/10^9,
     inception_year = lubridate::year(incept_date),
        .before = everything())
blackrock_etf_screener_ESG_vs_standard <- blackrock_etf_screener_ESG_vs_standard[!is.na(blackrock_etf_screener_ESG_vs_standard$msci_weighted_average_carbon_intensity_tons_co2e_m_sales), ]

Visualization #1: Asset distribution ESG vs standard ETF

ggplot(blackrock_etf_screener_ESG_vs_standard, 
       aes(x = standard_or_esg, y = net_assets_bn_usd, color = standard_or_esg)) +
  geom_boxplot() +
  labs(title = "Assets Distribution ESG vs standard ETF",
       x = "ETF Type",
       y = "Net Asset(Billions of USD)") +
  theme(plot.title = element_text(face = "bold"))

Visualization #2: Average CO2 intensity/sales by ESG ETF classification

blackrock_etf_screener_esgfunds |>
  group_by(sustainable_classification)|>
  summarise(average_carbon_intensity = mean(msci_weighted_average_carbon_intensity_tons_co2e_m_sales))|>
  ggplot(aes(x=sustainable_classification, y=average_carbon_intensity, fill=sustainable_classification)) +
  geom_col() +
  labs(title = "Average CO2 intensity/sales by classification", x = "Name", y = "Carbon emission/sales") +
  theme(plot.title = element_text(face = "bold"))

Visualization #3:

blackrock_etf_screener_ESG_vs_standard |>
  ggplot() +
  aes(x = standard_or_esg, y = msci_weighted_average_carbon_intensity_tons_co2e_m_sales, colour = standard_or_esg) +
  geom_point(shape = "circle") +
  labs(x = "ETF type", y = "Average CO2 intensity/sales", title = "Exploring the CO2 emission distribution") +
  theme(plot.title = element_text(face = "bold"))

Visualization #4: Exploring the relationship between asset and ESG score between ETF type

blackrock_etf_screener_ESG_vs_standard |>
  filter(net_assets_bn_usd<300) |>
ggplot(aes(x = net_assets_bn_usd, y =msci_esg_quality_score_0_10, color =standard_or_esg )) +
  geom_point()+
  labs(x = "Asset", y = "ESG score", title = "Exploring the relationship between asset and ESG score between ETF type") +
  theme_minimal()

Visualization #5:Overall trend of funds by years

etf_esg_vs_standard <- blackrock_etf_screener_ESG_vs_standard |> 
  group_by(standard_or_esg, inception_year) |> 
  count() |> 
  ungroup()

etf_esg_vs_standard |> 
  ggplot(aes(x = inception_year, y = n, fill = standard_or_esg)) +
  geom_bar(stat = "identity", position = "stack")+
  labs(x = "Inception year", y = "Number of Count", title = "Overall trend of funds by years") +
  theme(plot.title = element_text(face = "bold"))