Download Option chains with High Low

[nancy7505]

I can automate Excel to download multiple option chains (the stock I am watching) from Yahoo but they do not supply the High and Low prices for the day. Does anyone know of a service that provides the High and Low prices in their Option chains?

 

[ast4]

I can automate Excel to download multiple option chains (the stock I am watching) from Yahoo but they do not supply the High and Low prices for the day. Does anyone know of a service that provides the High and Low prices in their Option chains?

There are a few free-ish options that come to mind:

1) Best "free" source I can think of is scraping yahoo intraday, which can either be done directly off the website or using YQL. Then just convert your data to OHLC bars at your desired frequency.

2) If you know the high and low for the underlying, you could just ballpark it using black-scholes.

 

[nancy7505]

There are a few free-ish options that come to mind:

1) Best "free" source I can think of is scraping yahoo intraday, which can either be done directly off the website or using YQL. Then just convert your data to OHLC bars at your desired frequency.

2) If you know the high and low for the underlying, you could just ballpark it using black-scholes.

I am trying to using Excel web query but that will be a lot of work.
What is YQL? I think I am missing something.

 

[ast4]

I am trying to using Excel web query but that will be a lot of work.
What is YQL? I think I am missing something.

YQL Console

There's an example, just query throughout the day and store the data - then convert to OHLC bars of you flavor.

If that's too much work then just write a black-scholes UDF in excel, back out the implied forward so you get correct IVs. Then take the high and low of the underlying for the day and estimate what the high and low for the options would have been.

 

[nancy7505]

ast4,

Oh my! A whole new world has opened to me with the YQL.
Thank you!

 

[ast4]

ast4,

Oh my! A whole new world has opened to me with the YQL.
Thank you!

Nancy,

Though you'll have to keep in mind that you'll need something a little more robust than excel to really take advantage of this. Personally, I've used a python script w/ open tables that continually appends to a csv file. You can then refactor the data pretty easily using the pandas module in python at EOD.

They talk about it a bit here: python - YQL Options expiration - Stack Overflow

-a

 

[ast4]

Oh another thought that just came to mind, the original YQL table I sent you requires that you enter expiration. While it's always 3rd friday, sometimes this can be a pain to generate. Luckily there's another table within YQL that allows you to retrieve all current available contracts.

YQL Console