# How to: calculate correlation on systems + more

Forums ProRealTime English forum General trading discussions How to: calculate correlation on systems + more

This topic contains 3 replies, has 2 voices, and was last updated by  jebus89 2 months, 2 weeks ago.

Currently, there are 0 users and 1 guest visiting this topic.
View all attachments
Viewing 4 posts - 1 through 4 (of 4 total)
• #118261

I ment to post this in the General forum and not proOrder support, pls move it if you can Mods ðŸ™‚

Alot of people are wondering, and i was wondering myself for a long while, finally figured out how to do it the easy way in excel.

Note that if you have a lot of different systems in the same markets, this method wont work and you would need to do the same but instead of getting trade story from IG you have to copy paste using detailed report / backtest / live report for all strategies then follow the steps below:

• I upload it to excel via “data -> import”
• ctr + f and replace all “space” with nothing (removing spaces)
• ctr +f and replace all , with . (excel likes this: 10,87 -> 10.81)

I then remove useless info like entry/exit etc. Im then left with: Date – Market – Cost for trading – profit/loss – Total

Now comes the grueling part: You now have a list with all dates and totals and markets, you now need to manually add up the totals for each month in each market.

So for example start with Wall st, u add up all the TOtals for each week/month/quarter/year, whatever you want to look at. For me i use Monthly when looking at correlation, however i would like to use weekly, its just more work than i would bother doing for now ðŸ™‚ Im not in a position where my account might be gone within 1 month anyways.

So when you have all your markets/strategies lined up with monthly results (jan 2018, feb 2018, etc) then you go to Data in excel and use the correlation matrix option. Bam you now got a correlation matrix of your systems.

You also have the numbers to crunch alot more statistics and graphs ðŸ™‚

Once you get the hang of it, it really dosnt take all that long (depending on how many systems and how many trades ur doing)

###### 2 users thanked author for this post.
#118264

Would like to add that for this step:

“Now comes the grueling part: You now have a list with all dates and totals and markets, you now need to manually add up the totals for each month in each market.”

This step can be done easier if u use a pivot table for example. Then u can easily sort after X market and all u gotta do is add up the totals for all weeks/months/q/y/whatever

#118292

On IG.com you can get an excel file straight out

(see attached)

From there as you say a pivot table (or MS BI) and a couple of calcs from the results

###### Attachments:
#118349

Aha well spotted Robo.

Also i made an error in my first post:

“ctr +f and replace all , with . (excel likes this: 10,87 -> 10.81)”

Its the other way around:

ctr +f and replace all . with , (excel likes this: 10.87 -> 10,81)

Viewing 4 posts - 1 through 4 (of 4 total)