spreadsheet newbie

Discussion in 'Trading Software' started by alanack, Nov 8, 2002.

  1. Jeffrey


    I am working on a trade tracking spreadsheet. The problem I need guidance with is the formula in the Capital Gain/Loss column.

    Seven columns are used for this scenario:

    1. A for the position short/long
    2. B for the entry price
    3. C for the exit price
    4. D for the points =B1-C1
    5. E for the capital earned =D*F*G (for NQ's)
    6. F for Contracts
    7. G for contract value $20/contract NQ's

    This works for shorting(=A1-B1) in calculating the points earned.
    But I need the formula for "C" column to read =B1-A1 for Longs.

    Is there anyone who can provide me with a formula for column "D" that says:
    "If column A is short than(B1-C1) If column A is Long than (C1-B1)"

    Thanks in advance.

    #11     Nov 12, 2002
  2. Jeffrey



    #12     Nov 12, 2002
  3. ddefina


    @if(A1="S",B1-C1,C1-B1) The only problem with this is it defaults to long if it doesn't say "S", but it should work for what your doing.
    #13     Nov 12, 2002
  4. Jeffrey


    Thanks, I'll give it a try here.

    I had to go back and edit my post. I got confused between my stock and futures spread sheets.

    Again, Thanks.
    #14     Nov 12, 2002
  5. opm8



    Very quick answers to the most complex spreadsheet questions you can muster.

    #15     Nov 12, 2002
  6. Jeffrey


    The only way it defaults is if you don't fill in the position column short/long.

    These formula's work:



    1. Where A column states the position SHORT or LONG
    2. Where B column states the number of contracts
    3. Where C column states the contract value
    4. Where D column states the points calculated (entry minus exit)
    5. Enter the above formula in column E to calculate Capital Gain



    1. Where A column states the position SHORT or LONG
    2. Where B column states the entry price, or total cost.
    3. Where C column states the exit price, or total revenue.
    4. Enter the above formula in column D to calculate Capital Gain

    #16     Nov 12, 2002
  7. Jeffrey



    The default is set with the last entry.(Long or Short)
    #17     Nov 12, 2002
  8. Jeffrey



    Correction: The points column needs the formula to change the number to - if long.

    I'll have hold off before posting. Sorry, for any havoc.

    #18     Nov 13, 2002
  9. Jeffrey


    Replace this qoute for futures with my previous post.
    The minus 1 added to point calculation is the cost of spread on NQ's. For a ES spreadsheet minus .5 for the spread.

    #19     Nov 14, 2002
  10. Jeffrey


    Replace with


    Hopefully those who need a worksheet are smart enough to figure it out. I just wanted to correct what I started.
    I'm leaving anything else alone.

    #20     Nov 14, 2002