Option Spreadsheet Request

[Doobs789]

Quote from the bouch:

How do you guys manage getting r/t options data into excel? I want to input a list of options on various tickers to quote and analyze various spreads but im having a couple of problems.
Firstly If I want a chain of say ten options on each stock, I'm having trouble getting the data as I have symbol limits. I use esignal which has a 500 symbol limit. So that limits me to 50 tickers. I use IB and that has an even lower symbol limit. I can higher esignal to 1000 but i use it for other stuff as well. so it prob still wouldnt be enough. Where do you guys get your data?

Also, how do you deal with the fact that the ATM is always changing so as a result the list of options to follow and collect data on is always changing.

Curious how others deal with these problems.

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.

 

[gmst]

Quote from Doobs789:

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.

 

[the bouch]

Quote from Doobs789:

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.

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.

 

[Doobs789]

Quote from the bouch:

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).

 

[kapw7]

Quote from the bouch:

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.

Just use ActiveX instead of DDE. IB has an example Excel sheet just as with DDE.

 

[Doobs789]

Quote from kapw7:

Just use ActiveX instead of DDE. IB has an example Excel sheet just as with DDE.

+1

DDE is clunky and obsolete. ActiveX is the way to go. As you stated, IB's API supports this connection.

 

[gmst]

Quote from Doobs789:

+1

DDE is clunky and obsolete. ActiveX is the way to go. As you stated, IB's API supports this connection.

A different viewpoint on DDE vs RTD:

http://www.wilmott.com/messageview.cfm?catid=10&threadid=67656

 

[Doobs789]

Quote from gmst:

A different viewpoint on DDE vs RTD:

http://www.wilmott.com/messageview.cfm?catid=10&threadid=67656

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.

 

[gmst]

Quote from Doobs789:

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?

 

[cdcaveman]

more details....

http://msdn.microsoft.com/en-us/library/office/aa140059(v=office.10).aspx