Finding the pip profit of a forex trade

[ChaosTrader63]

Hello all! I am very new to the forum and even newer to excel macros and VBA. What I am trying to do is have excel calculate the amount of pip profit of a trade in forex. For instance if I was making a short or sell trade and the price was 1.3000 and price moved to 1.2950 then that would equal 50 pips profit so I just want this calculated automatically by excel. I will add the entry and exit prices manually. Also the formula has to know if it's minus profit. So with the same numbers if I was making a long or buy trade and it went from 1.3000 to 1.2950 then it should show this as -50 pips.
My cells are as follows:

B15 long or Short
B16 Entry Price
B20 Exit Price
B21 Profit/Loss in pips

Hopefully I explained this well enough and I appreciate the help. Thanks!

[dluhut]

Try this and see if it works...not tested

Please Login or Register  to view this content.

[ChaosTrader63]

Thanks for the response! After putting the formula in it says, "You have entered to many argument for this function".

[dluhut]

=IF(B15="Long", (B20-B16)*10000, IF(B15="Short", (B20-B16)*-10000, ""))

Missing another IF

[Ace_XL]

Try

=(B20-B16)*LOOKUP(B15,{"Long","Short"},{1,-1})*10000

[TYTWEN]

hi can you please modify the formula in the last comment which can used as both formula in one,fo example shown earlier is

1.3000 to 1.2950 then it should show this as -50 pips <<<<< USD pair
1.3000 and price moved to 1.2950 then that would equal 50
AUDCAD
AUDCHF
AUDNZD
AUDUSD
CADCHF
EURAUD
I'LL ADD THE REST

198.78 to 198.77 than it should show -10 pips <<<< Yen pair
198.78 198.88 thanit shou show 10 pips
AUDJPY
CADJPY
CHFJPY
EURJPY
GBPJPY
NZDJPY
USDJPY

is there any way that i can use this formula with Yen pairs and usd pair like by selecting the pair results calculate by selection of the pair to the same box

=(B20-B16)*LOOKUP(B15,{"Long","Short"},{1,-1})*10000 OR =(B20-B16)*LOOKUP(B15,{"Long","Short"},{1,-1})*100 = PIPS

THANKS IN ADVANCE

[FlameRetired]

Administrative Note:

Hello TYTWEN. Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Looking forward to seeing your threads.