Question for experienced Excel users...

Discussion in 'Automated Trading' started by CowboyBlue, Aug 13, 2009.

  1. Ok i have a tracking sheet on excel designed to track my positions/pnl, i am having troubles with a particular formula.

    I have it set up on an "IF" statement where if the pnl of a stock is less than -400 it locks in at -400 and wont go any lower, based on me setting up a stopout for -400 on any given position. My problem i am having is i cant lock it at -400 if it goes to -300 then it ends up -300, i just want it to lock the second it hits -400, is there a way to do this?
  2. bump
  3. You need to describe your problem better or post a spreadsheet showing what you've already done.
  4. To be specific, that just doesn't make any sense, the way you are describing it.
  5. If you're updating cells with live information, I think you're going to have to do what you wish with VBA/macro. I can't think of a way to do it with just formulas.

    If you use this:
    Private Sub Worksheet_Calculate()

    your VBA code will run every time the worksheet calculates. Make it check the value of the cells you have PL in and if any are -400 it will have to replace the IF with the value. This isn't a great solution but the only one I can think of.
  6. Assume your data is in cell A, and
    tabulated in B.

    just use formula for cell B:
    =if(cell(A) <-400,-400,cell(A))

    if you want the original cell balance to carry down,

    Unless I"m missing something, this is a trivial problem and does not require VBA.

    If it doesn't do what you want. Post a small example of some data and what you would like for it to do.
  7. After reviewing the problem in excel this is the best answer I could come up with also.

    You need a VBA script that can either:

    a) convert the formula to a value when it hits the -400 mark, or
    b) have another portion of the spreadsheet reference the cell, and have a VBA script convert that formula to a value when the origial (source cell) hits the -400 mark.

  8. ok here is the formula i have


    so basically it means that this cell should equal j12, unless j12 is less than or equal to -400 in which case it equal -420 (accounting for slippage), if it is not less than or equal to -400 it remains J12.

    What i am trying to do is if it goes over -400 i want it to lock there instead of coming back to -300 if it dips back below -400. Given the equation i gave you could you give me the euqation i would need to lock it if it goes over -400, this is supposed to respresent a stopout, so if it hits -400 it is supposed to lock in that price.

    So if it is -400 it will equal -420 but if not it equals J12 the pnl column, the problem is if the stock comes back from there it will continue to go higher just never lower thasn -400, i need it to lock once it hits that number.
  9. Again, it would be much simper to
    simply post a small table or column
    of actual values that you want processed. Your descriptions are a bit ambiguous. It would help to show where J12 itself comes from. If I understand you correctly,
    you could do something in j12 like:


    this will lock and hold -420 once it is
    triggered, no matter what other value
    comes into play.

  10. ok now you se where J12 is, you see how it is -504? so
    k12 = negative 420 meaning i was stopped out, now the problem is if J12 goes higher than -400, K12 will revert back to being J12, i want K 12 to lock once J12 goes over -400 to represent a stopout.

    the equation i have for K12 is =IF(J12<=-400,-420,J12)

    the problem is if J12 goes back under negative 400 then k12 will also go back under negative 400 and i need k12 to lock once J12 goes -400 or more.....

    #10     Aug 13, 2009