Google-sheets – Covariance matrix in Google Sheets for stocks

google sheets

I'm trying to use a template to see what it says about my stock portfolio. it uses a covariance matrix with the data from excess returns. The template instructions say to use an add-on to calculate the matrix but the add-on doesn't work. I tried a formula but it didn't work. =MMULT(TRANSPOSE('Excess Returns'!B3:F252),'Excess Returns'!B3:F252)/251

I've got 5 stocks and 249 dates of data. in a video I watched on how to make a matrix it says to highlight the 5×5 matrix and hit control shift enter and it will apply the formula to the grid but it doesn't work.

Do you anyone no what i am doing wrong or a better way of doing it?

Best Answer

this is the template https://docs.google.com/spreadsheets/d/1yS26o6VA12Ry7aZVzObQ7LhyD4uRznNcdOgVzpkSwFQ/edit?usp=sharing

and this is the reddit post with other info https://www.reddit.com/r/stocks/comments/crmpx7/i_made_a_portfolio_optimisation_tool_spreadsheet/

this is my post, but i guess i posted it as a guest and then made an account so idk.

Related Solutions

Google Sheets – How to Count Entries Based on Timestamp

Try:

=COUNTIF(ARRAYFORMULA(YEAR(B:B));2011)

=COUNT(FILTER(B:B;YEAR(B:B)=2012))

=INDEX(QUERY(B:B;"SELECT COUNT(B) WHERE YEAR(B)=2012");2;1)

If you use the QUERY formula without the INDEX, then a header will appear as well.

Google Sheets – Array-Safe Clamp Value

You can achieve the effect of CLAMP(value, min, max) with

=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)

which is compatible with ARRAYFORMULA and uses value only once. Explanation:

SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise.
IFERROR returns the first argument, unless an error occurred in it, which case it returns the second
Thus, IFERROR(SQRT(value-min)^2+min,min) clamps the value from below by min.
The other steps do a similar thing with max.

The above formula can be simplified, reducing the references to max and min from three to two of each:

=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))

Best Answer

Related Solutions

Google Sheets – How to Count Entries Based on Timestamp

Google Sheets – Array-Safe Clamp Value

Related Topic