Jumat, 14 Oktober 2011

[smf_addin] Digest Number 1985[2 Attachments]

Messages In This Digest (10 Messages)

Messages

1a.

Re: Get data for over 3000 companies?

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

Thu Oct 13, 2011 4:36 am (PDT)



I am still getting the #VALUE! error.  

This is the only spreadsheet I have open.  I wonder if this is the difference between Excel 2003 (Windows XP) and what ever you are running?

>________________________________
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Monday, October 10, 2011 11:46 AM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>

>I don't see any #VALUE! output on this end. I changed B8:B52 to B8:B457 and still no #VALUE! output.
>
>However, I do have two items to take note of:
>
> * FinViz output is returned sorted by ticker symbol, so having things side by side like that probably isn't giving you the results you want.
> * I'm only seeing 444 stocks returned instead of 450. I suspect ticker symbols that aren't recognized/used by FinViz.I think what you'll need to do is put the FinViz output in a separate worksheet and then use VLOOKUP() functions to place it into your data analysis grid.
>
>
>On Mon, Oct 10, 2011 at 5:05 AM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>Here is the spreadsheet with the #VALUE! output.  FinViz-temp1-value.xls The array is in cells H7:Q457.   The equation is
>>=smfGetCSVFile("http://finviz.com/export.ashx?v=151&T="&smfJoin(B8:B52,",")&"&c="&smfJoin(H6:Q6,","))
>>
>>
>>
>>I can make the #VALUE! go away by deleting the value in Cell Q6.  And I can make it return by changing the B8:B52 to B8:B53.
>>
>
>
>
>
1b.

Re: Get data for over 3000 companies?

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

Thu Oct 13, 2011 4:59 am (PDT)



I am working on modifying my spreadsheets to not use the volatile commands NOW() and TODAY() for references in calculations.  It's a big job because I had many spreadsheet that are dependent on TODAY() or NOW().

I tried using d1 as you suggested in =RCHGetYahooQuotes("spy","d1").  This changes when the market opens, so I have a correct output before the market opens, or after the market opens, but not both.  Date of the Previous Close is what I would like to have, but I couldn't find it in the Yahoo database.

Would it be possible to use something like =INT(NOW()), or would this result in a volatile function?

From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Monday, October 10, 2011 11:59 AM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>

>The 10 minutes of recalculation is because cell E5 in worksheet Web_Data refers to cell F2 of worksheet Main, which contains the NOW() function. That means EVERY time you change something in the workbook, the value of NOW() changes, triggering a recalculation of all of those smfPricesByDates() functions that use it as one of the dates being retrieved.
>
>If you always plan to get the current closing price, I would drop that date from the smfPricesByDates() function and replace it with an array-entered RCHGetYahooQuotes() and just get the current close from it. You're already using that function for other data items, so it wouldn't really change it much to add one additional data element.
>
>Also, all of your smfPricesBetween() use the TODAY() function, which also makes them volatile and recalculate with every change.
>
>
>On Mon, Oct 10, 2011 at 5:05 AM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>I have also attached Quality Stocks-01.01.xls.  This is a busy spreadsheet that takes about 10 minutes to calculate (SMFForceRecalculation).  I calculate it once per day.  After that, at the Main Tab, I just calculate the Main sheet (Shift-F9) which has a minimum of Internet calls.  It only take a few seconds to update.
>>
>
>
>
>
1c.

Re: Get data for over 3000 companies?

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

Thu Oct 13, 2011 5:15 am (PDT)



Or, I could use =RCHGetYahooHistory("SPY",,,,,,,,,0) not array entered.

>________________________________
>From: Ron Spruell <hashky@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, October 13, 2011 6:59 AM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>

>I am working on modifying my spreadsheets to not use the volatile commands NOW() and TODAY() for references in calculations.  It's a big job because I had many spreadsheet that are dependent on TODAY() or NOW().
>
>
>I tried using d1 as you suggested in =RCHGetYahooQuotes("spy","d1").  This changes when the market opens, so I have a correct output before the market opens, or after the market opens, but not both.  Date of the Previous Close is what I would like to have, but I couldn't find it in the Yahoo database.
>
>
>Would it be possible to use something like =INT(NOW()), or would this result in a volatile function?
>
>
>From: Randy Harmelink <rharmelink@gmail.com>
>>To: smf_addin@yahoogroups.com
>>Sent: Monday, October 10, 2011 11:59 AM
>>Subject: Re: [smf_addin] Get data for over 3000 companies?
>>
>>
>> 
>>The 10 minutes of recalculation is because cell E5 in worksheet Web_Data refers to cell F2 of worksheet Main, which contains the NOW() function. That means EVERY time you change something in the workbook, the value of NOW() changes, triggering a recalculation of all of those smfPricesByDates() functions that use it as one of the dates being retrieved.
>>
>>If you always plan to get the current closing price, I would drop that date from the smfPricesByDates() function and replace it with an array-entered RCHGetYahooQuotes() and just get the current close from it. You're already using that function for other data items, so it wouldn't really change it much to add one additional data element.
>>
>>Also, all of your smfPricesBetween() use the TODAY() function, which also makes them volatile and recalculate with every change.
>>
>>
>>On Mon, Oct 10, 2011 at 5:05 AM, Ron Spruell <hashky@yahoo.com> wrote:
>>
>>
>>>
>>>I have also attached Quality Stocks-01.01.xls.  This is a busy spreadsheet that takes about 10 minutes to calculate (SMFForceRecalculation).  I calculate it once per day.  After that, at the Main Tab, I just calculate the Main sheet (Shift-F9) which has a minimum of Internet calls.  It only take a few seconds to update.
>>>
>>
>>
>>
>
>
>
1d.

Re: Get data for over 3000 companies?

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

Thu Oct 13, 2011 4:42 pm (PDT)

[Attachment(s) from Ron Spruell included below]

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.

Ron

>________________________________
>From: Ron Spruell <hashky@yahoo.com>
>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>Sent: Thursday, October 13, 2011 7:14 AM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>

>Or, I could use =RCHGetYahooHistory("SPY",,,,,,,,,0) not array entered.
>
>
>
>
>
>>________________________________
>>From: Ron Spruell <hashky@yahoo.com>
>>To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
>>Sent: Thursday, October 13, 2011 6:59 AM
>>Subject: Re: [smf_addin] Get data for over 3000 companies?
>>
>>
>> 
>>I am working on modifying my spreadsheets to not use the volatile commands NOW() and TODAY() for references in calculations.  It's a big job because I had many spreadsheet that are dependent on TODAY() or NOW().
>>
>>
>>I tried using d1 as you suggested in =RCHGetYahooQuotes("spy","d1").  This changes when the market opens, so I have a correct output before the market opens, or after the market opens, but not both.  Date of the Previous Close is what I would like to have, but I couldn't find it in the Yahoo database.
>>
>>
>>Would it be possible to use something like =INT(NOW()), or would this result in a volatile function?
>>
>>
>>From: Randy Harmelink <rharmelink@gmail.com>
>>>To: smf_addin@yahoogroups.com
>>>Sent: Monday, October 10, 2011 11:59 AM
>>>Subject: Re: [smf_addin] Get data for over 3000 companies?
>>>
>>>
>>> 
>>>The 10 minutes of recalculation is because cell E5 in worksheet Web_Data refers to cell F2 of worksheet Main, which contains the NOW() function. That means EVERY time you change something in the workbook, the value of NOW() changes, triggering a recalculation of all of those smfPricesByDates() functions that use it as one of the dates being retrieved.
>>>
>>>If you always plan to get the current closing price, I would drop that date from the smfPricesByDates() function and replace it with an array-entered RCHGetYahooQuotes() and just get the current close from it. You're already using that function for other data items, so it wouldn't really change it much to add one additional data element.
>>>
>>>Also, all of your smfPricesBetween() use the TODAY() function, which also makes them volatile and recalculate with every change.
>>>
>>>
>>>On Mon, Oct 10, 2011 at 5:05 AM, Ron Spruell <hashky@yahoo.com> wrote:
>>>
>>>
>>>>
>>>>I have also attached Quality Stocks-01.01.xls.  This is a busy spreadsheet that takes about 10 minutes to calculate (SMFForceRecalculation).  I calculate it once per day.  After that, at the Main Tab, I just calculate the Main sheet (Shift-F9) which has a minimum of Internet calls.  It only take a few seconds to update.
>>>>
>>>
>>>
>>>
>>
>>
>
>
>

Attachment(s) from Ron Spruell

1 of 1 File(s)

1e.

Re: Get data for over 3000 companies? [1 Attachment]

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

Thu Oct 13, 2011 5:41 pm (PDT)



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 payments

Going 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.
>
>
2a.

Extracting Fundamental Data from Morningstar/Finviz/Zacks

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

Thu Oct 13, 2011 7:54 am (PDT)

[Attachment(s) from osman gulseven included below]

Hello everyone,

I am pretty new to the forum, as well as, to the SMF Add-in. I am trying to figure out, how can I extract data to use in Excel models. I am using a simple model based on trailing twelve month EPS, forward EPS, 5 Year EPS Growth Estimates, Book Value, and the Current Price. Ttm P/E ratio, forward P/E ratio and dividend yield will also be great. 
Zacks or Finviz is okay when it comes to estimates, but Morningstar is more reliable. I used to do this manually before. I tried extracting the data myself, but it did not work as I expected. 
I know, that I should be able to get this information from here, but I do not know how:

http://quicktake.morningstar.com/stocknet/PrintReport.aspx?Country=USA&Symbol=t

Although I have checked every file, and looked at all the links, I still could not figure out how to to get them. Basically, I just need the numbers shaded by yellow in the attached Excel File.

Ttm P/E Ratio,
Forward P/E Ratio,
EPS or Income Growth Estimate for Next 5 years,
Dividend Yield,
Ttm EPS, 
Forward EPS Estimate,
Book Value Per Share, 
(If available) Bond Yield,
Current Stock Price.

I am hoping to use these numbers to estimate the Fair Values, O-Metrix Scores, and Margin of Safety as suggested here:
http://www.efsinvestment.com/index.php/investment-philosophy.html

If Morningstar is not available, data from other resources is also welcome. Any help will be greatly appreciated. 

Attachment(s) from osman gulseven

1 of 1 File(s)

2b.

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks [1 Att

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

Thu Oct 13, 2011 8:12 am (PDT)



Take a look at the RCHGetTableCell() function. You'll need to look at the
source code of the web page for appropriate search strings. As an example,
to extract the P/E from the web page you cite below:

=RCHGetTableCell("
http://quicktake.morningstar.com/stocknet/PrintReport.aspx?Country=USA&Symbol=t
",1,">Price/Earnings")

Note that you may need to visit the Morningstar web site with either the
EXCEL Web Query dialog (preferred) or with IE. They sometimes do
redirections on the first visit of the day. By visiting the site with an IE
app, you'll create a cookie that bypasses that redirection.

On Thu, Oct 13, 2011 at 7:37 AM, osman gulseven <osmangulseven@yahoo.com>wrote:

>
> I am pretty new to the forum, as well as, to the SMF Add-in. I am trying to
> figure out, how can I extract data to use in Excel models. I am using a
> simple model based on trailing twelve month EPS, forward EPS, 5 Year EPS
> Growth Estimates, Book Value, and the Current Price. Ttm P/E ratio, forward
> P/E ratio and dividend yield will also be great.
> Zacks or Finviz is okay when it comes to estimates, but Morningstar is more
> reliable. I used to do this manually before. I tried extracting the data
> myself, but it did not work as I expected.
> I know, that I should be able to get this information from here, but I do
> not know how:
>
>
> http://quicktake.morningstar.com/stocknet/PrintReport.aspx?Country=USA&Symbol=t
>
> Although I have checked every file, and looked at all the links, I still
> could not figure out how to to get them. Basically, I just need the numbers
> shaded by yellow in the attached Excel File.
>
> Ttm P/E Ratio,
> Forward P/E Ratio,
> EPS or Income Growth Estimate for Next 5 years,
> Dividend Yield,
> Ttm EPS,
> Forward EPS Estimate,
> Book Value Per Share,
> (If available) Bond Yield,
> Current Stock Price.
>
> I am hoping to use these numbers to estimate the Fair Values, O-Metrix
> Scores, and Margin of Safety as suggested here:
> http://www.efsinvestment.com/index.php/investment-philosophy.html
>
> If Morningstar is not available, data from other resources is also welcome.
> Any help will be greatly appreciated.
>
2c.

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks [1 Att

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

Thu Oct 13, 2011 11:21 am (PDT)



Thank you Randy. I already obtained the data from Yahoo Finance, using the predefined GetElement Functions. The models look like they are ready to roll.

However, I will try to obtain the same data from Morningstar, since their numbers tend to be more logica,l when it comes to growth estimates. Yahoo Finance's growth estimates are pretty high. I tried getting the EPS growth estimate from Morningstar, but the code did not work well. I just need the Five Year Growth Forecast which is at the bottom of the report.

=RCHGetTableCell("http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",1,">Five Year Growth Forecast") gives me an error.

I looked at the page code, and it kind of looks static for the Five Year Growth Forecast term.

http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us

Nevertheless, there is a table at the bottom that gives Forward Comparison. The number that I am looking for is after the term 'Stock'. The problem is there are so many terms named stock. I do not know how to get this one.

Any idea on how to get the EPS growth estimates?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Take a look at the RCHGetTableCell() function. You'll need to look at the
> source code of the web page for appropriate search strings. As an example,
> to extract the P/E from the web page you cite below:
>
> =RCHGetTableCell("
> http://quicktake.morningstar.com/stocknet/PrintReport.aspx?Country=USA&Symbol=t
> ",1,">Price/Earnings")
>
> Note that you may need to visit the Morningstar web site with either the
> EXCEL Web Query dialog (preferred) or with IE. They sometimes do
> redirections on the first visit of the day. By visiting the site with an IE
> app, you'll create a cookie that bypasses that redirection.
>
> On Thu, Oct 13, 2011 at 7:37 AM, osman gulseven <osmangulseven@...>wrote:
>
> >
> > I am pretty new to the forum, as well as, to the SMF Add-in. I am trying to
> > figure out, how can I extract data to use in Excel models. I am using a
> > simple model based on trailing twelve month EPS, forward EPS, 5 Year EPS
> > Growth Estimates, Book Value, and the Current Price. Ttm P/E ratio, forward
> > P/E ratio and dividend yield will also be great.
> > Zacks or Finviz is okay when it comes to estimates, but Morningstar is more
> > reliable. I used to do this manually before. I tried extracting the data
> > myself, but it did not work as I expected.
> > I know, that I should be able to get this information from here, but I do
> > not know how:
> >
> >
> > http://quicktake.morningstar.com/stocknet/PrintReport.aspx?Country=USA&Symbol=t
> >
> > Although I have checked every file, and looked at all the links, I still
> > could not figure out how to to get them. Basically, I just need the numbers
> > shaded by yellow in the attached Excel File.
> >
> > Ttm P/E Ratio,
> > Forward P/E Ratio,
> > EPS or Income Growth Estimate for Next 5 years,
> > Dividend Yield,
> > Ttm EPS,
> > Forward EPS Estimate,
> > Book Value Per Share,
> > (If available) Bond Yield,
> > Current Stock Price.
> >
> > I am hoping to use these numbers to estimate the Fair Values, O-Metrix
> > Scores, and Margin of Safety as suggested here:
> > http://www.efsinvestment.com/index.php/investment-philosophy.html
> >
> > If Morningstar is not available, data from other resources is also welcome.
> > Any help will be greatly appreciated.
> >
>

2d.

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks

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

Thu Oct 13, 2011 2:18 pm (PDT)



The problem with the first one is that they have both the label AND the
value in the same table cell. So, you would need something like:

=VALUE(SUBSTITUTE(RCHGetTableCell("
http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",0,">Five
Year Growth Forecast"),"Five Year Growth Forecast:",""))

However, the second value is easier to extract if you use the "skip rows"
parameter (see documentation for details):

=RCHGetTableCell("
http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",2,"Next
5 Years",,,,1)/100

Or, using multiple search terms:

=RCHGetTableCell("
http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",1,"Next
5 Years","Stock")/100

On Thu, Oct 13, 2011 at 11:21 AM, <osmangulseven@yahoo.com> wrote:

> However, I will try to obtain the same data from Morningstar, since their
> numbers tend to be more logica,l when it comes to growth estimates. Yahoo
> Finance's growth estimates are pretty high. I tried getting the EPS growth
> estimate from Morningstar, but the code did not work well. I just need the
> Five Year Growth Forecast which is at the bottom of the report.
>
> =RCHGetTableCell("
> http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",1,">Five
> Year Growth Forecast") gives me an error.
>
> I looked at the page code, and it kind of looks static for the Five Year
> Growth Forecast term.
>
>
> http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us
>
> Nevertheless, there is a table at the bottom that gives Forward Comparison.
> The number that I am looking for is after the term 'Stock'. The problem is
> there are so many terms named stock. I do not know how to get this one.
>
> Any idea on how to get the EPS growth estimates?
>
2e.

Re: Extracting Fundamental Data from Morningstar/Finviz/Zacks

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

Fri Oct 14, 2011 1:44 am (PDT)



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?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The problem with the first one is that they have both the label AND the
> value in the same table cell. So, you would need something like:
>
> =VALUE(SUBSTITUTE(RCHGetTableCell("
> http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",0,">Five
> Year Growth Forecast"),"Five Year Growth Forecast:",""))
>
> However, the second value is easier to extract if you use the "skip rows"
> parameter (see documentation for details):
>
> =RCHGetTableCell("
> http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",2,"Next
> 5 Years",,,,1)/100
>
> Or, using multiple search terms:
>
> =RCHGetTableCell("
> http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",1,"Next
> 5 Years","Stock")/100
>
> On Thu, Oct 13, 2011 at 11:21 AM, <osmangulseven@...> wrote:
>
> > However, I will try to obtain the same data from Morningstar, since their
> > numbers tend to be more logica,l when it comes to growth estimates. Yahoo
> > Finance's growth estimates are pretty high. I tried getting the EPS growth
> > estimate from Morningstar, but the code did not work well. I just need the
> > Five Year Growth Forecast which is at the bottom of the report.
> >
> > =RCHGetTableCell("
> > http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us",1,">Five
> > Year Growth Forecast") gives me an error.
> >
> > I looked at the page code, and it kind of looks static for the Five Year
> > Growth Forecast term.
> >
> >
> > http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=t&country=USA&culture=en-us
> >
> > Nevertheless, there is a table at the bottom that gives Forward Comparison.
> > The number that I am looking for is after the term 'Stock'. The problem is
> > there are so many terms named stock. I do not know how to get this one.
> >
> > Any idea on how to get the EPS growth estimates?
> >
>

Recent Activity
Visit Your Group
Need traffic?

Drive customers

With search ads

on Yahoo!

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Fashion News

What's the word on

fashion and style?

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.

Tidak ada komentar:

Posting Komentar