Data transfomation

library(ggplot2)

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.3     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ lubridate 1.9.3     ✔ tibble    3.2.1
✔ purrr     1.0.2     ✔ tidyr     1.3.0
── 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)

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

#1
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")) |>
  filter(standard_or_esg == "ESG") |> 
  group_by(sustainable_classification) |>
  summarize(
    big_fund=max(net_assets_usd), pcg_fund=big_fund/sum(net_assets_usd)*100
  )|>
  mutate(
    top_fund_rank=rank(desc(pcg_fund))
  ) |>
  arrange(top_fund_rank |> desc())

# A tibble: 4 × 4
  sustainable_classification     big_fund pcg_fund top_fund_rank
  <chr>                             <dbl>    <dbl>         <dbl>
1 Uplift                     13385805595.     26.0             4
2 Screened                     182340754.     47.6             3
3 Thematic                    3057666513.     89.2             2
4 Impact                       335216065.    100               1

min <- blackrock_etf_screener |>
  slice_min(net_assets_usd, n=10
            ) |>
  arrange(desc(net_assets_usd)
          )
ggplot(min, aes(x=net_assets_usd,y=reorder(ticker,net_assets_usd) ))+
  geom_bar(stat = "identity")+
  labs(title= "small fund top 10")

blackrock_etf_screener |>
  filter(
    msci_esg_quality_score_0_10 <= quantile(msci_esg_quality_score_0_10, 0.25, 
                                            na.rm = TRUE))|>
  count()

# A tibble: 1 × 1
      n
  <int>
1   104

blackrock_etf_screener |>
  filter(
    msci_esg_quality_score_0_10 <= quantile(msci_esg_quality_score_0_10, 0.25, 
                                            na.rm = TRUE))|>
  count()

# A tibble: 1 × 1
      n
  <int>
1   104

blackrock_etf_screener |>
  filter(
    msci_esg_quality_score_0_10 <= quantile(msci_esg_quality_score_0_10, 0.25, 
                                            na.rm = TRUE))|>
  group_by(asset_class, sub_asset_class) |>
  summarize(total_assets = sum(net_assets_usd, na.rm = TRUE))

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

# A tibble: 10 × 3
# Groups:   asset_class [3]
   asset_class  sub_asset_class         total_assets
   <chr>        <chr>                          <dbl>
 1 Equity       All Cap                 95486052602.
 2 Equity       Large Cap                1852506812.
 3 Equity       Large/Mid Cap           42421984678.
 4 Equity       Small Cap              188148191582.
 5 Fixed Income Corporates                 41339182.
 6 Fixed Income Credit                   1994922916.
 7 Fixed Income Government             220879023443.
 8 Fixed Income High Yield              39783846631.
 9 Fixed Income Inflation               27172554003.
10 Real Estate  Real Estate Securities   7145439523.

library(esquisse)
library(dplyr)
library(ggplot2)

#How many funds gross expense ratio is over 1 percent
blackrock_etf_screener %>%
  filter(gross_expense_ratio_percent >= 1 & gross_expense_ratio_percent <= 1.47) %>%
  ggplot() +
  aes(x = name, y = gross_expense_ratio_percent, fill = ticker) +
  geom_col() +
  scale_fill_hue(direction = 1) +
  theme_minimal()

#fund over 30 billions
blackrock_etf_screener %>%
  filter(net_assets_usd >= 3e+10 & net_assets_usd <= 4e+11) %>%
  ggplot() +
  aes(x = name, y = net_assets_usd, fill = asset_class) +
  geom_col() +
  scale_fill_hue(direction = 1) +
  theme_minimal() +
  theme(axis.title.x = element_text(size = 15))

#total asset by asset class in different region,only show the class over 10 billions.
blackrock_etf_screener %>%
  filter(net_assets_usd >= 1e+10 & net_assets_usd <= 4e+11) %>%
  ggplot() +
  aes(x = sub_asset_class, y = net_assets_usd, fill = region) +
  geom_col() +
  scale_fill_manual(values = c(`Asia Pacific` = "#F8766D", Europe = "#BD9A00", Global = "#31B425", 
                               Kuwait = "#00C19F", `Latin America` = "#20AFEC", `Middle East and Africa` = "#B280FC", `North America` = "#FF61C3"
  )) +
  labs(x = "Asset class", y = "Net asset", title = "Made by shiyu") +
  theme_minimal() +
  theme(axis.title.x = element_text(size = 9L))

#total asset by ESG class in different region
blackrock_etf_screener %>%
  filter(!is.na(sustainable_classification)) %>%
  ggplot() +
  aes(x = sustainable_classification, y = net_assets_usd, fill = region) +
  geom_col() +
  scale_fill_hue(direction = 1) +
  scale_y_continuous(trans = "log10") +
  theme_minimal()

#Net asset by different country besides USD and abord.
blackrock_etf_screener %>%
  filter(!(location %in% c("United States", "Broad"))) %>%
  ggplot() +
  aes(x = location, y = net_assets_usd, fill = sub_asset_class) +
  geom_col() +
  scale_fill_hue(direction = 1) +
  scale_y_continuous(trans = "log10") +
  theme_minimal()