Sabtu, 15 Oktober 2011

[smf_addin] Digest Number 1986[3 Attachments]

Messages In This Digest (17 Messages)

Messages

1a.

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks

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

Fri Oct 14, 2011 2:10 am (PDT)



Sure. If you have a number of them, just create a sub-folder in the "Uploads
by Forum Members" folder.

On Fri, Oct 14, 2011 at 1:44 AM, <osmangulseven@yahoo.com> wrote:

> Hey Randy, thank you so much. The numbers work pretty well now. The table
> looks pretty cool and the formulas work very nicely. Shall I upload my Excel
> file (that calculates the Fair Value based on a simple discounted earnings
> model, and some other Metrics)in the Files Section?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1b.

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks

Posted by: "osmangulseven@yahoo.com" osmangulseven@yahoo.com   osmangulseven

Fri Oct 14, 2011 9:47 am (PDT)





I automated everything in the file which can be viewed here:
http://f1.grp.yahoofs.com/v1/gFyYTmPIe3FfP4EmjGjGEw4rhCecAGU0t0IbUH7sbuphsg_C8q3t9s3fEn-bBgFUvUvaOxQLX_aywbmT1V97sMpME_eczKtRu8YMhQ/Uploads%20by%20forum%20members/Automated%20Fair-Value%20Estimator.xls

SMF add-in is a great tool. I wonder whether I can use it for extracting data from other web pages. Let's see whether I can use it for non-English websites.

Thank you so much Randy.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Sure. If you have a number of them, just create a sub-folder in the "Uploads
> by Forum Members" folder.
>
> On Fri, Oct 14, 2011 at 1:44 AM, <osmangulseven@...> wrote:
>
> > Hey Randy, thank you so much. The numbers work pretty well now. The table
> > looks pretty cool and the formulas work very nicely. Shall I upload my Excel
> > file (that calculates the Fair Value based on a simple discounted earnings
> > model, and some other Metrics)in the Files Section?
> >
>

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

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks

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

Fri Oct 14, 2011 10:53 am (PDT)



The f1.grp... link is a temporary one that Yahoo creates. I have no idea
why. The permanent URL would be:

http://finance.groups.yahoo.com/group/smf_addin/files/Uploads%20by%20forum%20members/Automated%20Fair-Value%20Estimator.xls

And, yes, you can extract data from other web pages. I even use it to
extract data from NetFlix. The hard thing with non-English websites is
numeric conversion and search strings.

On Fri, Oct 14, 2011 at 9:47 AM, <osmangulseven@yahoo.com> wrote:

>
> I automated everything in the file which can be viewed here:
>
> http://f1.grp.yahoofs.com/v1/gFyYTmPIe3FfP4EmjGjGEw4rhCecAGU0t0IbUH7sbuphsg_C8q3t9s3fEn-bBgFUvUvaOxQLX_aywbmT1V97sMpME_eczKtRu8YMhQ/Uploads%20by%20forum%20members/Automated%20Fair-Value%20Estimator.xls
>
>
> SMF add-in is a great tool. I wonder whether I can use it for extracting
> data from other web pages. Let's see whether I can use it for non-English
> websites.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
2a.

=RCHGetTableCell()

Posted by: "David" nicholasdavid@comcast.net   dnicholas4967

Fri Oct 14, 2011 5:08 am (PDT)



Does someone have the formula for getting the current US10Y Yield?

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

Re: =RCHGetTableCell()

Posted by: "Pete" petekoch@yahoo.com   petekoch

Fri Oct 14, 2011 8:05 am (PDT)



Try '=RCHGetYahooQuotes("^TNX","l1"). That's a lower-case "L" in"l1".

--- In smf_addin@yahoogroups.com, "David" <nicholasdavid@...> wrote:
>
> Does someone have the formula for getting the current US10Y Yield?
>

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

Re: =RCHGetTableCell()

Posted by: "Pete" petekoch@yahoo.com   petekoch

Fri Oct 14, 2011 8:07 am (PDT)



=RCHGetYahooQuotes("^TNX","l1") or RCHGetYahooQuotes("^TNX","l1")/100.
That's a lower-case "L" in"l1".

--- In smf_addin@yahoogroups.com, "David" <nicholasdavid@...> wrote:
>
> Does someone have the formula for getting the current US10Y Yield?
>

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

Re: =RCHGetTableCell()

Posted by: "Pete" petekoch@yahoo.com   petekoch

Fri Oct 14, 2011 10:05 am (PDT)



FWIW, here are some additonal symbols:

^IRX - 90Day T-bill rate
^TYX - 30Year Treasury rate

Also, the following formula will retrieve the 3-Month LIBOR rate:

=RCHGetTablecell("http://www.bankrate.com/rates/interest-rates/libor.aspx?ec_id=msn_ag_libor_msn_brm_ky_standard_k_libor_quotes",1,"3 Month LIBOR")/100

--- In smf_addin@yahoogroups.com, "David" <nicholasdavid@...> wrote:
>
> Does someone have the formula for getting the current US10Y Yield?
>

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

Re: =RCHGetTableCell()

Posted by: "NicholasDavid" nicholasdavid@comcast.net   dnicholas4967

Fri Oct 14, 2011 2:12 pm (PDT)



Thanks Pete, where do I go to learn how to do this. Is a place on the group files that explains the syntax for this. I have all the basic formulas but, no explanation of how to do it. DAvid

----- Original Message -----
From: "Pete" <petekoch@yahoo.com>
To: "smf addin" <smf_addin@yahoogroups.com>
Sent: Friday, October 14, 2011 10:07:14 AM
Subject: [smf_addin] Re: =RCHGetTableCell()

 

=RCHGetYahooQuotes("^TNX","l1") or RCHGetYahooQuotes("^TNX","l1")/100.
That's a lower-case "L" in"l1".

--- In smf_addin@yahoogroups.com , "David" <nicholasdavid@...> wrote:
>
> Does someone have the formula for getting the current US10Y Yield?
>

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

Syntax for using formulas

Posted by: "David" nicholasdavid@comcast.net   dnicholas4967

Fri Oct 14, 2011 5:19 am (PDT)



Where can I find an explanation of the syntax to be used for the various formulas like "=RCHGetTableCell()" for instance. tks

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

Re: Syntax for using formulas

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

Fri Oct 14, 2011 8:14 am (PDT)



In the "Documentation" folder of the files area. Also check out the "Links"
area of the group for tips and FAQ.

On Fri, Oct 14, 2011 at 5:19 AM, David <nicholasdavid@comcast.net> wrote:

> Where can I find an explanation of the syntax to be used for the various
> formulas like "=RCHGetTableCell()" for instance. tks
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4.1.

New file uploaded to smf_addin

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

Fri Oct 14, 2011 9:41 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 : /Uploads by forum members/Fair Value - Automated.xls
Uploaded by : osmangulseven <osmangulseven@yahoo.com>
Description : Automated Fair Value Estimator creates a Fair Value range based on Discounted-Earnings Model. Also offers Graham Margin of Safety and O-Metrix Scores. Dividend Yield and EPS growth estimates are extracted from Morningstar. Rest of the data is from Yahoo Finance.

You can access this file at the URL:
http://groups.yahoo.com/group/smf_addin/files/Uploads%20by%20forum%20members/Fair%20Value%20-%20Automated.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,

osmangulseven <osmangulseven@yahoo.com>


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

New file uploaded to smf_addin

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

Fri Oct 14, 2011 9:43 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 : /Uploads by forum members/Automated Fair-Value Estimator.xls
Uploaded by : osmangulseven <osmangulseven@yahoo.com>
Description : Automated Fair-Value Estimator creates a Fair-Value range based on Discounted-Earnings Model. The code also offers Graham Margin of Safety and O-Metrix Scores. Dividend Yield and EPS growth estimates are extracted from Morningstar. Rest of the data is from Yahoo Finance.

You can access this file at the URL:
http://groups.yahoo.com/group/smf_addin/files/Uploads%20by%20forum%20members/Automated%20Fair-Value%20Estimator.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,

osmangulseven <osmangulseven@yahoo.com>


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

Fair Value - Automated.xls

Posted by: "kiwi2y_98" kiwi2y_98@yahoo.com   kiwi2y_98

Fri Oct 14, 2011 12:04 pm (PDT)



Great work!
Question -- Are the exponents missing in the calculations,
rows 23 thru 27, columns A thru J ?
thanks again.

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

Re: Get data for over 3000 companies?

Posted by: "Ron Spruell" hashky@yahoo.com   hashky

Fri Oct 14, 2011 4:23 pm (PDT)



I would use FinViz . . . if I could get it to work.  I don't have a clue what is causing the #VALUE! error when I go beyond 47 Tickers.  Do you have any idea what might be causing that?  I have tried 2 different computers.  Both get the same results, which is not surprising since both are running XP Pro and Excel 2003.  I have done a Google search and turned up nothing.  What OS are you using and what version of Excel?

I do believe that FinViz is more accurate and reliable than Yahoo Finance.  I have found way too many errors on Yahoo.

I have a minor issue with FinViz, assuming I can get it to work.  Can I get the actual 52-week high and low, rather than the distance between the price and the 52-week high, and the distance between the price and the 52-week low.  I know that I can calculate the actual high and low.  I was surprised that these were not included in the FinViz database.

Using Vlookup is not a problem.

Ron 

>________________________________
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Thursday, October 13, 2011 7:40 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
> 
>I do have one suggestion to speed it up -- in Web_Data, get everything but the pricing data from FinViz. For example, I added this formula:
>
>=smfGetCSVFile("http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A206,",")&"&c="&smfJoin(T6:AG6,","))
>
>...to get the data from FinViz. My results looked something like this:
>
>
>  1 57 58 63 14 16 15 20
>Source Ticker 52-Week
>High 52-Week
>Low Average
>Volume Dividend
>Yield EPS (ttm) Payout
>Ratio EPS growth
>next 5 years
>FinViz ABT -2.10% 18.95% 9365.98 3.68% $3.28 55.6% 8.8%
>Yahoo ABT -3.52% 16.11% 9334110 4.43% $3.28 70.7% 9.1%
>I added the "Source" column and "Yahoo" row for comparison purposes.
>
>From my own calculations, I think all of the data from FinViz is better:
>
> * If I calculate the 52-week high and low, my values are much closer to FinViz.
> * FinViz uses estimated FUTURE dividend yield instead of TRAILING dividend yield.
> * Payout ratio is also based on expected dividend paymentsGoing the FinViz route, even if only to get the 5-year growth rate, would change 200 web page retrievals into a single Internet file retrieval. It doesn't really speed up access of the other items, since those would still have been a single Internet access. But I do think the FinViz numbers are more accurate. You're still stuck with the 200 historical quotes retrievals, as I don't see what can be done to speed up that.
>
>One drawback is that the FinViz returned data is sorted by ticker symbol, so you'd need to use VLOOKUP() to put the data on the other sheet instead of a direct reference.
>
>
>On Thu, Oct 13, 2011 at 4:41 PM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>I used =RCHGetYahooHistory("SPY",,,,,,,,,0) not array entered.  It makes everything right.  The spreadsheet now runs in 4.5 minutes instead of 10.  The updates are very fast after the first run of the day.
>>
>>
>
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (20)
6b.

Re: Get data for over 3000 companies?

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

Fri Oct 14, 2011 4:40 pm (PDT)



I'm using Vista and EXCEL 2007. Are you using the beta version of the add-in
from the "Works in Progress" folder? I can't think of a change that would
affect the process.

The two most common causes I've seen of a #VALUE! error is either the
parameters are of the wrong type (i.e. trying to pass a string instead of an
integer) or one of the fields being returned exceeds the EXCEL length
specification of what can be contained in a worksheet cell.

As far as I know, FinViz only carries the percentages and not dollar
amounts.

On Fri, Oct 14, 2011 at 4:23 PM, Ron Spruell <hashky@yahoo.com> wrote:

>
> I would use FinViz . . . if I could get it to work. I don't have a clue
> what is causing the #VALUE! error when I go beyond 47 Tickers. Do you
> have any idea what might be causing that? I have tried 2 different
> computers. Both get the same results, which is not surprising since both
> are running XP Pro and Excel 2003. I have done a Google search and turned
> up nothing. What OS are you using and what version of Excel?
>
> I do believe that FinViz is more accurate and reliable than Yahoo Finance.
> I have found way too many errors on Yahoo.
>
> I have a minor issue with FinViz, assuming I can get it to work. Can I get
> the actual 52-week high and low, rather than the distance between the price
> and the 52-week high, and the distance between the price and the 52-week
> low. I know that I can calculate the actual high and low. I was surprised
> that these were not included in the FinViz database.
>
> Using Vlookup is not a problem.
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (20)
6c.

Re: Get data for over 3000 companies?

Posted by: "Ron Spruell" hashky@yahoo.com   hashky

Fri Oct 14, 2011 9:27 pm (PDT)

[Attachment(s) from Ron Spruell included below]

I am using the beta version. 
Stock Market Functions add-in, Version 2.1.2011.04.03 (C:\Program Files\SMF Add-In; 1)

I installed the UDF sfJoin and tried it.  No change.  47 Tickers is the working limit.  After that everything turns to #Value!

Here are 3 files.  The first is a pdf with a Ticker input Range of A8:A56 with values.  The second is a pdf with a Ticker input Range of A8:A57 which has the #VALUE! output.  The third is the xls file.

Where should I go from here?

Ron

>________________________________
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Friday, October 14, 2011 6:40 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
> 
>I'm using Vista and EXCEL 2007. Are you using the beta version of the add-in from the "Works in Progress" folder? I can't think of a change that would affect the process.
>
>The two most common causes I've seen of a #VALUE! error is either the parameters are of the wrong type (i.e. trying to pass a string instead of an integer) or one of the fields being returned exceeds the EXCEL length specification of what can be contained in a worksheet cell.
>
>As far as I know, FinViz only carries the percentages and not dollar amounts.
>
>
>On Fri, Oct 14, 2011 at 4:23 PM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>I would use FinViz . . . if I could get it to work.  I don't have a clue what is causing the #VALUE! error when I go beyond 47 Tickers.  Do you have any idea what might be causing that?  I have tried 2 different computers.  Both get the same results, which is not surprising since both are running XP Pro and Excel 2003.  I have done a Google search and turned up nothing.  What OS are you using and what version of Excel?
>>
>>
>>
>>I do believe that FinViz is more accurate and reliable than Yahoo Finance.  I have found way too many errors on Yahoo.
>>
>>
>>I have a minor issue with FinViz, assuming I can get it to work.  Can I get the actual 52-week high and low, rather than the distance between the price and the 52-week high, and the distance between the price and the 52-week low.  I know that I can calculate the actual high and low.  I was surprised that these were not included in the FinViz database.
>>
>>
>>Using Vlookup is not a problem.
>>
>
>
>
>

Attachment(s) from Ron Spruell

3 of 3 File(s)

randy-smfjoin1.pdf
randy-smfjoin2.pdf
randy-smfjoin1.xls
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (20)
6d.

Re: Get data for over 3000 companies? [3 Attachments]

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

Fri Oct 14, 2011 9:42 pm (PDT)



I think it's an EXCEL 2003 problem. I just got rid of the function and kept
just:

="http://finviz.com/export.ashx?v=151&T=
"&smfJoin(A8:A56,",")&"&c="&smfJoin(B6:C6,",")

That string is exactly 255 bytes long. I doubt that is a coincidence. My bet
would be that if that string gets longer than 255 bytes, EXCEL 2003 has a
problem. Try these two functions:

=LEN("http://finviz.com/export.ashx?v=151&T=
"&smfJoin(A8:A56,",")&"&c="&smfJoin(B6:C6,","))
=LEN("http://finviz.com/export.ashx?v=151&T=
"&smfJoin(A8:A66,",")&"&c="&smfJoin(B6:C6,","))

...and see if you get an error on the second LEN() function.

If that is the problem, the easiest solution would be to drop the "t="
parameter and just get a list of all ticker symbols. But that will make your
VLOOKUP() processing a little slower.

On Fri, Oct 14, 2011 at 9:27 PM, Ron Spruell <hashky@yahoo.com> wrote:

>
> I am using the beta version.
> Stock Market Functions add-in, Version 2.1.2011.04.03 (C:\Program Files\SMF
> Add-In; 1)
>
> I installed the UDF sfJoin and tried it. No change. 47 Tickers is the
> working limit. After that everything turns to #Value!
>
> Here are 3 files. The first is a pdf with a Ticker input Range of A8:A56
> with values. The second is a pdf with a Ticker input Range of A8:A57 which
> has the #VALUE! output. The third is the xls file.
>
> Where should I go from here?
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (20)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Get it all here

Breaking news to

entertainment news

Search Ads

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
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Diposting oleh EXCEL FEED LEARNING di 01.57

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)