+ Reply to Thread
Results 1 to 9 of 9

Please can someone help with a Forex Type Spreadsheet

Hybrid View

  1. 11-20-2019, 03:47 PM #1
    pricey75
    pricey75 is offline
    Registered User
    Join Date
    02-02-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    10

    Question Please can someone help with a Forex Type Spreadsheet

    Hi,

    Please can some help, I am trying to write a formula in Excel to work out Stop Loss/Take Profit etc

    However depending on Pairs traded the formula is a little different

    Trying to "use if cell contains" JPY for Japanese Yen then multiply by 0.20 if not contains JPY then multiply by 0.0020, there are 28 diffent pairs so just want it to look for string JPY if possible

    Struggling to get it work

    Thanks in advance.

    See sheet attached, i have managed to get it to work on different sheets but trying to combine if possible to a master sheet
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-20-2019, 03:54 PM #2
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Please can someone help with a Forex Type Spreadsheet

    =0.2*IFERROR(FIND($O$1,C4)*0+E4,E4%)
    iD1="JPY"
    Reply With QuoteRegister To Reply
  3. 11-20-2019, 04:56 PM #3
    pricey75
    pricey75 is offline
    Registered User
    Join Date
    02-02-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    10

    Re: Please can someone help with a Forex Type Spreadsheet

    Sorry, so should have said in the combined sheet (attached) how cvan nest the IF statement while looking for JPY String

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-20-2019, 04:29 PM #4
    pricey75
    pricey75 is offline
    Registered User
    Join Date
    02-02-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    10

    Re: Please can someone help with a Forex Type Spreadsheet

    Where would I put this to combine getting a error
    Reply With QuoteRegister To Reply
  5. 11-20-2019, 04:36 PM #5
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Please can someone help with a Forex Type Spreadsheet

    sorry
    O1="JPY"
    Reply With QuoteRegister To Reply
  6. 11-23-2019, 10:33 AM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,856

    Re: Please can someone help with a Forex Type Spreadsheet

    Perhaps this will help.
    I combined the two tables on the Non-JPY sheet with the "Multipliers" for JPY on row 1.
    The formula used in F4:F7 is:
    Formula: copy to clipboard 
    =IF(D4="","",IF($D4="Buy",$E4+IF(ISNUMBER(SEARCH($C$1,$C4)),F$1,F$2),$E4-IF(ISNUMBER(SEARCH($C$1,$C4)),F$1,F$2)))

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  7. 11-24-2019, 02:00 AM #7
    pricey75
    pricey75 is offline
    Registered User
    Join Date
    02-02-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    10

    Re: Please can someone help with a Forex Type Spreadsheet

    Excellent, Thanks so much.
    Reply With QuoteRegister To Reply
  8. 11-24-2019, 02:44 AM #8
    pricey75
    pricey75 is offline
    Registered User
    Join Date
    02-02-2016
    Location
    England
    MS-Off Ver
    2007
    Posts
    10

    Re: Please can someone help with a Forex Type Spreadsheet

    Sorry how, would I get this to work with the calculation to work out SL Pips?

    JPY Multiplier is 100 and all other's 10000 See Formula in Column M

    thanks again
    Last edited by AliGW; 11-24-2019 at 02:47 AM. Reason: Please don't quote unnecessarily!
    Reply With QuoteRegister To Reply
  9. 11-24-2019, 10:41 AM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,856

    Re: Please can someone help with a Forex Type Spreadsheet

    If I understand correctly then the following should do what you want:
    Formula: copy to clipboard 
    =IF($D4="","",IF($D4="Buy",($E4-$L4)*IF(ISNUMBER(SEARCH($C$1,$C4)),100,10000),($L4-$E4)*IF(ISNUMBER(SEARCH($C$1,$C4)),100,10000)))

    I might suggest that instead of 'hard coding' the values 100 and 10,000 you put them in cells and make references to those cells in the formula. That way if the numbers ever change you will not have to change the formula.
    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Condensing 1-minute forex data to 20-minute forex data
    By H28Sailor in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-12-2019, 08:24 PM
  2. Getting Live MT4 forex data
    By mrprofit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2014, 07:18 AM
  3. Need forex toolbar
    By Tareq87 in forum Excel General
    Replies: 2
    Last Post: 05-04-2012, 01:04 PM
  4. Forex
    By Tuzpulid in forum Excel General
    Replies: 1
    Last Post: 01-11-2012, 10:55 AM
  5. Excel Forex system
    By stewart08 in forum Excel General
    Replies: 4
    Last Post: 08-24-2011, 04:11 PM
  6. Live Forex Data - Not possible?
    By heatwave in forum Excel General
    Replies: 0
    Last Post: 07-25-2011, 02:56 AM
  7. Savs All Forex Data
    By YJL in forum Excel General
    Replies: 2
    Last Post: 12-05-2005, 07:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1