Rabu, 24 Oktober 2012

[smf_addin] Digest Number 2382[1 Attachment]

5 New Messages

Digest #2382

Messages

Tue Oct 23, 2012 7:41 am (PDT) . Posted by:

"Ron Spruell" hashky



Randy -

The short version - after 2 days of trying.  I don't see how to reference the embedded hyperlink.  I don't see how to use smfGetTagContent() to get the embedded link from the cell.

The web page is generated using Java Script, so I think I am dead-in-the-water when it comes to extracting anything from the web page.

Ron Spruell

>________________________________
> From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Sunday, October 21, 2012 4:26 PM
>Subject: Re: [smf_addin] Morningstar "Library Premium" Stock Screens
>
>

>That method was in the message reference I gave you...
>
>That is, use smfGetTagContent() to get the link, and then smfStrExtr() to extract the ticker symbol from the link.
>
>
>On Sun, Oct 21, 2012 at 12:58 PM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>It turns out that M* embeds the Ticker Symbol in the Name.  If I copy
and paste into Excel, I can right click on the Name and get the URL,
e.g. for the Name
>>
>>
>>
>>American Tower Corp has
>>
>>http://library.morningstar.com/Quote/Quote.html?ticker=AMT
>>
>>embedded.
>>
>>
>>I found a macro for extracting the URL here:
>>http://www.bohack.com/2009/06/get-the-url-from-a-hyperlink-in-excel/
>>
>>
>>Once I get the URL the string extraction is easy.  (Because I read a most of your posts.)
>>
>>
>>
>>Cell P2 contains the URL
>>
>>
>>=RIGHT(P2,LEN(P2)-FIND("=",P2))
>>
>>
>>Is there something in the smf_addin that extracts the URL?
>>
>>
>>
>>
>>>________________________________
>>> From: Randy Harmelink <rharmelink@gmail.com>
>>>To: smf_addin@yahoogroups.com
>>>Sent: Thursday, October 18, 2012 7:51 PM
>>>Subject: Re: [smf_addin] Morningstar "Library Premium" Stock Screens
>>>
>>>
>>> 
>>>On a similar listing, I suggested pulling the ticker symbol from the link in the table cell with the name -- see:
>>>
>>>http://finance.groups.yahoo.com/group/smf_addin/message/8961
>>>
>
>
>
>

Tue Oct 23, 2012 10:29 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

What web page is it?

On Tue, Oct 23, 2012 at 7:38 AM, Ron Spruell <hashky@yahoo.com> wrote:

>
> The short version - after 2 days of trying. I don't see how to reference
> the embedded hyperlink. I don't see how to use smfGetTagContent() to get
> the embedded link from the cell.
>
> The web page is generated using Java Script, so I think I am
> dead-in-the-water when it comes to extracting anything from the web page.
>
>

Tue Oct 23, 2012 4:03 pm (PDT) . Posted by:

"Ron Spruell" hashky

http://library.morningstar.com/Selectors/Stock/Results.html#AnchorResult

I attached the source code.  You can see the "ticker=".  I think it is a title?

>________________________________
> From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Tuesday, October 23, 2012 12:29 PM
>Subject: Re: [smf_addin] Morningstar "Library Premium" Stock Screens
>
>

>What web page is it?
>
>
>On Tue, Oct 23, 2012 at 7:38 AM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>The short version - after 2 days of trying.  I don't see how to reference the embedded hyperlink.  I don't see how to use smfGetTagContent() to get the embedded link from the cell.
>>
>>
>>
>>The web page is generated using Java Script, so I think I am dead-in-the-water when it comes to extracting anything from the web page.
>>
>
>
>
>
Attachments with this message:
1 of 1 File(s)

Tue Oct 23, 2012 9:59 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

This works for me if I save the HTML page:

=smfStrExtr(smfGetTagContent(...URL...,"td",-1,">3M Co<"),"?ticker=","'")

...since you can base the extraction on the company names.

However, if the web page is generated, that data won't be on the web page.

On Tue, Oct 23, 2012 at 4:03 PM, Ron Spruell <hashky@yahoo.com> wrote:

> [Attachment(s) <#13a90f502fe8939a_TopText> from Ron Spruell included
> below]
>
> http://library.morningstar.com/Selectors/Stock/Results.html#AnchorResult
> I attached the source code. You can see the "ticker=". I think it is a
> title?
>

Tue Oct 23, 2012 10:51 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

General extraction formula would be something like:

=smfStrExtr(smfStrExtr(smfWord(RCHGetWebData($D$2,1),$C4,"}"),""""&G$3&"""",","),"""","""")

....where:

$D$2 = the URL to retrieve the data
$C4 = which row to extract (e.g. 1, 2, 3, ...)
G$3 = which data item to extract (e.g. e, t, l_cur, ...)

Or, to retrieve by ticker symbol:

=smfStrExtr(smfStrExtr(smfStrExtr(RCHGetWebData($D$2,1),"""t"" :
"""&$C4&"""","}"),""""&G$3&"""",","),"""","""")

...where:

$D$2 = the URL to retrieve the data
$C4 = which ticker symbol to extract
G$3 = which data item to extract (e.g. e, t, l_cur, ...)

I found this list of data items:

avvo = Average volume (float with multiplier, like '3.54M')
beta = Beta (float)
c = Amount of change while open (float)
ccol = (unknown) (chars)
cl = Last perc. change
cp = Change perc. while open (float)
e = Exchange (text, like 'NASDAQ')
ec = After hours last change from close (float)
eccol = (unknown) (chars)
ecp = After hours last chage perc. from close (float)
el = After. hours last quote (float)
el_cur = (unknown) (float)
elt = After hours last quote time (unknown)
eo = Exchange Open (0 or 1)
eps = Earnings per share (float)
fwpe = Forward PE ratio (float)
hi = Price high (float)
hi52 = 52 weeks high (float)
id = Company id (identifying number)
l = Last value while open (float)
l_cur = Last value at close (like 'l')
lo = Price low (float)
lo52 = 52 weeks low (float)
lt = Last value date/time
ltt = Last trade time (Same as "lt" without the data)
mc = Market cap. (float with multiplier, like '123.45B')
name = Company name (text)
op = Open price (float)
pe = PE ratio (float)
t = Ticker (text)
type = Type (i.e. 'Company')
vo = Volume (float with multiplier, like '3.54M')

On Tue, Oct 23, 2012 at 1:18 AM, boo1712 <boo1712@yahoo.co.uk> wrote:

> Hi guys,
>
> I discovered a new google API:
> http://www.google.com/finance/info?infotype=infoquoteall&q=BTU,JPM,AIG
>
> It has more variables such as beta, 52wk high, premarket quotes etc.
>
> However I do not know how to handle the numerous """.
>
> Previously we had a spreadsheet using another google API
> smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock="&smfJoin($C$4:$C$33,"&stock="),"<symbol
> data="""&$C4&"""",5000),"<"&E$3&" data=""","""")
>
> It is useful and fast for getting multiple quotes.
>
> Can anyone help to provide the formula to the new API with more variables?
>

Tidak ada komentar:

Posting Komentar