[smf_addin] Digest Number 1879

EXCEL Stock Market Functions Add-in

Messages In This Digest (17 Messages)

1a.

Re: Extracting a Graph from a Web Site From: GaryFera

2a.

GetHTMLTable #VALUE! error From: davidmaartens

2b.

Re: GetHTMLTable #VALUE! error From: Randy Harmelink

2c.

Re: GetHTMLTable #VALUE! error From: davidmaartens

2d.

Re: GetHTMLTable #VALUE! error From: Randy Harmelink

3a.

Web site changes From: McGinnis

3b.

Re: Web site changes From: Randy Harmelink

4a.

Re: Yahoo History From: ROBERT GAUER

4b.

Re: Yahoo History From: Randy Harmelink

5a.

1-Year Z-Statistic From: KailuaKid

5b.

Re: 1-Year Z-Statistic From: Randy Harmelink

5c.

Re: 1-Year Z-Statistic From: KailuaKid

5d.

Re: 1-Year Z-Statistic From: Randy Harmelink

5e.

Re: 1-Year Z-Statistic From: KailuaKid

5f.

Re: 1-Year Z-Statistic From: Randy Harmelink

5g.

Re: 1-Year Z-Statistic From: KailuaKid

6.

Data Sources Question From: jeromieweatherburn

View All Topics | Create New Topic

Messages

1a.

Re: Extracting a Graph from a Web Site

Posted by: "GaryFera" gfera@shaw.ca   GaryFera

Wed Jul 6, 2011 4:19 am (PDT)

Randy, thanks for clarifying. I'll find another way to get what I need.

Gary

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Sorry, but the image URL is far too long -- EXCEL has a 99-byte limitation.
>
> On Tue, Jul 5, 2011 at 12:14 PM, GaryFera <gfera@...> wrote:
>
> > Randy, the SMF Add In has been working extremely well for me recently and
> > surely has settled down after all my initial questions. Great facility.
> >
> > Do you know if there is a way to extract the graph from the web site listed
> > below? Thanks
> >
> > Gary
> >
> > http://www.marketwatch.com/investing/stock/AAPL/option/AAPLA21123300000
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)

2a.

GetHTMLTable #VALUE! error

Posted by: "davidmaartens" davidmaartens@vodamail.co.za   davidmaartens

Wed Jul 6, 2011 9:06 am (PDT)

Hi Randy

Please can you help with getting the table in the following link:

http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker=NPN:SJ&dataset=incomeStatement&period=A&currency=native

Thanks and regards

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

2b.

Re: GetHTMLTable #VALUE! error

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Jul 6, 2011 10:35 am (PDT)

This works fine for me:

=RCHGetHTMLTable("
http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker=NPN:SJ&dataset=incomeStatement&period=A&currency=native","Currency
in",-1,"",1)

On Wed, Jul 6, 2011 at 9:05 AM, davidmaartens
<davidmaartens@vodamail.co.za>wrote:

>
> Please can you help with getting the table in the following link:
>
>
> http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker=NPN:SJ&dataset=incomeStatement&period=A&currency=native
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

2c.

Re: GetHTMLTable #VALUE! error

Posted by: "davidmaartens" davidmaartens@vodamail.co.za   davidmaartens

Thu Jul 7, 2011 12:49 am (PDT)

Thanks - I get it to update now. However, what would the syntax be if I want to link the ticker to a cell and not have to re-type with every change.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> This works fine for me:
>
> =RCHGetHTMLTable("
> http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker=NPN:SJ&dataset=incomeStatement&period=A&currency=native","Currency
> in",-1,"",1)
>
> On Wed, Jul 6, 2011 at 9:05 AM, davidmaartens
> <davidmaartens@...>wrote:
>
> >
> > Please can you help with getting the table in the following link:
> >
> >
> > http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker=NPN:SJ&dataset=incomeStatement&period=A&currency=native
> >
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

2d.

Re: GetHTMLTable #VALUE! error

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jul 7, 2011 1:45 am (PDT)

Just do normal EXCEL string concatenation when creating the URL:

="...string1..."&A1&"...string2..."

On Thu, Jul 7, 2011 at 12:45 AM, davidmaartens <davidmaartens@vodamail.co.za
> wrote:

> Thanks - I get it to update now. However, what would the syntax be if I
> want to link the ticker to a cell and not have to re-type with every change.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)

3a.

Web site changes

Posted by: "McGinnis" mrpatmcginnis@yahoo.com   mrpatmcginnis

Wed Jul 6, 2011 11:02 am (PDT)

Randy,
Under Files - uploads by forum members - Competition.xlsx
This program is no longer working. looking at a cell reference of RCHGetTableCell(http://biz.yahoo.com/p/"Industry & mktd.html" I go to the website but I am not sure how to find where the data has moved to so I can edit the file to get it working again. Any help will be appreciated. Thank you.
Pat

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

3b.

Re: Web site changes

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Jul 6, 2011 11:15 am (PDT)

Well, to be fair, the workbook is working. It's just that Yahoo hasn't been
populating their industry browser with companies within an industry group...
:)

As an alternative, check out this thread on extracting competitor data from
Google:

http://finance.groups.yahoo.com/group/smf_addin/message/14738

On Wed, Jul 6, 2011 at 10:48 AM, McGinnis <mrpatmcginnis@yahoo.com> wrote:

> Under Files - uploads by forum members - Competition.xlsx
> This program is no longer working. looking at a cell reference of
> RCHGetTableCell(http://biz.yahoo.com/p/"Industry & mktd.html" I go to the
> website but I am not sure how to find where the data has moved to so I can
> edit the file to get it working again. Any help will be appreciated. Thank
> you.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

4a.

Re: Yahoo History

Posted by: "ROBERT GAUER" b_bgauer.56@verizon.net   bgauer

Wed Jul 6, 2011 12:15 pm (PDT)

I've noticed that sometimes you can't get "today"'s prices until after nine p.m. with RCHGetYahooHistory. I suppose that they aren't posted (completely?) until then?

----- Original Message -----
From: Randy Harmelink
To: smf_addin@yahoogroups.com
Sent: Tuesday, July 05, 2011 10:47 AM
Subject: Re: [smf_addin] Yahoo History

Not quite sure what you're asking, but you can specify the dates that you want retrieved using the function's parameters...

On Fri, Jul 1, 2011 at 1:57 PM, Michael Thomas <thomas91112@yahoo.com> wrote:

Use RCHGetYahooHistory to retrieve end-of-month stock market prices. Noticed yesterday, June 30 (after market close), that while Yahoo posted the closing value for that day in its Historical Prices, RCHGetYahooHistory was retrieving the value of two days earlier for all securities. Now today, it is doing just fine. Is there a way to get the EOM values before the first day of the following month through GetYahooHistory which otherwise works just fine?

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)

4b.

Re: Yahoo History

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Jul 6, 2011 12:21 pm (PDT)

Correct. You can only get what Yahoo has available.

On Wed, Jul 6, 2011 at 7:14 AM, ROBERT GAUER <b_bgauer.56@verizon.net>wrote:

> **
>
> I've noticed that sometimes you can't get "today"'s prices until after nine
> p.m. with RCHGetYahooHistory. I suppose that they aren't posted
> (completely?) until then?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)

5a.

1-Year Z-Statistic

Posted by: "KailuaKid" Gary.Hartling@gmail.com   garyhartling

Wed Jul 6, 2011 3:46 pm (PDT)

Randy,

What formula would I use to get the "1-Year Z-Statistic" for the Closed-End Fund MMU on Morningstar?

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

Thx,
Gary

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

5b.

Re: 1-Year Z-Statistic

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Jul 6, 2011 5:00 pm (PDT)

This appears to work:

=RCHGetTableCell("http://cef.morningstar.com/quote?t=MMU
",4,"Z-Statistic",,,,1)

On Wed, Jul 6, 2011 at 3:30 PM, KailuaKid <Gary.Hartling@gmail.com> wrote:

>
> What formula would I use to get the "1-Year Z-Statistic" for the Closed-End
> Fund MMU on Morningstar?
>
> http://cef.morningstar.com/quote?t=MMU
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

5c.

Re: 1-Year Z-Statistic

Posted by: "KailuaKid" Gary.Hartling@gmail.com   garyhartling

Wed Jul 6, 2011 5:35 pm (PDT)

I'm getting a strange result from the formula for MMU. The website says it's -1.23, but I'm getting -0.55. But it's correct for IAF, which is 1.01. Any idea why some work and some don't?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> This appears to work:
>
> =RCHGetTableCell("http://cef.morningstar.com/quote?t=MMU
> ",4,"Z-Statistic",,,,1)
>
> On Wed, Jul 6, 2011 at 3:30 PM, KailuaKid <Gary.Hartling@...> wrote:
>
> >
> > What formula would I use to get the "1-Year Z-Statistic" for the Closed-End
> > Fund MMU on Morningstar?
> >
> > http://cef.morningstar.com/quote?t=MMU
> >
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

5d.

Re: 1-Year Z-Statistic

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Jul 6, 2011 6:15 pm (PDT)

I get -1.23 for MMU...

But I would suspect the search string may be unreliable, since it isn't
really that unique. This may be more reliable:

=RCHGetTableCell("http://cef.morningstar.com/quote?t=MMU",4,"52-WK
Range",,,,1)

On Wed, Jul 6, 2011 at 5:35 PM, KailuaKid <Gary.Hartling@gmail.com> wrote:

>
> I'm getting a strange result from the formula for MMU. The website says
> it's -1.23, but I'm getting -0.55. But it's correct for IAF, which is 1.01.
> Any idea why some work and some don't?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

5e.

Re: 1-Year Z-Statistic

Posted by: "KailuaKid" Gary.Hartling@gmail.com   garyhartling

Wed Jul 6, 2011 6:57 pm (PDT)

I'm still getting the same wrong result with the new formula. I don't know where this -0.55 is coming from because if I do a search on the source code for that value it's not there.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I get -1.23 for MMU...
>
> But I would suspect the search string may be unreliable, since it isn't
> really that unique. This may be more reliable:
>
> =RCHGetTableCell("http://cef.morningstar.com/quote?t=MMU",4,"52-WK
> Range",,,,1)
>
> On Wed, Jul 6, 2011 at 5:35 PM, KailuaKid <Gary.Hartling@...> wrote:
>
> >
> > I'm getting a strange result from the formula for MMU. The website says
> > it's -1.23, but I'm getting -0.55. But it's correct for IAF, which is 1.01.
> > Any idea why some work and some don't?
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

5f.

Re: 1-Year Z-Statistic

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Jul 6, 2011 7:08 pm (PDT)

How volatile is the Z-Statistic? Is it possible that the add-in is
extracting data from a page it saved before the value changed?

Try running the smfForceRecalculation macro and see if that makes the value
change to -1.23 when the add-in retrieves a new copy of the web page.

On Wed, Jul 6, 2011 at 6:55 PM, KailuaKid <Gary.Hartling@gmail.com> wrote:

>
> I'm still getting the same wrong result with the new formula. I don't know
> where this -0.55 is coming from because if I do a search on the source code
> for that value it's not there.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

5g.

Re: 1-Year Z-Statistic

Posted by: "KailuaKid" Gary.Hartling@gmail.com   garyhartling

Wed Jul 6, 2011 8:29 pm (PDT)

Yes! ForceReCalc did the trick. Thanks, Randy.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> How volatile is the Z-Statistic? Is it possible that the add-in is
> extracting data from a page it saved before the value changed?
>
> Try running the smfForceRecalculation macro and see if that makes the value
> change to -1.23 when the add-in retrieves a new copy of the web page.
>
> On Wed, Jul 6, 2011 at 6:55 PM, KailuaKid <Gary.Hartling@...> wrote:
>
> >
> > I'm still getting the same wrong result with the new formula. I don't know
> > where this -0.55 is coming from because if I do a search on the source code
> > for that value it's not there.
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

6.

Data Sources Question

Posted by: "jeromieweatherburn" jeromieweatherburn@yahoo.com.au   jeromieweatherburn

Wed Jul 6, 2011 7:31 pm (PDT)

Dear Group,
As a recent user of the SMF_Addin I wanted to see if anyone had a decent data source for Australian Listed Company financial information.

Thus far I have been using the RCHGetTableCell function for US based listed companies from ADVFN (up to 17 years worth).

Unfortunately this site does not have good information for smaller Australian companies (it links back to the US share code financial data even if the basic description page is correct).

Yahoo & MSN only seem to have 3 or 4 years worth. Hence the question.

Also wouldn't mind finding out generically what sort of "cool" stuff users are doing with the Addin.

Regards
JW

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

Recent Activity

•  98
  New Members
•  2
  New Files

Visit Your Group

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

New business?

Get new customers.

List your web site

in Yahoo! Search.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web

Messages | Files | Photos | Polls

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe