Jumat, 26 Juli 2013

[smf_addin] Digest Number 2711

15 New Messages

Digest #2711
2a
2b
2c
3a
Need help with formula by "Kermit W. Prather" kermitpra
3b
Re: Need help with formula by "Randy Harmelink" rharmelink
3c
Re: Need help with formula by "Kermit W. Prather" kermitpra
4a
MS Office 2013 by "oryxius" oryxius
4b
Re: MS Office 2013 by "Randy Harmelink" rharmelink
6a
Re: trouble with RCHGetHTMLTable by "mbadr911" mbadr911

Messages

Fri Jul 26, 2013 1:41 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Nothing simple at this point. At one point, all of the data for a stock on
MorningStar could be found on a print report page. That's what I originally
defined all of the elements for. But then they moved away from that print
report page and went to individual pages that dynamically display the data
-- which means the data isn't in the source code of that web page, and
there is nothing for the add-in to extract.

Lately, I've been poking around on their dynamic web pages, and have found
out they have source pages that they used to build the dynamic page. The
formula I gave you for yield pulls the data from just one of those web
page. Unfortunately, their are dozens of them. See this and the thread it
is in: http://finance.groups.yahoo.com/group/smf_addin/message/22957

So I've been working on a template that shows the formula for extracting
each data item from each of those pages. Maybe to eventually be used to
redefine the old elements and make some new ones.

The formula I gave you was one of those from my template, but I've only
done a few web pages so far. For example, the basic quote page:

Last Price: 116.55 =smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","div",0,"vkey=""LastPrice""")) Open Price: 115.3
=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""OpenPrice""")) Day Range: 114.84-116.90
=smfGetTagContent("http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""DayRange""") 114.84
=smfConvertData(smfstrExtr(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""DayRange"""),"~","-")) 116.9
=smfConvertData(smfstrExtr(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""DayRange"""),"-","~")) 52-Week Range: 86.74-117.30
=smfGetTagContent("http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""_52Week""") 86.74
=smfConvertData(smfstrExtr(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""_52Week"""),"~","-")) 117.3
=smfConvertData(smfstrExtr(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""_52Week"""),"-","~")) Projected Yield: 0.021
=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""ProjectedYield""")) Market Cap 80.4 bil
=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"id=""MarketCap""")) Volume 3.3 mil
=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""Volume""")) Avg Vol. 2.8 mil
=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""AverageVolume""")) Forward P/E 15.3
=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""PE""")) P/B 4.4 =smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""PB""")) P/S 2.7 =smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""PS""")) P/CF 15 =smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t=MMM
","span",0,"vkey=""PC"""))
But like I said, just one of dozens of web pages.

On Thu, Jul 25, 2013 at 9:19 PM, Gururaj Rao <honyakusha@gmail.com> wrote:

>
> Sorry, Randy, I was not clear about the question I posted.
> The example above is only for Yield.
> I wish to get many other kinds of data from the morningstar site such as
> P/E, P/CF and data from other pages at the same site.
> So I presume I need to get the relevant URL, insert it between the
> parentheses here --> =smfConvertData(smfGetTagContent( .....), get
> parameters such as "span",0,"vkey=""P/E "") and so on, after viewing the
> source page and insert them?
> Any simple methods to know about the parameters to insert after the URL?
> Or should a layman like me give up at this stage? Any pointers would be
> appreciated .
>
>

Fri Jul 26, 2013 9:20 am (PDT) . Posted by:

"waterboy5555" waterboy5555

Randy,

Thanks for your help. Something to consider for the excel add in. For some time periods, the add in works great and reads off morningstar like a champ. In other instances, the addin gives error values for the same functions that were one giving readings. If you know of something I am supposed to refresh or maintain so as not to get the error messages, that would be great. I have tried the smfforcerecalculation, but it does not seem to help. Thanks.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Not a clue. Sorry.
>
> On Thu, Jul 25, 2013 at 8:38 AM, waterboy5555 <bopha99@...> wrote:
>
> > Ok. I've got it to work while referencing a cell. For some reason it
> > took me a couple times reloading the page in IE before the formula kicked
> > in in Excel. Do you know why this could be the problem?
> >
>

Fri Jul 26, 2013 9:26 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The only time I get errors like that are on the first use of the day, when
I haven't created my cookie with IE. Because MorningStar does that
redirection on the first visit of the day for advertising purposes.

So, if I do get an "Error" value. I use the EXCEL Web Query process to
visit the site, cancel out of it, and then use the smfForceRecalculation. I
have always seen the "Error" value change to what I am expecting to have
returned.

But I too often forget to visit the site first, and it can be jarring to
get a bunch of "Error" values returned.

On Fri, Jul 26, 2013 at 9:20 AM, waterboy5555 <bopha99@aol.com> wrote:

>
> Thanks for your help. Something to consider for the excel add in. For
> some time periods, the add in works great and reads off morningstar like a
> champ. In other instances, the addin gives error values for the same
> functions that were one giving readings. If you know of something I am
> supposed to refresh or maintain so as not to get the error messages, that
> would be great. I have tried the smfforcerecalculation, but it does not
> seem to help. Thanks.
>

Fri Jul 26, 2013 10:03 am (PDT) . Posted by:

"waterboy5555" waterboy5555

What you talked about did not work for me. Rather, I had to go into the IE options and always allow cookies from morningstar.com. Then, I restarted IE and got the link from morningstar and it worked. I'm not sure if the links will go stale again, but I will be testing it out. Thanks for this cool add in.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The only time I get errors like that are on the first use of the day, when
> I haven't created my cookie with IE. Because MorningStar does that
> redirection on the first visit of the day for advertising purposes.
>
> So, if I do get an "Error" value. I use the EXCEL Web Query process to
> visit the site, cancel out of it, and then use the smfForceRecalculation. I
> have always seen the "Error" value change to what I am expecting to have
> returned.
>
> But I too often forget to visit the site first, and it can be jarring to
> get a bunch of "Error" values returned.
>
> On Fri, Jul 26, 2013 at 9:20 AM, waterboy5555 <bopha99@...> wrote:
>
> >
> > Thanks for your help. Something to consider for the excel add in. For
> > some time periods, the add in works great and reads off morningstar like a
> > champ. In other instances, the addin gives error values for the same
> > functions that were one giving readings. If you know of something I am
> > supposed to refresh or maintain so as not to get the error messages, that
> > would be great. I have tried the smfforcerecalculation, but it does not
> > seem to help. Thanks.
> >
>

Fri Jul 26, 2013 11:12 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

Randy, can you correct my formula. I tried several variation and can't get
it correct.

Thanks, Kermit

I am trying to place the message that follows "splits-text" into a cell.
The stock symbol is in cell K3

Right now I am just trying to find if this stock has had any stock splits.

Later I plan to retrieve the list of stock splits.

Using this formula I get #VALUE!

=smfGetTagContent("http://getsplithistory.com/",K3,"splits-text";,0)

Source for the webpage http://getsplithistory.com/AGNC is


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>

<head>
<meta http-equiv="Content-Type&quot; content="text/html; charset=utf-8">
<title>Anworth Mortgage Asset Corporation - ANH - Get Stock Split
History</title>
<meta name="description" content="Split history for Anworth Mortgage Asset
Corporation. Review with ratios, actual prices and calculator for shares.">
<link rel="shortcut icon" href="/favicon.ico";>
<link type="text/css" href="/style.css" rel="stylesheet">
<script type="text/javascript"
src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></scri
pt>
<script type="text/javascript" src="/script.js"></script>
<script
type="text/javascript">$(document).ready(function(){initStart();startCompany
();});</script&gt;
<script type="text/javascript">

var _gaq = _gaq || [];
_gaq.push([&#39;_setAccount&#39;, 'UA-3329357-5']);
_gaq.push([&#39;_trackPageview']);

(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript';
ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' :
'http://www') + '.google-analytics.com/ga.js&#39;;
var s = document.getElementsByTagName('script&#39;)[0];
s.parentNode.insertBefore(ga, s);
})();

</script>
</head>

<body>
<div id="container&quot;>
<div id="header&quot;><div class="logo"><h1><a href="/">Get Split
History</a></h1><span class="slogan">All Stock Splits on one
Site</span></div><div class="search"><form action="/search"
method="get"><label for="search&quot;>Symbol or Company name</label><input
type="text&quot; name="q" class="input" id="search&quot;> <input type="submit&quot;
value="Search" class="submit"></form></div></div>
<div id="main">
<h2><div style="float:right";></div><span class="symbol">ANH</span> <span
class="company c-blue">Anworth Mortgage Asset Corporation</span></h2><div
class="splits-text";><b>Anworth Mortgage Asset Corporation</b> has had no
splits since its stock began trading publicly.</div><div
style="padding-top:20px"><script type="text/javascript"><!--

Fri Jul 26, 2013 1:41 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Is this what you're going for?

=smfGetTagContent("http://getsplithistory.com/"&K3,"div",0,"splits-text";)

On Fri, Jul 26, 2013 at 11:12 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:

>
> Randy, can you correct my formula. I tried several variation and can't get
> it correct. ****
>
> ** **
>
> Thanks, Kermit****
>
> ** **
>
> I am trying to place the message that follows "splits-text" into a cell.
> The stock symbol is in cell K3****
>
> ** **
>
> Right now I am just trying to find if this stock has had any stock splits.
> ****
>
> ** **
>
> Later I plan to retrieve the list of stock splits. ****
>
> ** **
>
> Using this formula I get #VALUE!****
>
> ** **
>
> =smfGetTagContent("http://getsplithistory.com/",K3,"splits-text";,0)****
>
> ** **
>
> Source for the webpage http://getsplithistory.com/AGNC is****
>
>
>

Fri Jul 26, 2013 2:35 pm (PDT) . Posted by:

"Kermit W. Prather" kermitpra

That will work,

I continue to be amazed at how quickly you respond with the right answer.

I spent more time than that messing with it before taking the easy way out and asking you.

Thanks, Kermit


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, July 26, 2013 4:41 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Need help with formula


Is this what you're going for?

=smfGetTagContent("http://getsplithistory.com/"&K3,"div",0,"splits-text";)
On Fri, Jul 26, 2013 at 11:12 AM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:

Randy, can you correct my formula. I tried several variation and can't get it correct.

Thanks, Kermit

I am trying to place the message that follows "splits-text" into a cell. The stock symbol is in cell K3

Right now I am just trying to find if this stock has had any stock splits.

Later I plan to retrieve the list of stock splits.

Using this formula I get #VALUE!

=smfGetTagContent("http://getsplithistory.com/",K3,"splits-text";,0)

Source for the webpage http://getsplithistory.com/AGNC is


Fri Jul 26, 2013 11:42 am (PDT) . Posted by:

"oryxius" oryxius

Hello Randy,

Would the add-in work on Excel 2013 from the new Office?

Fri Jul 26, 2013 1:42 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I have no experience there, but I do believe we've had a few people say
they've been using it.

Does it have an add-in manager? If so, I would think you'd just add the
add-in there.

On Fri, Jul 26, 2013 at 11:42 AM, oryxius <oryxius@yahoo.com> wrote:

>
> Would the add-in work on Excel 2013 from the new Office?
>

Fri Jul 26, 2013 1:24 pm (PDT) . Posted by:

"quasiwasp" quasiwasp

Thanks for the swift Repsonse!
The "smffixlinks&quot; works perfectly, even we'll have to use it everytime. This was caused by an additional drive on his computer that for some reason references back to his C drive. So the excel file was trying to find: X/:ProgramFiles/C:Program Files
Go figure.

Another quick question -
I tried to use the smfForceRecalculation and it does not work on my bosses. I updated the Internet settings per the "Links" section, but to no avail. I also enabled all Macros.

I have not been able to find a thread where changing the internet settins did not work. Any thoughts?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> That happens because whenever you save a workbook, EXCEL saves the
> hard-coded locations of anything external, including the add-in.
>
> Then, when you open up the workbook, it looks for the add-in in that
> hard-coded location. If it can't find it there, it marks it as an
> "unresolved link" (you should be getting an error message about it when you
> open the workbook). That means you have to manually fix all of those
> unresolved links. However, you can try the smfFixLinks macro that is part
> of the add-in. I use it whenever I have these location problems.
>
> Take a look at the "Location Errors" item in the LINKS area of the Yahoo
> group. It has some additional information.
>
> I don't know what is going on with your RCHGetYahooQuotes() and
> RCHGetElementNumber() transposition. That behavior makes no sense to me.
>
> Someone else recently mentions problems with locating the add-in on
> DropBox. It must not act like a local drive.
>
> On Thu, Jul 25, 2013 at 4:54 PM, quasiwasp <joannapalmerjunk@...>wrote:
>
> > Hi there,
> >
> > I just started using the Add In and it's been going very well, until we
> > installed it on my bosses computer.
> >
> > We installed the add in and deployed it on mine and one supervisors
> > computer. We are both running Windows 7, but he has Office 10 and I have
> > Office 2003.
> >
> > We used the "RCHGetYahooQuotes" primarily to update current stock quotes,
> > changes, and to get Stock names. Even the Refresh Macro
> > (smfForceRecalculation) worked fine.
> >
> > When I installed and ran the add in on my bosses computer, I got nothing
> > but the "NAME?" and "VALUE". Then I realized that the add in was running
> > off of Dropbox (the online document sharing).
> >
> > I routed the add in through C/:Program Files and it worked great. but when
> > I tried to open the same spreadsheet on mine or my supervisors computer, we
> > would get the "NAME?" and "VALUE". I realized that instead of the
> > "RCHGetYahooQuotes" Function, there was an "RCHGetElement" Function. Even
> > though I type, "RCHGetYahooQuotes" if I close the excel sheet and open it
> > on my bosses computer, he receives the "NAME" and "VALUE" and the functions
> > are trying to get answers from the Dropbox File where we originally put the
> > SMF Add In folder.
> >
> > Are there any thoughts on why this is happening/how to fix? I tried
> > deleting the add in on my bosses computer. Perhaps just deleting and
> > reinstalling on all computers?
> >
> > My sincerest apologies if any of this is confusing, i'm not very
> > technically inclined.
> >
>

Fri Jul 26, 2013 1:48 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

NEVER enable all macros. That just opens yourself up to all types of
problems. And you don't need it for an add-in anyway, since adding the
add-in already enabled it for any security issues. So do be careful about
what add-ins you use.

You shouldn't need to update Internet settings for smfForceRecalculation to
work. All it does is empty out the saved web pages that the add-in has
stored, and then trigger a normal EXCEL recalculation. You could achieve
the same result by exiting EXCEL and restarting it, which resets the VBA
environment.

You say it does not work. What did you expect it to do, and what results
did you see?

And, BTW, you shouldn't need to use smfFixLinks every time. As long as the
workbook is saved and opened with the add-in in the same location, it
shouldn't be necessary. Typically, it only needs to be done once, because
after fixing the links, you save the workbook on your own computer and
reopen from there -- in which case the links should be automatically
resolved.

On Fri, Jul 26, 2013 at 1:24 PM, quasiwasp <joannapalmerjunk@gmail.com>wrote:

> Thanks for the swift Repsonse!
> The "smffixlinks&quot; works perfectly, even we'll have to use it everytime.
> This was caused by an additional drive on his computer that for some reason
> references back to his C drive. So the excel file was trying to find:
> X/:ProgramFiles/C:Program Files
> Go figure.
>
> Another quick question -
> I tried to use the smfForceRecalculation and it does not work on my
> bosses. I updated the Internet settings per the "Links" section, but to no
> avail. I also enabled all Macros.
>
> I have not been able to find a thread where changing the internet settins
> did not work. Any thoughts?
>

Fri Jul 26, 2013 2:30 pm (PDT) . Posted by:

"mbadr911" mbadr911

Worked like a charm. Thanks Randy!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> It has numerous tables within tables. Far too complicated for my little
> table parsing routine. Sorry.
>
> With all of those tables, any extraction with other functions would be
> problematic as well.
>
> You best bet might be to use the smfGetTagContent() function and retrieve
> consecutive table cells. See if you can find a pattern that can extract the
> data you want. For example, to get the filer name:
>
> =smfstrExtr(smfGetTagContent("
> http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport","td",18,"s
> name"),"Report&quot;">","<")
>
> ...and then increment that 18 by 28 to get the next one:
>
> =smfstrExtr(smfGetTagContent("
> http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport","td",46,"s
> name"),"Report&quot;">","<")
>
> ...and so forth. But that assumes the coding of the table will be
> consistent from line to line, without exceptions.
>
> On Thu, Jul 25, 2013 at 3:25 AM, mbadr911 <badrmh@...> wrote:
>
> >
> > I am trying to get a table but excel hangs every time i enter the
> > following equation:
> >
> > =RCHGetHTMLTable("
> > http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport
> > "
> > ,"s name",-1,"",3)
> >
>

Fri Jul 26, 2013 4:28 pm (PDT) . Posted by:

"Joel" joelfp2000

Ticker symbol would be PDVYX

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Sample ticker symbol please...
>
> On Thu, Jul 25, 2013 at 8:07 PM, Joel <joel.andrews@...> wrote:
>
> >
> > Question, I am using the quicktake print report (=RCHGetTableCell("
> > http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$A2,1,"average
> > eff maturity"))to try to get the Bond Quality (eg. AAA, AA, A, BBB, etc)
> > and also the Bond Sector Ratings (eg. Government, treasuries, tips, us
> > agency; Corporate, mortgage pass through, etc.). Do you have any idea how
> > I can do this?
> >
>

Fri Jul 26, 2013 4:32 pm (PDT) . Posted by:

"Joel" joelfp2000


Thanks. I reviewed the file but I am looking for mutual fund information. I believe the file you were referring to is just for actual bonds.

Joel
--- In smf_addin@yahoogroups.com, "gbullr" <gaston.bullrich@...> wrote:
>
> Take a look at SMF Bond Price file from me in the links user uploaded files.
>
> --- In smf_addin@yahoogroups.com, "Joel" <joel.andrews@> wrote:
> >
> > Randy,
> >
> > Thanks so much for all of your help with this great add-in!
> >
> > Question, I am using the quicktake print report (=RCHGetTableCell("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$A2,1,"average eff maturity"))to try to get the Bond Quality (eg. AAA, AA, A, BBB, etc) and also the Bond Sector Ratings (eg. Government, treasuries, tips, us agency; Corporate, mortgage pass through, etc.). Do you have any idea how I can do this?
> >
> > Thanks in advance!
> >
> > Joel
> >
>

Fri Jul 26, 2013 4:37 pm (PDT) . Posted by:

"Joel" joelfp2000

Sorry, try this:
=RCHGetTableCell("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=pdvyx",1,"Yield")

Joel

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I didn't get a print page for any of the three there.
>
> On Thu, Jul 25, 2013 at 8:25 PM, gbullr <gaston.bullrich@...> wrote:
>
> > Take a look at SMF Bond Price file from me in the links user uploaded
> > files.
> >
> > --- In smf_addin@yahoogroups.com, "Joel" <joel.andrews@> wrote:
> > >
> > > Randy,
> > >
> > > Thanks so much for all of your help with this great add-in!
> > >
> > > Question, I am using the quicktake print report (=RCHGetTableCell("
> > http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$A2,1,"average
> > eff maturity"))to try to get the Bond Quality (eg. AAA, AA, A, BBB, etc)
> > and also the Bond Sector Ratings (eg. Government, treasuries, tips, us
> > agency; Corporate, mortgage pass through, etc.). Do you have any idea how
> > I can do this?
> > >
> > > Thanks in advance!
> > >
> > > Joel
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
>

Diposting oleh EXCEL FEED LEARNING di 16.37

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)