Hello all, I read the posting guide, and so on going to attempt to follow to my best ability.
I am a day trader and somewhat familiar with Excel.
I've attempted creating the spreadsheet on my own, but realized after quite some time that it was too complex for me. So the help is greatly appreciated.
Let me just note, that the math in this is very simple, it's the formula that is kicking my butt. As to why I need the help. So don't get overwhelmed by how elaborate the notes below are.
A quick crash course in the math behind the madness.
1 e-mini futures contract goes for 12.50 per .25c move (each .25c move is called a TICK)
2000, 2000.25, 2000.50, 2000.75, 2001
1 e-mini futures POINT (I.E. 2000 - 2001) is = 50.00 with 1 contract. (1contract x 12.50 a TICK x 4 TICKS = 50.00)
I am a daytrader and I need to start journaling my trading. This consists of (for now) one cell for my entry price. One cell for my stop price. And multiple columns for multiple exits. This is known as "scaling out".
I've included an image below to help anyone willing to help, better grasp what I am trying to accomplish.
Gyazo - 1da94ef1dacc1802e475a2b5871b0da7.png
As you can see, I have given three different examples.
The first example illustrates me buying (e6 ="L" for LONG aka BUYING) 7 contracts At ENTRY PRICE 2091. And then scaling out At each target (denoted T# ie T1)
In this example c6:s6 MY trade hit all 6 targets or (T1 - T6)
In a separate spreadsheet named data, is an array for what TICK value each symbol accumulates at.
Gyazo - 7c74435d842571198013cb8f2089b7e8.png
So, in the image above, you'll see that the ES moves at a +- $.25c increments. These are known as TICKS. And each tick value is worth $12.50 (in the ES)
You can also see in the data table that the US crude oil or CL moves at a much more rapid TICK increment, $.01c and each tick is worth $10.00
So in a nutshell, the math looks like this:
7x ES futures contracts = $3500 (or $500/contract)
Each full point move = $50/contract
BOUGHT 7 Contracts at entry price 2091
2x @ 2092 = $100 (sold 2 contracts at 2092)
1x @ 2093.25 = $112.50 (sold 1 contract at 2093 and so on...)
1x @ 2094 = $150
1x @ 2095.50 = $225
1x @ 2096 = $250
1x @ 2097 = $337.50
The sum of those adds up to $1175.00
Here's the hard part, for me anyways
I need a formula that checks What symbol is in the cell i.e. ES. Then looks it up in the data sheet, and then uses those constants in order to calculate the total profit. And then I think it needs to be able to check Columns H:U in order to find out which columns or cells actually have contracts where price was sold or bought at.
That brings me to another caveat, the formula must also check Column E And if it is "L" The close price, or price at which I sold it back, is subtracted from the entry price. And if: E reads "S" The formula must be turned around So that the entry price needs to be subtracted from the closing price(s).
NOTE: As you can see in the second example C7:U7 , each column doesn't have to be filled with data.
In this example I sold (E7 = "S") 4 contracts at entry price 2090.25, Bought one back at 2089.25, But two back at 2085.50, And the last two contracts got stopped out ( just a fancy term for automatically buying it back ) at 2083.75 (T7:U7)
4x ES futures contracts = $1300
Each full point move = $50/contract .25x4x12.50
SOLD 4 Contracts at entry price 2090.25
1x @ 2089.25 = $100 (Bought 1 contract back at 2089.25)
2x @ 2085.50 = $475 (Bought 2 contracts back at 2085.50)
1x @ 2083.75 = $325 ( I know in the screenshot it says 2 contracts, but that is a goof up on my part. this one got stopped out at a much lower price than target six or T6, for a much bigger profit. This is why it is in the stopped out columns.)
The sum of those adds up to $900.00
And then in the final example with CL, this trade got stopped out (T7:U7) with a loss at my stop price (G7), Which I'm pretty sure I can handle this one as far as the formula
. So there it is,It would mean so much to me and be such a great help if someone could solve this enigma. I have looked online for examples, I've beat my head against the wall. But I just cannot figure this out. Thanks so much.
Please let me know if you have any further questions I'll be happy to help where I can.
I am a day trader and somewhat familiar with Excel.
I've attempted creating the spreadsheet on my own, but realized after quite some time that it was too complex for me. So the help is greatly appreciated.
Let me just note, that the math in this is very simple, it's the formula that is kicking my butt. As to why I need the help. So don't get overwhelmed by how elaborate the notes below are.
A quick crash course in the math behind the madness.
1 e-mini futures contract goes for 12.50 per .25c move (each .25c move is called a TICK)
2000, 2000.25, 2000.50, 2000.75, 2001
1 e-mini futures POINT (I.E. 2000 - 2001) is = 50.00 with 1 contract. (1contract x 12.50 a TICK x 4 TICKS = 50.00)
I am a daytrader and I need to start journaling my trading. This consists of (for now) one cell for my entry price. One cell for my stop price. And multiple columns for multiple exits. This is known as "scaling out".
I've included an image below to help anyone willing to help, better grasp what I am trying to accomplish.
Gyazo - 1da94ef1dacc1802e475a2b5871b0da7.png
As you can see, I have given three different examples.
The first example illustrates me buying (e6 ="L" for LONG aka BUYING) 7 contracts At ENTRY PRICE 2091. And then scaling out At each target (denoted T# ie T1)
In this example c6:s6 MY trade hit all 6 targets or (T1 - T6)
In a separate spreadsheet named data, is an array for what TICK value each symbol accumulates at.
Gyazo - 7c74435d842571198013cb8f2089b7e8.png
So, in the image above, you'll see that the ES moves at a +- $.25c increments. These are known as TICKS. And each tick value is worth $12.50 (in the ES)
You can also see in the data table that the US crude oil or CL moves at a much more rapid TICK increment, $.01c and each tick is worth $10.00
So in a nutshell, the math looks like this:
7x ES futures contracts = $3500 (or $500/contract)
Each full point move = $50/contract
BOUGHT 7 Contracts at entry price 2091
2x @ 2092 = $100 (sold 2 contracts at 2092)
1x @ 2093.25 = $112.50 (sold 1 contract at 2093 and so on...)
1x @ 2094 = $150
1x @ 2095.50 = $225
1x @ 2096 = $250
1x @ 2097 = $337.50
The sum of those adds up to $1175.00
Here's the hard part, for me anyways
I need a formula that checks What symbol is in the cell i.e. ES. Then looks it up in the data sheet, and then uses those constants in order to calculate the total profit. And then I think it needs to be able to check Columns H:U in order to find out which columns or cells actually have contracts where price was sold or bought at.
That brings me to another caveat, the formula must also check Column E And if it is "L" The close price, or price at which I sold it back, is subtracted from the entry price. And if: E reads "S" The formula must be turned around So that the entry price needs to be subtracted from the closing price(s).
NOTE: As you can see in the second example C7:U7 , each column doesn't have to be filled with data.
In this example I sold (E7 = "S") 4 contracts at entry price 2090.25, Bought one back at 2089.25, But two back at 2085.50, And the last two contracts got stopped out ( just a fancy term for automatically buying it back ) at 2083.75 (T7:U7)
4x ES futures contracts = $1300
Each full point move = $50/contract .25x4x12.50
SOLD 4 Contracts at entry price 2090.25
1x @ 2089.25 = $100 (Bought 1 contract back at 2089.25)
2x @ 2085.50 = $475 (Bought 2 contracts back at 2085.50)
1x @ 2083.75 = $325 ( I know in the screenshot it says 2 contracts, but that is a goof up on my part. this one got stopped out at a much lower price than target six or T6, for a much bigger profit. This is why it is in the stopped out columns.)
The sum of those adds up to $900.00
And then in the final example with CL, this trade got stopped out (T7:U7) with a loss at my stop price (G7), Which I'm pretty sure I can handle this one as far as the formula
. So there it is,It would mean so much to me and be such a great help if someone could solve this enigma. I have looked online for examples, I've beat my head against the wall. But I just cannot figure this out. Thanks so much.
Please let me know if you have any further questions I'll be happy to help where I can.
