Minggu, 08 Maret 2015

[smf_addin] Digest Number 3349

15 Messages

Digest #3349
1a
P/E Differences by rjemery7
1b
Re: P/E Differences by "Randy Harmelink" rharmelink
2
P/E Variations by rjemery7
3b
Re: Referencing my addin to RCH addin by "Randy Harmelink" rharmelink
3d
Re: Referencing my addin to RCH addin by "Randy Harmelink" rharmelink
4a
Re: Beta by rjemery7
5a
5b
Re: RCHGetHistory return showing blank cells by "Randy Harmelink" rharmelink
6b
Re: Getting Data From finviz.com by "Randy Harmelink" rharmelink
7b
Re: Element Creation for Dummies by "Randy Harmelink" rharmelink

Messages

Sun Mar 8, 2015 4:03 am (PDT) . Posted by:

rjemery7

RCHGetYahooQuotes() offers three P/E values: r, r6 and r7. I assume r6 is TTM and r7 is forward, but what then is r?

As of the close on 3/6/2015 and for AET (Aetna), the three values are r=17.97, r6=14.22 and r7=12.75. Yet, the webpage shows P/E (ttm) as 17.72, yet another value.

Can you explain how r, r6 and r7 are determined and the different value offered by the webpage?

Sun Mar 8, 2015 8:11 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

As noted in the documentation, r6 and r7 are supposed to be the P/E ratios
based on the estimates of current year earnings and next year earnings
(i.e. e6 and e7).

As to why they vary from values on the web pages, only Yahoo knows...I see
17.72 from both RCHGetYahooQuotes() and the web page right now, so maybe
the CSV file that Yahoo provides for RCHGetYahooQuotes() is from an
end-of-day value?

On Sun, Mar 8, 2015 at 4:03 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> RCHGetYahooQuotes() offers three P/E values: r, r6 and r7. I assume r6 is
> TTM and r7 is forward, but what then is r?
>
> As of the close on 3/6/2015 and for AET (Aetna), the three values are
> r=17.97, r6=14.22 and r7=12.75. Yet, the webpage shows P/E (ttm) as 17.72,
> yet another value.
>
> Can you explain how r, r6 and r7 are determined and the different value
> offered by the webpage?
>

Sun Mar 8, 2015 6:07 am (PDT) . Posted by:

rjemery7

RCHGetYahooQuotes() offers three P/E values: r, r6 and r7. I assume r6 is TTM and r7 is forward, but what then is r?

As of the close on 3/6/2015 and for AET (Aetna), the three values are r=17.97, r6=14.22, r7=12.75, respectively. The webpage shows P/E (ttm) as 17.72, yet another value.

Can you explain how r, r6 and r7 are determined and the different value offered by the webpage?

Sun Mar 8, 2015 6:37 am (PDT) . Posted by:

boo1712

Hi Guys,
Thank you Randy for creating such a wonderful addin for free.


Over the years, I had created my functions that are much dependent on Randy's RCH addin. I just an few of my personal modules into RCH addin. However when upgrading, it becomes a bit of hassle.


I want to install my modules as a standalone addin. What lines should I add to my addin such that it will always make reference to RCH addin or such that my addin will always look into RCH addin as some sort or public vba?


Thanks for reading my message.


Terry

Sun Mar 8, 2015 8:07 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

If I understand what you're asking, you'd just go to the upper menu and
choose the > Tools > References drop-down, and then click on the add-in
library. Then, your VBA project can utilize any routine from the add-in.
And when you update the add-in, your routine will pick up the updates.

On Sun, Mar 8, 2015 at 6:37 AM, boo1712@... wrote:

>
> Over the years, I had created my functions that are much dependent on
> Randy's RCH addin. I just an few of my personal modules into RCH addin.
> However when upgrading, it becomes a bit of hassle.
>
> I want to install my modules as a standalone addin. What lines should I
> add to my addin such that it will always make reference to RCH addin or
> such that my addin will always look into RCH addin as some sort or public
> vba?
>
>

Sun Mar 8, 2015 9:44 am (PDT) . Posted by:

boo1712

Thank you for the reply.

Done the standard installation of addin procedure already.


Will generate "Compile error" and "Sub or Function no defined"


For example, I used the following function which works as a module inside RCH addin, but not when I pull it out into another addin


Public Function GetSIFn(ByVal psicode As String, _
ByVal pPeriod As String, _
ByVal pCells As Integer, _
Optional ByVal pFind1 As String = "<BODY")

'*************Usage : GetSiFn(1.Ticker, 2.period/financial Q H A F,3.cell number, 4.name of data to find)
psicode = UCase(psicode)
Select Case UCase(pPeriod)
Case "Q": sUrl = "http://www.shareinvestor.com/fundamental/financials.html?counter=" & psicode & "&period=quarter&cols=10"
Case "H": sUrl = "http://www.shareinvestor.com/fundamental/financials.html?counter=" & psicode & "&period=half&cols=10"
Case "A": sUrl = "http://www.shareinvestor.com/fundamental/financials.html?counter=" & psicode & "&period=fy&cols=10"
Case "F": sUrl = "http://www.shareinvestor.com/fundamental/factsheet.html?counter=" & psicode
End Select


GetSIFn = RCHGetTableCell(sUrl, pCells, pFind1)


Exit Function
ErrorExit: GetSIFn = vError
End Function



Sun Mar 8, 2015 9:52 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The standard installation applies to EXCEL workbooks, but NOT to VBA
projects. You do need to set up a reference to the add-in within your VBA
project in order for that VBA project to use add-in functions.

On Sun, Mar 8, 2015 at 9:44 AM, boo1712@yahoo.co.uk [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Done the standard installation of addin procedure already.
>
> Will generate "Compile error" and "Sub or Function no defined"
>
> For example, I used the following function which works as a module inside
> RCH addin, but not when I pull it out into another addin
>
> Public Function GetSIFn(ByVal psicode As String, _
> ByVal pPeriod As String, _
> ByVal pCells As Integer, _
> Optional ByVal pFind1 As String = "<BODY")
>
> '*************Usage : GetSiFn(1.Ticker, 2.period/financial Q H A F,3.cell
> number, 4.name of data to find)
> psicode = UCase(psicode)
> Select Case UCase(pPeriod)
> Case "Q": sUrl = "
> http://www.shareinvestor.com/fundamental/financials.html?counter=" &
> psicode & "&period=quarter&cols=10"
> Case "H": sUrl = "
> http://www.shareinvestor.com/fundamental/financials.html?counter=" &
> psicode & "&period=half&cols=10"
> Case "A": sUrl = "
> http://www.shareinvestor.com/fundamental/financials.html?counter=" &
> psicode & "&period=fy&cols=10"
> Case "F": sUrl = "
> http://www.shareinvestor.com/fundamental/factsheet.html?counter=" &
> psicode
> End Select
>
> GetSIFn = RCHGetTableCell(sUrl, pCells, pFind1)
>
> Exit Function
> ErrorExit: GetSIFn = vError
> End Function
>

Sun Mar 8, 2015 7:41 am (PDT) . Posted by:

rjemery7

I'm glad you mentioned the variability of beta depending on the length of time considered (and the comparison benchmark). For AET (Aetna) as of 3/6/2015, a quick survey of beta values revealed wide disparity:

0.74 Yahoo Finance
0.91 Finviz
0.98 Google Finance
1.00 S&P Stock Report for AET dated 3/7/2015

Using the method outlined in Calculating Beta: Portfolio Math For The Average Investor http://www.investopedia.com/articles/financial-theory/09/calculating-beta.asp


http://www.investopedia.com/articles/financial-theory/09/calculating-beta.asp

Calculating Beta: Portfolio Math For The Average Inve... http://www.investopedia.com/articles/financial-theory/09/calculating-beta.asp Beta is a useful tool for calculating risk, but the formulas provided online aren't specific to you. Learn how to make your own.



View on www.investopedia.com http://www.investopedia.com/articles/financial-theory/09/calculating-beta.asp
Preview by Yahoo




and using Excel, I calculated my own values for 1 through 5 years, inclusive. My results:

1.08 1-year
0.99 2-year
0.92 3-year
0.99 4-year
0.98 5-year

Sun Mar 8, 2015 8:19 am (PDT) . Posted by:

"A. Sundvall" for30sqn

  I'm trying to create an array showing Date, High, Low and Close prices for the last 40 days.On Sheet2, an Array from B1 to E41 shows all cells with   {=RCHGetYahooHistory("A2",,,,,,,,"DHLC")}  entered in all cells within the array.  But the return I get is just a blank. Any other smf_addins used in the file are working well.   I'm sure I'm not the only one to experience this so if you have a solution, please advise. Thanks,AJS

Sun Mar 8, 2015 8:24 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Your first parameter, "A2", is a string literal, not a cell reference.
There is no ticker symbol "A2", so nothing gets returned. Actually, you
should see "Error" in cell B1?

On Sun, Mar 8, 2015 at 8:15 AM, 'A. Sundvall' for30sqn@... wrote:

>
> I'm trying to create an array showing Date, High, Low and Close prices for
> the last 40 days.
> On Sheet2, an Array from B1 to E41 shows all cells with
> {=RCHGetYahooHistory("A2",,,,,,,,"DHLC")} entered in all cells within
> the array. But the return I get is just a blank.
> Any other smf_addins used in the file are working well. I'm sure I'm not
> the only one to experience this so if you have a solution, please advise.
>
>

Sun Mar 8, 2015 8:40 am (PDT) . Posted by:

for30sqn

Thanks Randy. I removed the quotation marks around A2, and the function works well. I really appreciate your help.


Al

Sun Mar 8, 2015 8:49 am (PDT) . Posted by:

rjemery7

It doesn't appear that either of your methods now work for a Finviz non-subscriber. I added your sfJoin() macro to my workbook and then tried:

=smfGetCSVFile("http://finviz.com/export.ashx?v=151&T= http://finviz.com/export.ashx?v=151&T="&sfJoin(A3:A9,",")&"&c="&sfJoin(B1:E1,","))

Just to verify matters, what cells should be primed with what values before the above command is executed? And in what cell should the above formula be executed from?

Sun Mar 8, 2015 9:35 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You're correct that the export now only works for subscribers. However, the
other method worked fine for me when I tried it just now. I actually just
cut and pasted the text and the formulas directly from the message.

I can't answer your last two questions any better than is described in the
message the formula came from. Where did that description confuse you? It
refers to the ranges used. BTW, sfJoin() was added to the add-in as
smfJoin().

On Sun, Mar 8, 2015 at 8:49 AM, rjemery@... wrote:

>
> It doesn't appear that either of your methods now work for a Finviz
> non-subscriber. I added your sfJoin() macro to my workbook and then tried:
>
> =smfGetCSVFile("http://finviz.com/export.ashx?v=151&T=
> "&sfJoin(A3:A9,",")&"&c="&sfJoin(B1:E1,","))
>
> Just to verify matters, what cells should be primed with what values
> before the above command is executed? And in what cell should the above
> formula be executed from?
>

Sun Mar 8, 2015 11:01 am (PDT) . Posted by:

freefaller6

To the group (not necessarily Randy),


Is there anyone out there with a good understanding of excel (as I do), that has been able to figure out how to create a new element number? I have spent the morning reading through the group Tips and FAQs, but still cannot figure it out. I want to learn not out of need, but rather simply to increase my understanding of the add-in and perhaps be able to help out another group member in the future.


For starters, where the does url portion of the element definition come from?


If we use an example of the ROIC for FY1 and FY2; the element definitions in smf-elements-7.txt are:
FY1;AdvFN-A;1;>RATIOS CALCULATIONS;>Return on Capital Invested (ROCI); ; ;0;0;0;0
FY2;AdvFN-A;2;>RATIOS CALCULATIONS;>Return on Capital Invested (ROCI); ; ;0;0;0;0
Using aapl as an example, how does this now to go here: Apple Company Financial Information http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data


http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data

Apple Company Financial Information http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data Apple Computer Inc financial information, fundamentals and company reports including full balance sheet, profit and Loss, debtors, creditors, financial ratios...



View on www.advfn.com http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data
Preview by Yahoo




and pull the ROIC information for the two most recent periods? And what does this mean if you were to type it out in words: ; ; ;0;0;0;0


Again, just trying to sharpen the saw here, so if anyone else has figured this out and is willing to walk me through it, either with this example or another, I'd appreciate as I am sure others would who have been, or are curious about this as well.


Intrigued,
Brent

Sun Mar 8, 2015 11:15 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Don't use the AdvFN elements as an example. They have special processing
triggered by the "source" portion of the element definition. It tells the
add-in to us the smfGetAdvFNElement() function, instead of the typical
RCHGetTableCell() function that many element definitions use.

From the smf-elements-info.txt file distributed with the add-in:

------------------------------

New method of defining an element (a much simpler and more flexible
version):

#;source;name;formula

...where:

- "#" is the index for all element definitions. It can be anything from
1 to 20000.

- "source" can be whatever you want. I try to use a unique group name
for all elements coming from the same web page. It is informational only,
and not critical to anything.

- "name" is the name of the element, and can be whatever you want. It is
informational only, and not critical to anything.

- "formula" can be the add-in formula you use in a worksheet cell,
except that five tildas (i.e. "~~~~~") should be where you want the
"Ticker" parameter from the RCHGetElementNumber() formula to be placed.

For example, suppose I had this formula retrieving the 20-day volume for
ticker MMM from website stockscores.com:

=RCHGetTableCell("http://www.stockscores.com/quickreport.asp?ticker=MMM",1,"20
Day Avg Vol")

I could define an element number by adding an element number, the name of
the source, the name of the element, and then the formula (changing the
ticker symbol to "~~~~~"):

785;StockScores;20 Day Avg Vol;=RCHGetTableCell("
http://www.stockscores.com/quickreport.asp?ticker=~~~~~",1,"20 Day Avg Vol")

Once loaded, this formula would be the equivalent of the original formula:

=RCHGetElementNumber("MMM",785)

------------------------------

The add-in loads element definitions from files smf-elements-0.txt thru
smf-elements-20.txt, in that order, if they exist. I'd suggest creating the
currently non-existing smf-elements-20.txt file for your own element
definitions. Otherwise, if you put them into an existing file, they would
get wiped out if you need to replace the file with a new update from me.
The #20 file will also be loaded last, so you can override and replace
other element definitions by just reusing the element number.

------------------------------

On Sun, Mar 8, 2015 at 11:01 AM, freefaller6@... wrote:

> Is there anyone out there with a good understanding of excel (as I do),
> that has been able to figure out how to create a new element number? I
> have spent the morning reading through the group Tips and FAQs, but still
> cannot figure it out. I want to learn not out of need, but rather simply
> to increase my understanding of the add-in and perhaps be able to help out
> another group member in the future.
>
> For starters, where the does url portion of the element definition come
> from?
>
> If we use an example of the ROIC for FY1 and FY2; the element definitions
> in smf-elements-7.txt are:
>
> FY1;AdvFN-A;1;>RATIOS CALCULATIONS;>Return on Capital Invested (ROCI); ;
> ;0;0;0;0
>
> FY2;AdvFN-A;2;>RATIOS CALCULATIONS;>Return on Capital Invested (ROCI); ;
> ;0;0;0;0
>
> Using aapl as an example, how does this now to go here: Apple Company
> Financial Information
> <http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data>
> [image: image]
> <http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data>
> Apple Company Financial Information
> <http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data>
> Apple Computer Inc financial information, fundamentals and company reports
> including full balance sheet, profit and Loss, debtors, creditors,
> financial ratios...
> View on www.advfn.com
> <http://www.advfn.com/stock-market/NASDAQ/AAPL/financials?btn=annual_reports&mode=company_data>
> Preview by Yahoo
>
>
> and pull the ROIC information for the two most recent periods? And what
> does this mean if you were to type it out in words: ; ; ;0;0;0;0
>
> Again, just trying to sharpen the saw here, so if anyone else has figured
> this out and is willing to walk me through it, either with this example or
> another, I'd appreciate as I am sure others would who have been, or are
> curious about this as well.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 11.15

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)