DazBeta provides analytical and functional estimates of correlation, covariance and Beta.
Beta (and its associated parameters such as correlation) is one of the most common measures of ‘risk’ for investments. Indeed it should be since diversification is one of the fundamental principles of portfolio construction. Conventional measures of Beta uses a simple statistical model: linear regression with the least squares result. As with the assumption of the Normal distribution of returns, this statistical model may, or may not, be appropriate for the actual data. Another issues with its use for most purposes in finance is the sensitivity of the model to outliers combined with the use of the whole sample of returns, including such outliers. Read more at: Partial Natural Correlation.
DazBeta(Beta, Active, Passive, Freq, cMAR, rMAR, Pct, Label)
Beta: an integer indicating which DazBeta result to return. Flip this switch to move between any results!
Active: a data range for returns to be used in the function. Active refers to the investment being compared.
Passive: a data range for returns to be used in the function. Passive refers to the benchmark, index or portfolio that the Active investment is being compared with.
Freq: an optional integer indicating the number of return periods in a year, default is 12.
cMAR: an optional double indicating the Minimum Acceptable Return in percentage (i.e. 0.035)., default is 0.00
rMAR: an optional data range for time-varying returns to be used as the Minimum Acceptable Return, default is none.
Pct: an optional Boolean indicating if the data is in percent (0.035 or 3.50% – True) or float (3.50 – False), default is True.
Label: an optional Boolean indicating if the result is the function result or label, default is False (result).
Note that you only need three parameters as the others have reasonable defaults.
0 Beta – CAPM-standard Beta
1 Upside Beta – Beta for all gain periods in the Passive series
2 Downside Beta – Beta for all loss periods in the Passive series
3 PNB Beta – Partial Natural Beta for 95% of the periods (proprietary)
4 Pearson’s Correlation – the standard correlation coefficient r
5 Upside Correlation – Pearson’s correlation for all gain periods in the passive series
6 Downside Correlation – Pearson’s correlation for all loss periods in the passive series
7 PNC Correlation – Partial Natural Correlation for 95% of the periods (proprietary)
8 Spearman’s Rank – Spearman’s rank coefficient of non-parametric correlation rho
9 Kendall’s Tau – Kendall’s rank coefficient of non-parametric correlation tau
10 Covariance – covariance coefficient
11 M2 – Modigliani M2 ratio for volatility-adjusted returns
12 Information Ratio – excess Active return over the tracking error
13Vice – Vice Ratio of Vice over excess Active return (proprietary)
14 Virtue – Virtue Ratio of Virtue over excess Active return (proprietary)
A total of 53 results are returned using the arrays
An individual result may be a label (the name of the function) or an analytical result value.
The analytical result could be a label, a value or a columnar array. For example, the Pearson’s Correlation individual result is a value for the correlation coefficient. The same test returns an array of three values: the correlation coefficient, the
R-squared and the significance of the P-value.
DazBeta can also be used as a single array, in column or row orientation, for all function results at one time. This is more efficient than entering each Beta in individual cells.
Individual array results may be returned by using =Index(DazBeta(function), Array Number).
The DazBeta Examples worksheet contains different returns and an example of how to use DazBeta simply and powerfully.
The returns may have different start and end periods and be in either rows or columns.
The results are shown in individual cells and as a single array result.
The Beta switch can be outside the function, giving you direct control of which results you see.