See data worksheet for this problem, where you'll find 5 years of weekly returns for the S&P 500. You will want to copy that data here in order to create your spreadsheet.

a. Set up a spreadsheet to calculate the 26-week moving average of the index.

Note: In order to create the 26-week moving average for the S&P 500, convert the weekly returns to weekly index values using a base of 100 for the week prior to the first week of the data set. Set the value of the index at the beginning of the sample period equal to 100 + the S&P 500 Return value for that week. The index value in each week thereafter is then updated by adding the previous week's Index level to the current week's S&P 500 Return value. Then, graph the resulting S&P 500 values and the 26-week moving average, beginning with the 26th week of the data set. Use Excel's Chart Wizard and save the chart to a new page.

b. Identify every instance in which the index crosses through its moving average from below. In how many of the weeks following a cross-through does the index increase? Decrease?

Date of cross-through Direction of S&P 500 in subsequent week

c. Identify every instance in which the index crosses through its moving average from above. In how many of the weeks following a cross-through does the index increase? Decrease?

Date of cross-through Direction of S&P 500 in subsequent week

d. How well does the moving average rule perform in identifying buy or sell opportunities?

can you please explain how you get the answer on part b and c

https://learn-us-east-1-prod-fleet01-xythos.s3.amazonaws.com/5b7ade7761e43/6344521?response-cache-control=private%2C%20max-age%3D21600&response-content-disposition=inline%3B%20filename%2A%3DUTF-8%27%27Data%2520Worksheet%2520-%2520case%2520%25232.xlsx&response-content-type=application%2Fvnd.openxmlformats-officedocument.spreadsheetml.sheet&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20200921T180000Z&X-Amz-SignedHeaders=host&X-Amz-Expires=21600&X-Amz-Credential=AKIAZH6WM4PL5SJBSTP6%2F20200921%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Signature=ef78c638e49147ae03e1b3b0e51d54c2c6d58b9d29b93fed64ee8cf9384ceef9

Weekly Rates of Return: June 2014 - April 2019

Week of S&P 500 Index

29-Apr-2019 0.41

22-Apr-2019 0.83

15-Apr-2019 -3.27

8-Apr-2019 0.71

1-Apr-2019 0.13

24-Mar-2019 -1.53

18-Mar-2019 -0.87

11-Mar-2019 -1.80

4-Mar-2019 0.89

25-Feb-2019 0.81

18-Feb-2019 -0.31

11-Feb-2019 0.19

4-Feb-2019 2.70

28-Jan-2019 0.30

21-Jan-2019 -1.41

14-Jan-2019 -0.14

7-Jan-2019 -2.12

31-Dec-2018 0.15

23-Dec-2018 1.33

17-Dec-2018 0.52

10-Dec-2018 -0.27

3-Dec-2018 0.72

26-Nov-2018 1.05

19-Nov-2018 -1.17

12-Nov-2018 1.54

5-Nov-2018 3.18

29-Oct-2018 3.14

22-Oct-2018 -1.12

15-Oct-2018 -1.24

8-Oct-2018 -0.83

1-Oct-2018 1.93

24-Sep-2018 -1.63

17-Sep-2018 0.41

10-Sep-2018 0.92

3-Sep-2018 0.53

27-Aug-2018 0.86

20-Aug-2018 3.15

13-Aug-2018 0.08

6-Aug-2018 -3.43

30-Jul-2018 1.43

23-Jul-2018 -1.38

15-Jul-2018 -1.03

9-Jul-2018 -1.12

2-Jul-2018 -0.80

25-Jun-2018 -0.05

18-Jun-2018 -0.13

10-Jun-2018 1.24

4-Jun-2018 0.16

28-May-2018 2.48

21-May-2018 -0.20

14-May-2018 -0.27

7-May-2018 -0.78

30-Apr-2018 -2.92

23-Apr-2018 0.53

16-Apr-2018 -0.41

8-Apr-2018 -0.22

2-Apr-2018 3.05

26-Mar-2018 -0.15

19-Mar-2018 -0.96

12-Mar-2018 -3.14

5-Mar-2018 1.04

27-Feb-2018 0.07

20-Feb-2018 -0.15

13-Feb-2018 0.27

6-Feb-2018 1.03

30-Jan-2018 -0.91

23-Jan-2018 0.15

16-Jan-2018 1.60

9-Jan-2018 1.21

2-Jan-2018 -0.31

31-Dec-2017 1.46

26-Dec-2017 0.66

19-Dec-2017 1.35

12-Dec-2017 1.19

5-Dec-2017 0.31

28-Nov-2017 2.21

21-Nov-2017 -1.43

14-Nov-2017 -0.27

7-Nov-2017 0.24

31-Oct-2017 2.12

24-Oct-2017 -1.00

17-Oct-2017 0.12

10-Oct-2017 0.80

3-Oct-2017 3.31

26-Sep-2017 -3.81

19-Sep-2017 1.73

12-Sep-2017 -0.27

5-Sep-2017 1.33

29-Aug-2017 1.51

22-Aug-2017 0.24

15-Aug-2017 1.34

8-Aug-2017 -0.26

1-Aug-2017 -1.86

25-Jul-2017 0.54

18-Jul-2017 -0.48

11-Jul-2017 1.26

3-Jul-2017 0.97

27-Jun-2017 -1.96

20-Jun-2017 0.72

13-Jun-2017 0.09

6-Jun-2017 2.51

30-May-2017 3.25

23-May-2017 -1.17

16-May-2017 1.17

9-May-2017 0.36

2-May-2017 3.48

25-Apr-2017 0.59

17-Apr-2017 2.91

11-Apr-2017 -1.20

4-Apr-2017 1.78

28-Mar-2017 -3.60

21-Mar-2017 7.50

14-Mar-2017 0.53

7-Mar-2017 -1.46

28-Feb-2017 -0.83

21-Feb-2017 1.59

14-Feb-2017 0.63

7-Feb-2017 -3.04

31-Jan-2017 -0.66

24-Jan-2017 -4.48

17-Jan-2017 -2.78

10-Jan-2017 2.09

3-Jan-2017 3.27

31-Dec-2016 0.50

27-Dec-2016 -2.27

20-Dec-2016 0.71

13-Dec-2016 -2.49

6-Dec-2016 -2.57

29-Nov-2016 0.62

22-Nov-2016 2.28

15-Nov-2016 1.69

8-Nov-2016 -0.69

1-Nov-2016 0.37

25-Oct-2016 1.50

18-Oct-2016 5.87

11-Oct-2016 4.34

4-Oct-2016 -3.24

27-Sep-2016 -2.13

20-Sep-2016 -4.99

13-Sep-2016 -0.46

6-Sep-2016 -2.42

30-Aug-2016 -2.63

23-Aug-2016 1.30

16-Aug-2016 2.22

9-Aug-2016 5.14

2-Aug-2016 1.34

26-Jul-2016 0.60

19-Jul-2016 -7.99

12-Jul-2016 -6.84

5-Jul-2016 -0.08

28-Jun-2016 0.07

21-Jun-2016 -1.80

14-Jun-2016 -1.97

7-Jun-2016 -3.71

31-May-2016 -1.54

24-May-2016 -2.06

17-May-2016 4.89

10-May-2016 -1.72

3-May-2016 -0.27

26-Apr-2016 -4.34

19-Apr-2016 1.27

12-Apr-2016 -1.04

5-Apr-2016 -2.15

28-Mar-2016 -0.11

22-Mar-2016 -1.50

15-Mar-2016 0.16

8-Mar-2016 2.87

1-Mar-2016 3.85

22-Feb-2016 -1.30

15-Feb-2016 0.73

8-Feb-2016 -2.32

1-Feb-2016 -0.98

25-Jan-2016 0.51

18-Jan-2016 -1.57

11-Jan-2016 -2.30

4-Jan-2016 2.13

31-Dec-2015 -1.11

28-Dec-2015 1.41

21-Dec-2015 1.94

14-Dec-2015 -3.04

7-Dec-2015 1.66

30-Nov-2015 -0.95

23-Nov-2015 1.03

16-Nov-2015 1.64

9-Nov-2015 3.05

2-Nov-2015 -1.58

26-Oct-2015 2.90

19-Oct-2015 -1.66

12-Oct-2015 1.89

5-Oct-2015 2.92

28-Sep-2015 7.78

21-Sep-2015 -11.60

10-Sep-2015 0.62

7-Sep-2015 -4.22

31-Aug-2015 -4.33

24-Aug-2015 1.98

17-Aug-2015 -2.37

10-Aug-2015 -1.99

3-Aug-2015 0.71

27-Jul-2015 -0.42

20-Jul-2015 -0.40

13-Jul-2015 2.11

6-Jul-2015 -2.76

29-Jun-2015 -0.08

22-Jun-2015 0.91

15-Jun-2015 -4.00

8-Jun-2015 0.34

1-Jun-2015 -1.35

25-May-2015 -1.09

18-May-2015 3.72

11-May-2015 -1.65

4-May-2015 1.08

27-Apr-2015 0.81

20-Apr-2015 5.03

12-Apr-2015 4.88

6-Apr-2015 -2.75

30-Mar-2015 1.80

23-Mar-2015 -0.93

16-Mar-2015 -6.72

9-Mar-2015 -0.06

2-Mar-2015 -0.94

23-Feb-2015 -4.28

16-Feb-2015 -1.01

9-Feb-2015 -2.57

2-Feb-2015 -0.40

26-Jan-2015 0.92

19-Jan-2015 1.82

12-Jan-2015 1.56

5-Jan-2015 -1.66

29-Dec-2014 1.10

22-Dec-2014 -0.47

15-Dec-2014 -4.21

8-Dec-2014 4.16

1-Dec-2014 -1.98

24-Nov-2014 -1.90

17-Nov-2014 0.13

10-Nov-2014 -4.26

3-Nov-2014 3.41

27-Oct-2014 -1.24

20-Oct-2014 1.66

13-Oct-2014 -2.47

6-Oct-2014 -1.92

29-Sep-2014 -0.84

22-Sep-2014 -1.17

15-Sep-2014 -1.92

8-Sep-2014 -1.73

1-Sep-2014 0.95

25-Aug-2014 0.99

18-Aug-2014 1.35

11-Aug-2014 0.61

4-Aug-2014 3.03

28-Jul-2014 -4.07

21-Jul-2014 -1.97

14-Jul-2014 2.10

7-Jul-2014 1.67

30-Jun-2014 0.91

23-Jun-2014 -1.57

16-Jun-2014 0.52

9-Jun-2014 -1.37

2-Jun-2014 7.20