Excel question

[Hamb-ltrd]

I think I know some basic Excel but this one doesn't look that basic to me.

Can anyone describe the code to calculate the biggest drawdown?
I have all my trades in Excel with one column having the net profit-loss of each trade.

Another column has the present value of my account.

Thanks

Hamb

 

[ElectricSavant]

You might describe the "drawdown"...are you looking for peak to valley? or consecutive trades?

I do not know how to write the formula's either...I just know the min/max functions in excel..


Michael B.

 

[cvds16]

this is not so difficult, get a collumn witch keeps track of your peek value. In the next collumn calculate % difference between present value and peak value. In the third collumn use min to get the end result.

 

[ElectricSavant]

Well...... drawdown according the the NFA is as follows and I am putting this in my own words by example:

Start balance 5k

You trade up to 6k then down to 4k....is this a 1k drawdown....no but Tradestation says so.

Actually this is a 2k drawdown.....

You take the running highest peak and the running lowest valley in that order....

Understand?

Michael B.

Quote from cvds16:

this is not so difficult, get a collumn witch keeps track of your peek value. In the next collumn calculate % difference between present value and peak value. In the third collumn use min to get the end result.

 

[cvds16]

hmm, a bit confused by your explanation, because this was exactly what I entended to calculate with these collumns. You cant use running valley however, because the peak might go up, you would be using an old value for valley. So thats why i'm using this¨% calculation.

 

[ElectricSavant]

Not true...were looking for one-way direction AT THAT TIME.....think about it...

My explanation may seem dyslexic ...but I can't help it... I am a savant...

Michael B.

Quote from cvds16:

hmm, a bit confused by your explanation, because this was exactly what I entended to calculate with these collumns. You cant use running valley however, because the peak might go up, you would be using an old value for valley. So thats why i'm using this¨% calculation.

 

[cvds16]

Sorry, i have no clue what you are talking about here.

Let me try to explain again what i'm doing: the first collumn i use is to get my highest point reached ever, so you get 5K, 6..5K, 7K for example, the next figure can never be lower then the previous one, only the same (this is probably what you describe as running peak value), in my second collumn i calculate the % drawdown (the only meaningful thing imo), but you could use the absolute drawdown too by calculatiing the difference between the peak value and the last present value. If you make a new peak this will be 0. This will make sure your drawdown is calculated each time against the peak. The last collumn is simply to sift the highest precentage number out.

If you would use a running valley value to calculate drawdown we would have the following problem: say your low is 4000, marking a drawdown of 2000 from the previous high 6000, but the account goes up to 7000, the low would remain 4000 and this would make a "drawdown" of 3000, which of course is not true.

 

[Mr Subliminal]

http://www.elitetrader.com/vb/showthread.php?s=&threadid=23524

 

[ElectricSavant]

which comes first......think about it....


Thank you mr. sub....

Michael B.

Quote from cvds16:

Sorry, i have no clue what you are talking about here.

Let me try to explain again what i'm doing: the first collumn i use is to get my highest point reached ever, so you get 5K, 6..5K, 7K for example, the next figure can never be lower then the previous one, only the same (this is probably what you describe as running peak value), in my second collumn i calculate the % drawdown (the only meaningful thing imo), but you could use the absolute drawdown too by calculatiing the difference between the peak value and the last present value. If you make a new peak this will be 0. This will make sure your drawdown is calculated each time against the peak. The last collumn is simply to sift the highest precentage number out.

If you would use a running valley value to calculate drawdown we would have the following problem: say your low is 4000, marking a drawdown of 2000 from the previous high 6000, but the account goes up to 7000, the low would remain 4000 and this would make a "drawdown" of 3000, which of course is not true.

 

[cvds16]

Quote from ElectricSavant:

which comes first......think about it....


Thank you mr. sub....

Michael B.

I think we are just misunderstanding each other here, when i look at the other thread this is just what i meant, only i made this version up in percentage terms and probably could have explained it a bit better.

Still not too sure about your running valley concept ...