library(dplyr)
Attaching package: 'dplyr'The following objects are masked from 'package:stats':
filter, lagThe following objects are masked from 'package:base':
intersect, setdiff, setequal, unionlibrary(slider)Window Functions
Introduction
For this assignment, I will analyze daily closing stock prices for Apple (AAPL), Microsoft (MSFT), and Amazon (AMZN) starting from January 1, 2022. The goal of the analysis is to use window functions to examine how the average closing prices of these stocks change over time.
Planned Approach
I will use daily closing price data obtained from Yahoo Finance and stored as a CSV file in my GitHub repository. I plan to organize the data by stock and date, then use window functions to calculate the year-to-date average and the six-day moving average for each stock. The year-to-date average will be calculated separately for each year, while the six-day moving average will use the current trading day and the previous five trading days.
Anticipated Data Challenges
One anticipated challenge is ensuring that the stock price data are imported correctly from the CSV file, particularly making sure that the date, stock symbol, and closing price columns are read with the appropriate data types in R.
Code Base
Load Packages
The following packages are loaded for data manipulation and rolling calculations.
Load the Dataset
The stock price dataset is loaded directly from GitHub. It contains daily closing prices for multiple stocks over time.
stock_prices <- read.csv(
"https://raw.githubusercontent.com/OzgeG01/DATA607-Assignment/refs/heads/main/stock_prices.csv"
)
head(stock_prices) date symbol close
1 2022-01-03 AAPL 182.01
2 2022-01-04 AAPL 179.70
3 2022-01-05 AAPL 174.92
4 2022-01-06 AAPL 172.00
5 2022-01-07 AAPL 172.17
6 2022-01-10 AAPL 172.19Prepare the Data
The date variable is converted to Date format so that the observations can be correctly ordered over time. The stock symbols included in the dataset are also identified.
stock_prices <- stock_prices %>%
mutate(date = as.Date(date))
unique(stock_prices$symbol)[1] "AAPL" "AMZN" "MSFT"Explore the Dataset
Before calculating the window functions, the dataset is examined to identify the available stocks, the number of observations, and the date range.
stock_prices %>%
summarise(
number_of_rows = n(),
number_of_stocks = n_distinct(symbol),
start_date = min(date),
end_date = max(date)
) number_of_rows number_of_stocks start_date end_date
1 3540 3 2022-01-03 2026-09-16Year-to-Date Average
The year-to-date (YTD) average represents the average closing price for each stock from the beginning of the year through the current observation date. The data are grouped by stock symbol and year so that the calculation restarts at the beginning of each year.
stock_prices <- stock_prices %>%
mutate(year = format(date, "%Y"))stock_prices <- stock_prices %>%
arrange(symbol, date) %>%
group_by(symbol, year) %>%
mutate(
ytd_average = cummean(close)
) %>%
ungroup()
head(stock_prices, 10)# A tibble: 10 × 5
date symbol close year ytd_average
<date> <chr> <dbl> <chr> <dbl>
1 2022-01-03 AAPL 182. 2022 182.
2 2022-01-04 AAPL 180. 2022 181.
3 2022-01-05 AAPL 175. 2022 179.
4 2022-01-06 AAPL 172 2022 177.
5 2022-01-07 AAPL 172. 2022 176.
6 2022-01-10 AAPL 172. 2022 175.
7 2022-01-11 AAPL 175. 2022 175.
8 2022-01-12 AAPL 176. 2022 175.
9 2022-01-13 AAPL 172. 2022 175.
10 2022-01-14 AAPL 173. 2022 175.Six-Day Moving Average
The six-day moving average represents the average closing price of each stock over the current trading day and the previous five trading days. The calculation is performed separately for each stock and follows the chronological order of the observations.
stock_prices <- stock_prices %>%
arrange(symbol, date) %>%
group_by(symbol) %>%
mutate(
six_day_average = slide_dbl(
close,
mean,
.before = 5,
.complete = TRUE
)
) %>%
ungroup()
head(stock_prices, 10)# A tibble: 10 × 6
date symbol close year ytd_average six_day_average
<date> <chr> <dbl> <chr> <dbl> <dbl>
1 2022-01-03 AAPL 182. 2022 182. NA
2 2022-01-04 AAPL 180. 2022 181. NA
3 2022-01-05 AAPL 175. 2022 179. NA
4 2022-01-06 AAPL 172 2022 177. NA
5 2022-01-07 AAPL 172. 2022 176. NA
6 2022-01-10 AAPL 172. 2022 175. 175.
7 2022-01-11 AAPL 175. 2022 175. 174.
8 2022-01-12 AAPL 176. 2022 175. 174.
9 2022-01-13 AAPL 172. 2022 175. 173.
10 2022-01-14 AAPL 173. 2022 175. 173.View the Results
The following table displays the closing price, year-to-date average, and six-day moving average for the first observations in the dataset. This allows the two window calculations to be compared with the original daily closing prices.
stock_prices %>%
select(date, symbol, close, ytd_average, six_day_average) %>%
head(15)# A tibble: 15 × 5
date symbol close ytd_average six_day_average
<date> <chr> <dbl> <dbl> <dbl>
1 2022-01-03 AAPL 182. 182. NA
2 2022-01-04 AAPL 180. 181. NA
3 2022-01-05 AAPL 175. 179. NA
4 2022-01-06 AAPL 172 177. NA
5 2022-01-07 AAPL 172. 176. NA
6 2022-01-10 AAPL 172. 175. 175.
7 2022-01-11 AAPL 175. 175. 174.
8 2022-01-12 AAPL 176. 175. 174.
9 2022-01-13 AAPL 172. 175. 173.
10 2022-01-14 AAPL 173. 175. 173.
11 2022-01-18 AAPL 170. 174. 173.
12 2022-01-19 AAPL 166. 174. 172.
13 2022-01-20 AAPL 165. 173. 170.
14 2022-01-21 AAPL 162. 172. 168.
15 2022-01-24 AAPL 162. 172. 166.Conclusion
The year-to-date average and six-day moving average were calculated separately for each stock. The year-to-date average shows the average closing price from the beginning of each year through each observation date, while the six-day moving average shows the average closing price over the most recent six trading days.
Citation
I used ChatGPT (GPT-5.6 Luna), developed by OpenAI, to help me understand some of the functions I used in this assignment and to check my work.
OpenAI. (2026). ChatGPT (GPT-5.6 Luna) [Large language model]. [https://chatgpt.com/ Accessed September 20, 2026].
Video Explainer
Link: