General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

Discussion in 'Trading Software' started by clambill, Jul 28, 2008.

1. ### clambill

OK, I did a search on this forum and I also looked at some of the Excel video tutorials on youtube at
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â?

File size:
100.9 KB
Views:
109

3. ### 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.

4. ### 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.

5. ### 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).

7. ### 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)

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

File size:
103.4 KB
Views:
49
8. ### clambill

Here is the other screenshot...