I've Solved It!!!!

Discussion in 'Trading Software' started by catmango, Nov 14, 2003.

  1. I was trying to figure out how to set a trailing stop in Excel (I was trying to do something more exotic than what was allowed in IB), unfortunately I kept on hitting up against the problem of circular references. However, I've figured out that all you have to do is go into Tools-->Options, and click on the Calculation tab. Then make sure that the "Iteration" box is checked, and set "Maximum Iterations" to 0 and "Maximum change" to 0.001, and click OK. This will take care of any circular reference error popup windows and the calculation is flawless.

    This may not be a big deal to 99.9% of the readers out there, but I'm feeling quite satisfied right now. :D
  2. Good job.

    Those circular references can be annoying.
  3. qazmax


    The blue monkey sleeps well in the morning...

  4. I get an error telling me that I have to set Maximum Iterations to something between 1 and 32767. It won't take the zero. I am using Office 2002.

    Any ideas?

    I am trying to write a function to do this as well.

  5. Bullet


    I get the same error message, can enyone explain this
  6. Then set it to 1, 10 or whatever. Doesn't have to be zero.

    I love your SNL inspired alias :)
  7. I will give it a shot, although I still don't know if it will work for me as I am trying to calculate a real time trailing stop which will follow the market. It only needs to change if the direction is moving in one direction based on the +/- position. The problem is figuring out how to save the last value I had in the cell if the market is moving in the other direction. (trailing stop price only increases on a sell stop, long position) since the live data is always changing, i lose what I had to work with.

    Thanks fro the help

    Mr. Subliminal, you are the first one to notice the alias, same to you :)
  8. If you have price in cell A1, then to get the historical maximum value in A1 , enter the following in cell B1 :


    Alternatively, to get the minimum value, enter this in B1 :


    Note the circular reference and this is why Iterations has to be enabled. Hopefully this helps.
  9. I actually got it working using the UP/DOWN tick as my criterea to change the cell, or just refer back to itself. I think it works! Looking foreward to link up the quotes and try it out. One of our developers tells me that it will be ok as long as I am not doing a lot of symbols with intense calcualtions as changing the iterations increases the times Excel will calcualte the formula. I am going to just start with 10 or so symbols.