Approach

For this assignment, I will use historical daily stock price data for three companies which I chose to be Tesla (TSLA), Google/Alphabet (GOOGL), and Amazon (AMZN). I will obtain the historical stock data from Yahoo Finance directly in R using the quantmod package and use stock prices beginning on January 1, 2022. The historical data includes information such as the date, opening price, closing price, daily high and low prices, adjusted closing price, and trading volume. I will mainly focus on the date and closing price for each company. After combining the three data sets, I will organize the observations by company and date. Using window functions in R with dplyr, I will calculate the year-to-date average closing price for each stock. This will represent the average closing price from the beginning of each year through the current date. I will also calculate a six day moving average for each stock, using the current day’s closing price and the previous five trading days. The calculations will be grouped by stock symbol so that Tesla, Google, and Amazon are calculated separately. The final dataset will show the daily closing price, year-to-date average, and six-day moving average for each of the three stocks.

One challenge I anticipate is making sure that the three datasets have the same structure before combining them. I will also need to make sure the dates are properly formatted and sorted in chronological order before calculating the moving averages. Another challenge will be handling the beginning of each stock’s time series, since there will not initially be six trading days available to calculate a complete six-day moving average. I will need to decide how to handle these initial values, such as leaving them as NA until six observations are available.

Code Base

Download Data

library(quantmod)
## Loading required package: xts
## Loading required package: zoo
## 
## Attaching package: 'zoo'
## The following objects are masked from 'package:base':
## 
##     as.Date, as.Date.numeric
## Loading required package: TTR
## Registered S3 method overwritten by 'quantmod':
##   method            from
##   as.zoo.data.frame zoo
library(quantmod)
library(dplyr)
## 
## ######################### Warning from 'xts' package ##########################
## #                                                                             #
## # The dplyr lag() function breaks how base R's lag() function is supposed to  #
## # work, which breaks lag(my_xts). Calls to lag(my_xts) that you type or       #
## # source() into this session won't work correctly.                            #
## #                                                                             #
## # Use stats::lag() to make sure you're not using dplyr::lag(), or you can add #
## # conflictRules('dplyr', exclude = 'lag') to your .Rprofile to stop           #
## # dplyr from breaking base R's lag() function.                                #
## #                                                                             #
## # Code in packages is not affected. It's protected by R's namespace mechanism #
## # Set `options(xts.warn_dplyr_breaks_lag = FALSE)` to suppress this warning.  #
## #                                                                             #
## ###############################################################################
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:xts':
## 
##     first, last
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(zoo)
library(ggplot2)

##Load Stock Data

stocks <- c("TSLA", "GOOGL", "AMZN")

getSymbols(
  stocks,
  src = "yahoo",
  from = "2022-01-01",
  to = "2026-09-17"
)
## [1] "TSLA"  "GOOGL" "AMZN"

Create a simple dataframe with only the information wanted

tesla <- data.frame(
  date = index(TSLA),
  close = as.numeric(Cl(TSLA)),
  stock = "TSLA"
)

google <- data.frame(
  date = index(GOOGL),
  close = as.numeric(Cl(GOOGL)),
  stock = "GOOGL"
)

amazon <- data.frame(
  date = index(AMZN),
  close = as.numeric(Cl(AMZN)),
  stock = "AMZN"
)

Combine all values into one dataset

stock_data <- bind_rows(tesla, google, amazon)

stock_data <- stock_data %>%
  arrange(stock, date)

head(stock_data)
##         date    close stock
## 1 2022-01-03 170.4045  AMZN
## 2 2022-01-04 167.5220  AMZN
## 3 2022-01-05 164.3570  AMZN
## 4 2022-01-06 163.2540  AMZN
## 5 2022-01-07 162.5540  AMZN
## 6 2022-01-10 161.4860  AMZN

Calculate YTD average

stock_data <- stock_data %>%
  mutate(year = format(date, "%Y")) %>%
  group_by(stock, year) %>%
  mutate(ytd_average = cummean(close)) %>%
  ungroup()

head(stock_data)
## # A tibble: 6 × 5
##   date       close stock year  ytd_average
##   <date>     <dbl> <chr> <chr>       <dbl>
## 1 2022-01-03  170. AMZN  2022         170.
## 2 2022-01-04  168. AMZN  2022         169.
## 3 2022-01-05  164. AMZN  2022         167.
## 4 2022-01-06  163. AMZN  2022         166.
## 5 2022-01-07  163. AMZN  2022         166.
## 6 2022-01-10  161. AMZN  2022         165.

Calculate Six-Day Moving Average

stock_data <- stock_data %>%
  group_by(stock) %>%
  arrange(date, .by_group = TRUE) %>%
  mutate(
    six_day_average = rollmean(
      close,
      k = 6,
      fill = NA,
      align = "right"
    )
  ) %>%
  ungroup()

head(stock_data, 10)
## # A tibble: 10 × 6
##    date       close stock year  ytd_average six_day_average
##    <date>     <dbl> <chr> <chr>       <dbl>           <dbl>
##  1 2022-01-03  170. AMZN  2022         170.             NA 
##  2 2022-01-04  168. AMZN  2022         169.             NA 
##  3 2022-01-05  164. AMZN  2022         167.             NA 
##  4 2022-01-06  163. AMZN  2022         166.             NA 
##  5 2022-01-07  163. AMZN  2022         166.             NA 
##  6 2022-01-10  161. AMZN  2022         165.            165.
##  7 2022-01-11  165. AMZN  2022         165.            164.
##  8 2022-01-12  165. AMZN  2022         165.            164.
##  9 2022-01-13  161. AMZN  2022         165.            163.
## 10 2022-01-14  162. AMZN  2022         164.            163.

Check all 3 Stocks

stock_data %>%
  group_by(stock) %>%
  slice_head(n = 8) %>%
  ungroup()
## # A tibble: 24 × 6
##    date       close stock year  ytd_average six_day_average
##    <date>     <dbl> <chr> <chr>       <dbl>           <dbl>
##  1 2022-01-03  170. AMZN  2022         170.             NA 
##  2 2022-01-04  168. AMZN  2022         169.             NA 
##  3 2022-01-05  164. AMZN  2022         167.             NA 
##  4 2022-01-06  163. AMZN  2022         166.             NA 
##  5 2022-01-07  163. AMZN  2022         166.             NA 
##  6 2022-01-10  161. AMZN  2022         165.            165.
##  7 2022-01-11  165. AMZN  2022         165.            164.
##  8 2022-01-12  165. AMZN  2022         165.            164.
##  9 2022-01-03  145. GOOGL 2022         145.             NA 
## 10 2022-01-04  144. GOOGL 2022         145.             NA 
## # ℹ 14 more rows

Create a Data Table

final_stock_data <- stock_data %>%
  select(
    Date = date,
    Stock = stock,
    Closing_Price = close,
    YTD_Average = ytd_average,
    Six_Day_Moving_Average = six_day_average
  ) %>%
  mutate(
    Closing_Price = round(Closing_Price, 2),
    YTD_Average = round(YTD_Average, 2),
    Six_Day_Moving_Average = round(Six_Day_Moving_Average, 2)
  )

head(final_stock_data, 10)
## # A tibble: 10 × 5
##    Date       Stock Closing_Price YTD_Average Six_Day_Moving_Average
##    <date>     <chr>         <dbl>       <dbl>                  <dbl>
##  1 2022-01-03 AMZN           170.        170.                    NA 
##  2 2022-01-04 AMZN           168.        169.                    NA 
##  3 2022-01-05 AMZN           164.        167.                    NA 
##  4 2022-01-06 AMZN           163.        166.                    NA 
##  5 2022-01-07 AMZN           163.        166.                    NA 
##  6 2022-01-10 AMZN           161.        165.                   165.
##  7 2022-01-11 AMZN           165.        165.                   164.
##  8 2022-01-12 AMZN           165.        165.                   164.
##  9 2022-01-13 AMZN           161.        165.                   163.
## 10 2022-01-14 AMZN           162.        164.                   163.

Closing Prices and Six-Day Moving Averages

ggplot(final_stock_data, 
       aes(x = Date, y = Closing_Price, color = Stock)) +
  geom_line() +
  labs(
    title = "Daily Closing Prices",
    x = "Date",
    y = "Closing Price ($)",
    color = "Stock"
  ) +
  theme_minimal()

Daily Closing Prices

ggplot(final_stock_data, aes(x = Date)) +
  geom_line(aes(y = Closing_Price), alpha = 0.5) +
  geom_line(aes(y = Six_Day_Moving_Average)) +
  facet_wrap(~ Stock, scales = "free_y") +
  labs(
    title = "Closing Price and Six-Day Moving Average",
    x = "Date",
    y = "Price ($)"
  ) +
  theme_minimal()

Results

The window functions successfully calculated the year-to-date average and six-day moving average for Tesla, Google, and Amazon. The year-to-date average shows the average closing price from the beginning of each year through each trading day, while the six-day moving average shows the average closing price of the current trading day and previous five trading days. The moving average creates a smoother representation of the daily stock price changes, which can also be seen in the visualization. One complication was that a six-day moving average cannot be calculated for the first five observations of each stock because there are not yet six trading days available. I handled this by leaving these values as NA until the sixth observation. I also needed to make sure the data was grouped and ordered correctly so that calculations for Tesla, Google, and Amazon remained separate and the YTD average restarted for each new year.