Instead of trying to quote spreads on hundreds of tickers, just input a single ticker at a time, and having resting formulas that will add/subtract prices to form various spreads. I do not know the syntax that IB uses to quote options (symbols) for DDE/ActiveX. But once you know the structure perhaps use the concatenate function to build an option symbol. For example, place the underlying symbol, expiration, contract type in separate cells. Then in another cell, concatenate these cells to build the symbol. That way whenever you change the underlying ticker or expiration date, your option quotes will change with it. Since you do not know the atm strike price, pull the last price of the underlying stock into a cell, use the round function, and add this value to the option symbol formula. I have had success with this method before. Then you will only have to input a ticker, and all the option quotes/spread prices will populate.
This kind of thing is done precisely in this way. However, if you trade only options on 20-30 underlyings at most and want to monitor them and use color coding to flash interesting things, then you might want to display all these 20-30 symbols with their options chain together.
Ya thats pretty much exactly what I have now. Just kind of clunky. Having to refresh each option when I change symbol or when underlying moves is kind of annoying. IB's DDE is not very reliable either. Ive had many problems over the years. Also I was hoping to do some kind of screening thing. I guess Ill stick to this way for now though.
That's the problem with DDE. You can't have cells change dynamically without refreshing the links. When I was with TOS I had a some VBA code which I put into two buttons that activated/deactivated the dde links for modification. I currently use the LiveVol Excel product, which uses RTD links. It is much easier to automate things. Plus, their syntax for atm options uses a "0" input for atm, "1" for one strike up otm, and so on. The problem is you need to be a livevol subscriber ($250/mo), and pay for the excel add-in ($100/mo and up).
+1 DDE is clunky and obsolete. ActiveX is the way to go. As you stated, IB's API supports this connection.
Perhaps DDE links are faster. If one absolutely needs their data to update that much faster, then use DDE. In my case, and probably most others, the slightly quicker refresh rate is most likely inconsequential. As I am using the data to analyze potential trades. I prefer the ability to reference dynamic cells over speed.
I wanted to say that I had never heard about RTD before. Only after reading this thread I googled and landed on that wilmott thread. I will like a deeper discussion on merits of RTD vs DDE. I will try to set up sheets using both technologies, compare and then post my findings over here. What exactly do you mean when you say - prefer the ability to reference dynamic cells?