Sabtu, 10 Maret 2012

[smf_addin] Digest Number 2142

Messages In This Digest (8 Messages)

Messages

1a.

Re: getting data from IBD stock checkup page

Posted by: "mff2805" mff3429@comcast.net   mff2805

Fri Mar 9, 2012 2:27 am (PST)



thanks for the info. i use firefox as my default browser.

inside excel 2010, i just did a 'get external data' from the web, logged in to investors.com. didn't get a redirection. i logged in to investors, navigate to the checkup page with 'dg' as the ticker and imported the web info into a new sheet. i see about 350 lines, and the info i want to consolidate into my own spreadsheet summary is located on various lines of the import. so, i think i'm part way there.
i'm not sure how to make these imported lines linked to my master ticker cell that's on my master sheet. i guess i'll try some things. suggestions? got a sample spreadsheet for ibd data?

haven't seen the web query feature yet. i'm now looking at the help info and am lost int he 'data link properties' window. will try again tomorrow.

help would be appreciated. thx
- mike

--- In smf_addin@yahoogroups.com, "Randy H" <rharmelink@...> wrote:
>
> This formula:
>
> =RCHGetTableCell("http://research.investors.com/stockcheckup/nyse-agrium\
> -inc-agu.aspx",1,">EPS")
>
> ...returns the EPS Due Date for me.
>
> Two things you have to make sure of:
>
> 1. You need to visit the site with the EXCEL Web Query dialog or with
> IE before using the add-in. Sometimes they do a "first visit of the
> day" redirection to some advertising page. If that never gets resolved
> via the IE object, the cookie that does that redirection will never get
> updated.
>
>
> 2. You need to log into the site with the EXCEL Web Query dialog or
> with IE before using the add-in. This creates the security cookie you'll
> need to access the web pages on the site. Note that if you happen to
> use something like FireFox to visit the site, your IE cookie will
> automatically be voided. IBD only allows you to be logged in with one
> browser at the same time.
>
> On Fri, Mar 9, 2012 at 2:16 AM, Mike Fitzpatrick <mff3429@...>
> wrote:
>
> i have a subscription to IBD and use their stock checkup page on a very
> regular basis.
> I was looking to pull data off that page into a summary spreadsheet, but
> am having difficulties getting something useful. i can build the
> appropriate web address and am trying the rchgettablecell function and
> the rchgetwebdata function - to no avail. the attached screenshot shows
> my work to date. do i need to get more knowledgeable about web pages
> and the addin to learn how to get info such as composite rating,
> industry rank, etc? or is there an easier way? i've snooped through
> messages and files and am a few hrs into this exercise.
>
> looking for help. thanks much. i enjoy the addin; must be a lot of
> work, randy.
>

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

Re: getting data from IBD stock checkup page

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

Fri Mar 9, 2012 3:50 am (PST)



The EXCEL Web Query *is* the "Get External Data" process you went through.
That you can do a "Get External Data" process and import the data means you
are at the point that you should be able to use the add-in. The add-in and
the IE object both use the same Microsoft processing to access the Internet.

There are no sample spreadsheets of IBD data using their new URL structure.
I don't really use them that way anymore -- just the eTables.

I really hate working with IBD, because I like to use FireFox to explore
the web pages when I'm trying to set up the add-in formulas. So it's a real
pain to have it log me out of the IE process at the same time. I have to go
back and forth and log in to each every time I want to see something on
each side.

On Fri, Mar 9, 2012 at 3:27 AM, mff2805 <mff3429@comcast.net> wrote:

> thanks for the info. i use firefox as my default browser.
>
> inside excel 2010, i just did a 'get external data' from the web, logged
> in to investors.com. didn't get a redirection. i logged in to
> investors, navigate to the checkup page with 'dg' as the ticker and
> imported the web info into a new sheet. i see about 350 lines, and the
> info i want to consolidate into my own spreadsheet summary is located on
> various lines of the import. so, i think i'm part way there.
> i'm not sure how to make these imported lines linked to my master ticker
> cell that's on my master sheet. i guess i'll try some things. suggestions?
> got a sample spreadsheet for ibd data?
>
> haven't seen the web query feature yet. i'm now looking at the help info
> and am lost int he 'data link properties' window. will try again tomorrow.
>
> help would be appreciated. thx
> - mike
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
1c.

Re: getting data from IBD stock checkup page

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

Fri Mar 9, 2012 4:37 am (PST)



I threw together a template, because I had an idea of how I might be able
to drive data retrieval using just the ticker symbol, creating the
necessary URL with exchange and company name:

RCHGetTableCell-Template-IBD-Stock-Checkup.xls

On Fri, Mar 9, 2012 at 3:27 AM, mff2805 <mff3429@comcast.net> wrote:

>
> got a sample spreadsheet for ibd data?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
2a.

Get Table Cell

Posted by: "Steven" stevenletzer@yahoo.com   stevenletzer

Fri Mar 9, 2012 11:49 am (PST)



At this address http://www.michbar.org/memberdirectory/home.cfm
I am trying to capture data, where a search can be done ny a code which I have. For example code = 63129, the web address is http://www.michbar.org/memberdirectory/detail.cfm?PID=63129 A table is returned with information to be captured.

Phone: xxxxxxx
Fax: xxxxxxxxxx
e-Mail: xxxxxxxxxx
Web: xxxxxxxx
Michigan Licensed: xxxxxxxxxxx

This formula returned an error that I cannot correct
RCHGetTableCell("http://www.michbar.org/memberdirectory/detail.cfm?PID=63129",1,"Phone:")

I thought Phone: (and all of the others, were columns; but that did not work.

Any ideas?

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

Re: Get Table Cell

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

Fri Mar 9, 2012 12:49 pm (PST)



That's because it's not a TABLE of data, it's a table CELL of data -- all
of the data is individual lines within a single table cell. Try:

=RCHGetTableCell("
http://www.michbar.org/memberdirectory/detail.cfm?PID=63129",0,"Phone:")

...and you'll get all of the data in one shot, but you'll need to extract
out the various pieces you want.

On Fri, Mar 9, 2012 at 12:49 PM, Steven <stevenletzer@yahoo.com> wrote:

> At this address http://www.michbar.org/memberdirectory/home.cfm
> I am trying to capture data, where a search can be done ny a code which I
> have. For example code = 63129, the web address is
> http://www.michbar.org/memberdirectory/detail.cfm?PID=63129 A table is
> returned with information to be captured.
>
> Phone: xxxxxxx
> Fax: xxxxxxxxxx
> e-Mail: xxxxxxxxxx
> Web: xxxxxxxx
> Michigan Licensed: xxxxxxxxxxx
>
> This formula returned an error that I cannot correct
> RCHGetTableCell("
> http://www.michbar.org/memberdirectory/detail.cfm?PID=63129",1,"Phone:")
>
> I thought Phone: (and all of the others, were columns; but that did not
> work.
>
> Any ideas?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
2c.

Re: Get Table Cell

Posted by: "Steven" stevenletzer@yahoo.com   stevenletzer

Fri Mar 9, 2012 1:50 pm (PST)



Thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> That's because it's not a TABLE of data, it's a table CELL of data -- all
> of the data is individual lines within a single table cell. Try:
>
> =RCHGetTableCell("
> http://www.michbar.org/memberdirectory/detail.cfm?PID=63129",0,"Phone:")
>
> ...and you'll get all of the data in one shot, but you'll need to extract
> out the various pieces you want.
>
> On Fri, Mar 9, 2012 at 12:49 PM, Steven <stevenletzer@...> wrote:
>
> > At this address http://www.michbar.org/memberdirectory/home.cfm
> > I am trying to capture data, where a search can be done ny a code which I
> > have. For example code = 63129, the web address is
> > http://www.michbar.org/memberdirectory/detail.cfm?PID=63129 A table is
> > returned with information to be captured.
> >
> > Phone: xxxxxxx
> > Fax: xxxxxxxxxx
> > e-Mail: xxxxxxxxxx
> > Web: xxxxxxxx
> > Michigan Licensed: xxxxxxxxxxx
> >
> > This formula returned an error that I cannot correct
> > RCHGetTableCell("
> > http://www.michbar.org/memberdirectory/detail.cfm?PID=63129",1,"Phone:")
> >
> > I thought Phone: (and all of the others, were columns; but that did not
> > work.
> >
> > Any ideas?
> >
>

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

Problem with RCHGetTableCell() for Morningstar page

Posted by: "sharkshockey83" srichards83@gmail.com   sharkshockey83

Fri Mar 9, 2012 1:49 pm (PST)



It seems like getting data off Morningstar.com with the add-in is kind of hit-or-miss, so I'm not sure whether I'm screwing something up or if the add-in is just unable to get the info I need. I am trying to get data from the table under the 'Yield to Maturity' section on the following page:
http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=amat&Country=USA

I need the data from the 'Maturity Date' and 'Amount $(Mil)' columns in the table. I tried the following formula to grab the maturity date for the first row of the table and it returns 'Error' (the cell N3 contains the ticker):
=RCHGetTableCell("http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol="&N3&"&Country=USA",1,">Applied Matls 4.3%")

If the data can be retrieved from this page, is there a way I can use the add-in to get the data without having to use "Applied Matls 4.3%" as the search string? Obviously that is unique to Applied Materials, so if I entered a new ticker in cell N3 (say "IBM"), the formula wouldn't work. I am basically looking for a way to instruct the add-in: "Get columns 2 and 3 from this table."

Thanks for the help!

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

Re: Problem with RCHGetTableCell() for Morningstar page

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

Fri Mar 9, 2012 2:11 pm (PST)



The problem is that the data you want is NOT on that web page -- it's in
a frame within the web page. The actual web page for the data is:

http://bond.morningstar.com/internal/company.aspx?cID=0C00000ADK&country\
=USA&ticker=AMAT

You can retrieve that URL with something like:

=smfStrExtr(RCHGetWebData("http://quicktake.morningstar.com/StockNet/bon\
ds.aspx?Symbol=amat","<iframe",250),"src=""","""")

Then, you can retrieve the whole table with:

=RCHGetHTMLTable("http://bond.morningstar.com/internal/company.aspx?cID=\
0C00000ADK&country=USA&ticker=AMAT",">Callable",1,"",2)

You can get just the 2nd and 3rd columns with something like:

=INDEX(RCHGetHTMLTable("http://bond.morningstar.com/internal/company.asp\
x?cID=0C00000ADK&country=USA&ticker=AMAT",">Callable",1,"",2,,10,3),,2)
=INDEX(RCHGetHTMLTable("http://bond.morningstar.com/internal/company.asp\
x?cID=0C00000ADK&country=USA&ticker=AMAT",">Callable",1,"",2,,10,3),,3)

On Fri, Mar 9, 2012 at 2:37 PM, sharkshockey83 <srichards83@gmail.com>
wrote:
It seems like getting data off Morningstar.com with the add-in is kind
of hit-or-miss, so I'm not sure whether I'm screwing something up or if
the add-in is just unable to get the info I need. I am trying to get
data from the table under the 'Yield to Maturity' section on the
following page:
http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=amat&Country\
=USA
<http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=amat&Countr\
y=USA>

I need the data from the 'Maturity Date' and 'Amount $(Mil)' columns in
the table. I tried the following formula to grab the maturity date for
the first row of the table and it returns 'Error' (the cell N3 contains
the ticker):
=RCHGetTableCell("http://quicktake.morningstar.com/StockNet/bonds.aspx?S\
ymbol= <http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=>
"&N3&"&Country=USA",1,">Applied Matls 4.3%")

If the data can be retrieved from this page, is there a way I can use
the add-in to get the data without having to use "Applied Matls 4.3%" as
the search string? Obviously that is unique to Applied Materials, so if
I entered a new ticker in cell N3 (say "IBM"), the formula wouldn't
work. I am basically looking for a way to instruct the add-in: "Get
columns 2 and 3 from this table."

Thanks for the help!

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

Fashion News

What's the word on

fashion and style?

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

New web site?

Drive traffic now.

Get your business

on 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.56

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)