Description:Historical League Tables for JPY Issued Green Bonds

setwd("~/Desktop/SAIS/Academics/Fall 2022/Sustainable Finance/Final")

Libraries

library("lubridate") 

## Loading required package: timechange

## 
## Attaching package: 'lubridate'

## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union

library("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, union

library("readxl")
library("openxlsx")
library("forcats")

##Manual Way## #Load excel package

library(readxl)
JPY <- read_excel("~/Desktop/SAIS/Academics/Fall 2022/Sustainable Finance/Final/JPY.xlsx", 
    sheet = "Bonds", na = "#N/A N/A")

#“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 #Choose the row number of the rows containing the selected text

Daiwa_Count <- grep("DAIWA",JPY$`Roles`)

#Get the sum

Daiwa <- sum(JPY$Amt[Daiwa_Count]) 
Daiwa

## [1] 19020296099

#MU

MU_Count <- grep("MUMSS",JPY$`Roles`)

MUFG  <- sum(JPY$Amt[MU_Count]) 
MUFG

## [1] 22197745882

#MS

MS_Count <- grep("MS",JPY$`Roles`)

MS <- sum(JPY$Amt[MS_Count]) 
MS

## [1] 22793143343

#MUMS

MUMS <- MUFG + MS
MUMS

## [1] 44990889225

#MIZUHO

MIZUHO_Count <- grep("MIZUHO",JPY$`Roles`)

Mizuho  <- sum(JPY$Amt[MIZUHO_Count]) 
Mizuho

## [1] 25972351099

#BNPPAR

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

#BARCLY

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

#GS

GS_Count <- grep("GS",JPY$`Roles`)

Goldman_Sachs <- sum(JPY$Amt[GS_Count]) 
Goldman_Sachs

## [1] 1523415371

#BofA

BofAJ_Count <- grep("BofA",JPY$`Roles`)

BofAJ <- sum(JPY$Amt[BofAJ_Count]) 
BofAJ

## [1] 1959744630

#BOFAS

BOFAS_Count <- grep("BOFAS",JPY$`Roles`)

BofA_Securities <- sum(JPY$Amt[BOFAS_Count]) 
BofA_Securities

## [1] 92139600

#ML

ML_Count <- grep("ML",JPY$`Roles`)

Merill_Lynch  <- sum(JPY$Amt[ML_Count]) 
Merill_Lynch

## [1] 556075910

#BofA

BofA <- BofA_Securities + BofAJ + Merill_Lynch
BofA

## [1] 2607960140

#SMBC

SMBC_Count <- grep("SMBC",JPY$`Roles`)

SMBC <- sum(JPY$Amt[SMBC_Count]) 
SMBC

## [1] 17485387069

#SMBNIK

SMBNIK_Count <- grep("SMBNIK",JPY$`Roles`)

SMBNIK <- sum(JPY$Amt[SMBNIK_Count]) 
SMBNIK

## [1] 356997415

#NIKKO

NIKKO_Count <- grep("NIKKO",JPY$`Roles`)

NIKKO <- sum(JPY$Amt[NIKKO_Count]) 
NIKKO

## [1] 17293187992

#SMBCNIKKO

SMBCNIKKO<- SMBC + SMBNIK + NIKKO
SMBCNIKKO

## [1] 35135572476

#CRAGTK

CRAGTK_Count <- grep("CRAGTK",JPY$`Roles`)

Credit_Agricole <- sum(JPY$Amt[CRAGTK_Count]) 
Credit_Agricole

## [1] 182980988

#BOCHK

BOCHK_Count <- grep("BOCHK",JPY$`Roles`)

Bank_of_China <- sum(JPY$Amt[BOCHK_Count]) 
Bank_of_China

## [1] 264389400

Load ggplot2

library(ggplot2)

#“DAIWA”,“MUMS”,“Mizuho”,“Paribas”,“Nomura”,“Barclays”,“Citi”,“Goldman_Sachs”,“BofA”,“SMBCNIKKO”,“Credit_Agricole”,“Bank_of_China” # Create data

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) 
  )

Barplot

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   11

Chart banks in rank order

ggplot(data, aes(fct_reorder(Banks, -DealValue),DealValue)) + 
  geom_bar(stat = "identity")

data_top5 <- data %>%
  filter(`Rank` < 6)

Chart banks in rank order

ggplot(data_top5, aes(fct_reorder(Banks, -DealValue),DealValue)) + 
  geom_bar(stat = "identity")