General Topics
Markets
Technical Topics
Brokerage Firms
Company Specific
Community Lounge
Site Support

Discussion in 'Trading Software' started by alanack, Nov 8, 2002.

1. ### Jeffrey

I am working on a trade tracking spreadsheet. The problem I need guidance with is the formula in the Capital Gain/Loss column.

Seven columns are used for this scenario:

1. A for the position short/long
2. B for the entry price
3. C for the exit price
4. D for the points =B1-C1
5. E for the capital earned =D*F*G (for NQ's)
6. F for Contracts
7. G for contract value \$20/contract NQ's

This works for shorting(=A1-B1) in calculating the points earned.
But I need the formula for "C" column to read =B1-A1 for Longs.

Is there anyone who can provide me with a formula for column "D" that says:
"If column A is short than(B1-C1) If column A is Long than (C1-B1)"

Jeffrey

#11     Nov 12, 2002
2. ### Jeffrey

Yahooooo!!!!

lol

#12     Nov 12, 2002
3. ### ddefina

@if(A1="S",B1-C1,C1-B1) The only problem with this is it defaults to long if it doesn't say "S", but it should work for what your doing.

#13     Nov 12, 2002
4. ### Jeffrey

Thanks, I'll give it a try here.

I had to go back and edit my post. I got confused between my stock and futures spread sheets.

Again, Thanks.

#14     Nov 12, 2002
5. ### opm8

www.mrexcel.com

Very quick answers to the most complex spreadsheet questions you can muster.

--opm8

#15     Nov 12, 2002
6. ### Jeffrey

The only way it defaults is if you don't fill in the position column short/long.

These formula's work:

Futures

=IF(A1="SHORT",B1*C1*D1,-(B1*C1*D1))

1. Where A column states the position SHORT or LONG
2. Where B column states the number of contracts
3. Where C column states the contract value
4. Where D column states the points calculated (entry minus exit)
5. Enter the above formula in column E to calculate Capital Gain

Stockâs

=IF(A1="SHORT",B1-C1,C1-B1)

1. Where A column states the position SHORT or LONG
2. Where B column states the entry price, or total cost.
3. Where C column states the exit price, or total revenue.
4. Enter the above formula in column D to calculate Capital Gain

Jeffrey

#16     Nov 12, 2002
7. ### Jeffrey

Correction:

The default is set with the last entry.(Long or Short)

#17     Nov 12, 2002
8. ### Jeffrey

Futures:

Correction: The points column needs the formula to change the number to - if long.

I'll have hold off before posting. Sorry, for any havoc.

Jeffrey

#18     Nov 13, 2002
9. ### Jeffrey

Replace this qoute for futures with my previous post.
The minus 1 added to point calculation is the cost of spread on NQ's. For a ES spreadsheet minus .5 for the spread.

Jeffrey

#19     Nov 14, 2002
10. ### Jeffrey

Replace with

=IF(A1="SHORT",B1*C1*D1,B1*C1*D1)

Hopefully those who need a worksheet are smart enough to figure it out. I just wanted to correct what I started.
I'm leaving anything else alone.

Jeffrey

#20     Nov 14, 2002
ET IS FREE FOR TRADERS BECAUSE OF THE FINANCIAL SUPPORT FROM THESE SPONSORS: