5 New Messages
Digest #2881
Messages
Sun Dec 1, 2013 12:59 am (PST) . Posted by:
rharmelink
I'm not sure what to tell you -- I opened up the RCHGetTableCell-Template-IBD-Stock-Checkup.xls file from the group's FILES area, and everything there is working fine for me.
For your (1), I cut and pasted the relevant part of your formula:
=RCHGetTableCell("http://research.investors.com/stockcheckup/"&smfstrExtr(RCHGetWebData("http://www.investors.com/search/searchresults.aspx?Ntt="&M3,"/quotes/"),"/quotes/","'"),1,">Industry Group Rank")
...and it worked fine for me. Although I'm unclear why you don't just use the URL you generated in cell L3? The formula in the downloaded template, which I'm sure you used as your original source for the formula, just uses:
=RCHGetTableCell(URLCheckup,1,">Industry Group Rank")
...and, like I said, it also worked fine.
Hmmm. Is it possible you have trailing blanks on the end of the ticker symbol? Nah, then the other items wouldn't work either.
For (2), all extractions of data are done from a saved copy of a web page, so the web page is only retrieved once. However, if your URL isn't *exactly* the same (e.g. if the parameters are in a different order or there's a difference in upper and lower case), they will be treated as separate web pages. As far as I know, the 300 per day for stock checkups is still part of the terms of service.
But I haven't a clue why you're getting "Error" returned, since the formula appears to work fine here. Try using the URL in cell L3 and see if that works?
On Sun, Dec 1, 2013 at 12:17 AM, hjreed@swbell.net <hjreed@swbell.net mailto:hjreed@swbell.net> wrote:
I used to be able to query the Ranking of the groups from IBD. For some reason I get an Error for that data retrieval now.
I am logged into IDB using Web Query.
Here's a snapshot of the xls.
y T2.x IBD 50 Sector Pull Sector Industry Subgroup Current Group Rank Top 5 Composite Rating in Group # Stocks in Group URL one stock in the group 1 AEROSPACE AEROSPACE/DEFENSE Error BEAV HEI HEIA PCP ATRO 56 http://research.investors.com/ stockcheckup/nyse-transdigm- group-inc-tdg.aspx http://research.investors.com/stockcheckup/nyse-transdigm-group-inc-tdg.aspx TDG 2 AGRICULTRE AGRICULTURAL OPRTIONS Error ANDE LMNR GAGA POPE MON 20 http://research.investors.com/ stockcheckup/nasdaq-andersons- inc-ande.aspx http://research.investors.com/stockcheckup/nasdaq-andersons-inc-ande.aspx ANDE
D3 contains the following: =IF($A$1="y",IF(M3="","", RCHGetTableCell("http:// research.investors.com/ stockcheckup/"&smfStrExtr( RCHGetWebData("http://www. investors.com/search/ searchresults.aspx?Ntt="&M3,"/ quotes/"),"/quotes/","'"),1,"> Industry http://research.investors.com/stockcheckup/%22&smfStrExtr%28RCHGetWebData%28%22http://www.investors.com/search/searchresults.aspx?Ntt=%22&M3,%22/quotes/%22%29,%22/quotes/%22,%22%27%22%29,1,%22%3EIndustry Group Rank")),"")
This used to return the value of the IBD Group Rank
E3/F3/G3/H3/I3 contain: =IF($A$1="y",smfStrExtr( smfGetTagContent(L3,"dt",-1," Group Leaders","_rptComposite")," Symbol=",""""),"")
They work correctly and return the values of the top 5 tickers
J3 contains:
=IF($A$1="y",smfStrExtr( smfGetTagContent(L3,"p",-1," RANK WITHIN THE"),"("," STOCK"),"")
It works correctly and returns the number of stocks within the industry.
Two questions:
1) Do you see what is causing the Industry Group Rank not to be returned? I have tried several changes, but none seem to do the trick. Does it work for you?
2) I want to confirm this is pulling from the same query as E3-J3 or am I using two of the 300 hits IBD will let you have within a day? (or has that changed that you know of?)
Thank you in advance for taking a look at this.
For your (1), I cut and pasted the relevant part of your formula:
=RCHGetTableCell(
...and it worked fine for me. Although I'm unclear why you don't just use the URL you generated in cell L3? The formula in the downloaded template, which I'm sure you used as your original source for the formula, just uses:
=RCHGetTableCell(
...and, like I said, it also worked fine.
Hmmm. Is it possible you have trailing blanks on the end of the ticker symbol? Nah, then the other items wouldn't work either.
For (2), all extractions of data are done from a saved copy of a web page, so the web page is only retrieved once. However, if your URL isn't *exactly* the same (e.g. if the parameters are in a different order or there's a difference in upper and lower case), they will be treated as separate web pages. As far as I know, the 300 per day for stock checkups is still part of the terms of service.
But I haven't a clue why you're getting "Error" returned, since the formula appears to work fine here. Try using the URL in cell L3 and see if that works?
On Sun, Dec 1, 2013 at 12:17 AM, hjreed@swbell.
I used to be able to query the Ranking of the groups from IBD. For some reason I get an Error for that data retrieval now.
I am logged into IDB using Web Query.
Here's a snapshot of the xls.
y T2.x IBD 50 Sector Pull Sector Industry Subgroup Current Group Rank Top 5 Composite Rating in Group # Stocks in Group URL one stock in the group 1 AEROSPACE AEROSPACE/DEFENSE Error BEAV HEI HEIA PCP ATRO 56 http://research.
D3 contains the following: =IF($A$1="
This used to return the value of the IBD Group Rank
E3/F3/G3/H3/
They work correctly and return the values of the top 5 tickers
J3 contains:
=IF($A$1="
It works correctly and returns the number of stocks within the industry.
Two questions:
1) Do you see what is causing the Industry Group Rank not to be returned? I have tried several changes, but none seem to do the trick. Does it work for you?
2) I want to confirm this is pulling from the same query as E3-J3 or am I using two of the 300 hits IBD will let you have within a day? (or has that changed that you know of?)
Thank you in advance for taking a look at this.
Sun Dec 1, 2013 7:08 am (PST) . Posted by:
billtrig
I've been using some smf code for a couple of years to pull down stock and option prices into Excel 2010, but am currently considering buying a Mac. I haven't visited this site for a long time and cannot quickly find any FAQ that addresses my questions.
(1) Is there some equivalent sort of thing that would work with Excel for Mac? I've already wasted a few hours searching the internet to no avail. A simple tool that just downloads data into Excel (if it exists) doesn't seem to be described uniquely when using terms that don't pull in tons of unrelated topics (download, options, prices, etc. pick up huge numbers of unrelated hits). Just a pointer to get me started how to search would help. At this point, I'm willing to spend money on it, but I don't want an integrated package that holds data in its own database -- I've been using and adapting the same basic spreadsheet for years and don't wish to change that now.
(2) There's a chance that I'll stick with the Wintel world, and if so I probably need to spend some time to fix my add-ins. Right now I'm primarily relying on smfGetYahooOptionQuote to get option prices. But the process is very slow (I'm updating a few hundred option prices spread over a couple of different sheets) -- enough so that I do it in a separate spreadsheet so that I don't have the formulas open all the time, only when I'm ready to be patient, at which point I "copy/paste value" the results to my main spreadsheet and close the get-prices spreadsheet. Other than replacing smfGetYahooOptionQuote with a newer version, is there a better way to pull down a few hundred bid-ask option prices at one time (it's clearly not intended for large-quantity use)?
Sorry for the naive/ignorant questions.
Bill T
(1) Is there some equivalent sort of thing that would work with Excel for Mac? I've already wasted a few hours searching the internet to no avail. A simple tool that just downloads data into Excel (if it exists) doesn't seem to be described uniquely when using terms that don't pull in tons of unrelated topics (download, options, prices, etc. pick up huge numbers of unrelated hits). Just a pointer to get me started how to search would help. At this point, I'm willing to spend money on it, but I don't want an integrated package that holds data in its own database -- I've been using and adapting the same basic spreadsheet for years and don't wish to change that now.
(2) There's a chance that I'll stick with the Wintel world, and if so I probably need to spend some time to fix my add-ins. Right now I'm primarily relying on smfGetYahooOptionQu
Sorry for the naive/ignorant questions.
Bill T
Sun Dec 1, 2013 8:03 am (PST) . Posted by:
"Randy Harmelink" rharmelink
No Mac stuff from me. Never had one. Never wanted one. I'm not a fan of
Apple.
You can pull down a number of options quotes at one time using the Yahoo
API. Here are a few sample URLs:
- All expirations for ticker
"MMM"<http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol=%27MMM%27%20AND%20expiration%20in%20%28SELECT%20contract%20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%27MMM%27%29&env=http://datatables.org/alltables.env>
- All next month expirations for tickers "MMM" and
"GOOG"<http://query.yahooapis.com/v1/public/yql?env=http://datatables.org/alltables.env&q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol%20IN%28%27MMM%27,%27GOOG%27%29>
- All 2015-01 expirations for tickers "MMM" and
"GOOG"<http://query.yahooapis.com/v1/public/yql?env=http://datatables.org/alltables.env&q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol%20IN%28%27MMM%27,%27GOOG%27%29%20AND%20expiration=%272015-01%27>
- All options in 2014-01 and 2015-01 for ticker
"MMM"<http://query.yahooapis.com/v1/public/yql?env=http://datatables.org/alltables.env&q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol=%27MMM%27%20AND%20expiration%20IN%28%272014-01%27,%272015-01%27%29>
On Sun, Dec 1, 2013 at 7:59 AM, <billtrig@yahoo.com> wrote:
>
> I've been using some smf code for a couple of years to pull down stock and
> option prices into Excel 2010, but am currently considering buying a Mac. I
> haven't visited this site for a long time and cannot quickly find any FAQ
> that addresses my questions.
>
> (1) Is there some equivalent sort of thing that would work with Excel for
> Mac? I've already wasted a few hours searching the internet to no avail. A
> simple tool that just downloads data into Excel (if it exists) doesn't seem
> to be described uniquely when using terms that don't pull in tons of
> unrelated topics (download, options, prices, etc. pick up huge numbers of
> unrelated hits). Just a pointer to get me started how to search would help.
> At this point, I'm willing to spend money on it, but I don't want an
> integrated package that holds data in its own database -- I've been using
> and adapting the same basic spreadsheet for years and don't wish to change
> that now.
>
> (2) There's a chance that I'll stick with the Wintel world, and if so I
> probably need to spend some time to fix my add-ins. Right now I'm primarily
> relying on smfGetYahooOptionQuote to get option prices. But the process is
> very slow (I'm updating a few hundred option prices spread over a couple of
> different sheets) -- enough so that I do it in a separate spreadsheet so
> that I don't have the formulas open all the time, only when I'm ready to be
> patient, at which point I "copy/paste value" the results to my main
> spreadsheet and close the get-prices spreadsheet. Other than replacing
> smfGetYahooOptionQuote with a newer version, is there a better way to pull
> down a few hundred bid-ask option prices at one time (it's clearly not
> intended for large-quantity use)?
>
Apple.
You can pull down a number of options quotes at one time using the Yahoo
API. Here are a few sample URLs:
- All expirations for ticker
"MMM"
- All next month expirations for tickers "MMM" and
"GOOG"
- All 2015-01 expirations for tickers "MMM" and
"GOOG"
- All options in 2014-01 and 2015-01 for ticker
"MMM"
On Sun, Dec 1, 2013 at 7:59 AM, <billtrig@yahoo.
>
> I've been using some smf code for a couple of years to pull down stock and
> option prices into Excel 2010, but am currently considering buying a Mac. I
> haven't visited this site for a long time and cannot quickly find any FAQ
> that addresses my questions.
>
> (1) Is there some equivalent sort of thing that would work with Excel for
> Mac? I've already wasted a few hours searching the internet to no avail. A
> simple tool that just downloads data into Excel (if it exists) doesn't seem
> to be described uniquely when using terms that don't pull in tons of
> unrelated topics (download, options, prices, etc. pick up huge numbers of
> unrelated hits). Just a pointer to get me started how to search would help.
> At this point, I'm willing to spend money on it, but I don't want an
> integrated package that holds data in its own database -- I've been using
> and adapting the same basic spreadsheet for years and don't wish to change
> that now.
>
> (2) There's a chance that I'll stick with the Wintel world, and if so I
> probably need to spend some time to fix my add-ins. Right now I'm primarily
> relying on smfGetYahooOptionQu
> very slow (I'm updating a few hundred option prices spread over a couple of
> different sheets) -- enough so that I do it in a separate spreadsheet so
> that I don't have the formulas open all the time, only when I'm ready to be
> patient, at which point I "copy/paste value" the results to my main
> spreadsheet and close the get-prices spreadsheet. Other than replacing
> smfGetYahooOptionQu
> down a few hundred bid-ask option prices at one time (it's clearly not
> intended for large-quantity use)?
>
Sun Dec 1, 2013 5:06 pm (PST) . Posted by:
billtrig
Randy, thanks for the response. However, my large number of options isn't solely the result of pulling down variations of a particular option (e.g., trying to get all prices for a particular month) but rather simply because I'm tracking lots of underlyings, with different strikes and expiries. Thus, each data pull (except for vertical spreads, in which case I'm looking at two different strikes for a particular ticker/expiry). Thus, I'm not sure that your description of the Yahoo API would help.
As to a "Mac solution", from what I've heard, the current version of Excel for the Mac is much more compatible than in the old days, so it's possible that the macros/formulas here would work there, but I'll need to get somebody to test it for me to know. Meantime, I'm open to any alternative, including a more efficient data pull using smf tools or else a totally different approach, be it for Windows or Mac.
---In smf_addin@yahoogroups.com, <billtrig@...> wrote:
I've been using some smf code for a couple of years to pull down stock and option prices into Excel 2010, but am currently considering buying a Mac. I haven't visited this site for a long time and cannot quickly find any FAQ that addresses my questions.
(1) Is there some equivalent sort of thing that would work with Excel for Mac? I've already wasted a few hours searching the internet to no avail. A simple tool that just downloads data into Excel (if it exists) doesn't seem to be described uniquely when using terms that don't pull in tons of unrelated topics (download, options, prices, etc. pick up huge numbers of unrelated hits). Just a pointer to get me started how to search would help. At this point, I'm willing to spend money on it, but I don't want an integrated package that holds data in its own database -- I've been using and adapting the same basic spreadsheet for years and don't wish to change that now.
(2) There's a chance that I'll stick with the Wintel world, and if so I probably need to spend some time to fix my add-ins. Right now I'm primarily relying on smfGetYahooOptionQuote to get option prices. But the process is very slow (I'm updating a few hundred option prices spread over a couple of different sheets) -- enough so that I do it in a separate spreadsheet so that I don't have the formulas open all the time, only when I'm ready to be patient, at which point I "copy/paste value" the results to my main spreadsheet and close the get-prices spreadsheet. Other than replacing smfGetYahooOptionQuote with a newer version, is there a better way to pull down a few hundred bid-ask option prices at one time (it's clearly not intended for large-quantity use)?
Sorry for the naive/ignorant questions.
Bill T
As to a "Mac solution"
---In smf_addin@yahoogrou
I've been using some smf code for a couple of years to pull down stock and option prices into Excel 2010, but am currently considering buying a Mac. I haven't visited this site for a long time and cannot quickly find any FAQ that addresses my questions.
(1) Is there some equivalent sort of thing that would work with Excel for Mac? I've already wasted a few hours searching the internet to no avail. A simple tool that just downloads data into Excel (if it exists) doesn't seem to be described uniquely when using terms that don't pull in tons of unrelated topics (download, options, prices, etc. pick up huge numbers of unrelated hits). Just a pointer to get me started how to search would help. At this point, I'm willing to spend money on it, but I don't want an integrated package that holds data in its own database -- I've been using and adapting the same basic spreadsheet for years and don't wish to change that now.
(2) There's a chance that I'll stick with the Wintel world, and if so I probably need to spend some time to fix my add-ins. Right now I'm primarily relying on smfGetYahooOptionQu
Sorry for the naive/ignorant questions.
Bill T
Sun Dec 1, 2013 7:15 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Well, you could even do something like this, which would get two option
contracts for two different ticker symbols:
http://query.yahooapis.com/v1/public/yql?env=http://datatables.org/alltables.env&q=select%20option%20from%20yahoo.finance.options%20where%20%28symbol=%22MMM%22%20and%20expiration=%222014-01%22%20and%20option.symbol%20IN%28%22MMM140118C00045000%22,%22MMM140118C00060000%22%29%29%20OR%20%28symbol=%22GOOG%22%20and%20expiration=%222014-01%22%20and%20option.symbol%20IN%28%22GOOG140118C00285000%22,%22GOOG140118C00290000%22%29%29
Or, in a more readable format:
http://query.yahooapis.com/v1/public/yql?env=http://datatables.org/alltables.env&q=selectoption
from yahoo.finance.options where
(symbol="MMM" and expiration="2014-01" and option.symbol
IN("MMM140118C00045000","MMM140118C00060000")) OR
(symbol="GOOG" and expiration="2014-01" and option.symbol
IN("GOOG140118C00285000","GOOG140118C00290000"))
But the URLs start to get a bit long.
I was able to get about 23 individual option contracts with one URL.
On Sun, Dec 1, 2013 at 6:06 PM, <billtrig@yahoo.com> wrote:
> Randy, thanks for the response. However, my large number of options
> isn't solely the result of pulling down variations of a particular option
> (e.g., trying to get all prices for a particular month) but rather simply
> because I'm tracking lots of underlyings, with different strikes and
> expiries. Thus, each data pull (except for vertical spreads, in which case
> I'm looking at two different strikes for a particular ticker/expiry).
> Thus, I'm not sure that your description of the Yahoo API would help.
>
> As to a "Mac solution", from what I've heard, the current version of Excel
> for the Mac is much more compatible than in the old days, so it's possible
> that the macros/formulas here would work there, but I'll need to get
> somebody to test it for me to know. Meantime, I'm open to any alternative,
> including a more efficient data pull using smf tools or else a totally
> different approach, be it for Windows or Mac.
>
contracts for two different ticker symbols:
http://query.
Or, in a more readable format:
http://query.
from yahoo.finance.
(symbol="
IN("MMM140118C
(symbol="
IN("GOOG140118
But the URLs start to get a bit long.
I was able to get about 23 individual option contracts with one URL.
On Sun, Dec 1, 2013 at 6:06 PM, <billtrig@yahoo.
> Randy, thanks for the response. However, my large number of options
> isn't solely the result of pulling down variations of a particular option
> (e.g., trying to get all prices for a particular month) but rather simply
> because I'm tracking lots of underlyings, with different strikes and
> expiries. Thus, each data pull (except for vertical spreads, in which case
> I'm looking at two different strikes for a particular ticker/expiry)
> Thus, I'm not sure that your description of the Yahoo API would help.
>
> As to a "Mac solution"
> for the Mac is much more compatible than in the old days, so it's possible
> that the macros/formulas here would work there, but I'll need to get
> somebody to test it for me to know. Meantime, I'm open to any alternative,
> including a more efficient data pull using smf tools or else a totally
> different approach, be it for Windows or Mac.
>
Tidak ada komentar:
Posting Komentar