Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

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

  1. #11
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,720
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

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

    1) To fix transaction 1, change the multiplier in the table for OIL from 10 to 100.

    2) When posting a sample sheet, please post the expected results. On your original post, you had 30 as the result of the transaction, so that's what I tried to match. Had you posted 208, I would have made the OIL multiplier 100.

    3) If you already have hidden columns, there's no reason you can't hide the Symbol/Multiplier table, or put it on another sheet.

    4) There is no way with a normal Excel function that can detect how many decimals are used in the formatting. This makes me wonder how your data is entered. Do you enter it manually, then manually adjust the formatting? Or it is imported via some macro that adjusts the formatting at the same time? If it's via a macro, that macro can also be adjusted to insert the totals in columns J:K.

    5) There is an odd Excel function that could do what you want. You can try these formulas:

    J3: =IF(I3="BEP",0,IF(I3="P","",-ABS(F3-H3)*10^RIGHT(CELL("format",F3))))
    K3: =IF(I3="BEP",0,IF(I3="L","",ABS(F3-G3)*10^RIGHT(CELL("format",F3))))

    Copy down the columns as needed. The problem with these formulas is that they are not dynamic. If you change the formatting on F3, these formulas will not change. You'd need to manually recalculate them by pressing F9. Or if you change the values in F3:H3, remember to change the formatting first, then the values which does cause a recalculate. But that seems a bit risky if you forget. Also, using these formulas will require you to save your workbook as a macro-enabled workbook.

    6) I wrote a UDF just to see how that would work. It has the same limitation as the CELL formula, it does not update as a result of a format change, so there's no benefit in going that route.


    So that pretty much sums up your options.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  2. #12
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hello Eric,

    Thanks a lot for doing your best to help me

    But unfortunately the formulas you provide is not working.

    Anyway you can access my sheet on Excel Online Click Here

    This is a copy and i keep the original for me.

    All what i want from you is to test all the types of (Buy , Sell , Buy Limit, Sell Limit, Buy Stop , Sell Stop)

    Enter a deal by yourself and try to get the "Pips In Lose" and "Pips In Profit" depending on deal type and price format.

    I am sure you are an expert and when you work on the sheet itself , you gonna find the solution !

    All what i need at the end is to get the J AND k Auto-filler with correct result. Please without adding any column

    Once the formulas are done correctly and tested 100% , Please come here and paste them to me.
    Waiting your reply as soon as possible.
    Last edited by Polat; Jul 6th, 2018 at 02:50 PM.

  3. #13
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    You can try those deals as test :

    S/N Symbol Type Entry Point Take Profit Stope Lose
    1 EUR/USD Buy 1.1600 1.1650 1.15050
    2 EUR/JPY Buy Limit 127.00 128.50 126.50
    3 GBP/JPY Buy Stop 145.00 146.20 144.80
    4 GOLD Sell 1250.00 1240.00 1260.00
    5 OIL Sell Limit 73.00 72.50 73.50
    6 GBP/USD Sell Stop 1.3200 1.3100 1.3260
    Last edited by Polat; Jul 6th, 2018 at 03:05 PM.

  4. #14
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    1) EUR/USD should give 50 pips profit in case we type P and -50 pips lose in case L
    2) EUR/JPY should give 150 pips profit in case we type P and -50 pips lose in case L
    3) GBP/JPY should give 120 pips profit in case we type P and -20 pips lose in case L
    4) GOLD should give 100 pips profit in case we type P and -100 pips lose in case L
    5) OIL should give 50 pips profit in case we type P and -50 pips lose in case L
    6) GBP/USD should give 100 pips profit in case we type P and -60 pips lose in case L
    Last edited by Polat; Jul 6th, 2018 at 03:18 PM.

  5. #15
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,720
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

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

    The "format" suboption of CELL does not work on Excel Online. https://support.office.com/en-us/art...f-955d67c2b2cf

    In any case, I don't recommend using it, since it doesn't update dynamically. My best recommendation is to create a table like I showed, with the Symbol/Multiplier. You can even use the same table with Conditional Formatting to automatically show the right number of decimals so you don't have to constantly reformat the price cells.

    But I'm not aware of any other ways to do what you want. Sorry.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #16
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Okay Thanks for your time.

  7. #17
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hello Mr. Eric ,

    I made as you said and everything is okay now but :

    - I still have 1 problem i think you can solve it for me and i will appreciate that really.

    Check the following Image for explanations:



    And here is the formula i have for the I3 cell "Profit" :

    =IF(D3="Buy",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Limit",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Stop",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Limit",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Stop",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),0))))))

    waiting your reply as soon as possible,

    Regards.

  8. #18
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,720
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

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

    You can try this:

    =IF(H3="","",IF(D3="Buy",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Limit",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Stop",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Limit",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Stop",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),0)))))))

    Also, there's a lot of duplication in that formula. By consolidating some things, you get this:

    =IF(H3="","",IF(OR(D3={"Buy","Buy Limit","Buy Stop"}),H3-E3,IF(OR(D3={"Sell","Sell Limit","Sell Stop"}),E3-H3))*VLOOKUP(C3,$M$3:$N$35,2,0))

    In fact, if you take advantage of the fact that all 3 of your Buy options start with B, and if you only have those 6 options, you can get it all the way down to:

    =IF(H3="","",IF(LEFT(D3)="B",H3-E3,E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0))

    You may prefer the second version though, because it explicitly shows the options.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #19
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    The Last one is worked perfect !

    Thanks a lot my brother for helping me solving this issue ,

    Appreciated,. GOD BLESS you.

    My Regards.

  10. #20
    New Member
    Join Date
    Jul 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Sorry for bother , but i need a last small thing from you and everything will be perfect!

    =IF(H3="","",IF(LEFT(D3)="B",H3-E3,E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0))

    What i need is to add to same formula this :

    if H3 value = E3 value return 0 zero

    Thanks.
    Last edited by Polat; Jul 12th, 2018 at 05:05 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •