Week 3 Assignment 3B Approach - Window Functions

Introduction (Approach)

The goal of this assignment is to find a time series dataset covering two or more items and use window functions to calculate a year-to-date average and a 6-day moving average for each item. I’m planning to use daily stock price data for a small handful of companies since January 2022, pulled directly using the quantmod R package, which connects to Yahoo Finance and avoids the need to manually download or host a CSV file.

My plan is to pull daily closing prices for a few tickers, combine them into one tidy data frame with a date, ticker symbol, and price column, and then use dplyr and slider to calculate the two required window calculations. A 6-day moving average is a rolling calculation, so I’ll need a function that looks at a sliding window of the 6 most recent trading days for each ticker separately. The year-to-date average is a bit different, since it’s an expanding calculation that resets at the start of each calendar year and grows to include more days as the year goes on, rather than staying a fixed window size.

The first challenge I anticipate is making sure the rolling and expanding calculations are computed separately for each ticker, so that one company’s prices don’t accidentally get mixed into another company’s moving average. The second challenge will be handling the start of the dataset, since a 6-day moving average doesn’t have a full 6 days to average over until day 6, so I’ll need to decide how to handle those first few rows for each ticker.

Since this assignment allows either SQL or dplyr for the window functions, I’m planning to do the main analysis in R with dplyr and slider, since I can load the data directly there without an extra step through PostgreSQL.