1
$\begingroup$

I am in the process of making a vba subroutine in Excel to perform Monte Carlo bootstrap cross validation on a regression formula. I've got the regression part, and the MSE of the CV's part finished. I am running the MonteCarloCV through 100 replications. I recently am trying to add more statistics to my output which includes a correlation matrix for each replication of input data.

This is where I am running into the problem. About 10% of the time, the diagonal of my covariance matrix is degenerate which leads to me not being able to create a correlation matrix. My question is if this is common place, and if it is what work-arounds there are for generating the correlation matrix.

I am fairly certain that I am calculating each correctly, but here's an example of what I'm seeing:

Randomly mark 30 out of 30 records to be used in the training dataset. which comes out to ~20 of the 30 being marked for training:

(These are only X values, I did not want to include Y for the matrix) $$ \begin{matrix} -27&51&65&20&1\\ -14&76&19&2&1\\ -1&82&6&24&1\\ -21&99&78&3&1\\ 27&90&79&19&1\\ -3&79&37&14&1\\ 27&79&18&15&1\\ 8&80&28&13&1\\ -29&48&40&17&1\\ -18&86&99&12&1\\ 19&85&3&12&1\\ 9&100&85&15&1\\ -23&50&86&2&1\\ -17&90&47&1&1\\ 2&41&77&20&1\\ 28&84&86&20&1\\ 24&80&78&19&1\\ \end{matrix} $$

I then find the covariance matrix of the training dataset by using the equation: $$ a = A - 11^{T} A (1/n)$$

$$V = a^{T} a (1/n)$$

Which yields the following matrix:

$$ \begin{matrix} 393.0726644&135.3667820&-53.12456747&64.27681661&0\\ 135.3667820&302.0138408&-10.9480968&-22.31141868&0\\ -53.12456747&-10.9480968&946.2975779&1.332179931&0\\ 64.27681661&-22.31141868&1.332179931&50.00692042&0\\ 0&0&0&0&0\\ \end{matrix} $$

Then I use the following formula to calculate the correlation matrix:

$$ D = diag(\sqrt{V}) $$

$$ p = D^{-1} V D^{-1} $$

and the calculation of $$ D^{-1} $$ is what is causing problems since it's determinate = 0 which throws a div/0 error.

Let me know if more information is needed. I didn't want to post the code because it is rather long, but I can if it would help.

Improve this question
$\endgroup$
9
  • $\begingroup$ You are being inconsistent in constructing $a$ and including the constant column in the calculation. You can do one or the other but not both. Use a tiny dataset--say, three rows and two columns (including the constant) and compare what you're doing with the textbook formulas. $\endgroup$
    – whuber ♦
    Nov 20, 2017 at 20:05
  • $\begingroup$ @whuber Not exactly sure what you're asking. I did work the problem by hand just now and realized I entered a wrong number into my program to calculate the covariance matrix. I fixed it and edited my answer, but am still getting the same problem with the correlation matrix. $\endgroup$
    – Acumen Simulator
    Nov 20, 2017 at 20:52
  • $\begingroup$ Correlation to a constant is going to be zero $\endgroup$
    – Aksakal
    Nov 20, 2017 at 20:55
  • $\begingroup$ Basically, dont try to bootstrap the constant, just set it. It's not a bona fide input, anyways $\endgroup$
    – Aksakal
    Nov 20, 2017 at 21:01
  • 1
    $\begingroup$ Nobody includes the constant in correlation matrix when generating inputs while bootstrapping with monte carlo, trust me on this. $\endgroup$
    – Aksakal
    Nov 20, 2017 at 21:13

1 Answer 1

Reset to default
1
$\begingroup$

After great comments from whuber and Aksakal, I arrived at the following conclusion:

Since the covariance and variance of the OLS constant is 0, the standard deviation is also 0 which causes the correlation to be either 0 or undefined.

Using this, I eliminated the constant vector from the analysis (since the analysis would not be telling us something we already didn't know about it) and ran the program on the remaining variables. This leading to the following correlation matrix:

$$ \begin{matrix} 1&0.392882116&-0.087105408&0.458461513\\ 0.392882116&1&-0.020479123&-0.181551026\\ -0.087105408&-0.020479123&1&0.006123983\\ 0.458461513&-0.181551026&0.006123983&1\\ \end{matrix} $$

I have also run the vba program for 100s of replications on a few datasets, in which the problem seems to be fixed in entirety.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.