Assignment 3B: Window Functions — Approach & Code Base

DATA 607 · Week 3

Author

Dillon Leeper

Overview

This assignment calls for a dataset containing time series for two or more separate items, and the use of window functions (in SQL or dplyr) to calculate a year-to-date (YTD) average and a six-day moving average for each item. This document outlines the planned dataset and the window-function approach in both dplyr and SQL.

Dataset

Daily closing prices for two to three tickers since January 1, 2022, pulled via tidyquant::tq_get() or quantmod::getSymbols(), both of which source free daily data from Yahoo Finance and support multiple symbols and a date range in a single call. This naturally satisfies the “two or more separate items” requirement, with each ticker forming its own time series. A synthetic, LLM-generated dataset is a fallback if API access is unreliable.

The working shape after retrieval will be a long data frame with columns date, ticker, and close.

Planned Approach

Year-to-Date Average

Group by ticker and calendar year, order by date within each group, and take a running (cumulative) mean that resets at the start of each January. This is a running window that grows one row at a time from the first trading day of the year through the current row.

Six-Day Moving Average

Group by ticker only (not by year, since the window should roll across year boundaries), order by date, and take a trailing average over the current row plus the five prior rows — a fixed-width window that slides forward one row at a time.

R Implementation

library(tidyverse)
library(lubridate)

To keep this reproducible with no internet dependency (and no risk of a Yahoo Finance API hiccup on submission day), the dataset below is a seeded synthetic daily-price series for three tickers since January 1, 2022 — same shape as real data (ticker, date, close), so everything downstream is unaffected. Real data can be substituted with one line — see the commented-out tidyquant call.

set.seed(607)

tickers <- c("ALPHA", "BETA", "GAMMA")

trading_dates <- seq(as.Date("2022-01-01"), Sys.Date(), by = "day")
trading_dates <- trading_dates[!weekdays(trading_dates) %in% c("Saturday", "Sunday")]

simulate_prices <- function(ticker, start_price, dates, vol) {
  n <- length(dates)
  daily_returns <- rnorm(n, mean = 0.0003, sd = vol)
  close <- start_price * cumprod(1 + daily_returns)
  tibble(ticker = ticker, date = dates, close = round(close, 2))
}

prices <- bind_rows(
  simulate_prices("ALPHA", 100, trading_dates, 0.015),
  simulate_prices("BETA",  50,  trading_dates, 0.020),
  simulate_prices("GAMMA", 200, trading_dates, 0.012)
)

# To use real market data instead, comment out the block above and uncomment:
# library(tidyquant)
# prices <- tq_get(c("AAPL", "MSFT", "BTC-USD"), from = "2022-01-01", get = "stock.prices") |>
#   transmute(ticker = symbol, date, close)

glimpse(prices)
Rows: 3,690
Columns: 3
$ ticker <chr> "ALPHA", "ALPHA", "ALPHA", "ALPHA", "ALPHA", "ALPHA", "ALPHA", …
$ date   <date> 2022-01-03, 2022-01-04, 2022-01-05, 2022-01-06, 2022-01-07, 20…
$ close  <dbl> 98.53, 100.01, 100.41, 102.10, 100.99, 98.46, 96.68, 98.33, 99.…
prices_ytd <- prices |>
  group_by(ticker, yr = year(date)) |>
  arrange(date, .by_group = TRUE) |>
  mutate(ytd_avg = cummean(close)) |>
  ungroup()

Six-day moving average, using base R’s stats::filter() so no extra packages (slider, zoo) are required:

prices_ma <- prices_ytd |>
  group_by(ticker) |>
  arrange(date, .by_group = TRUE) |>
  mutate(ma6 = as.numeric(stats::filter(close, rep(1 / 6, 6), sides = 1))) |>
  ungroup() |>
  select(-yr)

Equivalent alternatives, for reference (not evaluated here to avoid adding package dependencies):

# slider::slide_dbl(close, mean, .before = 5, .complete = TRUE)
# zoo::rollmean(close, k = 6, align = "right", fill = NA)

Result Sample

prices_ma |>
  group_by(ticker) |>
  slice_head(n = 10) |>
  ungroup()

Validation

Spot-check the six-day moving average against a manual calculation, and confirm the YTD average resets at each year boundary.

check <- prices_ma |> filter(ticker == "ALPHA") |> slice(1:10)

manual_ma6 <- mean(check$close[5:10])
computed_ma6 <- check$ma6[10]

tibble(manual_ma6, computed_ma6, match = isTRUE(all.equal(manual_ma6, computed_ma6)))
prices_ma |>
  filter(ticker == "ALPHA") |>
  group_by(yr = year(date)) |>
  slice_head(n = 1) |>
  ungroup() |>
  select(ticker, date, close, ytd_avg)

Visualization

prices_ma |>
  filter(year(date) == year(Sys.Date())) |>
  ggplot(aes(x = date)) +
  geom_line(aes(y = close), alpha = 0.35) +
  geom_line(aes(y = ytd_avg), linewidth = 0.8) +
  geom_line(aes(y = ma6), linewidth = 0.8, linetype = "dashed") +
  facet_wrap(~ticker, scales = "free_y") +
  labs(
    title = "Close Price with YTD Average and 6-Day Moving Average",
    subtitle = "Solid = YTD average · Dashed = 6-day moving average · Faint = daily close",
    x = NULL, y = "Price"
  ) +
  theme_minimal()
Figure 1

SQL Equivalent

If implemented in SQL instead of (or alongside) dplyr, the same two calculations map to standard window function syntax:

SELECT
  ticker,
  date,
  close,
  AVG(close) OVER (
    PARTITION BY ticker, EXTRACT(YEAR FROM date)
    ORDER BY date
    ROWS UNBOUNDED PRECEDING
  ) AS ytd_avg,
  AVG(close) OVER (
    PARTITION BY ticker
    ORDER BY date
    ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
  ) AS ma_6day
FROM prices
ORDER BY ticker, date;