2024-04-15

library(tidyverse) library(readxl) library(lubridate) library(writexl)

Data Analysis

Clear environment

rm(list = ls())

Set seed for reproducibility

set.seed(1601190)

Set directory

directory <- “C:/Users/Trinity/Downloads/R project/”

Read data

My_stocks <- read_xlsx(paste0(directory, “Assignment data.xlsx”), sheet = “Flat Returns”) %>% distinct(Ticker) %>% slice_sample(n = 20, replace = FALSE) %>% unlist() %>% unname()

Ratios_df <- read_xlsx(paste0(directory, “Assignment data.xlsx”), sheet = “Flat Ratios”) %>% select(c(1:5, all_of(toupper(My_stocks)))) %>% mutate(across(c(1, 3), .fns = function(x){as.Date(x, origin = “1899-12-30”)}))

Returns_df <- read_xlsx(paste0(directory, “Assignment data.xlsx”), sheet = “Flat Returns”) %>% filter(Ticker %in% My_stocks)

Returns_df\(Ticker <- toupper(Returns_df\)Ticker)

##Importing and modifying data

Read the excel file

excel_file <- “C:/Users/Trinity/Downloads/R project/Assignment data.xlsx”
excel_sheets <- excel_sheets(excel_file) sheet_name <- “Flat Returns” # Sheet name data <- read_excel(excel_file, sheet = sheet_name)

Create pivot table

pivoted_df <- pivot_wider(data = data, names_from = “Ticker”, values_from = “Daily Return”)

Store data in output Excel file

write_xlsx(pivoted_df, path = “output_data.xlsx”)

Create covariance matrices

pivoted_df_1 <- pivoted_df[, ! names(pivoted_df) %in% c(‘Date’)] matrix_1 <- pivoted_df_1[1:80,] matrix_2 <- pivoted_df_1[81:160,] matrix_3 <- pivoted_df_1[161:240,] matrix_4 <- pivoted_df_1[241:320,]

Creating Correlation Matrices

Create correlation matrices

Correlation_matrix_1 <- cor(matrix_1) Correlation_matrix_2 <- cor(matrix_2) Correlation_matrix_3 <- cor(matrix_3) Correlation_matrix_4 <- cor(matrix_4)

Graphically Appealing Matrix

Correlation for selected 10 stocks from Correlation_matrix_4

matrix_10 <- Correlation_matrix_4[1:10, 1:10] corr_matrix_10 <- cor(matrix_10) rownames(corr_matrix_10) <- toupper(rownames(corr_matrix_10)) rownames(corr_matrix_10) <- colnames(corr_matrix_10) rounded_corr_matrix <- round(corr_matrix_10, digits = 1)

Generate a lighter palette

col <- colorRampPalette(c(“#BB4444”, “#EE9988”, “#FFFFFF”, “#77AADD”, “#4477AA”))

Plot the correlation matrix

corrplot(rounded_corr_matrix, method = “shade”, shade.col = NA, tl.col = “blue”, tl.srt = 40, col = col(2000), addCoef.col = “black”, cl.pos = “n”, order = “AOE”, tl.cex = 0.6)

Price Multiple Relative Valuation

Stock selection criteria:

Above Average number of analyst coverage

forward P/E must be less than average 7-year P/E

Return stocks below threshold correlation

average_analyst_coverage <- round(mean(as.numeric(Ratios_df[17, 6:25]))) columns_to_select <- colnames(Ratios_df[, 6:25])[which(as.numeric(Ratios_df[17, 6:25]) >= average_analyst_coverage)] updated_dataframe <- Ratios_df[, columns_to_select]

Select columns with z score below zero and match with lower P/E ratios

columns_below_zero_z_score <- colnames(updated_dataframe)[which(as.numeric(updated_dataframe[19,]) < 0)] dataframe_below_average <- updated_dataframe[19, columns_below_zero_z_score]

Match with the list containing the negatively correlated stocks

ticker_pairs <- combn(columns_below_zero_z_score, 2) ticker_pairs <- t(ticker_pairs) ticker_pairs_df <- as.data.frame(ticker_pairs) colnames(ticker_pairs_df) <- c(“row_name”, “column_name”) merged_df <- merge(ticker_pairs_df, correlations_dataframe, by = c(“row_name”, “column_name”), all.x = TRUE)

##Final stocks Selected

View(merged_df)