Can someone help me with MS EXCEL?? please

Discussion in 'Trading Software' started by cashmoney69, May 23, 2008.

  1. I'm trying to make an equity curve in excel (excel 2003), fit within

    a time frame but I cant figure out how to section off my charts on

    an X axis for my dates (march - may) ..can someone explain how

    to do this?

    thanks
     
  2. Can you post an image of the spread sheet cells?
    Sounds like you have prices in one row, spanning several colums (eg, A1:A12) and dates in another (ie, A2:A12), and you want to plot a subset of that (ie, C1:E2), right?
     
  3. I have my p/l in one colum, and the dates in a seperate colum
     
  4. A very fast way is to do menu/data/filter/autofilter then select non-blanks for col A 1st (dates).

    I.e. with auto_filter selected, grab the A col tab and scroll down to non-blanks.
    copy that filtered col over to a new worksheet and paste it there.

    Then go back, hit auto-filter again to undo the 1st filter. Redo auto-filter and this time select the daily total, and filter for non-blanks and copy that col over to the new worksheet. Now, you have both aligned on the new worksheet.

    [​IMG]

    2 small problems. 1) some of your dates have no equity (i.e. 5/16 and 5/20). You can either delete those dates before you do the auto-filter (which is what i did on the ex), or put a dummy variable in the daily total col (better solution), before you copy and paste the daily total col (i.e. just a letter like d). So when you paste over they line up.

    3) you need to create one more column on the new worksheet to accumulate the equity (i.e. add up each days equity). The equity curve is cumulative profits, rather than daily.

    p.s. sorry it's not under tools/data/filter/auto_filter

    just...

    data/filter/auto_filter


    also, select the col you want or all columns before doing the auto_filter.

    [​IMG]
     
  5. Is this what you're looking for?
    Far less elegant than dtrader98, I just copied/pasted the missing cells with the date cell above them, inserted the column next to price, copied/pasted the two columns to a new worksheet (pre-format the date column as a date), sorted by P&L, deleted rows w/no P&L, sorted by date, created chart.

    Sounds like it took longer than the 90 sec it actually did :)
    ...of course I didn't think about cummulating the gain :(
     
  6. thanks dt, and quote..the running gain is a good idea so the

    chart isnt so choppy. How do i write a formula that ignores

    empty cells? like if i wanted to do a =sum(A1:A20) but ignore the

    empty ones between, how would i do that?

    what was the excel formula for the running gain?
     
  7. ok i got it :) but how do i change the color and thinkness of the

    equity line?..cant figure it out
     
  8. regarding the sum and how to ignore blanks, just leave them in! (sum of x + 0 = x).

    Color thickness: right click on chart line.
    select format data series.

    thickness (weight) and color can be changed there.