Rabu, 01 Agustus 2012

[smf_addin] Digest Number 2298

15 New Messages

Digest #2298
1a
Re: DCF by "Bruce" bruce.bogart
1b
Re: DCF by "Ron Spruell" hashky
1c
Re: DCF by "Randy Harmelink" rharmelink
2b
2c
Re: Look ahead bias when using fundamental data by "zarathustra_winced@yahoo.com" zarathustra_winced
2d
3a
Re: Morningstar List of Stocks by "Ron Spruell" hashky
4a
Re: Sorting data with array by "Stephan Moebius" spikemgm
4b
Re: Sorting data with array by "Randy Harmelink" rharmelink
4c
Re: Sorting data with array by "Stephan Moebius" spikemgm
4d
Re: Sorting data with array by "Randy Harmelink" rharmelink

Messages

Wed Aug 1, 2012 7:16 am (PDT) . Posted by:

"Bruce" bruce.bogart



Sorry, my mistake, I'll upload it again without the link.

--- In smf_addin@yahoogroups.com, Taral Pandya <t.pandya2689@...> wrote:
>
> Jason,
>
> I downloaded it. It's asking for a file called Stock Analysis 001.xlsm. Where do I get that file from?
> -Taral
>
> On Jul 31, 2012, at 8:03 PM, "Bruce" <bruce@...> wrote:
>
> >
> >
> > Jason, I'm so glad you posted this request. I've long wanted to contribute something to Randy and the group as a way of saying "THANK YOU!".
> >
> > I'll upload a sample spreadsheet (DCF Template for smf_addin Group) to the Files area. It's pretty straightforward, and the math is documented in cell E1, but please let me know if you have any questions or need anything else. I start a new job on Monday so please ask your questions before Sunday. :)
> >
> > --- In smf_addin@yahoogroups.com, "Jason" <xxjaysonxx@> wrote:
> > >
> > > Does anyone have a good DCF template they wouldnt mind sharing?
> > >
> >
> >
>

Wed Aug 1, 2012 2:45 pm (PDT) . Posted by:

"Ron Spruell" hashky

Bruce -

Very nice!

1.  Why didn't you automate Column H?
 
Cell H2

EPS Next 5Y

Cell H8:H27
=RCHGetTableCell("http://finviz.com/quote.ashx?t="&smfJoin($A$8:$A$29,","),1,"?t="&$A8&"&",">"&H$2)

2.  How can I add more tickers?  I tried copying down the page but that doesn't work.

3.  I did one ticker CAT.  Morningstar has a "Fair Value" of 103.3.  I believe that they also use DCF to establish the "Fair Value".  Your DCF returns 153.57.  How can I get these 2 be more in agreement?

Ron Spruell

>________________________________
> From: Bruce <bruce@bogart.us>
>To: smf_addin@yahoogroups.com
>Sent: Tuesday, July 31, 2012 7:03 PM
>Subject: [smf_addin] Re: DCF
>
>

>
>
>Jason, I'm so glad you posted this request. I've long wanted to contribute something to Randy and the group as a way of saying "THANK YOU!".
>
>I'll upload a sample spreadsheet (DCF Template for smf_addin Group) to the Files area. It's pretty straightforward, and the math is documented in cell E1, but please let me know if you have any questions or need anything else. I start a new job on Monday so please ask your questions before Sunday. :)
>
>--- In smf_addin@yahoogroups.com, "Jason" <xxjaysonxx@...> wrote:
>>
>> Does anyone have a good DCF template they wouldnt mind sharing?
>>
>
>
>
>
>

Wed Aug 1, 2012 2:51 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

There are literally hundreds, if not thousands, of ways to create a "fair
value" of a company. There's no reason that two different methods should
necessarily agree with each other.

On Wed, Aug 1, 2012 at 2:41 PM, Ron Spruell <hashky@yahoo.com> wrote:

>
>
> Bruce -
>
> Very nice!
>
> 1. Why didn't you automate Column H?
>
> Cell H2
> EPS Next 5Y
>
> Cell H8:H27
> =RCHGetTableCell("http://finviz.com/quote.ashx?t=
> "&smfJoin($A$8:$A$29,","),1,"?t="&$A8&"&",">"&H$2)
>
> 2. How can I add more tickers? I tried copying down the page but that
> doesn't work.
>
> 3. I did one ticker CAT. Morningstar has a "Fair Value" of 103.3. I
> believe that they also use DCF to establish the "Fair Value". Your DCF
> returns 153.57. How can I get these 2 be more in agreement?
>
> Ron Spruell
>

Wed Aug 1, 2012 2:31 pm (PDT) . Posted by:

"ngogerty1" ngogerty1

Just curious if there is a "release data" associated with fundamental data. Often earnings via 10Q's and 10K's come out 4-10 weeks into the quarter. There is also restatement of earnings. Does the SMF-add in have any way of tracking the release dates of the data or restatements to give a true "point in time" view on available information. Any tips appreciated.

Wed Aug 1, 2012 2:39 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It wouldn't be the add-in that gave you that capability, but the data
source. And I don't know of a free data source that has that capability.
You could go to something like StockScreen123.com or Portfolio123.com, but
then you wouldn't be using the add-in. They both just converted over to
using Compustat data.

On Wed, Aug 1, 2012 at 2:00 PM, ngogerty1 <nick@gogerty.com> wrote:

> Just curious if there is a "release data" associated with fundamental
> data. Often earnings via 10Q's and 10K's come out 4-10 weeks into the
> quarter. There is also restatement of earnings. Does the SMF-add in have
> any way of tracking the release dates of the data or restatements to give a
> true "point in time" view on available information. Any tips appreciated.
>

Wed Aug 1, 2012 2:55 pm (PDT) . Posted by:

"zarathustra_winced@yahoo.com" zarathustra_winced

Advfn has a field 'Date Preliminary Data Loaded' which is the date advfn obtained the data and put it in their database. I've looked at a few stocks and the dates seemed to be near report dates, at least for recent periods. I haven't done an exhaustive examination of the usefulness of this date, or spoke to anyone at advfn. Let me know if you do.
Sent via BlackBerry by AT&T

-----Original Message-----
From: Randy Harmelink <rharmelink@gmail.com>
Sender: smf_addin@yahoogroups.com
Date: Wed, 1 Aug 2012 14:39:21
To: <smf_addin@yahoogroups.com>
Reply-To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Look ahead bias when using fundamental data

It wouldn't be the add-in that gave you that capability, but the data
source. And I don't know of a free data source that has that capability.
You could go to something like StockScreen123.com or Portfolio123.com, but
then you wouldn't be using the add-in. They both just converted over to
using Compustat data.

On Wed, Aug 1, 2012 at 2:00 PM, ngogerty1 <nick@gogerty.com> wrote:

> Just curious if there is a "release data" associated with fundamental
> data. Often earnings via 10Q's and 10K's come out 4-10 weeks into the
> quarter. There is also restatement of earnings. Does the SMF-add in have
> any way of tracking the release dates of the data or restatements to give a
> true "point in time" view on available information. Any tips appreciated.
>

Wed Aug 1, 2012 3:00 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The problem is if the company restates their financials, you have no idea
which data has been changed. So the date is meaningless in terms of a
"point in time" view.

On Wed, Aug 1, 2012 at 2:55 PM, <zarathustra_winced@yahoo.com> wrote:

>
> Advfn has a field 'Date Preliminary Data Loaded' which is the date advfn
> obtained the data and put it in their database. I've looked at a few stocks
> and the dates seemed to be near report dates, at least for recent periods.
> I haven't done an exhaustive examination of the usefulness of this date, or
> spoke to anyone at advfn. Let me know if you do.
>

Wed Aug 1, 2012 3:09 pm (PDT) . Posted by:

"ngogerty1" ngogerty1

thanks. I know that Reuters has the rolls-royce of point in time databases. Here is an article about the biases etc. I think the only way around it is to track the returns from 90 days out etc. and assume a restatement penalty. https://docs.google.com/viewer?url=http%3A%2F%2Fthomsonreuters.com%2Fcontent%2Ffinancial%2Fpdf%2Fi_and_a%2FPointInTime_Fundamentals.pdf

--- In smf_addin@yahoogroups.com, "ngogerty1" <nick@...> wrote:
>
> Just curious if there is a "release data" associated with fundamental data. Often earnings via 10Q's and 10K's come out 4-10 weeks into the quarter. There is also restatement of earnings. Does the SMF-add in have any way of tracking the release dates of the data or restatements to give a true "point in time" view on available information. Any tips appreciated.
>

Wed Aug 1, 2012 3:37 pm (PDT) . Posted by:

"ngogerty1" ngogerty1

zarathustra,

thanks the date submitted, seems to tie out. I haven't looked to see if it is the actual number and I don't know how restatements are handled so there is some uncertainty there. I hope others who examine this will take a look and contribute. I am using the past years for reference so the actual dates are less an issue, but I do hope the restatements are clean in the database.

I would like to thank Randy and the community for putting such a great resource out there. The add in and community are fantastic.

Nick

--- In smf_addin@yahoogroups.com, zarathustra_winced@... wrote:
>
> Advfn has a field 'Date Preliminary Data Loaded' which is the date advfn obtained the data and put it in their database. I've looked at a few stocks and the dates seemed to be near report dates, at least for recent periods. I haven't done an exhaustive examination of the usefulness of this date, or spoke to anyone at advfn. Let me know if you do.
> Sent via BlackBerry by AT&T
>
> -----Original Message-----
> From: Randy Harmelink <rharmelink@...>
> Sender: smf_addin@yahoogroups.com
> Date: Wed, 1 Aug 2012 14:39:21
> To: <smf_addin@yahoogroups.com>
> Reply-To: smf_addin@yahoogroups.com
> Subject: Re: [smf_addin] Look ahead bias when using fundamental data
>
> It wouldn't be the add-in that gave you that capability, but the data
> source. And I don't know of a free data source that has that capability.
> You could go to something like StockScreen123.com or Portfolio123.com, but
> then you wouldn't be using the add-in. They both just converted over to
> using Compustat data.
>
> On Wed, Aug 1, 2012 at 2:00 PM, ngogerty1 <nick@...> wrote:
>
> > Just curious if there is a "release data" associated with fundamental
> > data. Often earnings via 10Q's and 10K's come out 4-10 weeks into the
> > quarter. There is also restatement of earnings. Does the SMF-add in have
> > any way of tracking the release dates of the data or restatements to give a
> > true "point in time" view on available information. Any tips appreciated.
> >
>

Wed Aug 1, 2012 2:45 pm (PDT) . Posted by:

"Ron Spruell" hashky

I had a similar problem with the "Library Premium" version.  I could get the name of the company, but not the Ticker from their screener.  They told me that feature was not available with the "Library Premium" version.  The implication wat that it is available with the Premium version.

Ron Spruell

>________________________________
> From: oryxius <oryxius@yahoo.com>
>To: smf_addin@yahoogroups.com
>Sent: Wednesday, August 1, 2012 4:34 AM
>Subject: [smf_addin] Morningstar List of Stocks
>
>

>Anyone knows how to get the full ticker list of stocks covered by Morningstar with analyst reports (about 1700)? I couldn't find a practical way to download the list from their online screener.
>
>
>
>
>

Wed Aug 1, 2012 3:47 pm (PDT) . Posted by:

"Stephan Moebius" spikemgm

Thanks for the sort info.

On your documentation page for smfgetcsvfile you have as an example:
=smfGetCSVFile("http://unicorn.us.com/advdec/NASDAQ_advn.csv") that data
does not match anything.

With =smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv") it downloads
this:

2012/07/31 Market AdvIssues DecIssues UncIssues AdvVol
DeclVol UnchVol NewHis NewLows NYSE 1233 1763 120 1296392427
2375018390 60722954 177 25 AMEX 188 217 37 16800000 43793714 3240000 14 5
NASDAQ 1021 1463 120 683091800 1066370492 35043100 52 53

I just want the nyse and nasdaq data rows, do you have numbers or letters
to replace recent to make that work.

Thanks
Steve

On Tue, Jul 31, 2012 at 10:13 PM, Randy Harmelink <rharmelink@gmail.com>wrote:

> **
>
>
> That's the nature of an array-entered range -- it can't be adjusted
> externally. What I do in such cases is create a sort key field next to the
> ticker symbols, then sort those two columns only. When the ticker range
> changes, the array-entered RCHGetYahooQuotes() range based on it will also
> update. So, in essence, everything does get sorted.
>
> I'm not sure what you're asking about Unicorn or updated UDF's? Or which
> data is wrong?
>
> On Tue, Jul 31, 2012 at 9:07 PM, MoeB <smoebius@gmail.com> wrote:
>
>>
>> I'm using =rchgetyahooquotes and can't seem to get my column data to
>> sort. Comes up with error: you can not change part of an array.
>>
>> My other sheet works fine =smfgetcsvfile(finance.yahoo). can you not sort
>> getquotes array's.
>>
>> Also unicorn.com pulls with this
>> =smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv")
>>
>> It brings the whole file over, do you have an updated udf's, I just want
>> nyse and naz data and saw that you had naz.csv but that data is wrong.
>> Thanks
>> Steve
>>
>>
>>
>>
>> ------------------------------------
>>
>> Yahoo! Groups Links
>>
>>
>>
>>
>
>

Wed Aug 1, 2012 3:56 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry. Looks like they've changed the website since I used them as an
example. TO get just NYSE and NASDAQ, you could do:

=INDEX(smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv",,5,9),3)
=INDEX(smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv",,5,9),5)

On Wed, Aug 1, 2012 at 3:47 PM, Stephan Moebius <smoebius@gmail.com> wrote:

>
> On your documentation page for smfgetcsvfile you have as an example:
> =smfGetCSVFile("http://unicorn.us.com/advdec/NASDAQ_advn.csv") that data
> does not match anything.
>
> With =smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv") it
> downloads this:
>
> 2012/07/31 Market AdvIssues DecIssues UncIssues AdvVol
> DeclVol UnchVol NewHis NewLows NYSE 1233 1763 120 1296392427
> 2375018390 60722954 177 25 AMEX 188 217 37 16800000 43793714 3240000 14
> 5 NASDAQ 1021 1463 120 683091800 1066370492 35043100 52 53
>
> I just want the nyse and nasdaq data rows, do you have numbers or letters
> to replace recent to make that work.
>
>

Wed Aug 1, 2012 10:19 pm (PDT) . Posted by:

"Stephan Moebius" spikemgm

Thanks Randy, that works great,

Can you explain how you come up with the numbers.

1 other ?, this is more of an excel issue. I have the low print and high
print for SPY in a2-b2 and used =IF(DAY(TODAY())=31,(B2-A2),"") in cell c2,
it worked fine yesterday but today is gone when i opened the spread sheet,
the formula is still there but the data is gone. Now it went to today the
1st which is in d2 and that's fine but will be gone tomorrow, how to keep
yesterdays data in the cells, anyone?

Low High Tues Wed Thurs Fri Avg Range 137.40 138.73 1.33 1.33

Thanks
Steve
On Wed, Aug 1, 2012 at 3:56 PM, Randy Harmelink <rharmelink@gmail.com>wrote:

> **
>
>
> Sorry. Looks like they've changed the website since I used them as an
> example. TO get just NYSE and NASDAQ, you could do:
>
> =INDEX(smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv",,5,9),3)
> =INDEX(smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv",,5,9),5)
>
>
> On Wed, Aug 1, 2012 at 3:47 PM, Stephan Moebius <smoebius@gmail.com>wrote:
>
>>
>> On your documentation page for smfgetcsvfile you have as an example:
>> =smfGetCSVFile("http://unicorn.us.com/advdec/NASDAQ_advn.csv") that data
>> does not match anything.
>>
>> With =smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv") it
>> downloads this:
>>
>> 2012/07/31 Market AdvIssues DecIssues UncIssues AdvVol
>> DeclVol UnchVol NewHis NewLows NYSE 1233 1763 120 1296392427
>> 2375018390 60722954 177 25 AMEX 188 217 37 16800000 43793714 3240000
>> 14 5 NASDAQ 1021 1463 120 683091800 1066370492 35043100 52 53
>>
>> I just want the nyse and nasdaq data rows, do you have numbers or letters
>> to replace recent to make that work.
>>
>>
>
>
>

Wed Aug 1, 2012 10:35 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

For most of the array-entered functions, it defaults to returned an array
that is the size of the range it is array-entered over. So, the "5,9"
parameters override that and tell the function to return a 5x9 array --
what would normally be the 5-row by 9-column interpretation of the CSV
file. The INDEX(...,3) and INDEX(...,5) just tell EXCEL to only keep the
3rd or 5th row of the returned 5x9 array.

The only way to keep yesterday's values would be to convert the formulas to
values.

On Wed, Aug 1, 2012 at 10:19 PM, Stephan Moebius <smoebius@gmail.com> wrote:

>
> Can you explain how you come up with the numbers.
>
> 1 other ?, this is more of an excel issue. I have the low print and high
> print for SPY in a2-b2 and used =IF(DAY(TODAY())=31,(B2-A2),"") in cell c2,
> it worked fine yesterday but today is gone when i opened the spread sheet,
> the formula is still there but the data is gone. Now it went to today the
> 1st which is in d2 and that's fine but will be gone tomorrow, how to keep
> yesterdays data in the cells, anyone?
>
> Low High Tues Wed Thurs Fri Avg Range 137.40 138.73 1.33 1.33
>
> Thanks
> Steve
> On Wed, Aug 1, 2012 at 3:56 PM, Randy Harmelink <rharmelink@gmail.com>wrote:
>
>> **
>> Sorry. Looks like they've changed the website since I used them as an
>> example. TO get just NYSE and NASDAQ, you could do:
>>
>>
>> =INDEX(smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv",,5,9),3)
>> =INDEX(smfGetCSVFile("http://unicorn.us.com/advdec/recent.csv",,5,9),5)
>>
>

Wed Aug 1, 2012 11:53 pm (PDT) . Posted by:

"Chirag Vyas" vyas.chirag

I need closing market price for all US tickers for the given date, 31st July for now onto spreadsheet.

Thanks.

Tidak ada komentar:

Posting Komentar