formulas for spreadsheet for trading log

[clambill]

OK, I did a search on this forum and I also looked at some of the Excel video tutorials on youtube at
http://www.youtube.com/watch?v=8L1OVkw2ZQ8#
I find those videos pretty good and everything but I did the following....(look at screenshot)

(BTW, those are totally fictitious numbers. I have not traded those stocks nor have I even paper traded those stocks. And the reason why I put $25 in commissions which you can’t see in there anyway is because I have TradeFreedom which is the cheapest broker I could find in Canada that requires less than $10,000 as a minimal amount.)

Anyway, the formulas used were for example for the “profit or loss” column E: =C3*D3-B3*D3 and for total profit or loss: (D37). I can use explanations for Excel although I’m really using Works 2000.

So, what I want to know is does anyone know what formulas I need to use for “% of winners”, “avg P/L per trade” and “avg % loss on losers”?

 

[Bob111]

http://www.mrexcel.com/archive/Dates/20226.html

=AVERAGE(IF(A1:A5,A1:A5))} where your values
are contained in the cell range A1:A5.

http://www.algebrahelp.com/calculators/number/percentages/

 

[Boib]

Excel has an “If” function:

Set up a column with this function so that IF the trade is a win THEN value =1 if not then value =0.

Set up another column so the IF trades is a loss THEN value =1 if not then value =0.

Total each column. This give you the number of wins and losses. You can then figure out percentages.

You can do the same thing for the amounts of your wins and losses.
IF trades is a win THEN value =the amount of the win; if not then value =0
IF trades is a loss THEN value =the amount of the loss; if not then value =0.

Total each column and divide by the number of wins or losses to give you an average win /loss amount and you can figure out percentages .

I don’t know if "Works" will let you do this. You might try “Open Office” it’s supposed to be a free excel clone.

 

[clambill]

Wow, thank you both for making an effort to be helpful.

You mean the open office at http://www.openoffice.org/
?

This is great. I didn't know there was a free software program like that.

 

[dan_s]

You also might want to check out the COUNTIF and SUMIF functions. You can get decent help online if you google excel function names.

I haven't tried it, but I'm pretty sure MS Works supports all of the basic Excel functionality (including if functions).

 

[Bob111]

http://math.about.com/gi/dynamic/of....com/b_davidso/Web_Page_Files/Excel/math.html

http://www.functionx.com/excel/Lesson12.htm

http://office.microsoft.com/en-us/excel/HP052042111033.aspx

http://www.gummy-stuff.org/

should be enough for next few years

 

[clambill]

OK IT’S ALMOST ALL DONE!!! Of course, I noticed I forgot to add a formula for when I take on a short position. That’s the only issue now and it’s kind of weird because I don’t know how to correct that yet. So, I guess I’d have to temporarily have one spreadsheet for long positions and another one for short positions. This might not be a bad idea since it would allow me to analyze results separately.

I'm going to add 2 screenshots if you have a hard time following what I’m talking about here. These “formulas” were all typed in the free Open Office program found on the link listed above. The screenshots are also from that program. The other little odd thing is if you want to do this spreadsheet, then for practical reasons you might actually want to put rows 9 to 22 on the side instead. This is because every time you’d want to add another line, you’d end up pushing back the information on the bottom.

Here we go. The “formulas” I typed in are the following...

-column E profit or loss
=CD*D3-B3*D3

-column F net P/L
=E3-25
(This assumes round trip commissions of $25. This is high but I’m temporarily using a more expensive online broker.)

-column G profit=1
=IF(E3>0;1;0)
(This is to say the profitable trades are counted in a column as 1 so I can calculate the total number of winning trades which is necessary for other calculations. If E3 is greater than 0 then 1 else 0.)

-column H loss=1
=IF(E3<0;1;0)

-column I total cost
=B3*D3

-column J % win or loss
=E3/I3*100

-row 9 total profit or loss
=SUM(E3:E7)

-row 10 including commissions
=SUM(F3:F7)

-row 11 % of winners
=B15/B17*100

-row 12 avg P/L per trade
=B9/B17

-row 13 avg % loss on losers
=B21/B16

-row 14 avg % profit on winners
=B20/B15

-row 15 number of winners
=SUM(G3:G7)

-row 16 number of losers
=SUM(H3:H7)

-row 17 number of trades
=SUM(B15:B16)

-row 18 total profits
=SUMIF(G3:G7;”>0”;E3:E7)

-row 19 total losses
=SUMIF(H3:H7;”>0”;J3:J7)

-row 20 total % gains
=SUMIF(G3:G7;”>0”;J3:J7)
(This is just a number used for other calculations just like the one right below.)

-row 21 total % losses
=SUMIF(H3:H7,”>0”;J3:J7)

-row 22 risk reward ratio
=B14/B13*-1

Anyway, if you do the spreadsheet as it’s laid out here, please tell me if you come up with accurate numbers and if you have any comments to make to improve on it or whatever.

 

[clambill]

Here is the other screenshot...