5 Messages
Digest #4627
Messages
Mon Oct 14, 2019 6:34 am (PDT) . Posted by:
mmls011
Thank you Randy, by using =smfGetYahooPortfolioView function I was able to get annual dividend amounts. Great work!
Mon Oct 14, 2019 8:19 am (PDT) . Posted by:
"Ole Jeb" forse85
For what it is worth, my local library has a subscription to
MorningStar. My workaround is to download the 10yr income statement,
balance sheet, etc. and then I created a macro to import the info into
my spreadsheet. I can import data for four stocks in ~10min or less. A
little more work but not bad.
Scott
On 10/13/2019 11:57 AM, freefaller6@yahoo.com [smf_addin] wrote:
>
> Hi all,
>
>
> Has anyone found a solution yet for a workaround for the Advfn
> elements that no longer work? Specifically, I am looking for
> definitions that can provide 10 years worth of ROIC, EPS, Sales, Cash,
> and book value per share.
>
>
> If anyone can help me find or create definitions for these, I am
> willing to share a worksheet I have created that calculates what a
> stock price should be based on a discounted cash flow method. You can
> then compare this against what the price is, and determine whether or
> not it's "on sale." It has worked REALLY well for me, but without the
> Advfn elements, I am a ship without a rudder.
>
>
> This method is based on Phil Town's Rule #1 Investing and essentially
> takes his book and breaks it down into a spreadsheet - it automates
> (or it did) his process. This spreadsheet is a value investors dream.
> So if you have a source for this information and can help create some
> elements, let's talk.
>
>
> Thanks,
>
> Brent
>
>
MorningStar. My workaround is to download the 10yr income statement,
balance sheet, etc. and then I created a macro to import the info into
my spreadsheet. I can import data for four stocks in ~10min or less. A
little more work but not bad.
Scott
On 10/13/2019 11:57 AM, freefaller6@yahoo.com [smf_addin] wrote:
>
> Hi all,
>
>
> Has anyone found a solution yet for a workaround for the Advfn
> elements that no longer work? Specifically, I am looking for
> definitions that can provide 10 years worth of ROIC, EPS, Sales, Cash,
> and book value per share.
>
>
> If anyone can help me find or create definitions for these, I am
> willing to share a worksheet I have created that calculates what a
> stock price should be based on a discounted cash flow method. You can
> then compare this against what the price is, and determine whether or
> not it's "on sale." It has worked REALLY well for me, but without the
> Advfn elements, I am a ship without a rudder.
>
>
> This method is based on Phil Town's Rule #1 Investing and essentially
> takes his book and breaks it down into a spreadsheet - it automates
> (or it did) his process. This spreadsheet is a value investors dream.
> So if you have a source for this information and can help create some
> elements, let's talk.
>
>
> Thanks,
>
> Brent
>
>
Mon Oct 14, 2019 1:01 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You could try GuruFocus with the 4 years of data that is available to
non-subscribers, to see if you can create a model you could easily extend
to 10 years of you subscribed...
I'm somewhat familiar with Rule #1. I have a number of spreadsheets for
rule #1 calculations, going back to at least 2007. Element #15012 used to
be the Rule #1 MOS calculation, but the necessary data elements went away.
It was based on 5-year figures that were easily available from MSN:
Case "Rule #1 MOS Price"
n1 = RCHGetElementNumber(pTicker, 13630) ' 5-Year High P/E
from Reuter's
n2 = RCHGetElementNumber(pTicker, 13634) ' 5-Year Low P/E
from Reuter's
n3 = RCHGetElementNumber(pTicker, 962) ' Current EPS from
Yahoo
n4 = RCHGetElementNumber(pTicker, 621) ' 5-Year Projected
Growth Rate from Yahoo
If n1 > 50 Then n1 = 50
n5 = FV(n4, 10, 0, -n3)
n6 = PV(0.15, 10, 0, -n5 * (n1 + n2) / 2) / 2
RCHSpecialExtraction = n6
So it just required those four data items for calculation. Does your
calculation vary much from the above, other than using 10 years of data?
Sounds like you are doing something different, since you mention ROIC,
Sales, and Cash?
GuruFocus does have some stuff related to Phil Town:
https://www.gurufocus.com/ic/space.php?do=portfolio&id=44640&uid=276632
https://www.gurufocus.com/flarum/d/159982-rule-1
GuruFocus also has a DCF calculator:
https://www.gurufocus.com/stock/mmm/dcf
The parameters for that calculation can be saved. The DCF margin of safety
is available in the screening tools.
BTW, I was able to duplicate my add-in's formula on Portfolio123, for a
5-year time frame of the data elements:
(((EPSExclXor(0,TTM,ZERONA) * (1 + LTGrthMean/100)^10) * (Min(50, PEHigh) +
PELow) / 2) / 1.15^10 / 2) / close(0) - 1
Excluding trading costs, a 20-year backtest using a 50% margin of safety on
S&P 500 stocks had a CAGR of 9.75% versus the S&P 500 CAGR of 6.25%. On the
average, holding about 10 stocks.
Current screen output:
Ticker Last PEHigh PELow LTGrthMean $R1MOS
RE 250.5 101.07 6.55 75.42 46.93
AMP 138.27 17.87 8.92 48 7.2
HII 211.74 25.07 10.84 40 3.9
AMZN 1731.92 1424.25 72.03 37.45 1.52
GM 35.57 54.19 3.62 15.35 1.44
WYNN 112.78 55.78 13.14 23.5 1.17
DAL 52.95 63.08 3.5 16.17 0.99
CELG 100.57 60.18 12.58 21.1 0.9
NFLX 282.93 359.6 90.85 37.54 0.9
However, vastly overweighted with exaggerated 5-year High P/E ratios, even
though the calculation topped them off at 50....
On Mon, Oct 14, 2019 at 4:41 AM Brent freefaller6@ <freefaller6@yahoo.com>.
... wrote:
>
> That's a good idea, but not sure how Gurufocus could automate the system
> like I had it with the Advfn values. And it does work well - one of my
> recent "wins" came from my sheet identifying that RH was on sale at $40 a
> few years back...not too shabby of a return so far.
>
> The way that my sheet use to work, based on Phil's system, was that I
> would run a screen based on set criteria that would return about 50
> candidates. I would then cut and paste the symbols into my sheet and it
> would pull the 10 year data for ROIC, EPS, Sales, Cash. Rule #1 isn't
> necessarily about that raw data as it is about the growth rates over ten
> years. So my sheet would take the raw data, calculate the growth rates and
> based on the growth rates, I could quickly see if it was a valid
> candidate. If so, the sheet would then calculate what the future price
> could/should be, assuming it was a good company and the 10 year trend
> continued (this is where some of the subjectivity and research into the
> company comes in). Then the sheet would discount that future price to what
> the current price should be. You then add in a margin of safety to the
> calculated current price, and compare that against current market price.
> If the margin of safety price is less than the market price, and subjective
> analysis of the company is strong, it's one to strongly consider.
>
> This entire process was automated with the Advfn values. I would run a
> screen, copy/paste ticker symbols and out would pop potential winners.
>
> Do you think that could be done with Gurufocus? I am not familiar with
> the paid version of the site, but would be happy to become a member if it
> restored functionality of the sheet with some tweeking.
>
> Thanks,
> Brent
>
> On Sunday, October 13, 2019, 01:23:58 PM EDT, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
>
>
> If it works that well, why not subscribe to GuruFocus? You could probably
> even automate the process with their screener tool, even to send you email
> alerts of stocks making their way onto or off of your selection universe.
>
> On Sun, Oct 13, 2019 at 9:18 AM freefaller6@... wrote:
>
> Has anyone found a solution yet for a workaround for the Advfn elements
> that no longer work? Specifically, I am looking for definitions that can
> provide 10 years worth of ROIC, EPS, Sales, Cash, and book value per
> share.
>
> If anyone can help me find or create definitions for these, I am willing
> to share a worksheet I have created that calculates what a stock price
> should be based on a discounted cash flow method. You can then compare
> this against what the price is, and determine whether or not it's "on
> sale." It has worked REALLY well for me, but without the Advfn elements, I
> am a ship without a rudder.
>
> This method is based on Phil Town's Rule #1 Investing and essentially
> takes his book and breaks it down into a spreadsheet - it automates (or it
> did) his process. This spreadsheet is a value investors dream. So if you
> have a source for this information and can help create some elements, let's
> talk.
>
>
>
>
non-subscribers, to see if you can create a model you could easily extend
to 10 years of you subscribed...
I'm somewhat familiar with Rule #1. I have a number of spreadsheets for
rule #1 calculations, going back to at least 2007. Element #15012 used to
be the Rule #1 MOS calculation, but the necessary data elements went away.
It was based on 5-year figures that were easily available from MSN:
Case "Rule #1 MOS Price"
n1 = RCHGetElementNumber(pTicker, 13630) ' 5-Year High P/E
from Reuter's
n2 = RCHGetElementNumber(pTicker, 13634) ' 5-Year Low P/E
from Reuter's
n3 = RCHGetElementNumber(pTicker, 962) ' Current EPS from
Yahoo
n4 = RCHGetElementNumber(pTicker, 621) ' 5-Year Projected
Growth Rate from Yahoo
If n1 > 50 Then n1 = 50
n5 = FV(n4, 10, 0, -n3)
n6 = PV(0.15, 10, 0, -n5 * (n1 + n2) / 2) / 2
RCHSpecialExtraction = n6
So it just required those four data items for calculation. Does your
calculation vary much from the above, other than using 10 years of data?
Sounds like you are doing something different, since you mention ROIC,
Sales, and Cash?
GuruFocus does have some stuff related to Phil Town:
https://www.gurufocus.com/ic/space.php?do=portfolio&id=44640&uid=276632
https://www.gurufocus.com/flarum/d/159982-rule-1
GuruFocus also has a DCF calculator:
https://www.gurufocus.com/stock/mmm/dcf
The parameters for that calculation can be saved. The DCF margin of safety
is available in the screening tools.
BTW, I was able to duplicate my add-in's formula on Portfolio123, for a
5-year time frame of the data elements:
(((EPSExclXor(0,TTM,ZERONA) * (1 + LTGrthMean/100)^10) * (Min(50, PEHigh) +
PELow) / 2) / 1.15^10 / 2) / close(0) - 1
Excluding trading costs, a 20-year backtest using a 50% margin of safety on
S&P 500 stocks had a CAGR of 9.75% versus the S&P 500 CAGR of 6.25%. On the
average, holding about 10 stocks.
Current screen output:
Ticker Last PEHigh PELow LTGrthMean $R1MOS
RE 250.5 101.07 6.55 75.42 46.93
AMP 138.27 17.87 8.92 48 7.2
HII 211.74 25.07 10.84 40 3.9
AMZN 1731.92 1424.25 72.03 37.45 1.52
GM 35.57 54.19 3.62 15.35 1.44
WYNN 112.78 55.78 13.14 23.5 1.17
DAL 52.95 63.08 3.5 16.17 0.99
CELG 100.57 60.18 12.58 21.1 0.9
NFLX 282.93 359.6 90.85 37.54 0.9
However, vastly overweighted with exaggerated 5-year High P/E ratios, even
though the calculation topped them off at 50....
On Mon, Oct 14, 2019 at 4:41 AM Brent freefaller6@ <freefaller6@yahoo.com>.
... wrote:
>
> That's a good idea, but not sure how Gurufocus could automate the system
> like I had it with the Advfn values. And it does work well - one of my
> recent "wins" came from my sheet identifying that RH was on sale at $40 a
> few years back...not too shabby of a return so far.
>
> The way that my sheet use to work, based on Phil's system, was that I
> would run a screen based on set criteria that would return about 50
> candidates. I would then cut and paste the symbols into my sheet and it
> would pull the 10 year data for ROIC, EPS, Sales, Cash. Rule #1 isn't
> necessarily about that raw data as it is about the growth rates over ten
> years. So my sheet would take the raw data, calculate the growth rates and
> based on the growth rates, I could quickly see if it was a valid
> candidate. If so, the sheet would then calculate what the future price
> could/should be, assuming it was a good company and the 10 year trend
> continued (this is where some of the subjectivity and research into the
> company comes in). Then the sheet would discount that future price to what
> the current price should be. You then add in a margin of safety to the
> calculated current price, and compare that against current market price.
> If the margin of safety price is less than the market price, and subjective
> analysis of the company is strong, it's one to strongly consider.
>
> This entire process was automated with the Advfn values. I would run a
> screen, copy/paste ticker symbols and out would pop potential winners.
>
> Do you think that could be done with Gurufocus? I am not familiar with
> the paid version of the site, but would be happy to become a member if it
> restored functionality of the sheet with some tweeking.
>
> Thanks,
> Brent
>
> On Sunday, October 13, 2019, 01:23:58 PM EDT, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
>
>
> If it works that well, why not subscribe to GuruFocus? You could probably
> even automate the process with their screener tool, even to send you email
> alerts of stocks making their way onto or off of your selection universe.
>
> On Sun, Oct 13, 2019 at 9:18 AM freefaller6@
>
> Has anyone found a solution yet for a workaround for the Advfn elements
> that no longer work? Specifically, I am looking for definitions that can
> provide 10 years worth of ROIC, EPS, Sales, Cash, and book value per
> share.
>
> If anyone can help me find or create definitions for these, I am willing
> to share a worksheet I have created that calculates what a stock price
> should be based on a discounted cash flow method. You can then compare
> this against what the price is, and determine whether or not it's "on
> sale." It has worked REALLY well for me, but without the Advfn elements, I
> am a ship without a rudder.
>
> This method is based on Phil Town's Rule #1 Investing and essentially
> takes his book and breaks it down into a spreadsheet - it automates (or it
> did) his process. This spreadsheet is a value investors dream. So if you
> have a source for this information and can help create some elements, let's
> talk.
>
>
>
>
Mon Oct 14, 2019 6:46 pm (PDT) . Posted by:
"Omar Jaleel" saanajaleel
Randy,
Can you help me with extracting from this site:
https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL
The formula I am using only works for the data that is contained within the
first set of { }. I cannot figure out how to get data in the subsequent
groups of data for prior years.
=smfStrExtr(RCHGetWebData("
https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL","Net
Profit Margin"),":",",",1)
I also cannot figure out how to use smfStrExtr to remove the quotes around
the number.
Thanks in advance. Regards.
Can you help me with extracting from this site:
https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL
The formula I am using only works for the data that is contained within the
first set of { }. I cannot figure out how to get data in the subsequent
groups of data for prior years.
=smfStrExtr(RCHGetWebData("
https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL","
Profit Margin"
I also cannot figure out how to use smfStrExtr to remove the quotes around
the number.
Thanks in advance. Regards.
Tue Oct 15, 2019 2:10 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You either need to use smfWord() or do a cascading extraction. Since the
file is shorter than 32767 bytes, smfWord() can be used, so something like:
=smfStrExtr(smfWord(RCHGetWebData("
https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL"),3,"{"),"""Net
Profit Margin"" : ""","""",1)
Just vary the "3" parameter from "3" thru "12" for the 10 years of data.
On Mon, Oct 14, 2019 at 6:46 PM Omar Jaleel omar.jaleel@... wrote:
>
> Can you help me with extracting from this site:
> https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL
>
> The formula I am using only works for the data that is contained within
> the first set of { }. I cannot figure out how to get data in the subsequent
> groups of data for prior years.
> =smfStrExtr(RCHGetWebData("
> https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL","Net
> Profit Margin"),":",",",1)
>
> I also cannot figure out how to use smfStrExtr to remove the quotes around
> the number.
>
>
file is shorter than 32767 bytes, smfWord() can be used, so something like:
=smfStrExtr(smfWord(RCHGetWebData("
https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL"),3,"{"),"""Net
Profit Margin"" : ""","""",1)
Just vary the "3" parameter from "3" thru "12" for the 10 years of data.
On Mon, Oct 14, 2019 at 6:46 PM Omar Jaleel omar.jaleel@... wrote:
>
> Can you help me with extracting from this site:
> https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL
>
> The formula I am using only works for the data that is contained within
> the first set of { }. I cannot figure out how to get data in the subsequent
> groups of data for prior years.
> =smfStrExtr(RCHGetWebData("
> https://financialmodelingprep.com/api/v3/financials/income-statement/AAPL","
> Profit Margin"
>
> I also cannot figure out how to use smfStrExtr to remove the quotes around
> the number.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar