Microsoft, Apple and Google, descriptive, correlation matrix

Date Google Close Microsoft Close Apple Close
31/12/2009 619.98 30.34 210.73
30/12/2009 622.73 30.82 211.64
29/12/2009 619.4 31.24 209.1
28/12/2009 622.87 31.02 211.61
24/12/2009 618.48 30.86 209.04
23/12/2009 611.68 30.78 202.1
22/12/2009 601.12 30.68 200.36
21/12/2009 598.68 30.38 198.23
18/12/2009 596.42 30.22 195.43
17/12/2009 593.94 29.46 191.86
16/12/2009 597.76 29.96 195.03
15/12/2009 593.14 29.88 194.17
14/12/2009 595.73 29.97 196.98
11/12/2009 590.51 29.71 194.67
10/12/2009 591.5 29.73 196.43
09/12/2009 589.02 29.57 197.8
08/12/2009 587.05 29.43 189.87
07/12/2009 586.25 29.65 188.95
04/12/2009 585.01 29.84 193.32
03/12/2009 585.74 29.69 196.48
02/12/2009 587.51 29.64 196.23
01/12/2009 589.87 29.87 196.97
30/11/2009 583 29.27 199.91
27/11/2009 579.76 29.08 200.59
25/11/2009 585.74 29.65 204.19
24/11/2009 583.09 29.77 204.44
23/11/2009 582.35 29.8 205.88
20/11/2009 569.96 29.48 199.92
19/11/2009 572.99 29.64 200.51
18/11/2009 576.65 29.97 205.96
17/11/2009 577.49 29.86 207
16/11/2009 576.28 29.27 206.63
13/11/2009 572.05 29.36 204.45
12/11/2009 567.85 29.09 201.99
11/11/2009 570.56 28.86 203.25
10/11/2009 566.76 28.75 202.98
09/11/2009 562.51 28.73 201.46
06/11/2009 551.1 28.26 194.34
05/11/2009 548.65 28.21 194.03
04/11/2009 540.33 27.81 190.81
03/11/2009 537.29 27.28 188.75
02/11/2009 533.99 27.63 189.31
30/10/2009 536.12 27.48 188.5
29/10/2009 551.05 27.97 196.35
28/10/2009 540.3 27.77 192.4
27/10/2009 548.29 28.33 197.37
26/10/2009 554.21 28.42 202.48
23/10/2009 553.69 27.77 203.94
22/10/2009 554.09 26.35 205.2
21/10/2009 551.1 26.34 204.92
20/10/2009 551.72 26.13 198.76
19/10/2009 552.09 26.12 189.86
16/10/2009 549.85 26.26 188.05
15/10/2009 529.91 26.47 190.56
14/10/2009 535.32 25.73 191.29
13/10/2009 526.11 25.58 190.02
12/10/2009 524.04 25.49 190.81
09/10/2009 516.25 25.32 190.47
08/10/2009 514.18 25.44 189.27
07/10/2009 517.54 24.87 190.25
06/10/2009 498.74 24.88 190.01
05/10/2009 488.52 24.42 186.02
02/10/2009 484.58 24.73 184.9
01/10/2009 487.2 24.66 180.86
30/09/2009 495.85 25.49 185.35
29/09/2009 498.53 25.52 185.38
28/09/2009 498.53 25.6 186.15
25/09/2009 492.48 25.32 182.37
24/09/2009 496.77 25.71 183.82
23/09/2009 498.46 25.48 185.5
22/09/2009 499.06 25.54 184.48
21/09/2009 497 25.07 184.02
18/09/2009 491.46 25.03 185.02
17/09/2009 491.72 25.07 184.55
16/09/2009 488.29 24.97 181.87
15/09/2009 477.54 24.97 175.16
14/09/2009 475.12 24.77 173.72
11/09/2009 472.14 24.64 172.16
10/09/2009 470.94 24.77 172.56
09/09/2009 463.97 24.56 171.14
08/09/2009 458.62 24.6 172.93
04/09/2009 461.3 24.4 170.31
03/09/2009 457.52 23.89 166.55
02/09/2009 453.01 23.64 165.18
01/09/2009 455.76 23.78 165.3
31/08/2009 461.67 24.43 168.21
28/08/2009 464.75 24.46 170.05
27/08/2009 466.06 24.47 169.45
26/08/2009 468 24.33 167.41
25/08/2009 471.37 24.42 169.4
24/08/2009 468.73 24.42 169.06
21/08/2009 465.24 24.19 169.22
20/08/2009 460.41 23.46 166.33
19/08/2009 443.97 23.44 164.6
18/08/2009 445.28 23.37 164
17/08/2009 444.89 22.91 159.59
14/08/2009 460 23.34 166.78
13/08/2009 462.28 23.28 168.42
12/08/2009 458.58 23.19 165.31
11/08/2009 453.94 22.79 162.83
10/08/2009 456.61 23.08 164.72
07/08/2009 457.1 23.22 165.51
06/08/2009 450.36 23.12 163.91
05/08/2009 451.14 23.46 165.11
04/08/2009 453.73 23.42 165.55
03/08/2009 452.21 23.48 166.43
31/07/2009 443.05 23.18 163.39
30/07/2009 445.64 23.46 162.79
29/07/2009 436.24 23.45 160.03
28/07/2009 439.85 23.13 160
27/07/2009 444.8 22.77 160.1
24/07/2009 446.72 23.11 159.99
23/07/2009 437.34 25.19 157.82
22/07/2009 427.69 24.44 156.74
21/07/2009 427.9 24.47 151.51
20/07/2009 430.17 24.17 152.91
17/07/2009 430.25 23.94 151.75
16/07/2009 442.6 24.08 147.52
15/07/2009 438.17 23.77 146.88
14/07/2009 424.69 22.77 142.27
13/07/2009 424.3 22.89 142.34
10/07/2009 414.4 22.06 138.52
09/07/2009 410.39 22.11 136.36
08/07/2009 402.49 22.23 137.22
07/07/2009 396.63 22.2 135.4
06/07/2009 409.61 22.86 138.61
02/07/2009 408.49 23.03 140.02
01/07/2009 418.99 23.69 142.83
30/06/2009 421.59 23.42 142.43
29/06/2009 424.14 23.51 141.97
26/06/2009 425.32 23.01 142.44
25/06/2009 415.77 23.44 139.86
24/06/2009 409.29 23.13 136.22
23/06/2009 405.68 23 134.01
22/06/2009 407.35 22.94 137.37
19/06/2009 420.09 23.72 139.48
18/06/2009 414.06 23.16 135.88
17/06/2009 415.16 23.33 135.58
16/06/2009 416 23.11 136.35
15/06/2009 416.77 23.08 136.09
12/06/2009 424.84 22.99 136.97
11/06/2009 429 22.5 139.95
10/06/2009 432.6 22.22 140.25
09/06/2009 435.62 21.76 142.72
08/06/2009 438.77 21.73 143.85
05/06/2009 444.32 21.82 144.67
04/06/2009 440.28 21.51 143.74
03/06/2009 431.65 21.41 140.95
02/06/2009 428.4 21.09 139.49
01/06/2009 426.56 21.09 139.35
29/05/2009 417.23 20.59 135.81
28/05/2009 410.4 20.15 135.07
27/05/2009 405.56 19.84 133.05
26/05/2009 404.36 20.04 130.78
22/05/2009 393.5 19.46 122.5
21/05/2009 396.5 19.53 124.18
20/05/2009 397.18 20.08 125.87
19/05/2009 398.88 20.01 127.45
18/05/2009 396.84 20.17 126.65
15/05/2009 390 19.8 122.42
14/05/2009 387.5 19.64 122.95
13/05/2009 389.54 19.34 119.49
12/05/2009 399.01 19.48 124.42
11/05/2009 407.98 18.92 129.57
08/05/2009 407.33 19.02 129.19
07/05/2009 396.61 18.92 129.06
06/05/2009 403.47 19.38 132.5
05/05/2009 402.99 19.38 132.71
04/05/2009 401.98 19.77 132.07
01/05/2009 393.69 19.82 127.24
30/04/2009 395.97 19.84 125.83
29/04/2009 391.47 19.83 125.14
28/04/2009 383.71 19.52 123.9
27/04/2009 385.95 19.98 124.73
24/04/2009 389.49 20.48 123.9
23/04/2009 384.69 18.53 125.4
22/04/2009 383.86 18.39 121.51
21/04/2009 381.47 18.58 121.76
20/04/2009 379.3 18.22 120.5
17/04/2009 392.24 18.8 123.42
16/04/2009 388.74 19.35 121.45
15/04/2009 379.5 18.44 117.64
14/04/2009 368.91 18.95 118.31
13/04/2009 378.11 19.18 120.22
09/04/2009 372.5 19.26 119.57
08/04/2009 362 18.79 116.32
07/04/2009 358.65 18.37 115
06/04/2009 368.24 18.37 118.45
03/04/2009 369.78 18.36 115.99
02/04/2009 362.5 18.89 112.71
01/04/2009 354.09 18.91 108.69
31/03/2009 348.06 17.99 105.12
30/03/2009 342.69 17.12 104.49
27/03/2009 347.7 17.75 106.85
26/03/2009 353.29 18.44 109.87
25/03/2009 344.07 17.51 106.49
24/03/2009 347.17 17.56 106.5
23/03/2009 348.6 17.95 107.66
20/03/2009 330.16 16.71 101.59
19/03/2009 329.94 16.78 101.62
18/03/2009 333.1 16.61 101.52
17/03/2009 335.34 16.55 99.66
16/03/2009 319.69 15.91 95.42
13/03/2009 324.42 16.3 95.93
12/03/2009 323.53 16.66 96.35
11/03/2009 317.91 16.75 92.68
10/03/2009 308.17 16.14 88.63
09/03/2009 290.89 14.83 83.11
06/03/2009 308.57 14.96 85.3
05/03/2009 305.64 14.95 88.84
04/03/2009 318.92 15.78 91.17
03/03/2009 325.48 15.55 88.37
02/03/2009 327.16 15.46 87.94
27/02/2009 337.99 15.81 89.31
26/02/2009 337.18 16.08 89.19
25/02/2009 341.64 16.61 91.16
24/02/2009 345.45 16.81 90.25
23/02/2009 330.06 16.85 86.95
20/02/2009 346.45 17.63 91.2
19/02/2009 342.64 17.54 90.64
18/02/2009 353.11 17.74 94.37
17/02/2009 342.66 17.71 94.53
13/02/2009 357.68 18.57 99.16
12/02/2009 363.05 18.73 99.27
11/02/2009 358.04 18.68 96.82
10/02/2009 358.51 18.28 97.83
09/02/2009 378.77 18.91 102.51
06/02/2009 371.28 19.12 99.72
05/02/2009 353.72 18.52 96.46
04/02/2009 343 18.12 93.55
03/02/2009 340.45 17.99 92.98
02/02/2009 340.57 17.34 91.51
30/01/2009 338.53 16.63 90.13
29/01/2009 343.32 17.11 93
28/01/2009 348.67 17.54 94.2
27/01/2009 331.48 17.18 90.73
26/01/2009 323.87 17.15 89.64
23/01/2009 324.7 16.73 88.36
22/01/2009 306.5 16.64 88.36
21/01/2009 303.08 18.85 82.83
20/01/2009 282.75 17.97 78.2
16/01/2009 299.67 19.17 82.33
15/01/2009 298.99 18.71 83.38
14/01/2009 300.97 18.57 85.33
13/01/2009 314.32 19.28 87.71
12/01/2009 312.69 18.94 88.66
09/01/2009 315.07 18.98 90.58
08/01/2009 325.19 19.57 92.7
07/01/2009 322.01 18.97 91.01
06/01/2009 334.06 20.19 93.02
05/01/2009 328.05 19.96 94.58
02/01/2009 321.32 19.77 90.75

Descriptive Statistics

1. Calculate the descriptive statistics for all three data series (in Data tab), using the Descriptive Statistics tool in Excel (Data > Data Anlaysis > Descriptive Statistics)

2. Calculate the mean (=average), standard deviation (=stdev), min, and max for these three series using Excel functions, to compare with the output from Part 1.

3. Calculate the correlation matrix for these three series using the Correlation tool (Data > Data Anlysis > Correlation). Which two series are most highly correlated?

Plotting

1. Plot Microsoft and Apple as a line plot.

2. Add a secondary axis for one of these series, so you can better see the dynamics of each (right-click on data in plot > Format Data Series > Series Options > Secondary Axis).

3. These data are ploted with time moving backward as the X-axis increases. Reorder these data to plot time as an increasing variable. Create a column to rank order these (or number the days). Sort entire data array (highlight data, with headings > Data > Sort > Sort By > Rank) according to Days/Rank so time travels forward in the data. Re-plot the sorted data.

Random Variables

1. Generate 200 Uniform(0,1) random variables (=rand()).

2. Convert these to Uniform(5,15) random variables (multiply by 10, add 5).

3. Build a frequency table for the U(5,15) random variables. Use Bin increments running from 5 to 15, with unit spacing (i.e. Bins 5, 6, 7, ..., 15). Use the frequency function (an array funciton) to count the number of random variables in each bin. Highlight the entire frequency array you wan to fill in, press =, type frequency, and fill in the function. When it's filled in, press Ctrl + Shift + Enter to populate the entire array.

4. Find the total number of observations (it should be 200).

5. Calculate the probability for each bin (Frequency/Total), and plot this using a bar graph.

6. Calculate the CDF (cumulative distribution function) by accumulating probabiilty from the smallest bin to the largest. Plot the CDF using a bar graph.

Random Variables

1. Repeat all the steps from Random Variables and Histograms (previous tab), but this time for a Normal(50, 10) random variable. Choose appropriate bin increments to display the distribution.

To generate a Normal(mu,sigma) random variable, use

=mu + normsinv(rand())*sigma