15 New Messages
Digest #2758
Messages
Wed Sep 4, 2013 5:15 am (PDT) . Posted by:
parts57
Would anyone have a suggestion on how to pull data from the quote page of options xpress for a stock , more specifically for historic volatility and implied volatility
Thanks in advance for your help
RHJ
Thanks in advance for your help
RHJ
Wed Sep 4, 2013 5:34 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't think the add-in can do it. That page doesn't appear to have a
direct URL. Plus, you have to be logged in to get to that page, and OX has
a very strange method of security.
Can you use 888options.com instead? You do need to access one quote on the
website first, but after that creates the security cookie, you can access
all of the historical and implied volatility information. Documentation of
the function is in the "Works in Progress" folder. But, for example, if I
create the security cookie first, this:
=smfGet888OptionQuote("MMM",,,,"HV10C")
...gets me the "Historical Volatility, 10 days, Current" value from their
web page.
On Wed, Sep 4, 2013 at 5:15 AM, <parts57@verizon.net> wrote:
> Would anyone have a suggestion on how to pull data from the quote page of
> options xpress for a stock , more specifically for historic volatility and
> implied volatility
>
> Thanks in advance for your help
>
direct URL. Plus, you have to be logged in to get to that page, and OX has
a very strange method of security.
Can you use 888options.com instead? You do need to access one quote on the
website first, but after that creates the security cookie, you can access
all of the historical and implied volatility information. Documentation of
the function is in the "Works in Progress" folder. But, for example, if I
create the security cookie first, this:
=smfGet888OptionQuo
...gets me the "Historical Volatility, 10 days, Current" value from their
web page.
On Wed, Sep 4, 2013 at 5:15 AM, <parts57@verizon.
> Would anyone have a suggestion on how to pull data from the quote page of
> options xpress for a stock , more specifically for historic volatility and
> implied volatility
>
> Thanks in advance for your help
>
Wed Sep 4, 2013 5:46 am (PDT) . Posted by:
"Richard Johnson" parts57
Hi Randy
The 888optionsquote gets historic volatility just fine but comes up
short on implied volatility of a stock .
All the data I need is also on IVolatility.com . Is there an easy way to
get it from there . ( I chose OX since I have an account there and the
data is there
Thanks again
Richard
On 9/4/2013 8:34 AM, Randy Harmelink wrote:
> I don't think the add-in can do it. That page doesn't appear to have a
> direct URL. Plus, you have to be logged in to get to that page, and OX
> has a very strange method of security.
>
> Can you use 888options.com <http://888options.com> instead? You do
> need to access one quote on the website first, but after that creates
> the security cookie, you can access all of the historical and implied
> volatility information. Documentation of the function is in the "Works
> in Progress" folder. But, for example, if I create the security cookie
> first, this:
>
> =smfGet888OptionQuote("MMM",,,,"HV10C")
>
> ...gets me the "Historical Volatility, 10 days, Current" value from
> their web page.
>
> On Wed, Sep 4, 2013 at 5:15 AM, <parts57@verizon.net
> <mailto:parts57@verizon.net>> wrote:
>
> Would anyone have a suggestion on how to pull data from the quote
> page of options xpress for a stock , more specifically for
> historic volatility and implied volatility
>
> Thanks in advance for your help
>
>
>
The 888optionsquote gets historic volatility just fine but comes up
short on implied volatility of a stock .
All the data I need is also on IVolatility.
get it from there . ( I chose OX since I have an account there and the
data is there
Thanks again
Richard
On 9/4/2013 8:34 AM, Randy Harmelink wrote:
> I don't think the add-in can do it. That page doesn't appear to have a
> direct URL. Plus, you have to be logged in to get to that page, and OX
> has a very strange method of security.
>
> Can you use 888options.com <http://888options.
> need to access one quote on the website first, but after that creates
> the security cookie, you can access all of the historical and implied
> volatility information. Documentation of the function is in the "Works
> in Progress" folder. But, for example, if I create the security cookie
> first, this:
>
> =smfGet888OptionQuo
>
> ...gets me the "Historical Volatility, 10 days, Current" value from
> their web page.
>
> On Wed, Sep 4, 2013 at 5:15 AM, <parts57@verizon.
> <mailto:parts57@verizon.
>
> Would anyone have a suggestion on how to pull data from the quote
> page of options xpress for a stock , more specifically for
> historic volatility and implied volatility
>
> Thanks in advance for your help
>
>
>
Wed Sep 4, 2013 6:06 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
What URL and what items? 888options.com is actually displaying
iVolatility.com data.
On Wed, Sep 4, 2013 at 5:46 AM, Richard Johnson <parts57@verizon.net> wrote:
> The 888optionsquote gets historic volatility just fine but comes up short
> on implied volatility of a stock .
>
> All the data I need is also on IVolatility.com . Is there an easy way to
> get it from there . ( I chose OX since I have an account there and the data
> is there
>
iVolatility.
On Wed, Sep 4, 2013 at 5:46 AM, Richard Johnson <parts57@verizon.
> The 888optionsquote gets historic volatility just fine but comes up short
> on implied volatility of a stock .
>
> All the data I need is also on IVolatility.
> get it from there . ( I chose OX since I have an account there and the data
> is there
>
Wed Sep 4, 2013 6:20 am (PDT) . Posted by:
"Richard Johnson" parts57
Hi
On this page
http://www.ivolatility.com/options.j?ticker=pg&R=0&x=0&y=0
I would like to get IV index mean in the current column
IF this a difficult problem , I have a way to get an approximation from
option data so I need not use any of your valuable time
Richard
On 9/4/2013 9:06 AM, Randy Harmelink wrote:
> What URL and what items? 888options.com <http://888options.com> is
> actually displaying iVolatility.com data.
>
> On Wed, Sep 4, 2013 at 5:46 AM, Richard Johnson <parts57@verizon.net
> <mailto:parts57@verizon.net>> wrote:
>
> The 888optionsquote gets historic volatility just fine but comes
> up short on implied volatility of a stock .
>
> All the data I need is also on IVolatility.com . Is there an easy
> way to get it from there . ( I chose OX since I have an account
> there and the data is there
>
>
>
On this page
http://www.ivolatil
I would like to get IV index mean in the current column
IF this a difficult problem , I have a way to get an approximation from
option data so I need not use any of your valuable time
Richard
On 9/4/2013 9:06 AM, Randy Harmelink wrote:
> What URL and what items? 888options.com <http://888options.
> actually displaying iVolatility.
>
> On Wed, Sep 4, 2013 at 5:46 AM, Richard Johnson <parts57@verizon.
> <mailto:parts57@verizon.
>
> The 888optionsquote gets historic volatility just fine but comes
> up short on implied volatility of a stock .
>
> All the data I need is also on IVolatility.
> way to get it from there . ( I chose OX since I have an account
> there and the data is there
>
>
>
Wed Sep 4, 2013 8:00 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Isn't that the same thing that is on 888options.com (because iVolatility
provides the data in a frame on their site)? Which can be retrieved with:
=smfGet888OptionQuote("PG",,,,"IVIMC")
On Wed, Sep 4, 2013 at 6:20 AM, Richard Johnson <parts57@verizon.net> wrote:
>
> On this page
>
> http://www.ivolatility.com/options.j?ticker=pg&R=0&x=0&y=0
>
> I would like to get IV index mean in the current column
>
> IF this a difficult problem , I have a way to get an approximation from
> option data so I need not use any of your valuable time
>
provides the data in a frame on their site)? Which can be retrieved with:
=smfGet888OptionQuo
On Wed, Sep 4, 2013 at 6:20 AM, Richard Johnson <parts57@verizon.
>
> On this page
>
> http://www.ivolatil
>
> I would like to get IV index mean in the current column
>
> IF this a difficult problem , I have a way to get an approximation from
> option data so I need not use any of your valuable time
>
Wed Sep 4, 2013 9:32 am (PDT) . Posted by:
"Richard Johnson" parts57
Hi
Color me a little embarrassed and a little bit of stupid. I did not read
enough or search for the right thing .
Sorry to take a little bit of your time .....
But a big thanks for getting me in the right direction
I will make a donation to "Gift of Life " for organ donation in your honor.
Richard Johnson
On 9/4/2013 11:00 AM, Randy Harmelink wrote:
> Isn't that the same thing that is on 888options.com
> <http://888options.com> (because iVolatility provides the data in a
> frame on their site)? Which can be retrieved with:
>
> =smfGet888OptionQuote("PG",,,,"IVIMC")
>
>
> On Wed, Sep 4, 2013 at 6:20 AM, Richard Johnson <parts57@verizon.net
> <mailto:parts57@verizon.net>> wrote:
>
>
> On this page
>
> http://www.ivolatility.com/options.j?ticker=pg&R=0&x=0&y=0
>
> I would like to get IV index mean in the current column
>
> IF this a difficult problem , I have a way to get an approximation
> from option data so I need not use any of your valuable time
>
>
>
Color me a little embarrassed and a little bit of stupid. I did not read
enough or search for the right thing .
Sorry to take a little bit of your time .....
But a big thanks for getting me in the right direction
I will make a donation to "Gift of Life " for organ donation in your honor.
Richard Johnson
On 9/4/2013 11:00 AM, Randy Harmelink wrote:
> Isn't that the same thing that is on 888options.com
> <http://888options.
> frame on their site)? Which can be retrieved with:
>
> =smfGet888OptionQuo
>
>
> On Wed, Sep 4, 2013 at 6:20 AM, Richard Johnson <parts57@verizon.
> <mailto:parts57@verizon.
>
>
> On this page
>
> http://www.ivolatil
>
> I would like to get IV index mean in the current column
>
> IF this a difficult problem , I have a way to get an approximation
> from option data so I need not use any of your valuable time
>
>
>
Wed Sep 4, 2013 6:44 am (PDT) . Posted by:
"raiders8112" raiders8112
It is possible for this model to only pull 5 years worth of data? I tried deleting the first 5 years, but the table "breaks".
Thanks
Thanks
Wed Sep 4, 2013 8:03 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You'd have to adjust the pointers appropriately, in the first row. Convert
them to values first, instead of formulas.
On Wed, Sep 4, 2013 at 6:44 AM, raiders8112 <mike.russo@comcast.net> wrote:
> It is possible for this model to only pull 5 years worth of data? I tried
> deleting the first 5 years, but the table "breaks".
>
them to values first, instead of formulas.
On Wed, Sep 4, 2013 at 6:44 AM, raiders8112 <mike.russo@comcast.
> It is possible for this model to only pull 5 years worth of data? I tried
> deleting the first 5 years, but the table "breaks"
>
Wed Sep 4, 2013 8:21 am (PDT) . Posted by:
hamishthedenizen
You could monkey around and try to introduce a variable for the
number of years of data to pull, I guess, but I don't have the
time to build that out at the moment.
Also, if you only need 5 years of data, there are models in the
files section that do that using Google and MSN data that you
might consider. The data set is richer in Randy's Google-based
model as I remember - I often use it for companies with a shorter
history.
--- In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
You'd have to adjust the pointers appropriately, in the first
row. Convert them to values first, instead of formulas.
On Wed, Sep 4, 2013 at 6:44 AM, raiders8112 <mike.russo@...>
wrote:
It is possible for this model to only pull 5 years worth of data?
���� I tried deleting the first 5 years, but the table
"breaks".
number of years of data to pull, I guess, but I don't have the
time to build that out at the moment.
Also, if you only need 5 years of data, there are models in the
files section that do that using Google and MSN data that you
might consider. The data set is richer in Randy's Google-based
model as I remember - I often use it for companies with a shorter
history.
--- In smf_addin@yahoogrou
You'd have to adjust the pointers appropriately, in the first
row. Convert them to values first, instead of formulas.
On Wed, Sep 4, 2013 at 6:44 AM, raiders8112 <mike.russo@
wrote:
It is possible for this model to only pull 5 years worth of data?
���� I tried deleting the first 5 years, but the table
"breaks"
Wed Sep 4, 2013 9:06 am (PDT) . Posted by:
hamishthedenizen
Here's a quick and dirty fix.
For 5 years of data, set cell G4 = -1
For 6 years of data, you would set cell F4 = -1
etc.
Don't forget to replace the -1 with the formula to go back to 10
year data sets
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
You could monkey around and try to introduce a variable for the
number of years of data to pull, I guess, but I don't have the
time to build that out at the moment.
Also, if you only need 5 years of data, there are models in the
files section that do that using Google and MSN data that you
might consider. The data set is richer in Randy's Google-based
model as I remember - I often use it for companies with a shorter
history.
--- In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
You'd have to adjust the pointers appropriately, in the first
row. Convert them to values first, instead of formulas.
On Wed, Sep 4, 2013 at 6:44 AM, raiders8112 <mike.russo@...>
wrote:
It is possible for this model to only pull 5 years worth of data?
����'� I tried deleting the first 5 years, but the table
"breaks".
For 5 years of data, set cell G4 = -1
For 6 years of data, you would set cell F4 = -1
etc.
Don't forget to replace the -1 with the formula to go back to 10
year data sets
--- In smf_addin@yahoogrou
You could monkey around and try to introduce a variable for the
number of years of data to pull, I guess, but I don't have the
time to build that out at the moment.
Also, if you only need 5 years of data, there are models in the
files section that do that using Google and MSN data that you
might consider. The data set is richer in Randy's Google-based
model as I remember - I often use it for companies with a shorter
history.
--- In smf_addin@yahoogrou
You'd have to adjust the pointers appropriately, in the first
row. Convert them to values first, instead of formulas.
On Wed, Sep 4, 2013 at 6:44 AM, raiders8112 <mike.russo@
wrote:
It is possible for this model to only pull 5 years worth of data?
����'� I tried deleting the first 5 years, but the table
"breaks"
Wed Sep 4, 2013 9:55 am (PDT) . Posted by:
"raiders8112" raiders8112
Thanks. After deleting years 2003 thru 2007 I had to change cell C1 and C4 to 6 and it works great.
Wed Sep 4, 2013 8:03 am (PDT) . Posted by:
g_oudreva
Thanks so much for your help with the GuruFocus 10 year model.
I was trying it out and noticed that it works great for
established companies for the 10 year annual data, but when you
import data for a new stock like Facebook (FB), it mixes the
quarterly data in with the annual data.
I was trying to come up with some way to adjust this to keep the
annual data and quarterly data separated, but I haven't been able
to come up with a workable solution.
Anyone have suggestions?
--- In smf_addin@yahoogrou
Go to Files > Uploads by Forum Members
http://groups.
0forum%20members
it's the third file down, named "10 year model - GuruFocus.xlsx
<http://f1.grp.
ui2J5yaLVhMrcj_
ads%20by%20forum%
> "
--- In smf_addin@yahoogrou
Could you point me to the location of the file to download that
has the Guru 10 year statements
--- In smf_addin@yahoogrou
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the
source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogrou
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If
you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogrou
Hi Dan,
I finally got the bugs worked out with my
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�location���Ò�'�Ò�¢Ò�'\
�Ò�'�Ò�'
you���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�ve learned a lot about
using the add-in instructions.
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogrou
[mailto:smf_addin@yahoogrou
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date
there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER
check to clean up the sheet.
--- In smf_addin@yahoogrou
This string is to isolate fixes needed on the 10 year
GuruFocus-based model. I will flag that I am nowhere near in the
league of Randy & co, so if VB skills are needed, I'm out. This
is just my best effort at producing something to replace the 10
year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and
errors.
I'd also welcome anyone else having a go at building on this.
Just please attach a version number and flag the changes on this
thread.
One last note: though Gurufocus does standardize the data, not
all fields occur for each stock. If the sheet attempts to pull
data that isn't there, you will get "Error"
I've gotten this I've modified the rows to put zeroes if "Error"
is returned, but I haven't done it for the whole sheet (too lazy
I guess :-)) The data for "Inventory&quo
"Inventory Turnover" were examples. See the light green blocked
data on rows 117/118 of v3 for an example.
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
���Ò�'�Ò�'�Ò�'�Ò�· Stock Price in A4
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� changed to
=RCHGetYahooQuotes(
Randy���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�s Suggestion
Issues addressed this iteration (v3):
Kermit:
1. Cells O2 ���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� S2 return
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Error���Ò�'�Ò�¢Ò�'�Ò\
�'�Ò�'�Ò
=RCHGetTableCell(
<http://www.gurufocu
share",
as Ticker
���Ò�'�Ò�'�Ò�'�Ò�· I went back and did a global
change of $A$1 to Ticker. That was an oversight on my part, for
which I apologize. I���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�m not
used to producing sheets for others to use, so I tend to skimp on
the cleanup and user-friendliness.
���Ò�'�Ò�'�Ò�'�Ò�· Which ticker are you using?
The sheet had some problems with companies that have a short
history, which I hoped I had fixed, but maybe not. Let me know
the ticker and I���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�ll see what
I error I get and try to come up with a fix.
2. Rows 87 ���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� 96 are
totally blank except a return
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Error���Ò�'�Ò�¢Ò�'�Ò\
�'�Ò�'�Ò
���Ò�'�Ò�'�Ò�'�Ò�· That block should return the
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�company
description���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� from MSN. It
does for some tickers, not for others. You can delete it if you
want.
3. Row
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�N���Ò�'�Ò�¢Ò�'�Ò�'�\
Ò�'�Ò�'� has no heading, but I noticed starting in Cell N130
thru N136 they are Averages I would enter AVG in Cell N129
���Ò�'�Ò�'�Ò�'�Ò�· Done. Also put the label in
Row 5. The column is mostly there to separate annual from
quarterly numbers, but for valuation I thought
I���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were
the growth rate of. Seems to me those rows should be right after
row 24
���Ò�'�Ò�'�Ò�'�Ò�· On location, I prefer to get
the three financial statements in first. Feel free to move the
rows around as you prefer. If a lot of people would prefer that
format, let me know and I���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�ll
do it.
���Ò�'�Ò�'�Ò�'�Ò�· As to what they are the growth
rates of, I thought the rows were labeled clearly. They are Y/Y
growth rates for Revenues, EBITDA, etc. Let me know if the
labeling is confusing.
5. I noticed a later email that you had made a change. I
downloaded at 9:55am U.S. EST so I may not have latest level. So
might I suggest that you include a version level maybe in cell
C4.
���Ò�'�Ò�'�Ò�'�Ò�· The only change in that
version was to the price in A4, as noted above.
Randy:
Cell C135 has a #REF error...
���Ò�'�Ò�'�Ò�'�Ò�· Fixed
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� my bad.
For me, rows 138 and 139 show up as "######
is too wide for the columns. You can probably drop everything to
the right of the decimal point.
���Ò�'�Ò�'�Ò�'�Ò�· Done. I tend to look at
smaller cap stocks, so I left a single decimal place for the
income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major
statements around ���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� moved
margins and growth to just below description block, as I think
those are higher priority. I also moved some of the balance sheet
measures to the balance sheet block from
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Misc���Ò�'�Ò�¢Ò�'�Ò�\
'�Ò�'�Ò�
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Capital
Efficiency���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'� to
���Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Returns���Ò�'�Ò�¢Ò�'�\
Ò�'�Ò�'�
As noted at the top, I also modified certain rows where the data
is not always there, to replace "Error" messages with zeroes -
"Treasury Stock" is an example.
Wed Sep 4, 2013 8:23 am (PDT) . Posted by:
hamishthedenizen
Let me take a look. I thought I'd fixed that.
--- In smf_addin@yahoogroups.com, <lottidottigigliotti@...>
wrote:
Thanks so much for your help with the GuruFocus 10 year model.
I was trying it out and noticed that it works great for
established companies for the 10 year annual data, but when you
import data for a new stock like Facebook (FB), it mixes the
quarterly data in with the annual data.
I was trying to come up with some way to adjust this to keep the
annual data and quarterly data separated, but I haven't been able
to come up with a workable solution.
Anyone have suggestions?
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
Go to Files > Uploads by Forum Members
http://groups.yahoo.com/neo/groups/smf_addin/files/Uploads%20by%2\
0forum%20members
it's the third file down, named "10 year model - GuruFocus.xlsx
<http://f1.grp.yahoofs.com/v1/mNIkUsEt8C2lIEfaQ_aVdzHZHO08bX3CHI5\
ui2J5yaLVhMrcj_FEVEStrN3p-otm0kJBLbf1eT29zG5S1cdeV38B45DcEFw/Uplo\
ads%20by%20forum%20members/10%20year%20model%20-%20GuruFocus.xlsx\
> "
--- In smf_addin@yahoogroups.com, <ddbohrer@...> wrote:
Could you point me to the location of the file to download that
has the Guru 10 year statements
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the
source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogroups.com, <J_M_Kuehl@...> wrote:
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If
you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogroups.com, <amt2100@...> wrote:
Hi Dan,
I finally got the bugs worked out with my
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�location���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò��\
���'������'����'� problems. Thanks for this file. I
looks like
you���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�\
Ò�'�Ò�'�ve learned a lot about using the add-in instructions.
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogroups.com
[mailto:smf_addin@yahoogroups.com] On Behalf Of dan-carroll@...
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date
there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER
check to clean up the sheet.
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
This string is to isolate fixes needed on the 10 year
GuruFocus-based model. I will flag that I am nowhere near in the
league of Randy & co, so if VB skills are needed, I'm out. This
is just my best effort at producing something to replace the 10
year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and
errors.
I'd also welcome anyone else having a go at building on this.
Just please attach a version number and flag the changes on this
thread.
One last note: though Gurufocus does standardize the data, not
all fields occur for each stock. If the sheet attempts to pull
data that isn't there, you will get "Error". In some cases where
I've gotten this I've modified the rows to put zeroes if "Error"
is returned, but I haven't done it for the whole sheet (too lazy
I guess :-)) The data for "Inventory" and "Days Inventory" and
"Inventory Turnover" were examples. See the light green blocked
data on rows 117/118 of v3 for an example.
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Stock
Price in A4
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� changed to =RCHGetYahooQuotes(Ticker,"l1"), per
Randy���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'�����s Suggestion
Issues addressed this iteration (v3):
Kermit:
1. Cells O2
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� S2 return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'����'�����
=RCHGetTableCell("http://www.gurufocus.com/financials/
<http://www.gurufocus.com/financials/> "&$A$1,O$3,"except for per
share",">"&$B2) in my copy A1 thru b3 is a merged cell defined
as Ticker
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· I went
back and did a global change of $A$1 to Ticker. That was an
oversight on my part, for which I apologize.
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�m not used to producing sheets for others to use, so I
tend to skimp on the cleanup and user-friendliness.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Which
ticker are you using? The sheet had some problems with companies
that have a short history, which I hoped I had fixed, but maybe
not. Let me know the ticker and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll see what I error I get and try to come up with a
fix.
2. Rows 87
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� 96 are totally blank except a return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'����'����� =RCHGetElementNumber(Ticker,1)
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· That
block should return the
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�company
description���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'������'� from MSN. It does for some tickers, not for
others. You can delete it if you want.
3. Row
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�N���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'�Ò�'� has no heading, but I noticed starting in
Cell N130 thru N136 they are Averages I would enter AVG in Cell
N129
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
Also put the label in Row 5. The column is mostly there to
separate annual from quarterly numbers, but for valuation I
thought
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were
the growth rate of. Seems to me those rows should be right after
row 24
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· On
location, I prefer to get the three financial statements in
first. Feel free to move the rows around as you prefer. If a lot
of people would prefer that format, let me know and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll do it.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· As to
what they are the growth rates of, I thought the rows were
labeled clearly. They are Y/Y growth rates for Revenues, EBITDA,
etc. Let me know if the labeling is confusing.
5. I noticed a later email that you had made a change. I
downloaded at 9:55am U.S. EST so I may not have latest level. So
might I suggest that you include a version level maybe in cell
C4.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· The
only change in that version was to the price in A4, as noted
above.
Randy:
Cell C135 has a #REF error...
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Fixed
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� my bad.
For me, rows 138 and 139 show up as "######" because the format
is too wide for the columns. You can probably drop everything to
the right of the decimal point.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
I tend to look at smaller cap stocks, so I left a single decimal
place for the income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major
statements around
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� moved margins and growth to just below description
block, as I think those are higher priority. I also moved some of
the balance sheet measures to the balance sheet block from
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Misc���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�Ò�'�Ò�'� and Capital Efficiency. Also renamed
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Capital
Efficiency���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'�Ò�'� to
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Returns���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'�Ò�'�Ò�'�
As noted at the top, I also modified certain rows where the data
is not always there, to replace "Error" messages with zeroes -
"Treasury Stock" is an example.
--- In smf_addin@yahoogrou
wrote:
Thanks so much for your help with the GuruFocus 10 year model.
I was trying it out and noticed that it works great for
established companies for the 10 year annual data, but when you
import data for a new stock like Facebook (FB), it mixes the
quarterly data in with the annual data.
I was trying to come up with some way to adjust this to keep the
annual data and quarterly data separated, but I haven't been able
to come up with a workable solution.
Anyone have suggestions?
--- In smf_addin@yahoogrou
Go to Files > Uploads by Forum Members
http://groups.
0forum%20members
it's the third file down, named "10 year model - GuruFocus.xlsx
<http://f1.grp.
ui2J5yaLVhMrcj_
ads%20by%20forum%
> "
--- In smf_addin@yahoogrou
Could you point me to the location of the file to download that
has the Guru 10 year statements
--- In smf_addin@yahoogrou
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the
source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogrou
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If
you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogrou
Hi Dan,
I finally got the bugs worked out with my
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�location���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò��\
���'���
looks like
you���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�\
Ò�'�Ò�'�
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogrou
[mailto:smf_addin@yahoogrou
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date
there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER
check to clean up the sheet.
--- In smf_addin@yahoogrou
This string is to isolate fixes needed on the 10 year
GuruFocus-based model. I will flag that I am nowhere near in the
league of Randy & co, so if VB skills are needed, I'm out. This
is just my best effort at producing something to replace the 10
year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and
errors.
I'd also welcome anyone else having a go at building on this.
Just please attach a version number and flag the changes on this
thread.
One last note: though Gurufocus does standardize the data, not
all fields occur for each stock. If the sheet attempts to pull
data that isn't there, you will get "Error"
I've gotten this I've modified the rows to put zeroes if "Error"
is returned, but I haven't done it for the whole sheet (too lazy
I guess :-)) The data for "Inventory&quo
"Inventory Turnover" were examples. See the light green blocked
data on rows 117/118 of v3 for an example.
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Stock
Price in A4
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� changed to =RCHGetYahooQuotes(
Randy���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'���
Issues addressed this iteration (v3):
Kermit:
1. Cells O2
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� S2 return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'��
=RCHGetTableCell(
<http://www.gurufocu
share",
as Ticker
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· I went
back and did a global change of $A$1 to Ticker. That was an
oversight on my part, for which I apologize.
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�m not used to producing sheets for others to use, so I
tend to skimp on the cleanup and user-friendliness.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Which
ticker are you using? The sheet had some problems with companies
that have a short history, which I hoped I had fixed, but maybe
not. Let me know the ticker and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll see what I error I get and try to come up with a
fix.
2. Rows 87
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� 96 are totally blank except a return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'��
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· That
block should return the
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�company
description���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'���
others. You can delete it if you want.
3. Row
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�N���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'
Cell N130 thru N136 they are Averages I would enter AVG in Cell
N129
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
Also put the label in Row 5. The column is mostly there to
separate annual from quarterly numbers, but for valuation I
thought
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were
the growth rate of. Seems to me those rows should be right after
row 24
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· On
location, I prefer to get the three financial statements in
first. Feel free to move the rows around as you prefer. If a lot
of people would prefer that format, let me know and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll do it.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· As to
what they are the growth rates of, I thought the rows were
labeled clearly. They are Y/Y growth rates for Revenues, EBITDA,
etc. Let me know if the labeling is confusing.
5. I noticed a later email that you had made a change. I
downloaded at 9:55am U.S. EST so I may not have latest level. So
might I suggest that you include a version level maybe in cell
C4.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· The
only change in that version was to the price in A4, as noted
above.
Randy:
Cell C135 has a #REF error...
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Fixed
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� my bad.
For me, rows 138 and 139 show up as "######
is too wide for the columns. You can probably drop everything to
the right of the decimal point.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
I tend to look at smaller cap stocks, so I left a single decimal
place for the income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major
statements around
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� moved margins and growth to just below description
block, as I think those are higher priority. I also moved some of
the balance sheet measures to the balance sheet block from
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Misc���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�Ò
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Capital
Efficiency���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Returns���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'
As noted at the top, I also modified certain rows where the data
is not always there, to replace "Error" messages with zeroes -
"Treasury Stock" is an example.
Wed Sep 4, 2013 8:43 am (PDT) . Posted by:
hamishthedenizen
Fixed. I forgot to set the match_type = 0 in cell M4. Fixed and
re-uploaded. My bad.
--- In smf_addin@yahoogroups.com, <lottidottigigliotti@...>
wrote:
Thanks so much for your help with the GuruFocus 10 year model.
I was trying it out and noticed that it works great for
established companies for the 10 year annual data, but when you
import data for a new stock like Facebook (FB), it mixes the
quarterly data in with the annual data.
I was trying to come up with some way to adjust this to keep the
annual data and quarterly data separated, but I haven't been able
to come up with a workable solution.
Anyone have suggestions?
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
Go to Files > Uploads by Forum Members
http://groups.yahoo.com/neo/groups/smf_addin/files/Uploads%20by%2\
0forum%20members
it's the third file down, named "10 year model - GuruFocus.xlsx
<http://f1.grp.yahoofs.com/v1/mNIkUsEt8C2lIEfaQ_aVdzHZHO08bX3CHI5\
ui2J5yaLVhMrcj_FEVEStrN3p-otm0kJBLbf1eT29zG5S1cdeV38B45DcEFw/Uplo\
ads%20by%20forum%20members/10%20year%20model%20-%20GuruFocus.xlsx\
> "
--- In smf_addin@yahoogroups.com, <ddbohrer@...> wrote:
Could you point me to the location of the file to download that
has the Guru 10 year statements
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the
source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogroups.com, <J_M_Kuehl@...> wrote:
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If
you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogroups.com, <amt2100@...> wrote:
Hi Dan,
I finally got the bugs worked out with my
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�location���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò��\
���'������'����'� problems. Thanks for this file. I
looks like
you���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�\
Ò�'�Ò�'�ve learned a lot about using the add-in instructions.
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogroups.com
[mailto:smf_addin@yahoogroups.com] On Behalf Of dan-carroll@...
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date
there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER
check to clean up the sheet.
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
This string is to isolate fixes needed on the 10 year
GuruFocus-based model. I will flag that I am nowhere near in the
league of Randy & co, so if VB skills are needed, I'm out. This
is just my best effort at producing something to replace the 10
year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and
errors.
I'd also welcome anyone else having a go at building on this.
Just please attach a version number and flag the changes on this
thread.
One last note: though Gurufocus does standardize the data, not
all fields occur for each stock. If the sheet attempts to pull
data that isn't there, you will get "Error". In some cases where
I've gotten this I've modified the rows to put zeroes if "Error"
is returned, but I haven't done it for the whole sheet (too lazy
I guess :-)) The data for "Inventory" and "Days Inventory" and
"Inventory Turnover" were examples. See the light green blocked
data on rows 117/118 of v3 for an example.
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Stock
Price in A4
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� changed to =RCHGetYahooQuotes(Ticker,"l1"), per
Randy���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'�����s Suggestion
Issues addressed this iteration (v3):
Kermit:
1. Cells O2
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� S2 return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'����'�����
=RCHGetTableCell("http://www.gurufocus.com/financials/
<http://www.gurufocus.com/financials/> "&$A$1,O$3,"except for per
share",">"&$B2) in my copy A1 thru b3 is a merged cell defined
as Ticker
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· I went
back and did a global change of $A$1 to Ticker. That was an
oversight on my part, for which I apologize.
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�m not used to producing sheets for others to use, so I
tend to skimp on the cleanup and user-friendliness.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Which
ticker are you using? The sheet had some problems with companies
that have a short history, which I hoped I had fixed, but maybe
not. Let me know the ticker and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll see what I error I get and try to come up with a
fix.
2. Rows 87
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� 96 are totally blank except a return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'����'����� =RCHGetElementNumber(Ticker,1)
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· That
block should return the
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�company
description���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'������'� from MSN. It does for some tickers, not for
others. You can delete it if you want.
3. Row
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�N���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'�Ò�'� has no heading, but I noticed starting in
Cell N130 thru N136 they are Averages I would enter AVG in Cell
N129
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
Also put the label in Row 5. The column is mostly there to
separate annual from quarterly numbers, but for valuation I
thought
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were
the growth rate of. Seems to me those rows should be right after
row 24
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· On
location, I prefer to get the three financial statements in
first. Feel free to move the rows around as you prefer. If a lot
of people would prefer that format, let me know and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll do it.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· As to
what they are the growth rates of, I thought the rows were
labeled clearly. They are Y/Y growth rates for Revenues, EBITDA,
etc. Let me know if the labeling is confusing.
5. I noticed a later email that you had made a change. I
downloaded at 9:55am U.S. EST so I may not have latest level. So
might I suggest that you include a version level maybe in cell
C4.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· The
only change in that version was to the price in A4, as noted
above.
Randy:
Cell C135 has a #REF error...
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Fixed
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� my bad.
For me, rows 138 and 139 show up as "######" because the format
is too wide for the columns. You can probably drop everything to
the right of the decimal point.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
I tend to look at smaller cap stocks, so I left a single decimal
place for the income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major
statements around
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� moved margins and growth to just below description
block, as I think those are higher priority. I also moved some of
the balance sheet measures to the balance sheet block from
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Misc���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�Ò�'�Ò�'� and Capital Efficiency. Also renamed
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Capital
Efficiency���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'�Ò�'� to
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Returns���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'�Ò�'�Ò�'�
As noted at the top, I also modified certain rows where the data
is not always there, to replace "Error" messages with zeroes -
"Treasury Stock" is an example.
re-uploaded. My bad.
--- In smf_addin@yahoogrou
wrote:
Thanks so much for your help with the GuruFocus 10 year model.
I was trying it out and noticed that it works great for
established companies for the 10 year annual data, but when you
import data for a new stock like Facebook (FB), it mixes the
quarterly data in with the annual data.
I was trying to come up with some way to adjust this to keep the
annual data and quarterly data separated, but I haven't been able
to come up with a workable solution.
Anyone have suggestions?
--- In smf_addin@yahoogrou
Go to Files > Uploads by Forum Members
http://groups.
0forum%20members
it's the third file down, named "10 year model - GuruFocus.xlsx
<http://f1.grp.
ui2J5yaLVhMrcj_
ads%20by%20forum%
> "
--- In smf_addin@yahoogrou
Could you point me to the location of the file to download that
has the Guru 10 year statements
--- In smf_addin@yahoogrou
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the
source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogrou
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If
you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogrou
Hi Dan,
I finally got the bugs worked out with my
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�location���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò��\
���'���
looks like
you���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�\
Ò�'�Ò�'�
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogrou
[mailto:smf_addin@yahoogrou
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date
there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER
check to clean up the sheet.
--- In smf_addin@yahoogrou
This string is to isolate fixes needed on the 10 year
GuruFocus-based model. I will flag that I am nowhere near in the
league of Randy & co, so if VB skills are needed, I'm out. This
is just my best effort at producing something to replace the 10
year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and
errors.
I'd also welcome anyone else having a go at building on this.
Just please attach a version number and flag the changes on this
thread.
One last note: though Gurufocus does standardize the data, not
all fields occur for each stock. If the sheet attempts to pull
data that isn't there, you will get "Error"
I've gotten this I've modified the rows to put zeroes if "Error"
is returned, but I haven't done it for the whole sheet (too lazy
I guess :-)) The data for "Inventory&quo
"Inventory Turnover" were examples. See the light green blocked
data on rows 117/118 of v3 for an example.
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Stock
Price in A4
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� changed to =RCHGetYahooQuotes(
Randy���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'���
Issues addressed this iteration (v3):
Kermit:
1. Cells O2
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� S2 return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'��
=RCHGetTableCell(
<http://www.gurufocu
share",
as Ticker
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· I went
back and did a global change of $A$1 to Ticker. That was an
oversight on my part, for which I apologize.
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�m not used to producing sheets for others to use, so I
tend to skimp on the cleanup and user-friendliness.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Which
ticker are you using? The sheet had some problems with companies
that have a short history, which I hoped I had fixed, but maybe
not. Let me know the ticker and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll see what I error I get and try to come up with a
fix.
2. Rows 87
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� 96 are totally blank except a return
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Error���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'��\
��'��'��
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· That
block should return the
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�company
description���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò��\
���'���
others. You can delete it if you want.
3. Row
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�N���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'
Cell N130 thru N136 they are Averages I would enter AVG in Cell
N129
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
Also put the label in Row 5. The column is mostly there to
separate annual from quarterly numbers, but for valuation I
thought
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were
the growth rate of. Seems to me those rows should be right after
row 24
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· On
location, I prefer to get the three financial statements in
first. Feel free to move the rows around as you prefer. If a lot
of people would prefer that format, let me know and
I���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�ll do it.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· As to
what they are the growth rates of, I thought the rows were
labeled clearly. They are Y/Y growth rates for Revenues, EBITDA,
etc. Let me know if the labeling is confusing.
5. I noticed a later email that you had made a change. I
downloaded at 9:55am U.S. EST so I may not have latest level. So
might I suggest that you include a version level maybe in cell
C4.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· The
only change in that version was to the price in A4, as noted
above.
Randy:
Cell C135 has a #REF error...
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Fixed
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� my bad.
For me, rows 138 and 139 show up as "######
is too wide for the columns. You can probably drop everything to
the right of the decimal point.
���Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�· Done.
I tend to look at smaller cap stocks, so I left a single decimal
place for the income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major
statements around
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'� moved margins and growth to just below description
block, as I think those are higher priority. I also moved some of
the balance sheet measures to the balance sheet block from
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Misc���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò\
�'�Ò�'�Ò
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Capital
Efficiency���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'
���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�'�Ò�\
'�Ò�'�Returns���Ò�'�Ò�'�Ò�'�Ò�¢Ò�'�Ò�'�Ò�'�Ò�'\
�Ò�'�Ò�'
As noted at the top, I also modified certain rows where the data
is not always there, to replace "Error" messages with zeroes -
"Treasury Stock" is an example.
Tidak ada komentar:
Posting Komentar