Window Function

Approach

For this assignment, we need to have a data downloaded for dataset that is continous time series for two or more items. We need to Use window functions (in SQL or dplyr) to calculate the year-to-date average and the six-day moving averages for each item.

I downloaded Apple and Microsoft (AAPL and MSFT)’s historical data from Nasdaq as a csv .

Once I read the data into a dataframe, I examined the columns that’re being read in. I then cleaned up the data so it’s useful. After organizing the data by item. I calculated the yearly average YTD and the slide_dbl function for moving average.

In conclusion, I graphed the yearly average as mean vs moving average over the window of 5 on the same graph. It showed me how accurately a moving window average is compared to just an overall average, which is skewed.

Code

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.1     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(slider)

##library(quantmod)
##downloading the stocks data. 
AAPLCSV <- read.csv("HistoricalData_1789699125674.csv")
MSFTCSV <- read.csv("HistoricalData_1789806795427.csv") 


## Cleaning up the raw data to fix Date from char to a date format, 
## 
AAPL<-AAPLCSV%>%
  mutate(Date=as.Date(Date, format="%m/%d/%Y"))
MSFT <-MSFTCSV%>%
          mutate(Date=as.Date(Date, format="%m/%d/%Y"))


 MSFT <-MSFT%>% mutate(Close = as.numeric(gsub("\\$","", Close.Last)))
 AAPL <-AAPL%>% mutate(Close = as.numeric(gsub("\\$","", Close.Last))) 


## Adding a column that shows which stock it is
MSFT <- MSFT %>% mutate(Item="MSFT")
AAPL <- AAPL %>% mutate(Item="AAPL")

## Selecting just the needed columns
MSFT<- MSFT %>%
              select(Item, Date, Close)
AAPL<-AAPL %>%
             select(Item, Date, Close)

Stocks<- rbind(AAPL, MSFT)

AAPL <- AAPL %>%
  arrange(Date) %>%
  mutate(Mean = cummean(Close))

MSFT <- MSFT %>%
  arrange(Date) %>%
  mutate(Mean = cummean(Close))

df <- AAPL%>%
  mutate(
    # 5 day moving average (current row + 1 row before)
    moving_avg = slide_dbl(Close, mean, .before = 5))

ggplot(df, aes(x = Date)) +
  geom_line(aes(y = Close)) +
  geom_line(aes(y = moving_avg), linewidth = 1.2) +
  geom_line(aes(y = Mean), color="red", linewidth = 1.2) +
  labs(
    title = "AAPL Closing Price, 5 Day Moving Average, and Mean",
    x = "Date",
    y = "Price ($)"
  )