5 Replies Latest reply on Mar 6, 2012 12:44 PM by gabrielgriggs0

    Correlation Coeffecient Question

      Hi all,

       

      I am wondering if plotting something like this is possible:

      correlationplot.png

      I am able to calculate the values that I need in Tableau, but I am not sure how to plot them as shown above.  The closest  I could come was to filter them by sets of 12 month periods.

       

      Please check out the attached workbook for more information on this specific example.  You will see that I have the calculations working properly, but I am unsure how to proceed from here.

       

      Any help would greatly be appreciated... I am trying to see if Tableau can do this.

      Thanks,

      Gabe

        • 1. Re: Correlation Coeffecient Question
          Alex Kerin

          I think you're going to need a calculation that creates a dimension mirroring the sets you have. You can then use this as a partition to create a single view with all the coefficients 

          1 of 1 people found this helpful
          • 2. Re: Correlation Coeffecient Question

            Hi Alex,

             

            Thank you for the quick response.

             

            I think that I comprehend what you are saying, but I still have two questions:

             

            1)  Creating this calculated field for the "date sets" --

                 If I were treating the "Date" field as a vector [(1/31/2006),(2/28/2006)...(2/28/2007)], I would do something like:

                      Date_subset1 = Date(1,12);     // 1 through 12

                      Date_subset2 = Date(2,13);     // 2 through 13

                      Date_subset3 = Date(3,14);     // 3 through 14

             

                 How do I replicate these date subsets in Tableau as a calculated field?

             

            2) Once I have these created, how do I use them to get the calculations that I need?

                 Currently, I have three sheets to get the three separate Correlation Coeffecients.  Each sheet looks like this (the only difference being that in the Filter Shelf I change the Date subset):

             

                

             

                 How do I "capture" these calculated correlation coefficients, so that I can graph them?

            • 3. Re: Correlation Coeffecient Question
              Alex Kerin

              So your periods are rolling 12 month blocks?

              • 4. Re: Correlation Coeffecient Question

                Yep.

                 

                Thanks, again, for all of your help with this.

                • 5. Re: Correlation Coeffecient Question

                  Here is what I am hoping to do explained in terms of MATLAB:

                  http://etf-central.com/2010/10/03/calculating-moving-correlation-in-matlab/

                   

                  Much of my research focuses on the dynamic relationships between assets in the market (#1,#2,#3).  Typically, I use correlation as a measure of relationship dependence since its results are easy to communicate and understand (as opposed to mutual information, which is somewhat less used in finance than it is in information theory).  However, analyzing the dynamics of correlation require us to calculate a moving correlation (a.k.a. windowed, trailing, or rolling).

                   

                  Moving averages are well-understood and easily calculated – they take into account one asset at a time and produce one value for each time period.  Moving correlations, unlike moving averages,  must take into account multiple assets and produce a matrix of values for each time period.  In the simplest case, we care about the correlation between two assets – for example, the S&P 500  (SPY) and the financial sector (XLF).  In this case, we need only pay attention to one value in the matrix.  However, if we were to add the energy sector (XLE), it becomes more difficult to efficiently calculate and represent these correlations.  This is always true for 3 or more different assets.

                   

                  I’ve written the code below to simplify this process (download).  First, you provide a matrix (dataMatrix) with variables in the columns – for example, SPY in column 1, XLF in column 2, and XLE in column 3.  Second, you provide a window size (windowSize).  For example, if dataMatrix contained minutely returns, then a window size of 60 would produce trailing hourly correlation estimates.  Third, you indicate which column (indexColumn) you care about seeing the results for.  In our example, we would likely specify column 1, since this would allow us to observe the correlation between (1) the S&P and financial sector and (2) the S&P and energy sector.

                   

                  % Author: Michael J. Bommarito II

                   

                  % Contact: michael.bommarito@gmail.com

                  % Date: Oct 3, 2010

                  % Provided as-is, informational purposes only, public domain.

                  %

                  % Inputs:

                  %   1. dataMatrix: variables (X1,X2,...,X_M) in the columns

                  %   2. windowSize: number of samples to include in the moving window

                  %   3. indexColumn: the variable X_i against which correlations should be

                  % returned

                  %

                  % Output:

                  %   1. correlationTS: correlation between X_{indexColumn} and X_j for j !=

                  % indexColumn from windowSize+1 to the number of observations.  The first

                  % windowSize rows are NaN.

                   

                  function correlationTS = movingCorrelation(dataMatrix, windowSize, indexColumn)

                   

                  [N,M] = size(dataMatrix);

                  correlationTS = nan(N, M-1);

                   

                  for t = windowSize+1:N

                      C = corrcoef(dataMatrix(t-windowSize:t, :));

                      idx = setdiff(1:M, [indexColumn]);

                      correlationTS(t, = C(indexColumn, idx);

                  end

                   

                   

                   

                  The image below shows the results for exactly the example above for last Friday, October 1st, 2010.

                   

                  Thanks,

                  Gabe

                   

                  Message was edited by: Gabriel Griggs