Welcome Guest, please sign in to participate in a discussion. Search | Active Topics |

Trouble with Correlation formula (and Correlation Block) Rate this Topic:
Previous Topic · Next Topic Watch this topic · Print this topic ·
feltburner
Posted : Wednesday, June 18, 2014 12:11:51 PM
Registered User
Joined: 1/16/2005
Posts: 72

This is somewhat involved, so forgive me. I imported a data set as an indicator I called "Precast" and wish to run a correlation between price and the 5MA of that indicator.  I attempted to use the Correlation block, but it would never survive the closing and reopening of the layout.  

Tech support was able to use the block to create one that DID survive reopenings, but the resulting output was incorrect vs. an excel result (the numbers were not even close).  Tech support said the issue was that "The Correlation Block is stuck in a "Working" state when directly connected to price for one of its inputs and either Precast or a moving average of Precast for its other input."  I tried to see if this could be resolved by creating new indicators called "price proxy" and "precast proxy" which are simply the price and the 5MA of the precast indicator multiplied by one, but this did not work either.  Tech support hoped to create an editable Correlation Block, was unable to do so.

I then created in realcode a formula for Covariance (5 day) of the two, which utilizes the price proxy, its 5MA and the precast proxy (which was the 5MA of the precast indicator) and its 5MA.  The result matched the 5 day covariance in excel.

 Since correlation of price and precast MA  is the Covariance of the two divided by the products of the standard deviations of the two, i added 5 period standard deviation indicators to price proxy and precast proxy and created a correlation indicator in realcode; this indicator also matched the excel numbers perfectly.  However, when I created a 15 day Covariance indicator, the number differs from excel.  In addition, the 15 period standard deviations given by the stockfinder indicators also differ from excel (they match neither the "population" or "sample" formulas in excel).  It goes without saying that a 15 day correlation would be horribly off since every component of the formula is off.  I have shared the layout as "Correlation issues".  Any explanation why the 5 day figures all match but every 15 day figure is wrong? 

To make matters even more difficult, you will be unable to see the precast line since it draws upon a .csv file in my Stockfinder5 DataFiles folder.  I would attach it here, but I don't see a method of doing so.

Thanks for any help/suggestions,

Bruce V. (feltburner)

Bruce_L
Posted : Wednesday, June 18, 2014 12:55:11 PM


Worden Trainer

Joined: 10/7/2004
Posts: 65,138

I was able to get a copy of the at least the SP-500 pre-cast data from one of the emails you sent in.

Can you post your RealCode so I can take a look?



-Bruce
Personal Criteria Formulas
TC2000 Support Articles
feltburner
Posted : Wednesday, June 18, 2014 1:05:06 PM
Registered User
Joined: 1/16/2005
Posts: 72

I thought you'd be able to get it from the shared layout.  If not, i'd be glad to post it, just need to know which realcode you want (Precast, covariance, correlation, all).

Bruce_L
Posted : Wednesday, June 18, 2014 1:14:06 PM


Worden Trainer

Joined: 10/7/2004
Posts: 65,138

Sorry. You are right. I can get it from the shared layout. Thanks!



-Bruce
Personal Criteria Formulas
TC2000 Support Articles
feltburner
Posted : Thursday, June 19, 2014 11:55:42 AM
Registered User
Joined: 1/16/2005
Posts: 72

Bruce,

I wrote two clumsy formulas - a 5 period standard deviation of price and a 15 period standard deviation of price to check the results vs. the stockfinder built-in indicator and excel results.

The 5 period formula is:

plot = (((price.Close(4) - ma.value) ^ 2 + (price.Close(3) - ma.value) ^ 2 + (price.Close(2) - ma.value) ^ 2 + (price.Close(1) - ma.value) ^ 2 + (price.Close - ma.value) ^ 2) / 5) ^ .5
 
The results are identical to the stockfinder indicator for all dates and match the excel numbers from 6/18 through 5/29 when they start to differ (I think the equal closing given to the S&P500 on the 5/26 holiday is messing with the numbers somehow).
 
However, when I simply lengthen the formula for a 15 period  standard deviation (and use the15MA in the formula) like below, I receive an "End of Statement expected" error. 
 
plot = (((price.Close(14) - ma.value) ^ 2 + (price.Close(13) - ma.value) ^ 2 + (price.Close(12) - ma.value) ^ 2 + (price.Close(11) - ma.value) ^ 2 + (price.Close(10) - ma.value) ^ 2 + (price.Close(9) - ma.value) ^ 2 + (price.Close(8) - ma.value) ^ 2 + (price.Close(7) - ma.value) ^ 2 + (price.Close(6) - ma.value) ^ 2 + (price.Close(5) - ma.value) ^ 2) + (price.Close(4) - ma.value) ^ 2 + (price.Close(3) - ma.value) ^ 2 + (price.Close(2) - ma.value) ^ 2 + (price.Close(1) - ma.value) ^ 2 + (price.Close - ma.value) ^ 2) / 15)^.5
 
 What does this error mean?  I don't see the difference in the two formulas (other than adding additional periods) to get why one works and other won't.  
 
In addition, I shared this updated layout and included my 5 period correlation indicator (which matches excel) as well as the 5 period correlation indicator created by tech support so you can see how much they differ in results.

 

Bruce_L
Posted : Thursday, June 19, 2014 1:25:58 PM


Worden Trainer

Joined: 10/7/2004
Posts: 65,138

Without yet commenting on the rest of the post (I will, but the issue is kind of complicated and requires more testing than most other questions), there is an extra closing parentheses at the following section of your RealCode.

(price.Close(5) - ma.value) ^ 2)

That last ) shouldn't be there.



-Bruce
Personal Criteria Formulas
TC2000 Support Articles
feltburner
Posted : Thursday, June 19, 2014 2:14:40 PM
Registered User
Joined: 1/16/2005
Posts: 72

That did it - It now shows and also matches identically the built-in 15period standard deviation numbers (which matched the excel numbers from 6/18 back to 6/12 before the excel numbers start to differ); even though both the excel spreadsheet and stockfinder have matching dates and prices they start to spew out different standard deviation values as I go backward from current date.  Very odd.  

Thanks!

feltburner
Posted : Thursday, June 19, 2014 3:25:10 PM
Registered User
Joined: 1/16/2005
Posts: 72

I now have two working correlation indicators (5 day and 15 day); they are not as convenient as the Block Correlation, where I could edit time period in either the edit screen or in the block diagram (and did not require the clutter of standard deviation and covariance indicators) but they work and survive layout reopenings whereas the others did not.

Two smaller issues remain:

1.)  why do  the price standard deviations start to diverge from excel's numbers beyond the most recent dates while the standard deviations of indicator numbers match perfectly throughout all dates?  I have checked going back to 4/30/14 (I would think that is far enough back for 15-day standard deviation and correlation calculations), and all dates and prices match stockfinder's data exactly.  This certainly isn't your problem, as the program is doing what it is supposed to, but if you have any thoughts I'd love to hear them.

2.)  why was your stochastics correlation indicator (from http://forums.worden.com/default.aspx?g=posts&t=51335 ) able to survive layout closing/reopening, but edited versions of that (both mine and tech support's) either couldn't survive layout closing or returned incorrect data?

Thanks for your help and any further thoughts you may have.  I have shared an updated version of the layout with the working correlation indicators if that helps in any way.

- Bruce

 

Users browsing this topic
Guest-1

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.