I have no idea which workbook you are referring to.

However, if you use "OX" as a data source for options information in the smfGetOptionQuotes() function, only calls are available.

--- In smf_addin@yahoogroups.com, Axel Ronnisch wrote:
>
> i am new to this group and tried to work with the fantastic Option Tracker. But i have a Problem and can't fix it. i tried to add my first options for a test. So far the HPQ was not found. Astonishing, today i got data for HPQ. But some is missing.
> I added a written put for HPQ. In the put tab the option price is nott filled. And than the other calculations are not done. And for other puts i tried, it is the same.
> I am working with a german excel version. Maybe it has someting to do with the transformation of fucntiosn from english to german and maybe with the different kind of the date representation US vs German.
>
> How can i fix it?
> Please give advice

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2a

Date formatting

Fri Jan 4, 2013 11:32 am (PST) . Posted by:

"neosephiroth86" neosephiroth86

Hi!
Using your template for RCHGetYahooHistory I tried to recreate a sheet in Excel where I can manipulate financial data.

Ticker: IBM Ticker Date Adj. Open Adj. High Adj. Low Adj. Close Volume
Start Year: 2006 IBM 38891 6906,476005 6968,244877 6887,676783 6902 4520600
Start Month: 6 IBM 38894 6896,153467 6939,120156 6874,670123 6906 3448000
Start Day: 1 IBM 38895 6921,769542 6946,835443 6832,248467 6860 5596800
End Year: 2006 IBM 38896 6851,314263 6857,580982 6809,237722 6854 4392100
End Month: 7 IBM 38897 6883,334708 6964,799588 6853,792499 6946 5448900
End Day: 10 IBM 38898 6955,778443 6976,368263 6874,314371 6877 7911700
Period: d IBM 38901 6941,032556 7006,378877 6916,863368 6984 3177300
Items: TDOHLCVA IBM 38903 6982,589688 7017,502636 6939,619905 6962 4047800
Names: 1 IBM 38904 6945,243949 7029,385325 6943,453707 6990 4333100
Adjust: 1 IBM 38905 6990,495813 6990,495813 6814,144465 6841 6641600
Resort: 1 IBM 38908 6819,138385 6877,32216 6791,3892 6863 7154700

As you can see the problem is that the data is a progressive one, not in this format YYYY-MM-DD or YYYY-DD-MM.

The function I use is this =RCHGetYahooHistory(C2;C3;C4;C5;C6;C7;C8;C9;C10;C11;C12;C13)

(C2...C13) are the same as in your template

Can you help me please?
Thanks

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2b

Re: Date formatting

Fri Jan 4, 2013 12:01 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

EXCEL dates are serial values -- numeric -- basically the number of days
since 1-1-1900. If you want to see them displayed as YYYY-MM-DD or
YYYY-DD-MM, you need to apply such a format to those serial date values.

On Fri, Jan 4, 2013 at 10:38 AM, neosephiroth86 <
snowboard_extr3me@hotmail.it> wrote:

> Using your template for RCHGetYahooHistory I tried to recreate a sheet in
> Excel where I can manipulate financial data.
>
> Ticker: IBM Ticker Date Adj. Open Adj. High
> Adj. Low Adj. Close Volume
> Start Year: 2006 IBM 38891 6906,476005
> 6968,244877 6887,676783 6902 4520600
> Start Month: 6 IBM 38894 6896,153467
> 6939,120156 6874,670123 6906 3448000
> Start Day: 1 IBM 38895 6921,769542
> 6946,835443 6832,248467 6860 5596800
> End Year: 2006 IBM 38896 6851,314263
> 6857,580982 6809,237722 6854 4392100
> End Month: 7 IBM 38897 6883,334708
> 6964,799588 6853,792499 6946 5448900
> End Day: 10 IBM 38898 6955,778443
> 6976,368263 6874,314371 6877 7911700
> Period: d IBM 38901 6941,032556
> 7006,378877 6916,863368 6984 3177300
> Items: TDOHLCVA IBM 38903 6982,589688
> 7017,502636 6939,619905 6962 4047800
> Names: 1 IBM 38904 6945,243949
> 7029,385325 6943,453707 6990 4333100
> Adjust: 1 IBM 38905 6990,495813
> 6990,495813 6814,144465 6841 6641600
> Resort: 1 IBM 38908 6819,138385 6877,32216
> 6791,3892 6863 7154700
>
>
> As you can see the problem is that the data is a progressive one, not in
> this format YYYY-MM-DD or YYYY-DD-MM.
>
> The function I use is this
> =RCHGetYahooHistory(C2;C3;C4;C5;C6;C7;C8;C9;C10;C11;C12;C13)
>
> (C2...C13) are the same as in your template
>
> Can you help me please?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3a

Complete Equity Name

Fri Jan 4, 2013 2:07 pm (PST) . Posted by:

"W. DarrellC" wdcorpening

Anyone know how to extract the whole equity name from Yahoo? The "n" function is rather incomplete for some equities. For example, FSAVX ticker returns "Fidelity Select A" when the equity is "Fidelity Select Automotive" and ITB ticker returns "IShares Dow Jones" when the equity is "IShares Dow Jones US Home Construction". The "n" function essentially saws off the desriptor at 16-20 characters which often doesn't tell the story. The complete desriptor is there on Yahoo, just can't figure how to get to it. Any ideas?

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3b

Re: Complete Equity Name

Fri Jan 4, 2013 2:21 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Try something like:

=smfStrExtr(smfGetTagContent("http://finance.yahoo.com/q?s=ITB","h2",1,"class=""rtq_div"""),"~","
(ITB)")

...but it does mean you end up with one Internet access per ticker symbol.
So it could be slow if you are getting a lot of them. I would recommend
getting them once and then converting the results to values.

If you don't mind the ticker symbol following the name, you could just drop
the smfStrExtr() portion:

=smfGetTagContent("http://finance.yahoo.com/q?s=ITB
","h2",1,"class=""rtq_div""")

On Fri, Jan 4, 2013 at 2:46 PM, W. DarrellC wdcorp2775@comcast.net> wrote:

> Anyone know how to extract the whole equity name from Yahoo? The "n"
> function is rather incomplete for some equities. For example, FSAVX ticker
> returns "Fidelity Select A" when the equity is "Fidelity Select Automotive"
> and ITB ticker returns "IShares Dow Jones" when the equity is "IShares Dow
> Jones US Home Construction". The "n" function essentially saws off the
> desriptor at 16-20 characters which often doesn't tell the story. The
> complete desriptor is there on Yahoo, just can't figure how to get to it.
> Any ideas?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

4a

Expiration Dates around earnings

Fri Jan 4, 2013 6:29 pm (PST) . Posted by:

"Adam Singh" adamsingh1

I am looking to retrieve the options expiration date that includes a given stock's future earnings announcement, and the expiration date following the earnings expiry date(with stocks that have both weekly and monthly options, and those with just monthlies). From here I would like to pull the ATM-strike put and call market prices, thus giving me the straddle price for the earnings expiration date, and the next expiration date. Then I can use a pricing model to back-out the implied volatility for both expiration's straddle, and thus calculate the implied jump from earnings. Ideally I would like to automate this so all I have to enter is a ticker symbol. Thanks for your help.

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4b

Re: Expiration Dates around earnings

Fri Jan 4, 2013 6:47 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

If I understand correctly, a workbook model could be created based on these
steps:

1. Use RCHGetTableCell() or RCHGetElementNumber() to retrieve the date
of the next earnings announcement

2. Use smfGetOptionExpirations() to get the available expiration dates

3. Use MATCH()/INDEX() to find the appropriate expiration date(s)

4. Use smfGetOptionStrikes() to get the first (few?) ITM and OTM strike
prices

5. Use smfGetOptionQuotes() to get the bid/ask prices (and whatever) of
the desired option contracts

Take a look at this message on the Yahoo group to get an idea of how some
of the option functions work:

http://finance.groups.yahoo.com/group/smf_addin/message/17234
On Fri, Jan 4, 2013 at 7:29 PM, Adam Singh singh.adam@gmail.com> wrote:

> I am looking to retrieve the options expiration date that includes a given
> stock's future earnings announcement, and the expiration date following the
> earnings expiry date(with stocks that have both weekly and monthly options,
> and those with just monthlies). From here I would like to pull the
> ATM-strike put and call market prices, thus giving me the straddle price
> for the earnings expiration date, and the next expiration date. Then I can
> use a pricing model to back-out the implied volatility for both
> expiration's straddle, and thus calculate the implied jump from earnings.
> Ideally I would like to automate this so all I have to enter is a ticker
> symbol. Thanks for your help.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4c

Re: Expiration Dates around earnings

Fri Jan 4, 2013 7:08 pm (PST) . Posted by:

"Adam Singh" adamsingh1

Thanks. What would be the inputs into the match or index formula, given that I have an earnings date, and a list of expiration dates?

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> If I understand correctly, a workbook model could be created based on these
> steps:
>
> 1. Use RCHGetTableCell() or RCHGetElementNumber() to retrieve the date
> of the next earnings announcement
>
> 2. Use smfGetOptionExpirations() to get the available expiration dates
>
> 3. Use MATCH()/INDEX() to find the appropriate expiration date(s)
>
> 4. Use smfGetOptionStrikes() to get the first (few?) ITM and OTM strike
> prices
>
> 5. Use smfGetOptionQuotes() to get the bid/ask prices (and whatever) of
> the desired option contracts
>
> Take a look at this message on the Yahoo group to get an idea of how some
> of the option functions work:
>
> http://finance.groups.yahoo.com/group/smf_addin/message/17234
> On Fri, Jan 4, 2013 at 7:29 PM, Adam Singh wrote:
>
> > I am looking to retrieve the options expiration date that includes a given
> > stock's future earnings announcement, and the expiration date following the
> > earnings expiry date(with stocks that have both weekly and monthly options,
> > and those with just monthlies). From here I would like to pull the
> > ATM-strike put and call market prices, thus giving me the straddle price
> > for the earnings expiration date, and the next expiration date. Then I can
> > use a pricing model to back-out the implied volatility for both
> > expiration's straddle, and thus calculate the implied jump from earnings.
> > Ideally I would like to automate this so all I have to enter is a ticker
> > symbol. Thanks for your help.
> >
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4d

Re: Expiration Dates around earnings

Fri Jan 4, 2013 7:31 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

=MATCH(_earningsdate_,_expirationlist_,1)

...would point to the first date in the list that is less that the earnings
date. Be sure your earnings date is an EXCEL serial date value, and not a
text string. So, then:

=INDEX(_expirationlist_,_matchresult_+1)
=INDEX(_expirationlist_,_matchresult_+2)

...would find you the first and second expiration dates after that, and so
forth.

On Fri, Jan 4, 2013 at 8:08 PM, Adam Singh singh.adam@gmail.com> wrote:

> What would be the inputs into the match or index formula, given that I
> have an earnings date, and a list of expiration dates?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4e

Re: Expiration Dates around earnings

Fri Jan 4, 2013 8:24 pm (PST) . Posted by:

"Adam Singh" adamsingh1

I am getting errors on those formulas. Should I define a name for a cell that contains the date(s)? How do I convert a date to a serial date value? What are the purpose of the underscores in your formulas? Thanks for your patience.

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> =MATCH(_earningsdate_,_expirationlist_,1)
>
> ...would point to the first date in the list that is less that the earnings
> date. Be sure your earnings date is an EXCEL serial date value, and not a
> text string. So, then:
>
> =INDEX(_expirationlist_,_matchresult_+1)
> =INDEX(_expirationlist_,_matchresult_+2)
>
> ...would find you the first and second expiration dates after that, and so
> forth.
>
> On Fri, Jan 4, 2013 at 8:08 PM, Adam Singh wrote:
>
> > What would be the inputs into the match or index formula, given that I
> > have an earnings date, and a list of expiration dates?
> >
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4f

Re: Expiration Dates around earnings

Fri Jan 4, 2013 8:51 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Those would be cell and range references, named or otherwise, which would
depend on your workbook. That's what the underscores represent -- just
generic cell/range references.

For example, the MATCH() might look like this:

=MATCH(C4,D4:D17,1)

...and the first INDEX() might look like this:

=INDEX(D4:D17,F4+1)

If you have a typical date string, the easiest way to convert it to a
serial date value would be to use the DATEVALUE() function. For example:

=DATEVALUE(RCHGetTableCell("http://finance.yahoo.com/q?s=MMM",1,"Earnings
Date:"))

On Fri, Jan 4, 2013 at 9:24 PM, Adam Singh singh.adam@gmail.com> wrote:

> I am getting errors on those formulas. Should I define a name for a cell
> that contains the date(s)? How do I convert a date to a serial date value?
> What are the purpose of the underscores in your formulas? Thanks for your
> patience.
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
> >
> > =MATCH(_earningsdate_,_expirationlist_,1)
> >
> > ...would point to the first date in the list that is less that the
> earnings
> > date. Be sure your earnings date is an EXCEL serial date value, and not a
> > text string. So, then:
> >
> > =INDEX(_expirationlist_,_matchresult_+1)
> > =INDEX(_expirationlist_,_matchresult_+2)
> >
> > ...would find you the first and second expiration dates after that, and
> so
> > forth.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4g

Re: Expiration Dates around earnings

Fri Jan 4, 2013 10:10 pm (PST) . Posted by:

"Adam Singh" adamsingh1

That works great. Thanks!!

One other issue I ran into. smfGetOptionQuotes is pulling prices for the expiration after the one I want (yahoo data). For example, when inputting the string AAPL 2/1 2013 $525.00 Call it is giving me bid-ask prices for the 2/16/2013 525 call, even though the expiration date and the string syntax are correct. This was not an issue with the 1/25/2013 expiration. Could this possibly be caused by the fact the CBOE only recently started releasing weekly option series 30 days out (as opposed to the Thursday before the next trading week)? Or is it merely an issue with using Yahoo as a data source?

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> Those would be cell and range references, named or otherwise, which would
> depend on your workbook. That's what the underscores represent -- just
> generic cell/range references.
>
> For example, the MATCH() might look like this:
>
> =MATCH(C4,D4:D17,1)
>
> ...and the first INDEX() might look like this:
>
> =INDEX(D4:D17,F4+1)
>
> If you have a typical date string, the easiest way to convert it to a
> serial date value would be to use the DATEVALUE() function. For example:
>
> =DATEVALUE(RCHGetTableCell("http://finance.yahoo.com/q?s=MMM",1,"Earnings
> Date:"))
>
> On Fri, Jan 4, 2013 at 9:24 PM, Adam Singh wrote:
>
> > I am getting errors on those formulas. Should I define a name for a cell
> > that contains the date(s)? How do I convert a date to a serial date value?
> > What are the purpose of the underscores in your formulas? Thanks for your
> > patience.
> >
> > --- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
> > >
> > > =MATCH(_earningsdate_,_expirationlist_,1)
> > >
> > > ...would point to the first date in the list that is less that the
> > earnings
> > > date. Be sure your earnings date is an EXCEL serial date value, and not a
> > > text string. So, then:
> > >
> > > =INDEX(_expirationlist_,_matchresult_+1)
> > > =INDEX(_expirationlist_,_matchresult_+2)
> > >
> > > ...would find you the first and second expiration dates after that, and
> > so
> > > forth.
> >
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4h

Re: Expiration Dates around earnings

Fri Jan 4, 2013 10:29 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

You've nailed it. I haven't looked at the function since they added the
additional weeks.

When I first wrote my expiration date function, they didn't even have the
weekly series, so I made two assumptions *that no longer apply*:

-- A day value of 1 assumes a monthly option needs to be returned
-- A day value of 30 or 31 assumes a quarterly option needs to be returned

I suppose the easiest thing to do is just take out those two cases and use
whatever date is actually passed.

Since there was so much duplication of code between the various sources, I
had started writing a new function that would be a general purpose
one...but I'm still not quite sure how to handle it.

On Fri, Jan 4, 2013 at 11:10 PM, Adam Singh singh.adam@gmail.com> wrote:

>
> One other issue I ran into. smfGetOptionQuotes is pulling prices for the
> expiration after the one I want (yahoo data). For example, when inputting
> the string AAPL 2/1 2013 $525.00 Call it is giving me bid-ask prices for
> the 2/16/2013 525 call, even though the expiration date and the string
> syntax are correct. This was not an issue with the 1/25/2013 expiration.
> Could this possibly be caused by the fact the CBOE only recently started
> releasing weekly option series 30 days out (as opposed to the Thursday
> before the next trading week)? Or is it merely an issue with using Yahoo
> as a data source?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

4i

Re: Expiration Dates around earnings

Fri Jan 4, 2013 11:34 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I've uploaded a new version of the add-in (2013.01.04) to the "Works In
Progress" folder that removes the day 1 assumption of a monthly option and
the day 30 or 31 assumption of a quarterly option.

For the most part, I use OptionsXPress as my data source for options
information. It's slower than the others, but the returned data is more
reliable -- because their customers are actually trading the options,
whereas Google and Yahoo are just reporting on the options.

On Fri, Jan 4, 2013 at 11:10 PM, Adam Singh singh.adam@gmail.com> wrote:

>
> One other issue I ran into. smfGetOptionQuotes is pulling prices for the
> expiration after the one I want (yahoo data). For example, when inputting
> the string AAPL 2/1 2013 $525.00 Call it is giving me bid-ask prices for
> the 2/16/2013 525 call, even though the expiration date and the string
> syntax are correct. This was not an issue with the 1/25/2013 expiration.
> Could this possibly be caused by the fact the CBOE only recently started
> releasing weekly option series 30 days out (as opposed to the Thursday
> before the next trading week)? Or is it merely an issue with using Yahoo
> as a data source?
>