Messages
Mon Sep 26, 2016 2:24 am (PDT) . Posted by:
I don't know if this is what you are looking for, but the occ publishes new
series daily. You can download the last ten days worth of data. You
should be able to keep it up to date using the downloads on this page:
http://www.theocc.com/webapps/series-download
Randy
On Mon, Sep 26, 2016 at 12:14 AM, nicolasanchoa@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> [Attachment(s) <#m_-2860258970677039263_TopText> from
> nicolasanchoa@yahoo.com [smf_addin] included below]
>
> Hello people
>
>
> Excellent tool we have here..
>
>
>
> So here´s our problem: we have approx 450 stocks (basically those with
> weeklies) and need to keep their strikes list updated in our datamodel, in
> a table similar to the one on the attached spreadsheet.
>
>
> I´ve tried just to get the ATM strike from Yahoo for a few of those
> symbols simultaneously and is very slow. So that discards the extracting in
> real-time. Having it on the model on the other hand would be perfect.
> Maybe updating it once per week.
>
>
> Could this be done with smf_addin? Any idea on how to get this done
> efficiently?
>
>
>
>
>
Mon Sep 26, 2016 7:06 am (PDT) . Posted by:
I just did something similar:
http://wrongwaycoveredcalls.blogspot.com/2016/09/dividend-champions-and-options.html
Each request took about a second, so 450 ticker symbols would take about 6
to 8 minutes to update. My formula was something like this, array-entered
over a 1-row by 5-column range(the reverse of the 1 and the 5 in the
formula, so it can be transposed):
=TRANSPOSE(smfGetOptionExpirations("MMM","Y",,,5,1))
However, the question is whether Yahoo data would be reliable enough for
you? I bought a call last week that wasn't listed anywhere on Yahoo until I
checked this morning. If you did go this route, I would suggest using VBA
to place values into your table, instead of using formulas in a spreadsheet
that would recalculate any time you open the workbook. Or, you could use
the add-in's smfUpdateDownloadTable macro to do everything -- see attached.
Would the XLS file available from the CBOE be of any use?
http://www.cboe.com/micro/weeklys/availableweeklys.aspx
On Sun, Sep 25, 2016 at 9:14 PM, nicolasanchoa@y
...wrote:
> So here´s our problem: we have approx 450 stocks (basically those with
> weeklies) and need to keep their strikes list updated in our datamodel, in
> a table similar to the one on the attached spreadsheet.
>
> I´ve tried just to get the ATM strike from Yahoo for a few of those
> symbols simultaneously and is very slow. So that discards the extracting in
> real-time. Having it on the model on the other hand would be perfect.
> Maybe updating it once per week.
>
> Could this be done with smf_addin? Any idea on how to get this done
> efficiently?
>
>
Attachment(s) from Randy Harmelink
1 of 1 File(s)
Mon Sep 26, 2016 11:02 am (PDT) . Posted by:
Thanks both very much for the replies.
I was thinking that maybe the fastest and most effective to get them would be with the strike multiplier for each symbol, as they rarely change.
There are some functions in Excel that round to a multiplier: FLOOR and CEILING, testing them right now.
I´d get an error message if they don´t exist, so I could revise the multipliers individually.
I´ll test it n see how it goes.
Mon Sep 26, 2016 11:27 am (PDT) . Posted by:
Focusing on the weeklies, from what I see:
last < 100, multiplier is .5
last > 100, multiplier is 1
last > 150, multiplier is 2.5
Looks pretty simple.
Mon Sep 26, 2016 11:39 am (PDT) . Posted by:
Not based on "last" price. But, yes, GENERALLY strike prices CAN vary by
$0.50 under 100 and $1 over. That doesn't mean each and every interval is
covered, nor that they will be available on every expiration date, plus
they may vary between calls and puts.
Note the two AAPL example I sent on a previous message. The Dec 2016
expiration date has $5 intervals in the upper range.
On Mon, Sep 26, 2016 at 11:27 AM, nicolasanchoa@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Focusing on the weeklies, from what I see:
>
> last < 100, multiplier is .5
> last > 100, multiplier is 1
> last > 150, multiplier is 2.5
>
> Looks pretty simple.
>
>
Mon Sep 26, 2016 11:45 am (PDT) . Posted by:
As far as simple...
You say you need ALL strike prices? When I look at my brokerage trading
platform, I see there is a $2.50 strike price for AAPL in Dec 2016. To get
that, you'd need to iterate down by $0.50 to cover that, which would mean a
lot of strike prices not being used. They go up by $2.50 from there.
On Mon, Sep 26, 2016 at 11:27 AM, nicolasanchoa@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Focusing on the weeklies, from what I see:
>
> last < 100, multiplier is .5
> last > 100, multiplier is 1
> last > 150, multiplier is 2.5
>
> Looks pretty simple.
>
>
Mon Sep 26, 2016 11:58 am (PDT) . Posted by:
There also are things like special dividends, mergers,spinoffs etc that
result in non-standard series.
Randy Randall
On Mon, Sep 26, 2016 at 2:45 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> As far as simple...
>
> You say you need ALL strike prices? When I look at my brokerage trading
> platform, I see there is a $2.50 strike price for AAPL in Dec 2016. To get
> that, you'd need to iterate down by $0.50 to cover that, which would mean a
> lot of strike prices not being used. They go up by $2.50 from there.
>
> On Mon, Sep 26, 2016 at 11:27 AM, nicolasanchoa@yahoo.com [smf_addin] <
> smf_addin@yahoogroups.com> wrote:
>
>>
>> Focusing on the weeklies, from what I see:
>>
>> last < 100, multiplier is .5
>> last > 100, multiplier is 1
>> last > 150, multiplier is 2.5
>>
>> Looks pretty simple.
>>
>>
>
>
Mon Sep 26, 2016 12:21 pm (PDT) . Posted by:
Ack, do I feel foolish.
For some reason, I got "expiration dates" in my head instead of "strike
prices".
I think the smfGetOptionStrikes() function can get you what you need.
However, do you really want ALL strike prices? That can be quite a list,
and vary by expiration date. For example:
=smfGetOptionStrikes("AAPL",DATE(2016,9,30),"C","Y")
Returns:
$95.00
$96.50
$97.00
$97.50
$98.00
$98.50
$99.00
$100.00
$101.00
$102.00
$103.00
$104.00
$105.00
$106.00
$107.00
$108.00
$109.00
$110.00
$111.00
$112.00
$113.00
$114.00
$115.00
$116.00
$117.00
$118.00
$119.00
$120.00
$121.00
$122.00
$125.00
....while this:
=smfGetOptionStrikes("AAPL",DATE(2016,12,16),"C","Y")
...returns this:
$70.00
$75.00
$80.00
$85.00
$90.00
$95.00
$97.50
$100.00
$105.00
$110.00
$115.00
$120.00
$125.00
$130.00
$135.00
$140.00
$145.00
$150.00
$155.00
$160.00
$165.00
$170.00
$175.00
But, again, these are what Yahoo is listing. Note the function is
symmetrical -- if you ask it for 10 strike prices, it will return the first
five that are ITM and the first five that are OTM (or blank fill if there
aren't enough). But that means you always know the 5th is the first ITM and
the 6th is the first OTM.
On Mon, Sep 26, 2016 at 11:02 AM, nicolasanchoa@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I was thinking that maybe the fastest and most effective to get them would
> be with the strike multiplier for each symbol, as they rarely change.
>
> There are some functions in Excel that round to a multiplier: FLOOR and
> CEILING, testing them right now.
>
> I´d get an error message if they don´t exist, so I could revise the
> multipliers individually.
>
> I´ll test it n see how it goes.
>
Mon Sep 26, 2016 1:14 pm (PDT) . Posted by:
Here's a side-by-side comparison of strike prices for two expiration dates,
puts versus calls, and Yahoo versus OptionsXPress. Since OptionsXPress is a
trading platform, I'm sure there's would be accurate. However, their web
pages typically take 2 to 3 times longer than Yahoo.
AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL
Y Y OX OX Y Y OX OX
C P C P C P C P
2016-12-16 2016-12-16 2016-12-16 2016-12-16 2016-09-30 2016-09-30
2016-09-30 2016-09-30
$2.50 $2.50
$5.00 $5.00
$7.50 $7.50
$10.00 $10.00
$12.50 $12.50
$15.00 $15.00
$17.50 $17.50 $90.00 $90.00 $90.00
$20.00 $20.00 $95.00 $95.00 $95.00
$22.50 $22.50 $95.00 $96.50 $96.50 $96.50
$25.00 $25.00 $96.50 $97.00 $97.00 $97.00
$30.00 $30.00 $97.00 $97.50 $97.50 $97.50
$35.00 $35.00 $97.50 $98.00 $98.00 $98.00
$40.00 $40.00 $98.00 $98.50 $98.50 $98.50
$45.00 $45.00 $98.50 $99.00 $99.00 $99.00
$50.00 $50.00 $99.00 $99.50 $99.50 $99.50
$55.00 $55.00 $55.00 $100.00 $100.00 $100.00 $100.00
$60.00 $60.00 $60.00 $101.00 $101.00 $101.00 $101.00
$65.00 $65.00 $65.00 $102.00 $102.00 $102.00 $102.00
$70.00 $70.00 $70.00 $70.00 $103.00 $103.00 $103.00 $103.00
$75.00 $75.00 $75.00 $75.00 $104.00 $104.00 $104.00 $104.00
$80.00 $80.00 $80.00 $80.00 $105.00 $105.00 $105.00 $105.00
$85.00 $85.00 $85.00 $85.00 $106.00 $106.00 $106.00 $106.00
$90.00 $90.00 $90.00 $90.00 $107.00 $107.00 $107.00 $107.00
$95.00 $95.00 $95.00 $95.00 $108.00 $108.00 $108.00 $108.00
$97.50 $97.50 $97.50 $97.50 $109.00 $109.00 $109.00 $109.00
$100.00 $100.00 $100.00 $100.00 $110.00 $110.00 $110.00 $110.00
$105.00 $105.00 $105.00 $105.00 $111.00 $111.00 $111.00 $111.00
$110.00 $110.00 $110.00 $110.00 $112.00 $112.00 $112.00 $112.00
$115.00 $115.00 $115.00 $115.00 $113.00 $113.00 $113.00 $113.00
$120.00 $120.00 $120.00 $120.00 $114.00 $114.00 $114.00 $114.00
$125.00 $125.00 $125.00 $125.00 $115.00 $115.00 $115.00 $115.00
$130.00 $130.00 $130.00 $130.00 $116.00 $116.00 $116.00 $116.00
$135.00 $135.00 $135.00 $135.00 $117.00 $117.00 $117.00 $117.00
$140.00 $140.00 $140.00 $140.00 $118.00 $118.00 $118.00 $118.00
$145.00 $145.00 $145.00 $145.00 $119.00 $119.00 $119.00 $119.00
$150.00 $150.00 $150.00 $150.00 $120.00 $120.00 $120.00 $120.00
$155.00 $215.00 $155.00 $155.00 $121.00 $122.00 $121.00 $121.00
$160.00 $160.00 $160.00 $122.00 $125.00 $122.00 $122.00
$165.00 $165.00 $165.00 $125.00 $125.00 $125.00
$170.00 $170.00 $170.00
$175.00 $175.00 $175.00
$180.00 $180.00
$185.00 $185.00
$190.00 $190.00
$195.00 $195.00
$200.00 $200.00
$205.00 $205.00
$210.00 $210.00
$215.00 $215.00
On Mon, Sep 26, 2016 at 11:02 AM, nicolasanchoa@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> Thanks both very much for the replies.
>
> I was thinking that maybe the fastest and most effective to get them would
> be with the strike multiplier for each symbol, as they rarely change.
>
> There are some functions in Excel that round to a multiplier: FLOOR and
> CEILING, testing them right now.
>
> I´d get an error message if they don´t exist, so I could revise the
> multipliers individually.
>
> I´ll test it n see how it goes.
>
Mon Sep 26, 2016 2:14 pm (PDT) . Posted by:
Cool, I just needed the weeklies (for day trading).
See the pic, I build some formulas there to get the strikes based on the moneyness.
The only error I found was with the VIX, so it seems to work very good for all symbols.
The VIX has unique increments in different ranges.
Attachment(s) from
1 of 1 Photo(s)
