19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 Ready EO R S T U V W X Y Z AA AB AC AD AE AF AG AH AJ AK AL AM AN AQ AP AQ AR AS A CSL FMG 4-week 4-week moving moving Average Average (CSL) (FMG) 4-week moving Average: No Outliers (CSL) 4-week moving| Average: No Outliers (FMG) 120.00000% 100.00000% 2019/11/4 4.711% 0.605% 2019/11/11 0.060% 16.582% 2019/11/18 3.496% 5.417% 2019/11/25 5.568% 4.292% 2019/12/2 2.802% 3.148% 2019/12/9 6.179% 6.112% 2019/12/16 3.884% 8.645% 2019/12/23 3.068% 10.511% 2019/12/30 5.442% 6.291% 2020/1/6 6.407% 9.339% 2020/1/13 4.142% 2.004% 2020/1/20 8.575% 9.228% 2020/1/27 5.190% 6.580% 2020/2/3 5.823% 4.309% 2020/2/10 2.151% 5.579% 2020/2/17 0.479% 6.638% 2020/2/24 4.209% 4.008% 2020/3/2 4.369% 2.837% 2020/3/9 7.521% 7.399% 2020/3/16 4.269% 9.436% Std Dev Name Moving Average Market Status (+) 80.00000% 60.00000% 40.00000% 20.00000% 0.00000% 2019/11/4 2019/11/11 -CSL -FMG --->CSL No Outliers -FMG No Outliers 2019/11/18 2019/11/25 2019/12/2 2019/12/9 2019/12/16 2019/12/23 Plot Area 2019/12/30 4-week Moving Average over time 2020/1/6 2020/1/13 2020/1/20 2020/1/27 2020/2/3 2020/2/10 2020/2/17 2020/2/24 2020/3/2 2020/3/9 2020/3/ 20/3/16 76% Task 2 (25%) A moving average returns is defined as the average of n days of returns, including the current day. If this is a 6-day moving average, it will be the sum of today's return and the 5 previous days divided by 6. Using a SUBROUTINE you must complete the following for a series of stocks with supplied returns: Find the sample standard deviation for each stock (formula supplied below) - Find the 4-week Moving Average for each stock - Find the 4-week Moving Average for each stock, removing outliers - An outlier is defined as any return that is more than ONE standard deviation away from the corresponding normal 4-week Moving Average Remember: If an outlier is removed, N will decrease by 1 for that calculation. Note: Only fill in the cells that should have a value in them for the Moving Averages ( - )2 V N-1