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.
view(blackrock_etf_screener)
Problem 1:
Create a dplyr pipeline to create a tibble fulfills the following parameters:
only contains ESG funds (no standard funds)
contains the largest fund from each sustainable classification.
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).
Ranks each sustainable classification by the % of assets in its largest fund, and arranges them in descending order.
#create new variable to sort whether a stock is ESG or notblackrock_etf_screener_new <- blackrock_etf_screener |>mutate(standard_or_esg =if_else(condition =is.na(sustainable_classification), "Standard", "ESG"), inception_year =year(incept_date), net_assets_bn_usd = net_assets_usd /10^9)#creat new pipelinepipeline <- blackrock_etf_screener_new %>%filter(standard_or_esg=="ESG") %>%group_by(sustainable_classification) %>%summarize(top_fund_assets =max(net_assets_usd),total_assets =sum(net_assets_usd),.groups ='drop') %>%mutate(percent_of_category = top_fund_assets / total_assets) %>%arrange(desc(percent_of_category))pipeline
Create a sorted bar chart of the 10 smallest ETFs.
Hint: for sorting the bar chart, look up fct_reorder() . Make sure your chart has meaningful titles and labels, including numbers of an appropriate magnitude.
#select the 10 smallest ETFstop_10_smallest_etfs <- blackrock_etf_screener_new %>%arrange(net_assets_usd) %>%top_n(-10, net_assets_usd) %>%mutate(etf_name_reordered =fct_reorder(name, net_assets_usd))# Drawing the bar graphggplot(top_10_smallest_etfs, aes(x = etf_name_reordered, y = net_assets_usd)) +geom_bar(stat ="identity", fill="blue") +coord_flip() +labs(title ="10 Smallest ETFs by Net Assets",x ="ETF Name",y ="Net Assets ($)") +theme_minimal()
Problem 3:
Find the funds in the bottom quintile of the MSCI ESG quality score.
For the funds in the bottom quintile of MSCI ESG quality scores, calculate i) the number of funds, and ii) the total assets, grouped by asset class (e.g. Equity, Fixed Income) and sub-asset class (e.g. Emerging Markets)
#get quintile thresholdquintile_threshold <- blackrock_etf_screener_new %>%summarise(bottom_quintile_threshold =quantile(msci_esg_quality_score_0_10, 0.2, na.rm=TRUE)) %>%pull(bottom_quintile_threshold)# filter for funds in the bottom quintilebottom_quintile_funds <- blackrock_etf_screener_new %>%filter(msci_esg_quality_score_0_10 <= quintile_threshold)# summarize by asset class and sub-asset classsummary_stats <- bottom_quintile_funds %>%group_by(asset_class, sub_asset_class) %>%summarise(number_of_funds =n(),total_assets_millions =sum(net_assets_usd, na.rm =TRUE) /10^6,.groups ='drop') %>%mutate(total_assets_millions =paste0(round(total_assets_millions, 2), "m"))summary_stats
# A tibble: 9 × 4
asset_class sub_asset_class number_of_funds total_assets_millions
<chr> <chr> <int> <chr>
1 Equity All Cap 23 95486.05m
2 Equity Large Cap 1 1852.51m
3 Equity Large/Mid Cap 10 42421.98m
4 Equity Small Cap 17 187292.56m
5 Fixed Income Corporates 1 41.34m
6 Fixed Income Credit 1 408.04m
7 Fixed Income Government 3 17560.71m
8 Fixed Income High Yield 16 38239.62m
9 Real Estate Real Estate Securities 4 7145.44m
Problem 4:
We only began exploring this data. Use your newfound dplyr skills to uncover actionable insights from the dataset.
Show your work.
Present 5 repeatable factoids or data visualizations. As always, make sure your data visualizations have meaningful titles and labels. We will share these in class.
Insight 1:
assets_by_class <- blackrock_etf_screener_new %>%group_by(asset_class) %>%summarise(total_assets_usd =sum(net_assets_usd, na.rm =TRUE)) %>%arrange(desc(total_assets_usd))ggplot(assets_by_class, aes(x ="", y = total_assets_usd/10^9, fill = asset_class)) +geom_bar(width =1, stat ="identity", color ="white") +coord_polar("y", start =0) +labs(title ="Proportion of Total Assets by Asset Class", x ="", y ="", fill ="Asset Class") +theme_void()
Insight 2:
esg_by_subclass <- blackrock_etf_screener_new %>%group_by(sub_asset_class) %>%summarise(average_esg_score =mean(msci_esg_quality_score_0_10, na.rm =TRUE)) %>%arrange(desc(average_esg_score)) %>%slice_max(average_esg_score, n =5)ggplot(esg_by_subclass, aes(x = average_esg_score, y =reorder(sub_asset_class, average_esg_score))) +geom_point(color ="forestgreen", size =4) +labs(title ="Top 5 Sub-Asset Classes by Average ESG Score", x ="Average ESG Score", y ="Sub-Asset Class")
Insight 3:
bottom_quintile <- blackrock_etf_screener_new %>%filter(msci_esg_quality_score_0_10 <=quantile(msci_esg_quality_score_0_10, 0.2, na.rm =TRUE))funds_by_class_bottom_quintile <- bottom_quintile %>%group_by(asset_class) %>%summarise(number_of_funds =n()) %>%arrange(desc(number_of_funds))ggplot(funds_by_class_bottom_quintile, aes(x = asset_class, y = number_of_funds)) +geom_tile(aes(fill = number_of_funds), color ="white") +scale_fill_gradient(low ="white", high ="red") +labs(title ="Funds in the Lowest ESG Quintile by Asset Class", x ="Asset Class", y ="Number of Funds", fill ="Count") +theme_minimal() +theme(axis.text.x =element_text(angle =45, hjust =1))
Insight 4:
ggplot(blackrock_etf_screener_new, aes(x = msci_esg_quality_score_0_10)) +geom_histogram(binwidth =0.1, fill ="cadetblue", color ="white") +labs(title ="Distribution of MSCI ESG Quality Scores Across All Funds",x ="ESG Quality Score (0-10)", y ="Number of Funds") +theme_minimal()
