Final Project: Initial findings + Data EDA

Author

Anran Fang

Topic:

Focus on how may biotech companies’ approaches to sustainability affect their stock price. (Subject to edits.)

Data:

Currently referring to those in previous studies, including one that investigates impact of ESG index on stock return, one that researches on how does news affect biopharma stock prices, one that analyzes the impact of ESG on financial performances, and one that looks into US biopharmaceutical companies’ stock market reaction following a “paradoxical spiral”.

Hopefully these papers provide a scope of the companies that I want to look into, as well as their correlating sustainability data. Other financial data, if needed, would be cited from Yahoo Finance.

Data Analysis:

Does efforts on sustainability improve their stock price? Is the effect significant? Is the effect short-term or long-term? What are some potential mechanism behind this effect? What are some side effects?
Is the observed pattern specific to biotech companies, comparing to the other industries? Why? Will breaking biotech industry down to tech and pharmaceuticals company further clarifies or sharpens the relationship?
What insightful conclusions can be drawn for investors?

Audience:

Including companies targeting biotech investments, biotech companies and their consulting agents that target a growth in stock price (or financial performance), and those ESG auditing companies creating frameworks and measurement for biotech industry.

Data Tidying:

Current two variables are stock price and ESG rating: Stock price from May 2018 to Apr 2024 will be recorded from Yahoo Finance on a monthly basis, which is the stock close price on the first day of each month. ESG rating will adopt MSCI ESG rating from 2019 to 2023. Assume each rating accurately captures the company’s ESG level of compliance until the next rating is published. The rating is given on a scale of AAA, AA, A, BBB, BB, B, CCC; For the ease of further analysis, the elements are each assigned a number, with AAA labeled 7 and CCC labeled 1.

Ten mega healthcare industry companies are chosen, with majority included in S&P 500. The criteria of screening is not only the business size, but more importantly that their MSCI ESG rating is available for MSCI only rated a limited number of mega companies.

# Load essential packages.
library(DT)

# Import raw data.
esg_rating <- read.csv(file = "/Users/far/Desktop/Raw Data/MSCI ESG Rating.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(esg_rating, 3)

        Date AMGN BAX BIIB GILD LH LLY REGN TEVA VRTX VTRS
1 2018-05-01   NA  NA   NA   NA NA  NA   NA   NA   NA   NA
2 2018-06-01   NA  NA   NA   NA NA  NA   NA   NA   NA   NA
3 2018-07-01   NA  NA   NA   NA NA  NA   NA   NA   NA   NA

amgn <- read.csv(file = "/Users/far/Desktop/Raw Data/AMGN.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(amgn, 3)

        Date     Open     High      Low    Close Adj.Close   Volume
1 1985-01-01 0.098958 0.151042 0.096354 0.145833  0.104836 29952000
2 1985-02-01 0.145833 0.148438 0.114583 0.114583  0.082371 17145600
3 1985-03-01 0.114583 0.125000 0.101563 0.114583  0.082371 21811200

bax <- read.csv(file = "/Users/far/Desktop/Raw Data/BAX.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(bax, 3)

        Date     Open     High      Low    Close Adj.Close   Volume
1 1985-01-01 3.206678 3.823347 3.175845 3.730847  1.458992 51230489
2 1985-02-01 3.638347 3.730847 3.453346 3.576680  1.398702 37901214
3 1985-03-01 3.545846 4.162515 3.515013 3.885014  1.519280 35458250

biib <- read.csv(file = "/Users/far/Desktop/Raw Data/BIIB.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(biib, 3)

        Date     Open     High      Low    Close Adj.Close   Volume
1 1991-10-01 3.166667 3.541667 2.458333 3.083333  3.083333 14941800
2 1991-11-01 3.000000 3.125000 1.833333 2.000000  2.000000 17532600
3 1991-12-01 1.875000 2.500000 1.875000 2.166667  2.166667  6007200

gild <- read.csv(file = "/Users/far/Desktop/Raw Data/GILD.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(gild, 3)

        Date    Open     High      Low    Close Adj.Close   Volume
1 1992-02-01 0.65625 0.687500 0.476563 0.554688  0.408151 83308800
2 1992-03-01 0.56250 0.609375 0.453125 0.515625  0.379408 48476800
3 1992-04-01 0.50000 0.515625 0.324219 0.406250  0.298928 73248000

lh <- read.csv(file = "/Users/far/Desktop/Raw Data/LH.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(lh, 3)

        Date     Open     High      Low    Close Adj.Close Volume
1 1990-04-01 24.16237 27.38402 23.08849 23.89390  21.94713  26819
2 1990-05-01 23.89390 27.92096 23.62543 27.92096  25.64608  50286
3 1990-06-01 27.92096 32.21650 27.38402 29.53179  27.12567 112164

lly <- read.csv(file = "/Users/far/Desktop/Raw Data/LLY.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(lly, 3)

        Date     Open     High      Low    Close Adj.Close   Volume
1 1985-01-01 4.109375 4.468750 4.023438 4.460938  1.490623 38801600
2 1985-02-01 4.437500 4.828125 4.406250 4.828125  1.613320 54888000
3 1985-03-01 4.843750 4.968750 4.757813 4.859375  1.641629 68363200

regn <- read.csv(file = "/Users/far/Desktop/Raw Data/REGN.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(regn, 3)

        Date  Open   High   Low  Close Adj.Close  Volume
1 1991-05-01 12.75 14.125 10.25 12.250    12.250 3041000
2 1991-06-01 12.50 12.500 10.25 11.125    11.125 1258100
3 1991-07-01 10.75 12.000  9.75 11.500    11.500  830000

teva <- read.csv(file = "/Users/far/Desktop/Raw Data/TEVA.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(teva, 3)

        Date     Open     High      Low   Close Adj.Close  Volume
1 1985-01-01 0.089844 0.171875 0.089844 0.12500  0.083711 1139200
2 1985-02-01 0.000000 0.179688 0.132813 0.15625  0.104639  654400
3 1985-03-01 0.000000 0.179688 0.156250 0.15625  0.104639  758400

vrtx <- read.csv(file = "/Users/far/Desktop/Raw Data/VRTX.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(vrtx, 3)

        Date   Open   High    Low  Close Adj.Close  Volume
1 1991-08-01 4.5000 5.1875 4.1875 5.1875    5.1875 2159000
2 1991-09-01 5.1875 7.6250 4.8750 7.3750    7.3750 4106800
3 1991-10-01 7.3750 8.0000 6.6250 7.2500    7.2500 2788000

vtrs <- read.csv(file = "/Users/far/Desktop/Raw Data/VTRS.csv", header = TRUE, sep = ",", quote = "\"", dec = ".", fill = TRUE, comment.char = "")
head(vtrs, 3)

        Date Open     High      Low    Close Adj.Close   Volume
1 1985-01-01    0 4.962963 3.555556 4.629630  3.516282 22078575
2 1985-02-01    0 5.333333 2.351852 2.666667  2.025379 26139375
3 1985-03-01    0 3.129630 2.592593 3.055556  2.320746 21367800

# For stock price, choose only close price and desired time period.
start_date <- as.Date("2018-05-01")
end_date <- as.Date("2024-04-01")
date_filtered <- amgn[amgn$Date >= start_date & amgn$Date <= end_date, ]
amgn_new <- date_filtered[, c("Date", "Close")]
rownames(amgn_new) <- NULL
head(amgn_new, 3)

        Date  Close
1 2018-05-01 179.62
2 2018-06-01 184.59
3 2018-07-01 196.55

date_filtered <- bax[bax$Date >= start_date & bax$Date <= end_date, ]
bax_new <- date_filtered[, c("Date", "Close")]
rownames(bax_new) <- NULL
head(bax_new, 3)

        Date Close
1 2018-05-01 70.84
2 2018-06-01 73.84
3 2018-07-01 72.45

date_filtered <- biib[biib$Date >= start_date & biib$Date <= end_date, ]
biib_new <- date_filtered[, c("Date", "Close")]
rownames(biib_new) <- NULL
head(biib_new, 3)

        Date  Close
1 2018-05-01 293.96
2 2018-06-01 290.24
3 2018-07-01 334.37

date_filtered <- gild[gild$Date >= start_date & gild$Date <= end_date, ]
gild_new <- date_filtered[, c("Date", "Close")]
rownames(gild_new) <- NULL
head(gild_new, 3)

        Date Close
1 2018-05-01 67.40
2 2018-06-01 70.84
3 2018-07-01 77.83

date_filtered <- lh[lh$Date >= start_date & lh$Date <= end_date, ]
lh_new <- date_filtered[, c("Date", "Close")]
rownames(lh_new) <- NULL
head(lh_new, 3)

        Date    Close
1 2018-05-01 155.1460
2 2018-06-01 154.2354
3 2018-07-01 150.6357

date_filtered <- lly[lly$Date >= start_date & lly$Date <= end_date, ]
lly_new <- date_filtered[, c("Date", "Close")]
rownames(lly_new) <- NULL
head(lly_new, 3)

        Date Close
1 2018-05-01 85.04
2 2018-06-01 85.33
3 2018-07-01 98.81

date_filtered <- regn[regn$Date >= start_date & regn$Date <= end_date, ]
regn_new <- date_filtered[, c("Date", "Close")]
rownames(regn_new) <- NULL
head(regn_new, 3)

        Date  Close
1 2018-05-01 300.32
2 2018-06-01 344.99
3 2018-07-01 368.01

date_filtered <- teva[teva$Date >= start_date & teva$Date <= end_date, ]
teva_new <- date_filtered[, c("Date", "Close")]
rownames(teva_new) <- NULL
head(teva_new, 3)

        Date Close
1 2018-05-01 21.50
2 2018-06-01 24.32
3 2018-07-01 23.94

date_filtered <- vrtx[vrtx$Date >= start_date & vrtx$Date <= end_date, ]
vrtx_new <- date_filtered[, c("Date", "Close")]
rownames(vrtx_new) <- NULL
head(vrtx_new, 3)

        Date  Close
1 2018-05-01 154.00
2 2018-06-01 169.96
3 2018-07-01 175.05

date_filtered <- vtrs[vtrs$Date >= start_date & vtrs$Date <= end_date, ]
vtrs_new <- date_filtered[, c("Date", "Close")]
rownames(vtrs_new) <- NULL
head(vtrs_new, 3)

        Date Close
1 2018-05-01 38.46
2 2018-06-01 36.14
3 2018-07-01 37.31

# Add variables of Company, Industry, Category, and append ESG Rating to each dataframe.
amgn_new[["Company"]] <- "AMGN"
amgn_new[["Category"]] <- "Pharmaceuticals"
amgn_new[["ESG Rating"]] <- esg_rating$AMGN
names(amgn_new)[names(amgn_new) == "Close"] <- "Stock Price"
amgn_new <- amgn_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(amgn_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01    AMGN Pharmaceuticals      179.62         NA
2 2018-06-01    AMGN Pharmaceuticals      184.59         NA
3 2018-07-01    AMGN Pharmaceuticals      196.55         NA

bax_new[["Company"]] <- "BAX"
bax_new[["Category"]] <- "Biotechnology & Pharmaceuticals"
bax_new[["ESG Rating"]] <- esg_rating$BAX
names(bax_new)[names(bax_new) == "Close"] <- "Stock Price"
bax_new <- bax_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(bax_new, 3)

        Date Company                        Category Stock Price ESG Rating
1 2018-05-01     BAX Biotechnology & Pharmaceuticals       70.84         NA
2 2018-06-01     BAX Biotechnology & Pharmaceuticals       73.84         NA
3 2018-07-01     BAX Biotechnology & Pharmaceuticals       72.45         NA

biib_new[["Company"]] <- "BIIB"
biib_new[["Category"]] <- "Pharmaceuticals"
biib_new[["ESG Rating"]] <- esg_rating$BIIB
names(biib_new)[names(biib_new) == "Close"] <- "Stock Price"
biib_new <- biib_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(biib_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01    BIIB Pharmaceuticals      293.96         NA
2 2018-06-01    BIIB Pharmaceuticals      290.24         NA
3 2018-07-01    BIIB Pharmaceuticals      334.37         NA

gild_new[["Company"]] <- "GILD"
gild_new[["Category"]] <- "Pharmaceuticals"
gild_new[["ESG Rating"]] <- esg_rating$GILD
names(gild_new)[names(gild_new) == "Close"] <- "Stock Price"
gild_new <- gild_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(gild_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01    GILD Pharmaceuticals       67.40         NA
2 2018-06-01    GILD Pharmaceuticals       70.84         NA
3 2018-07-01    GILD Pharmaceuticals       77.83         NA

lh_new[["Company"]] <- "LH"
lh_new[["Category"]] <- "Biotechnology"
lh_new[["ESG Rating"]] <- esg_rating$LH
names(lh_new)[names(lh_new) == "Close"] <- "Stock Price"
lh_new <- lh_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(lh_new, 3)

        Date Company      Category Stock Price ESG Rating
1 2018-05-01      LH Biotechnology    155.1460         NA
2 2018-06-01      LH Biotechnology    154.2354         NA
3 2018-07-01      LH Biotechnology    150.6357         NA

lly_new[["Company"]] <- "LLY"
lly_new[["Category"]] <- "Pharmaceuticals"
lly_new[["ESG Rating"]] <- esg_rating$LLY
names(lly_new)[names(lly_new) == "Close"] <- "Stock Price"
lly_new <- lly_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(lly_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01     LLY Pharmaceuticals       85.04         NA
2 2018-06-01     LLY Pharmaceuticals       85.33         NA
3 2018-07-01     LLY Pharmaceuticals       98.81         NA

regn_new[["Company"]] <- "REGN"
regn_new[["Category"]] <- "Pharmaceuticals"
regn_new[["ESG Rating"]] <- esg_rating$REGN
names(regn_new)[names(regn_new) == "Close"] <- "Stock Price"
regn_new <- regn_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(regn_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01    REGN Pharmaceuticals      300.32         NA
2 2018-06-01    REGN Pharmaceuticals      344.99         NA
3 2018-07-01    REGN Pharmaceuticals      368.01         NA

teva_new[["Company"]] <- "TEVA"
teva_new[["Category"]] <- "Pharmaceuticals"
teva_new[["ESG Rating"]] <- esg_rating$TEVA
names(teva_new)[names(teva_new) == "Close"] <- "Stock Price"
teva_new <- teva_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(teva_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01    TEVA Pharmaceuticals       21.50         NA
2 2018-06-01    TEVA Pharmaceuticals       24.32         NA
3 2018-07-01    TEVA Pharmaceuticals       23.94         NA

vrtx_new[["Company"]] <- "VRTX"
vrtx_new[["Category"]] <- "Pharmaceuticals"
vrtx_new[["ESG Rating"]] <- esg_rating$VRTX
names(vrtx_new)[names(vrtx_new) == "Close"] <- "Stock Price"
vrtx_new <- vrtx_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(vrtx_new, 3)

        Date Company        Category Stock Price ESG Rating
1 2018-05-01    VRTX Pharmaceuticals      154.00         NA
2 2018-06-01    VRTX Pharmaceuticals      169.96         NA
3 2018-07-01    VRTX Pharmaceuticals      175.05         NA

vtrs_new[["Company"]] <- "VTRS"
vtrs_new[["Category"]] <- "Biotechnology & Pharmaceuticals"
vtrs_new[["ESG Rating"]] <- esg_rating$VTRS
names(vtrs_new)[names(vtrs_new) == "Close"] <- "Stock Price"
vtrs_new <- vtrs_new[, c("Date", "Company", "Category", "Stock Price", "ESG Rating")]
head(vtrs_new, 3)

        Date Company                        Category Stock Price ESG Rating
1 2018-05-01    VTRS Biotechnology & Pharmaceuticals       38.46         NA
2 2018-06-01    VTRS Biotechnology & Pharmaceuticals       36.14         NA
3 2018-07-01    VTRS Biotechnology & Pharmaceuticals       37.31         NA

# Combine these into one table with ascending dates.
clean <- rbind(amgn_new, bax_new, biib_new, gild_new, lh_new, lly_new, regn_new, teva_new, vrtx_new, vtrs_new)
clean <- clean[order(as.Date(clean$Date)), ]
rownames(clean) <- NULL
datatable(clean, options = list(pageLength = 15))

# Save the combined tidied dataframe into csv.
write.csv(clean, "Clean Data.csv", row.names = FALSE)