Jobs By Location
Jobs by Programming
Jobs by Role
Jobs by Sector
Subscribe to RSS Feeds
| Category: Excel |
View Full Details
|
 SVD Singular Value Decomposition of a matrix in Excel |
| Submitter: vanna |
Date: 2010/11/30 |
|
|
Description:
Some FAQs to help understand the code and it's usage: What does this spreadsheet do?This spreadsheet has code to perform SVD on any given matrix in VBA. There is a class called "SVD" which could be used for the purpose of doing SVD. What is SVD of a matrix?It is decomposition of a matrix A into 3 other matrices U, S and V such that following condition is satisfied: \[ A = U S V' \] How do I do SVD on a matrix?An example code to use the SVD class is written in onclick function of the button. Following is the code:
Private Sub CommandButton1_Click()
'read input matrix for which we need to do SVD
a = ReadMatrix("sheet1", 7, 9, 3, 6)
Dim svdObj As New SVD
svdObj.SetMatrix a
sMat = svdObj.GetS()
uMat = svdObj.GetU()
vMat = svdObj.GetV()
'display output result
ShowMatrix "sheet1", 3, 9, uMat
ShowMatrix "sheet1", 12, 9, sMat
ShowMatrix "sheet1", 24, 9, vMat
End Sub
It is a simple utility function which reads a range of cells in a sheet and creates a matrix out of the range of cells. This matrix can later be directly passed to the SVD functions What is SetMatrix function of SVD in above code?It loads the SVD object with the input matrix for which SVD is to be performed, and implements the actual algorithm How do I get SVD result?The functions GetS, GetU, GetV of SVD class give you the matrices U,S, and V respectively Have you verified accuracy of your SVD code?The results can be verified by copying the output matrices U,S and V displayed on sheet1 into some other language and then doing U*S*V'. The resulting matrix should match with the original matrix. For example, here is Matlab code I had used to verify the result:
U2,S2,V2 are pasted from Excel
U2=[0.206735891 -0.889153308 0.40824829
0.518288738 -0.254381834 -0.816496581
0.829841585 0.38038964 0.40824829];
S2=[25.43683563 0 0
0 1.722612248 0
0 0 5.42884E-16];
V2=[0.403617572 0.732866192 0.510398801
0.46474413 0.289849777 -0.532408168
0.525870689 -0.153166638 -0.466380067
0.586997247 -0.596183053 0.488389434];
testmatrix=U2*S2*V2'
testmatrix values match to the input matrix used in Excel. Note that if you compare U matrix returned from Excel with U matrix returned by svd function of Matlab, the values could be different because there is more than one way in which the relationship of SVD can be maintained
Got a question or problem with this link? Just enter your message and click on submit. No registration is required.
|
3941  0 bytes  VBA Excel  http://www.quantcode.com/ |
|
| Poster |
Thread |
| Bazman76 |
Posted: 2009/8/6 7:59 Updated: 2009/8/6 7:59 |
Just popping in   Joined: 2009/8/6 From: Posts: 4 |
 Re: SVD Singular Value Decomposition of a matrix in Excel Hi there,
I can not download matrixmodule from post 1 below. Can someone repost please.
Also why is SVD implemented as a class? Can someone please exaplain the intuition behind this.
Also when incorporating this file into my spreadsheet so I have to insert part if the code into a class module? If so which part?
Thanks
Baz
|
|
|
|
| Poster |
Thread |
| vanna |
Posted: 2009/8/7 19:42 Updated: 2009/8/7 19:45 |
Just can't stay away   Joined: 2007/3/21 From: Posts: 104 |
Re: SVD Singular Value Decomposition of a matrix in Excel Hi Bazman, Thanks for using QuantCode! I have added an FAQ for usage of .cls and .bas file here: http://www.quantcode.com/modules/smartfaq/faq.php?faqid=67SVD has been setup as a class because the programs which use this method need 3 output matrices. Advantage of having a class is that it can store the object state. Another advantage is that the methods are local and same method name can exist as a public function or a class method. This gives the conveience that if you have already a public method or global variable with a name defined in your VBA projeect, you do not have to spend time in solving compiling error due to name collision. Please let me know if you have more questions. Regards |
|
|
|
| Poster |
Thread |
| l_levy |
Posted: 2010/11/30 8:27 Updated: 2010/11/30 22:04 |
Just popping in Joined: 2010/11/30 From: Posts: 1 |
Re: SVD Singular Value Decomposition of a matrix in Excel Hello,
I am looking for SVD decomposition in excel.
I ve download files, but when I import files and try to debug Excel send an error Showmatrix undefined.
Do you know why ?
Thank's
|
|
|
|
| Poster |
Thread |
| vanna |
Posted: 2010/11/30 23:22 Updated: 2010/11/30 23:22 |
Just can't stay away Joined: 2007/3/21 From: Posts: 104 |
Re: SVD Singular Value Decomposition of a matrix in Excel Sorry for the trouble. You are right, it error-ed out for me too!
I have now bundled all dependent code into the attached spreadsheet. please do let me know if you find problems in using any other functions. Thanks for using quantcode!
|
|
|
|
| Poster |
Thread |
| Anonymous |
Posted: 2011/2/1 12:46 Updated: 2011/2/1 12:46 |
| |
SVD Singular Value Decomposition of a matrix in Excel |
|
|
| Poster |
Thread |
| Anonymous |
Posted: 2011/2/1 12:46 Updated: 2011/2/1 12:46 |
| |
SVD Singular Value Decomposition of a matrix in Excel GEN A B C
1 0,7316 1,4522 0,8412
2 0,7665 1,5404 0,8812
3 0,6972 1,4712 0,8007
4 0,7662 1,5767 0,8803
5 0,7358 1,2862 0,8481
6 0,6496 1,4294 0,7453
7 0,6997 1,1826 0,8071
8 0,633 1,4379 0,7252
9 0,712 0,0251 0,8355
|
|
|
| Poster |
Thread |
| Anonymous |
Posted: 2011/5/11 8:43 Updated: 2011/5/11 8:43 |
| |
SVD Singular Value Decomposition of a matrix in Excel for and mXn matrix A, shouldn't the SVD give U as mXm, S as mXn, and V as nXn? this produces a square singular value matrix, but that doesn't seem right.
|
|
|
| Poster |
Thread |
| Anonymous |
Posted: 2011/5/11 8:50 Updated: 2011/5/11 8:50 |
| |
SVD Singular Value Decomposition of a matrix in Excel to clarify the previous post, this code produces S as mXm and V as nXm (V' is mXn). seems like it should produce S as mXn and V as nXn.
|
|
|
| Poster |
Thread |
| Anonymous |
Posted: 2011/5/12 8:42 Updated: 2011/5/12 8:42 |
| |
SVD Singular Value Decomposition of a matrix in Excel never mind, please disregard the previous two posts
|
|
|
| Poster |
Thread |
| Anonymous |
Posted: 2012/3/31 20:21 Updated: 2012/5/6 19:22 |
| |
SVD Singular Value Decomposition of a matrix in Excel So where do I find the SVDD or SVDU functon in Excel, not in Math functions.
|
|
|
Discuss this file. Just enter your message and click on submit. No registration is required.
Subscribe to RSS or daily email updates of latest quantitative finance code listings
|
|
|
|
|
|
