Final Project Data Tidying

options(repos = c(CRAN = "https://cran.rstudio.com/"))
install.packages("readxl")

The downloaded binary packages are in
    /var/folders/25/scx22j0150v_7hw79jvjm8gh0000gn/T//RtmpEO7GiO/downloaded_packages

install.packages("here")

The downloaded binary packages are in
    /var/folders/25/scx22j0150v_7hw79jvjm8gh0000gn/T//RtmpEO7GiO/downloaded_packages

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

library(readxl)
library(janitor)

Attaching package: 'janitor'

The following objects are masked from 'package:stats':

    chisq.test, fisher.test

library(readxl)
library(writexl)

VIX

#load the data 
library(readxl)
VIXCLS <- read_excel("00_data_raw/VIXCLS.xls", 
   range = "A11:B60")
View(VIXCLS)

Fed Funds Effective Rates

#load the data 
library(readxl)
FEDFUNDS <- read_excel("00_data_raw/FEDFUNDS.xls", 
   range = "A11:B60")
View(FEDFUNDS)

Debt Monitor Data

#loading the data
#1. loading country coverage so I cant later create context about which countries are covered in the dataset 
Sustainable_debt_monitor <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "Country coverage", range = "A42:D183", 
    col_types = c("skip", "text", "text", 
        "text"))
View(Sustainable_debt_monitor)

#2. Global Markets Issuance Data, not well formatted so need to load each data chunk as its own dataset first and then later I will merge the data

#2.1 Total Sustainable debt and loans issuance 
 Sustainable_debt_loans_issuance <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
  sheet = "1a_global markets - issuance", 
  range = "B3:G48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

#2.2 Sustainable Bond Issuance  
Sustainable_bonds_issuance <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "I3:N48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(Sustainable_bonds_issuance)

#2.3 Sustainable loan issuance 
Sustainable_loan_issuance_ <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "P3:U48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(Sustainable_loan_issuance_)


# 3.Issuance by instrument type
#This data is also not well-formated so i need to load each data chunk as a dataset.Each of the data chunks are disaggregated acccouring to market. 

#3.1 Green Bonds  

green_bonds_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "W3:AB48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)




#3.2 Green Asset Back Securities (ABS)

green_abs_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "AD3:AI48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)

#3.4 Sustainability Bonds


sus_bonds_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "AK3:AP48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)


#3.5 Social Bonds 

social_bond_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "AR3:AW48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)


#3.6 Green municipal bonds 

greenmunicipality_bonds_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "AY3:BD48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)


#3.7 Sustainability-linked bonds 

suslinked_bonds_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "BF3:BK48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)


#3.8 Green loans 

green_loans_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "BM3:BR48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)




#3.9 Sustainability-linked loans 

green_loans_type <- read_excel("00_data_raw/Sustainable_debt_monitor.xlsx", 
    sheet = "1a_global markets - issuance", 
    range = "BT3:BY48")

New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`

View(green_bonds_type)


#Issuance by Sector