League Tables for All JPY Issued Green Bonds
Japanese Yen denominated green bonds started when the International Bank for Reconstruction & Development(The World Bank) issued the first ever yen-denominated green bond. In 2008, the first green bonds were issued by The World Bank. Ever since then, the Japanese Yen has seen issuances of JPY 31Bn, equal to more than USD 310 Mn. Japanese banks have all issued statements supporting their underwriting of green bonds, but which banks have really stepped up historically? Banks will show league tables in favorable ways in their marketing materials, and it is hard to know which banks are truly the “green” bank. With the analysis of Bloomberg data, investors and corporates looking to buy or sell their ESG-related financial products, will know how to rank banks in their expertise.
The Analysis:
Data: Use Bloomberg bond issuance data for green instruments and corporate bonds. The data will be need to be cleaned, as Bloomberg data has all bookrunner/underwriting information under one tab in “Lead Manager(s)”. Date formats will also need to be converted from the excel format to R format. Two datasets, one for green issuances, and one for bond issuances of a recent year will need to be combined in data analyses after the league tables are created independently of each other.
Procedure: Look at total deals committed to buy the banks, in terms of deal value and deal count. Also break this down year by year, to see historical trends and new players in the field. Also, will compare this to the league tables of general corporate bonds in a recent year to see if there are over performers and underperformers compared to general corporate underwriting.
Set Woking Directory
setwd("~/Desktop/SAIS/Academics/Fall 2022/Sustainable Finance/Final")Library
library("lubridate") ## Loading required package: timechange##
## Attaching package: 'lubridate'## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, unionlibrary("dplyr") ##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary("readxl")
library("openxlsx")
library("forcats")
library("ggplot2")Load Excel Data
library(readxl)
JPY <- read_excel("~/Desktop/SAIS/Academics/Fall 2022/Sustainable Finance/Final/JPY.xlsx",
sheet = "Bonds", na = "#N/A N/A")
glimpse(JPY)## Rows: 333
## Columns: 15
## $ Issuer <chr> "Japan Housing Finance Agency", "Japan Railway Construction T…
## $ Ticker <chr> "HLC", "JRCTTA", "HLC", "HLC", "HLC", "KANAP", "MIEPRE", "TOK…
## $ Green <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "…
## $ Issued <chr> "7/16/2021", "2/27/2018", "7/17/2019", "6/18/2021", "9/27/202…
## $ Maturity <chr> "7/18/2036", "2/26/2038", "7/20/2039", "6/18/2026", "9/25/203…
## $ YTM <dbl> 2.1428571, 2.7142857, 2.8571429, 0.7142857, 2.0000000, 0.5714…
## $ Currency <chr> "JPY", "JPY", "JPY", "JPY", "JPY", "JPY", "JPY", "JPY", "JPY"…
## $ Amt <dbl> 90822800, 228153065, 92514200, 907183000, 138107200, 74009540…
## $ Cpn <dbl> 0.00294, 0.00630, 0.00273, 0.00001, 0.00667, 0.00200, 0.00269…
## $ Series <chr> "323", "113", "269", "1", "9", "3", "3-2", "4", "331", "4", "…
## $ MtyType <chr> "AT MATURITY", "AT MATURITY", "AT MATURITY", "AT MATURITY", "…
## $ Moody <chr> NA, "A1", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ Fitch <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…
## $ `S&P` <chr> "A+", NA, "A+", NA, NA, NA, NA, "A+", NA, "A+", "A+", NA, "A+…
## $ Roles <chr> "Bookrunner Daiwa Securities Co Ltd DAIWA JLMB Joint Lead Man…#The banks in the issuances and their abbreviations “DAIWA”, “MUMSS”, “MIZUHO”, “BNPPAR”, “ML”, “NOMURA”, “BARCLY”,“CITI”, “NIKKO”, “GS”, “BOFAS”, “MIZ”, “BofA”, “MS”, “SMBC”, “SMBNIK”, “CRAGTK”, “BOCHK” “Daiwa”,“MUMS”,“Mizuho”,“Paribas”,“Nomura”,“Barclays”,“Citi”,“Goldman_Sachs”,“BofA”,“SMBCNIKKO”,“Credit_Agricole”,“Bank_of_China”
#Daiwa Securities Choose the row number of the rows containing the selected text, repeat for other banks.
Daiwa_Count <- grep("DAIWA",JPY$`Roles`)Get the sum
Daiwa <- sum(JPY$Amt[Daiwa_Count])
Daiwa## [1] 19020296099#Mitsubishi UFJ Financial Group
MU_Count <- grep("MUMSS",JPY$`Roles`)
MUFG <- sum(JPY$Amt[MU_Count])
MUFG## [1] 22197745882#Morgan Stanley
MS_Count <- grep("MS",JPY$`Roles`)
MS <- sum(JPY$Amt[MS_Count])
MS## [1] 22793143343#Mitsubishi UFJ Morgan Stanley
MUMS <- MUFG + MS
MUMS## [1] 44990889225#Mizuho
MIZUHO_Count <- grep("MIZUHO",JPY$`Roles`)
Mizuho <- sum(JPY$Amt[MIZUHO_Count])
Mizuho## [1] 25972351099#BNP Paribas
BNPPAR_Count <- grep("BNPPAR",JPY$`Roles`)
Paribas <- sum(JPY$Amt[BNPPAR_Count])
Paribas## [1] 535055030#Nomura
NOMURA_Count <- grep("NOMURA",JPY$`Roles`)
Nomura <- sum(JPY$Amt[NOMURA_Count])
Nomura## [1] 21551722634#Barclays
BARCLY_Count <- grep("BARCLY",JPY$`Roles`)
Barclays <- sum(JPY$Amt[BARCLY_Count])
Barclays## [1] 352002800#Citi
CITI_Count <- grep("CITI",JPY$`Roles`)
Citi <- sum(JPY$Amt[CITI_Count])
Citi## [1] 331630234#Goldman Sachs
GS_Count <- grep("GS",JPY$`Roles`)
Goldman_Sachs <- sum(JPY$Amt[GS_Count])
Goldman_Sachs## [1] 1523415371#BofA Japan
BofAJ_Count <- grep("BofA",JPY$`Roles`)
BofAJ <- sum(JPY$Amt[BofAJ_Count])
BofAJ## [1] 1959744630#BofA Securities
BOFAS_Count <- grep("BOFAS",JPY$`Roles`)
BofA_Securities <- sum(JPY$Amt[BOFAS_Count])
BofA_Securities## [1] 92139600#Merill Lynch
ML_Count <- grep("ML",JPY$`Roles`)
Merill_Lynch <- sum(JPY$Amt[ML_Count])
Merill_Lynch## [1] 556075910#Bank of America (BofA Japan + BofA Securities + Merill Lynch Combined)
BofA <- BofA_Securities + BofAJ + Merill_Lynch
BofA## [1] 2607960140#Sumitomo Mitsui Banking Corporation
SMBC_Count <- grep("SMBC",JPY$`Roles`)
SMBC <- sum(JPY$Amt[SMBC_Count])
SMBC## [1] 17485387069#SMBC Nikko Securities
SMBNIK_Count <- grep("SMBNIK",JPY$`Roles`)
SMBNIK <- sum(JPY$Amt[SMBNIK_Count])
SMBNIK## [1] 356997415#Nikko Securities
NIKKO_Count <- grep("NIKKO",JPY$`Roles`)
NIKKO <- sum(JPY$Amt[NIKKO_Count])
NIKKO## [1] 17293187992#SMBC Nikko Securities(SMBC + SMBC Nikko + Nikko combined)
SMBCNIKKO<- SMBC + SMBNIK + NIKKO
SMBCNIKKO## [1] 35135572476#Credit Agricole
CRAGTK_Count <- grep("CRAGTK",JPY$`Roles`)
Credit_Agricole <- sum(JPY$Amt[CRAGTK_Count])
Credit_Agricole## [1] 182980988#Bank of China Hong Kong
BOCHK_Count <- grep("BOCHK",JPY$`Roles`)
Bank_of_China <- sum(JPY$Amt[BOCHK_Count])
Bank_of_China## [1] 264389400Load ggplot2
library(ggplot2)Create data “DAIWA”,“MUMS”,“Mizuho”,“Paribas”,“Nomura”,“Barclays”,“Citi”,“Goldman_Sachs”,“BofA”,“SMBCNIKKO”,“Credit_Agricole”,“Bank_of_China”
data <- data.frame(Banks=c("Daiwa","MUMS","Mizuho","Paribas","Nomura","Barclays","Citi","Goldman_Sachs","BofA","SMBCNIKKO","Credit_Agricole","Bank_of_China") , DealValue=c(Daiwa,MUMS,Mizuho,Paribas,Nomura,Barclays,Citi,Goldman_Sachs,BofA,SMBCNIKKO,Credit_Agricole,Bank_of_China)
)
sum(data$Amt)## [1] 0Bar Plot
ggplot(data, aes(x=Banks, y=DealValue)) +
geom_bar(stat = "identity")
Rank Banks by Deal Value
data$Rank<-rank(-data$DealValue)
data## Banks DealValue Rank
## 1 Daiwa 19020296099 5
## 2 MUMS 44990889225 1
## 3 Mizuho 25972351099 3
## 4 Paribas 535055030 8
## 5 Nomura 21551722634 4
## 6 Barclays 352002800 9
## 7 Citi 331630234 10
## 8 Goldman_Sachs 1523415371 7
## 9 BofA 2607960140 6
## 10 SMBCNIKKO 35135572476 2
## 11 Credit_Agricole 182980988 12
## 12 Bank_of_China 264389400 11Chart Banks in Order of Deal Value
ggplot(data, aes(fct_reorder(Banks, -DealValue),DealValue)) +
geom_bar(stat = "identity")
data_top5 <- data %>%
filter(`Rank` < 6)Chart Top 5 Banks in Order of Deal Value
ggplot(data_top5, aes(fct_reorder(Banks, -DealValue),DealValue)) +
geom_bar(stat = "identity")
#Results We can observe that Mitsubishi UFJ Morgan Stanley has the advantage, due to the partnership between Mitsubishi UFJ, Japan’s #1 retail and commercial bank, and Morgan Stanley, a top American investment bank. I would like to further observe results and show the makeup of the Mitsubishi UFJ Morgan Stanley to show which bank contributed which portion.
#To Do for Final Draft: Clean date data to observe historical trends and historical league tables Compare data to recent league table of financial year Make stacked bar graph for Mitsubishi UFJ Morgan Stanley due to its unique partnership structure Add color and make colorful (Wes Anderson?)