For this assignment, I plan to use window functions in R to calculate year-to-date averages and six-day moving averages for several stocks. Window functions are useful because they calculate values across related rows while keeping every original observation in the dataset.
I will use the gafa_stock
dataset from the tsibbledata package. This dataset
contains daily stock prices from 2014 through 2018 for Apple, Amazon,
Facebook, and Google. Each row contains the stock symbol, date, opening
price, closing price, daily high and low prices, adjusted closing price,
and trading volume.
For this assignment, I will mainly use the Symbol,
Date, and Close columns. The
Symbol column identifies each company, the
Date column provides the order of the observations, and the
Close column contains the daily closing price that will be
used in the calculations.
First, I will load the stock data into R and arrange the observations by stock symbol and date. Keeping the data in the correct order is important because both calculations depend on the previous observations for the same stock.
To calculate the year-to-date average, I will group the data by stock symbol and calendar year. I will then use a cumulative mean so that each row shows the average closing price from the beginning of that year through the current date. The calculation will restart when a new calendar year begins.
For the six-day moving average, I will group the data by stock symbol and calculate the average closing price for the current trading day and the previous five trading days. This will create a six-observation window that moves forward one row at a time. Each stock will be calculated separately so that prices from different companies are never included in the same average.
A complete six-day moving average cannot be calculated until a stock
has at least six observations. I plan to keep the first five
moving-average values for each stock as NA instead of
calculating averages from incomplete windows. I will also treat the
observations as trading days because the stock market does not have
records for weekends and market holidays.
The final dataframe will include the stock symbol, date, closing price, calendar year, year-to-date average, and six-day moving average. I also plan to display a sample of the completed calculations and create a graph comparing the original closing prices with the six-day moving averages for each stock.
The main challenge will be making sure the data is correctly grouped and ordered before performing the calculations. The year-to-date average must restart for every stock at the beginning of each year, while the six-day moving average must use only the observations belonging to the same stock. I will also need to make sure that incomplete six-day windows are handled correctly.