## Question 2
# URL of the CSV file
url <- "https://s3.us-east-2.amazonaws.com/artificium.us/datasets/financial_portfolio_data.csv"
# Read the CSV file into a data frame
df <- read.csv(url, stringsAsFactors = FALSE)
# Show the first five rows of df
head(df, 5)
## Question 3
# Convert Date column from chr to Date
df$Date <- as.Date(df$Date)
# Show the updated first five rows of df
head(df, 5)
## Question 4
# Extract rows with Bond B
bond_b_rows <- which(df$Asset == "Bond B")
df.B <- df[bond_b_rows, ]
# Sort the subset by Date in ascending order
df.B <- df.B[order(df.B$Date), ]
# Show the least recent data
head (df.B, 5)
## Question 5
# Add a new Month column to df.B
df.B$Month <- as.numeric(format(df.B$Date, "%m"))
# Show numeric Month column in df.B
head(df.B, 5)
## Question 6
# Calculate the monthly average price of Bond B transactions
df.Monthly <- sqldf("SELECT Month, AVG(Price) AS `AvgPrice`
FROM `df.B`
GROUP BY Month
ORDER BY Month")
# Show averaged Bond B prices per month in a table
head(df.Monthly, 5)
## Question 7
# Create a vector for the months (1-12)
months_numeric <- 1:12
# Create a vector for the average prices
avg_prices <- df.Monthly$AvgPrice
# Plot the monthly average price with month labels
plot(months_numeric, avg_prices,
type = "o",
col = "darkorange",
xlab = "Month (1-12)",
ylab = "Average Price ($)",
main = "Monthly Average Price of Bond B",
pch = 19)
# Set x-axis labels
axis(1, at = months_numeric, labels = months_numeric)
## Question 8
# Define the weights for the WMA
weights <- c(0.2, 0.3, 0.5)
# Calculate the Weighted Moving Average
wma <- TTR::WMA(avg_prices, n = length(weights), wts = weights)
# Forecast using the last value of WMA
forecast.val <- tail(wma, 1)
# Show the forecasted value for the next month
print(forecast.val)
## [1] 282.1885
## Question 9
# Define the weights for the WMA (3-period example)
w <- c(0.2, 0.3, 0.5)
# Calculate forecast for each prior time period
df.Monthly$WMA <- TTR::WMA(avg_prices,
n = 3,
weights = w)
periods <- nrow(df.Monthly)
# Calculate the absolute errors
for (t in 3:periods) {
df.Monthly$abs.err[t] <- abs(df.Monthly$WMA[t-1] - df.Monthly$WMA[t])
}
# Calculate mean of absolute errors (MAE)
MAE.WMA <- mean(df.Monthly$abs.err, na.rm = TRUE)
# Show MAE and forecasts with absolute error
print(MAE.WMA)
## [1] 22.78582
print(df.Monthly)
## Month AvgPrice WMA abs.err
## 1 1 297.4357 NA NA
## 2 2 317.4233 NA NA
## 3 3 266.3863 288.5735 NA
## 4 4 269.3782 276.3884 12.185125
## 5 5 326.7325 297.5567 21.168289
## 6 6 375.9030 341.7587 44.202011
## 7 7 226.1575 292.8352 48.923530
## 8 8 280.6593 278.3660 14.469190
## 9 9 311.6131 287.0526 8.686574
## 10 10 255.4080 278.3516 8.700977
## 11 11 335.8140 304.9785 26.626940
## 12 12 260.7255 284.8687 20.109786
## Question 10
# Assign quarters based on the month
# Assumed that the instructions were incorrectly categorizing months into trimesters instead of quarters
df.Monthly$Quarter <- ifelse(df.Monthly$Month %in% 1:3, "Q1",
ifelse(df.Monthly$Month %in% 4:6, "Q2",
ifelse(df.Monthly$Month %in% 7:9, "Q3", "Q4")))
# Dummy-coded columns
df.Monthly$Q1 <- ifelse(df.Monthly$Quarter == "Q1", 1, 0)
df.Monthly$Q2 <- ifelse(df.Monthly$Quarter == "Q2", 1, 0)
df.Monthly$Q3 <- ifelse(df.Monthly$Quarter == "Q3", 1, 0)
# Q4 is not any of the previous three quarters
# Additive seasonality, adjusted linear regression trend forecasting model
trend.model.adjusted <- lm(
df.Monthly$AvgPrice ~ df.Monthly$Month +
df.Monthly$Q1 +
df.Monthly$Q2 +
df.Monthly$Q3
)
# Print the trend model sumamry
summary(trend.model.adjusted)
##
## Call:
## lm(formula = df.Monthly$AvgPrice ~ df.Monthly$Month + df.Monthly$Q1 +
## df.Monthly$Q2 + df.Monthly$Q3)
##
## Residuals:
## Min 1Q Median 3Q Max
## -48.143 -27.865 5.289 23.873 51.832
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 55.39 158.25 0.350 0.737
## df.Monthly$Month 20.78 14.23 1.460 0.188
## df.Monthly$Q1 196.80 132.22 1.488 0.180
## df.Monthly$Q2 164.71 91.49 1.800 0.115
## df.Monthly$Q3 51.17 53.88 0.950 0.374
##
## Residual standard error: 40.25 on 7 degrees of freedom
## Multiple R-squared: 0.4076, Adjusted R-squared: 0.06906
## F-statistic: 1.204 on 4 and 7 DF, p-value: 0.3883
# Print the updated data frame
print(df.Monthly)
## Month AvgPrice WMA abs.err Quarter Q1 Q2 Q3
## 1 1 297.4357 NA NA Q1 1 0 0
## 2 2 317.4233 NA NA Q1 1 0 0
## 3 3 266.3863 288.5735 NA Q1 1 0 0
## 4 4 269.3782 276.3884 12.185125 Q2 0 1 0
## 5 5 326.7325 297.5567 21.168289 Q2 0 1 0
## 6 6 375.9030 341.7587 44.202011 Q2 0 1 0
## 7 7 226.1575 292.8352 48.923530 Q3 0 0 1
## 8 8 280.6593 278.3660 14.469190 Q3 0 0 1
## 9 9 311.6131 287.0526 8.686574 Q3 0 0 1
## 10 10 255.4080 278.3516 8.700977 Q4 0 0 0
## 11 11 335.8140 304.9785 26.626940 Q4 0 0 0
## 12 12 260.7255 284.8687 20.109786 Q4 0 0 0
## Question 11
# Pull values from adjusted trend model
b <- trend.model.adjusted$coefficients[[1]] # intercept
t <- trend.model.adjusted$coefficients[[2]] # time period coefficient
Q1 <- trend.model.adjusted$coefficients[[3]] # Q1 coefficient
Q2 <- trend.model.adjusted$coefficients[[4]] # Q2 coefficient
Q3 <- trend.model.adjusted$coefficients[[5]] # Q3 coefficient
# Next month prediction (January = month 13 = Q1)
F.13 <- (t * 13) + b + (Q1*1) + (Q2*0) + (Q3*0)
# Show Month 13 predicted value
print(F.13)
## [1] 522.34