How to: calculate correlation on systems + more

• 01/30/2020 at 11:16 AM #118261
  jebus89

  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 have done over 3K trades so i gotta go to ig.com and download all my trade history.
  • 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.

  Nicolas, nonetheless

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  01/30/2020 at 11:39 AM #118264
  jebus89

  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

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  01/30/2020 at 5:58 PM #118292
  deletedaccount100622

  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

  

  deletedaccount100622

  Participant

  Topics: 13

  Replies: 116

  Been thanked: 60 times
  01/31/2020 at 1:25 PM #118349
  jebus89

  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)

   

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
• Author
  Posts