I am very new to python, in fact this is my first code in python.
I am attempting to find the covariance matrix for 4 rows of data with each element having a decreasing weight datewise
I have performed the calculations on excel already, and a snapshot of how the data looks is like is below:
| Date |
Obs |
SPX |
DJI |
VIX |
VXD |
SPX |
DJI |
VIX |
VXD |
|
Weights |
| 12-Feb-2016 |
1 |
1864.78 |
15,973.84 |
25.4 |
24.58 |
1.93% |
1.98% |
-10.24% |
-10.82% |
|
0.060000 |
| 11-Feb-2016 |
2 |
1829.08 |
15,660.18 |
28.14 |
27.39 |
-1.24% |
-1.61% |
6.80% |
8.85% |
|
0.056400 |
| 10-Feb-2016 |
3 |
1851.86 |
15,914.74 |
26.29 |
25.07 |
-0.02% |
-0.62% |
-0.95% |
-0.56% |
|
0.053016 |
| 9-Feb-2016 |
4 |
1852.21 |
16,014.38 |
26.54 |
25.21 |
-0.07% |
-0.08% |
2.06% |
2.21% |
|
0.049835 |
| 8-Feb-2016 |
5 |
1853.44 |
16,027.05 |
26 |
24.66 |
-1.43% |
-1.10% |
10.62% |
10.82% |
|
0.046845 |
I need to calculate the covariance matrix in the following format:
| |
SPX |
DJI |
VIX |
VXD |
| SPX |
0.000174 |
0.000170 |
-0.000985 |
-0.000906 |
| DJI |
0.000170 |
0.000175 |
-0.000944 |
-0.000887 |
| VIX |
-0.000985 |
-0.000944 |
0.006568 |
0.005917 |
| VXD |
-0.000906 |
-0.000887 |
0.005917 |
0.005573 |
I have to use python where i have calculated the returns and the weights
to find the covariance matrix.
I would appreciate any help thank you
import pandas as pd
import numpy as np
import math
xl = pd.ExcelFile('path+file.xlsx')
xl.sheet_names
df = xl.parse('Sheet 1')
df['spxr']=np.log(df.SPX/df.SPX.shift(-1))
df['djir']=np.log(df.DJI/df.DJI.shift(-1))
df['vixr']=np.log(df.VIX/df.VIX.shift(-1))
df['vxdr']=np.log(df.VXD/df.VXD.shift(-1))
df['weights']=(0.06)*(0.94**(df.Obs-1))
df=df.fillna(0)
0 Answer(s)