Rabu, 02 Mei 2012

[smf_addin] Digest Number 2195

Messages In This Digest (19 Messages)

Messages

1a.

Getting quotes data from the Google API

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

Tue May 1, 2012 2:03 am (PDT)



I wouldn't use the smfGetTagContent() that way, because you have no idea
if the last data will always be the 12th item in the list.

Also, I found I was doing the FIND/FIND/MID technique you're using so
often, I wrote the smfStrExtr() function to do it it as a user-defined
function.

In any case, this is what I would use for the extraction:

=0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock=msft","l\
ast data=",50),"=""","""")

You could extend that formula to any of the other data items by changing
the "last data=" search string.

Did you know you can get multiple ticker symbols with one Internet
access? For example, if you put ticker symbols in cells A2:A4, put this
formula in cell B2 and copy down:

=0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock="&sfJoin\
($A$2:$A$4,"&stock="),"<symbol data="""&A2&"""",5000),"last
data=""","""")

On Mon, Apr 30, 2012 at 11:09 PM, B B <kokdari82@yahoo.com
<mailto:kokdari82@yahoo.com> > wrote:
Thanks for that suggestion but one reason I like to stay away from yahoo
to get prices is because pink sheet stock symbols require .pk at the end
otherwise nothing shows up.

That's why I much prefer something like Google.

It also seems like Google has a api too which makes the basic
information easier to get.

http://www.google.com/ig/api?stock=aapl
<http://www.google.com/ig/api?stock=aapl>

So for something like this where I want the last price which is defined
by the following tag <last data="583.98"/> , how should I extract that?

I did something like the following, but is there a more efficient way of
using the getsmftagcontent function?

Cell A1 has
=smfGetTagContent("http://www.google.com/ig/api?stock=msft
<http://www.google.com/ig/api?stock=msft> ","",12)

Cell A2
=FIND("/",A1)-1

Cell A3
=FIND("=",A1)+2

Cell A4
=MID(A1,A3,A2-A3)

Cell A4 displays the correct last price.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
1b.

Re: Getting quotes data from the Google API

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

Tue May 1, 2012 2:31 am (PDT)



I just uploaded a little template to give an example of extracting data
from the Google Quotes API:

smfGetTagContent-Google-API-Quotes.xls

Like Yahoo, it can be used to get multiple data items and multiple
ticker symbols in a single Internet access. I'm not sure what the
limitation is for the number of ticker symbols. I was able to do the
first 202 companies of the S&P 500 before I got errors, but I suspect
the limit is based on the length of the URL created by concatenating
ticker symbols rather than on the actual number of ticker symbols.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
1c.

Re: Getting quotes data from the Google API

Posted by: "B B" kokdari82@yahoo.com   kokdari82

Tue May 1, 2012 10:01 am (PDT)



Awesome. I didn't realize the functions could be used like this.
Thank you very much.

--- In smf_addin@yahoogroups.com, "Randy H" <rharmelink@...> wrote:
>
> I just uploaded a little template to give an example of extracting data
> from the Google Quotes API:
>
> smfGetTagContent-Google-API-Quotes.xls
>
> Like Yahoo, it can be used to get multiple data items and multiple
> ticker symbols in a single Internet access. I'm not sure what the
> limitation is for the number of ticker symbols. I was able to do the
> first 202 companies of the S&P 500 before I got errors, but I suspect
> the limit is based on the length of the URL created by concatenating
> ticker symbols rather than on the actual number of ticker symbols.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
1d.

Re: Getting quotes data from the Google API

Posted by: "ken" kab777@optonline.net   mustang66.7599

Tue May 1, 2012 5:43 pm (PDT)



I see how the first extraction will get the last price for MSFT - how do I
set it up to get the last price of whatever symbol I enter in A2?

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of Randy H
Sent: Tuesday, May 01, 2012 5:04 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Getting quotes data from the Google API

I wouldn't use the smfGetTagContent() that way, because you have no idea if
the last data will always be the 12th item in the list.

Also, I found I was doing the FIND/FIND/MID technique you're using so often,
I wrote the smfStrExtr() function to do it it as a user-defined function.

In any case, this is what I would use for the extraction:

=0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock=msft","last
data=",50),"=""","""")

You could extend that formula to any of the other data items by changing the
"last data=" search string.

Did you know you can get multiple ticker symbols with one Internet access?
For example, if you put ticker symbols in cells A2:A4, put this formula in
cell B2 and copy down:

=0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock="&sfJoin($A$
2:$A$4,"&stock="),"<symbol data="""&A2&"""",5000),"last data=""","""")

On Mon, Apr 30, 2012 at 11:09 PM, B B <kokdari82@yahoo.com> wrote:

Thanks for that suggestion but one reason I like to stay away from yahoo to
get prices is because pink sheet stock symbols require .pk at the end
otherwise nothing shows up.

That's why I much prefer something like Google.

It also seems like Google has a api too which makes the basic information
easier to get.

http://www.google.com/ig/api? <http://www.google.com/ig/api?stock=aapl>
stock=aapl

So for something like this where I want the last price which is defined by
the following tag <last data="583.98"/> , how should I extract that?

I did something like the following, but is there a more efficient way of
using the getsmftagcontent function?

Cell A1 has
=smfGetTagContent("http://www. <http://www.google.com/ig/api?stock=msft>
google.com/ig/api?stock=msft","",12)

Cell A2
=FIND("/",A1)-1

Cell A3
=FIND("=",A1)+2

Cell A4
=MID(A1,A3,A2-A3)

Cell A4 displays the correct last price.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
1e.

Re: Getting quotes data from the Google API

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

Tue May 1, 2012 5:55 pm (PDT)



Take a look at the template. It uses smfGetTagContent() to get the
<finance> tag for the particular ticker symbol, and then uses smfStrExtr()
to extract the particular data item from that <finance> tag.

On Tue, May 1, 2012 at 5:43 PM, ken <kab777@optonline.net> wrote:

>
> I see how the first extraction will get the last price for MSFT – how do
> I set it up to get the last price of whatever symbol I enter in A2?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
2.1.

New file uploaded to smf_addin

Posted by: "smf_addin@yahoogroups.com" smf_addin@yahoogroups.com

Tue May 1, 2012 2:21 am (PDT)




Hello,

This email message is a notification to let you know that
a file has been uploaded to the Files area of the smf_addin
group.

File : /Templates and Examples/smfGetTagContent/smfGetTagContent-Google-API-Quotes.xls
Uploaded by : rharmelink <rharmelink@gmail.com>
Description : An example of pulling in quotes data from the Google Quotes API.

You can access this file at the URL:
http://groups.yahoo.com/group/smf_addin/files/Templates%20and%20Examples/smfGetTagContent/smfGetTagContent-Google-API-Quotes.xls

To learn more about file sharing for your group, please visit:
http://help.yahoo.com/l/us/yahoo/groups/original/members/web/index.html
Regards,

rharmelink <rharmelink@gmail.com>


Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (262)
3a.

Industry and Name from MSN

Posted by: "bob_cutillo" bob.cutillo@yahoo.com   bob_cutillo

Tue May 1, 2012 7:02 am (PDT)



Elements 4 for Industry and 13862 for Name stopped returning the info I want and now return Error when used with the download and rchgetelement functions. Do I need to code differently or is there a different code I can use?

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3b.

Re: Industry and Name from MSN

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

Tue May 1, 2012 8:23 am (PDT)



Both of those elements are working fine for me.

You probably just need the new element definitions -- smf-elements-1.txt
from the "Works in Progress" folder of the files area. MSN recently changed
their URL structure and web page layout.

On Tue, May 1, 2012 at 7:01 AM, bob_cutillo <bob.cutillo@yahoo.com> wrote:

> Elements 4 for Industry and 13862 for Name stopped returning the info I
> want and now return Error when used with the download and rchgetelement
> functions. Do I need to code differently or is there a different code I
> can use?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4a.

I can't get anything to run correctly in Excel 2007.

Posted by: "Ken" kendchu@gmail.com   kenc_7_21

Tue May 1, 2012 8:26 am (PDT)



It displays "#NAME?" and says unable to conncet. How do i fix it?

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4b.

Re: I can't get anything to run correctly in Excel 2007.

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

Tue May 1, 2012 8:27 am (PDT)



Without more specifics about what is returning #NAME?, it's hard to help.
There are several reasons it could occur.

"Unable to connect" sounds like you have a firewall in place that is
preventing EXCEL from accessing the Internet.

On Tue, May 1, 2012 at 8:24 AM, Ken <kendchu@gmail.com> wrote:

> It displays "#NAME?" and says unable to conncet. How do i fix it?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5.

Please disregard prior message  on MSN problem

Posted by: "bob_cutillo" bob.cutillo@yahoo.com   bob_cutillo

Tue May 1, 2012 9:19 am (PDT)



I read the other messages and posted the works in progress file. Thank you

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
6a.

Missing something while using latest stable add in

Posted by: "Subu S" mail_to_subu@yahoo.com   mail_to_subu

Tue May 1, 2012 11:33 am (PDT)



Hi Randy

Good day to you...

Here's what I've done
--------------------------------
- I've tried to use the latest Add in from http://bit.ly/Inmrc9 i.e. downloaded and extracted files to c:\Program files\SMF Add-in directory
- Then opened XL
- Then typed =RCHGetElementNumber("AAPL",1) on a blank cell
- I get "Error" as the result
- for =RCHGetElementNumber("AAPL",2)I get "No longer available" as the result
- for =RCHGetElementNumber("AAPL",3) ... =RCHGetElementNumber("AAPL",552) I get "Error" as the result
- For =RCHGetElementNumber("AAPL",553) I get "Current Qtr. Jun 12" and then some data

- what am I missing ?

- Hate to trouble you ...still can't quite figure out !!

PS : AAPL is just an example, I shall replace that with a Cell ref / variable etc

*Thanks in advance* and regards

Subu

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
6b.

Re: Missing something while using latest stable add in

Posted by: "MikeM" mikemcq802@yahoo.com   mikemcq802

Tue May 1, 2012 11:40 am (PDT)



All of those elements are from MSN.

MSN recently changed its website. You need to get the latest smf-elements-1.txt file from the Works In Progress folder in the group.

http://finance.groups.yahoo.com/group/smf_addin/files/Works-In-Progress/

--- In smf_addin@yahoogroups.com, "Subu S" <mail_to_subu@...> wrote:
>
> Hi Randy
>
> Good day to you...
>
> Here's what I've done
> --------------------------------
> - I've tried to use the latest Add in from http://bit.ly/Inmrc9 i.e. downloaded and extracted files to c:\Program files\SMF Add-in directory
> - Then opened XL
> - Then typed =RCHGetElementNumber("AAPL",1) on a blank cell
> - I get "Error" as the result
> - for =RCHGetElementNumber("AAPL",2)I get "No longer available" as the result
> - for =RCHGetElementNumber("AAPL",3) ... =RCHGetElementNumber("AAPL",552) I get "Error" as the result
> - For =RCHGetElementNumber("AAPL",553) I get "Current Qtr. Jun 12" and then some data
>
> - what am I missing ?
>
> - Hate to trouble you ...still can't quite figure out !!
>
>
> PS : AAPL is just an example, I shall replace that with a Cell ref / variable etc
>
>
> *Thanks in advance* and regards
>
> Subu
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
7a.

Re: MSN elements not working anymore

Posted by: "bizmark_ee" dustinsmoak@live.com   bizmark_ee

Tue May 1, 2012 2:06 pm (PDT)



Just checking: Is element 25 supposed to be working yet? I tried using latest definition file, and the formula, =RCHGetElementNumber("CHK",25), returns a valid number but it is returning yesterday's close price instead of last price.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Thanks for the heads up!
>
> The fix for element #1 will be in the next upload of the element
> definitions file.
>
> (...not real fond of MSN at the moment...)
>
> On Fri, Apr 27, 2012 at 12:44 PM, Mark <ballard@...> wrote:
>
> >
> > Element 1 also seems to still be not working.
> >
> > Thanks for all the time you put into this!
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
7b.

Re: MSN elements not working anymore

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

Tue May 1, 2012 2:09 pm (PDT)



That's what MSN is posting as "Last Price".

On Tue, May 1, 2012 at 2:06 PM, bizmark_ee <dustinsmoak@live.com> wrote:

> Just checking: Is element 25 supposed to be working yet? I tried using
> latest definition file, and the formula, =RCHGetElementNumber("CHK",25),
> returns a valid number but it is returning yesterday's close price instead
> of last price.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
7c.

Re: MSN elements not working anymore

Posted by: "bizmark_ee" dustinsmoak@live.com   bizmark_ee

Tue May 1, 2012 2:15 pm (PDT)



OK, but when I go to http://investing.money.msn.com/investments/stock-price?Symbol=chk&ocid=qbeb, it shows the correct last price ($19.60) at the top. Is your query looking at a different quote page?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> That's what MSN is posting as "Last Price".
>
> On Tue, May 1, 2012 at 2:06 PM, bizmark_ee <dustinsmoak@...> wrote:
>
> > Just checking: Is element 25 supposed to be working yet? I tried using
> > latest definition file, and the formula, =RCHGetElementNumber("CHK",25),
> > returns a valid number but it is returning yesterday's close price instead
> > of last price.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
7d.

Re: MSN elements not working anymore

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

Tue May 1, 2012 2:40 pm (PDT)



Most MSN elements come from the print report page, so that only one web
page needs to be retrieved:

http://investing.money.msn.com/investments/stock-report?symbol=chk&QD=1&OP=1&Y1=1&CR=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1

Do a search on "Last Price".

On Tue, May 1, 2012 at 2:15 PM, bizmark_ee <dustinsmoak@live.com> wrote:

> OK, but when I go to
> http://investing.money.msn.com/investments/stock-price?Symbol=chk&ocid=qbeb,
> it shows the correct last price ($19.60) at the top. Is your query looking
> at a different quote page?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
8a.

Re: getting data from IBD stock checkup page

Posted by: "ccsjeba" ccsjeba@hotmail.com   ccsjeba

Tue May 1, 2012 2:57 pm (PDT)



This is great. Thanks a lot Mike

--- In smf_addin@yahoogroups.com, Mike Fitzpatrick <mff3429@...> wrote:
>
> the key to generating the list is to know one stock in each of the 197
> subgroups. enter that stock in randy's template to find the top 5 for a
> variety of ratings, such as composite rating. it took me about a year
> to find a stock for each subgroup, because i'm too cheap to buy the $40
> book they publish in paper only. attached is a pdf and a xlsm file for
> those who may have an interest.
> --
> *Mike
> *
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (16)
9.

ft.markets.com

Posted by: "Lawrence" lawrence.leesh@gmail.com   lawleesh

Wed May 2, 2012 12:07 am (PDT)



Hi

Is there a way to download info from ft.markets.com? They have a lot of good fundamental info on companies.

regards

LL

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Fashion News

What's the word on

fashion and style?

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
Diposting oleh EXCEL FEED LEARNING di 01.19

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)