Expectancy Spreadsheet formulas...

H

Huios

Hey Traders…

I was wondering if anyone has put Van Tharp’s expectancy formula’s into an excel spreadsheet. I have been doing it by hand, but the number of trades is making this process pretty tedious. I did a search, but didn't find what I was looking for.

I have trouble figuring out the formulas. All my trades are in column A in points gain/loss format, (ie. 1.25; -.75).

For Average Win I use: =SUMIF(A:A,">=0")/COUNTIF(A:A,">=0")

For Average Loss I use: =SUMIF(A:A,"<0")/COUNTIF(A:A,"<0")

For Percentage Win I use: =COUNTIF(A:A,">0")/COUNT(A:A)

The above 3 formula’s I got off another spreadsheet and seem to be accurate.

My average risk in 1 point, so R = 1 (for now).

Scratch trades are -.25, 0, and +.25 pts.

.5R trades are .50 to .75; 1R trades are 1.0 to 1.25; 1.5R gains are 1.5 to 1.75: and so on. Reverse is true for losses.

Here is where my "programming deficiencies" lie…

What I want in my 1R cell is the sum of all trades in A that are equal to or greater than .75 and equal to or less than 1.25.

What I want in my # of 1R trades is the number of all trades in column A that were equal to or greater than .75 and equal to or less than 1.25.

Once I get these formula’s down, the rest is just changing numbers.

Any help would be greatly appreciated.

:)
 
Originally posted by Huios
What I want in my 1R cell is the sum of all trades in A that are equal to or greater than .75 and equal to or less than 1.25.
More...
=SUMIF(A:A,"<=1.25") - SUMIF(A:A,"<0.75")

A little cumbersome, but it works.

What I want in my # of 1R trades is the number of all trades in column A that were equal to or greater than .75 and equal to or less than 1.25.
More...
=COUNTIF(A:A,"<=1.25") - COUNTIF(A:A,"<0.75")

For the sake of correctness, to avoid dividing by zero (a possibility at the extremes), you could add an IF function in the denominator.
 
Mr Subliminal
=SUMIF(A:A,"<=1.25") - SUMIF(A:A,"<0.75")

=COUNTIF(A:A,"<=1.25") - COUNTIF(A:A,"<0.75")
More...

They work like a charm. Thank you. I was trying to get it all in one formula, ie =sumif(A:A, "<0.75","<=1.25") but kept getting an error.

Do you (you too GG) know any good sources that teach you how to write the formulas? Even an online help desk. Looked at the Microsoft.com but haven't found anything, yet.

Thanks again.
 
Originally posted by Huios
Do you (you too GG) know any good sources that teach you how to write the formulas? Even an online help desk. Looked at the Microsoft.com but haven't found anything, yet.

Thanks again.
More...
a few years ago i took a computer class and they taught us some excel stuff. other than that, 99% of what else i know is from trial and error...but i'm sure there's plenty of info on the internet if you search for it. try http://www.google.com.
 
I suggest that you leisurely go through the online help which has many good examples. Other than that, don't make a project out of it - you can always post the thorny problems on the ET Software Forum.
 
Originally posted by Huios


Do you (you too GG) know any good sources that teach you how to write the formulas? Even an online help desk. Looked at the Microsoft.com but haven't found anything, yet.
More...

I found a truly excellent book on the subject. Read this book, and you will take your Excel spreadsheets to a new level...

Microsoft Excel 2000 Formulas (by John Walkenbach)

It even comes with a CD-ROM with all the spreadsheets used in the examples in the book.
 
Originally posted by MartinWeinberg


I found a truly excellent book on the subject. Read this book, and you will take your Excel spreadsheets to a new level...

Microsoft Excel 2000 Formulas (by John Walkenbach)

It even comes with a CD-ROM with all the spreadsheets used in the examples in the book.
More...

you beat me to it...I was just going to suggest that book as well...

Huios:

This book lists all of the different formulas, functions, arrays, etc in a very "textbook" manner that you could reference for years to come...Highly recommended...
 
You must log in or register to reply here.
Back
Top