R Notebook

Cleaning Process for Final Project

I am starting with data downloaded from Bloomberg Terminal on volumn, AUM, and fund flow of ESG ETFs and Energy related thematic ETFs like clean energy ETF and EV ETF.

I dowloaded 6 separate excel files from Bloomberg and plan to clean them respectively. I have already cleaned the first 2 data sets below using different methods we learned in class.

1. ESG ETF $ Volumn Cleaning Process

# Load required libraries
library(readxl)
library(dplyr)
library(tidyr)

# Import data from Excel file
data <- read_excel("/Users/hahazhou/Desktop/Final Project/00_data_raw/Volume_ESG.xlsx", sheet = "BIData")

# Transform second column to first 4 letters
data$Ticker <- substr(data$Ticker, 1, 4)

# Transform to long format
Volume_ESG_Cleaned <- data %>%
  pivot_longer(cols = starts_with("2"), names_to = "Year", values_to = "Volume") %>%
  mutate(Year = gsub("X", "", Year)) %>%
  select(Description, Ticker, Year, Volume)
# Save the cleaned data to the specified location
write.csv(Volume_ESG_Cleaned, "/Users/hahazhou/Desktop/Final Project/03_data_processed/Volume_ESG_Cleaned.csv")

2. Thematic ETF $ Volumn Cleaning Process

# Import required libraries
library(readxl)
library(dplyr)
library(tidyr)

# Read data from Excel file
data1 <- read_excel("/Users/hahazhou/Desktop/Final Project/00_data_raw/Volume_Thematic ETF.xlsx", 
                    sheet = "BIData", range = "A2:AA34")
data2 <- read_excel("/Users/hahazhou/Desktop/Final Project/00_data_raw/Volume_Thematic ETF.xlsx", 
                    sheet = "BIData", range = "A35:AA51")
data3 <- read_excel("/Users/hahazhou/Desktop/Final Project/00_data_raw/Volume_Thematic ETF.xlsx", 
                    sheet = "BIData", range = "A52:AA61")

# Transform data
Thematic_Clean_Energy <- data1 %>%
  mutate(Ticker = substr(Ticker, 1, 4)) %>%
  pivot_longer(cols = -c(Description, Ticker), names_to = "Year", values_to = "Volume")

Thematic_Natural_Resources <- data2 %>%
  mutate(Ticker = substr(Ticker, 1, 4)) %>%
  pivot_longer(cols = -c(Description, Ticker), names_to = "Year", values_to = "Volume")

Thematic_Electric_Car_Battery <- data3 %>%
  mutate(Ticker = substr(Ticker, 1, 4)) %>%
  pivot_longer(cols = -c(Description, Ticker), names_to = "Year", values_to = "Volume")

# Combine datasets
Volume_Thematic_Cleaned <- bind_rows(Thematic_Clean_Energy, Thematic_Natural_Resources, Thematic_Electric_Car_Battery)

# Save the cleaned data 
write.csv(Volume_Thematic_Cleaned, "/Users/hahazhou/Desktop/Final Project/03_data_processed/Volume_Thematic_Cleaned.csv")