SpearianCorrelation

 

Syntax: SpearianCorrelation(Tickers, ReferenceFromDate, ReferenceToDate, ShowLabels, AutoFormat)

 

Equivalent Function Names: SPCORREL

 

Purpose: Function used to calculate the correlation matrix of log returns.

 

Parameters: 
 

1.Tickers are standard Spearian Tickers. Review the Tickers guidelines here.
 

2.Parameter ReferenceFromDate and ReferenceToDate are optional.

If provided, it can be specified either as an Excel date or in the text format "yyyy-MM-dd", e.g. "2013-02-25". The time range can be entered using relative time periods, see here.

If not provided, the default of the last 2 years is applied.
 

3.ShowLabels - an optional flag specifying if the result should contain the labels of the correlation matrix - the tickers
 

4.AutoFormat - an optional flag signifying if the result should be automatically converted to a range and if the result should be automatically formatted as Excel Data Types

 

Calculation Methodology:

1.The matrix of all price data of the default columns is retrieved

2.All rows of the matrix where at least one number is NaN are removed

3.The matrix is transformed into a log returns matrix

4.Standard Spearman Correlation Matrix is calculated and returned as the result

 

Examples:

 

1. Calculate the correlation matrix between the four tickers with explicit specification of the tickers

=SPCORREL("MSFT,IBM,GS,JPM")

 

2. Calculate the correlation matrix between the four tickers with explicit specification of the tickers without labels

=SPCORREL("MSFT,IBM,GS,JPM",,,FALSE)

 

 

3. Calculate the correlation matrix between the constituents of the Dow Jones Industrials Index

=SPCORREL(B2:B31)

 

 

 

Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017