6 Messages
Digest #3539
Messages
Wed Oct 21, 2015 10:04 am (PDT) . Posted by:
gaeisen
Thanks Randy. That fix on Barron's using ,,,,,,,,2 at the end of the string worked on almost all my cells. Only one or 2 exceptions.
I figured Barron's had done something like that to mess me up.
On the CBOE page, I looked closely and they clearly do NOT want me to try to pull their data using the SMF. They post a warning:
PLEASE NOTE: IT IS STRICTLY PROHIBITED TO DOWNLOAD DELAYED QUOTE TABLE DATA FROM THIS WEB SITE BY USING AUTO-EXTRACTION PROGRAMS/QUERIES AND/OR SOFTWARE. CBOE WILL BLOCK IP ADDRESSES OF ALL PARTIES WHO ATTEMPT TO DO SO. THIS DATA IS PROPERTY OF THOMSON REUTERS OR ITS DATA PROVIDERS. DOWNLOADING THIS DATA IN ANY OTHER WAY THAN BY MANUAL TICKER SYMBOL ENTRY IS STRICTLY PROHIBITED.
So not surprising. That link used to work. Oh well. Though it brings up a troubling thought: Imagine if all web sites (at least the ones with data we SMF users want to use) adopt a similar policy to try to block data extraction?
Thanks again.
Greg
----- Forwarded Message -----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: "smf addin" <smf_addin@yahoogroups.com>
Sent: Tuesday, October 20, 2015 8:41:03 PM
Subject: Re: [smf_addin] Data not loading successfully from Barron's or CBOE site
It looks like Barron's has done something to prevent the add-in from grabbing the source code of the web page using the XMLHTTP protocol, because RCHGetWebData() on the web page is returning a null string. So, of course there is nothing to extract.
As a last resort, you could try:
=RCHGetTableCell(" http://online.barrons.com/public/page/9_0210-cboemktrt.html ",3,">S&P 100",,,,,,,,2)
The "2" tells the function to try and retrieve the HTMLObject of the web page instead. That may or may not work, depending on which versions of EXCEL and IE you have. And you may get an IE warning message of some type?
The CBOE extraction fails because there's no quote on the web page you're using:
http://www.cboe.com/DelayedQuote/SimpleQuote.aspx?ticker=Vix
At least none when I looked. It went to a symbol search web page.
On Tue, Oct 20, 2015 at 7:25 PM, g.eisen@... wrote:
Hi. I've been using the SMF Add-In successfully, but lately I'm getting ERROR returned on my links to Barron's and the CBOE.
The pages at Barron's and the CBOE do not appear to have changed, and yet the links that worked before do not work now.
Any idea how I should approach this?
Example:
Cell Entry: =RCHGetTableCell(" http://online.barrons.com/public/page/9_0210-cboemktrt.html ",3,">S&P 100")
Would previously link to this page: http://www.barrons.com/public/page/9_0210-cboemktrt.html
and return the figure for the most recent week's Put/Call ratio, 129/100, in column 3 for the S&P 100.
Now it returns "Error".
Another example from the CBOE:
Cell Entry: =RCHGetTableCell(" http://www.cboe.com/DelayedQuote/SimpleQuote.aspx?ticker=Vix ",1,"Last Sale")
Used to link to page: http://www.cboe.com/DelayedQuote/SimpleQuote.aspx?ticker=VIX
And would return the most recent quote for the VIX.
And now it returns Error.
Yet my SMF is working fine for pulling down data from YAHOO. So it's working, just not for Barron's or from the CBOE.
Again, it does not appear the pages themselves have changed, so I'm at a loss as to what to change on my entry.
System: Excel 2010 on Windows 7.
Any advice?
I figured Barron's had done something like that to mess me up.
On the CBOE page, I looked closely and they clearly do NOT want me to try to pull their data using the SMF. They post a warning:
PLEASE NOTE: IT IS STRICTLY PROHIBITED TO DOWNLOAD DELAYED QUOTE TABLE DATA FROM THIS WEB SITE BY USING AUTO-EXTRACTION PROGRAMS/QUERIES AND/OR SOFTWARE. CBOE WILL BLOCK IP ADDRESSES OF ALL PARTIES WHO ATTEMPT TO DO SO. THIS DATA IS PROPERTY OF THOMSON REUTERS OR ITS DATA PROVIDERS. DOWNLOADING THIS DATA IN ANY OTHER WAY THAN BY MANUAL TICKER SYMBOL ENTRY IS STRICTLY PROHIBITED.
So not surprising. That link used to work. Oh well. Though it brings up a troubling thought: Imagine if all web sites (at least the ones with data we SMF users want to use) adopt a similar policy to try to block data extraction?
Thanks again.
Greg
----- Forwarded Message -----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: "smf addin" <smf_addin@yahoogroups.com>
Sent: Tuesday, October 20, 2015 8:41:03 PM
Subject: Re: [smf_addin] Data not loading successfully from Barron's or CBOE site
It looks like Barron's has done something to prevent the add-in from grabbing the source code of the web page using the XMLHTTP protocol, because RCHGetWebData() on the web page is returning a null string. So, of course there is nothing to extract.
As a last resort, you could try:
=RCHGetTableCell(" http://online.barrons.com/public/page/9_0210-cboemktrt.html ",3,">S&P 100",,,,,,,,2)
The "2" tells the function to try and retrieve the HTMLObject of the web page instead. That may or may not work, depending on which versions of EXCEL and IE you have. And you may get an IE warning message of some type?
The CBOE extraction fails because there's no quote on the web page you're using:
http://www.cboe.com/DelayedQuote/SimpleQuote.aspx?ticker=Vix
At least none when I looked. It went to a symbol search web page.
On Tue, Oct 20, 2015 at 7:25 PM, g.eisen@... wrote:
Hi. I've been using the SMF Add-In successfully, but lately I'm getting ERROR returned on my links to Barron's and the CBOE.
The pages at Barron's and the CBOE do not appear to have changed, and yet the links that worked before do not work now.
Any idea how I should approach this?
Example:
Cell Entry: =RCHGetTableCell(" http://online.barrons.com/public/page/9_0210-cboemktrt.html ",3,">S&P 100")
Would previously link to this page: http://www.barrons.com/public/page/9_0210-cboemktrt.html
and return the figure for the most recent week's Put/Call ratio, 129/100, in column 3 for the S&P 100.
Now it returns "Error".
Another example from the CBOE:
Cell Entry: =RCHGetTableCell(" http://www.cboe.com/DelayedQuote/SimpleQuote.aspx?ticker=Vix ",1,"Last Sale")
Used to link to page: http://www.cboe.com/DelayedQuote/SimpleQuote.aspx?ticker=VIX
And would return the most recent quote for the VIX.
And now it returns Error.
Yet my SMF is working fine for pulling down data from YAHOO. So it's working, just not for Barron's or from the CBOE.
Again, it does not appear the pages themselves have changed, so I'm at a loss as to what to change on my entry.
System: Excel 2010 on Windows 7.
Any advice?
Wed Oct 21, 2015 11:16 am (PDT) . Posted by:
rtcutler
Addin Manager!! THANK YOU! I pointed the addin manager to the smf spreadsheet, it knew what to do with it and my problems are solved.
I told you it was a dumby question, and I'm not surprised that it had a simple answer.
I told you it was a dumby question, and I'm not surprised that it had a simple answer.
Wed Oct 21, 2015 12:08 pm (PDT) . Posted by:
johnwyard
I am a member of Morningstar premium and would like to automate the downloading of etf roics. This is available under my premium membership. This is a different path than stock roics.
I am new to smf-addin , and appreciate any assistance.
JYard
I am new to smf-addin , and appreciate any assistance.
JYard
Wed Oct 21, 2015 12:11 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sample web pages please?
On Wed, Oct 21, 2015 at 12:05 PM, johnwyard@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I am a member of Morningstar premium and would like to automate the
> downloading of etf roics. This is available under my premium membership.
> This is a different path than stock roics.
>
> I am new to smf-addin , and appreciate any assistance.
>
>
On Wed, Oct 21, 2015 at 12:05 PM, johnwyard@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I am a member of Morningstar premium and would like to automate the
> downloading of etf roics. This is available under my premium membership.
> This is a different path than stock roics.
>
> I am new to smf-addin , and appreciate any assistance.
>
>
Wed Oct 21, 2015 12:30 pm (PDT) . Posted by:
johnwyard
The url is
portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
SPDR® S&P 500 ETF SPY
Add to Portfolio http://portfolio.morningstar.com/RtPort/reg/addtoportfolio.aspx?ticker=SPY Get E-mail Alerts http://portfolio.morningstar.com/PortAsp/EditAlerts.asp?ptype=T&action=E&symbol=SPY®ion=usa&x=#StockFundAlerts Print This Page Data Question Quote http://www.morningstar.com/etfs/ARCX/SPY/quote.html Chart http://quotes.morningstar.com/chart/etf/chart.action?t=SPY®ion=usa&culture=en-US ETF Analysis http://analysis.morningstar.com/analystreport/ear.aspx?Symbol=SPY&Country=usa Distributions http://etfs.morningstar.com/distribution?t=SPY®ion=usa&culture=en-US Performance http://performance.morningstar.com/funds/etf/total-returns.action?t=SPY®ion=usa&culture=en-US Ratings & Risk http://performance.morningstar.com/funds/etf/ratings-risk.action?t=SPY®ion=usa&culture=en-US Portfolio http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US Fees & Expenses http://financials.morningstar.com/etfund/operations.html?t=SPY®ion=usa&culture=en-US Tax http://performance.morningstar.com/fund/tax-analysis.action?t=SPY®ion=usa&culture=en-US Ownership http://investors.morningstar.com/ownership/shareholders-overview.html?t=SPY®ion=usa&culture=en-US Filings http://quicktake.morningstar.com/EtfNet/SecFiling.aspx?Symbol=SPY&Country=usa Summary http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US Holdings http://portfolios.morningstar.com/fund/holdings?t=SPY®ion=usa&culture=en-US Premium Details http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
Benchmark Morningstar Category Russell 1000 TR USD
Large Blend
Equity Portfolio Financial Metrics SPY
% of Portfolio Benchmark Category Avg ROIC 12.99 12.52 12.15 Wide Moat Coverage 52.31 49.83 50.74 Narrow Moat Coverage 38.79 40.01 43.89 No Moat Coverage 8.91 10.16 5.38 Financial Health B+ B+ B+ Profitability C C C Growth B- B- B- Cash Return % 23.44 18.07 24.88 Free Cashflow Yield ex-financials 0.04 0.04 0.05 D/C Ratio 39.17 39.81 36.71
portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
SPDR® S&P 500 ETF SPY
Add to Portfolio http://portfolio.morningstar.com/RtPort/reg/addtoportfolio.aspx?ticker=SPY Get E-mail Alerts http://portfolio.morningstar.com/PortAsp/EditAlerts.asp?ptype=T&action=E&symbol=SPY®ion=usa&x=#StockFundAlerts Print This Page Data Question Quote http://www.morningstar.com/etfs/ARCX/SPY/quote.html Chart http://quotes.morningstar.com/chart/etf/chart.action?t=SPY®ion=usa&culture=en-US ETF Analysis http://analysis.morningstar.com/analystreport/ear.aspx?Symbol=SPY&Country=usa Distributions http://etfs.morningstar.com/distribution?t=SPY®ion=usa&culture=en-US Performance http://performance.morningstar.com/funds/etf/total-returns.action?t=SPY®ion=usa&culture=en-US Ratings & Risk http://performance.morningstar.com/funds/etf/ratings-risk.action?t=SPY®ion=usa&culture=en-US Portfolio http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US Fees & Expenses http://financials.morningstar.com/etfund/operations.html?t=SPY®ion=usa&culture=en-US Tax http://performance.morningstar.com/fund/tax-analysis.action?t=SPY®ion=usa&culture=en-US Ownership http://investors.morningstar.com/ownership/shareholders-overview.html?t=SPY®ion=usa&culture=en-US Filings http://quicktake.morningstar.com/EtfNet/SecFiling.aspx?Symbol=SPY&Country=usa Summary http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US Holdings http://portfolios.morningstar.com/fund/holdings?t=SPY®ion=usa&culture=en-US Premium Details http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
Benchmark Morningstar Category Russell 1000 TR USD
Large Blend
Equity Portfolio Financial Metrics SPY
% of Portfolio Benchmark Category Avg ROIC 12.99 12.52 12.15 Wide Moat Coverage 52.31 49.83 50.74 Narrow Moat Coverage 38.79 40.01 43.89 No Moat Coverage 8.91 10.16 5.38 Financial Health B+ B+ B+ Profitability C C C Growth B- B- B- Cash Return % 23.44 18.07 24.88 Free Cashflow Yield ex-financials 0.04 0.04 0.05 D/C Ratio 39.17 39.81 36.71
Wed Oct 21, 2015 2:09 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
That data is within the source code of the web page, so normal add-in
functions can extract the data. For example:
=RCHGetTableCell("
http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
",1,">ROIC")
...or you could grab the entire table by array-entering this:
=RCHGetHTMLTable("
http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
",">ROIC",-1,"",1)
On Wed, Oct 21, 2015 at 12:30 PM, johnwyard@... wrote:
> The url is
>
> portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
> SPDR® S&P 500 ETF SPY
>
> - Add to Portfolio
> <http://portfolio.morningstar.com/RtPort/reg/addtoportfolio.aspx?ticker=SPY>
> - Get E-mail Alerts
> <http://portfolio.morningstar.com/PortAsp/EditAlerts.asp?ptype=T&action=E&symbol=SPY®ion=usa&x=#StockFundAlerts>
> - Print This Page
> - Data Question
>
>
> - Quote <http://www.morningstar.com/etfs/ARCX/SPY/quote.html>
> - Chart
> <http://quotes.morningstar.com/chart/etf/chart.action?t=SPY®ion=usa&culture=en-US>
> - ETF Analysis
> <http://analysis.morningstar.com/analystreport/ear.aspx?Symbol=SPY&Country=usa>
> - Distributions
> <http://etfs.morningstar.com/distribution?t=SPY®ion=usa&culture=en-US>
> - Performance
> <http://performance.morningstar.com/funds/etf/total-returns.action?t=SPY®ion=usa&culture=en-US>
> - Ratings & Risk
> <http://performance.morningstar.com/funds/etf/ratings-risk.action?t=SPY®ion=usa&culture=en-US>
> - Portfolio
> <http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US>
> - Fees & Expenses
> <http://financials.morningstar.com/etfund/operations.html?t=SPY®ion=usa&culture=en-US>
> - Tax
> <http://performance.morningstar.com/fund/tax-analysis.action?t=SPY®ion=usa&culture=en-US>
> - Ownership
> <http://investors.morningstar.com/ownership/shareholders-overview.html?t=SPY®ion=usa&culture=en-US>
> - Filings
> <http://quicktake.morningstar.com/EtfNet/SecFiling.aspx?Symbol=SPY&Country=usa>
>
>
> - Summary
> <http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US>
> - Holdings
> <http://portfolios.morningstar.com/fund/holdings?t=SPY®ion=usa&culture=en-US>
> - Premium Details
> <http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US>
>
> BenchmarkMorningstar Category
> Russell 1000 TR USD
> Large Blend
> Equity Portfolio Financial Metrics SPY
> % of PortfolioBenchmarkCategory AvgROIC12.9912.5212.15Wide Moat Coverage
> 52.3149.8350.74Narrow Moat Coverage38.7940.0143.89No Moat Coverage8.91
> 10.165.38Financial HealthB+B+B+ProfitabilityCCCGrowthB-B-B-Cash Return %
> 23.4418.0724.88Free Cashflow Yield ex-financials0.040.040.05D/C Ratio39.17
> 39.8136.71
>
functions can extract the data. For example:
=RCHGetTableCell("
http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
",1,">ROIC")
...or you could grab the entire table by array-entering this:
=RCHGetHTMLTable("
http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
",">ROIC",-1,"",1)
On Wed, Oct 21, 2015 at 12:30 PM, johnwyard@... wrote:
> The url is
>
> portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US
> SPDR® S&P 500 ETF SPY
>
> - Add to Portfolio
> <http://portfolio.morningstar.com/RtPort/reg/addtoportfolio.aspx?ticker=SPY>
> - Get E-mail Alerts
> <http://portfolio.morningstar.com/PortAsp/EditAlerts.asp?ptype=T&action=E&symbol=SPY®ion=usa&x=#StockFundAlerts>
> - Print This Page
> - Data Question
>
>
> - Quote <http://www.morningstar.com/etfs/ARCX/SPY/quote.html>
> - Chart
> <http://quotes.morningstar.com/chart/etf/chart.action?t=SPY®ion=usa&culture=en-US>
> - ETF Analysis
> <http://analysis.morningstar.com/analystreport/ear.aspx?Symbol=SPY&Country=usa>
> - Distributions
> <http://etfs.morningstar.com/distribution?t=SPY®ion=usa&culture=en-US>
> - Performance
> <http://performance.morningstar.com/funds/etf/total-returns.action?t=SPY®ion=usa&culture=en-US>
> - Ratings & Risk
> <http://performance.morningstar.com/funds/etf/ratings-risk.action?t=SPY®ion=usa&culture=en-US>
> - Portfolio
> <http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US>
> - Fees & Expenses
> <http://financials.morningstar.com/etfund/operations.html?t=SPY®ion=usa&culture=en-US>
> - Tax
> <http://performance.morningstar.com/fund/tax-analysis.action?t=SPY®ion=usa&culture=en-US>
> - Ownership
> <http://investors.morningstar.com/ownership/shareholders-overview.html?t=SPY®ion=usa&culture=en-US>
> - Filings
> <http://quicktake.morningstar.com/EtfNet/SecFiling.aspx?Symbol=SPY&Country=usa>
>
>
> - Summary
> <http://portfolios.morningstar.com/fund/summary?t=SPY®ion=usa&culture=en-US>
> - Holdings
> <http://portfolios.morningstar.com/fund/holdings?t=SPY®ion=usa&culture=en-US>
> - Premium Details
> <http://portfolios.morningstar.com/fund/details?t=SPY®ion=usa&culture=en-US>
>
> BenchmarkMorningsta
> Russell 1000 TR USD
> Large Blend
> Equity Portfolio Financial Metrics SPY
> % of PortfolioBenchmarkC
> 52.3149.8350.
> 10.165.38Financial HealthB+B+B+
> 23.4418.0724.
> 39.8136.71
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar