Hi,

again I have a really very special topic. I am currently doing some quant work and work with regression analysis.

Optuma have some great tools and functions about regression analysis but I have still some questions about how to work with these regression tools and functions.

I used the tool “Correlation” to calculate the correlation between Apple and the S&P 500. I created TWO examples, one chart using “% change” in the setting “Calculate on” and another chart using “Price” in the setting “Calculate on”.

Using “% Change” I get a correlation of 0.779, using “Price” I get a correlation of 0.966.

Then I used the tool “Pearson’s Correlation Coefficient”. This tool has NOT the option to use “% Change”, it calculates only with absolute price changes. I think this is so.

The correlation is 0.996, the same as using the tool “Correlation” and using “Price” in the setting “Calculate on”.

Now I created a script to calculate R-Squared.

Then I created a chart to display R-Squared.

R-Squared is 0.933. That is of course correct since 0.966 * 0966 = 0.933. So far so good, no problem.

Then I used the tool “Regression Chart”.

As you can see at the top of this chart R-Squared (R2) is 0.62!!!

Now the issue gets challenging. What is the right R-Squared number 0.62 or 0.933. Statistically this is a real big difference.

Using the tool “Correlation” and the setting “% change” in “Calculate on” I get a correlation of 0.779. Squaring 0.779 (0.779 * 0.779) and you get R-Squared of 0.61 very close to 0.62.

In the tool “Regression Analysis”, see screen shot above, the setting for the “Dependent Script” and for the “Independent Script” is “Change”. The script for “Change” is the following:

So now we have all the puzzles together and need only to understand what is going on.

- The tool "Correlation" allows the settings for "% Change" and "Price"
- The tool "Pearson's Correlation Coefficient" uses only "Price"
- The tool "Regression Analysis" has as standard setting "Change".

As far as I understand the issue of regression analysis and correlation it is done using ONLY absolute price changes and not %-changes.

When regression analysis is usually done with price changes, why is in the tool “Regression Analysis” the standard setting “Change”?

What do you think is the “right” correlation, using %-change or absolute price change?

What script have I to use in the tool “Regression Analysis” to use absolute price changes and not %-changes?

I hope I have made the issue clear, a really very subtle issue.

Thanks

Thomas