General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

Discussion in 'Trading Software' started by clambill, Aug 3, 2008.

1. ### clambill

OK, I finally completed my spreadsheet and I took the time to verify the calculations by hand so I know this should be the final version that works very well. If you want to use the file with the original program I used to write it, itâs free at...(you click on Calc after installation)

http://www.openoffice.org/

And some tips on how to use Microsoft Excel (the spreadsheet in the free Open Office is supposedly a âcloneâ of Excel)...

This spreadsheet will calculate the total profits or losses (including commissions as well), your percentage of winning trades (and losers), average profit or loss per trade, average percentage profit on winners (and losers as well), risk/reward ratio, number of winners (and losers) and total profits and total losses. All you have to do is type in your ticker symbols, entry price, exit price, number of shares traded, whether it was a long or short position and the commissions column you donât need to fill. (You can always click to change the formula and insert the right amount instead of \$25. So, tell me if you like it.

File size:
10.1 KB
Views:
1,470
2. ### clambill

HERE ARE THE FORMULAS IN CASE YOU WANT TO CONSTRUCT YOUR OWN.

column A
row 2: Ticker symbol

column B
row 2: Long or short

column C
row 2: Entry price

column D
row 2: Exit price

column E
row 2: N of shares

column F
row 2: Profit or loss
=IF(B3=âLâ;D3*E3-C3*E3;C3*E3-D3*E3)

column G
row 2: Net P/L
=F3-25
(this is a \$25 round-trip commission, you can double-click to change the amount)

column H
row 2: Profit=1
=IF(F3>0;1;0)

column I
row 2: Losses=1
=IF(F3<0;1;0)

column J
row 2: Total cost
=C3*E3

column K
row 2: % win or loss
=F3/J3*100

column L (here the title is in column L but the formula is in column M so Iâll just add the title to the column M description)

column M
row 2: Total profit or loss
=SUM(F3:F10)

row 3: Including commissions
=SUM(G3:G10)
(in reality, the calculations for % of gain on winners etc is all done with the results excluding the cost of commissions)

row 4: % of winners
=M9/M11*100

row 5: % of losers
=M10/M11*100

row 6: Avg P/L per trade
=M2/M11

row 7: Avg % loss on losers
=M15/M10

row 8: Avg % profit on winners
=M14/M9

row 9: Number of winners
=SUM(H3:H10)

row 10: Number of losers
=SUM(I3:I10)

=SUM(M9:M10)

row 12: Total profits
=SUMIF(H3:H10;â>0â;F3:F10)

row 13: Total losses
=SUMIF(I3:I10;â>0â;F3:F10)

row 14: Total % gains
=SUMIF(H3:H10;â>0â;K3:K10)

row 15: Total % losses
=SUMIF(I3:I10;â>0â;K3:K10)

row 16: Risk/reward ratio
=M8/M7*-1

winning percentage is for amateurs.

4. ### Surdo

Hey Dickweed:

Do you ever contribute anything positive to this board?
Your negativity on every thread starts to get old after a while.

I just thought I would welcome you to my ignore list prick.

el surdo

5. ### clambill

Cool, someone posted an Excel version on the trade2win software forum.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: