I've Solved It!!!!

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.

 

Good job.

Those circular references can be annoying.

 

The blue monkey sleeps well in the morning...

 

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.

Thanks,
Mike

 

Strategery
I get the same error message, can enyone explain this

 

Then set it to 1, 10 or whatever. Doesn't have to be zero.

I love your SNL inspired alias

 

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

 

If you have price in cell A1, then to get the historical maximum value in A1 , enter the following in cell B1 :

=MAX(B1,A1)

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

=IF(B1=0,A1,MIN(A1,B1))

Note the circular reference and this is why Iterations has to be enabled. Hopefully this helps.

 

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.

Thanks!