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!

 

[JackDanIce]

Welcome to the board

Try this as formula in B21:

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

 

[ChaosTrader63]

Welcome to the board

Try this as formula in B21:

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

Thanks for the response! The profit is showing as minus when it should show plus profit. It needs to distinguish between a sell and buy.

 

[JackDanIce]

You said Long or Short, not buy or sell. If B15=Long then my formula takes the entry price AWAY from the exit price (if long, then profit should occur when the exit price is higher than the entry price, if short, vice versea), multiplies it by 10,000 to give an answer in pips. Otherwise, because if it's not a Long then it can only be a short so the formula takes the exit price away from the entry price, before multiplying by 10,000.

Change the formula from (B15="Long" to (B15="Buy" if you're using the words Buy and Sell instead of Long and Short

 

[ChaosTrader63]

You said Long or Short, not buy or sell. If B15=Long then my formula takes the entry price AWAY from the exit price (if long, then profit should occur when the exit price is higher than the entry price, if short, vice versea), multiplies it by 10,000 to give an answer in pips. Otherwise, because if it's not a Long then it can only be a short so the formula takes the exit price away from the entry price, before multiplying by 10,000.

Change the formula from (B15="Long" to (B15="Buy" if you're using the words Buy and Sell instead of Long and Short

Your formula is bringing back minus such as -5 when it should be +5. It's doing that for both long and short so its saying long trade entry 1.30000 and exit 1.30050 = -5. Also this should say 50 and not 5.
Do I also have to place a formula in the long and short cell. I have a combo box for long and short. Once again Thanks!

 

[JackDanIce]

Could be because of the combo box - remove it and type in either "Long" or "Short" in cell B15. I'll try to think of a way you can use the combo box when I get into work today

 

[JackDanIce]

I've just set up a drop down combo box with "Long" and "Short" and it works fine for me when I switch between the two directions. Without seeing the setup of your spreadsheet, can't suggest anything else as it works for me (same cells, same formula and with a drop down box)...

 

[ChaosTrader63]

I've just set up a drop down combo box with "Long" and "Short" and it works fine for me when I switch between the two directions. Without seeing the setup of your spreadsheet, can't suggest anything else as it works for me (same cells, same formula and with a drop down box)...

I appreciate you! If it's working for you then I have to figure out the minor detail that is hindering me but mainly I have the code. Thank you!

 

[JackDanIce]

Good luck!