Trying to make a formula to calculate the Profit and Lose in Forex trades.

Polat

New Member
Joined
Jul 3, 2018
Messages
19
Hello Dears,


As in image above : i am trying to make a formula for column J and K:

- As in column "J" i need the Pips In Lose to be calculated in case i type "L" in column "I"
- As in column "K" i need the Pips in Profit to be calculated in case i type "P" in column "I"
- In column "I" when i type BEP (Break Even Point) it should give 0 in both "J" and "K"

So, To calculate the Pips In Lose and Pips In Profit you have to check the following first:

1 - Check if the deal is (Buy/Buy Limit) SAME JOB Or (Sell/Sell Limit) SAME JOB "But Mist type the 4 types in formula"
2- The Price format for example EUR/USD is x.xxxx e.g 1.1600 , and EUR/JPY is xxx.xx e.g 129.00 , and Oil is xx.xx e.g 72.00.

I THINK FOR (1 AND 2) ABOVE, NEEDS TO USE NESTED IF , AND I DON'T KNOW HOW LOL


Now
how to calculate Pips In Lose and Pips In Profit ..

* IN case the position is Buy Or Buy Limit :

Pips In Profit =
Take Profit - Entry Point.
Pips In Lose = Entry Point - Stope Lose (result must be in minus.)

* In case the position is Sell or Sell Limit :

Pips In Profit = Entry Point - Take Profit.
Pips In Lose = Stope Lose - Entry Point (result must be in minus.)

Hope you help me with the formula to contains all the above cases , :)

My Regards.
 

Polat

New Member
Joined
Jul 3, 2018
Messages
19
2- The Price format for example EUR/USD is x.xxxx e.g 1.1600 , and EUR/JPY is xxx.xx e.g 129.00 , and Oil is xx.xx e.g 72.00.

IN-addition, i forgot to add the 4ths case which is GOLD Price xxxx.xx1259.00

So now there are 4 prices format to be added in the formula : (X.XXXX AND XXX.XX AND XX.XX AND XXXX.XX)
 

Polat

New Member
Joined
Jul 3, 2018
Messages
19
Up , Please experts help me with the formula !
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,959
The formulas you need aren't that hard. Consider:

ABCDEFGHIJKLMNOP
1
2S/NSymbolTypeEntry PointTake ProfitStop LossP/L/BEP?Pips in LossPips in ProfitSymbolMultiplier
31OILSell Limit74.2872.274.73P20.8OIL10
42GBP/JPYBuy Limit145.83145.98144.69P15GBP/JPY100
53GBP/JPYBuy Limit145.36145.98144.69P62EUR/USD10000
64EUR/USDBuy1.1651.16441.158L-70EUR/JPY100
75EUR/JPYBuy128.95129.3112.3P36GBP/USD10000
86GBP/USDBuy1.31781.3128L-50
97EUR/USDBuy1.16291.16441.158P15
108GBP/USDBuy1.31591.3128L-31
119OILSell Limit74.174.174.1BEP00
1210EUR/JPYBuy128.52129.31127.7P79

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13

Worksheet Formulas
CellFormula
J3=IF(I3="BEP",0,IF(I3="P","",-ABS(H3-F3)*VLOOKUP(D3,$N$3:$O$7,2,0)))
K3=IF(I3="BEP",0,IF(I3="L","",ABS(F3-G3)*VLOOKUP(D3,$N$3:$O$7,2,0)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



What is tricky is that what you consider a "pip" varies by symbol. Anywhere from a tenth of a percentage to 1/10000. In order to standardize what you call a pip, I created an additional table with the multiplier needed by symbol. However, even with the table, the results do not match your sample sheet. Some can be explained by rounding, some not. EUR/USD in particular works out different on the 2 examples here.

Hope this helps.
 

Polat

New Member
Joined
Jul 3, 2018
Messages
19
Hello , Thanks for replying ,

But adding 2 new columns to my sheet will destroy everything , also i can't add more columns because i am hiding the others column for the design ,

SO asking me to add these columns is so hard.

i just need the formula for existing table .. as i said the formula i need must be in both J and K with those considerations :

* Pips is like Points.
* Buy means Buy market now, Buy Limit and BUy stop are pending commands to be executed auto from aimed prices. at the end did same job
* Sell means Sell Market now, Sell Limit and Sell stop are pending commands to be executed auto from aimed prices. at the end did same job

e.g:
============================================================================

1- Buy Or Buy Limit Or Buy Stop the EUR/USD at :

Entry Point: 1.1600
Take Profit: 1.1650
Stop Lose: 1.1550
if the price goes (UP) to 1.1650 there are (50 pips Profit) different. if the price went (DOWN) to 1.1550 there are (-50 pips Lose)
how we calculate the profit and losefor this trade :
Pips In Profit = Take profit - Entry point * 10000
Pips In Lose = Entry Point - Stop Lose * 10000 (should be in minus).

=================================================================================

2- Sell Or Sell Limit Or Sell Stop the EUR/USD at:

Entry Point: 1.1600
Take Profit: 1.1550
Stop Lose: 1.1650
if the price went (DOWN) to 1.1550 there are (50 pips Profit) different. if the price goes (UP) to 1.1650 there are (-50 pips Lose)
how we calculate the profit and losefor this trade :

Pips In Profit
= Entry Point - Take Profit * 10000
Pips In Lose = Stop Lose - Entry Point * 10000 (should be in minus).

I multiply with 10000 because the price format here is
x.xxxx (1.1600)

NOTICE: Keep in mind the prices format will be in :

- X.XXXX e.g EUR/USD = 1.1600
- XXX.XX e.g EUR/JPY = 129.00
- XX.XX e.g OIL = 72.00
- XXXX.XX e.g GOLD = 1257.00

Means sometimes we have to multiply with 10 or 100 or 1000 depends on price format.

======================================================================
"P" = Profit
"L" = Lose
"BEP" = Break Even Point

Entry Point is F3

Take Profit is G3
Stop Lose is H3
Pips In Profit is K3
Pips In Lose is J3
I3 <-- I will type manually "P" or "L" or "BEP" in it to calculate me the :

If I type "P" Must Auto calculate the Pips in profit with checking the Type deal (buy,buy limit, buy stop, sell, sell limit, sell stop) plus checking the price format.
If i type "L" Must Auto calculate the Pips in lose with checking as the above too.
If I type "BEP" bust give zero in both columns (J3 and K3)

I think everything is clear now , no need to add 2 columns and to destroy my design and everything. There is a formula contains all these cases

USING "NESTED IF FORMULA"
 
Last edited:

Polat

New Member
Joined
Jul 3, 2018
Messages
19
Another Notice : Your formula giving is only working for the Deals Type : Buy/Buy Limit/Buy Stop And giving wrong results for the All Sell's Types.
 

Forum statistics

Threads
1,081,518
Messages
5,359,244
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top