Question

In: Statistics and Probability

Q1) construct a contingency table for the positive and non-positive returns of the two stocks Date...

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.

Solutions

Expert Solution

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


Related Solutions

Consider stocks of two companies A and B, the table below gives their expected returns and...
Consider stocks of two companies A and B, the table below gives their expected returns and standard deviation Expected return for Stock A 10% Expected return for Stock B 25% Standard deviation for Stock A 12% Standard deviation for Stock B 30% Plot the risk and expected return of portfolio of these two stocks for the following correlation coefficient : -1.0,-0.5,0.0,0.5,1.0
Using StatCrunch, construct a labeled contingency table (with row, column, and total %s), as well as...
Using StatCrunch, construct a labeled contingency table (with row, column, and total %s), as well as the contribution to the chi-square statistic, for the following set of data for 300 people:                                                 Group A          Group B          Group C          Total             Had flu shot                     20                   30                    38                   88             Didn’t have flu shot         80                   70                    62                 212             Total                               100                 100                  100               300         Is the value of the chi-square...
The probability distribution of returns for Stocks A and B are given in the table below....
The probability distribution of returns for Stocks A and B are given in the table below. If you invest $1,200,000 in Stock A and $800,000 in Stock B, calculate the expected return of your portfolio. State of Economy Probability of state Stock A's Return Stock B's Return Boom 0.20 40% 28% Normal 0.40 25% 12% Slow Down 0.30 0% 7% Recession 0.10 -20% 0% a.16.00% b.15.2% c.12.8% d.14.6% Group of answer choices
Q1/Given the following information about the returns of stocks A, B, and C, what is the...
Q1/Given the following information about the returns of stocks A, B, and C, what is the expected return of a portfolio invested 30% in stock A, 40% in stock B, and 30% in stock C? State of economy Probability Stock A Stock B Stock C Boom 0.15 0.24 0.31 0.23 Good 0.21 0.19 0.14 0.12 Poor 0.25 0.01 0.08 0.04 Bust -- -0.29 -0.16 -0.19 Enter answer in percents. Q2/GIMP Inc., is trying to determine its cost of debt. The...
Q1/Given the following information about the returns of stocks A, B, and C, what is the...
Q1/Given the following information about the returns of stocks A, B, and C, what is the expected return of a portfolio invested 30% in stock A, 40% in stock B, and 30% in stock C? State of economy Probability Stock A Stock B Stock C Boom 0.12 0.2 0.23 0.37 Good 0.24 0.15 0.12 0.18 Poor 0.25 0 0.08 0.09 Bust -- -0.13 -0.2 -0.18 Enter answer in percents. Q2/A stock has an expected return of 10.2 percent, the risk-free...
A contingency table, also called a two-way table, is made up of r rows and c...
A contingency table, also called a two-way table, is made up of r rows and c columns. In general, how many cells are in a contingency table with r rows and c columns? 1) r x c (r times c) 2) (r-1)(c-1) 3) r + c In general, how many degrees of freedom are associated with a contingency table with r rows and c columns? 1) r + c 2) r times c 3) (r-1)(c-1) When should Fishers Exact Test...
The following table shows the nominal returns on Brazilian stocks and the rate of inflation. Year...
The following table shows the nominal returns on Brazilian stocks and the rate of inflation. Year Nominal Return (%) Inflation (%) 2012 0.4 7.4 2013 -17.0 7.5 2014 -15.0 8.0 2015 -43.0 12.3 2016 67.8 7.9 2017 28.5 4.5 a. What was the standard deviation of the market returns? (Use decimals, not percents, in your calculations. Do not round intermediate calculations. Enter your answer as a percent rounded to 2 decimal places.) b. Calculate the average real return. (A negative...
Stocks A and B have the expected returns and standard deviations shown in the table below:...
Stocks A and B have the expected returns and standard deviations shown in the table below: Asset E(R) Std. deviation A 15% 30% B 20% 50% The correlation between A and B is 0.6. The risk-free rate is 3% and you have a risk-aversion parameter of 2. What is the proportion of your investment in A and B, respectively, in your optimal risky portoflio? A. 25.0% in A ; 75.0% in B B. 76.6% in A; 23,4% in B C....
The following table contains the historic returns from a portfolio consisting of large stocks and a...
The following table contains the historic returns from a portfolio consisting of large stocks and a portfolio consisting of long-term Treasury bonds over the last 20 years. T-bills returns represent risk-free returns. Analyze the risk-return trade-off that would have characterized these portfolios. The following dataset is also available in Excel format in Module 3 Resources on Canvas. Returns in the dataset are in percents. For example, 31.33 means 31.33% per year. Year Large Stock Long-Term T-Bonds T-Bills 1997 31.33 11.312...
The following table contains the historic returns from a portfolio consisting of large stocks and a...
The following table contains the historic returns from a portfolio consisting of large stocks and a portfolio consisting of long-term Treasury bonds over the last 20 years. T-bills returns represent risk-free returns. Analyze the risk-return trade-off that would have characterized these portfolios. The following dataset is also available in Excel format in Module 3 Resources on Canvas. Returns in the dataset are in percents. For example, 31.33 means 31.33% per year. Year Large Stock Long-Term T-Bonds T-Bills 1997 31.33 11.312...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT