Jumat, 31 Agustus 2012

[smf_addin] Digest Number 2333

15 New Messages

Digest #2333
1a
Re: S&P 500 historical daily dividend by "Kermit W. Prather" kermitpra
1b
2a
2b
3a
3b
Re: Very slow opening and saving some files by "Randy Harmelink" rharmelink
4a
RCHGetYahooHistory by date by "MoeB" spikemgm
4b
Re: RCHGetYahooHistory by date by "Randy Harmelink" rharmelink
5a
How to get the Morningstar Rating? by "leiliao78@ymail.com" leiliao78@ymail.com
5b
5c
Re: How to get the Morningstar Rating? by "Lei Liao" leiliao78@ymail.com
5d
Re: How to get the Morningstar Rating? by "Randy Harmelink" rharmelink
5e
Re: How to get the Morningstar Rating? by "Lei Liao" leiliao78@ymail.com
6.1
New file uploaded to smf_addin by "smf_addin@yahoogroups.com"

Messages

Fri Aug 31, 2012 7:35 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

You might try extracting it from earnings.com. However, you will need to do each symbol individually. Then parse the data into whatever format you need.

I suspect, This will require some VB code to loop through all 500 symbols.

Kermit

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Thursday, August 30, 2012 11:53 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] S&P 500 historical daily dividend


I don't know of any place off-hand. And daily might be a stretch. I'd suggest using Google (or another search engine) to look for a possible source.
On Thu, Aug 30, 2012 at 6:27 PM, Minh <minh0279@yahoo.ca> wrote:

I am looking for the S&P 500 historical daily dividend. Please let me know if you know how to get it.

Fri Aug 31, 2012 8:39 am (PDT) . Posted by:

"pharnsbe" pharnsbe

Check out the S&P website:
http://www.standardandpoors.com/indices/sp-500/en/us/?indexId=spusa-500-usduf--p-us-l--

If you click on the "Download Index Data" link there's a list of Excel spreadsheets containing historical dividend info.

--- In smf_addin@yahoogroups.com, "Minh" <minh0279@...> wrote:
>
> hi all,
>
> I am looking for the S&P 500 historical daily dividend. Please let me know if you know how to get it.
>
> Thanks
>
> Minh
>

Fri Aug 31, 2012 12:40 pm (PDT) . Posted by:

"Tushar Manna" tusharmanna

Hi Randy,
I am getting Error for this function RCHGetElementNumber(Ticker,25) . Please let me know how to fix this.

Thanks,
Tushar

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, August 30, 2012 11:53 PM
Subject: Re: [smf_addin] S&P 500 historical daily dividend


 
I don't know of any place off-hand. And daily might be a stretch. I'd suggest using Google (or another search engine) to look for a possible source.

On Thu, Aug 30, 2012 at 6:27 PM, Minh <minh0279@yahoo.ca> wrote:

>I am looking for the S&P 500 historical daily dividend. Please let me know if you know how to get it.
>

Fri Aug 31, 2012 1:11 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

See:

http://finance.groups.yahoo.com/group/smf_addin/message/19709
http://finance.groups.yahoo.com/group/smf_addin/message/19783

On Fri, Aug 31, 2012 at 12:40 PM, Tushar Manna <tusharmanna@yahoo.com>wrote:

> I am getting Error for this function RCHGetElementNumber(Ticker,25) .
> Please let me know how to fix this.
>

Fri Aug 31, 2012 3:04 pm (PDT) . Posted by:

"billsadek" billsadek

I have Windows 7 and MS Office 2007. When I open or save some files they are very slow to load. For example one file keeps track of all my options trades, it has about 10 tabs, 5 small pivot tables and no macros. Another file has only two tabs, no pivot tables and no macros. On the other hand, some other files I have open and save with no problems!

I have 6 GB of RAM and Intel Pentium i5 processor. Any help or ideas would be greatly appreciated. I am going crazy... :(

Fri Aug 31, 2012 3:11 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

If you have a lot of add-in functions accessing a large number of web
pages, files will be slow to open -- because they have to recalculate all
of the formulas. And you can't save a file unless all the formulas have
calculated values.

One thing I do on some of my workbooks is make the functions conditional,
such as:

=IF($B$3<>"Y","--",smfGetOptionQuotes(….))

...so it will only retrieve web pages and calculate the
smfGetOptionQuotes() function if cell B3 contains a "Y" string. This allows
me to open the workbook quickly, and decide when I want it to do the
initial calculation. Of course, I have to set it to "N" (or whatever)
before I close the workbook.

If you have a lot of RCHGetYahooQuotes() functions in individual cells, I'd
suggest getting as many data items as possible in one Internet access by
array-entering the functions once over a range. Each invocation of that
function requires its own Internet access. So if you can get 10 data items
for 200 ticker symbols in one shot, it would save you 1999 Internet
accesses -- and speed things up tremendously.

On Fri, Aug 31, 2012 at 3:02 PM, billsadek <billsadek@gmail.com> wrote:

> I have Windows 7 and MS Office 2007. When I open or save some files they
> are very slow to load. For example one file keeps track of all my options
> trades, it has about 10 tabs, 5 small pivot tables and no macros. Another
> file has only two tabs, no pivot tables and no macros. On the other hand,
> some other files I have open and save with no problems!
>
> I have 6 GB of RAM and Intel Pentium i5 processor. Any help or ideas would
> be greatly appreciated. I am going crazy... :(
>

Fri Aug 31, 2012 4:24 pm (PDT) . Posted by:

"billsadek" billsadek

Randy: Thanks for your feed back. Surprisingly, I have no formulas accessing the web. I mean none, which is why I'm going crazy!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> If you have a lot of add-in functions accessing a large number of web
> pages, files will be slow to open -- because they have to recalculate all
> of the formulas. And you can't save a file unless all the formulas have
> calculated values.
>
> One thing I do on some of my workbooks is make the functions conditional,
> such as:
>
> =IF($B$3<>"Y","--",smfGetOptionQuotes(….))
>
> ...so it will only retrieve web pages and calculate the
> smfGetOptionQuotes() function if cell B3 contains a "Y" string. This allows
> me to open the workbook quickly, and decide when I want it to do the
> initial calculation. Of course, I have to set it to "N" (or whatever)
> before I close the workbook.
>
> If you have a lot of RCHGetYahooQuotes() functions in individual cells, I'd
> suggest getting as many data items as possible in one Internet access by
> array-entering the functions once over a range. Each invocation of that
> function requires its own Internet access. So if you can get 10 data items
> for 200 ticker symbols in one shot, it would save you 1999 Internet
> accesses -- and speed things up tremendously.
>
> On Fri, Aug 31, 2012 at 3:02 PM, billsadek <billsadek@...> wrote:
>
> > I have Windows 7 and MS Office 2007. When I open or save some files they
> > are very slow to load. For example one file keeps track of all my options
> > trades, it has about 10 tabs, 5 small pivot tables and no macros. Another
> > file has only two tabs, no pivot tables and no macros. On the other hand,
> > some other files I have open and save with no problems!
> >
> > I have 6 GB of RAM and Intel Pentium i5 processor. Any help or ideas would
> > be greatly appreciated. I am going crazy... :(
> >
>

Fri Aug 31, 2012 7:37 pm (PDT) . Posted by:

"MoeB" spikemgm

Hi Randy,

I'm using this one you posted for average volume for 10days,and it works great.

=AVERAGE(RCHGetYahooHistory("SPY",,,,,,,,"V",0,,,10,1))

Now I'm trying to get just the open price for spy 10 days ago.
I removed AVERAGE and replaced V with "O",,,,10)) and it brings up todays open.

Do i need to use the smfpricesbydates? something like (opentoday-9).

So, the data will change everyday.

Thanks
Steve

Fri Aug 31, 2012 8:03 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Either of the following should do the trick:

=RCHGetYahooHistory("SPY",,,,,,,,"O",0,,1,10,1)
=INDEX(RCHGetYahooHistory("SPY",,,,,,,,"O",0,,,10,1),10,1)

On Fri, Aug 31, 2012 at 7:37 PM, MoeB <smoebius@gmail.com> wrote:

>
> I'm using this one you posted for average volume for 10days,and it works
> great.
>
> =AVERAGE(RCHGetYahooHistory("SPY",,,,,,,,"V",0,,,10,1))
>
> Now I'm trying to get just the open price for spy 10 days ago.
> I removed AVERAGE and replaced V with "O",,,,10)) and it brings up todays
> open.
>
> Do i need to use the smfpricesbydates? something like (opentoday-9).
>
> So, the data will change everyday.
>

Fri Aug 31, 2012 8:00 pm (PDT) . Posted by:

"leiliao78@ymail.com" leiliao78@ymail.com

Hi Randy (and all others),

Can you tell me how to retrieve the Mstar Grades from its website? e.g. Getting the Fair Value-Uncertainty,Economic Moat-Narrow, Stewardship- Standard for AAPL on this link: http://quote.morningstar.com/stock/s.aspx?t=AAPL, I don't know how to do that.

P.S. I only found the # for getting Growth, profitability and Financial Health Grade.

Thanks

Fri Aug 31, 2012 8:36 pm (PDT) . Posted by:

"Randy H" rharmelink

They can't be retrieved from that web page, because they're dynamically
placed onto the web page.

However, they are available on the print report -- I just uploaded this
template:

http://finance.groups.yahoo.com/group/smf_addin/files/Templates%20and%20\
Examples/RCHGetTableCell/RCHGetTableCell-Template-Morningstar-Stock-Grad\
es.xls


On Fri, Aug 31, 2012 at 8:00 PM, leiliao78@ymail.com
<liaolei78@gmail.com <mailto:liaolei78@gmail.com> > wrote:

Can you tell me how to retrieve the Mstar Grades from its website? e.g.
Getting the Fair Value-Uncertainty,Economic Moat-Narrow, Stewardship-
Standard for AAPL on this link:
http://quote.morningstar.com/stock/s.aspx?t=AAPL
<http://quote.morningstar.com/stock/s.aspx?t=AAPL> , I don't know how to
do that.

P.S. I only found the # for getting Growth, profitability and Financial
Health Grade.

Fri Aug 31, 2012 8:50 pm (PDT) . Posted by:

"Lei Liao" leiliao78@ymail.com

Thanks Randy, I got it, and it works fine.

Just one more question, I am the premium subscriber on Mstar, and how to
get the Premium content to be shown in excel other than just showing the
"Premium" when I extract them.

Thanks,
Lei

Fri Aug 31, 2012 8:52 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You need to log in to MorningStar with either the EXCEL Web Query dialog
(preferred) or with IE, so that EXCEL has the proper security cookies
available for the add-in to access the premium data. Logging in with
another browser like FireFox won't do it.

On Fri, Aug 31, 2012 at 8:49 PM, Lei Liao <liaolei78@gmail.com> wrote:

>
> Just one more question, I am the premium subscriber on Mstar, and how to
> get the Premium content to be shown in excel other than just showing the
> "Premium" when I extract them.
>

Fri Aug 31, 2012 9:20 pm (PDT) . Posted by:

"Lei Liao" leiliao78@ymail.com

Hi Randy, I tried logging in thru excel web query, and nothing changed.

Here is what I did:

1. I opened a new web query
2. I typed the www. morningstar.com in the address
3. I logged in as a premium account holder

Is there anything else I still need to do to get the data on
premium account to be shown in excel?

Thanks
Lei

Fri Aug 31, 2012 8:33 pm (PDT) . Posted by:

"smf_addin@yahoogroups.com"


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/RCHGetTableCell/RCHGetTableCell-Template-Morningstar-Stock-Grades.xls
Uploaded by : rharmelink <rharmelink@gmail.com>
Description : A template that shows how to grab the Morningstar grades and premium items.

You can access this file at the URL:
http://groups.yahoo.com/group/smf_addin/files/Templates%20and%20Examples/RCHGetTableCell/RCHGetTableCell-Template-Morningstar-Stock-Grades.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>


READ MORE....