Week 3B: Window Functions — Moving Averages

Setup

# If something is missing, install in the Console (not here):
# install.packages(c("tidyquant","dplyr","slider","lubridate","ggplot2","knitr"))

library(tidyquant)  # for convenient price data
library(dplyr)
library(slider)
library(lubridate)
library(ggplot2)
library(knitr)


# Pick any two tickers you like
symbols <- c("AAPL","MSFT")

prices <- tq_get(symbols, from = "2022-01-01") %>%
  dplyr::select(symbol, date, close) %>%
  dplyr::arrange(symbol, date)

glimpse(prices)

## Rows: 1,854
## Columns: 3
## $ symbol <chr> "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL",…
## $ date   <date> 2022-01-03, 2022-01-04, 2022-01-05, 2022-01-06, 2022-01-07, 20…
## $ close  <dbl> 182.01, 179.70, 174.92, 172.00, 172.17, 172.19, 175.08, 175.53,…

head(prices, 10)

## # A tibble: 10 × 3
##    symbol date       close
##    <chr>  <date>     <dbl>
##  1 AAPL   2022-01-03  182.
##  2 AAPL   2022-01-04  180.
##  3 AAPL   2022-01-05  175.
##  4 AAPL   2022-01-06  172 
##  5 AAPL   2022-01-07  172.
##  6 AAPL   2022-01-10  172.
##  7 AAPL   2022-01-11  175.
##  8 AAPL   2022-01-12  176.
##  9 AAPL   2022-01-13  172.
## 10 AAPL   2022-01-14  173.

Window functions: YTD average & 6-day moving average

# YTD average: within each (symbol, year), cumulative mean of close up to each date
ytd_ma <- prices %>%
  dplyr::mutate(year = lubridate::year(date)) %>%
  dplyr::group_by(symbol, year) %>%
  dplyr::arrange(date, .by_group = TRUE) %>%
  dplyr::mutate(ytd_avg = cummean(close)) %>%
  dplyr::ungroup()

# 6-day moving average: rolling mean of the last 6 trading days per symbol
with_roll <- ytd_ma %>%
  dplyr::group_by(symbol) %>%
  dplyr::arrange(date, .by_group = TRUE) %>%
  dplyr::mutate(ma6 = slider::slide_dbl(close, mean, .before = 5, .complete = TRUE)) %>%
  dplyr::ungroup()

# Preview
knitr::kable(head(with_roll, 12), caption = "Sample with YTD average and 6-day MA")

Sample with YTD average and 6-day MA

symbol	date	close	year	ytd_avg	ma6
AAPL	2022-01-03	182.01	2022	182.0100	NA
AAPL	2022-01-04	179.70	2022	180.8550	NA
AAPL	2022-01-05	174.92	2022	178.8767	NA
AAPL	2022-01-06	172.00	2022	177.1575	NA
AAPL	2022-01-07	172.17	2022	176.1600	NA
AAPL	2022-01-10	172.19	2022	175.4983	175.4983
AAPL	2022-01-11	175.08	2022	175.4386	174.3433
AAPL	2022-01-12	175.53	2022	175.4500	173.6483
AAPL	2022-01-13	172.19	2022	175.0878	173.1933
AAPL	2022-01-14	173.07	2022	174.8860	173.3717
AAPL	2022-01-18	169.80	2022	174.4236	172.9767
AAPL	2022-01-19	166.23	2022	173.7408	171.9833

ggplot(with_roll, aes(date)) +
  geom_line(aes(y = close), alpha = 0.5) +
  geom_line(aes(y = ma6)) +
  facet_wrap(~ symbol, scales = "free_y") +
  labs(title = "Close vs 6-day Moving Average",
       y = "Price", x = "Date") +
  theme_minimal()

## Warning: Removed 5 rows containing missing values or values outside the scale range
## (`geom_line()`).

Results (brief)

• The YTD average (cummean within each year) shows how each stock’s average price evolves as the year progresses—early values move quickly, then stabilize as more days are added.
• The 6-day moving average smooths day-to-day noise and reveals short-term trends; when the MA rises above (or falls below) the close, it often signals short momentum shifts.
• Comparing panels, you can see that symbols with higher volatility have a larger gap between the daily close and the 6-day MA, while steadier symbols track the MA more closely.