Kamis, 10 Juli 2014

[smf_addin] Digest Number 3115

10 Messages

Digest #3115
1b
Re: NEWBIE Question by "andrei radulescu-banu" iubica2
1c
Re: NEWBIE Question by "Randy Harmelink" rharmelink
1d
Re: NEWBIE Question by "Randy Harmelink" rharmelink
1f
Re: NEWBIE Question by "Randy Harmelink" rharmelink
2b
Re: Problem with Vanguard Corporate Bond ETFs by "Randy Harmelink" rharmelink
2d
Re: Problem with Vanguard Corporate Bond ETFs by "Randy Harmelink" rharmelink

Messages

Wed Jul 9, 2014 3:58 am (PDT) . Posted by:

body{font-size:10pt;font-family:arial,sans-serif;background-color\
:#ffffff;color:black;}p{margin:0px;}Good morning Randy.
I'll do my best to put together Data Elements for the Morningstar
fields I want. I'll try to follow the formula in your message to
get to the specific fields:
=RCHGetTableCell("http://cef.morningstar.com/cef-header?&t=~
<http://cef.morningstar.com/cef-header?&t=HYF>
~~~~",0,"id=""last-act-nav""")
Questions:
(1) That formula works for CEFs. It seems that Morningstar uses
diferent URL for ETFs. Rather than
http://cef.morningstar.com/cef-header?&t=
<http://cef.morningstar.com/cef-header?&t=HYF> , it uses
http://etfs.morningstar.com/quote?t=. That leads to different
screen displays and labeling scheme, then I assume that would
also lead to different cell names. So, where to find their
actual names?
(2) The screen labels of the fields I want are: ETFs: NAV, 12
Month Yield, Expenses, Category, Name, Star Rating; For CEFs the
equivalent labels on the screen are: Last Actual NAV, Total
Distribution Rate, Total Expense Ratio Adjusted, Morningstar
Category, Name, Star Rating (the last two I am just guessing
because there are no label for them on the screen)

One last comment. I did input the formulas you suggested in my
spreadsheet. Although that worked for NAV, I noticed that
performance suffered when compared to GetYahooQuotes (in an
array). I take that is the result of going to the Morningstar
site for every single ticker. (By concatenating Xs to the ticker
(e.g. XhyfX) for some issues and ^-V (^hyg-V) for others we can
get NAV from Yahoo as L1 except for IFGL, IBB, VNGI, and QQQ
(have no ideia why is that)
Best regards,
Armando


-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Jul 8, 2014 12:40 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] NEWBIE Question



On Tue, Jul 8, 2014 at 4:01 AM, armando@pipeline.com
<mailto:armando@pipeline.com> [smf_addin]
<smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> >
wrote:

Here are the results of my testing following your instructions:
(1) NAV
(a) smfConvertData:
It did work, but it seems that the command requires hard coding
the Ticker into the command line. I tried entering a cell rather
than the ticker which result in Error.
You just need to use plain old EXCEL string concatenation:

=smfConvertData(smfGetTagContent("http://quotes.morningstar.com/f\
und/c-header?&t=
<http://quotes.morningstar.com/fund/c-header?&t=> "&C5, "span",
0, "vkey=""NAV"">"))

(b) smfGetTableCell
Same as in (a).
Same solution -- plain old EXCEL string concatenation:

=RCHGetTableCell("http://finance.yahoo.com/q?s=
<http://finance.yahoo.com/q?s=> "&C5,1,">NAV")
In addition, it produced Error when I entered HYF as the
Ticker.
Those data items are extracted from mutual fund web pages. I
don't see NAV on Yahoo for HYF, but from MorningStar:

=RCHGetTableCell("http://cef.morningstar.com/cef-header?&t=HYF
<http://cef.morningstar.com/cef-header?&t=HYF>
",0,"id=""last-act-nav""")

...or, with the ticker in a cell:

=RCHGetTableCell("http://cef.morningstar.com/cef-header?&t=
<http://cef.morningstar.com/cef-header?&t=>
"&C8,0,"id=""last-act-nav""")


Is there a way to enter a cell or a range of cells in either (a)
or (b)? Or is there a different way to do it?
Not for a range of cells, but plain old EXCEL string
concatenation gets a single ticker from a cell.
(2) Data Elements producing Error: 979, 5079, 5059 and 5064
Here is the command I've used: =RCHGetElementNumber(+C3, 979)
It results in Error for all those Data Elements and for both CEFs
and ETFs. I've tried for HYF, REM, VNQI, JPS.
It did work for your example "VFINX" though. So I suspect those
Data Elements are Mutual Funds not for ETFs or CEFs.
Am I right? If so, how to do it? I am not interested in Mutual
Funds, but in CEFs and ETFs.

In general, element 979 is only for stocks (as per
RCHGetElementNumber-Element-Definitions.xls), but it depends on
what Yahoo is displaying on their quotes page. The other three
elements come from Yahoo mutual fund web pages, although they
sometimes do display the data for ETFs.

I haven't defined any elements specifically for CEFs, so if
that's what you're interested in, you'll need to write up your
own extractions using RCHGetTableCell() and smfGetTagContent().


Wed Jul 9, 2014 8:08 am (PDT) . Posted by:

"andrei radulescu-banu" iubica2

> It seems that Morningstar uses diferent URL for ETFs.

This has caused me quite a bit of inconvenience. Morningstar has different
URLs for stocks, funds, and ETFs. For now, I maintain a separate column in
the spreadsheet with a hand-edited security type, and I'm using the Excel
IF() function to dispatch based on that and reference the right URL.

But I wonder if there's a programmatic way to solve this, simply based on
ticker name. Is there any web resource available where one can enter the
ticker name and it reports the type of security?

Thanks,
Andrei

Wed Jul 9, 2014 9:18 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Wed, Jul 9, 2014 at 3:58 AM, armando@pipeline.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Questions:
>
> (1) That formula works for CEFs. It seems that Morningstar uses diferent
> URL for ETFs. Rather than http://cef.morningstar.com/cef-header?&t=
> <http://cef.morningstar.com/cef-header?&t=HYF>, it uses
> http://etfs.morningstar.com/quote?t=. That leads to different screen
> displays and labeling scheme, then I assume that would also lead to
> different cell names. So, where to find their actual names?
>

I'm not quite sure what you're asking, but I'll give it a go...

When you're going to a MorningStar web page like:

http://cef.morningstar.com/quote?t=hyf

...it's a dynamically created web page. That means there may be very little
the add-in can actually scrape from the web page, because the data isn't
physically within the source code of the web page. However, by examining
the JavaScript code, or by using some type of URL sniffer, the web pages
used to construct that dynamic web page can be uncovered.

That's where I got:

http://cef.morningstar.com/cef-header?&t=HYF

But that dynamic web page above has nearly a dozen other "static" web page
used to create that dynamic web page. For example:

http://cef.morningstar.com/cef-key-statics?&t=HYF
http://cef.morningstar.com/cef-valuation?&t=HYF

...and more.

> (2) The screen labels of the fields I want are: ETFs: NAV, 12 Month Yield,
> Expenses, Category, Name, Star Rating; For CEFs the equivalent labels on
> the screen are: Last Actual NAV, Total Distribution Rate, Total Expense
> Ratio Adjusted, Morningstar Category, Name, Star Rating (the last two I am
> just guessing because there are no label for them on the screen)
>

That's not a question? In any case, screen labels won't be nearly as
important as how things are set up within the source code of the web page.
That's what the basis of the data extraction needs to be based on.

One last comment. I did input the formulas you suggested in my
> spreadsheet. Although that worked for NAV, I noticed that performance
> suffered when compared to GetYahooQuotes (in an array). I take that is the
> result of going to the Morningstar site for every single ticker. (By
> concatenating Xs to the ticker (e.g. XhyfX) for some issues and ^-V
> (^hyg-V) for others we can get NAV from Yahoo as L1 except for IFGL, IBB,
> VNGI, and QQQ (have no ideia why is that)
>

Yes, the ability to grab multiple data items for multiple ticker symbols,
all in one Internet access, is what makes RCHGetYahooQuotes() run so fast
as an array-entered formula. Any scraping done by need to grab one or more
web pages per ticker symbol is going to be much slower.

I had no idea of the XHYFX ticker symbol. So you know more than I do in
that area.

Wed Jul 9, 2014 9:24 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I had such an issue with MarketWatch -- ETFs, Stocks, and Indexes had their
own URLs. What I ended up doing was checking one of them first. If it was
that, I kept the URL. Otherwise, I tested the second URL. If that worked, I
used it. Otherwise, I used the third URL.

Not conducive to fast turnaround.

On Wed, Jul 9, 2014 at 8:08 AM, andrei radulescu-banu bitdribble@... wrote:

>
> > It seems that Morningstar uses diferent URL for ETFs.
>
> This has caused me quite a bit of inconvenience. Morningstar has different
> URLs for stocks, funds, and ETFs. For now, I maintain a separate column in
> the spreadsheet with a hand-edited security type, and I'm using the Excel
> IF() function to dispatch based on that and reference the right URL.
>
> But I wonder if there's a programmatic way to solve this, simply based on
> ticker name. Is there any web resource available where one can enter the
> ticker name and it reports the type of security?
>
>

Wed Jul 9, 2014 12:04 pm (PDT) . Posted by:

body{font-size:10pt;font-family:arial,sans-serif;background-color\
:#ffffff;color:black;}p{margin:0px;}Hi Randy,
I am confused; perhaps getting more confused.... (smiles)
Let me try to clarify my confusion....
One of your answers to me, you suggested to use the following
command to get the NAV for HYF (a CEF):
=RCHGetTableCell("http://cef.morningstar.com/cef-header?&t=HYF
<http://cef.morningstar.com/cef-header?&t=HYF>
",0,"id=""last-act-nav""")
I noticed in the command above two things:
(1) the ID for the NAV cel for Morningstar CEF URL is
:last-act-nav. Perhaps I am using the wrong terminology. I use
the term "label" for the "last-act-nav". So, when I say the
other labels I mean the ids that identify the other items I am
looking for.
(2) I put the URL above in Internet Explorer and Chrome. Got a
much simpler screen than the regular Morningstar one. That
screen contains all info I needed . I figured that since you
got the label "las-act-nav" for the NAV info from that screen,
you'd know the other labels as well. Hence my question: Where do
I get the labels (IDs) for the other data I want.
I thought that since you know the NAV label, you'd know the other
labels as well..
In short, the GetTableCell you suggested is able to capture NAV
for all CEFs. Shouldn't the same command work for the other
items as well since they are right there on the screen? Dynamic
or not, the data is always there, under the same label
"last-act-nav"? Wouldn't the place you got the ID:last-act-nav
have the IDs for the other items as well?
Cheers,
Armando

-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Jul 9, 2014 1:18 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] NEWBIE Question



On Wed, Jul 9, 2014 at 3:58 AM, armando@pipeline.com
<mailto:armando@pipeline.com> [smf_addin]
<smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> >
wrote:

Questions:
(1) That formula works for CEFs. It seems that Morningstar uses
diferent URL for ETFs. Rather than
http://cef.morningstar.com/cef-header?&t=
<http://cef.morningstar.com/cef-header?&t=HYF> , it uses
http://etfs.morningstar.com/quote?t=
<http://etfs.morningstar.com/quote?t=> . That leads to different
screen displays and labeling scheme, then I assume that would
also lead to different cell names. So, where to find their
actual names?
I'm not quite sure what you're asking, but I'll give it a go...

When you're going to a MorningStar web page like:

http://cef.morningstar.com/quote?t=hyf
<http://cef.morningstar.com/quote?t=hyf>

...it's a dynamically created web page. That means there may be
very little the add-in can actually scrape from the web page,
because the data isn't physically within the source code of the
web page. However, by examining the JavaScript code, or by using
some type of URL sniffer, the web pages used to construct that
dynamic web page can be uncovered.

That's where I got:

http://cef.morningstar.com/cef-header?&t=HYF
<http://cef.morningstar.com/cef-header?&t=HYF>

But that dynamic web page above has nearly a dozen other "static"
web page used to create that dynamic web page. For example:

http://cef.morningstar.com/cef-key-statics?&t=HYF
<http://cef.morningstar.com/cef-key-statics?&t=HYF>
http://cef.morningstar.com/cef-valuation?&t=HYF
<http://cef.morningstar.com/cef-valuation?&t=HYF>

...and more. (2) The screen labels of the fields I want are:
ETFs: NAV, 12 Month Yield, Expenses, Category, Name, Star Rating;
For CEFs the equivalent labels on the screen are: Last Actual
NAV, Total Distribution Rate, Total Expense Ratio Adjusted,
Morningstar Category, Name, Star Rating (the last two I am just
guessing because there are no label for them on the screen)
That's not a question? In any case, screen labels won't be nearly
as important as how things are set up within the source code of
the web page. That's what the basis of the data extraction needs
to be based on.

One last comment. I did input the formulas you suggested in my
spreadsheet. Although that worked for NAV, I noticed that
performance suffered when compared to GetYahooQuotes (in an
array). I take that is the result of going to the Morningstar
site for every single ticker. (By concatenating Xs to the ticker
(e.g. XhyfX) for some issues and ^-V (^hyg-V) for others we can
get NAV from Yahoo as L1 except for IFGL, IBB, VNGI, and QQQ
(have no ideia why is that)

Yes, the ability to grab multiple data items for multiple ticker
symbols, all in one Internet access, is what makes
RCHGetYahooQuotes() run so fast as an array-entered formula. Any
scraping done by need to grab one or more web pages per ticker
symbol is going to be much slower.

I had no idea of the XHYFX ticker symbol. So you know more than I
do in that area.



Wed Jul 9, 2014 12:28 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The problem is that the add-in extracts data from the source code of a web
page. That is, what is sent. However, although what you see displayed in
your browser is based on that, it may run JavaScript in order to grab the
data from someplace else to place into the display. If it does that, the
raw data is NOT within the source code of the web page. So the add-in has
nothing to extract.

It's all about parsing the raw data of the web pages to extract the data.

In this case:

http://cef.morningstar.com/quote?t=hyf

...doesn't really have any data to extract from its source code. All of
those displayed sections on the page are retrieved with JavaScript from
another source, and the web page is built from that. The add-in doesn't
process JavaScript, so that web page with the retrieved data isn't
available to it. However, I can look at the JavaScript to find out from
where the data is being retrieved, in order to build the displayed page.
That where this comes from:

http://cef.morningstar.com/cef-header?&t=HYF

Since that web page DOES have the raw data, the add-in can extract it from
there. But, yes, it's just a data repository. It's not anything MorningStar
displays on any web page of theirs.

Now, on that data repository page above, the source code contains something
that looks like this:

<td class="data_small" id="last-act-nav">2.33</td>

That's where the last-act-nav label comes from. In order to find the other
labels, you'll need to look at the source code of the web page to see out
they have it coded. I typically use either RCHGetTableCell() or
smfGetTagContent() at that point. Note that the "labels" are just search
strings. They don't have to be "id" fields. They just need to be a set of
search strings (up to 4) that are unique enough to get you to the data you
want to extract. It all depends on how the data provider tends to code
their web pages. The "id" usage is pretty consistent amongst the
MorningStar "static" web pages.

For example, if I wanted to get the "Last Price" from that web page, I'd
look through the source code of the web page and find something like this:

<div id="lastPrice">2.11</div>

So, to extract that, I would need:

=smfConvertData(smfGetTagContent("
http://cef.morningstar.com/cef-header?&t=HYF","div",-1,"id=""lastPrice"""))

But it does require looking through the source code of the web page. What
you see rendered from the source code is not always the best way to extract
the data.

On Wed, Jul 9, 2014 at 12:04 PM, armando@pipeline.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Hi Randy,
>
> I am confused; perhaps getting more confused.... (smiles)
>
> Let me try to clarify my confusion....
>
> One of your answers to me, you suggested to use the following command to
> get the NAV for HYF (a CEF):
>
> =RCHGetTableCell("http://cef.morningstar.com/cef-header?&t=HYF
> ",0,"id=""last-act-nav""")
>
> I noticed in the command above two things:
>
> (1) the ID for the NAV cel for Morningstar CEF URL is :last-act-nav.
> Perhaps I am using the wrong terminology. I use the term "label" for the
> "last-act-nav". So, when I say the other labels I mean the ids that
> identify the other items I am looking for.
>
> (2) I put the URL above in Internet Explorer and Chrome. Got a much
> simpler screen than the regular Morningstar one. That screen contains all
> info I needed .
>
> I figured that since you got the label "las-act-nav" for the NAV info from
> that screen, you'd know the other labels as well. Hence my question:
> Where do I get the labels (IDs) for the other data I want.
>
> I thought that since you know the NAV label, you'd know the other labels
> as well..
>
> In short, the GetTableCell you suggested is able to capture NAV for all
> CEFs. Shouldn't the same command work for the other items as well since
> they are right there on the screen? Dynamic or not, the data is always
> there, under the same label "last-act-nav"? Wouldn't the place you got the
> ID:last-act-nav have the IDs for the other items as well?
>
>

Wed Jul 9, 2014 11:37 am (PDT) . Posted by:

kirkmellard



Subroutine RCHGetYahooQuotes will not return the 52wk low, 52wk high, x-date, and dividend amount for the following Vanguard ETFs:


VCLT


VCIT


VCSH







Are there any solutions to this problem? Other Vanguard ETFs return the correct values (VB, VEU, and VGK).


Wed Jul 9, 2014 12:07 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but there's nothing the add-in can do about it. It's just reporting
the data that Yahoo is sending. The data feed would need to be fixed on
Yahoo's side.

A work-around would be to grab the data individually by ticker, but that
will be a lot slower than RCHGetYahooQuotes()

On Wed, Jul 9, 2014 at 10:54 AM, kirkmellard@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> Subroutine RCHGetYahooQuotes will not return the 52wk low, 52wk high,
> x-date, and dividend amount for the following Vanguard ETFs:
>
> VCLT
>
> VCIT
>
> VCSH
>
> Are there any solutions to this problem? Other Vanguard ETFs return the
> correct values (VB, VEU, and VGK).
>

Wed Jul 9, 2014 1:13 pm (PDT) . Posted by:

kirkmellard

Randy:
Thank you for your answer. Is there a way to ask Yahoo to fix the return values?

Wed Jul 9, 2014 1:31 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On most Yahoo Finance web pages, there is a "Send Feedback" link.

Unfortunately, since the CSV file that feeds RCHGetYahooQuotes() is more or
less undocumented by Yahoo, it's tough to complain about it not working
properly. I don't know how much effort they would put into fixing something
that is undocumented and not officially part of their offerings.

Maybe the best route is to complain that is isn't on their current quotes
API:

http://query.yahooapis.com/v1/public/yql?q=select * from
yahoo.finance.quotes where symbol in ("YHOO","AAPL","VCLT","VCIT")
&diagnostics=true&env=http://datatables.org/alltables.env

I think the data comes from the same database.

On Wed, Jul 9, 2014 at 1:13 PM, kirkmellard@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Thank you for your answer. Is there a way to ask Yahoo to fix the
> return values?
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 01.20

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)