Can someone help me with MS EXCEL?? please

C

cashmoney69

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
 
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?
 
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.

25zjl2q.jpg


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.

14ms97d.jpg
 
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 :(
 

Attachments

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?
 
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.
 
You must log in or register to reply here.
Back
Top