Selasa, 25 Februari 2014

[smf_addin] Digest Number 2982

15 Messages

Digest #2982
3.3
Re: Possible SMF Web Site? by "Randy Harmelink" rharmelink
4b
Re: Argus & S&P Information by "Randy Harmelink" rharmelink
5b

Messages

Tue Feb 25, 2014 5:21 am (PST) . Posted by:

bushpilote

Thanks Randy. How did you determine the first offset besides trial and error?

Tue Feb 25, 2014 7:45 am (PST) . Posted by:

"Randy Harmelink" rharmelink

I knew the thing that was being searched for was there, so the only thing I
could think of was that the location had to be further out than it was
before. On some workbooks, I start from the top and use 32767-byte chunks
(the larger I can use), until it finds it. this one has it hard-coded.

On Tue, Feb 25, 2014 at 6:21 AM, <bushpilote@yahoo.ca> wrote:

>
> Thanks Randy. How did you determine the first offset besides trial and
> error?
>

Tue Feb 25, 2014 7:03 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

I have not used SMF to pull data from AAII. However, I see no reason why it should not work. Just like any premium site you will need to logon first then pull the data.

I am a lifetime member of AAIII but that's only because I use it to get my 7 adult children interested in their retirement future. I haven't found to many problems with AAII data.
With the exception of their Portfolio performance. Their figures are way off based on my calculations using the enter and exit data. However, if you are a buy and hold investor their portfolios are very good over a long period.
I do follow their portfolio changes and make recommendations to my 7 adult children when I think it fits their style of investing.

Myself, I am a short term trader not an investor.

I would hate to think that either Morningstar or MSN are putting out bad stats but nobody is perfect. That's why I use multiple sources and compare the data.

I still cannot use SMF to access MSN I get "Error" on most all elements. So I gave up trying to use it. I have 5 computers with SMF installed and each one get different MN results.

I will say though I DO NOT trust Yahoo data. Way to many mistakes in it. It is my last choice for data. FREE data is not always the best data.

BTW, In my opinion Stock Selection is the easiest part of trading. If you manage your "Risk" properly you can still make money even with the wrong stocks. But not if you are consistently picking bad stocks.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of rr76012@yahoo.com
Sent: Monday, February 24, 2014 6:50 PM
To: smf_addin@yahoogroups.com
Subject: RE: [smf_addin] Re: Morningstar dynamic pages - element list update planned?


Hi Kermit,

Thanks AAII looks great. I am trying to get a reasonable eps and dividend growth rate to attempt to come up with a reasonable fv. I think AAII website might help.

Kermit does it work with SMF, I think I have most of a very simple spreadsheet working for me with smf getting the future growth rates for eps and dividends.

Kermit, I keep wondering if the forecast eps and dividend growth rate numbers on morningstar and msn are reasonable or not, what do you think?

Kermit thanks for you help. Have a great week. Best wishes with your stock selections.

rr76012

Tue Feb 25, 2014 7:49 am (PST) . Posted by:

"Randy Harmelink" rharmelink

AAII just uses data from Reuters, so it's still the same "free" data you
see on the Reuters website. AAII just charges for packaging it into a
screening product.

You shouldn't be getting "Error" on MSN elements if you're using the
current element definitions.

On Tue, Feb 25, 2014 at 8:03 AM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:

>
> I have not used SMF to pull data from AAII. However, I see no reason why
> it should not work. Just like any premium site you will need to logon first
> then pull the data.
>
>
> I would hate to think that either Morningstar or MSN are putting out bad
> stats but nobody is perfect. That's why I use multiple sources and compare
> the data.
>
>
>
> I still cannot use SMF to access MSN I get "Error" on most all elements.
> So I gave up trying to use it. I have 5 computers with SMF installed and
> each one get different MN results.
>
>
>
> I will say though I DO NOT trust Yahoo data. Way to many mistakes in it.
> It is my last choice for data. FREE data is not always the best data.
>
>
>
> BTW, In my opinion Stock Selection is the easiest part of trading. If
> you manage your "Risk" properly you can still make money even with the
> wrong stocks. But not if you are consistently picking bad stocks.
>
>
>

Tue Feb 25, 2014 8:00 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

Randy, I agree with you. I should not be getting errors on MSN elements

But I am, Why I do not know. We went through this last year with no resolution.

I finally just gave up trying to figure it out. There are other places to get the data I need.

Maybe someday I'll try again to use MSN but not now.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, February 25, 2014 10:49 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Morningstar dynamic pages - element list update planned?


AAII just uses data from Reuters, so it's still the same "free" data you see on the Reuters website. AAII just charges for packaging it into a screening product.
You shouldn't be getting "Error" on MSN elements if you're using the current element definitions.

On Tue, Feb 25, 2014 at 8:03 AM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:

I have not used SMF to pull data from AAII. However, I see no reason why it should not work. Just like any premium site you will need to logon first then pull the data.

I would hate to think that either Morningstar or MSN are putting out bad stats but nobody is perfect. That's why I use multiple sources and compare the data.

I still cannot use SMF to access MSN I get "Error" on most all elements. So I gave up trying to use it. I have 5 computers with SMF installed and each one get different MN results.

I will say though I DO NOT trust Yahoo data. Way to many mistakes in it. It is my last choice for data. FREE data is not always the best data.

BTW, In my opinion Stock Selection is the easiest part of trading. If you manage your "Risk" properly you can still make money even with the wrong stocks. But not if you are consistently picking bad stocks.



Tue Feb 25, 2014 12:57 pm (PST) . Posted by:

mikemcq802

Just looked at the updated pages and overall I really like where you are headed.
I have a variety of comments which I placed in a 1-page Word document. It was easier for me than typing it here and should be easier for you to read. It's here:
https://s3.amazonaws.com/mlweb/SMF+Feedback+2014Feb25.docx https://s3.amazonaws.com/mlweb/SMF+Feedback+2014Feb25.docx
Thanks again Randy for all you do.

Tue Feb 25, 2014 1:04 pm (PST) . Posted by:

mikemcq802

That link didn't download correctly once posted in the group. Here's a link to a pdf of it in case you have problems:
https://s3.amazonaws.com/mlweb/SMF_Feedback_2014Feb25.pdf https://s3.amazonaws.com/mlweb/SMF_Feedback_2014Feb25.pdf

Tue Feb 25, 2014 4:11 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

On Tue, Feb 25, 2014 at 2:04 PM, <mikemcq802@yahoo.com> wrote:
>
> That link didn't download correctly once posted in the group. Here's a
link to a pdf of it in case you
> have problems:
>
> https://s3.amazonaws.com/mlweb/SMF_Feedback_2014Feb25.pdf

Thanks for the feedback!

> SMF Feedback, MikeMcQ802, Feb 25, 2014
>
> Current Intro:
>
> The SMF (Stock Market Functions) add-in contains a number of user defined
functions for EXCEL that can
> allow data to be extracted and/or retrieved from the web and placed
directly into EXCEL cells or ranges. This
> website is a repository of data files -- templates, documentation, FAQs,
etc -- related to using the add-in.
>
> Suggested Intro:
>
> The SMF (Stock Market Functions) add-in allows data to be retrieved from
web pages and placed directly
> into EXCEL cells or ranges. This website contains the add-in and
supporting files – documentation, example
> templates, FAQs, etc.

Hmmm. I always like to emphasize that the primary nature of the add-in is
functions, not macros.

I'm thinking each web page should have a similar introduction on it.

> Current Documentation overview:
>
> A folder containing the documentation of the various user-defined
functions available In the add-in
>
> Suggested Documentation overview:
>
> Describes the installation process and the use of each of the functions

I was actually thinking of removing the installation document from that
folder, and maybe putting it on the top level web page. Then that folder
would just be documentation for each function. It would also highlight the
installation document, having it right on the front page. Or maybe it
should just be a "readme.txt" file within the ZIP archive...

> The Templates, Tips and FAQs, and WIP overviews can be shorted similarly.
Mainly, by removing "this is a
> folder" language which is redundant and describing its purpose to the
user rather than the contents:
>
> Templates: Example workbooks demonstrating various add-in functions and
data sources
> Tips and FAQs: Tips for common problems and frequently asked questions
> Works In Progress: Updates since the last major update of the add-in

Yeah, I do have a lot of extraneous "This is a ..." sentence lead-ins. I
may be too comfortable with them. :)

> Documentation Folder
>
> I don't see the value for size and last-modified date on this page.
Presumably the documentation is always
> the most current available which is all anyone really cares about. I
like the categories.

I didn't really see a need, but from what Tony was saying, it sounds like
maybe the last modification date, and maybe a new column describing the
update, might be useful. But that would be a lot of info on the page. I was
thinking it would be more useful to have a quick example of each function
directly on that index page.

> Elements Folder
>
> I don't like this being a folder listed equally with the others. These
are add-in files, not supporting files.
> I think a folder named "Add-In" or "Install Files" would be better and
containing the latest official release
> plus element updates.

One reason it's a separate folder is I may be using those files directly
from the add-in. Or maybe have a way that people can choose whether they
use their local or remote files.

The size of the AdvFN file concerns me, when using it as a remote file.

Maybe they should be in the WIP folder, since they all are constant works
in progress. :)

> Templates, Tips, and WIP
>
> I like the direction you are taking with these. Far better than the
Yahoo collections.

But a lot of work, since I'm envisioning writing a new document for each
Tip/FAQ, because I don't want to just point to the Yahoo group messages any
more. The Yahoo group no longer renders their HTML markup properly. And
some of the messages don't address the general issue, just a specific
related question.

> Other
>
> I see many people struggle with array-entering formulas. Perhaps in the
documentation you can have a link
> whenever that word appears to take them to an example showing how to do
that. As one example, you might
> want to relocate the "Usage Notes" from the YahooQuotes function to the
top of its page. Then, add a link
> for array-entered to a new Tip entry dedicated just to that.

DRossien addressed a number of issues like that that would be good for the
new person to know:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/24946

I've been trying to decide if that should be in the new installation
document, or posted as a separate document. Again, maybe one that should be
on the top level page.

Tue Feb 25, 2014 4:26 pm (PST) . Posted by:

mikemcq802

"I always like to emphasize that the primary nature of the add-in is functions, not macros."
Fair enough, then just say "functions&quot;. I am mostly concerned with the overly technical description of what is happening in what you should a brief summary. New users won't appreciate what "user defined functions" means. That's only meaningful to Excel wizards and doesn't adequately express how much SMF simplifies that with its provided functions.

Yes, I like DRRossien's bits too but I don't think new users meander through all the Tips and bits - they just want to get something done quickly. If each time you had the phrase "array formula" in the docs if it was a hyperlink to his bits or something new they could find it more readily.

Tue Feb 25, 2014 3:14 pm (PST) . Posted by:

cgrablew

Randy,

I have a spreadsheet that I need to update manually with Argus rating (opinion) and target price as well as S&P rating (opinion) and target price. I currently go through the Charles Schwab site to get the information but it's tedious.I have about 250 stocks to update so it's time consuming as well. Do you know of anyway I could get the information using SMF functions?


Thanks,


Chuck Grablewski

Tue Feb 25, 2014 3:43 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I'm assuming you'd need to create an IE security cookie to get to the data
with EXCEL and the add-in. You'll also need to have bookmarkable URL's.
That is, web addresses that get you directly to a web page containing the
data. Beyond that, I don't think I can be of much help...

If you attach a copy of the source code of one web page, I can try and see
if I can extract something.

On Tue, Feb 25, 2014 at 4:14 PM, <cgrablew@gmail.com> wrote:

>
> I have a spreadsheet that I need to update manually with Argus rating
> (opinion) and target price as well as S&P rating (opinion) and target
> price. I currently go through the Charles Schwab site to get the
> information but it's tedious.I have about 250 stocks to update so it's time
> consuming as well. Do you know of anyway I could get the information
> using SMF functions?
>

Tue Feb 25, 2014 4:03 pm (PST) . Posted by:

mikemcq802

The url is bookmarkable and the html lends itself to an SMF GetTagContent lookup. However, the security credentials are another matter and I was not able to find a workable solution.

I tried logging on and staying logged on in IE - no help. I tried starting an Excel Web Query but that didn't help either as you must exit that to return to the spreadsheet to enter SMF functions. Schwab does not have a persistent login, it is only per-session - which is where the problem lies I suspect.

Tue Feb 25, 2014 8:01 pm (PST) . Posted by:

joeyu51788

I've disabled auto calculate in my Excel 2013 and have enabled all processors to run calculations. Granted my system maxes out at 4 processors but I've noticed that when running calculate sheet updates, the process is tremendously slow.

Sample sheet:
Market Cap - =RCHGetElementNumber(XX,941)/1000
Price - =smfpricesbydates(XX,Today&#39;s Date)
Price Target - =RCHGetTableCell("http://finance.yahoo.com/q/ao?s=XXXX",1,">Mean Target")


The sheet contains a list of about 75 tickers.


Any thoughts would be greatly appreciated.


Tue Feb 25, 2014 8:16 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

For 75 tickers, you're accessing the Internet 300 times with those three
formulas. So I suspect it would be slow. Especially since, the last month
or so, people have been complaining about Yahoo being slower than normal.

You can get the same 225 data items in a single Internet access with this
formula, array-entered over a range (adjust the range as needed for your
ticker symbols -- you can have up to 200 of them):

=RCHGetYahooQuotes(B4:B78,"l1j1t8")

I don't know who spread the idea of using the smfPricesByDates() function
to get the current day quote. Several people lately have said they use that
method. That is a VERY bad way to go about it. During the day, it will
first get the historical quotes for the ticker, then search it for the
current date, not find it, and then issue another SMF function to get the
data:

=RCHGetYahooQuotes(XX,"l1")

...so you end up with two Internet accesses just to get that piece of data.
Not only that, but using a volatile date function makes EVERY one of those
formulas volatile. That means your worksheet will recalculate every time
you do something on it. I suspect that is why you are using manual
calculation.

That array-entered function will get all of the data almost instantaneously.

On Tue, Feb 25, 2014 at 8:17 PM, <joeyu51788@yahoo.com> wrote:

>
> I've disabled auto calculate in my Excel 2013 and have enabled all
> processors to run calculations. Granted my system maxes out at 4 processors
> but I've noticed that when running calculate sheet updates, the process is
> tremendously slow.
>
> Sample sheet:
> Market Cap - =RCHGetElementNumber(XX,941)/1000
> Price - =smfpricesbydates(XX,Today&#39;s Date)
> Price Target - =RCHGetTableCell("http://finance.yahoo.com/q/ao?s=XXXX",1,">Mean
> Target")
>
> The sheet contains a list of about 75 tickers.
>
> Any thoughts would be greatly appreciated.
>

Tue Feb 25, 2014 9:01 pm (PST) . Posted by:

gryemann

Hello,
I am working through smfGetOptionQuotes-Examples-Table-Building-Exercise. I have:B2: SPYB3: OX
B5: =smfGetOptionExpiry() which evaluates to 2014-03-22 C9:C18 =smfGetOptionStrikes($B$2,$B$5,"Call&quot;,$B$3,1). C9 evaluates to "Invalid data source: OX" and c10:c18 appear as empty.
B20: =smfGetOptionStrikes($B$2,$B$5,"Call&quot;,$B$3,,2,1). It evaluates to "Invalid data source"
D8:F8 =smfGetOptionQuotes("",D$7,1). These evaluate to "Last Price", "Bid Price", "Ask Price"
D9:F18 =smfGetOptionQuotes($C9,D$7,,$B$3) appear as empty.
I am using excel 2010.

I am guessing the "OX" in B3 is in error. What can I try instead.
Any other ideas from the above?
Thanks.

JB
Diposting oleh EXCEL FEED LEARNING di 21.01

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)