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 find getSymbols function to be more helpful in downloading the stocks data (Suggested by Chatgpt) but I was also able to download a csv for both Apple and Microsoft from Nasdaq.
Once I’ve read the data into a dataframe, I’d examine the columns that’re being read in. I’d clean up the data so it’s useful. I’d organize the data by item. I would calculate the yearly average
then I’d use window functions (OVER in SQL, cummean and slide_dbl() functions to calculate corresponding averages.
Reading in the stocks data:
I’d be using the following data.
── 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 (dplyr)library (quantmod)
Loading required package: xts
Loading required package: zoo
Attaching package: 'zoo'
The following objects are masked from 'package:base':
as.Date, as.Date.numeric
######################### Warning from 'xts' package ##########################
# #
# The dplyr lag() function breaks how base R's lag() function is supposed to #
# work, which breaks lag(my_xts). Calls to lag(my_xts) that you type or #
# source() into this session won't work correctly. #
# #
# Use stats::lag() to make sure you're not using dplyr::lag(), or you can add #
# conflictRules('dplyr', exclude = 'lag') to your .Rprofile to stop #
# dplyr from breaking base R's lag() function. #
# #
# Code in packages is not affected. It's protected by R's namespace mechanism #
# Set `options(xts.warn_dplyr_breaks_lag = FALSE)` to suppress this warning. #
# #
###############################################################################
Attaching package: 'xts'
The following objects are masked from 'package:dplyr':
first, last
Loading required package: TTR
Registered S3 method overwritten by 'quantmod':
method from
as.zoo.data.frame zoo
##downloading the stocks data. <- getSymbols ("AAPL" , src = "yahoo" ,from = "2025-01-01" ,auto.assign = FALSE )
AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
2025-01-02 248.93 249.10 241.82 243.85 55740700 242.0931
2025-01-03 243.36 244.18 241.89 243.36 40244100 241.6067
2025-01-06 244.31 247.33 243.20 245.00 45045600 243.2348
2025-01-07 242.98 245.55 241.35 242.21 40856000 240.4650
2025-01-08 241.92 243.71 240.05 242.70 37628900 240.9514
2025-01-10 240.01 240.16 233.00 236.85 61710900 235.1436
2025-01-13 233.53 234.67 229.72 234.40 49630700 232.7112
2025-01-14 234.75 236.12 232.47 233.28 39435300 231.5993
2025-01-15 234.64 238.96 234.43 237.87 39832000 236.1562
2025-01-16 237.35 238.01 228.03 228.26 71759100 226.6154
...
2026-09-02 326.87 328.40 323.53 324.96 33776400 324.9600
2026-09-03 324.87 330.81 324.11 328.21 37225800 328.2100
2026-09-04 328.31 328.93 317.86 319.97 39606900 319.9700
2026-09-08 317.10 320.70 314.90 316.22 35477100 316.2200
2026-09-09 315.49 319.15 309.90 315.34 65640000 315.3400
2026-09-10 316.67 326.74 316.51 326.57 70011900 326.5700
2026-09-11 327.45 336.22 326.30 332.27 50716900 332.2700
2026-09-14 334.79 335.50 331.34 333.08 39269100 333.0800
2026-09-15 330.14 331.78 328.35 331.34 31748200 331.3400
2026-09-16 332.53 335.48 330.70 332.41 35981000 332.4100
<- read.csv ("HistoricalData_1789699125674.csv" )
Date Close.Last Volume Open High Low
1 09/17/2026 $337.00 36700230 $334.77 $338.34 $330.1833
2 09/16/2026 $332.41 35981000 $332.53 $335.48 $330.70
3 09/15/2026 $331.34 31748180 $330.135 $331.78 $328.35
4 09/14/2026 $333.08 39269150 $334.79 $335.50 $331.34
5 09/11/2026 $332.27 50716870 $327.45 $336.22 $326.30
6 09/10/2026 $326.57 70011910 $316.67 $326.74 $316.51
7 09/09/2026 $315.34 65639960 $315.485 $319.15 $309.90
8 09/08/2026 $316.22 35477090 $317.10 $320.70 $314.90
9 09/04/2026 $319.97 39606880 $328.305 $328.93 $317.86
10 09/03/2026 $328.21 37225840 $324.87 $330.81 $324.11
11 09/02/2026 $324.96 33776370 $326.865 $328.40 $323.53
12 09/01/2026 $325.13 53167390 $316.98 $327.30 $314.73
13 08/31/2026 $316.85 41242720 $319.60 $321.235 $312.80
14 08/28/2026 $319.70 38649400 $316.845 $322.37 $315.4504
15 08/27/2026 $314.58 32419230 $310.545 $315.40 $309.4001
16 08/26/2026 $313.45 34024490 $310.30 $315.43 $308.8001
17 08/25/2026 $309.90 25869810 $310.79 $313.59 $308.21
18 08/24/2026 $310.34 34673580 $311.47 $313.36 $309.97
19 08/21/2026 $309.35 46876820 $312.05 $312.38 $307.01
20 08/20/2026 $311.30 40959180 $317.455 $320.28 $310.65
21 08/19/2026 $316.83 50505650 $310.14 $319.2799 $309.60
22 08/18/2026 $310.03 53424540 $307.58 $311.49 $305.74
23 08/17/2026 $305.59 38169260 $306.21 $307.66 $302.939
24 08/14/2026 $305.93 28229380 $306.00 $307.49 $304.30
25 08/13/2026 $305.26 40349290 $304.21 $306.00 $302.05
26 08/12/2026 $302.25 41657770 $305.10 $305.66 $300.57
27 08/11/2026 $304.91 37476750 $307.75 $309.97 $302.79
28 08/10/2026 $308.26 44812500 $306.83 $308.26 $304.61
29 08/07/2026 $313.33 34437190 $311.45 $314.81 $310.74
30 08/06/2026 $312.41 46139900 $314.34 $316.2894 $309.23
31 08/05/2026 $311.00 49438760 $309.36 $311.71 $305.67
32 08/04/2026 $309.38 68000970 $302.725 $310.42 $301.32
33 08/03/2026 $303.42 75051950 $309.58 $311.80 $302.56
34 07/31/2026 $308.91 132489100 $304.81 $310.69 $300.00
35 07/30/2026 $333.43 74817790 $333.10 $334.75 $329.59
36 07/29/2026 $338.19 56090840 $339.73 $344.5699 $337.3501
37 07/28/2026 $340.08 51859040 $340.03 $342.89 $335.60
38 07/27/2026 $336.91 49604300 $334.54 $339.57 $334.02
39 07/24/2026 $333.02 47489420 $321.79 $334.37 $321.62
40 07/23/2026 $321.66 40840780 $321.73 $323.30 $319.35
41 07/22/2026 $325.89 38755930 $327.87 $328.9995 $323.34
42 07/21/2026 $327.74 41338920 $323.13 $329.60 $322.2204
43 07/20/2026 $326.59 53468010 $333.505 $333.71 $323.68
44 07/17/2026 $333.74 63407060 $331.98 $334.99 $329.0006
45 07/16/2026 $333.26 62970620 $328.005 $334.68 $326.79
46 07/15/2026 $327.50 60957640 $317.615 $328.73 $317.32
47 07/14/2026 $314.86 36336830 $313.76 $316.19 $311.91
48 07/13/2026 $317.31 43257800 $317.015 $323.45 $315.78
49 07/10/2026 $315.32 34132320 $314.72 $316.91 $312.17
50 07/09/2026 $316.22 48124490 $310.51 $316.53 $308.16
51 07/08/2026 $313.39 41323480 $311.91 $314.82 $307.05
52 07/07/2026 $310.66 42490000 $315.29 $315.48 $310.15
53 07/06/2026 $312.66 53589980 $307.36 $314.20 $307.00
54 07/02/2026 $308.63 75400630 $294.12 $309.42 $293.68
55 07/01/2026 $294.38 50164230 $293.44 $296.59 $289.195
56 06/30/2026 $289.36 65100160 $281.17 $289.94 $280.695
57 06/29/2026 $281.74 66427000 $286.73 $288.3697 $279.85
58 06/26/2026 $283.78 261775500 $275.00 $285.95 $274.21
59 06/25/2026 $275.15 107253700 $287.40 $288.80 $273.75
60 06/24/2026 $293.08 53081860 $295.355 $299.70 $292.94
61 06/23/2026 $294.30 52010930 $297.538 $301.64 $294.18
62 06/22/2026 $297.01 44879910 $297.31 $302.42 $296.76
63 06/18/2026 $298.01 85962200 $298.11 $300.57 $295.62
64 06/17/2026 $295.95 42745060 $300.845 $302.07 $294.36
65 06/16/2026 $299.24 39874400 $295.245 $300.48 $293.97
66 06/15/2026 $296.42 45732570 $294.12 $297.78 $291.70
67 06/12/2026 $291.13 38784790 $296.03 $297.14 $289.62
68 06/11/2026 $295.63 42572500 $293.72 $297.00 $289.59
69 06/10/2026 $291.58 52793270 $290.74 $294.75 $287.38
70 06/09/2026 $290.55 70108850 $300.275 $300.75 $287.78
71 06/08/2026 $301.54 77949080 $308.739 $317.40 $301.17
72 06/05/2026 $307.34 65310500 $312.86 $315.17 $307.15
73 06/04/2026 $311.23 44869130 $313.23 $313.54 $309.65
74 06/03/2026 $310.26 50836710 $314.175 $316.94 $308.85
75 06/02/2026 $315.20 44534720 $307.46 $315.45 $306.685
76 06/01/2026 $306.31 48849930 $309.625 $310.94 $305.02
77 05/29/2026 $312.06 70026750 $311.775 $315.00 $309.53
78 05/28/2026 $312.51 48220390 $310.68 $312.80 $309.57
79 05/27/2026 $310.85 50430920 $308.33 $313.26 $308.30
80 05/26/2026 $308.33 48000490 $309.56 $311.82 $307.67
81 05/22/2026 $308.82 43670220 $306.12 $311.40 $305.84
82 05/21/2026 $304.99 42965130 $301.055 $305.54 $300.40
83 05/20/2026 $302.25 38229840 $298.18 $302.80 $298.08
84 05/19/2026 $298.97 42243560 $296.97 $300.51 $296.35
85 05/18/2026 $297.84 34482960 $300.24 $300.66 $294.91
86 05/15/2026 $300.23 54862840 $297.90 $303.20 $296.52
87 05/14/2026 $298.21 35324920 $299.82 $300.45 $295.38
88 05/13/2026 $298.87 52684260 $293.50 $300.92 $293.50
89 05/12/2026 $294.80 45748130 $292.56 $295.27 $292.56
90 05/11/2026 $292.68 42247290 $291.979 $293.88 $290.23
91 05/08/2026 $293.32 52692760 $290.01 $294.76 $290.00
92 05/07/2026 $287.44 45224300 $289.27 $292.13 $285.78
93 05/06/2026 $287.51 58336070 $281.915 $288.03 $281.07
94 05/05/2026 $284.18 49311710 $276.925 $284.57 $276.501
95 05/04/2026 $276.83 46668400 $279.655 $280.63 $274.8601
96 05/01/2026 $280.14 79915440 $278.855 $287.22 $278.37
97 04/30/2026 $271.35 91848230 $270.50 $276.00 $268.14
98 04/29/2026 $270.17 30047870 $267.55 $271.04 $267.04
99 04/28/2026 $270.71 40018940 $272.335 $273.23 $268.66
100 04/27/2026 $267.61 41466760 $266.09 $268.36 $265.07
101 04/24/2026 $271.06 38157110 $272.755 $273.06 $269.65
102 04/23/2026 $273.43 33399640 $275.045 $275.77 $271.65
103 04/22/2026 $273.17 43249200 $267.82 $273.74 $266.87
104 04/21/2026 $266.17 50209760 $271.50 $272.80 $265.40
105 04/20/2026 $273.05 36590170 $270.33 $274.275 $270.29
106 04/17/2026 $270.23 61436230 $266.96 $272.30 $266.72
107 04/16/2026 $263.40 43323110 $266.80 $267.16 $261.27
108 04/15/2026 $266.43 49913510 $258.16 $266.56 $257.81
109 04/14/2026 $258.83 48370710 $259.245 $261.93 $257.19
110 04/13/2026 $259.20 36234700 $259.73 $260.18 $256.66
111 04/10/2026 $260.48 31291470 $259.98 $262.19 $259.0231
112 04/09/2026 $260.49 28121570 $259.00 $261.12 $256.07
113 04/08/2026 $258.90 41032770 $258.45 $259.7499 $256.53
114 04/07/2026 $253.50 62148010 $256.155 $256.20 $245.70
115 04/06/2026 $258.86 29329910 $256.51 $262.16 $256.46
116 04/02/2026 $255.92 31289370 $254.20 $256.13 $250.65
117 04/01/2026 $255.63 40059430 $254.08 $256.18 $253.33
118 03/31/2026 $253.79 49598090 $247.91 $255.48 $247.101
119 03/30/2026 $246.63 39446210 $250.07 $250.87 $245.51
120 03/27/2026 $248.80 47900000 $253.90 $255.493 $248.07
121 03/26/2026 $252.89 41796650 $252.115 $257.00 $250.77
122 03/25/2026 $252.62 28476670 $254.095 $255.00 $251.60
123 03/24/2026 $251.64 45152290 $250.35 $254.825 $249.55
124 03/23/2026 $251.49 40546110 $253.97 $254.60 $250.28
125 03/20/2026 $247.99 88331080 $247.975 $249.1999 $246.00
126 03/19/2026 $248.96 34864080 $249.40 $251.83 $247.30
127 03/18/2026 $249.94 35757870 $252.625 $254.94 $249.00
128 03/17/2026 $254.23 32361610 $252.955 $255.13 $252.18
129 03/16/2026 $252.82 32074210 $252.105 $253.885 $249.88
130 03/13/2026 $250.12 36929990 $255.48 $256.33 $249.52
131 03/12/2026 $255.76 40794020 $258.66 $258.95 $254.18
132 03/11/2026 $260.81 26218930 $261.09 $262.13 $259.55
133 03/10/2026 $260.83 30590770 $257.645 $262.48 $256.95
134 03/09/2026 $259.88 38218530 $255.69 $261.15 $253.6805
135 03/06/2026 $257.46 41120040 $258.63 $258.77 $254.37
136 03/05/2026 $260.29 49658630 $260.79 $261.555 $257.25
137 03/04/2026 $262.52 39803120 $264.65 $266.15 $261.42
138 03/03/2026 $263.75 38568920 $263.48 $265.56 $260.13
139 03/02/2026 $264.72 41827950 $262.41 $266.53 $260.20
140 02/27/2026 $264.18 72366510 $272.81 $272.81 $262.89
141 02/26/2026 $272.95 32345110 $274.945 $276.11 $270.795
142 02/25/2026 $274.23 33714340 $271.78 $274.94 $271.05
143 02/24/2026 $272.14 47014620 $267.86 $274.89 $267.71
144 02/23/2026 $266.18 37308160 $263.49 $269.43 $263.381
145 02/20/2026 $264.58 42070500 $258.97 $264.75 $258.16
146 02/19/2026 $260.58 30845290 $262.60 $264.48 $260.05
147 02/18/2026 $264.35 34203340 $263.60 $266.82 $262.45
148 02/17/2026 $263.88 58469090 $258.05 $266.29 $255.54
149 02/13/2026 $255.78 56290670 $262.01 $262.23 $255.45
150 02/12/2026 $261.73 81077230 $275.59 $275.72 $260.18
151 02/11/2026 $275.50 51931280 $274.695 $280.18 $274.45
152 02/10/2026 $273.68 34376900 $274.885 $275.37 $272.94
153 02/09/2026 $274.62 44623400 $277.905 $278.20 $271.70
154 02/06/2026 $278.12 50453410 $277.12 $280.905 $276.925
155 02/05/2026 $275.91 52977440 $278.13 $279.50 $273.23
156 02/04/2026 $276.49 90545710 $272.285 $278.95 $272.285
157 02/03/2026 $269.48 64394660 $269.20 $271.875 $267.61
158 02/02/2026 $270.01 73913430 $260.03 $270.49 $259.205
159 01/30/2026 $259.48 92443410 $255.165 $261.8999 $252.18
160 01/29/2026 $258.28 67253010 $258.00 $259.65 $254.41
161 01/28/2026 $256.44 41287970 $257.65 $258.855 $254.51
162 01/27/2026 $258.27 49648270 $259.17 $261.95 $258.21
163 01/26/2026 $255.41 55969230 $251.48 $256.56 $249.80
164 01/23/2026 $248.04 41688980 $247.32 $249.41 $244.68
165 01/22/2026 $248.35 39708340 $249.20 $251.00 $248.15
166 01/21/2026 $247.65 54641730 $248.70 $251.56 $245.18
167 01/20/2026 $246.70 80267520 $252.73 $254.79 $243.42
168 01/16/2026 $255.53 72142770 $257.90 $258.90 $254.93
169 01/15/2026 $258.21 39388560 $260.65 $261.04 $257.05
170 01/14/2026 $259.96 40019420 $259.49 $261.82 $256.71
171 01/13/2026 $261.05 45730850 $258.72 $261.81 $258.39
172 01/12/2026 $260.25 45263770 $259.16 $261.30 $256.80
173 01/09/2026 $259.37 39996970 $259.075 $260.21 $256.22
174 01/08/2026 $259.04 50419340 $257.02 $259.29 $255.70
175 01/07/2026 $260.33 48309800 $263.20 $263.68 $259.81
176 01/06/2026 $262.36 52352090 $267.00 $267.55 $262.12
177 01/05/2026 $267.26 45647190 $270.64 $271.51 $266.14
178 01/02/2026 $271.01 37838050 $272.255 $277.84 $269.00