spreadsheet newbie

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)"

Thanks in advance.

Jeffrey
 
Quote from Jeffrey:

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

Five 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 =B*C*D

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)"

Thanks in advance.

Jeffrey
More...


@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.
 
Quote from 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.
More...



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.
 
Quote from 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.
More...


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
 
Quote from Jeffrey:




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



Correction:

The default is set with the last entry.(Long or Short)
 
Quote from 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




Jeffrey
More...



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
 
Quote from Jeffrey:





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 =IF(A1="SHORT",F1-G1-1,G1-F1-1)
5. Enter the above formula in column E to calculate Capital Gain
6. Where F column states Entry price
7. Where G column states Exit price
More...

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
 
Quote from Jeffrey:





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

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
 
You must log in or register to reply here.
Back
Top