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 use 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 issue with its use for most purposes in finance is the sensitivity of Beta to outliers combined with the use of the whole sample of returns, including such outliers. Read more at: Partial Natural Correlation.

Structure

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.

Betas

0 Beta – CAPM-standard Beta1 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 Max Beta – Partial Natural Beta for the given percentile of periods (proprietary)
4 PNB Min Beta – Partial Natural Beta for the given percentile of periods (proprietary)
5 Pearson’s Correlation – the standard correlation coefficient r
6 Upside Correlation – Pearson’s correlation for all gain periods in the passive series
7 Downside Correlation – Pearson’s correlation for all loss periods in the passive series
8 PNC Max Correlation – highest Partial Natural Correlation for 95% of the periods (proprietary)
9 PNC Min Correlation – lowest Partial Natural Correlation for 95% of the periods (proprietary)
10 Kendall’s Tau – Kendall’s rank coefficient of non-parametric correlation tau
11 Spearman’s Rank – Spearman’s rank correlation coefficient rho
12 Covariance – covariance coefficient
13 M2 – Modigliani M2 ratio for volatility-adjusted returns
14 Information Ratio – excess Active return over the tracking error
15 Vice – Vice Ratio of Vice over excess Active return (proprietary)
16 Virtue – Virtue Ratio of Virtue over excess Active return (proprietary)
17 Coincidence – Coincidence (proprietary)
98 Horizontal array of all 59 DazBeta results
99 Vertical array of all 59 DazBeta results

Results

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).

Examples

The DazBeta Examples worksheet contains different returns and an example of how to use DazBeta simply and powerfully.
The returns are shown with different start and end periods and in both row and column orientations.
The results are shown in individual cells and as a single array result.