• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Forex conditional P/L formula

C

ChaosTrader63

New Member
Hello! I am trading forex and made a trading journal using excel and I am keeping track of my profit/loss. In forex if you enter a long trade then you are buying and expect the price to increase. So if you enter a trade at 1.30000 and exit at 1.30500 then that would be a 50 pip profit. If the price decreases to 1.29500 then that would be a -50 loss.

On the other hand if you enter a short trade then you are expecting the price to decrease so entering at the same price of 1.30000 and exiting at 1.29500 this would be a 50 pip profit but if you exit at 1.30500 that would be a -50 pip loss. I'm trying to get a formula that will give me a profit or loss based off of a combobox stating long or short on my trade. Here's how I have it set up:


cell B14 = combobox with long or short

cell B16 = entry price

cell B21 = exit price

cell B22 = profit/loss


Any help with this would be greatly appreciated! Thanks!
 
Not very elegant, but try this


=(B21-B16)*(IF(B14="Long",1,-1))*10000


OR


=(B21-B16)*(2*(B14="Long")-1)*10000


OR


=(B21-B16)*((2*(B14="Long")-1)&"0000")
 
ChaosTrader63


Try either of these:

=(B21-B16)*IF(B14="Long",10000,-10000)


or


=10000*(B21-B16)*IF(B14="Long",1,-1)*("send share of profits to Hui")
 
Sam your formulas are adding and subtracting the value but not based off of the long or short position. I am attaching a file that you can look at from dropbox.com if you like. Thanks for the help!

https://www.dropbox.com/s/stdmqhdy8r9tu82/Carl%27s%20Trading%20Log.xlsm
 
ChaosTrader63, can you recheck. I tried the formula in your file, and put 1.3 in B16 and 1.305 in B21, and the formula I posted above (all of the formulas above actually) gives result.


The problem is with the activex control combobox that you are using to update the values in B14 between Long and Short. You don't need an activex control to do this. It is like pulling up a bazooka to terminate a roach.


Instead, select the cell B14, go to Data Validation, and select the List option, and in the field where you pass value, just type


Long,Short


Now you will be able to use the data validation just like a drop-down.
 
You must log in or register to reply here.
Back
Top