Can't compute inverse of matrix by MINVERSE or User-Function VBA

[vinhhuynh]

I have a 50x50 matrix in sheet1. I can't compute inverse of matrix by MINVERSE of Excel or User-Function VBA. But I can compute it with a 50x50 matrix in sheet2 (Random Matrix) by two functions.
Moreover, I can compute a 10x10 matrix of matrix in sheet1 with two functions.
Can you explain and resolve my problem in Excel VBA?

Best Regards,
Vinh

 

[Keith Tan]

determinant of matrix is 0?

 

[vinhhuynh]

I don't know and can't test this. But 50x50 matrix in sheet1 is the covariance-variance matrix. Because if we use MINVERSE function of excel, 50x50 matrix results to "#NUM". I think that results are too large and excel can solve it despite of Excel VBA.

Please help me solve this problem.

Best Regards,
Vinh

 

[Daniel Duffy]

Normally in applications you never need to invert matrices but rather solve things like Ax = b where A is a matrix.
Is there a reason for wanting to have the inverse in explicit form then?

 

[vinhhuynh]

I am implementing the portfolio management modeling. So that I need to invert variance-covariance matrix.
Besides, I heard from my professor that portfolio management modeling without inverse of variance-covariance matrix is no mean and useless.

I never program in C# or the other languages, but only on Excel VBA. I don't know great mathematics procedure in C# or ... can compute the inverse of variance-covariance matrix that the value of variance-covariance matrix are very small like my Excel File.

Best Regards,
Vinh Huynh

 

[vinhhuynh]

... Not only variance-covariance matrix need to invert, but also many other types of matrices in Portfolio Management need to invert to implement portfolio management modeling.

 

[Keith Tan]

I don't know and can't test this. But 50x50 matrix in sheet1 is the covariance-variance matrix. Because if we use MINVERSE function of excel, 50x50 matrix results to "#NUM". I think that results are too large and excel can solve it despite of Excel VBA.

Please help me solve this problem.

Best Regards,
Vinh

u can use excel MDETRM to get the determinant of the matrix.

A-1 x A = I, should give you the identity matrix I

you have too many negative numbers. it's like 1 divide by 0.

http://www.mathsisfun.com/algebra/matrix-inverse.html

 

[vinhhuynh]

Hi Keith Tan, I understood your explain. I checked, I think that det(A) is too too too small to express in Double variable.
So that do you think we need a double variable 256 or 512 bit to compute it.
Any other solutions for my problem about inverse of matrix ?

Best Regards,
Vinh

 

[Daniel Duffy]

You might find this lecture by Prof Strang useful
http://ocw.mit.edu/courses/mathemat...re-20-cramers-rule-inverse-matrix-and-volume/

Also ... Check the input e.g. is your matrix diagonally dominant? (your matrix may _not_ be dd and then there is an issue ...)

http://en.wikipedia.org/wiki/Diagonally_dominant_matrix

Cramer's rule (N.B. O(n^3) complexity ....)

http://en.wikipedia.org/wiki/Cramer's_rule

 

[Daniel Duffy]

Kind of got me interested ...

http://web.eecs.utk.edu/~itamar/Papers/JDA2011.pdf

 

[vinhhuynh]

Hi Daniel Duffy, by the way, I want to ask you a question: "C# or C++ are suitable for Finance and Investment Programming? Especially for Portfolio Management Application with communication with stock exchange, getting data from internet, and processing data with large scale, inverse matrix, many optimization approaches and others. "
I am planning for studying C# for Finance and Investment. You can guide me that tasks of C# or C++ that I have to study to create applications in Portfolio Management, Financial Risk Management, and Financial Analysis.

Best Regards,
Vinh

 

[Daniel Duffy]

Hi Daniel Duffy, by the way, I want to ask you a question: "C# or C++ are suitable for Finance and Investment Programming? Especially for Portfolio Management Application with communication with stock exchange, getting data from internet, and processing data with large scale, inverse matrix, many optimization approaches and others. "
I am planning for studying C# for Finance and Investment. You can guide me that tasks of C# or C++ that I have to study to create applications in Portfolio Management, Financial Risk Management, and Financial Analysis.

Best Regards,
Vinh

Hi Vinh,
You can use both, but I do see C# being useful for these kinds of applications. Here are some impressions that might help to give a more complete answer:

http://www.moneyscience.com/pg/blog...cial-markets-an-interview-with-daniel-j-duffy

Let me know if you have any queries.
hth

 

[vinhhuynh]

Hi, Daniel Duffy. The materials that you give, especially paper I think that it is hard to me because I have not a degree in mathematics.
So that, I want to know my inverse matrix problem that has any solutions. If any, give me details in VBA or programming codes.

Best Regards,
Vinh

 

[Daniel Duffy]

Sorry, don't know

Try "Cramer's rule in VBA"

 

[DAVID TA]

Hi, Daniel Duffy. The materials that you give, especially paper I think that it is hard to me because I have not a degree in mathematics.
So that, I want to know my inverse matrix problem that has any solutions. If any, give me details in VBA or programming codes.

Best Regards,
Vinh

I 'm also interested in the way to compute the output of the inverse of a matrix by Excel to solve the optimization portfolio in finance. I just suggest you using Matlab for the problem and if you found the way to calculate the inverse by Excel, you could recommend this to me.

 

[Daniel Duffy]

Why do you need to compute an inverse? No one does that anymore..

I could be wrong, but can you solve a matrix system Ax = b?

You need to formulate the problem first and then decide how to solve it.

 

[DAVID TA]

Thanks for your answer. Actually, my research is related to applying to LASOO in portfolio optimization, the algorithm requires a precision matrix( the inverse of a matrix). Application of Graphic LASSO in Portfolio Optimization_Yixuan Chen & …
but I want to build step by step by Excel tool.