Script Question Using Pearson's Correlation Coefficient


I have a workbook attached. In it there is a watchlist that is looking at the correlation value for several pairs of symbols. I have two issues I need help with please.

  1. There are two rows (KSA and ETHUSD) where the result is returning 0.00 across the entire row. I know it is not accurate because if you look at tabs 2 and 3 they show a value that is not 0.00 using the tool instead of the script. What have I done wrong with these scripts?

  2. Please see tab 4. What setting do I need to adjust on the CORREL tool have the data match the data being returned in the watchlist. For example, the watchlist shows VNK to OEF as .71 but the tool shows .63. I tried adjusting the tool to 21 bars, but it doesn’t match. What have I done wrong with the settings on the tool?

Thank you, thank you!

TacticalCorrelation.owb (74.5 KB)


On the scripts in the Watchlist, some (like the OEF column) are set to use Day / 10 Years, whereas the Correlation tool on the charts are using Weekly / 1 Year. So the first step was to adjust the script to match the tools settings.

Secondly, adjust the Watchlists Date Range setting so that all data loads, not just the last year.

Once the above was done the results matched the chart / tool value.

As a side note, have you seen the Correlation Grid chart? It looks like it may do what you’re trying to set up without the need for manual scripts:

Hi Matthew,

Thank you for the info! I see the issue. Some of these symbols don’t have enough data on the charts to go back the 10 years and so are returning zeros. When I put these symbols into the Correlation Grid Chart everything displays perfectly without having to make any adjustments. Using the Correlation Grid Chart is an amazing suggestion and a killer feature to have in Optuma! Thank you.