9 Messages
Digest #4261
Messages
Sun Dec 10, 2017 5:31 am (PST) . Posted by:
jbr863
Hey Randy,
Some weeks ago, smfGetOptionExpirations() sourcing "OX" started erroring out in my spreadsheet. I figured I'd look into it later. Just yesterday though, I downloaded smfGetOptionQuotes-Example-ITM-OTM-Multiple-Expirations.xls for a control point and I'm getting the same "Invalid Data Source" error message for "MMM" on "OX"and "OX2". Sourcing "G" returns a valid source but says "Bad Expiration Date:" for "MMM". Yahoo works for for expiration dates.
Are you seeing the same issue? Any idea what may be going on with the non-Yahoo sources? http://ogres-crypt.com/SMF/Templates/smfGetOptionQuotes-Example-ITM-OTM-Multiple-Expirations.xls
Some weeks ago, smfGetOptionExpirations() sourcing "OX" started erroring out in my spreadsheet. I figured I'd look into it later. Just yesterday though, I downloaded smfGetOptionQuotes-Example-ITM-OTM-Multiple-Expirations.xls for a control point and I'm getting the same "Invalid Data Source" error message for "MMM" on "OX"and "OX2". Sourcing "G" returns a valid source but says "Bad Expiration Date:" for "MMM". Yahoo works for for expiration dates.
Are you seeing the same issue? Any idea what may be going on with the non-Yahoo sources? http://ogres-crypt.com/SMF/Templates/smfGetOptionQuotes-Example-ITM-OTM-Multiple-Expirations.xls
Sun Dec 10, 2017 8:33 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Yahoo is the only realistic source right now for smfGetOptionExpirations().
OptionsXPress has been gone for a few months now. See:
https://smf-add-in.blogspot.com/2017/10/optionsxpress-no-longer-usable-data.html
Google has been unusable for years, because of so many missing contracts,
and now the new Google Finance doesn't even have option quotes.
On Sun, Dec 10, 2017 at 6:31 AM, jbr863@
...
wrote:
>
> Some weeks ago, smfGetOptionExpirations() sourcing "OX" started
> erroring out in my spreadsheet. I figured I'd look into it later. Just
> yesterday though, I downloaded smfGetOptionQuotes-Example-
> ITM-OTM-Multiple-Expirations.xls for a control point and I'm getting the
> same "Invalid Data Source" error message for "MMM" on "OX"and "OX2".
> Sourcing "G" returns a valid source but says "Bad Expiration Date:" for
> "MMM". Yahoo works for for expiration dates.
>
> Are you seeing the same issue? Any idea what may be going on with the
> non-Yahoo sources?
> <http://ogres-crypt.com/SMF/Templates/smfGetOptionQuotes-Example-ITM-OTM-Multiple-Expirations.xls>
>
OptionsXPress has been gone for a few months now. See:
https://smf-add-in.blogspot.com/2017/10/optionsxpress-no-longer-usable-data.html
Google has been unusable for years, because of so many missing contracts,
and now the new Google Finance doesn't even have option quotes.
On Sun, Dec 10, 2017 at 6:31 AM, jbr863@
...
wrote:
>
> Some weeks ago, smfGetOptionExpirations() sourcing "OX" started
> erroring out in my spreadsheet. I figured I'd look into it later. Just
> yesterday though, I downloaded smfGetOptionQuotes-Example-
> ITM-OTM-Multiple-Expirations.xls for a control point and I'm getting the
> same "Invalid Data Source" error message for "MMM" on "OX"and "OX2".
> Sourcing "G" returns a valid source but says "Bad Expiration Date:" for
> "MMM". Yahoo works for for expiration dates.
>
> Are you seeing the same issue? Any idea what may be going on with the
> non-Yahoo sources?
> <http://ogres-crypt.com/SMF/Templates/smfGetOptionQuotes-Example-ITM-OTM-Multiple-Expirations.xls>
>
Sun Dec 10, 2017 6:36 am (PST) . Posted by:
e_bartsch
Randy;
I know you've been swamped with all the changes over the past few weeks.
I have a spreadsheet that quotes options with both traditional and weekly expiration dates. I'm still getting quotes for options that are one strike out of the money. However, anything beyond that returns an "Error". The weekly fields now return blanks.
For example,
=smfGetOptionQuotes(F7,"bs",1,"Y"), where "F7" is "PG 12/15 2017 $89.50 Put" correctly returns the bid and Strike Price.
=smfGetOptionQuotes(F8,"as",1,"Y"), where "F8" is "PG 12/15 2017 $89.00 Put" returns "Error" for the ask price and "89.0" for the strike price.
To pull weeklies, I'm using this formula string:
=INDEX(smfGetOptionStrikes(Q4,P9,"P","B",1,4,1),2,1) (where Q4 is "PG" and P9 is "22-Dec-17") to generate the option description at R7 and then using
=smfGetOptionQuotes(R7,"bs",1,"B") to get the bid and strike prices.
The INDEX command, above, has been generating a blank for the option description. So, no quote is forthcoming.
So, I'm getting "Error" for OTM2 and more distant option quotes, and smfGetOptionStrikes is not generating a table of strikes, as it once did.
Any tips?
Thanks for the help.
Eric
I know you've been swamped with all the changes over the past few weeks.
I have a spreadsheet that quotes options with both traditional and weekly expiration dates. I'm still getting quotes for options that are one strike out of the money. However, anything beyond that returns an "Error"
For example,
=smfGetOptionQuotes
=smfGetOptionQuotes
To pull weeklies, I'm using this formula string:
=INDEX(smfGetOption
=smfGetOptionQuotes
The INDEX command, above, has been generating a blank for the option description. So, no quote is forthcoming.
So, I'm getting "Error" for OTM2 and more distant option quotes, and smfGetOptionStrikes is not generating a table of strikes, as it once did.
Any tips?
Thanks for the help.
Eric
Sun Dec 10, 2017 9:02 am (PST) . Posted by:
"Randy Harmelink" rharmelink
All of those appear to be working here. See below. Are you using the most
recent release of the add-in? There have been a number of changes to Yahoo
options quote processing this year.-
On Sun, Dec 10, 2017 at 7:35 AM, eabartsch@
...
wrote:
>
> I know you've been swamped with all the changes over the past few weeks.
>
> I have a spreadsheet that quotes options with both traditional and weekly
> expiration dates. I'm still getting quotes for options that are one strike
> out of the money. However, anything beyond that returns an "Error". The
> weekly fields now return blanks.
>
> For example,
>
> =smfGetOptionQuotes(F7,"bs",1,"Y"), where "F7" is "PG 12/15 2017 $89.50
> Put" correctly returns the bid and Strike Price.
>
> =smfGetOptionQuotes(F8,"as",1,"Y"), where "F8" is "PG 12/15 2017 $89.00
> Put" returns "Error" for the ask price and "89.0" for the strike price.
>
I get:
PG 12/15 2017 $89.50 Put bs Y 0.21 89.5
PG 12/15 2017 $89.00 Put as Y 0.15 89
> To pull weeklies, I'm using this formula string:
>
> =INDEX(smfGetOptionStrikes(Q4,P9,"P","B",1,4,1),2,1) (where Q4 is "PG"
> and P9 is "22-Dec-17") to generate the option description at R7 and then
> using
>
> =smfGetOptionQuotes(R7,"bs",1,"B") to get the bid and strike prices.
>
> The INDEX command, above, has been generating a blank for the option
> description. So, no quote is forthcoming.
>
I get:
2017-12-22 PG P B PG 12/22 2017 $90.00 Put bs 0.53 90
> So, I'm getting "Error" for OTM2 and more distant option quotes, and
> smfGetOptionStrikes is not generating a table of strikes, as it once did.
>
I had to remove the ITM/OTM options when Yahoo updated their web pages.
But smfGetOptionStrikes() is working here:
PG
2017-12-15
P
Y
PG 12/15 2017 $89.00 Put
PG 12/15 2017 $89.50 Put
PG 12/15 2017 $90.00 Put
PG 12/15 2017 $90.50 Put
PG 12/15 2017 $91.00 Put
PG 12/15 2017 $91.50 Put
Any tips?
>
Another option is that Yahoo didn't have the data when you were attempting
to get the data? Sometimes they don't have bid/ask prices after the market
is closed.
recent release of the add-in? There have been a number of changes to Yahoo
options quote processing this year.-
On Sun, Dec 10, 2017 at 7:35 AM, eabartsch@
...
wrote:
>
> I know you've been swamped with all the changes over the past few weeks.
>
> I have a spreadsheet that quotes options with both traditional and weekly
> expiration dates. I'm still getting quotes for options that are one strike
> out of the money. However, anything beyond that returns an "Error"
> weekly fields now return blanks.
>
> For example,
>
> =smfGetOptionQuotes
> Put" correctly returns the bid and Strike Price.
>
> =smfGetOptionQuotes
> Put" returns "Error" for the ask price and "89.0" for the strike price.
>
I get:
PG 12/15 2017 $89.50 Put bs Y 0.21 89.5
PG 12/15 2017 $89.00 Put as Y 0.15 89
> To pull weeklies, I'm using this formula string:
>
> =INDEX(smfGetOption
> and P9 is "22-Dec-
> using
>
> =smfGetOptionQuotes
>
> The INDEX command, above, has been generating a blank for the option
> description. So, no quote is forthcoming.
>
I get:
2017-12-22 PG P B PG 12/22 2017 $90.00 Put bs 0.53 90
> So, I'm getting "Error" for OTM2 and more distant option quotes, and
> smfGetOptionStrikes is not generating a table of strikes, as it once did.
>
I had to remove the ITM/OTM options when Yahoo updated their web pages.
But smfGetOptionStrikes
PG
2017-12-15
P
Y
PG 12/15 2017 $89.00 Put
PG 12/15 2017 $89.50 Put
PG 12/15 2017 $90.00 Put
PG 12/15 2017 $90.50 Put
PG 12/15 2017 $91.00 Put
PG 12/15 2017 $91.50 Put
Any tips?
>
Another option is that Yahoo didn't have the data when you were attempting
to get the data? Sometimes they don't have bid/ask prices after the market
is closed.
Sun Dec 10, 2017 10:28 am (PST) . Posted by:
e_bartsch
Yeah, I'm getting blanks.
I wonder if it is because I have my watch list, which does a bunch of Yahoo pulls at a ForceRecalculate, as part of my overall workbook that also has worksheets that drill down on specific securities/options (which is from where I pulled the above formulas).
Maybe I should pull the watch list out as a separate workbook, so I can recalculate one separate from the other? The Watch list is still plagued by some error returns, notwithstanding my upgrade to the portfolio view functions. I am using the 11/30/17 version of the add-in.
I wonder if it is because I have my watch list, which does a bunch of Yahoo pulls at a ForceRecalculate, as part of my overall workbook that also has worksheets that drill down on specific securities/options (which is from where I pulled the above formulas).
Maybe I should pull the watch list out as a separate workbook, so I can recalculate one separate from the other? The Watch list is still plagued by some error returns, notwithstanding my upgrade to the portfolio view functions. I am using the 11/30/17 version of the add-in.
Sun Dec 10, 2017 10:37 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Do the functions work apart from your watchlist workbook?
If you can put something in a "test" workbook that isn't working and attach
it to an email, I can take a quick look and see if I spot something.
Another issue might be your version of operating system, IE, or EXCEL?
On Sun, Dec 10, 2017 at 11:28 AM, eabartsch@
...
wrote:
>
> Yeah, I'm getting blanks.
>
> I wonder if it is because I have my watch list, which does a bunch of
> Yahoo pulls at a ForceRecalculate, as part of my overall workbook that also
> has worksheets that drill down on specific securities/options (which is
> from where I pulled the above formulas).
>
> Maybe I should pull the watch list out as a separate workbook, so I can
> recalculate one separate from the other? The Watch list is still plagued
> by some error returns, notwithstanding my upgrade to the portfolio view
> functions. I am using the 11/30/17 version of the add-in.
>
>
If you can put something in a "test" workbook that isn't working and attach
it to an email, I can take a quick look and see if I spot something.
Another issue might be your version of operating system, IE, or EXCEL?
On Sun, Dec 10, 2017 at 11:28 AM, eabartsch@
...
wrote:
>
> Yeah, I'm getting blanks.
>
> I wonder if it is because I have my watch list, which does a bunch of
> Yahoo pulls at a ForceRecalculate, as part of my overall workbook that also
> has worksheets that drill down on specific securities/options (which is
> from where I pulled the above formulas).
>
> Maybe I should pull the watch list out as a separate workbook, so I can
> recalculate one separate from the other? The Watch list is still plagued
> by some error returns, notwithstanding my upgrade to the portfolio view
> functions. I am using the 11/30/17 version of the add-in.
>
>
Sun Dec 10, 2017 12:27 pm (PST) . Posted by:
e_bartsch
Randy:
That's curious. I started putting a test workbook together to send to you by copying worksheets over to a clean workbook. A recalc of the clean workbook started working!
So, it must be my relic worksheet. False alarm.
One other quick question--
I'm attempting to get "zsba" option quotes across an array range of D7:D125. The first 79 or so return information. Ater that, I'm getting errors.
I assume that is the Yahoo throttle kicking in. Is there a workaround?
Eric
That's curious. I started putting a test workbook together to send to you by copying worksheets over to a clean workbook. A recalc of the clean workbook started working!
So, it must be my relic worksheet. False alarm.
One other quick question--
I'm attempting to get "zsba" option quotes across an array range of D7:D125. The first 79 or so return information. Ater that, I'm getting errors.
I assume that is the Yahoo throttle kicking in. Is there a workaround?
Eric
Sun Dec 10, 2017 3:03 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I would assume it is the 1000-webpage limit of the add-in that you are
running into. Maybe that is what is causing some problems on your "relic"
as well?
When I make workbooks I use regularly, I often check the add-in log to see
how many web pages I'm accessing, and try to reduce it down as much as
possible. One big advantage is that it speeds up recalculation.
The "zs" data items of smfGetOptionQuotes() don't access the Internet at
all. But the "ba" portion will. Once per option ticker symbol.
One trick I've been using lately is to generate the Yahoo ticker symbols
(using "z") and then doing a single smfGetYahooPortfolioView() on all of
those resulting Yahoo ticker symbols. Then those 79 quotes could all be
retrieved in a single Internet access. And be combined with quotes from the
underlying equities as well.
With smfGetYahooPortfolioView(), it can be advantageous to putting **ALL**
of the quotes retrievals into a single worksheet, and then do lookups
(usually a MATCH()/INDEX() combination) into that worksheet for any of your
worksheet that present the data.
On Sun, Dec 10, 2017 at 1:27 PM, eabartsch@
...
wrote:
>
> That's curious. I started putting a test workbook together to send to you
> by copying worksheets over to a clean workbook. A recalc of the clean
> workbook started working!
>
> So, it must be my relic worksheet. False alarm.
>
> One other quick question--
>
> I'm attempting to get "zsba" option quotes across an array range of
> D7:D125. The first 79 or so return information. Ater that, I'm getting
> errors.
>
> I assume that is the Yahoo throttle kicking in. Is there a workaround?
>
>
>
running into. Maybe that is what is causing some problems on your "relic"
as well?
When I make workbooks I use regularly, I often check the add-in log to see
how many web pages I'm accessing, and try to reduce it down as much as
possible. One big advantage is that it speeds up recalculation.
The "zs" data items of smfGetOptionQuotes(
all. But the "ba" portion will. Once per option ticker symbol.
One trick I've been using lately is to generate the Yahoo ticker symbols
(using "z"
those resulting Yahoo ticker symbols. Then those 79 quotes could all be
retrieved in a single Internet access. And be combined with quotes from the
underlying equities as well.
With smfGetYahooPortfoli
of the quotes retrievals into a single worksheet, and then do lookups
(usually a MATCH()/INDEX(
worksheet that present the data.
On Sun, Dec 10, 2017 at 1:27 PM, eabartsch@
...
wrote:
>
> That's curious. I started putting a test workbook together to send to you
> by copying worksheets over to a clean workbook. A recalc of the clean
> workbook started working!
>
> So, it must be my relic worksheet. False alarm.
>
> One other quick question--
>
> I'm attempting to get "zsba" option quotes across an array range of
> D7:D125. The first 79 or so return information. Ater that, I'm getting
> errors.
>
> I assume that is the Yahoo throttle kicking in. Is there a workaround?
>
>
>
Sun Dec 10, 2017 5:45 pm (PST) . Posted by:
e_bartsch
Interesting.
How do you access the add-in log? I'll start there.
I might bifurcate the workbook. Just run the watch list. The, after I have my shortlist, close that sheet and open up the more specific sheets. That should cut down on the access time, too.
How do you access the add-in log? I'll start there.
I might bifurcate the workbook. Just run the watch list. The, after I have my shortlist, close that sheet and open up the more specific sheets. That should cut down on the access time, too.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar