Calculating results in Excel

Discussion in 'Trading Software' started by hans123, Feb 25, 2006.

  1. hans123

    hans123

    I want to calculate some results in Excel. I have a column with daily changes of closing prices and a column with a signal value (Long, Short or Neutral). If the signal still is the same then I want to add (in case of Long) the daily change to the previous value or subtract (in case of a Short) the daily change to the previous value.
    My data looks like:

    A B C
    -23 Short +23
    -45 Short +68
    +5 Long +73
    +7 Long +80
    +132 Long +212
    +34 Long +256
    -4 Long +252
    +54 Long +306
    -34 Neutral +306

    Does someone know how I can calculate column C in Excel?

    Thanks.

    Hans
     
  2. vak001

    vak001

    I want to calculate some results in Excel. I have a column with daily changes of closing prices and a column with a signal value (Long, Short or Neutral). If the signal still is the same then I want to add (in case of Long) the daily change to the previous value or subtract (in case of a Short) the daily change to the previous value.
    My data looks like:

    A B C
    -23 Short +23
    -45 Short +68
    +5 Long +73
    +7 Long +80
    +132 Long +212
    +34 Long +256
    -4 Long +252
    +54 Long +306
    -34 Neutral +306


    d2=if(b1=b2,abs(a1)+d1,d1)

    note u might have to change "," to ";" depending on ur language settings
     
  3. hopback

    hopback

    you can also use conditional formatting statements

    it sounds like you're doing something similar to a basic black jack +/- counting system but your C column seems to be adding even when the B column says short.
    maybe I'm missing something.

    also, C6 should be 246 not 256 if you're adding A6(+34) to C5(+212).
     
  4. hans123

    hans123

    You're right, it should be 246. Sorry for that mistake.

    Basicly I had one short serie and one long serie. I want to add the total of the short serie to the total of the long serie. If the daily result is negative and I was short the result of the trade should be positive.

    Hans
     
  5. hans123

    hans123

    I don't want to make an addition when there is a neutral signal. I think this code don't make that comparison.

    Hans
     
  6. hans123

    hans123

    Another option might be:

    A B C
    -23 Short
    -45 Short +68
    +5 Long
    +7 Long
    +132 Long
    +34 Long
    -4 Long
    +54 Long +228
    -34 Neutral 0

    Hans
     
  7. Instead of using Long, Short, Neutral you could use 1, -1, 0, thus:

    c2 = (a2 * b2) + c1

    You can create constants in Excel (e.g., Long = 1, etc.), so you still use the names in Column B:

    1. On the Insert menu, point to Name, and then click Define.

    2. In the Names in workbook box, enter the name for the formula.

    3. In the Refers to box, type = (equal sign), followed by the formula or the constant value.
     
  8. hopback

    hopback

    I guess I'm not following you.
    Are you saying it's not cumulative?

    "If the daily result is negative and I was short the result of the trade should be positive."

    So, if A is negative and B is "SHORT" then C becomes positive?
    What would make C negative?

    here's a formula for a cumulative approach, the plus/minus idea I mentioned. Although it sounds like it's not what you're looking for.

    I left your column C as is and used column D.

    Based off of your C1, I started with:
    D1 =ABS(A1)
    D2 =IF(B2="long",(D1+ABS(A2)),IF(B2="short",(D1-ABS(A2)),D1))

    then drag the formula down the column.
     
  9. hopback

    hopback

    is the daily result column A or C. I thought A was the daily change.

    Are you saying you want to track the days you were right on market direction? long on up days and short on down days?

    If this is the case, why is C equal to the entire daily change? Are you in your position from open to close?
    If not, are you trying to track possible gains on the days you are right?

    I may be entirely off base. Just trying to fugure out exactly what you're trying to do.
     
  10. hans123

    hans123

    I post my original file. I want to calculate my results based on my position. My position is short (-1). neutral (0) or long (1). When I am short and the result is negative, my trading result should be positive. When I am long and the result is negative I want to subtract it from the total I have at the moment.

    Hans
     
    • data.xls
      File size:
      62.5 KB
      Views:
      151
    #10     Feb 26, 2006