I've Solved It!!!!

C

catmango

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
 
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
 
Quote from Strategery:

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
More...
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!
 
You must log in or register to reply here.
Back
Top