Anyone any good with excel here?

• 05/09/2018 at 8:11 AM #70033
  jebus89

  I would like to graph out the correlation between my strategies in excel. (backtest results)

  i got trade log with dates and thats where the tricky part starts.

  I would like Dates to show on the X axis and money on the Y axis.

  I have a huge excel file containing backtest results from 7 different algos from the last 2 years. Some got 100 trades and others got 400~ trades, i would love to show all 7 different algos “taking trades” in the graphs on a timeline from 2 years ago -> today. I have cleaned the data so it cointains:

  1. algo name

  2. profit/loss pr trade

  3. Summed/cumulative profit/loss pr trade

  4. Date the trade is done

  how can i use that info to plot into a graph showing time on X and summed/cumulative proft/loss on Y?

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  05/09/2018 at 8:22 AM #70034
  GraHal

  Use Pivot Tables.

  You’ll wonder how you ever lived without them! 🙂

  If you don’t like the link below (as in not easy to follow maybe?) google Excel Pivot Tables as there are loads of how to guides.

  https://blog.hubspot.com/marketing/how-to-create-pivot-table-tutorial-ht

  Let us know how you get on?

   

   

  

  GraHal

  Participant

  Topics: 214

  Replies: 8827

  Been thanked: 2198 times
  05/09/2018 at 8:38 AM #70041
  jebus89

  @GraHal ive got em in a pivot table as well, the main problem is that i need X axis to just be dates from 01.01.2016 -> todays date (Edit: Or it can just be “Jan 2016, Feb 2016, Mar 2016 etc on the X axis, but i still need it to “show trades” on the correct dates so i can see the correlation between them)

  And i need the trades to be plotted in on the correct dates that they where taken. If 2 trades happened the same day i need both trades to be on same day (even if its the same algo)

  Does this make sense?

  In other words: i want EQ lines of 7 algos in the same graph/Chart!

   

  I got the data (i think), i just need to know how i can plot dates into X axis and then place the trades on the correct trades.

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  05/09/2018 at 8:50 AM #70046
  jebus89

  By the way, if i name all the trades “Trade number 1, 2, 3” etc i have no problem making an equity line.

  The problem is that if i make it like that i got no clue about what date it was taken. I cant see the correlation when looking at it like this. I need to plot in exactly what DATES the trades where done, Also its not the same amount of trades pr algo, so that would look weird. Some of the equity lines would just stop at trade 50, while others would continue well past trade #100…

  Does this make sense?

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  05/09/2018 at 10:41 AM #70058
  GraHal

  Whatever you put the date as then excel will cumulate all orders on that date for the pivot table.

  It’s coming back to me now … you need to have separate columns with the date converted so that you pivot these separate converted columns … that is if the default date is not good for you? So for example … all dates in week 1 could be converted to read week 1.

  If you post a screen shot of what you have got so far, it might trigger more intel in my brain! 🙂

  jebus89 wrote:

  i need X axis to just be dates from 01.01.2016 -> todays date

  Re above sounds all you need to do is untick the date boxes you don’t need  or only make a pivot chart of part of your pivot table … yeah that’s the best way.  Right click (somewhere ! 🙂 and look for table reference like a1: z10,000 and change this to a1:z2000.  Or (for better instructions) google change pivot chart / table reference cells, 

  Wow didn’t think I’d remember that without looking at the pivot table on the screen … alzheimers has not set in yet! 🙂

   

   

  

  GraHal

  Participant

  Topics: 214

  Replies: 8827

  Been thanked: 2198 times
• Author
  Posts