My Third Quarto Document

Author

Yin Guan

Problem 1:

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

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.

ESG_only <- blackrock_etf_screener |>
  filter(!is.na(sustainable_classification))
ESG_only

# A tibble: 40 × 18
   ticker name                        incept_date         gross_expense_ratio_…¹
   <chr>  <chr>                       <dttm>                               <dbl>
 1 ESGU   iShares ESG Aware MSCI USA… 2016-12-01 00:00:00                   0.15
 2 ESGD   iShares ESG Aware MSCI EAF… 2016-06-28 00:00:00                   0.2 
 3 SUSA   iShares MSCI USA ESG Selec… 2005-01-24 00:00:00                   0.25
 4 DSI    iShares MSCI KLD 400 Socia… 2006-11-14 00:00:00                   0.25
 5 ESGE   iShares ESG Aware MSCI EM … 2016-06-28 00:00:00                   0.25
 6 EAGG   iShares ESG Aware U.S. Agg… 2018-10-18 00:00:00                   0.11
 7 ICLN   iShares Global Clean Energ… 2008-06-24 00:00:00                   0.41
 8 USCL   iShares Climate Conscious … 2023-06-06 00:00:00                   0.08
 9 PABU   iShares Paris-Aligned Clim… 2022-02-08 00:00:00                   0.1 
10 LCTU   BlackRock U.S. Carbon Tran… 2021-04-06 00:00:00                   0.29
# ℹ 30 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>, …

classification_group <- ESG_only |>
  group_by(sustainable_classification)
each_largest_fund <- classification_group |>
  slice_max(order_by = net_assets_usd, n=1)
each_largest_fund

# A tibble: 4 × 18
# Groups:   sustainable_classification [4]
  ticker name  incept_date         gross_expense_ratio_…¹ net_expense_ratio_pe…²
  <chr>  <chr> <dttm>                               <dbl>                  <dbl>
1 BGRN   iSha… 2018-11-13 00:00:00                   0.2                    0.2 
2 XVV    iSha… 2020-09-22 00:00:00                   0.08                   0.08
3 ICLN   iSha… 2008-06-24 00:00:00                   0.41                   0.41
4 ESGU   iSha… 2016-12-01 00:00:00                   0.15                   0.15
# ℹ abbreviated names: ¹gross_expense_ratio_percent, ²net_expense_ratio_percent
# ℹ 13 more variables: 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>,
#   msci_esg_percent_coverage <dbl>, sustainable_classification <chr>

percent_of_assets <- classification_group |>
  mutate(category_assets = sum(net_assets_usd)) |>
  slice_max(order_by = net_assets_usd, n=1) |>
  mutate(percentage = (net_assets_usd/category_assets)*100)
percent_of_assets

# A tibble: 4 × 20
# Groups:   sustainable_classification [4]
  ticker name  incept_date         gross_expense_ratio_…¹ net_expense_ratio_pe…²
  <chr>  <chr> <dttm>                               <dbl>                  <dbl>
1 BGRN   iSha… 2018-11-13 00:00:00                   0.2                    0.2 
2 XVV    iSha… 2020-09-22 00:00:00                   0.08                   0.08
3 ICLN   iSha… 2008-06-24 00:00:00                   0.41                   0.41
4 ESGU   iSha… 2016-12-01 00:00:00                   0.15                   0.15
# ℹ abbreviated names: ¹gross_expense_ratio_percent, ²net_expense_ratio_percent
# ℹ 15 more variables: 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>,
#   msci_esg_percent_coverage <dbl>, sustainable_classification <chr>, …

percentage_arrange <- percent_of_assets |>
  arrange(desc(percentage))
percentage_arrange

# A tibble: 4 × 20
# Groups:   sustainable_classification [4]
  ticker name  incept_date         gross_expense_ratio_…¹ net_expense_ratio_pe…²
  <chr>  <chr> <dttm>                               <dbl>                  <dbl>
1 BGRN   iSha… 2018-11-13 00:00:00                   0.2                    0.2 
2 ICLN   iSha… 2008-06-24 00:00:00                   0.41                   0.41
3 XVV    iSha… 2020-09-22 00:00:00                   0.08                   0.08
4 ESGU   iSha… 2016-12-01 00:00:00                   0.15                   0.15
# ℹ abbreviated names: ¹gross_expense_ratio_percent, ²net_expense_ratio_percent
# ℹ 15 more variables: 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>,
#   msci_esg_percent_coverage <dbl>, sustainable_classification <chr>, …

Problem 2:

smallest_ETFs <- blackrock_etf_screener |>
  slice_min(order_by = net_assets_usd, n=10) |>
  mutate(EFT_name = fct_reorder(name, net_assets_usd))
smallest_ETFs

# A tibble: 10 × 19
   ticker name                        incept_date         gross_expense_ratio_…¹
   <chr>  <chr>                       <dttm>                               <dbl>
 1 ERUS   iShares MSCI Russia ETF     2010-11-09 00:00:00                   0.65
 2 AGIH   iShares Inflation Hedged U… 2022-06-22 00:00:00                   0.16
 3 HYGI   iShares Inflation Hedged H… 2022-06-22 00:00:00                   1.01
 4 IRTR   iShares® LifePath® Retirem… 2023-10-17 00:00:00                   0.08
 5 ITDA   iShares® LifePath® Target … 2023-10-17 00:00:00                   0.09
 6 ITDH   iShares® LifePath® Target … 2023-10-17 00:00:00                   0.11
 7 ITDI   iShares® LifePath® Target … 2023-10-17 00:00:00                   0.11
 8 ITDG   iShares® LifePath® Target … 2023-10-17 00:00:00                   0.11
 9 ITDF   iShares® LifePath® Target … 2023-10-17 00:00:00                   0.11
10 IWFH   iShares Virtual Work and L… 2020-09-29 00:00:00                   0.47
# ℹ abbreviated name: ¹gross_expense_ratio_percent
# ℹ 15 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>,
#   msci_esg_percent_coverage <dbl>, sustainable_classification <chr>, …

ggplot(smallest_ETFs) +
  aes(x = EFT_name, weight = net_assets_usd) +
  geom_bar(fill = "black") +
  labs(
    x = "Assets",
    y = "Name",
    title = "Assets of 10 smallest ETFs",
    caption = "Yin Guan"
  ) +
  theme_minimal()

Problem 3:

bottom_quintile <- blackrock_etf_screener |>
  mutate(esg_quintile = ntile(msci_esg_quality_score_0_10, 5)) |>
  filter(esg_quintile == 1)
bottom_quintile

# A tibble: 76 × 19
   ticker name                        incept_date         gross_expense_ratio_…¹
   <chr>  <chr>                       <dttm>                               <dbl>
 1 IJR    iShares Core S&P Small-Cap… 2000-05-22 00:00:00                   0.06
 2 IEMG   iShares Core MSCI Emerging… 2012-10-18 00:00:00                   0.09
 3 IWM    iShares Russell 2000 ETF    2000-05-22 00:00:00                   0.19
 4 HYG    iShares iBoxx $ High Yield… 2007-04-04 00:00:00                   0.49
 5 EEM    iShares MSCI Emerging Mark… 2003-04-07 00:00:00                   0.69
 6 EMB    iShares J.P. Morgan USD Em… 2007-12-17 00:00:00                   0.39
 7 PFF    iShares Preferred and Inco… 2007-03-26 00:00:00                   0.46
 8 IWN    iShares Russell 2000 Value… 2000-07-24 00:00:00                   0.24
 9 USHY   iShares Broad USD High Yie… 2017-10-25 00:00:00                   0.08
10 IWO    iShares Russell 2000 Growt… 2000-07-24 00:00:00                   0.24
# ℹ 66 more rows
# ℹ abbreviated name: ¹gross_expense_ratio_percent
# ℹ 15 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>, …

bottom_quintile |>
  group_by(asset_class, sub_asset_class) |>
  summarise(
    total_assets = sum(net_assets_usd, na.rm = TRUE),
    num_funds = n()
    )

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

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

Problem 4:

useful_observation <- blackrock_etf_screener |>
  filter(!is.na(msci_weighted_average_carbon_intensity_tons_co2e_m_sales)) |>
  filter(!is.na(msci_esg_fund_rating_aaa_ccc))
useful_observation

# A tibble: 354 × 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 EFA    iShares MSCI EAFE ETF       2001-08-14 00:00:00                   0.33
# ℹ 344 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>, …

carbon_fund_rate <- useful_observation |>
  group_by(msci_esg_fund_rating_aaa_ccc) |>
  summarise(
    avarage_carbon = mean(msci_weighted_average_carbon_intensity_tons_co2e_m_sales, na.rm = TRUE),
    num_carbon = n()
    ) 
carbon_fund_rate

# A tibble: 5 × 3
  msci_esg_fund_rating_aaa_ccc avarage_carbon num_carbon
  <chr>                                 <dbl>      <int>
1 A                                      186.        203
2 AA                                     135.         73
3 AAA                                    132.          1
4 BB                                     508.          8
5 BBB                                    259.         69

ggplot(carbon_fund_rate) +
  aes(
    x = msci_esg_fund_rating_aaa_ccc,
    weight = avarage_carbon
  ) +
  geom_bar(fill = "black") +
  labs(
    x = "ESG Fund Rating",
    y = "Average Carbon Intensity",
    title = "Average Carbon Intensity in Each Fund Rating",
    caption = "Yin Guan"
  ) +
  theme_minimal()

heavy_carbon <- useful_observation |>
  group_by(msci_esg_fund_rating_aaa_ccc) |>
  mutate(above_avg = msci_weighted_average_carbon_intensity_tons_co2e_m_sales > mean(msci_weighted_average_carbon_intensity_tons_co2e_m_sales, na.rm = TRUE)) |>
  summarise(num_above_avg = sum(above_avg, na.rm = TRUE))
heavy_carbon

# A tibble: 5 × 2
  msci_esg_fund_rating_aaa_ccc num_above_avg
  <chr>                                <int>
1 A                                       71
2 AA                                      17
3 AAA                                      0
4 BB                                       4
5 BBB                                     30

ggplot(heavy_carbon) +
  aes(
    x = msci_esg_fund_rating_aaa_ccc,
    weight = num_above_avg
  ) +
  geom_bar(fill = "black") +
  labs(
    x = "Fund rating",
    y = "Number",
    title = "The number of heavy carbon intensity in each funding rate",
    subtitle = "Heavy refer as higher than average",
    caption = "Yin Guan"
  ) +
  theme_minimal()