Introduction

For this assignment, I used a time-series dataset containing daily stock prices for two companies, AAPL and MSFT. The dataset includes a date column, a stock identifier, and a daily stock price.My approach was to organize the data chronologically and then apply window functions to calculate two metrics for each stock: Year-to-Date (YTD) Average,Six-Day Moving Average The YTD average provides a cumulative average from the beginning of the year through the current observation. The six-day moving average uses a rolling window consisting of the current day and the previous five days. These calculations help smooth fluctuations in stock prices and make trends easier to identify.

#Data

The dataset used in this analysis was a generated stock-price dataset containing 30 days of observations for two stocks, AAPL and MSFT. The data was stored in a CSV file and imported into R for analysis.

library(readr)
stock_prices <- read_csv("https://raw.githubusercontent.com/lioneljr17/LDATA607/refs/heads/main/607LAB3B/stock_prices.csv", show_col_types = FALSE)
View(stock_prices)

head(stock_prices)
## # A tibble: 6 × 3
##   Date       Stock Price
##   <date>     <chr> <dbl>
## 1 2022-01-01 AAPL    170
## 2 2022-01-02 AAPL    172
## 3 2022-01-03 AAPL    171
## 4 2022-01-04 AAPL    174
## 5 2022-01-05 AAPL    176
## 6 2022-01-06 AAPL    175

Calculating the YTD Average

In this section, I do the first window function, calculates a Year-to-Date average for each stock. This is a cumulative average that includes all observations from the beginning of the dataset through the current row.

stocks_ytd <- stock_prices %>%
 group_by(Stock) %>%
 arrange(Date, .by_group = TRUE) %>%
 mutate(
  YTD_Avg = cummean(Price)
 )

Calculating the Six-Day Moving Average

in this section,I do the second window function, calculates a six-day moving average. For each observation, the calculation uses the current day’s price and the previous 5 days’ prices. As the window moves forward, the oldest value is dropped, and the newest value is added.

stocks_final <- stocks_ytd %>%
 group_by(Stock) %>%
 mutate(
  Moving_Avg_6 =
   slide_dbl(
    Price,
    mean,
    .before = 5,
    .complete = FALSE
   )
 )
write_csv(stocks_final,"stock_final.csv")

head(stocks_final,15)
## # A tibble: 15 × 5
## # Groups:   Stock [1]
##    Date       Stock Price YTD_Avg Moving_Avg_6
##    <date>     <chr> <dbl>   <dbl>        <dbl>
##  1 2022-01-01 AAPL    170    170          170 
##  2 2022-01-02 AAPL    172    171          171 
##  3 2022-01-03 AAPL    171    171          171 
##  4 2022-01-04 AAPL    174    172.         172.
##  5 2022-01-05 AAPL    176    173.         173.
##  6 2022-01-06 AAPL    175    173          173 
##  7 2022-01-07 AAPL    178    174.         174.
##  8 2022-01-08 AAPL    180    174.         176.
##  9 2022-01-09 AAPL    179    175          177 
## 10 2022-01-10 AAPL    182    176.         178.
## 11 2022-01-11 AAPL    184    176.         180.
## 12 2022-01-12 AAPL    183    177          181 
## 13 2022-01-13 AAPL    185    178.         182.
## 14 2022-01-14 AAPL    187    178.         183.
## 15 2022-01-15 AAPL    186    179.         184.

result

The Year-to-Date average provides a cumulative view of stock performance from the beginning of the year through each observation. As additional observations are added, the average becomes less sensitive to daily fluctuations.The six-day moving average uses a rolling window that contains the current observation and the previous five observations. This creates a smoother trend line by reducing the impact of short-term price changes.Both calculations were performed separately for each stock using window functions, ensuring that observations from different stocks were not combined.

Visualization

The following graph compares the original stock prices with the Year-to-Date average and the six-day moving average for each stock. The moving average line is smoother than the original price line because it averages multiple observations together.

ggplot(
 stocks_final,
 aes(x = Date)
) +
 geom_line(
  aes(y = Price,
    color = "Price"),
  linewidth = 1
 ) +
 geom_line(
  aes(y = YTD_Avg,
    color = "YTD Average"),
  linewidth = 1
 ) +
 geom_line(
  aes(y = Moving_Avg_6,
    color = "6-Day Moving Average"),
  linewidth = 1
 ) +
 facet_wrap(~Stock, scales = "free_y") +
 labs(
  title = "Stock Prices with Window Functions",
  x = "Date",
  y = "Price",
  color = "Metric"
 ) +
 theme_minimal()

conclusion

This analysis demonstrated the use of window functions on time-series data. Using stock prices for AAPL and MSFT, I calculated both a Year-to-Date average and a six-day moving average. The YTD average provided a cumulative measure of performance throughout the year, while the six-day moving average highlighted short-term trends by smoothing daily fluctuations. These calculations illustrate how window functions can be used to analyze sequential data without collapsing the dataset through aggregation.