General Topics
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# I've Solved It!!!!

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

1. ### 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.

2. ### bungrider

Good job.

Those circular references can be annoying.

3. ### qazmax

The blue monkey sleeps well in the morning...

4. ### 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

5. ### Bullet

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

6. ### Mr Subliminal

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

I love your SNL inspired alias

7. ### Strategery

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. ### Mr Subliminal

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.

9. ### Strategery

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!

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: