0

I'm new to R and portfolio optimization. I try to produce an inverse matrix of a co-variance table. I have 10 stocks with a rate of returns over 5 days. (I reduced the actual size for the example) Here is the Return Table:

             ID1    ID2     ID3     ID4     ID5     ID6      ID7      ID8   ID9      ID10
3/29/2017   0.0654 -0.0027  0.0025  0.0168  0.4676  0.8515  -0.1152 -0.0298 0       -0.3897
3/30/2017  -0.0326  0      -0.0076  0      -0.5556  0.4907   0.0142 -0.0097 0        0.1392
3/31/2017   0      -0.0054  0.0152 -0.0335  0.2336  0.5856  -0.0577  0     -0.0054  -0.219
4/3/2017    0.0643  0.0027  0.0152  0       0.6429  1.0852   0.0289  0.0287 0        0.267
4/4/2017    0.0318  0       0.005   0.0169 -0.1906 -0.0313  -0.145  -0.0096 0.0027  -1.1001

I then apply the co-variance function:

CovTable <- cov(Returns)

Which produce this table:

     ID1        ID2         ID3         ID4         ID5         ID6         ID7         ID8         ID9         ID10
ID1 0.001791    0.000034    0.000192    0.000408    0.017139    0.007902    -0.000916   0.000108    0.000039    -0.003565
ID2 0.000034    0.000009    -0.000004   0.000034    -0.000036   0.000172    0.000101    0.000034    0.000007    0.000387
ID3 0.000192    -0.000004   0.000092    -0.000096   0.003535    0.001392    0.000027    0.000128    -0.000013   0.000273
ID4 0.000408    0.000034    -0.000096   0.000423    -0.000804   -0.001490   -0.000610   -0.000166   0.000057    -0.004438
ID5 0.017139    -0.000036   0.003535    -0.000804   0.240015    0.152353    0.000960    0.003544    -0.000363   0.056594
ID6 0.007902    0.000172    0.001392    -0.001490   0.152353    0.177324    0.018715    0.003369    -0.000409   0.177301
ID7 -0.000916   0.000101    0.000027    -0.000610   0.000960    0.018715    0.005890    0.001099    -0.000057   0.038787
ID8 0.000108    0.000034    0.000128    -0.000166   0.003544    0.003369    0.001099    0.000454    -0.000009   0.005793
ID9 0.000039    0.000007    -0.000013   0.000057    -0.000363   -0.000409   -0.000057   -0.000009   0.000009    -0.000623
ID10 -0.003565  0.000387    0.000273    -0.004438   0.056594    0.177301    0.038787    0.005793    -0.000623   0.290340

Now if i try to have the reverse with

minverse <- solve(CovTable)

I get this error:

Error in solve.default(covTable) : system is computationally singular: reciprocal condition number = 1.67973e-21

Now, if I use only 5 stocks or less, or any number of stocks that is smaller or equal to the number of days I have in the Return Table, that works perfectly.

So my question is how can I have an inverse matrix of a co-variance table with more stocks than the number of days observed in the return table?

Here is the full code that generates the error:

library(RODBC)
ch <- odbcDriverConnect('driver={SQL Server};server=MyServername\\SQLEXPRESS;database=MyDatabase;trusted_connection=true')
Returns <- sqlFetch(ch, "Returns") # Import Returns Table from SQL to R
Returns <-cast(Returns, Date ~ Id) # Organize Returns Table in Pivot Table
rownames(Returns) <- Returns[,1] # Use the Date Column as Row Headers
Returns <- Returns[,-1] # Remove the Date Column 
CovTable <- cov(Returns) # Create Covariance Matrix
minverse <- solve(CovTable) # Solve CovTable
Rich
  • 271
  • 1
  • 13
  • (No R-user): it looks like this one is for general matrices, right? If so, use the fact that your is symmetric positive semi-definite. So probably [this](https://stat.ethz.ch/R-manual/R-devel/library/base/html/chol.html) and [this](https://stat.ethz.ch/R-manual/R-devel/library/base/html/chol2inv.html) is relevant. – sascha Nov 24 '17 at 16:08
  • @sascha I just copied and pasted the covTable in Excel and applying the excel function `={MINVERSE(covTable)}` I got the result that I'm looking for so I thought `solve()` in R was the equivalent of `MINVERSE()` in Excel which appears to not be the case. Thank you for the links they will indeed be relevant. – Rich Nov 24 '17 at 16:29
  • These kind of solves/inverse-calcs differ from lib to lib. Matlab and Octave usually inspects your matrix and do some automatic choosing, while in python's scipy on the other hand you need to choose yourself. As there are many different approaches (although most are using lapack or some common sparse-variant internally) and often the problem is numerically-unstable, there are often differences (when comparing different environments). – sascha Nov 24 '17 at 16:44
  • I don't understand this question at all - `solve` works fine for me on your covariance table. You need to put `dput(CovTable)` in the question to make this reproducible. – dww Nov 24 '17 at 18:51
  • @dww I'm not sure I understand. You are using `solve(dput(CovTable))` on this covariance table and you dont get this:`Error in solve.default(covTable) : system is computationally singular: reciprocal condition number = 1.67973e-21` ? – Rich Nov 24 '17 at 19:24
  • @dww tx vm solve(dput(CovTable)) works!! – Rich Nov 24 '17 at 19:28
  • That's not what I meant. Please read [**this link**](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example-aka-mcve-minimal-complete-and-ver) to understand what I meant about dput. The fact that `solve(dput(CovTable))` worked but `solve(CovTable)` did not is bizarre in itself but can't be diagnosed if you do not provide enough information. Please consider the link above as **essential** reading before posting R questions on this forum as it is impossible to provide good answers unless you follow the guidelines there. – dww Nov 25 '17 at 01:08
  • @dww I read those usefull links and I edited the question adding the full code in its simplest but complete form. – Rich Nov 28 '17 at 16:04
  • Really? Still no `dput(Returns)` of your data!!! A line of code connecting to a database that only you can access is of no use to anyone. I'll vote to close this question if you can't remedy it soon. – dww Nov 28 '17 at 16:15
  • @dww I thought you wanted to see the original error. Also the table with the numbers are provided a the beginning of the question, I showed the process that get me to the returns table in case it was pertinent. Like I said, I'm a beginner and try to understand the process here, I'm very sorry I'm not up to your expectations but I will try to continue to learn. – Rich Nov 28 '17 at 17:04

0 Answers0