Calculate Stock Beta by Covariance and Variance

Questions about MultiCharts and user contributed studies.
ctu1121
Posts: 135
Joined: 05 Jul 2012
Has thanked: 10 times
Been thanked: 4 times

Calculate Stock Beta by Covariance and Variance

Postby ctu1121 » 14 Aug 2013

Hi,
The definition of stock Beta is Covariance/Variance
I use the following study to calculate beta, however, the result seems wrong.
Please refer the result for your reference. The Beta should not be such a big number.
May I have your suggestion to correct my error? Thanks.

Charles

Code: Select all

value1=close[0] of data1/ close[1] of data1 -1; //Stock
value2=close[0] of data2/ close[1] of data2 -1; //Index

value11=Covariance(value1,value2,100);
value12=Variance(value2,100);
value13=value11/value12;

plot11(value13);
Attachments
Cov.png
(219.94 KiB) Downloaded 801 times

User avatar
TJ
Posts: 7740
Joined: 29 Aug 2006
Location: Global Citizen
Has thanked: 1033 times
Been thanked: 2221 times

Re: Calculate Stock Beta by Covariance and Variance

Postby TJ » 14 Aug 2013

Step #1 in debugging:

Do not use generic variable names. ie. value1, value2, condition1, etc.,

Custom variable names are free -- you should create a meaningful name for each of the variables, so that you can easily trace your logic when you read your code.

User avatar
furytrader
Posts: 354
Joined: 30 Jul 2010
Location: Chicago, IL
Has thanked: 155 times
Been thanked: 217 times

Re: Calculate Stock Beta by Covariance and Variance

Postby furytrader » 15 Aug 2013

I think the issue has to do with the covariance function - the number it is outputting in the tests I've run using AAPL vs. SPY is very high - in the 3000 range. When I run the same data through Excel, the number comes in at 0.3949.

In contrast, the variance function gives the exact same number as Excel.

User avatar
furytrader
Posts: 354
Joined: 30 Jul 2010
Location: Chicago, IL
Has thanked: 155 times
Been thanked: 217 times

Re: Calculate Stock Beta by Covariance and Variance

Postby furytrader » 15 Aug 2013

In checking out functions, etc., I discovered that there is a COVAR function that delivers results equal to Excel. Try that out.

ctu1121
Posts: 135
Joined: 05 Jul 2012
Has thanked: 10 times
Been thanked: 4 times

Re: Calculate Stock Beta by Covariance and Variance

Postby ctu1121 » 15 Aug 2013

Hi furytrader,
Thanks for your suggestion. I have changed to Covar and calculate 29 days IBM and SPY as example. I got Beta 0.4617 from Excel and 0.4161 from MC. Please see attached photo for your reference. May I have your suggestion why I got different beta?

Charles
Attachments
beta.png
(402.47 KiB) Downloaded 797 times

User avatar
furytrader
Posts: 354
Joined: 30 Jul 2010
Location: Chicago, IL
Has thanked: 155 times
Been thanked: 217 times

Re: Calculate Stock Beta by Covariance and Variance

Postby furytrader » 16 Aug 2013

Instead of troubleshooting your example, I am posting the code I experimented with as well as an Excel spreadsheet I used to verify the results from the code below:

Here is my code (it should look familiar):

Code: Select all

Vars: stockNetChange(0), indexNetChange(0), pairCovariance(0), indexVariance(0),beta(0);

If BarStatus = 2 Then Begin

stockNetChange =(close[0] of data1 - close[1] of data1)/(Close[1] of data1) * 100; //Stock
indexNetChange =(close[0] of data2- close[1] of data2)/(close[1] of data2) * 100; //Index

pairCovariance =Covar(stockNetChange,indexNetChange,100);
indexVariance=Variance(indexNetChange,100);

beta=pairCovariance/indexVariance;

Print(Date,",",NumToStr(stockNetChange,6),",",NuMToStr(indexNetChange,6),
",",NumToStr(pairCovariance,6),",",NumToStr(indexVariance,6),",",NumToStr(Beta,6));

End;

plot11(beta);
For each bar, this will print out the date, the stock's net change, the index's net change, the covariance between the two going back 100 days, the index variance going back 100 days and the calculated beta.

In the attached Excel spreadsheet I show the output of this code using AAPL as my stock and SPY as my index. If you scroll to the bottom of the spreadsheet, you will see that the resulting calculations tie out exactly.

Hope this helps.

P.S. You'll note that my formula for net change is different from yours but the numbers shouldn't cause any difference between MultiCharts and Excel.
Attachments
Beta Test.xls
(215.5 KiB) Downloaded 225 times


Return to “MultiCharts”