# Import / Load the necessary librarys
library(openxlsx)
library(readxl)
# Reading the Data from the Sheet (Excel)
fx_data <- read.xlsx("Foreign_Exchange_Rates.xlsx", sheet = "Rates")
# Replacement of all "ND" values
# Using the values from the previous rows to do so
for (col_index in 1:ncol(fx_data)) {
for (row_index in 1:nrow(fx_data)) {
if (fx_data[row_index, col_index] == "ND") {
fx_data[row_index, col_index] <- fx_data[row_index - 1, col_index]
}
}
}
# Saving the cleaned data under another Excel file
write.xlsx(fx_data, "Cleaned_Foreign_Exchange_Rates.xlsx", sheetName = "Rates")
# Convert the 'Time.Serie' column to a Date format
fx_data$Time.Serie <- as.Date(fx_data$Time.Serie)
# Convert the 'EURO.AREA.-.EURO/US$' column to a numeric format
fx_data$`EURO.AREA.-.EURO/US$` <- as.numeric(as.character(fx_data$`EURO.AREA.-.EURO/US$`))
# Assigning each currency's rates to individual variables (Named after their currency codes (EUR,GBP,AUD,CAD,JPY))
EUR <- c(fx_data$`EURO.AREA.-.EURO/US$`)
GBP <- c(fx_data$`UNITED.KINGDOM.-.UNITED.KINGDOM.POUND/US$`)
AUD <- c(fx_data$`AUSTRALIA.-.AUSTRALIAN.DOLLAR/US$`)
SGD <- c(fx_data$`SINGAPORE.-.SINGAPORE.DOLLAR/US$`)
JPY <- c(fx_data$`JAPAN.-.YEN/US$`)
#All exchange rates: Each plotted in a separate table
par(mfrow = c(2, 3))
plot(EUR , type = "l",col = "gold", xlab = "time", ylab = "Exchange Rate to USD", main = "Euro")
plot(GBP , type = "l",col = "green", xlab = "time", ylab = "Exchange to USD", main = "Great British Pound")
plot(AUD , type = "l", col = "darkblue", xlab = "time", ylab = "Exchange Rate to USD", main = "Australian Dollar")
plot(SGD , type = "l", col = "red", xlab = "time", ylab = "Exchange Rate to USD", main = "Singapore Dollar")
plot(JPY , type = "l", col = "purple", xlab = "time", ylab = "Exchange Rate to USD", main = "Japanese Yen")
par(mfrow = c(1, 1))
# All exchange rates in a single table: Leaving out JPY (Due to Visibility)
plot(EUR , type = "l",col = "gold", xlab = "Time", xlim = c(2000, 2024), ylab = "Exchange Rates to USD", ylim = c(0.4, 1.6), main = "Exchange Rates over time")
lines(GBP , type = "l",col = "green")
lines(AUD , type = "l",col = "darkblue")
lines(SGD , type = "l",col = "red")
# Load required packages or install (if necessary)
if (!require("corrplot")) {install.packages("corrplot"); library("corrplot")}
## Lade nötiges Paket: corrplot
## corrplot 0.92 loaded
#Converting exchange rate data to numeric format(Enabling mathematical operations and analysis)
EUR <- as.numeric(EUR)
GBP <- as.numeric(GBP)
AUD <- as.numeric(AUD)
SGD <- as.numeric(SGD)
JPY <- as.numeric(JPY)
#Calculating the discrete returns for each currency: Taking the difference between consecutive exchange rate values and dividing by the previous value
#Effectively computing the percentage change from one period to the next.
# R[t]=P[t]/P[t-1]-1
Return_EUR <- diff(EUR) / EUR[-length(EUR)]
Return_GBP <- diff(GBP) / GBP[-length(GBP)]
Return_AUD <- diff(AUD) / AUD[-length(AUD)]
Return_SGD <- diff(SGD) / SGD[-length(SGD)]
Return_JPY <- diff(JPY) / JPY[-length(JPY)]
# Assuming all return vectors have the same length
returns <- cbind(Return_EUR, Return_GBP, Return_AUD, Return_SGD, Return_JPY)
colnames(returns) <- c("EUR", "GBP", "AUD", "SGD", "JPY")
# Calculate correlation matrix
cor_matrix <- cor(returns, use = "complete.obs")
# Plot correlation matrix
corrplot(cor_matrix, method = "color")
# Creating the Return Histograms
par(mfrow = c(2, 2))
hist(Return_EUR, breaks = 30, col = "gold", xlab = "Return", ylab = "Frequency", main = "Histogram: EUR Returns")
hist(Return_GBP, breaks = 30, col = "green", xlab = "Return", ylab = "Frequency", main = "Histogram: GBP Returns")
hist(Return_AUD, breaks = 30, col = "darkblue", xlab = "Return", ylab = "Frequency", main = "Histogram: AUD Returns")
hist(Return_SGD, breaks = 30, col = "red", xlab = "Return", ylab = "Frequency", main = "Histogram: SGD Returns")
hist(Return_JPY, breaks = 30, col = "purple", xlab = "Return", ylab = "Frequency", main = "Histogram: JPY Returns")
# Plotting all Return Rates in single tables
par(mfrow = c(2, 2))
plot(Return_EUR,type = "l",col = "gold", xlab = "time", xlim = c(2000, 2024), ylab = "Exchange Rates to USD", ylim = c(-0.05, 0.05), main = "Euro Return")
plot(Return_GBP , type = "l",col = "green", xlab = "time", xlim = c(2000, 2024), ylab = "Exchange Rates to USD", ylim = c(-0.05, 0.05), main = "Great Britain Pound Return")
plot(Return_AUD , type = "l", col = "darkblue", xlab = "time", xlim = c(2000, 2024), ylab = "Exchange Rates to USD", ylim = c(-0.05, 0.05), main = "Australian Dollar Return")
plot(Return_SGD , type = "l", col = "red", xlab = "time", xlim = c(2000, 2024), ylab = "Exchange Rates to USD", ylim = c(-0.05, 0.05), main = "Singapore Dollar Return")
plot(Return_JPY , type = "l", col = "purple", xlab = "time", xlim = c(2000, 2024), ylab = "Exchange Rates to USD", ylim = c(-0.05, 0.05), main = "Japanese Yen Return")
par(mfrow = c(1, 1))
# Loading the required "moments" package (if necessary)
if (!require("moments")) {install.packages("moments"); library("moments")}
## Lade nötiges Paket: moments
# List of all the currencies
currencies <- c("EUR", "GBP", "AUD", "SGD", "JPY")
# Function: Calculating the measures for each currency
calculate_measures <- function(currency) {
returns <- get(paste0("Return_", currency))
# Calculation: Return p.a. (250 trading days per year)
mean_return_pa <- mean(returns) * 250
# Calculation: Historical volatility
sd_pa <- sd(returns) * sqrt(250)
# Calculation: Skewness
skew <- skewness(returns)
# Kurtosis
kurt <- kurtosis(returns)
# Sharpe ratio with 4.25% risk-free rate
sharpe_ratio <- (mean_return_pa - 0.0425) / sd_pa
# Calculation: Value-at-Risk (1 year, 3 Months, 10 Days // 95% Confidence)
var_1year <- mean_return_pa + qnorm(0.05) * sd_pa
var_3months <- mean_return_pa + qnorm(0.05) * sd_pa * sqrt(60)
var_10days <- mean_return_pa + qnorm(0.05) * sd_pa * sqrt(10)
return(c(mean_return_pa, sd_pa, skew, kurt, sharpe_ratio, var_1year, var_3months, var_10days))
}
# Calculating the relevant measures for every currency
results <- t(sapply(currencies, calculate_measures))
# Reviewing the dimensions of the results matrix to ensure there were no calculation mistakes
print(dim(results))
## [1] 5 8
#Generating a data frame for the results
results_df <- data.frame(
Mean_Return_pa = results[,1],
Volatility_pa = results[,2],
Skewness = results[,3],
Kurtosis = results[,4],
Sharpe_Ratio = results[,5],
VaR_1year = results[,6],
VaR_3months = results[,7],
VaR_10days = results[,8]
)
# Display the results
print(results_df)
## Mean_Return_pa Volatility_pa Skewness Kurtosis Sharpe_Ratio VaR_1year
## EUR -0.0003710667 0.09420434 -0.04721343 5.562962 -0.4550859 -0.15532342
## GBP 0.0140379613 0.09251702 0.85736170 16.114097 -0.3076411 -0.13813900
## AUD 0.0045870609 0.12414954 0.80673282 16.560690 -0.3053812 -0.19962077
## SGD -0.0087018845 0.05081241 0.03338141 8.431664 -1.0076649 -0.09228086
## JPY 0.0078775149 0.09690920 -0.26413005 7.353733 -0.3572673 -0.15152394
## VaR_3months VaR_10days
## EUR -1.2006268 -0.4903734
## GBP -1.1647197 -0.4671878
## AUD -1.5772000 -0.6411748
## SGD -0.6561019 -0.2730018
## JPY -1.2268408 -0.4961941