Kamis, 30 Januari 2014

[smf_addin] Digest Number 2948

15 New Messages

Digest #2948
1a
Morningstar stocks credit rating by "Jacob Jose" pepecan47
1b
Re: Morningstar stocks credit rating by "Randy Harmelink" rharmelink
3a

Messages

Thu Jan 30, 2014 9:43 am (PST) . Posted by:

"Jacob Jose" pepecan47

Randy, when using element 884 for M* Credit Rating, for many of the stocks I follow, the function  returns Error.

I get much better results using for credit ratings:

=RCHGetTableCell(smfstrExtr(smfGetTagContent("http://quicktake.morningstar.com/stocknet/bonds.aspx?symbol="&A3,"td",-1,"iframe"),"src=""",""""),1,"Credit Rating","<tr")

You might want to consider changing the definition for element 884?

PEP, KO, JNJ, KMB, MCD and others return Error using element 884.

Regards,
Jose L. Jacob

Thu Jan 30, 2014 10:09 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Done. They must have removed it from the "Take" page. I can't imagine why I
would have created the element as an item from that web page, unless it had
been there. :)

I typically use MMM and IBM as my guides, and neither "Take" page for them
has it on it now.

Thanks for the heads-up.

On Thu, Jan 30, 2014 at 10:43 AM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
> Randy, when using element 884 for M* Credit Rating, for many of the
> stocks I follow, the function returns Error.
>
> I get much better results using for credit ratings:
>
> =RCHGetTableCell(smfstrExtr(smfGetTagContent("
> http://quicktake.morningstar.com/stocknet/bonds.aspx?symbol="&A3,"td",-1,"iframe"),"src=""",""""),1,"CreditRating","<tr")
>
> You might want to consider changing the definition for element 884?
>
> PEP, KO, JNJ, KMB, MCD and others return Error using element 884.
>
>

Thu Jan 30, 2014 10:35 am (PST) . Posted by:

rharmelink

I just uploaded a new official version of the add-in ZIP archive. It contains all the latest element definitions, as well as several new functions and fixed functions. I had been holding off on a new official release, waiting until AdvFN changed over to the new "Beta" format. But, given AdvFN's change process, that may take a while...
Changes made to the add-in since the 2012-12-29 release: Date Routine Description 2013-01-04 smfGetMSNOptionQuote
smfGetOXOptionQuote
smfGetYahooOptionQuote
Remove day 1, 30, and 31 assumptions for option expiration date (because weekly options can fall on those days) 2013-01-06 smfGetOptionExpirations Modified Yahoo processing to pick up expiration dates from Yahoo's API feed (a complete list) 2013-02-06 smfGetOptionQuotes
smfGetYahooOptionQuotes Add workaround to fix the Yahoo "^VIX" / "VIX" issue 2013-03-02 smfGetBigChartsOptionQuote New function to get current and historical option quotes from bigcharts.com 2013-03-13 RCHGetElementNumber Fixed definition of element #13873 to pick up 2-word recommendations (i.e. "Strong Buy") 2013-03-16 RCHGetElementNumber Fixed elements #13774-13776, "5-yr avg effective tax rates" from Reuters 2013-05-20 RCHGetElementNumber Changed element #1285 (Yahoo next earnings date) to pick the date up from Yahoo's main quote page (smf-elements-2.txt) 2013-05-20 RCHGetElementNumber Obsoleted StockCharts P&F elements #1513-#1515 because they no longer offer a text P&F chart (smf-elements-9.txt) 2013-06-03 smfGetOptionQuotes
smfGetYahooOptionQuote Allow mini-options to be retrieved from Yahoo by appending a "7" to the end of the ticker symbol (i.e. AAPL7) 2013-06-19 RCHGetElementNumber Changed element #13862, MSN -- Company Name, on smf-elements-1.txt 2013-06-28 smfGetOptionQuotes
smfGetYahooOptionQuote Expanded mini-option processing to include special adjusted options as well (i.e. numeric right-side character on ticker symbol) 2013-07-06 RCHGetElementNumber Fixed ETFScreen element definitions in file smf-element-9.txt (changed http://etfscreen.com to http://www.etfscreen.com) 2013-09-29 RCHGetElementNumber Changed elements #253-#352, on smf-elements-1.txt, because MSN changed the sort order on a few of the 10-year summary tables. 2013-10-16 smfGetAdvFNElement Changed this routine to use the new AdvFN web site structure 2013-10-17 smfGetAdvFNElement Additional fix of above change 2013-11-04 RCHGetElementNumber Updated almost every Zacks element definition, on smf-elements-6.txt, because Zacks rearranged the content of their web pages. Also added several new elements (Beta, Next Earnings Date, etc). 2013-11-04 RCHGetElementNumber Standardized the format of all obsolete element definitions 2013-11-04 RCHGetElementNumber Changed all "evaluate"; element defintions to regular formulas 2013-11-05 RCHGetElementNumber General updates based on review of all element definitions 2013-11-05 RCHGetElementNumber Replaced the earnings.com earnings announcement element definitions, #1286 thru #1400, with element definitions from Zacks. 2013-11-06 RCHGetElementNumber Reinstated obsolete elements #13931-#14057, on smf-elements-4.txt, to pull valuation ratios from static MorningStar web pages. 2013-11-06 RCHGetElementNumber Reinstated obsolete elements #14058-#14343, on smf-elements-4.txt, to pull key ratios from static MorningStar web pages. 2013-11-14 RCHGetElementNumber Update/added some MorningStar "Take" and "Profile" element definitions, #877-#891, to smf-elements-4.txt. 2013-12-21 smfGetMSNOptionQuote
smfGetYahooOptionQuote
Fixed so that "Put" and "Call" strings can be passed as parameters instead of just "P" and "C". 2014-01-14 RCHGetYahooQuotes Use "XXXXXX" as a placeholder symbol where spaces are found in the passed ticker array 2014-01-30 RCHGetElementNumber Update element #884 on smf-elements-4.txt. MorningStar moved the item to a different web page.

Thu Jan 30, 2014 11:28 am (PST) . Posted by:

mikemcq802

I think 884 might still be a problem. I can't see any results - only "error". Examples for IBM:


ibm
884 Error
885 None found
886 $ 208.00
887 $ 145.60
888 $ 280.80
889 Medium
890 Wide
891 Exemplary



For what it's worth, the sample in Jacob's post didn't work for me either.


It's no surprise why it couldn't find it - if I look at the html source in FF for that url I can't find those search terms - or the credit rating value. I only found a region for a dynamic table. I'm a M* subscriber and am logged in - not sure if that matters as to what's returned.

Thu Jan 30, 2014 11:45 am (PST) . Posted by:

"R. C. Gonzalez" rcglezglez

Randy:

How do I get to the file--I'm looking at the following page in the
group site:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25233

Bit I don't see any download links for the updated zip archive.

Thanks,

Ralph

At 01:35 PM 1/30/2014, you wrote:

>I just uploaded a new official version of the add-in ZIP archive. It
>contains all the latest element definitions, as well as several new
>functions and fixed functions. I had been holding off on a new
>official release, waiting until AdvFN changed over to the new "Beta"
>format. But, given AdvFN's change process, that may take a while...
>
>
>Changes made to the add-in since the 2012-12-29 release:
>
>
>
>Date Routine Description
>2013-01-04 smfGetMSNOptionQuote
>smfGetOXOptionQuote
>smfGetYahooOptionQuote
>Remove day 1, 30, and 31 assumptions for option expiration date
>(because weekly options can fall on those days)
>2013-01-06 smfGetOptionExpirations Modified Yahoo processing to pick
>up expiration dates from Yahoo's API feed (a complete list)
>2013-02-06 smfGetOptionQuotes
>smfGetYahooOptionQuotes Add workaround to fix the Yahoo "^VIX" / "VIX"
>issue
>2013-03-02 smfGetBigChartsOptionQuote New function to get current and
>historical option quotes from bigcharts.com
>2013-03-13 RCHGetElementNumber Fixed definition of element #13873 to
>pick up 2-word recommendations (i.e. "Strong Buy")
>2013-03-16 RCHGetElementNumber Fixed elements #13774-13776, "5-yr avg
>effective tax rates" from Reuters
>2013-05-20 RCHGetElementNumber Changed element #1285 (Yahoo next
>earnings date) to pick the date up from Yahoo's main quote page
>(smf-elements-2.txt)
>2013-05-20 RCHGetElementNumber Obsoleted StockCharts P&F elements
>#1513-#1515 because they no longer offer a text P&F chart
>(smf-elements-9.txt)
>2013-06-03 smfGetOptionQuotes
>smfGetYahooOptionQuote Allow mini-options to be retrieved from Yahoo
>by appending a "7" to the end of the ticker symbol (i.e. AAPL7)
>2013-06-19 RCHGetElementNumber Changed element #13862, MSN -- Company
>Name, on smf-elements-1.txt
>2013-06-28 smfGetOptionQuotes
>smfGetYahooOptionQuote Expanded mini-option processing to include
>special adjusted options as well (i.e. numeric right-side character on
>ticker symbol)
>2013-07-06 RCHGetElementNumber Fixed ETFScreen element definitions in
>file smf-element-9.txt (changed http://etfscreen.com to
>http://www.etfscreen.com)
>2013-09-29 RCHGetElementNumber Changed elements #253-#352, on
>smf-elements-1.txt, because MSN changed the sort order on a few of the
>10-year summary tables.
>2013-10-16 smfGetAdvFNElement Changed this routine to use the new
>AdvFN web site structure
>2013-10-17 smfGetAdvFNElement Additional fix of above change
>2013-11-04 RCHGetElementNumber Updated almost every Zacks element
>definition, on smf-elements-6.txt, because Zacks rearranged the
>content of their web pages. Also added several new elements (Beta,
>Next Earnings Date, etc).
>2013-11-04 RCHGetElementNumber Standardized the format of all obsolete
>element definitions
>2013-11-04 RCHGetElementNumber Changed all "evaluate"; element
>defintions to regular formulas
>2013-11-05 RCHGetElementNumber General updates based on review of all
>element definitions
>2013-11-05 RCHGetElementNumber Replaced the earnings.com earnings
>announcement element definitions, #1286 thru #1400, with element
>definitions from Zacks.
>2013-11-06 RCHGetElementNumber Reinstated obsolete elements
>#13931-#14057, on smf-elements-4.txt, to pull valuation ratios from
>static MorningStar web pages.
>2013-11-06 RCHGetElementNumber Reinstated obsolete elements
>#14058-#14343, on smf-elements-4.txt, to pull key ratios from static
>MorningStar web pages.
>2013-11-14 RCHGetElementNumber Update/added some MorningStar "Take"
>and "Profile" element definitions, #877-#891, to smf-elements-4.txt.
>2013-12-21 smfGetMSNOptionQuote
>smfGetYahooOptionQuote
>Fixed so that "Put" and "Call" strings can be passed as parameters
>instead of just "P" and "C".
>2014-01-14 RCHGetYahooQuotes Use "XXXXXX" as a placeholder symbol
>where spaces are found in the passed ticker array
>2014-01-30 RCHGetElementNumber Update element #884 on
>smf-elements-4.txt. MorningStar moved the item to a different web page.
>
>
>

Thu Jan 30, 2014 11:56 am (PST) . Posted by:

"Randy Harmelink" rharmelink

This works fine for me, and returns "AA-":

=RCHGetElementNumber("IBM";,884)

IBM is one of those I tried before and after changing the element
definition. It would actually pull the element from this web page:

http://bond.morningstar.com/internal/company.aspx?cID=0C00000BDL&country=USA&ticker=IBM

No log in necessary.

You did exit EXCEL before updating all the files, right?

Hmmm. But if the sample didn't work for you, it could be some other issue.
Which of these fail:

=smfGetTagContent("
http://quicktake.morningstar.com/stocknet/bonds.aspx?symbol=IBM
","td",-1,"iframe")

=smfstrExtr(smfGetTagContent("
http://quicktake.morningstar.com/stocknet/bonds.aspx?symbol=IBM
","td",-1,"iframe"),"src=""","""")

=RCHGetTableCell(smfstrExtr(smfGetTagContent("
http://quicktake.morningstar.com/stocknet/bonds.aspx?symbol=IBM","td",-1,"iframe"),"src=""",""""),1,"Credit
Rating","<tr")

On Thu, Jan 30, 2014 at 12:28 PM, <mikemcq802@yahoo.com> wrote:

> I think 884 might still be a problem. I can't see any results - only
> "error". Examples for IBM:
>
> ibm
> 884 Error
> 885 None found
> 886 $ 208.00
> 887 $ 145.60
> 888 $ 280.80
> 889 Medium
> 890 Wide
> 891 Exemplary
>
> For what it's worth, the sample in Jacob's post didn't work for me either.
>
> It's no surprise why it couldn't find it - if I look at the html source in
> FF for that url I can't find those search terms - or the credit rating
> value. I only found a region for a dynamic table. I'm a M* subscriber and
> am logged in - not sure if that matters as to what's returned.
>

Thu Jan 30, 2014 11:57 am (PST) . Posted by:

"Randy Harmelink" rharmelink

The official release is always in the "Add In Files" folder in the FILES
area of the group...

On Thu, Jan 30, 2014 at 11:54 AM, R. C. Gonzalez <rcg@utk.edu> wrote:

>
> Randy:
>
> How do I get to the file--I'm looking at the following page in the group
> site:
>
> https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25233
>
> Bit I don't see any download links for the updated zip archive.
>
> Thanks,
>
> Ralph
>

Thu Jan 30, 2014 12:53 pm (PST) . Posted by:

mikemcq802

All 3 of those functions fail. The first one has "Error" that is blue and underlined. The second gives a blank cell. The third gives "Error" in plain black text.

If I click the url in your message I see the M* page just fine. But, view source with FF doesn't have any "AA-" string in it anywhere (IBM's credit rating).


I have a sheet using a large variety of lookups across various data sources - including M*. The only thing I notice being a problem is this one. For M*, the high level domains that work are quotes.morningstar and financials.morningstar.

Thu Jan 30, 2014 1:04 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Hmmm. For me, the "MorningStar Credit Rating" is the very first thing shown
on the web page:

http://bond.morningstar.com/internal/company.aspx?cID=0C00000BDL&country=USA&ticker=IBM

But if you aren't seeing it, that would explain why the functions are
failing.

Do you see it on any other web page?

On Thu, Jan 30, 2014 at 1:53 PM, <mikemcq802@yahoo.com> wrote:

>
> All 3 of those functions fail. The first one has "Error" that is blue and
> underlined. The second gives a blank cell. The third gives "Error" in
> plain black text.
>
> If I click the url in your message I see the M* page just fine. But, view
> source with FF doesn't have any "AA-" string in it anywhere (IBM's credit
> rating).
>
> I have a sheet using a large variety of lookups across various data
> sources - including M*. The only thing I notice being a problem is this
> one. For M*, the high level domains that work are quotes.morningstar and
> financials.morningstar.
>

Thu Jan 30, 2014 1:14 pm (PST) . Posted by:

mikemcq802

I see AA- in the html for the bond.morningstar ... page. And, I have no doubt a properly constructed smfGetTag would find it. (Although I didn't try).

But, I don't see any such text in the html for the quicktake.morningstar page used in element 884.


I can see the AA- when the quicktake page is rendered by a browser (IE and FF). Just not in the html source using FF or, presumably, when using SMF (since it doesn't return a value).

Thu Jan 30, 2014 1:24 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

That's because the quicktake.morningstar page is using an IFRAME HTML tag.
*It* contains the credit rating. So it's actually on a different web page.
That's why the formula has to first find the IFRAME and extract it's URL.
So that the RCHGetTableCell() function can go to *that* URL and get the
credit rating.

But you're apparently not finding that IFRAME either...

What do you get with:

=RCHGetWebData("
http://quicktake.morningstar.com/stocknet/bonds.aspx?symbol=IBM
","iframe",300,-5)

I get:

<td><iframe name="showframe" align="right" height="1310" width="955"
frameborder="0" style="margin:0;" scrolling="no" src="
http://bond.morningstar.com/internal/company.aspx?cID=0C00000BDL&country=USA&ticker=IBM
"></iframe></td>...

That is where I am getting the bond.morningstar URL, which contains the
credit rating. At least for me.

On Thu, Jan 30, 2014 at 2:14 PM, <mikemcq802@yahoo.com> wrote:

>
> I see AA- in the html for the bond.morningstar ... page. And, I have no
> doubt a properly constructed smfGetTag would find it. (Although I didn't
> try).
>
> But, I don't see any such text in the html for the quicktake.morningstar
> page used in element 884.
>
> I can see the AA- when the quicktake page is rendered by a browser (IE and
> FF). Just not in the html source using FF or, presumably, when using SMF
> (since it doesn't return a value).
>

Thu Jan 30, 2014 1:50 pm (PST) . Posted by:

mikemcq802

Ah, I understand the two-level extraction now.

Even better, I resolved the problem. I thought since I used IE to view those pages that the security credentials for M* would be fine in Excel. Alas, this is not so.

I used Excel Web Query, got a "Security Alert" window indicating the security certificate is invalid or the name does not match.

Once I proceeded with that then SMF retrieved the data as you described.

I have no idea why the other M* elements (e.g., 885-891) worked but 884 did not. I noticed they are not 2-level extractions - just singles. Could this be the difference?

If this is known issue - sorry.

Thu Jan 30, 2014 2:02 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I just redefined #884 because it used to be on the "Take" page. I was
informed it no longer was. But we had worked out a formula to retrieve from
the "Bond" page a month or so ago.

So, rather than obsolete the element, I updated it to get it from the
"Bond" page. Which required the 2-level extraction. Maybe I should have
obsoleted it. It's EXTREMELY slow. :)

On Thu, Jan 30, 2014 at 2:50 PM, <mikemcq802@yahoo.com> wrote:

>
> I have no idea why the other M* elements (e.g., 885-891) worked but 884
> did not. I noticed they are not 2-level extractions - just singles. Could
> this be the difference?
>
> If this is known issue - sorry.
>

Thu Jan 30, 2014 12:42 pm (PST) . Posted by:

dmatu2000

I know this might not be possible with our currently resources (ie. free data) but does anyone know of a way to retrieve subsidiary financial data? Just us an example, if I use the smf-addin to pull the consolidated financials for Google, could I customize it so that it will also pull the financials for the 30+ Google subsidiaries?

Thu Jan 30, 2014 12:53 pm (PST) . Posted by:

mikemcq802

If Google provides it in their SEC financial reporting you might be able to get it from here:

http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0001288776&owner=exclude&count=40&hidefilings=0

Here's the link for their latest 10K filings:
http://www.sec.gov/Archives/edgar/data/1288776/000119312513028362/0001193125-13-028362-index.htm

There are products to auto-parse the XBRL companies submit to the SEC, like Randy does with websites (html). But, some of the above could be retrieved using SMF I should think.
Diposting oleh EXCEL FEED LEARNING di 14.03

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)