about 8 years ago
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)