wk888888 project

profilehelpothers
Final_WFC_stockPrices.xlsx
Home>Computer Science homework help>wk888888 project

stocksReturns

date close.WF close.MK ExpectedReturn.WF (%age) ExpectedReturn.MK (%age)
9/1/15 50.99 1913.849976
9/2/15 51.99 1948.859985 1.9611688566 1.82929746
9/3/15 52.43 1951.130005
9/4/15 51.29 1921.219971
9/8/15 52.93 1969.410034
9/9/15 52.19 1942.040039
9/10/15 52.56 1952.290039
9/11/15 52.62 1961.050049
9/14/15 52.77 1953.030029
9/15/15 53.47 1978.089966
9/16/15 53.72 1995.310059
9/17/15 52.21 1990.199951
9/18/15 51.04 1958.030029
9/21/15 51.55 1966.969971
9/22/15 50.69 1942.73999
9/23/15 50.78 1938.76001
9/24/15 50.55 1932.23999
9/25/15 51.48 1931.339966
9/28/15 50.37 1881.77002
9/29/15 50.89 1884.089966
9/30/15 51.35 1920.030029
10/1/15 51.44 1923.819946
10/2/15 51.26 1951.359985
10/5/15 52.41 1987.050049
10/6/15 52.03 1979.920044
10/7/15 52.19 1995.829956
10/8/15 52.54 2013.430054
10/9/15 52.14 2014.890015
10/12/15 52.18 2017.459961
10/13/15 51.86 2003.689941
10/14/15 51.5 1994.23999
10/15/15 52.69 2023.859985
10/16/15 52.88 2033.109985
10/19/15 52.56 2033.660034
10/20/15 53.08 2030.77002
10/21/15 53.12 2018.939941
10/22/15 54.06 2052.51001
10/23/15 54.75 2075.149902
10/26/15 54.29 2071.179932
10/27/15 54.12 2065.889893
10/28/15 55.45 2090.350098
10/29/15 55.09 2089.409912
10/30/15 54.14 2079.360107
11/2/15 54.85 2104.050049
11/3/15 54.93 2109.790039
11/4/15 54.58 2102.310059
11/5/15 54.86 2099.929932
11/6/15 55.85 2099.199951
11/9/15 55.47 2078.580078
11/10/15 55.91 2081.719971
11/11/15 55.8 2075
11/12/15 55.17 2045.969971
11/13/15 54.57 2023.040039
11/16/15 55.27 2053.189941
11/17/15 54.96 2050.439941
11/18/15 55.67 2083.580078
11/19/15 55.97 2081.23999
11/20/15 55.82 2089.169922
11/23/15 55.6 2086.590088
11/24/15 55.28 2089.139893
11/25/15 55.22 2088.870117
11/27/15 55.39 2090.110107
11/30/15 55.1 2080.409912
12/1/15 55.71 2102.629883
12/2/15 55.08 2079.51001
12/3/15 54.2 2049.620117
12/4/15 55.67 2091.689941
12/7/15 55.42 2077.070068
12/8/15 54.4 2063.590088
12/9/15 54.12 2047.619995
12/10/15 54.34 2052.22998
12/11/15 53.31 2012.369995
12/14/15 53.2 2021.939941
12/15/15 54.91 2043.410034
12/16/15 55.85 2073.070068
12/17/15 55.47 2041.890015
12/18/15 53.79 2005.550049
12/21/15 54.02 2021.150024
12/22/15 54.34 2038.969971
12/23/15 55.04 2064.290039
12/24/15 54.82 2060.98999
12/28/15 54.68 2056.5
12/29/15 55.29 2078.360107
12/30/15 54.89 2063.360107
12/31/15 54.36 2043.939941
1/4/16 52.91 2012.660034
1/5/16 52.89 2016.709961
1/6/16 51.88 1990.26001
1/7/16 50.4 1943.089966
1/8/16 49.56 1922.030029
1/11/16 50.09 1923.670044
1/12/16 51.36 1938.680054
1/13/16 49.73 1890.280029
1/14/16 50.64 1921.839966
1/15/16 48.82 1880.329956
1/19/16 48.21 1881.329956
1/20/16 47.87 1859.329956
1/21/16 48.01 1868.98999
1/22/16 49.02 1906.900024
1/25/16 47.66 1877.079956
1/26/16 48.26 1903.630005
1/27/16 48.57 1882.949951
1/28/16 48.92 1893.359985
1/29/16 50.23 1940.23999
2/1/16 49.94 1939.380005
2/2/16 48.83 1903.030029
2/3/16 47.6 1912.530029
2/4/16 48.25 1915.449951
2/5/16 47.86 1880.050049
2/8/16 46.5 1853.439941
2/9/16 46.45 1852.209961
2/10/16 46.17 1851.859985
2/11/16 45.16 1829.079956
2/12/16 47.31 1864.780029
2/16/16 48.24 1895.579956
2/17/16 48.13 1926.819946
2/18/16 47.73 1917.829956
2/19/16 48.09 1917.780029
2/22/16 49.19 1945.5
2/23/16 48.1 1921.27002
2/24/16 47.61 1929.800049
2/25/16 47.75 1951.699951
2/26/16 48.07 1948.050049
2/29/16 46.92 1932.22998
3/1/16 48.72 1978.349976
3/2/16 49.57 1986.449951
3/3/16 49.77 1993.400024
3/4/16 50.11 1999.98999
3/7/16 50.07 2001.76001
3/8/16 49.05 1979.26001
3/9/16 48.79 1989.26001
3/10/16 48.51 1989.569946
3/11/16 50.07 2022.189941
3/14/16 49.88 2019.640015
3/15/16 49.98 2015.930054
3/16/16 49.54 2027.219971
3/17/16 49.73 2040.589966
3/18/16 50.54 2049.580078
3/21/16 50.67 2051.600098
3/22/16 50.37 2049.800049
3/23/16 49.76 2036.709961
3/24/16 48.9 2035.939941
3/28/16 48.7 2037.050049
3/29/16 48.05 2055.01001
3/30/16 48.65 2063.949951
3/31/16 48.36 2059.73999
4/1/16 48.45 2072.780029
4/4/16 48.5 2066.129883
4/5/16 47.51 2045.170044
4/6/16 48.08 2066.659912
4/7/16 46.93 2041.910034
4/8/16 47.07 2047.599976
4/11/16 47.03 2041.98999
4/12/16 47.77 2061.719971
4/13/16 49.03 2082.419922
4/14/16 48.79 2082.780029
4/15/16 48.25 2080.72998
4/18/16 48.84 2094.340088
4/19/16 49.88 2100.800049
4/20/16 50.45 2102.399902
4/21/16 50.05 2091.47998
4/22/16 50.62 2091.580078
4/25/16 50.51 2087.790039
4/26/16 50.92 2091.699951
4/27/16 50.93 2095.149902
4/28/16 50.41 2075.810059
4/29/16 49.98 2065.300049
5/2/16 50.59 2081.429932
5/3/16 50 2063.370117
5/4/16 48.94 2051.120117
5/5/16 48.96 2050.629883
5/6/16 49.02 2057.139893
5/9/16 48.88 2058.689941
5/10/16 49.4 2084.389893
5/11/16 49.08 2064.459961
5/12/16 49.2 2064.110107
5/13/16 48.24 2046.609985
5/16/16 48.27 2066.659912
5/17/16 47.62 2047.209961
5/18/16 48.65 2047.630005
5/19/16 48.38 2040.040039
5/20/16 48.75 2052.320068
5/23/16 48.7 2048.040039
5/24/16 49.2 2076.060059
5/25/16 50.5 2090.540039
5/26/16 50.55 2090.100098
5/27/16 50.85 2099.060059
5/31/16 50.72 2096.949951
6/1/16 51 2099.330078
6/2/16 51.11 2105.26001
6/3/16 50.19 2099.129883
6/6/16 50.49 2109.409912
6/7/16 50.27 2112.129883
6/8/16 50 2119.120117
6/9/16 49.14 2115.47998
6/10/16 48.34 2096.070068
6/13/16 47.97 2079.060059
6/14/16 46.88 2075.320068
6/15/16 46.78 2071.5
6/16/16 46.85 2077.98999
6/17/16 46.6 2071.219971
6/20/16 46.93 2083.25
6/21/16 47.23 2088.899902
6/22/16 46.97 2085.449951
6/23/16 47.91 2113.320068
6/24/16 45.71 2037.410034
6/27/16 45.01 2000.540039
6/28/16 46.1 2036.089966
6/29/16 46.97 2070.77002
6/30/16 47.33 2098.860107
7/1/16 47.03 2102.949951
7/5/16 46.21 2088.550049
7/6/16 46.65 2099.72998
7/7/16 46.8 2097.899902
7/8/16 47.79 2129.899902
7/11/16 48.08 2137.159912
7/12/16 48.35 2152.139893
7/13/16 48.27 2152.429932
7/14/16 48.94 2163.75
7/15/16 47.71 2161.73999
7/18/16 48.28 2166.889893
7/19/16 48.37 2163.780029
7/20/16 48.61 2173.02002
7/21/16 48.3 2165.169922
7/22/16 48.32 2175.030029
7/25/16 48.12 2168.47998
7/26/16 47.97 2169.179932
7/27/16 48 2166.580078
7/28/16 48.13 2170.060059
7/29/16 47.97 2173.600098
8/1/16 47.81 2170.840088
8/2/16 47.7 2157.030029
8/3/16 47.57 2163.790039
8/4/16 47.84 2164.25
8/5/16 48.68 2182.870117
8/8/16 48.91 2180.889893
8/9/16 48.93 2181.73999
8/10/16 48.18 2175.48999
8/11/16 48.24 2185.790039
8/12/16 47.9 2184.050049
8/15/16 48.27 2190.149902
8/16/16 48.44 2178.149902
8/17/16 48.61 2182.219971
8/18/16 48.53 2187.02002
8/19/16 48.65 2183.870117
8/22/16 48.63 2182.639893
8/23/16 48.41 2186.899902
8/24/16 48.63 2175.439941
8/25/16 48.38 2172.469971
8/26/16 48.51 2169.040039
8/29/16 49.56 2180.379883
8/30/16 50.62 2176.120117
8/31/16 50.8 2170.949951
9/1/16 50.43 2170.860107
9/2/16 50.55 2179.97998
9/6/16 49.99 2186.47998
9/7/16 49.77 2186.159912
9/8/16 49.9 2181.300049
9/9/16 48.72 2127.810059
9/12/16 48.54 2159.040039
9/13/16 46.96 2127.02002
9/14/16 46.52 2125.77002
9/15/16 46.15 2147.26001
9/16/16 45.43 2139.159912
9/19/16 46.01 2139.120117
9/20/16 46.56 2139.76001
9/21/16 45.83 2163.120117
9/22/16 45.72 2177.179932
9/23/16 45.74 2164.689941
9/26/16 44.88 2146.100098
9/27/16 45.09 2159.929932
9/28/16 45.31 2171.370117
9/29/16 44.37 2151.129883
9/30/16 44.28 2168.27002
10/3/16 43.83 2161.199951
10/4/16 43.75 2150.48999
10/5/16 44.99 2159.72998
10/6/16 45.18 2160.77002
10/7/16 45.33 2153.73999
10/10/16 45.65 2163.659912

Correlations and Betas

Average Monthly Return (Wells Fargo) =AVERAGE(stocksReturns!D3:D281)
Average Monthly Return (Market) =AVERAGE(stocksReturns!E3:E281)
Sum product of deviations =SUM(stocksReturns!#REF!)
No of observations (n) 279
DF (n - 1) 278
Covariance = Sum of product of deviations / n - 1 ERROR:#VALUE! Covar = Sigma(X-Ux) ( Y- Uy)/n - 1
Sum of Squared Deviation WF ERROR:#REF!
Sum of Squared Deviation Market ERROR:#REF!
Standard deviation (SD)
Variance WF = Sum Squared Deviation WF / (n-1) ERROR:#REF! ERROR:#REF! (SD RW) We are getting in fraction because we had converted %age back in decimals during calculations
Variance Market = Sum Squared Deviation Market / (n-1) ERROR:#REF! ERROR:#REF! (SD RM)
Corr (RW, RM) = Cov(RW, RM)/SD(WC) x SD (RM) ERROR:#VALUE!
Beta ERROR:#VALUE!
Beta = Correlation (Firm's Expected Returns x Market Expected Returns/Market Standard Deviation)
Beta = Slope (Firm's Fargo Expected Return, Market Expected Return Standard Deviation)
Use EXCEL function = Slope (Wells Fargo Expected Return, Market Expected Return Standard Deviation)
Market Beta =SLOPE(stocksReturns!D3:D281,stocksReturns!E3:E281)
Corr (ERWellsFargo, ERMarket) =CORREL(stocksReturns!D3:D281,stocksReturns!E3:E281)
Standard deviation (Wells Fargo) =STDEV.S(stocksReturns!D3:D281) ERROR:#VALUE! Non %
Standard deviation (Market) =STDEV.S(stocksReturns!E3:E281) ERROR:#VALUE! Non %

Risk Estimation

ρ λ,s = Correlation (ERW, Sentiment) -0.05697226
ρ λ,m = Correlation (ERW, Market ER) 0.7822005106
Standard deviation (SD -W) 1.3970401501 0.0139704015
Standard deviation (SD -M) 0.93030869 0.0093030869
σλ = ρλ,m * σλ + σλ (1 – ρλ,m – ρλ,s) + ɛ
Unsystematic risk = σλ (1 – ρ λ, m – ρ λ,s)
Total Voltality = 1 - Correlatio(Expected Return, Market Expected Return) - Correlatio(Expected Return, Sentiment score)
σλ = (1 – ρ λ, m – ρ λ,s)

Annual data - VaR-CVaR

Count 0 Chance
Var(95) 0 ERROR:#REF! CVAR(95) ERROR:#DIV/0! 5%
Var(99) 0 ERROR:#REF! CVAR(99) ERROR:#DIV/0! 1%
Var(99.9) 0 ERROR:#REF! CVar(99.9) ERROR:#DIV/0! 0.1 %
Actual Loss -1.80% 9/26/16
Conditional Var:
Conditional VaR (C-VaR) = (1/VaR)*SUM(Sum all Historical VaR values in VaR) VaR
Example: Number Count = 14 Number Position Value at Position CVaR C-VaR Value
Sorted ER Position Var(95) 1.25 5.00 CVAR(95) 3.20 =1/VaR(95) * sum (from top to position 1.25)
4 1 Var(99) 0.25 4.00 CVAR(99) 0.00 =1/VaR(99) * sum (from top to position 0.25)
6 2 Var(99.9) 0.025 0.10 CVar(99.9) 0.00 =1/VaR(99.9) * sum (from top to position 0.025)
7 3
8 4
9 5
10 6
11 7
12 8
14 9
15 10
17 11
18 12
19 13
20 14
22 15
24 16
25 17
26 18
27 19
28 20
29 21
30 22
32 23
34 24
35 25

Var- Sentiment Stocks Data

Count 0
Var(95) 0 ERROR:#REF! CVAR(95) ERROR:#DIV/0! 5%
-0.05