Syntax: SpearianRollingCorrelation(Ticker1, Ticker2, RollingWindow, , ReferenceFromDate, ReferenceToDate, ShowLabels, AutoFormat)


Equivalent Function Names: SPROLLCORREL


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



1.Ticker1 and Ticker2 are standard Spearian Tickers. Review the Tickers guidelines here.

2.Parameter RollingWindow is optional and determines the number of datapoints in the rolling window. If not specified, the default value is 20.

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

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

5.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.For each snapshot of the time series of log returns of the size of RollingWindow data points, the standard Spearman Correlation Matrix is calculated and associated with the last date of the snapshot

5.The timeseries of correlations is returned




1. Calculate the rolling correlation between IBM and MSFT  of the rolling window 20 days for the last 2 years




Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017