VBA code for PCA

Hi everyone,

I'm just a newbee to VBA/Excel. For a project, I have to write a function to calculate the eigenvectors and eigenvalues of a matrix using the "Power Method". I've tried searching the web for some help but there's not much out there. Essentially, I want to input a matrix from the excel spreadsheet along with a tolerance level (i.e. two inputs).

The function then calculates the covariance matrix, call this M (m x m), of the input data. You then make an initial guess of the eigenvector, let's say this is a vector of 1's (m x 1) (call this x), you multiply the two together to get

y=Mx

You then calculate beta which is the element of y with the largest modulus. And, recalculate x as

x=(1/beta)y

and then calculate new y, y=Mx

This is done iteratively until the difference between the old x and new x is less than the tolerance level.

The normalised x, i.e. (1/sqrt(sum(x^2)))*x , call this v, is the first prinicipal component and the last value of beta is the associated eigenvalue.

A new M is calculated as Mnew = M-beta*v*transpose(v)

And, the whole procedure is repeat for Mnew to get the second prinicipal component and associated eigenvalue. The is done m times.

I want to output all the eigenvectors (prinicipal components) and eigenvalues to some location in the spreadsheet.


I would be extremely grateful if someone could assist me with writing this function or providing some pointers.


Regards,
John

Hello johnboy12,

Please change the title of this thread. You have another thread with a similar title. If you don't change the title of the post I will either lock it or delete it. Please read the forum rules about proper posting.

Sincerely,
Leith Ross