── 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
# this will stop R from presenting data in scientific notation, which can be annoying.options(scipen =999)
#Read the cleaned data from class GitHub data repo.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_funds_only <- blackrock_etf_screener |>mutate(# if the sustainable_classification column is NA, then the fund is not an ESG fundstandard_or_esg =if_else(condition =is.na(sustainable_classification),true ="Standard",false ="ESG" ) )|># Then, filter to keep only ESG fundsfilter(standard_or_esg =="ESG") # View the resultprint(esg_funds_only)
Task 2: Contains the largest fund from each sustainable classification.
esg_funds_largest_by_classification <- esg_funds_only |># Group by sustainable_classificationgroup_by(sustainable_classification) |># For each group, select the fund with the maximum net_assets_bn_usdslice_max(net_assets_usd)# View the resultprint(esg_funds_largest_by_classification)
Task 3: Shows the percent of assets that the top fund comprises for its category.
# First, calculate the total assets per category and the percentage for each fund.esg_funds_with_percent <- esg_funds_only |>group_by(sustainable_classification) |>mutate(total_assets_in_category =sum(net_assets_usd),percent_of_category_assets = (net_assets_usd / total_assets_in_category) *100 ) |># Remove the grouping to avoid affecting subsequent operationsungroup() # Then, select the largest fund by net assets percent within each category.esg_funds_toppercent_by_category <- esg_funds_with_percent |>group_by(sustainable_classification) |>slice_max(order_by = net_assets_usd, with_ties =FALSE) |>ungroup() # Ensure the data is no longer grouped for general use.# View the resultprint(esg_funds_toppercent_by_category)
Task 4: Ranks each sustainable classification by the % of assets in its largest fund, and arranges them in descending order.
# Rank and arrange the sustainable classifications by the % of assets in their largest fund in descending orderesg_funds_ranked_by_top_percent <- esg_funds_toppercent_by_category %>%arrange(desc(percent_of_category_assets))# View the ranked resultprint(esg_funds_ranked_by_top_percent)
final_tibble <- blackrock_etf_screener |># Step 1: Filter for only ESG fundsmutate(# if the sustainable_classification column is NA, then the fund is not an ESG fundstandard_or_esg =if_else(condition =is.na(sustainable_classification),true ="Standard",false ="ESG" ) )|># Then, filter to keep only ESG fundsfilter(standard_or_esg =="ESG") |># Calculate total assets per sustainable classification for later usegroup_by(sustainable_classification) |>mutate(total_assets_in_category =sum(net_assets_usd, na.rm =TRUE)) |>ungroup() |># Step 2: Select the largest fund within each sustainable classificationgroup_by(sustainable_classification) |>slice_max(net_assets_usd, n =1, with_ties =FALSE) |># Step 3: Calculate the percentage of total assets for the largest fund within its categorymutate(percent_of_category_assets = (net_assets_usd / total_assets_in_category) *100) |>ungroup() |># Step 4: Rank and arrange sustainable classifications in descending order based on the percentarrange(desc(percent_of_category_assets)) |># Optionally, select relevant columns to tidy up the outputselect(sustainable_classification, ticker, name, net_assets_usd, percent_of_category_assets)# View the final tibbleprint(final_tibble)
Create a sorted bar chart of the 10 smallest ETFs.
library(dplyr)library(ggplot2)library(forcats)smallest_etfs <- blackrock_etf_screener |># Select the necessary columnsselect(ticker, net_assets_usd) |># Arrange ETFs by their size to identify the smallestarrange(net_assets_usd) |># Filter for the 10 smallest ETFstop_n(-10, net_assets_usd)# Create the bar chartggplot(smallest_etfs, aes(x =fct_reorder(ticker, net_assets_usd), y = net_assets_usd, fill = ticker)) +geom_bar(stat ="identity") +coord_flip() +# Flip coordinates to make the chart horizontal; helps with readability of tickersscale_fill_brewer(palette ="BrBG", direction =1) +geom_text(aes(label = scales::dollar_format(scale =1e-6, suffix ="M" )(net_assets_usd)), position =position_dodge(width =0.9), hjust =-0.1) +labs(title ="10 Smallest ETFs by Net Assets",x ="Ticker",y ="Net Assets (USD)",caption ="Data source: BlackRock ETF Screener") +theme_minimal() +# Convert to millions for readabilityscale_y_continuous(labels = scales::dollar_format(scale =1e-6, suffix ="M"))
Problem 3
Find the funds in the bottom quintile of the MSCI ESG quality score.
library(dplyr)library(ggplot2)# Step 1: Determine the threshold for the bottom quintile of the MSCI ESG quality scorebottom_quintile_threshold <- blackrock_etf_screener |>mutate(# if the sustainable_classification column is NA, then the fund is not an ESG fundstandard_or_esg =if_else(condition =is.na(sustainable_classification),true ="Standard",false ="ESG" ) )|># Then, filter to keep only ESG fundsfilter(standard_or_esg =="ESG") |>summarise(threshold =quantile(net_assets_usd, 0.20)) |>pull(threshold)# Step 2: Filter funds that are in the bottom quintile of the MSCI ESG quality scorebottom_quintile_funds <- blackrock_etf_screener |>filter(net_assets_usd <= bottom_quintile_threshold)# Step 3: Calculate the number of funds by total assets by asset class and sub asset classfunds_by_asset_class <- bottom_quintile_funds |>group_by(asset_class, sub_asset_class) |>summarise(total_assets =sum(net_assets_usd, na.rm =TRUE),.groups ="drop",number_of_funds =n())funds_by_asset_class
# A tibble: 9 × 4
asset_class sub_asset_class total_assets number_of_funds
<chr> <chr> <dbl> <int>
1 Equity All Cap 56786484. 12
2 Equity Large Cap 18408512. 3
3 Equity Large/Mid Cap 21701894. 5
4 Equity Mid/Small Cap 3942487. 1
5 Fixed Income High Yield 2607469. 1
6 Fixed Income Inflation 51007592. 8
7 Fixed Income Multi Sectors 7613896. 2
8 Multi Asset Multi Strategy 12757341. 2
9 Multi Asset Target Date 33122223. 10
Problem 4
Actionable insights from the dataset.
For the funds in the top quintile of MSCI ESG quality scores, calculate i) the number of funds, and ii) the total asset, grouped by market and region.
#Take a first look at the data library(dplyr)# Step 1: Determine the threshold for the top quintile of ESG quality scorestop_quintile_threshold <- blackrock_etf_screener %>%# Ensure we're only looking at funds with a defined ESG scorefilter(!is.na(sustainable_classification)) %>%# Calculate the 80th percentile for ESG scoressummarise(threshold =quantile(net_assets_usd, 0.80, na.rm =TRUE)) %>%pull(threshold)# Step 2: Filter funds that are in the top quintile of the ESG quality scorestop_quintile_funds <- blackrock_etf_screener %>%filter(net_assets_usd >= top_quintile_threshold)# Step 3: Calculate i) the number of funds, and ii) the total asset, grouped by market and regionfunds_summary_by_market_and_region <- top_quintile_funds %>%group_by(market, region) %>%summarise(number_of_funds =n(),total_assets =sum(net_assets_usd, na.rm =TRUE),.groups ="drop")# View the resultprint(funds_summary_by_market_and_region)
# A tibble: 7 × 4
market region number_of_funds total_assets
<chr> <chr> <int> <dbl>
1 Developed Asia Pacific 4 1.97e10
2 Developed Europe 4 1.57e10
3 Developed Global 29 3.80e11
4 Developed North America 93 1.92e12
5 Emerging Asia Pacific 6 2.76e10
6 Emerging Global 6 1.25e11
7 Emerging Latin America 3 1.00e10
FACTOIDS
North America has the highest number of funds: With 93 funds, North America leads in the number of funds among Developed markets.
Developed Markets dominate in Global presence: There are 29 funds classified as ‘Global’ in Developed markets, indicating a significant presence.
Asia Pacific is active in both Developed and Emerging markets: The Asia Pacific region has representation in both Developed (with 4 funds) and Emerging (with 6 funds) markets.
Total assets are substantial in Developed markets: Developed markets have a considerable amount of total assets, with the Global category alone amassing over 38 trillion.
Europe shows significant assets under management: In Europe, the funds have over 15 trillion in total assets, underscoring the region’s importance in Developed markets.
Emerging markets have a strong presence in Asia Pacific and Global categories: With total assets of approximately 2.76 trillion in Asia Pacific and 1.24 trillion in Global categories, Emerging markets are notable players in these regions.
Latin America has fewer funds but notable assets: Despite having only 3 funds in Emerging markets, Latin America holds over 1 trillion in assets.
A stacked bar for the funds in the top quintile of MSCI ESG quality scores, calculate i) the number of funds, and ii) the total asset, grouped by market and region.
library(ggplot2)# Assuming 'total_assets' are in USD and we want to display them in millions for better readabilityfunds_summary_by_market_and_region$total_assets_million <- funds_summary_by_market_and_region$total_assets /1e6# Generate a Faceted chartggplot(funds_summary_by_market_and_region, aes(x = region, y = total_assets_million, fill = region)) +geom_bar(stat ="identity", position ="dodge") +facet_wrap(~ market, scales ="free_x") +scale_fill_brewer(palette ="BrBG", direction =1) +geom_text(aes(label = number_of_funds), vjust =-0.5, position =position_dodge(width =0.9), size =3) +labs(title ="Top Quintile ESG Funds: Assets & Counts by Market",x ="Region",y ="Total Assets (in Millions USD)",fill ="Region") +theme_minimal() +theme(axis.text.x =element_text(angle =45, hjust =1))
Compare the number of tickers in fun rating level (msci_esg_fund_rating_aaa_ccc) group by esg fund type, generate suitable ggplot.
library(dplyr)library(ggplot2)# Step 1: Prepare the datadata_for_plot <- blackrock_etf_screener %>%filter(!is.na(msci_esg_fund_rating_aaa_ccc) &!is.na(sustainable_classification)) %>%count(msci_esg_fund_rating_aaa_ccc, sustainable_classification)# Step 2: Generate the ggplotggplot(data_for_plot, aes(x = msci_esg_fund_rating_aaa_ccc, y = n, fill = sustainable_classification)) +geom_bar(stat ="identity", position ="dodge") +geom_text(aes(label = n), position =position_dodge(width =0.9), vjust =-0.25, # Adjust vertical position to place text above barssize =3.5) +# Set text color and sizescale_fill_brewer(palette ="RdYlBu", direction =1) +labs(title ="Ticker Counts by MSCI ESG Fund Rating and ESG Fund Type",x ="MSCI ESG Fund Rating",y ="Number of Tickers",fill ="Fund Type") +theme_minimal() +theme(axis.text.x =element_text(angle =45, hjust =1))
Factoid
The bar chart reveals that Uplift ESG funds predominantly occupy the ‘A’ and ‘AA’ rating category with , far outnumbering other ESG funds.
Examining the trend of the msci_esg_quality_score_0_10 over years (incept_date) from 2010
library(dplyr)library(ggplot2)library(lubridate)# Convert incept_date to Date type and msci_esg_quality_score_0_10 to numeric blackrock_etf_screener_w_new_features <- blackrock_etf_screener %>%mutate(incept_date =ymd(incept_date), # Convert to Date type using lubridate's ymd functionmsci_esg_quality_score_0_10 =as.numeric(msci_esg_quality_score_0_10) # Convert to numeric ) %>%# Filter for rows where incept_date is greater than or equal to the year 2010filter(year(incept_date) >=2010)# Plot the trend of msci_esg_quality_score_0_10 over the years using ggplotggplot(blackrock_etf_screener_w_new_features, aes(x = incept_date, y = msci_esg_quality_score_0_10)) +geom_line(color ="lightblue", size =1) +# Use a line plot to show the trend over timegeom_point(size =2, color ="lightblue", alpha =0.6) +# Add points to show individual scoresgeom_smooth(se =FALSE, color ="lightyellow")+labs(title ="Trend of MSCI ESG Quality Score Since 2010",x ="Inception Date",y ="MSCI ESG Quality Score (0-10)") +theme_minimal() +scale_x_date(date_breaks ="1 year", date_labels ="%Y") +# Set date breaks for x-axistheme(axis.text.x =element_text(angle =45, hjust =1)) # Rotate x-axis text for readability
Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
ℹ Please use `linewidth` instead.
`geom_smooth()` using method = 'loess' and formula = 'y ~ x'
Factoid:
Despite fluctuations, there is a visible trend line indicating a general stability in scores over the years, with slight variations. The data points are densely plotted, suggesting a substantial number of funds are evaluated each year, reflecting the growing importance of ESG considerations in the investment landscape. The consistent presence of scores above the midpoint suggests a reasonable level of ESG performance among the evaluated funds
