In: Statistics and Probability
Q1) construct a contingency table for the positive and non-positive returns of the two stocks
Date | BIT | MAC |
11/3/13 | 52.06 | 37.7145 |
18/3/13 | 53.84 | 34.8626 |
25/3/13 | 99.99 | 36.5344 |
1/4/13 | 139.23 | 36.023 |
8/4/13 | 113.07 | 37.5375 |
15/4/13 | 123.93 | 35.3838 |
22/4/13 | 141.36 | 37.3506 |
29/4/13 | 126.5 | 42.3956 |
6/5/13 | 120.39 | 45.2082 |
13/5/13 | 125.5 | 43.8118 |
20/5/13 | 142.52 | 41.8547 |
27/5/13 | 137.88 | 42.4546 |
3/6/13 | 115.66 | 39.6617 |
10/6/13 | 120.98 | 40.8615 |
17/6/13 | 125.28 | 41.127 |
24/6/13 | 111.97 | 41.1762 |
1/7/13 | 97.54 | 41.2647 |
8/7/13 | 114.95 | 43.9986 |
15/7/13 | 96.5 | 43.3299 |
22/7/13 | 92 | 42.2973 |
29/7/13 | 99.95 | 44.451 |
5/8/13 | 125.49 | 43.025 |
12/8/13 | 104.1 | 43.6839 |
19/8/13 | 109.53 | 43.8904 |
26/8/13 | 158.75 | 43.1332 |
2/9/13 | 136.12 | 45.4049 |
9/9/13 | 147.87 | 47.9913 |
16/9/13 | 143.09 | 49.1715 |
23/9/13 | 142.08 | 48.0897 |
30/9/13 | 140.24 | 47.7455 |
7/10/13 | 151.77 | 47.2538 |
14/10/13 | 193.52 | 49.3386 |
21/10/13 | 213.89 | 50.3516 |
28/10/13 | 233.5 | 52.2201 |
4/11/13 | 331.05 | 53.6559 |
11/11/13 | 547.08 | 53.6461 |
18/11/13 | 974.55 | 53.5969 |
25/11/13 | 1191.99 | 53.2822 |
2/12/13 | 1016.27 | 50.9908 |
9/12/13 | 1027.82 | 51.335 |
16/12/13 | 781.78 | 54.8 |
23/12/13 | 889.11 | 54.95 |
30/12/13 | 999 | 55 |
please construct on on excel.
Date | BIT | MAC | ret_BIT | ret_MAC | sign_BIT | sign_MAC |
11-03-2013 | 52.06 | 37.7145 | ||||
18-03-2013 | 53.84 | 34.8626 | 0.034191 | -0.07562 | 1 | -1 |
25-03-2013 | 99.99 | 36.5344 | 0.857169 | 0.047954 | 1 | 1 |
01-04-2013 | 139.23 | 36.023 | 0.392439 | -0.014 | 1 | -1 |
08-04-2013 | 113.07 | 37.5375 | -0.18789 | 0.042043 | -1 | 1 |
15-04-2013 | 123.93 | 35.3838 | 0.096047 | -0.05737 | 1 | -1 |
22-04-2013 | 141.36 | 37.3506 | 0.140644 | 0.055585 | 1 | 1 |
29-04-2013 | 126.5 | 42.3956 | -0.10512 | 0.135071 | -1 | 1 |
06-05-2013 | 120.39 | 45.2082 | -0.0483 | 0.066342 | -1 | 1 |
13-05-2013 | 125.5 | 43.8118 | 0.042445 | -0.03089 | 1 | -1 |
20-05-2013 | 142.52 | 41.8547 | 0.135618 | -0.04467 | 1 | -1 |
27-05-2013 | 137.88 | 42.4546 | -0.03256 | 0.014333 | -1 | 1 |
03-06-2013 | 115.66 | 39.6617 | -0.16115 | -0.06579 | -1 | -1 |
10-06-2013 | 120.98 | 40.8615 | 0.045997 | 0.030251 | 1 | 1 |
17-06-2013 | 125.28 | 41.127 | 0.035543 | 0.006498 | 1 | 1 |
24-06-2013 | 111.97 | 41.1762 | -0.10624 | 0.001196 | -1 | 1 |
01-07-2013 | 97.54 | 41.2647 | -0.12887 | 0.002149 | -1 | 1 |
08-07-2013 | 114.95 | 43.9986 | 0.178491 | 0.066253 | 1 | 1 |
15-07-2013 | 96.5 | 43.3299 | -0.1605 | -0.0152 | -1 | -1 |
22-07-2013 | 92 | 42.2973 | -0.04663 | -0.02383 | -1 | -1 |
29-07-2013 | 99.95 | 44.451 | 0.086413 | 0.050918 | 1 | 1 |
05-08-2013 | 125.49 | 43.025 | 0.255528 | -0.03208 | 1 | -1 |
12-08-2013 | 104.1 | 43.6839 | -0.17045 | 0.015314 | -1 | 1 |
19-08-2013 | 109.53 | 43.8904 | 0.052161 | 0.004727 | 1 | 1 |
26-08-2013 | 158.75 | 43.1332 | 0.449375 | -0.01725 | 1 | -1 |
02-09-2013 | 136.12 | 45.4049 | -0.14255 | 0.052667 | -1 | 1 |
09-09-2013 | 147.87 | 47.9913 | 0.086321 | 0.056963 | 1 | 1 |
16-09-2013 | 143.09 | 49.1715 | -0.03233 | 0.024592 | -1 | 1 |
23-09-2013 | 142.08 | 48.0897 | -0.00706 | -0.022 | -1 | -1 |
30-09-2013 | 140.24 | 47.7455 | -0.01295 | -0.00716 | -1 | -1 |
07-10-2013 | 151.77 | 47.2538 | 0.082216 | -0.0103 | 1 | -1 |
14-10-2013 | 193.52 | 49.3386 | 0.275087 | 0.044119 | 1 | 1 |
21-10-2013 | 213.89 | 50.3516 | 0.10526 | 0.020532 | 1 | 1 |
28-10-2013 | 233.5 | 52.2201 | 0.091683 | 0.037109 | 1 | 1 |
04-11-2013 | 331.05 | 53.6559 | 0.417773 | 0.027495 | 1 | 1 |
11-11-2013 | 547.08 | 53.6461 | 0.65256 | -0.00018 | 1 | -1 |
18-11-2013 | 974.55 | 53.5969 | 0.781367 | -0.00092 | 1 | -1 |
25-11-2013 | 1191.99 | 53.2822 | 0.223118 | -0.00587 | 1 | -1 |
02-12-2013 | 1016.27 | 50.9908 | -0.14742 | -0.043 | -1 | -1 |
09-12-2013 | 1027.82 | 51.335 | 0.011365 | 0.00675 | 1 | 1 |
16-12-2013 | 781.78 | 54.8 | -0.23938 | 0.067498 | -1 | 1 |
23-12-2013 | 889.11 | 54.95 | 0.137289 | 0.002737 | 1 | 1 |
30-12-2013 | 999 | 55 | 0.123596 | 0.00091 | 1 | 1 |
Formulas
Date | BIT | MAC | ret_BIT | ret_MAC | sign_BIT | sign_MAC |
41344 | 52.06 | 37.7145 | ||||
41351 | 53.84 | 34.8626 | =B3/B2-1 | =C3/C2-1 | =SIGN(D3) | =SIGN(E3) |
41358 | 99.99 | 36.5344 | =B4/B3-1 | =C4/C3-1 | =SIGN(D4) | =SIGN(E4) |
41365 | 139.23 | 36.023 | =B5/B4-1 | =C5/C4-1 | =SIGN(D5) | =SIGN(E5) |
41372 | 113.07 | 37.5375 | =B6/B5-1 | =C6/C5-1 | =SIGN(D6) | =SIGN(E6) |
41379 | 123.93 | 35.3838 | =B7/B6-1 | =C7/C6-1 | =SIGN(D7) | =SIGN(E7) |
41386 | 141.36 | 37.3506 | =B8/B7-1 | =C8/C7-1 | =SIGN(D8) | =SIGN(E8) |
41393 | 126.5 | 42.3956 | =B9/B8-1 | =C9/C8-1 | =SIGN(D9) | =SIGN(E9) |
41400 | 120.39 | 45.2082 | =B10/B9-1 | =C10/C9-1 | =SIGN(D10) | =SIGN(E10) |
41407 | 125.5 | 43.8118 | =B11/B10-1 | =C11/C10-1 | =SIGN(D11) | =SIGN(E11) |
41414 | 142.52 | 41.8547 | =B12/B11-1 | =C12/C11-1 | =SIGN(D12) | =SIGN(E12) |
41421 | 137.88 | 42.4546 | =B13/B12-1 | =C13/C12-1 | =SIGN(D13) | =SIGN(E13) |
41428 | 115.66 | 39.6617 | =B14/B13-1 | =C14/C13-1 | =SIGN(D14) | =SIGN(E14) |
41435 | 120.98 | 40.8615 | =B15/B14-1 | =C15/C14-1 | =SIGN(D15) | =SIGN(E15) |
41442 | 125.28 | 41.127 | =B16/B15-1 | =C16/C15-1 | =SIGN(D16) | =SIGN(E16) |
41449 | 111.97 | 41.1762 | =B17/B16-1 | =C17/C16-1 | =SIGN(D17) | =SIGN(E17) |
41456 | 97.54 | 41.2647 | =B18/B17-1 | =C18/C17-1 | =SIGN(D18) | =SIGN(E18) |
41463 | 114.95 | 43.9986 | =B19/B18-1 | =C19/C18-1 | =SIGN(D19) | =SIGN(E19) |
41470 | 96.5 | 43.3299 | =B20/B19-1 | =C20/C19-1 | =SIGN(D20) | =SIGN(E20) |
41477 | 92 | 42.2973 | =B21/B20-1 | =C21/C20-1 | =SIGN(D21) | =SIGN(E21) |
41484 | 99.95 | 44.451 | =B22/B21-1 | =C22/C21-1 | =SIGN(D22) | =SIGN(E22) |
41491 | 125.49 | 43.025 | =B23/B22-1 | =C23/C22-1 | =SIGN(D23) | =SIGN(E23) |
41498 | 104.1 | 43.6839 | =B24/B23-1 | =C24/C23-1 | =SIGN(D24) | =SIGN(E24) |
41505 | 109.53 | 43.8904 | =B25/B24-1 | =C25/C24-1 | =SIGN(D25) | =SIGN(E25) |
41512 | 158.75 | 43.1332 | =B26/B25-1 | =C26/C25-1 | =SIGN(D26) | =SIGN(E26) |
41519 | 136.12 | 45.4049 | =B27/B26-1 | =C27/C26-1 | =SIGN(D27) | =SIGN(E27) |
41526 | 147.87 | 47.9913 | =B28/B27-1 | =C28/C27-1 | =SIGN(D28) | =SIGN(E28) |
41533 | 143.09 | 49.1715 | =B29/B28-1 | =C29/C28-1 | =SIGN(D29) | =SIGN(E29) |
41540 | 142.08 | 48.0897 | =B30/B29-1 | =C30/C29-1 | =SIGN(D30) | =SIGN(E30) |
41547 | 140.24 | 47.7455 | =B31/B30-1 | =C31/C30-1 | =SIGN(D31) | =SIGN(E31) |
41554 | 151.77 | 47.2538 | =B32/B31-1 | =C32/C31-1 | =SIGN(D32) | =SIGN(E32) |
41561 | 193.52 | 49.3386 | =B33/B32-1 | =C33/C32-1 | =SIGN(D33) | =SIGN(E33) |
41568 | 213.89 | 50.3516 | =B34/B33-1 | =C34/C33-1 | =SIGN(D34) | =SIGN(E34) |
41575 | 233.5 | 52.2201 | =B35/B34-1 | =C35/C34-1 | =SIGN(D35) | =SIGN(E35) |
41582 | 331.05 | 53.6559 | =B36/B35-1 | =C36/C35-1 | =SIGN(D36) | =SIGN(E36) |
41589 | 547.08 | 53.6461 | =B37/B36-1 | =C37/C36-1 | =SIGN(D37) | =SIGN(E37) |
41596 | 974.55 | 53.5969 | =B38/B37-1 | =C38/C37-1 | =SIGN(D38) | =SIGN(E38) |
41603 | 1191.99 | 53.2822 | =B39/B38-1 | =C39/C38-1 | =SIGN(D39) | =SIGN(E39) |
41610 | 1016.27 | 50.9908 | =B40/B39-1 | =C40/C39-1 | =SIGN(D40) | =SIGN(E40) |
41617 | 1027.82 | 51.335 | =B41/B40-1 | =C41/C40-1 | =SIGN(D41) | =SIGN(E41) |
41624 | 781.78 | 54.8 | =B42/B41-1 | =C42/C41-1 | =SIGN(D42) | =SIGN(E42) |
41631 | 889.11 | 54.95 | =B43/B42-1 | =C43/C42-1 | =SIGN(D43) | =SIGN(E43) |
41638 | 999 | 55 | =B44/B43-1 | =C44/C43-1 | =SIGN(D44) | =SIGN(E44) |
hence table