1 Topic by tnkfunds (edited by tnkfunds 2011-06-12 12:37:29)

  • Registered: 2011-06-07
  • Posts: 2

Topic: Computing the correlation coefficient between 10 strategies!

Hey Guys,

I want to run 10 different strategies (for 10 different pairs - 1h bars strategies) simultaneously by using the same capital (which is my biggest concern).

By generating the strategies I've used 10% Draw Down as a maximum - which is fine, but if all the 10 strategies experience a draw down period of even 3% average at the same time - I will be in trouble.

The solution is to calculate the correlation coefficient between the strategies' rates of return over time, and even create one TOTAL Balance/Equity chart, which will include all the 10 strategies PnLs fluctuations.

I will calculate the correlation coefficient using the FSB's EXPORT - POSITION IN CURRENCY function to export the data from the 10 strategies and then collect all the 10 EQUITY data columns in one new sheet - then use the following formula to compute the correlation coefficient between strategies on a hourly bases:

Example: =CORREL(C1:C50000,G1:G50000)

I can also create one TOTAL Balance/Equity chart by doing the following:
1) Let say the initial account is 100,000 USD, so I will copy the 10 Equity columns (the equity data for each of the 10 strategies) and withdraw the initial capital (100,000) from every ROW - so I will have the PnL for every strategy on a hourly bases.
2) I will create a new column (11th) and SUM the all the 10 ROWs, in order to compute the total PnL of the account (using the 10 strategies simultaneously) again on a hourly bases.
3) I will use the 11th column data to create an excel CHART and see the total Balance/Equity chart of the account and the Total Draw Down (HYPOTHETICAL of course)

NOW TO THE PROBLEM:

When using the FSB strategy calculation and export function (position in currency) the JOURNAL BY POSITION includes only information when the strategy is in position. Of course, when out of position - the PnL is Zero and it's easy to calculate, but when comparing different strategies with each other and especially their correlation coefficient we need to have all the 10 PnLs for the same Bar Opening Time in one ROW, in order to run the calculation properly.

So the following is needed:
When calculating and exporting the JOURNAL BY POSITION, doesn't matter whether the strategy is in position or not - to give the necessary information for EVERY Bar Opening Time.
Thus, the time in every Equity Column's ROW will be the same (after exporting)

MY AIM IS:

If I run this calculations, and I don't like the (positive) correlation coefficient between the strategies, I will be able to continue developing strategies, and find some Zero, or even little negative correlated, in order to complete my portfolio.


P.S. I know I can do this manually by using the BAR NUMBER information as a reference, and I will do it, if there is no smarter way, which I hope somebody will share.


I HOPE, I EXPLAINED MY PROBLEM STRAIGHTFORWARD ENOUGH!!!

Thanks for reading!!!

Best
Martin

  • krog
  • Licensed Member
  • Offline
  • Registered: 2010-02-26
  • Posts: 407

Re: Computing the correlation coefficient between 10 strategies!

Hi, I think I understand, for the No Equity problem, try this, it may be easier than manually:

1) Export Bar Summary instead of Positions in Currency (gives data for every bar, not just positions)
2) sort all data by Profit
3) paste in 0 in all the blank cells
4) sort data by bar number (get back to original order)
5) at end of column titles, add your starting balance (for me, it's column W, cell W5, after Interpolation)
6) in cell below, enter formula to add above cell to profit column
      eg: in cell W6, enter:
               =W5+N6
7) copy and paste that formula to end of your data

Then, it should have a column with the equity amount bar by bar, for every bar. Plus, the Profit column will all have values too. To be sure all the rows match for time, best to check them to ensure they are in sync, and that some of your data is not missing. My data never syncs up across instruments, because some will have Friday 16:00, some Fri 16:05, some Fri 16:10.

let me know if that works

  • From: Egypt- Saudi Arabia
  • Registered: 2009-12-12
  • Posts: 968

Re: Computing the correlation coefficient between 10 strategies!

Hello Krog
it will be nice to make the portfolio testing option as an option in the generator
Is that new feature applicable !!!!!
Regards