Assignment 2 Due Dec 17", 2021 at 11:55 pm For this assignment, you will use the columns titled close and Volume from the excel sheet you downloaded. The Close column represents the price of the stock when the market closed for that day, and the Volume represents the number of stocks that were traded on that day. You may use COUNTIF, and AND Excel functions in questions 2 and 4, LOOKUP or VLOOKUP Or XLOOKUP functions for question 5 or use filter from data tab and define your filtration criteria. 1. For the close price, use the minimum, the maximum, and the quartiles you calculated in Assignment 1 to categorize the closing price into four segments. Each category will be an event that the closing stock price will fall into during that period. Clearly define each of those events, remember that the close price is continuous. Name the events C1, C2, C3, and C4, respectively. 2. What is the probability of each of the events you defined in 1? 3. For the volume, take the range divide it by 4. Segment the volume into four segments as follows: a. [Minimum volume, Minimum volume + range/4) b. (Minimum volume + range/4, Minimum volume + range/2) [Minimum volume + range/2, Minimum volume + 3*range/4) d. (Minimum volume + 3*range/4, Maximum volume) Clearly define the boundaries of those events. Although the volume is discrete, you may assume it is continuous. Name the events V1, V2, V3, and V4, respectively. 4. What is the probability of each of the events you defined in 3? 5. What is the probability of each pair from 1 and 3? As in, PC1 and v1), PIC1 and V1), ...,PC4 and V3). and P[C4 and V4). 6. Using the answers to 5 and 2, what are the conditional probabilities of volume given close price? As in, P(V1C1), P(V11C2)., P(V4|C3), and P[V4/C4). + Hentet for Duta New View All C WO Sheet 22. The MOV . 27 . D 00 Optow Close A 27.5 2015 26,6 27.19 27:21 2003 26.46 27.01 more P 39.03 53.12 36.82 57.97 MAXE2:E84) MIN (E2E84) QUARTILE.EXCE2:E84.1 QUARTILE.EXCE2:E843 1 Date 2 10/1/2020 3 10/2/2020 4 10/5/2020 5 10/6/2020 6 10/7/2020 7 10/8/2020 8 10/9/2020 9 10/12/2020 10 10/13/2020 11 10/14/2020 12 10/15/2020 13 10/16/2020 14 10/19/2020 15 10/20/2020 16 10/21/2020 17 10/22/2020 18 10/23/2020 19 10/26/2020 20 10/27/2020 21 10/28/2020 22 10/29/2020 23 10/30/2020 24 11/2/2020 25 11/3/2020 26 11/4/2020 27 11/5/2020 Open High Low Close Adj Close Volume 57.63 57.96 57.16 53.12 56.67087 5875300 57.44 57.78 56.57 53.15 55.64335 6198500 56.95 57.14 56.7 53.19 55.85864 5144800 57.07 57.2 55.92 54.34 54.88982 5015200 56.15 56.42 55.67 54,42 54.98768 4770100 56.48 56.75 56.23 54.94 55.29104 4483100 56.92 57.48 56.68 55.01 56.07393 4513600 57.62 59.07 57.62 55.23 57.39504 8333900 58.92 59.02 58.53 55.44 57.63969 5345400 58.66 59.11 58.11 55.64 57.33632 5130800 57.8 58.49 57.63 55.84 57.00168 5815000 58.58 58.7 57.83 55.92 56.7296 5871800 57.98 58.31 57.03 56.09 55.94671 39,40700 57.53 57.61 56.8 56.12 55.76078 4131700 56.66 57.25 56.64 56.19 55,6012 4116900 56.83 56.91 56.24 56.25 55.6042 3528800 57.26 57.3 5647 56.42 55.398693942500 56.16 56,17 54.79 56,5 54,04822 6983400 55.13 55.76 54.93 56,61 53.83293 4402900 54.28 54.59 53.08 56.82 52.01273 6289000 53.14 53.78 52.51 56.82 52.05187 5951900 53.03 53.51 52.56 56.86 51.98337 6574100 53.94 54.6 53.6 56.87 53.17727 6042900 54.03 55.49 54 56.98 53.25555 7200700 55.61 55.81 54.67 57.08 53.76443 8391700 55 56 5611 55 57 0949045246M M N o P Q R 55.56 55.86 57.75 55.42 57.2 57.8 27 11/5/2020 28 11/6/2020 29 11/9/2020 30 11/10/2020 31 11/11/2020 32 11/12/2020 33 11/13/2020 34 11/16/2020 35 11/17/2020 36 11/18/2020 37 11/19/2020 38 11/20/2020 39 11/23/2020 40 11/24/2020 41 11/25/2020 42 11/27/2020 4) 11/30/2020 44 12/1/2020 12/2/2020 46 12/3/2020 47 12/4/2020 40 12/7/2020 12/8/2020 50 12/9/2020 51 12/10/2020 52 12/11/2020 53 12/14/2020 57.52 58.3 58.26 58.73 57.52 57 42 57.1 57.51 58 57.63 57.25 57.79 58.34 58.26 58.73 58.92 58.26 58.18 58.01 SR2 57.37 C 56.11 56.38 58.24 57.36 58.1 57.8 58.13 58.7 58.95 59.03 57.65 57.78 57.51 58.03 58.09 57.82 57.51 58.69 58.75 58.78 59.12 58.99 586 58.25 58.00 57.62 57.94 D 55.38 55.62 55.88 55.06 57.04 57 57.34 57.97 58.19 57.75 56.93 56.97 56.87 57.36 57.48 573 56.89 57.49 57.82 58 23 58.63 58.13 5803 57.42 57.08 56.68 57.26 G 57.09 54.44944 5744700 57.09 54,64517 4527800 57.1 54.72346 8273800 57.12 55.86842 7449200 57.14 56.50452 5867500 57.14 56.24029 3890000 57.16 56.81767 4703700 57.17 57,4244 5018600 57.18 57.2874 4674100 57.2 56.52408 4978800 57.22 56.36751 6273800 57.29 55.91735 4884300 57.3 56.12286 4934700 57.31 56.5828 9101200 57.32 56.25987 4232600 57.35 56.2305 2664200 57.35 56.22072 7659600 57.37 52.20911 7386200 57.4 56.89596 5054900 57.45 57.30097 7775400 57.45 57.76091 5384900 57.46 57.16018 4834700 57.47 57,04225 5313200 57.49 56.484 000 57.59 55.9565 54100 576 56.17179 492600 57.68 56,06414 5204400 53 12/14/2020 54 12/15/2020 55 12/16/2020 56 12/17/2020 57 12/18/2020 58 12/21/2020 59 12/22/2020 60 12/23/2020 61 12/24/2020 62 12/28/2020 63 12/29/2020 64 12/30/2020 65 12/31/2020 66 1/4/2021 67 1/5/2021 68 1/6/2021 69 1/7/2021 70 1/8/2021 71 1/11/2021 72 1/12/2021 73 1/11/2021 74 1/14/2021 75 1/15/2021 76 1/19/2021 77 1/20/2021 70 1/21/2021 79 1/22/2021 57.37 57.34 57.46 57.24 58.12 57.32 57.46 57.52 57.42 58.15 5853 58.07 58.1 58.49 57.89 57.99 57.72 57.37 58 16 57.35 57.09 57.42 57.23 57.49 57.14 56.92 56.07 57.94 57.65 57.8 58.1 58.41 57.82 57.65 57.73 57.87 58.58 58.96 58.37 58.53 60 58.12 58.33 58.35 58.27 58.22 57.53 57.73 57.71 57.5 57.7 57.34 57 56,58 57.26 57.18 57.09 57.1 57.84 57.05 56.92 57.33 57.32 57.94 58.31 57.91 57.61 57.33 57.32 57.66 57.35 57.68 56,06414 5204400 57.72 56.22072 5465400 57.74 55.97607 5727100 57.76 56.69045 7811300 57.76 57.07211 10574800 57.82 56.445795987400 57.85 55.93692 4886600 57.87 56 12286 4273400 57.91 56.61216 2347800 57.92 57.02317 3496500 57.93 57.199324479300 57.97 57,06649 4490400 57.98 57.52893 5321600 58 56.98778 9387200 58.06 57.04681 5421900 58.14 56.93858 7563200 58.19 56.8.30058589100 58.27 57.25343 6642700 58.29 56.17114 6761500 5832 56.39743 7490000 58.34 56.44661 7276500 58.41 56.3876 7325200 58.45 56.299057410400 58.46 56.22033 6672100 58.47 56.18098 5179100 58.54 55,21675 6938100 58.56 55,34466 6241800 57.33 57.06 56.76 57.02 56.99 56.69 57.02 56.62 55.95 55.92 58 12/21/2020 59 12/22/2020 60 22/23/2020 61 12/24/2020 62 12/28/2020 63 12/29/2020 64 12/30/2020 65 12/31/2020 66 1/4/2021 67 1/5/2021 68 1/6/2021 69 1/7/2021 70 1/8/2021 71 1/11/2021 72 1/12/2021 73 1/13/2021 74 1/14/2021 75 1/15/2021 76 1/19/2021 77 1/20/2021 78 1/21/2021 79 1/22/2021 80 1/25/2021 81 1/26/2021 82 1/27/2021 83 1/28/2021 141/29/2021 57.32 57.46 57.52 57.42 58.15 58.53 58.07 58.1 58.49 57.89 57.99 57.72 57.37 58.16 57.35 57.09 57.42 57.23 57.49 57.14 56.92 56.07 55.99 56.79 57.82 57.65 57.73 57.87 58.58 58.96 58.37 58 53 60 58.12 58 33 58.35 58.27 58.22 57.53 57.73 57.71 57.5 57.7 57.34 57 56.58 57.05 57.72 57.98 57.59 56.84 57.05 56.97 57.33 57.32 57.94 58.31 57.91 57.61 57.33 57.32 57.66 57.35 57.33 57.06 56.76 57.02 56.99 56.69 57.02 56.62 55.95 55.92 55.79 56.58 56.29 56.39 54.96 57.82 56.44579 5987400 57.85 55.93692 4886600 57.87 56.12286 4273400 57.91 56.61216 2347800 57.92 57.02317 3496500 57.93 57.19932 4479300 57.97 57.06649 4490400 57.98 57.52893 5321600 58 56.98778 9187200 58.06 57.04681 5421900 58.14 56.93858 7663200 58.19 56.83035 8589100 58.27 57.25343 6642700 58.29 56.17114 6761500 58.32 56.39743 7450900 58.34 56.44663 7276500 58.41 56.3876 7325200 58.45 56.29905 7410400 58.46 56.22033 6672100 58.47 56.18098 5179100 58.54 55.21675 6938100 58.56 55.34466 6241800 58.59 55.95468 7388700 58.65 56.66309 4759800 58.68 55.51192 7583000 58.9 56.20065 7522700 59.03 54.5477 14261000 56.99 56.6 55.Os 1 Assignment 2 Due Dec 17", 2021 at 11:55 pm For this assignment, you will use the columns titled close and Volume from the excel sheet you downloaded. The Close column represents the price of the stock when the market closed for that day, and the Volume represents the number of stocks that were traded on that day. You may use COUNTIF, and AND Excel functions in questions 2 and 4, LOOKUP or VLOOKUP Or XLOOKUP functions for question 5 or use filter from data tab and define your filtration criteria. 1. For the close price, use the minimum, the maximum, and the quartiles you calculated in Assignment 1 to categorize the closing price into four segments. Each category will be an event that the closing stock price will fall into during that period. Clearly define each of those events, remember that the close price is continuous. Name the events C1, C2, C3, and C4, respectively. 2. What is the probability of each of the events you defined in 1? 3. For the volume, take the range divide it by 4. Segment the volume into four segments as follows: a. [Minimum volume, Minimum volume + range/4) b. (Minimum volume + range/4, Minimum volume + range/2) [Minimum volume + range/2, Minimum volume + 3*range/4) d. (Minimum volume + 3*range/4, Maximum volume) Clearly define the boundaries of those events. Although the volume is discrete, you may assume it is continuous. Name the events V1, V2, V3, and V4, respectively. 4. What is the probability of each of the events you defined in 3? 5. What is the probability of each pair from 1 and 3? As in, PC1 and v1), PIC1 and V1), ...,PC4 and V3). and P[C4 and V4). 6. Using the answers to 5 and 2, what are the conditional probabilities of volume given close price? As in, P(V1C1), P(V11C2)., P(V4|C3), and P[V4/C4). + Hentet for Duta New View All C WO Sheet 22. The MOV . 27 . D 00 Optow Close A 27.5 2015 26,6 27.19 27:21 2003 26.46 27.01 more P 39.03 53.12 36.82 57.97 MAXE2:E84) MIN (E2E84) QUARTILE.EXCE2:E84.1 QUARTILE.EXCE2:E843 1 Date 2 10/1/2020 3 10/2/2020 4 10/5/2020 5 10/6/2020 6 10/7/2020 7 10/8/2020 8 10/9/2020 9 10/12/2020 10 10/13/2020 11 10/14/2020 12 10/15/2020 13 10/16/2020 14 10/19/2020 15 10/20/2020 16 10/21/2020 17 10/22/2020 18 10/23/2020 19 10/26/2020 20 10/27/2020 21 10/28/2020 22 10/29/2020 23 10/30/2020 24 11/2/2020 25 11/3/2020 26 11/4/2020 27 11/5/2020 Open High Low Close Adj Close Volume 57.63 57.96 57.16 53.12 56.67087 5875300 57.44 57.78 56.57 53.15 55.64335 6198500 56.95 57.14 56.7 53.19 55.85864 5144800 57.07 57.2 55.92 54.34 54.88982 5015200 56.15 56.42 55.67 54,42 54.98768 4770100 56.48 56.75 56.23 54.94 55.29104 4483100 56.92 57.48 56.68 55.01 56.07393 4513600 57.62 59.07 57.62 55.23 57.39504 8333900 58.92 59.02 58.53 55.44 57.63969 5345400 58.66 59.11 58.11 55.64 57.33632 5130800 57.8 58.49 57.63 55.84 57.00168 5815000 58.58 58.7 57.83 55.92 56.7296 5871800 57.98 58.31 57.03 56.09 55.94671 39,40700 57.53 57.61 56.8 56.12 55.76078 4131700 56.66 57.25 56.64 56.19 55,6012 4116900 56.83 56.91 56.24 56.25 55.6042 3528800 57.26 57.3 5647 56.42 55.398693942500 56.16 56,17 54.79 56,5 54,04822 6983400 55.13 55.76 54.93 56,61 53.83293 4402900 54.28 54.59 53.08 56.82 52.01273 6289000 53.14 53.78 52.51 56.82 52.05187 5951900 53.03 53.51 52.56 56.86 51.98337 6574100 53.94 54.6 53.6 56.87 53.17727 6042900 54.03 55.49 54 56.98 53.25555 7200700 55.61 55.81 54.67 57.08 53.76443 8391700 55 56 5611 55 57 0949045246M M N o P Q R 55.56 55.86 57.75 55.42 57.2 57.8 27 11/5/2020 28 11/6/2020 29 11/9/2020 30 11/10/2020 31 11/11/2020 32 11/12/2020 33 11/13/2020 34 11/16/2020 35 11/17/2020 36 11/18/2020 37 11/19/2020 38 11/20/2020 39 11/23/2020 40 11/24/2020 41 11/25/2020 42 11/27/2020 4) 11/30/2020 44 12/1/2020 12/2/2020 46 12/3/2020 47 12/4/2020 40 12/7/2020 12/8/2020 50 12/9/2020 51 12/10/2020 52 12/11/2020 53 12/14/2020 57.52 58.3 58.26 58.73 57.52 57 42 57.1 57.51 58 57.63 57.25 57.79 58.34 58.26 58.73 58.92 58.26 58.18 58.01 SR2 57.37 C 56.11 56.38 58.24 57.36 58.1 57.8 58.13 58.7 58.95 59.03 57.65 57.78 57.51 58.03 58.09 57.82 57.51 58.69 58.75 58.78 59.12 58.99 586 58.25 58.00 57.62 57.94 D 55.38 55.62 55.88 55.06 57.04 57 57.34 57.97 58.19 57.75 56.93 56.97 56.87 57.36 57.48 573 56.89 57.49 57.82 58 23 58.63 58.13 5803 57.42 57.08 56.68 57.26 G 57.09 54.44944 5744700 57.09 54,64517 4527800 57.1 54.72346 8273800 57.12 55.86842 7449200 57.14 56.50452 5867500 57.14 56.24029 3890000 57.16 56.81767 4703700 57.17 57,4244 5018600 57.18 57.2874 4674100 57.2 56.52408 4978800 57.22 56.36751 6273800 57.29 55.91735 4884300 57.3 56.12286 4934700 57.31 56.5828 9101200 57.32 56.25987 4232600 57.35 56.2305 2664200 57.35 56.22072 7659600 57.37 52.20911 7386200 57.4 56.89596 5054900 57.45 57.30097 7775400 57.45 57.76091 5384900 57.46 57.16018 4834700 57.47 57,04225 5313200 57.49 56.484 000 57.59 55.9565 54100 576 56.17179 492600 57.68 56,06414 5204400 53 12/14/2020 54 12/15/2020 55 12/16/2020 56 12/17/2020 57 12/18/2020 58 12/21/2020 59 12/22/2020 60 12/23/2020 61 12/24/2020 62 12/28/2020 63 12/29/2020 64 12/30/2020 65 12/31/2020 66 1/4/2021 67 1/5/2021 68 1/6/2021 69 1/7/2021 70 1/8/2021 71 1/11/2021 72 1/12/2021 73 1/11/2021 74 1/14/2021 75 1/15/2021 76 1/19/2021 77 1/20/2021 70 1/21/2021 79 1/22/2021 57.37 57.34 57.46 57.24 58.12 57.32 57.46 57.52 57.42 58.15 5853 58.07 58.1 58.49 57.89 57.99 57.72 57.37 58 16 57.35 57.09 57.42 57.23 57.49 57.14 56.92 56.07 57.94 57.65 57.8 58.1 58.41 57.82 57.65 57.73 57.87 58.58 58.96 58.37 58.53 60 58.12 58.33 58.35 58.27 58.22 57.53 57.73 57.71 57.5 57.7 57.34 57 56,58 57.26 57.18 57.09 57.1 57.84 57.05 56.92 57.33 57.32 57.94 58.31 57.91 57.61 57.33 57.32 57.66 57.35 57.68 56,06414 5204400 57.72 56.22072 5465400 57.74 55.97607 5727100 57.76 56.69045 7811300 57.76 57.07211 10574800 57.82 56.445795987400 57.85 55.93692 4886600 57.87 56 12286 4273400 57.91 56.61216 2347800 57.92 57.02317 3496500 57.93 57.199324479300 57.97 57,06649 4490400 57.98 57.52893 5321600 58 56.98778 9387200 58.06 57.04681 5421900 58.14 56.93858 7563200 58.19 56.8.30058589100 58.27 57.25343 6642700 58.29 56.17114 6761500 5832 56.39743 7490000 58.34 56.44661 7276500 58.41 56.3876 7325200 58.45 56.299057410400 58.46 56.22033 6672100 58.47 56.18098 5179100 58.54 55,21675 6938100 58.56 55,34466 6241800 57.33 57.06 56.76 57.02 56.99 56.69 57.02 56.62 55.95 55.92 58 12/21/2020 59 12/22/2020 60 22/23/2020 61 12/24/2020 62 12/28/2020 63 12/29/2020 64 12/30/2020 65 12/31/2020 66 1/4/2021 67 1/5/2021 68 1/6/2021 69 1/7/2021 70 1/8/2021 71 1/11/2021 72 1/12/2021 73 1/13/2021 74 1/14/2021 75 1/15/2021 76 1/19/2021 77 1/20/2021 78 1/21/2021 79 1/22/2021 80 1/25/2021 81 1/26/2021 82 1/27/2021 83 1/28/2021 141/29/2021 57.32 57.46 57.52 57.42 58.15 58.53 58.07 58.1 58.49 57.89 57.99 57.72 57.37 58.16 57.35 57.09 57.42 57.23 57.49 57.14 56.92 56.07 55.99 56.79 57.82 57.65 57.73 57.87 58.58 58.96 58.37 58 53 60 58.12 58 33 58.35 58.27 58.22 57.53 57.73 57.71 57.5 57.7 57.34 57 56.58 57.05 57.72 57.98 57.59 56.84 57.05 56.97 57.33 57.32 57.94 58.31 57.91 57.61 57.33 57.32 57.66 57.35 57.33 57.06 56.76 57.02 56.99 56.69 57.02 56.62 55.95 55.92 55.79 56.58 56.29 56.39 54.96 57.82 56.44579 5987400 57.85 55.93692 4886600 57.87 56.12286 4273400 57.91 56.61216 2347800 57.92 57.02317 3496500 57.93 57.19932 4479300 57.97 57.06649 4490400 57.98 57.52893 5321600 58 56.98778 9187200 58.06 57.04681 5421900 58.14 56.93858 7663200 58.19 56.83035 8589100 58.27 57.25343 6642700 58.29 56.17114 6761500 58.32 56.39743 7450900 58.34 56.44663 7276500 58.41 56.3876 7325200 58.45 56.29905 7410400 58.46 56.22033 6672100 58.47 56.18098 5179100 58.54 55.21675 6938100 58.56 55.34466 6241800 58.59 55.95468 7388700 58.65 56.66309 4759800 58.68 55.51192 7583000 58.9 56.20065 7522700 59.03 54.5477 14261000 56.99 56.6 55.Os 1