Rabu, 17 September 2014

[smf_addin] Digest Number 3182[2 Attachments]

15 Messages

Digest #3182
1b
Re: Retrieving data within an EXCEL VBA by "Randy Harmelink" rharmelink
1d
Re: Retrieving data within an EXCEL VBA by "Randy Harmelink" rharmelink
3a
Zacks rank by "Scott Hutchens" shhutchens6
3b
Re: Zacks rank by "Randy Harmelink" rharmelink

Messages

Wed Sep 17, 2014 2:46 am (PDT) . Posted by:

ronaldj309

In an EXCEL worksheet I have array entered in AN22:AQ22 this function call:
=RCHGetYahooQuotes(P24, "ohgl1") where P24 contains the TICKER of interest.



In a VBA, I have
Dim RTprices As Range
Set RTprices = Range("AN22:AQ22")
For i = tdOpen To tdClose: retPrices(TrowMax + 1, i) = RTprices.Cells(1, i - 2): Next i



This works as desired except that I want the whole of this to be in the VBA. In other words, no array entered function on the worksheet. Can someone tell me the proper syntax to use to receive the data in the VBA? e.g.
Something = RCHGetYahooQuotes(Ticker, "ohgl1")


Thanks for any help.
Ron Johnson


Wed Sep 17, 2014 3:36 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You need to use the pDim1 and pDim2 parameters in an array-entered function
when issuing it within VBA, because the function gets its dimensions from
the range the function is entered into, and a VBA invocation won't have
that range. So, try something like:

Range("AN22:AQ22") = RCHGetYahooQuotes("MMM", "ohgl1", pDim1:=1, pDim2:=5)

On Wed, Sep 17, 2014 at 2:46 AM, ronaldj309@cox.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> In an EXCEL worksheet I have array entered in AN22:AQ22 this function
> call:
>
> =RCHGetYahooQuotes(P24, "ohgl1") where P24 contains the TICKER of interest.
>
> In a VBA, I have
>
> Dim RTprices As Range
>
> Set RTprices = Range("AN22:AQ22")
>
> For i = tdOpen To tdClose: retPrices(TrowMax + 1, i) = RTprices.Cells(1, i
> - 2): Next i
>
> This works as desired except that I want the whole of this to be in the
> VBA. In other words, no array entered function on the worksheet. Can
> someone tell me the proper syntax to use to receive the data in the VBA?
> e.g.
>
> Something = RCHGetYahooQuotes(Ticker, "ohgl1")
>
>

Wed Sep 17, 2014 5:10 am (PDT) . Posted by:

ronaldj309

Randy,

Thanks. I can do what you suggest, but I wanted to find a way to get the data within the VBA without having to reference cells on the worksheet.


Ron Johnson

Wed Sep 17, 2014 5:55 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Just assign it to a variable instead of to the range.

On Wed, Sep 17, 2014 at 5:10 AM, ronaldj309@cox.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Thanks. I can do what you suggest, but I wanted to find a way to get the
> data within the VBA without having to reference cells on the worksheet.
>
>

Wed Sep 17, 2014 8:28 am (PDT) . Posted by:

ronaldj309

Randy,

Thanks again. I had tried an array of Doubles, etc. but not the simple (in hind sight) one of Variant. That worked.


Ron Johnson

Wed Sep 17, 2014 10:22 am (PDT) . Posted by:

bigzippy@bellsouth.net

Works on symbols wierd.
=IF(ISNUMBER(RCHGetTableCell("http://www.gurufocus.com/financials/"&$B8,D$4,"except for per share",">Dividends Per Share")),RCHGetTableCell("http://www.gurufocus.com/financials/"&$B8,D$4,"except for per share",">Dividends Per Share"),"") -this gives blank but should not symbol is MMM









Wed Sep 17, 2014 11:57 am (PDT) . Posted by:

bigzippy@bellsouth.net

Screenshot of my worksheet as well
Attachment(s) from
1 of 1 Photo(s)

Wed Sep 17, 2014 1:59 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Not sure what to tell you. This works fine for me:

=RCHGetTableCell("http://www.gurufocus.com/financials/"&$B8,D$4,"except for
per share",">Dividends Per Share")

...with "MMM" in cell B8 and 1 thru 16 in cell D4.

On Wed, Sep 17, 2014 at 10:22 AM, bigzippy@bellsouth.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> Works on symbols wierd.
>
> =IF(ISNUMBER(RCHGetTableCell("http://www.gurufocus.com/financials/"&$B8,D$4,"except
> for per share",">Dividends Per Share")),RCHGetTableCell("
> http://www.gurufocus.com/financials/"&$B8,D$4,"except for per
> share",">Dividends Per Share"),"") -this gives blank but should not symbol
> is MMM
>

Wed Sep 17, 2014 12:32 pm (PDT) . Posted by:

"Scott Hutchens" shhutchens6

I've been using the addin for some time and have been getting error from my zacks rank worksheet for the past couple days. I haven't changed the sheet and has worked for months. All my other links, yahoo analyst estimates ect. Work fine. So maybe they have altered their website. Any help would be greatly appreciated. Thanks

Sent from my iPad

Wed Sep 17, 2014 2:04 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Not sure what to tell you, as this worked fine for me just now:

=RCHGetElementNumber("MMM";, 848)

I also tried IBM, MCD, and INTC.

Try visiting the website with Internet Explorer and see if there is some
kind of issue. For example, a splash ad page when you first visit the
website.

On Wed, Sep 17, 2014 at 12:29 PM, Scott Hutchens shhutchens6@... wrote:

> I've been using the addin for some time and have been getting error from
> my zacks rank worksheet for the past couple days. I haven't changed the
> sheet and has worked for months. All my other links, yahoo analyst
> estimates ect. Work fine. So maybe they have altered their website. Any
> help would be greatly appreciated.
>

Wed Sep 17, 2014 1:54 pm (PDT) . Posted by:

"Mark E. Miller" me_miller

Hi all,

I'm a long-time follower and a first-time poster to the EXCEL Stock Market
Functions Add-in user group. I've learned a lot from the members of the
group and appreciate the group's expertise and willingness to help each
other.

My question is how to get the dynamic content (table data) from the
following Morningstar webpage:

http://performance.morningstar.com/fund/tax-analysis.action?t=AFIFX

For example, as of today (17-Sep-2014), this webpage shows the 1-Yr "Pretax
Return" for the ticker symbol AFIFX to be "23.78" as shown in the snippet
below:

I'm using the Microsoft Internet Explorer (version 11) to open the webpage
and the F12 Developer Tools
<http://msdn.microsoft.com/en-us/library/ie/gg589512(v=vs.85).aspx> to
inspect the elements on the webpage.

When I look through the webpage's elements (right-click the webpage and
select "Inspect element"), the table's data is visible in the F12 Developer
Tools window, including the text "23.78" as shown below:

When I search the webpage's HTML source code (right-click the webpage and
select "View source"), the search can't find the text "23.78". I think this
is because the table's content is loaded dynamically via JavaScript. I
can't find the link to webpage that contains the table's data.

I know there has been some discussions by the EXCEL Stock Market Functions
Add-in user group members about Morningstar&#39;s dynamic content, so I'm hoping
somebody can take a look at this Morningstar webpage and let me whether or
not it is possible to access the webpage's table's data.

Thanks in advance for any help I can get on this question.

Mark

"The only real failure is the failure to try. The measure of success is how
we cope with disappointment."

Wed Sep 17, 2014 2:14 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

MorningStar uses JavaScript to load the data onto the dynamic web page from
this static web page:

http://performance.morningstar.com/TaxAnalysis/fund/tax-analysis-component.action?t=AFIFX

That is:

=RCHGetTableCell("
http://performance.morningstar.com/TaxAnalysis/fund/tax-analysis-component.action?t=AFIFX",6,"PreTax
Return","<tr","<tr")

On Wed, Sep 17, 2014 at 9:46 AM, 'Mark E. Miller' miller.mark.e@... wrote:

>
> I'm a long-time follower and a first-time poster to the EXCEL Stock Market
> Functions Add-in user group. I've learned a lot from the members of the
> group and appreciate the group's expertise and willingness to help each
> other.
>
>
>
> My question is how to get the dynamic content (table data) from the
> following Morningstar webpage:
>
>
>
> http://performance.morningstar.com/fund/tax-analysis.action?t=AFIFX
>

Wed Sep 17, 2014 6:59 pm (PDT) . Posted by:

me_miller

Randy,


Thank you for taking a look and clearing up my confusion. I missed the "/TaxAnalysis/" part of the URL!


Mark

Wed Sep 17, 2014 2:22 pm (PDT) . Posted by:

sachapouget

Hi,


since today, the smfPricesByDates does not work anymore. I have no idea where this comes from, as it was working yesterday... Do you have any problem with the smfPricesByDates from your side ?


May thanks


Regards,
sacha
Attachment(s) from
1 of 1 Photo(s)

Wed Sep 17, 2014 2:48 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Column C in your attachment should contain dates. Instead, I see #VALUE
errors. You'll need to determine why those have value errors. They aren't
part of the add-in.

On Wed, Sep 17, 2014 at 2:19 PM, sachapouget@... wrote:

> since today, the smfPricesByDates does not work anymore. I have no idea
> where this comes from, as it was working yesterday... Do you have any
> problem with the smfPricesByDates from your side ?
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 18.59

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)