[smf_addin] Digest Number 1856

EXCEL Stock Market Functions Add-in

Messages In This Digest (25 Messages)

1a.

List of ADRs & their countries From: malramsay

1b.

Re: List of ADRs & their countries From: Farhan Ansari

2a.

Re: How/where do I extract 5 year forecasted EPS growth for a stock From: paul messerschmidt

2b.

How to extract competitor's ticker symbols from this link From: Au

2c.

Re: How/where do I extract 5 year forecasted EPS growth for a stock From: Randy Harmelink

2d.

Re: How to extract competitor's ticker symbols from this link From: Randy Harmelink

2e.

Re: How/where do I extract 5 year forecasted EPS growth for a stock From: Pete

2f.

Re: How to extract competitor's ticker symbols from this link From: Au

2g.

Re: How to extract competitor's ticker symbols from this link From: Randy Harmelink

2h.

Re: How to extract competitor's ticker symbols from this link From: Au

2i.

Re: How to extract competitor's ticker symbols from this link From: Au

2j.

Re: How to extract competitor's ticker symbols from this link From: Au

3a.

Re: Reuters.com last quote From: yogimodak

4a.

OPTION QUOTES From: smortonm

4b.

Re: OPTION QUOTES From: Randy Harmelink

5a.

Am unable to retrieve MSN Stockscouter values From: investor952

5b.

Re: Am unable to retrieve MSN Stockscouter values From: Randy Harmelink

5c.

Re: Am unable to retrieve MSN Stockscouter values From: investor952

5d.

Re: Am unable to retrieve MSN Stockscouter values From: Randy Harmelink

5e.

Re: Am unable to retrieve MSN Stockscouter values From: investor952

5f.

Re: Am unable to retrieve MSN Stockscouter values From: Randy Harmelink

6a.

In RCHGetTableCell, need space for <BR> From: Charley Kyd

6b.

Re: In RCHGetTableCell, need space for <BR> From: Randy Harmelink

7a.

just got run time error '28' trying to run smfForceRecalculation mac From: investor952

7b.

Re: just got run time error '28' trying to run smfForceRecalculation From: Randy Harmelink

View All Topics | Create New Topic

Messages

1a.

List of ADRs & their countries

Posted by: "malramsay" malramsay@yahoo.com   malramsay

Mon Jun 13, 2011 6:07 am (PDT)

Hi all,

I require a list of ADRs on the NYSE & NASDAQ & their HQ countries.

I can't find this information in smf_addin. I've found some places on the 'net that have this information but nowhere that is easily accessible (i.e. I'd like one large list preferably).

Can anyone point out a source?

Thanks,
Mal

Back to top

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

1b.

Re: List of ADRs & their countries

Posted by: "Farhan Ansari" farhan86a@gmail.com   farsdocs

Mon Jun 13, 2011 6:16 am (PDT)

TopForeignStocks.com (http://topforeignstocks.com/foreign-adrs-list/) can be
precisely what you are looking for

Cheers!
Farhan

On Mon, Jun 13, 2011 at 6:37 PM, malramsay <malramsay@yahoo.com> wrote:

>
>
> Hi all,
>
> I require a list of ADRs on the NYSE & NASDAQ & their HQ countries.
>
> I can't find this information in smf_addin. I've found some places on the
> 'net that have this information but nowhere that is easily accessible (i.e.
> I'd like one large list preferably).
>
> Can anyone point out a source?
>
> Thanks,
> Mal
>
>
>

Back to top

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

2a.

Re: How/where do I extract 5 year forecasted EPS growth for a stock

Posted by: "paul messerschmidt" financepaul@gmail.com   fuelspotpaul

Mon Jun 13, 2011 7:06 am (PDT)

randy - this is what i love about this group. someone is always uncovering
a site i had not yet discovered.

while i wouldn't put too much weight into a 5-year forecast, i do like the
depth of free info hosted by morningstar in an easy-to-digest format. (btw
- has anybody tracked morningstar's accuracy of this forecast?)

thanks for pointing me to this link

good trading...

On Sat, Jun 11, 2011 at 12:10 AM, Randy Harmelink <rharmelink@gmail.com>wrote:

>
>
> Try:
>
> =0+smfStrExtr(RCHGetTableCell("
> http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=MMM",0,"Five
> Year Growth Forecast:")&"|",":","|")
>
>
> On Fri, Jun 10, 2011 at 7:09 PM, investor952 <investor952@yahoo.com>wrote:
>
>> I am a newbie. I was looking at your files & links section but could not
>> find this. I did see reference to average 5 year eps growth from
>> Morningstar, but I'm looking for FORECASTED 5 year eps growth (preferably
>> from Morningstar)..but elsewhere would do. Can you point me in the right
>> direction? Thanks.
>>
>
>
>

--
-----------------------------------------
Paul Messerschmidt
paul.messerschmidt (skype)
+1-813-334-8682 (mobile)

Back to top

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

2b.

How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Mon Jun 13, 2011 8:14 am (PDT)

http://www.google.com/finance?client=ig&q=NASDAQ:PAC

Hi,
from above google finance data is it possible to extract the competitor data. ?
thanks for your help

Aku

Back to top

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

2c.

Re: How/where do I extract 5 year forecasted EPS growth for a stock

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

Mon Jun 13, 2011 9:41 am (PDT)

It's not actually MorningStar data -- they get it from FactSet, according to
their web page.

On Mon, Jun 13, 2011 at 7:06 AM, paul messerschmidt
<financepaul@gmail.com>wrote:

> while i wouldn't put too much weight into a 5-year forecast, i do like the
> depth of free info hosted by morningstar in an easy-to-digest format. (btw
> - has anybody tracked morningstar's accuracy of this forecast?)
>

Back to top

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

2d.

Re: How to extract competitor's ticker symbols from this link

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

Mon Jun 13, 2011 9:52 am (PDT)

Not easily. The data is all in a script. So this:

=RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR
","streaming:")

...gives you something like:

related:{cols:[0,1,2,37,4,30,34,31,10,11,5,14,27,7,8,21,22,33,3],rows:[{id:"665678",values:["PACR","Pacer
International,
Inc.","NASDAQ","USD","4.51","-0.05","chr","-1.10","0.10","46.39","157.80M","0.00","6.15","0.34","0.92","0.54","0.06","665678","PACR"]},{id:"658968",values:["HUBG","Hub
Group, Inc....

...so you'd need to manually extract it from that mess.

On Mon, Jun 13, 2011 at 8:14 AM, Au <uanant@ymail.com> wrote:

>
> http://www.google.com/finance?client=ig&q=NASDAQ:PAC<http://www.google.com/finance?client=ig&q=NASDAQ:PACR>
>
> from above google finance data is it possible to extract the competitor data.
> ?
>

Back to top

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

2e.

Re: How/where do I extract 5 year forecasted EPS growth for a stock

Posted by: "Pete" petekoch@yahoo.com   petekoch

Mon Jun 13, 2011 9:53 am (PDT)

You could also use the RCHGetElementNumber function: 442 for Zacks and 621 for Yahoo's estimate....

--- In smf_addin@yahoogroups.com, "investor952" <investor952@...> wrote:
>
> I am a newbie. I was looking at your files & links section but could not find this. I did see reference to average 5 year eps growth from Morningstar, but I'm looking for FORECASTED 5 year eps growth (preferably from Morningstar)..but elsewhere would do. Can you point me in the right direction? Thanks.
>

Back to top

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

2f.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Mon Jun 13, 2011 12:47 pm (PDT)

Hello Randy,
I managed to get this far from your solutions. 
streaming:[{cid:"663968",s:"RIG",e:"NYSE"},{cid:"715119",s:"TOJ",e:"FRA"},{cid:"667133",s:"PDE",e:"NYSE"},{cid:"407040",s:"NE",e:"NYSE"},{cid:"658040",s:"DO",e:"NYSE"},{cid:"666292",s:"PTEN",e:"NASDAQ"},{cid:"17024",s:"HP",e:"NYSE"},{cid:"197450",s:"ESV",e:"NYSE"},{cid:"30360",s:"RDC",e:"NYSE"},{cid:"701334",s:"HERO",e:"NASDAQ"},{cid:"711364",s:"VTG",e:"AMEX"}]

I see a repeating pattern in there, but at this point have no idea how can I narrow it down to ticker symbols. I know its more of a VB / Excel question rather than your market function question. But any help would be greatly appreciated. 
Aku

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Monday, June 13, 2011 12:52 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Not easily. The data is all in a script. So this:

=RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR","streaming:")

...gives you something like:

related:{cols:[0,1,2,37,4,30,34,31,10,11,5,14,27,7,8,21,22,33,3],rows:[{id:"665678",values:["PACR","Pacer International, Inc.","NASDAQ","USD","4.51","-0.05","chr","-1.10","0.10","46.39","157.80M","0.00","6.15","0.34","0.92","0.54","0.06","665678","PACR"]},{id:"658968",values:["HUBG","Hub Group, Inc....

...so you'd need to manually extract it from that mess.

On Mon, Jun 13, 2011 at 8:14 AM, Au <uanant@ymail.com> wrote:

>
>http://www.google.com/finance?client=ig&q=NASDAQ:PACR
>
>
>from above google finance data is it possible to extract the competitor data. ?
>

Back to top

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

2g.

Re: How to extract competitor's ticker symbols from this link

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

Mon Jun 13, 2011 2:12 pm (PDT)

Oops -- using "related:" instead of "streaming:" gets you the various data
items as well, if you want them. Yours may vary from mine, since the output
can be customized.

I do have some utility functions in the new beta version of the add-in, in
the "Works in Progress" folder of the file area, that may be of help. Using
the new smfWord() function (to extract words based on delimiters), try
something like:

=SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("
http://www.google.com/finance?client=ig&q=NASDAQ:PACR",
"related:"),row,"values:"),"[","]"),column,""","""),"""","")

...varying "row" from 2 to n and column from 1 to n. Then I get something
like:

PACR Pacer International, Inc. NASDAQ USD 4.51 -0.05 chr -1.10 0.10 46.39
157.80M 0.00 6.15 0.34 0.92 0.54 0.06 665678 PACR HUBG Hub Group, Inc.
NASDAQ USD 35.02 -0.15 chr -0.43 1.22 28.80 1.31B 0.00 0.00 7.23 11.90 3.81
2.37 658968 HUBG CHRW C.H. Robinson Worldwide, Inc. NASDAQ USD 75.92 +0.09
chg 0.12 2.41 31.46 12.57B 1.04 2.99 20.21 33.89 6.72 4.17 661019 CHRW
JBHT J.B.
Hunt Transport Services, Inc. NASDAQ USD 43.28 +0.25 chg 0.58 1.68 25.75
5.25B 0.48 33.35 10.46 32.81 9.16 5.26 275712 JBHT R Ryder System, Inc.
NYSE USD 50.68 +0.23 chg 0.46 2.62 19.31 2.61B 1.04 41.29 1.93 8.68 3.63
2.43 30515 R UTIW UTi Worldwide Inc. NASDAQ USD 19.00 -0.12 chr -0.65 0.67
28.35 1.95B 0.06 14.62 3.69 8.33 2.70 1.64 665473 UTIW VTNC Vitran Corp.,
Inc. (USA) NASDAQ USD 13.08 +0.08 chg 0.62 -2.27 213.60M 0.00 29.87 -14.10
-32.43 1.02 -5.66 662250 VTNC EXPD Expeditors Intl. of Washington NASDAQ
USD 46.70 -0.33 chr -0.70 1.73 27.00 9.92B 0.40 0.00 13.76 20.90 9.17 5.77
201264 EXPD CNW Con-way Inc. NYSE USD 36.02 -0.50 chr -1.37 0.28 128.40
2.00B 0.40 28.28 0.14 0.53 1.58 0.08 8707 CNW PACC Pacific CMA, Inc. PINK
USD 0.0060 0.0000 chb 0.00 -0.28 171,716.00 0.00 30.50 -6.46 -39.09 -0.27
-1.63 679188 PACC RRTS Roadrunner Transportation Services Hold. NYSE USD
13.48 -0.32 chr -2.32 0.16 86.48 407.12M 0.00 7.32 1.05 1.44 2.19 0.57
729385 RRTS
Break that function up into its components and you'll see what each one
does.

On Mon, Jun 13, 2011 at 12:47 PM, Au <uanant@ymail.com> wrote:

> I managed to get this far from your solutions.
>
> streaming:[{cid:"663968",s:"RIG",e:"NYSE"},{cid:"715119",s:"TOJ",e:"FRA"},{cid:"667133",s:"PDE",e:"NYSE"},{cid:"407040",s:"NE",e:"NYSE"},{cid:"658040",s:"DO",e:"NYSE"},{cid:"666292",s:"PTEN",e:"NASDAQ"},{cid:"17024",s:"HP",e:"NYSE"},{cid:"197450",s:"ESV",e:"NYSE"},{cid:"30360",s:"RDC",e:"NYSE"},{cid:"701334",s:"HERO",e:"NASDAQ"},{cid:"711364",s:"VTG",e:"AMEX"}]
>
> I see a repeating pattern in there, but at this point have no idea how can
> I narrow it down to ticker symbols. I know its more of a VB / Excel question
> rather than your market function question. But any help would be
> greatly appreciated.
> Aku
>

Back to top

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

2h.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Mon Jun 13, 2011 5:16 pm (PDT)

Randy,
I was really excited to see the results in the attached format. So I downloaded the Beta  version of the addin and pasted the formula it gave me #value 
I put in the place of Row =1 and Column = 2
Pardon my ignorance but I did not understand how to use it. 
I would appreciate your help. 
Regards
Aku

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Monday, June 13, 2011 5:12 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Oops -- using "related:" instead of "streaming:" gets you the various data items as well, if you want them. Yours may vary from mine, since the output can be customized.

I do have some utility functions in the new beta version of the add-in, in the "Works in Progress" folder of the file area, that may be of help. Using the new smfWord() function (to extract words based on delimiters), try something like:

=SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR",
"related:"),row,"values:"),"[","]"),column,""","""),"""","")

...varying "row" from 2 to n and column from 1 to n. Then I get something like:

PACR Pacer International, Inc. NASDAQ USD 4.51 -0.05 chr -1.10 0.10 46.39 157.80M 0.00 6.15 0.34 0.92 0.54 0.06 665678 PACR
HUBG Hub Group, Inc. NASDAQ USD 35.02 -0.15 chr -0.43 1.22 28.80 1.31B 0.00 0.00 7.23 11.90 3.81 2.37 658968 HUBG
CHRW C.H. Robinson Worldwide, Inc. NASDAQ USD 75.92 +0.09 chg 0.12 2.41 31.46 12.57B 1.04 2.99 20.21 33.89 6.72 4.17 661019 CHRW
JBHT J.B. Hunt Transport Services, Inc. NASDAQ USD 43.28 +0.25 chg 0.58 1.68 25.75 5.25B 0.48 33.35 10.46 32.81 9.16 5.26 275712 JBHT
R Ryder System, Inc. NYSE USD 50.68 +0.23 chg 0.46 2.62 19.31 2.61B 1.04 41.29 1.93 8.68 3.63 2.43 30515 R
UTIW UTi Worldwide Inc. NASDAQ USD 19.00 -0.12 chr -0.65 0.67 28.35 1.95B 0.06 14.62 3.69 8.33 2.70 1.64 665473 UTIW
VTNC Vitran Corp., Inc. (USA) NASDAQ USD 13.08 +0.08 chg 0.62 -2.27   213.60M 0.00 29.87 -14.10 -32.43 1.02 -5.66 662250 VTNC
EXPD Expeditors Intl. of Washington NASDAQ USD 46.70 -0.33 chr -0.70 1.73 27.00 9.92B 0.40 0.00 13.76 20.90 9.17 5.77 201264 EXPD
CNW Con-way Inc. NYSE USD 36.02 -0.50 chr -1.37 0.28 128.40 2.00B 0.40 28.28 0.14 0.53 1.58 0.08 8707 CNW
PACC Pacific CMA, Inc. PINK USD 0.0060 0.0000 chb 0.00 -0.28   171,716.00 0.00 30.50 -6.46 -39.09 -0.27 -1.63 679188 PACC
RRTS Roadrunner Transportation Services Hold. NYSE USD 13.48 -0.32 chr -2.32 0.16 86.48 407.12M 0.00 7.32 1.05 1.44 2.19 0.57 729385 RRTS
Break that function up into its components and you'll see what each one does.

On Mon, Jun 13, 2011 at 12:47 PM, Au <uanant@ymail.com> wrote:

I managed to get this far from your solutions. 
>streaming:[{cid:"663968",s:"RIG",e:"NYSE"},{cid:"715119",s:"TOJ",e:"FRA"},{cid:"667133",s:"PDE",e:"NYSE"},{cid:"407040",s:"NE",e:"NYSE"},{cid:"658040",s:"DO",e:"NYSE"},{cid:"666292",s:"PTEN",e:"NASDAQ"},{cid:"17024",s:"HP",e:"NYSE"},{cid:"197450",s:"ESV",e:"NYSE"},{cid:"30360",s:"RDC",e:"NYSE"},{cid:"701334",s:"HERO",e:"NASDAQ"},{cid:"711364",s:"VTG",e:"AMEX"}]
>
>
>
>I see a repeating pattern in there, but at this point have no idea how can I narrow it down to ticker symbols. I know its more of a VB / Excel question rather than your market function question. But any help would be greatly appreciated. 
>Aku
>

Back to top

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

2i.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Mon Jun 13, 2011 5:21 pm (PDT)

Randy,
Nevermind my panic, I tried installing the add-in again and this time it worked. 
Thanks a million for your help
Aku

________________________________
From: Au <uanant@ymail.com>
To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
Sent: Monday, June 13, 2011 8:16 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Randy,
I was really excited to see the results in the attached format. So I downloaded the Beta  version of the addin and pasted the formula it gave me #value 
I put in the place of Row =1 and Column = 2
Pardon my ignorance but I did not understand how to use it. 
I would appreciate your help. 
Regards
Aku

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Monday, June 13, 2011 5:12 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Oops -- using "related:" instead of "streaming:" gets you the various data items as well, if you want them. Yours may vary from mine, since the output can be customized.

I do have some utility functions in the new beta version of the add-in, in the "Works in Progress" folder of the file area, that may be of help. Using the new smfWord() function (to extract words based on delimiters), try something like:

=SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR",
"related:"),row,"values:"),"[","]"),column,""","""),"""","")

...varying "row" from 2 to n and column from 1 to n. Then I get something like:

PACR Pacer International, Inc. NASDAQ USD 4.51 -0.05 chr -1.10 0.10 46.39 157.80M 0.00 6.15 0.34 0.92 0.54 0.06 665678 PACR
HUBG Hub Group, Inc. NASDAQ USD 35.02 -0.15 chr -0.43 1.22 28.80 1.31B 0.00 0.00 7.23 11.90 3.81 2.37 658968 HUBG
CHRW C.H. Robinson Worldwide, Inc. NASDAQ USD 75.92 +0.09 chg 0.12 2.41 31.46 12.57B 1.04 2.99 20.21 33.89 6.72 4.17 661019 CHRW
JBHT J.B. Hunt Transport Services, Inc. NASDAQ USD 43.28 +0.25 chg 0.58 1.68 25.75 5.25B 0.48 33.35 10.46 32.81 9.16 5.26 275712 JBHT
R Ryder System, Inc. NYSE USD 50.68 +0.23 chg 0.46 2.62 19.31 2.61B 1.04 41.29 1.93 8.68 3.63 2.43 30515 R
UTIW UTi Worldwide Inc. NASDAQ USD 19.00 -0.12 chr -0.65 0.67 28.35 1.95B 0.06 14.62 3.69 8.33 2.70 1.64 665473 UTIW
VTNC Vitran Corp., Inc. (USA) NASDAQ USD 13.08 +0.08 chg 0.62 -2.27   213.60M 0.00 29.87 -14.10 -32.43 1.02 -5.66 662250 VTNC
EXPD Expeditors Intl. of Washington NASDAQ USD 46.70 -0.33 chr -0.70 1.73 27.00 9.92B 0.40 0.00 13.76 20.90 9.17 5.77 201264 EXPD
CNW Con-way Inc. NYSE USD 36.02 -0.50 chr -1.37 0.28 128.40 2.00B 0.40 28.28 0.14 0.53 1.58 0.08 8707 CNW
PACC Pacific CMA, Inc. PINK USD 0.0060 0.0000 chb 0.00 -0.28   171,716.00 0.00 30.50 -6.46 -39.09 -0.27 -1.63 679188 PACC
RRTS Roadrunner Transportation Services Hold. NYSE USD 13.48 -0.32 chr -2.32 0.16 86.48 407.12M 0.00 7.32 1.05 1.44 2.19 0.57 729385 RRTS
Break that function up into its components and you'll see what each one does.

On Mon, Jun 13, 2011 at 12:47 PM, Au <uanant@ymail.com> wrote:

I managed to get this far from your solutions. 
>streaming:[{cid:"663968",s:"RIG",e:"NYSE"},{cid:"715119",s:"TOJ",e:"FRA"},{cid:"667133",s:"PDE",e:"NYSE"},{cid:"407040",s:"NE",e:"NYSE"},{cid:"658040",s:"DO",e:"NYSE"},{cid:"666292",s:"PTEN",e:"NASDAQ"},{cid:"17024",s:"HP",e:"NYSE"},{cid:"197450",s:"ESV",e:"NYSE"},{cid:"30360",s:"RDC",e:"NYSE"},{cid:"701334",s:"HERO",e:"NASDAQ"},{cid:"711364",s:"VTG",e:"AMEX"}]
>
>
>
>I see a repeating pattern in there, but at this point have no idea how can I narrow it down to ticker symbols. I know its more of a VB / Excel question rather than your market function question. But any help would be greatly appreciated. 
>Aku
>

Back to top

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

2j.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Tue Jun 14, 2011 5:13 am (PDT)

Randy,
You suggested this following line to get the competitor's data from google finance. 
=SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR","related:"),row,"values:"),"[","]"),column,""","""),"""","")

This line worked great with excel 2007 but its not working with excel 2003, do you think there could be any problem. I downloaded the lasted add-in from work in progress as you said in your e-mail. Put Row=2 and Column = 1

Thanks for your help

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Monday, June 13, 2011 5:12 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Oops -- using "related:" instead of "streaming:" gets you the various data items as well, if you want them. Yours may vary from mine, since the output can be customized.

I do have some utility functions in the new beta version of the add-in, in the "Works in Progress" folder of the file area, that may be of help. Using the new smfWord() function (to extract words based on delimiters), try something like:

=SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR",
"related:"),row,"values:"),"[","]"),column,""","""),"""","")

...varying "row" from 2 to n and column from 1 to n. Then I get something like:

PACR Pacer International, Inc. NASDAQ USD 4.51 -0.05 chr -1.10 0.10 46.39 157.80M 0.00 6.15 0.34 0.92 0.54 0.06 665678 PACR
HUBG Hub Group, Inc. NASDAQ USD 35.02 -0.15 chr -0.43 1.22 28.80 1.31B 0.00 0.00 7.23 11.90 3.81 2.37 658968 HUBG
CHRW C.H. Robinson Worldwide, Inc. NASDAQ USD 75.92 +0.09 chg 0.12 2.41 31.46 12.57B 1.04 2.99 20.21 33.89 6.72 4.17 661019 CHRW
JBHT J.B. Hunt Transport Services, Inc. NASDAQ USD 43.28 +0.25 chg 0.58 1.68 25.75 5.25B 0.48 33.35 10.46 32.81 9.16 5.26 275712 JBHT
R Ryder System, Inc. NYSE USD 50.68 +0.23 chg 0.46 2.62 19.31 2.61B 1.04 41.29 1.93 8.68 3.63 2.43 30515 R
UTIW UTi Worldwide Inc. NASDAQ USD 19.00 -0.12 chr -0.65 0.67 28.35 1.95B 0.06 14.62 3.69 8.33 2.70 1.64 665473 UTIW
VTNC Vitran Corp., Inc. (USA) NASDAQ USD 13.08 +0.08 chg 0.62 -2.27   213.60M 0.00 29.87 -14.10 -32.43 1.02 -5.66 662250 VTNC
EXPD Expeditors Intl. of Washington NASDAQ USD 46.70 -0.33 chr -0.70 1.73 27.00 9.92B 0.40 0.00 13.76 20.90 9.17 5.77 201264 EXPD
CNW Con-way Inc. NYSE USD 36.02 -0.50 chr -1.37 0.28 128.40 2.00B 0.40 28.28 0.14 0.53 1.58 0.08 8707 CNW
PACC Pacific CMA, Inc. PINK USD 0.0060 0.0000 chb 0.00 -0.28   171,716.00 0.00 30.50 -6.46 -39.09 -0.27 -1.63 679188 PACC
RRTS Roadrunner Transportation Services Hold. NYSE USD 13.48 -0.32 chr -2.32 0.16 86.48 407.12M 0.00 7.32 1.05 1.44 2.19 0.57 729385 RRTS
Break that function up into its components and you'll see what each one does.

On Mon, Jun 13, 2011 at 12:47 PM, Au <uanant@ymail.com> wrote:

I managed to get this far from your solutions. 
>streaming:[{cid:"663968",s:"RIG",e:"NYSE"},{cid:"715119",s:"TOJ",e:"FRA"},{cid:"667133",s:"PDE",e:"NYSE"},{cid:"407040",s:"NE",e:"NYSE"},{cid:"658040",s:"DO",e:"NYSE"},{cid:"666292",s:"PTEN",e:"NASDAQ"},{cid:"17024",s:"HP",e:"NYSE"},{cid:"197450",s:"ESV",e:"NYSE"},{cid:"30360",s:"RDC",e:"NYSE"},{cid:"701334",s:"HERO",e:"NASDAQ"},{cid:"711364",s:"VTG",e:"AMEX"}]
>
>
>
>I see a repeating pattern in there, but at this point have no idea how can I narrow it down to ticker symbols. I know its more of a VB / Excel question rather than your market function question. But any help would be greatly appreciated. 
>Aku
>

Back to top

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

3a.

Re: Reuters.com last quote

Posted by: "yogimodak" ymodak@gmail.com   yogimodak

Mon Jun 13, 2011 8:49 am (PDT)

Thanks will try this.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> This appears to get the last quote:
>
> =0+smfGetTagContent("
> http://www.reuters.com/finance/stocks/overview?symbol=MMM
> ","span",2,"id=""headerQuoteContainer""")
>
> ...although I'm a little uncomfortable with the search string and not sure
> how reliable it will be over time. Also, since the data isn't presented in a
> table, things might change...
>
> I just uploaded a revised copy of the analyst workbook -- I just changed the
> search string of ">Consensus Estimates Trend" to be "Consensus Estimates
> Trend". Apparently they changed the web page.
>
> On Sun, Jun 12, 2011 at 9:19 PM, yogimodak <ymodak@...> wrote:
>
> >
> > How can I get the last quote for a stock from reuters.com? Is there a
> > simple way?
> >
> > Also the Consensus Estimate Trend and Revision Summary section of the
> > spreadsheet is not working. Any simple fix?
> >
>

Back to top

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

4a.

OPTION QUOTES

Posted by: "smortonm" smmarder@yahoo.com   smortonm

Mon Jun 13, 2011 4:57 pm (PDT)

I notice that I can now import the last price of an option from MSN by using element # 25. However there does not seem to be an element number for MSN ask or bid price as there is in Yahoo. Do you know if such an element # exists that would allow importing the bid/ask on options.
Thanks much.
Sid

Back to top

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

4b.

Re: OPTION QUOTES

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

Mon Jun 13, 2011 5:00 pm (PDT)

Use the smfGetOptionQuotes() function instead.

For stock/ETF/mutual fund quotes, you should be using the
RCHGetYahooQuotes() function.

On Mon, Jun 13, 2011 at 4:57 PM, smortonm <smmarder@yahoo.com> wrote:

> I notice that I can now import the last price of an option from MSN by
> using element # 25. However there does not seem to be an element number for
> MSN ask or bid price as there is in Yahoo. Do you know if such an element #
> exists that would allow importing the bid/ask on options.
>

Back to top

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

5a.

Am unable to retrieve MSN Stockscouter values

Posted by: "investor952" investor952@yahoo.com   investor952

Mon Jun 13, 2011 7:49 pm (PDT)

I am trying to retrieve some MSN Stockscouter values into Excel 2002 using the function =RCHGetElementNumber(a,b), where a points to the ticker and b is, say 543, the current stock scouter rating. For this, an others, the only thing that pops up is Error.

Do i need to save this file in the smf folder in the program files folder for this to work? I remember reading this somewhere. I am new using this great tool. thx.

Back to top

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

5b.

Re: Am unable to retrieve MSN Stockscouter values

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

Mon Jun 13, 2011 8:02 pm (PDT)

You probably just need the new smf-elements-1.txt element definition file
from the "Works in Progress" folder of the files area of the group. I had to
redefine a number of elements earlier this year because MSN changed some of
their web pages.

Just replace your version of the smf-elements-1.txt file in the add-in
folder with the new one and then run the smfForceRecalculation macro to
reload the element definitions. See the "Links" area of the group for info
on smfForceRecalculation.

On Mon, Jun 13, 2011 at 7:48 PM, investor952 <investor952@yahoo.com> wrote:

> I am trying to retrieve some MSN Stockscouter values into Excel 2002 using
> the function =RCHGetElementNumber(a,b), where a points to the ticker and b
> is, say 543, the current stock scouter rating. For this, an others, the only
> thing that pops up is Error.
>
> Do i need to save this file in the smf folder in the program files folder
> for this to work? I remember reading this somewhere. I am new using this
> great tool. thx.
>

Back to top

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

5c.

Re: Am unable to retrieve MSN Stockscouter values

Posted by: "investor952" investor952@yahoo.com   investor952

Mon Jun 13, 2011 10:22 pm (PDT)

Thanks. I tried what you suggested and it apparently worked..but..only one of the 11 fields i was trying to retrieve works.
Element number 543 (current stock scouter rating) worked,
elements 542,544 through 552 returned either 'obsolete' or 'error'.

Are there any other work in progress files I/we should copy over (sorry if you mentioned this in prior posts as I am new using this).

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You probably just need the new smf-elements-1.txt element definition file
> from the "Works in Progress" folder of the files area of the group. I had to
> redefine a number of elements earlier this year because MSN changed some of
> their web pages.
>
> Just replace your version of the smf-elements-1.txt file in the add-in
> folder with the new one and then run the smfForceRecalculation macro to
> reload the element definitions. See the "Links" area of the group for info
> on smfForceRecalculation.
>
> On Mon, Jun 13, 2011 at 7:48 PM, investor952 <investor952@...> wrote:
>
> > I am trying to retrieve some MSN Stockscouter values into Excel 2002 using
> > the function =RCHGetElementNumber(a,b), where a points to the ticker and b
> > is, say 543, the current stock scouter rating. For this, an others, the only
> > thing that pops up is Error.
> >
> > Do i need to save this file in the smf folder in the program files folder
> > for this to work? I remember reading this somewhere. I am new using this
> > great tool. thx.
> >
>

Back to top

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

5d.

Re: Am unable to retrieve MSN Stockscouter values

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

Mon Jun 13, 2011 10:37 pm (PDT)

That's because those items are no longer available on MSN, so I had to
obsolete them.

At the time, elements #547-550 were available, but it appears the web page
they used to be on is now gone, so I'll need to obsolete them as well.

On Mon, Jun 13, 2011 at 10:22 PM, investor952 <investor952@yahoo.com> wrote:

> Thanks. I tried what you suggested and it apparently worked..but..only one
> of the 11 fields i was trying to retrieve works.
> Element number 543 (current stock scouter rating) worked,
> elements 542,544 through 552 returned either 'obsolete' or 'error'.
>
> Are there any other work in progress files I/we should copy over (sorry if
> you mentioned this in prior posts as I am new using this).
>

Back to top

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

5e.

Re: Am unable to retrieve MSN Stockscouter values

Posted by: "investor952" investor952@yahoo.com   investor952

Mon Jun 13, 2011 10:49 pm (PDT)

Are there any other files in the Works in Progress folder that you recommend me copying over? thx.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> That's because those items are no longer available on MSN, so I had to
> obsolete them.
>
> At the time, elements #547-550 were available, but it appears the web page
> they used to be on is now gone, so I'll need to obsolete them as well.
>
> On Mon, Jun 13, 2011 at 10:22 PM, investor952 <investor952@...> wrote:
>
> > Thanks. I tried what you suggested and it apparently worked..but..only one
> > of the 11 fields i was trying to retrieve works.
> > Element number 543 (current stock scouter rating) worked,
> > elements 542,544 through 552 returned either 'obsolete' or 'error'.
> >
> > Are there any other work in progress files I/we should copy over (sorry if
> > you mentioned this in prior posts as I am new using this).
> >
>

Back to top

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

5f.

Re: Am unable to retrieve MSN Stockscouter values

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

Mon Jun 13, 2011 11:09 pm (PDT)

All of the smf-elements-*.txt files contain element definition updates that
have been made since the 2.1.2010.08.02 release of the add-in.

The 2.1.2011.04.03.zip file contains both the updated element definitions as
well as changes to the add-in programming.

Check the change log file for changes that have been made.

On Mon, Jun 13, 2011 at 10:49 PM, investor952 <investor952@yahoo.com> wrote:

> Are there any other files in the Works in Progress folder that you
> recommend me copying over? thx.
>

Back to top

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

6a.

In RCHGetTableCell, need space for <BR>

Posted by: "Charley Kyd" kyd@exceluser.com   charleykyd

Mon Jun 13, 2011 10:30 pm (PDT)

RCHGetTableCell is returning data from a cell that has: Text<br>Text. Because you strip out tags, the function is returning TextText.

Just as you replace Chr(9) with a space, you need to replace <BR> and </BR> with a space. If you add too many spaces, that's easier to correct in Excel than it is to insert spaces to separate run-together words.

Charley Kyd

Back to top

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

6b.

Re: In RCHGetTableCell, need space for <BR>

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

Mon Jun 13, 2011 10:39 pm (PDT)

Actually, the <br> is replaced with a form feed. If you format that cell so
that it wraps, your result should actually be:

Text
Text

...which is what the <br> is intended to do.

On Mon, Jun 13, 2011 at 10:22 PM, Charley Kyd <kyd@exceluser.com> wrote:

> RCHGetTableCell is returning data from a cell that has: Text<br>Text.
> Because you strip out tags, the function is returning TextText.
>
> Just as you replace Chr(9) with a space, you need to replace <BR> and </BR>
> with a space. If you add too many spaces, that's easier to correct in Excel
> than it is to insert spaces to separate run-together words.
>

Back to top

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

7a.

just got run time error '28' trying to run smfForceRecalculation mac

Posted by: "investor952" investor952@yahoo.com   investor952

Mon Jun 13, 2011 11:00 pm (PDT)

I just tried to run the smfForceRecalculation macro several times and each time I get a window popping up that says "Run-time error '28': Out of stack space"

I didn't get this before. Do you know what would cause this? I did a search at your chat group and couldn't find any mention of 'stack space' . thx.

Back to top

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

7b.

Re: just got run time error '28' trying to run smfForceRecalculation

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

Mon Jun 13, 2011 11:12 pm (PDT)

I've not seen the error before. Do you have limited RAM available?

On Mon, Jun 13, 2011 at 11:00 PM, investor952 <investor952@yahoo.com> wrote:

> I just tried to run the smfForceRecalculation macro several times and each
> time I get a window popping up that says "Run-time error '28': Out of stack
> space"
>
> I didn't get this before. Do you know what would cause this? I did a search
> at your chat group and couldn't find any mention of 'stack space' . thx.
>

Back to top

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

Recent Activity

•  77
  New Members
•  1
  New Files

Visit Your Group

Give Back

Yahoo! for Good

Get inspired

by a good cause.

Y! Toolbar

Get it Free!

easy 1-click access

to your groups.

Yahoo! Groups

Start a group

in 3 easy steps.

Connect with others.

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

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