Jumat, 01 Februari 2013

[smf_addin] Digest Number 2490

15 New Messages

Digest #2490
3a
Re: GetElementNumber "Error" by "bozo" calhandon
3b
Re: GetElementNumber "Error" by "Randy Harmelink" rharmelink
3c
Re: GetElementNumber "Error" by "bozo" calhandon
3d
Re: GetElementNumber "Error" by "bozo" calhandon

Messages

Thu Jan 31, 2013 7:29 pm (PST) . Posted by:

"jsawyermib" jsawyermib

The only site I've found that carries this data is '4-traders.com'. Does anyone know how to download this into excel or is aware of another site where this might also be available?

Thu Jan 31, 2013 7:48 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Try:

=0+SUBSTITUTE(RCHGetTableCell("
http://www.4-traders.com/3M-CO-4836/financials/",4,"(EBITDA)")," ","")

On Thu, Jan 31, 2013 at 8:29 PM, jsawyermib jsawyermib@yahoo.com> wrote:

> The only site I've found that carries this data is '4-traders.com'. Does
> anyone know how to download this into excel or is aware of another site
> where this might also be available?
>

Fri Feb 1, 2013 2:49 am (PST) . Posted by:

"jsawyermib" jsawyermib

Randy, that's brilliant. Works great - many thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> Try:
>
> =0+SUBSTITUTE(RCHGetTableCell("
> http://www.4-traders.com/3M-CO-4836/financials/",4,"(EBITDA)")," ","")
>
> On Thu, Jan 31, 2013 at 8:29 PM, jsawyermib wrote:
>
> > The only site I've found that carries this data is '4-traders.com'. Does
> > anyone know how to download this into excel or is aware of another site
> > where this might also be available?
> >
>

Thu Jan 31, 2013 10:11 pm (PST) . Posted by:

"bozo" calhandon

Thanks for your reply, Randy. So, you primarily use AdvFN for statements and detailed financial info?


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Thursday, January 31, 2013 6:33 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Which site has the most accurate Fundamental data?


I generally avoid Yahoo financial statements. They've pigeon-holed the data into as few line items as possible.

When I was doing a type of value calculation a few years ago, I needed "Cash" -- which Yahoo does have on their Key Statistics web page. However, you can't derive it from their balance sheet if a company has "Restricted Cash", because Yahoo includes the "Restricted Cash" in their "Cash and Equivalents" bucket. For example, according to Yahoo, GM has $24.18B in "Cash and Equivalents", while AdvFN reports $23.32B in "Cash and Equivalents" and the remaining $0.86B is in "Restricted Cash" (which is not the equivalent of cash).

I ran into similar issues with a few other line items on Yahoo.
On Thu, Jan 31, 2013 at 4:29 PM, bozo bozo@donandcarla.com> wrote:

Which of the many sites supported by the SMF add-in do you believe provides the most accurate and up-to-date fundamental financial data? Not just the normal past 12 months ratios that every site seems to have, but multi-quarter / year data for earnings, revenue, margins, debt, cash flow, management effectiveness ratios, etc. Along with 5+ years of quarterly and annual income statements & balance sheets.

AdvFN? Yahoo? MSN? Other?



Thu Jan 31, 2013 10:31 pm (PST) . Posted by:

"calhandon" calhandon


Thanks for the link to this article, Jose. It's the same conclusion
I've come to, especially now that it's so easy to compare the various
sources via Randy's add-in. It is quite disconcerting.

Right now I'm facing drastically different numbers for "5-Year Annual
Average Dividend Growth Rate %", for various industries, for just 10
stocks (see below). And I'm sure there are 100 reasons why two websites
have two totally different numbers, including the fact that none of the
financial websites use the same Industry and/or Sector classification
system (grrr). Which is why I'm basically asking folks on the list what
website they have come to trust.

RCHGetElementNumber() ...

64 13675
MSN Reuters
----- -------
13.15 4.43
23.07 10.92
12.58 4.30
15.29 10.15
Error 4.17
1.75 -0.87
NA 4.97
15.29 10.15
4.84 3.61
0.15 7.60

--- In smf_addin@yahoogroups.com, Jacob Jose wrote:
>
> Interesting article about how different sources calculate the data
differently
>
>
http://seekingalpha.com/article/1043751-the-not-so-standard-trailing-p-e\
-ratio

>
> Regards,
> Jose L. Jacob

Thu Jan 31, 2013 10:54 pm (PST) . Posted by:

"Chee Kean C" cckean

Does anyone know if AdvFN provide csv file for financial data like what MorningStar is doing?

--- In smf_addin@yahoogroups.com, "bozo" wrote:
>
> Hello All,
>
> This is a general question to everyone ...
>
> Which of the many sites supported by the SMF add-in do you believe provides
> the most accurate and up-to-date fundamental financial data? Not just the
> normal past 12 months ratios that every site seems to have, but
> multi-quarter / year data for earnings, revenue, margins, debt, cash flow,
> management effectiveness ratios, etc. Along with 5+ years of quarterly and
> annual income statements & balance sheets.
>
> AdvFN? Yahoo? MSN? Other?
>
> Thank you all, in advance,
>
> -Don
>

Thu Jan 31, 2013 11:08 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

These days, I don't do a lot of fundamental analysis. I primarily do
covered calls on a few major ETFs and sentimental favorite stocks, and
dabble with Dividend Champions.

But I was always the "paralysis by analysis" guy. So busy looking at the
data, I could never make a decision. Even with horse racing...

On Thu, Jan 31, 2013 at 11:11 PM, bozo bozo@donandcarla.com> wrote:

>
> Thanks for your reply, Randy. So, you primarily use AdvFN for statements
> and detailed financial info?****
>
> ****
>
>
>

Fri Feb 1, 2013 12:40 am (PST) . Posted by:

"bozo" calhandon

Howdy Randy,

> But I was always the "paralysis by analysis" guy.

Unfortunately, this is where I find myself too many times as well. That's where I'm hoping the SMF add-in will help, by automating a lot of the manual website work I've been doing for years. BUT, the data has to be reliable and trustworthy enough for me to stop most of my manual Internet work.

> I primarily do covered calls ...

We've not gotten into any Options trading yet. Can you recommend a good site to learn some of the basics like covered calls, as you mention?

Personally, I would also like to learn more about investing directly in individual bonds of various types (U.S. Treasuries, Corp debt, etc.), instead of using ETFs that may or may not have the particular "mix" we're looking for. Any suggestions for this?

Cheers for now,

-Don


----------------------------------------------------------
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, February 01, 2013 12:08 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Which site has the most accurate Fundamental data?

These days, I don't do a lot of fundamental analysis. I primarily do covered calls on a few major ETFs and sentimental favorite stocks, and dabble with Dividend Champions.

But I was always the "paralysis by analysis" guy. So busy looking at the data, I could never make a decision. Even with horse racing...

On Thu, Jan 31, 2013 at 11:11 PM, bozo bozo@donandcarla.com> wrote:

Thanks for your reply, Randy. So, you primarily use AdvFN for statements and detailed financial info?

Thu Jan 31, 2013 11:27 pm (PST) . Posted by:

"bozo" calhandon

Thank you for all of these troubleshooting tips, Randy. They have been duly noted in the "SMF Add-In Notes" document I started a while back.


> Right now, all of those elements are working fine for me,

And for me, they are still showing "Error" and "N/A". I've done everything short of rebooting my PC, which is next on the list (sigh).

> The best way to verify anything would be to look at the content of the web page the add-in has stored,

How do I do this?


> And, keep in mind that smfForceRecalculation can be used to ...

I keep seeing this mentioned, so I did search the Messages section on this topic earlier today, but all I could find is where you told someone to use the smfForceRecalculation Macro - but there are no Macros showing up for the SMF add-in in my Developer / Macros pop-up, and nothing listed in the VB code section as a named function either. So, how do I actually force a recalc for SMF?

-Don



From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Thursday, January 31, 2013 6:55 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] GetElementNumber "Error"


The MSN web pages you cite aren't where the add-in gets the information. They get it from here:

http://investing.money.msn.com/investments/stock-report?CR=1 http://investing.money.msn.com/investments/stock-report?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=MDT> &AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=MDT

You could have found that by doing:

=RCHGetElementNumber("P-URL",63)

...or looking at the loaded element definition with:

=smfGetAParms(63)

"N/A" is not a value the add-in makes up, so that had to be what was being posted at the time.

Right now, all of those elements are working fine for me, for both GWW and MDT, so I would assume something was being updated, or you had a bad web page retrieval. At least nothing I can track down from here.

The best way to verify anything would be to look at the content of the web page the add-in has stored, because the functions don't dynamically get the data from the Internet for each function. They retrieve the web page once, and then extract the data from that stored web page. So even looking at the current web page in your browser may not tell you what happened. And, keep in mind that smfForceRecalculation can be used to retrieve fresh copies of all necessary web pages (for more info, see related items in the LINKS area of the Yahoo group).

The smfGetTagContent-Quick-Webpage-Examination.xls workbook I uploaded the other day could help though. For example, if I entered this for the "Web page":

http://investing.money.msn.com/investments/stock-report?CR=1 http://investing.money.msn.com/investments/stock-report?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=MDT> &AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=MDT

...and "td" for the "HTML Tag", and "Dividends (5-Year Annual Avg.)" for the "Start String:", then I get a list of the table cells following that start string. What I'm looking at currently looks like this, which are the three values you were trying to retrieve, and the values I am getting:

Count
Tag Contents

1
17.13

2
7.06

3
5.51

4


5
PRICE RATIOS

Unfortunately, AdvFN isn't quite as easy to trouble shoot...

Thu Jan 31, 2013 11:53 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

On Fri, Feb 1, 2013 at 12:27 AM, bozo bozo@donandcarla.com> wrote:

>
> Thank you for all of these troubleshooting tips, Randy. They have been
> duly noted in the "SMF Add-In Notes" document I started a while back.****
>
> **
>

> And, keep in mind that smfForceRecalculation can be used to ...
>
>
>
> I keep seeing this mentioned, so I did search the Messages section on this
> topic earlier today, but all I could find is where you told someone to use
> the smfForceRecalculation Macro - but there are no Macros showing up for
> the SMF add-in in my Developer / Macros pop-up, and nothing listed in the
> VB code section as a named function either. So, how do I actually force
> a recalc for SMF?
>

That's why I said to go to the LINKS area of the Yahoo group -- there are
two items there, one dealing with smfForceRecalculation, and one dealing
with IE cached web pages. Both affect web page updating of the add-in.

The macros don't show up in the macros pop-up because it doesn't show
add-in macros. Note that the drop-down box under the list of macros
defaults to "All Open Workbooks" -- an add-in is NOT an open workbook.
There is no option there to list *ALL* available macros, including those in
add-ins.

> > Right now, all of those elements are working fine for me,****
>
> ** **
>
> And for me, they are still showing "Error" and "N/A". I've done
> everything short of rebooting my PC, which is next on the list (sigh).
>

Read the two items on smfForceRecalculation first. Either, or both, might
solve your problem.

I've NEVER needed to reboot my PC to fix something with the add-in. Heck,
I've probably only rebooted my PC about 5 times in the last two years. I
leave it on 24 hours a day. because I'm almost always near it.

But exiting and restarting EXCEL will do about the same thing
smfForceRecalculation does, because it resets the VBA environment for the
add-in.

> The best way to verify anything would be to look at the content of the
> web page the add-in has stored,
>
> ** **
>
> How do I do this?
>

That was the whole point of the
smfGetTagContent-Quick-Webpage-Examination.xls workbook discussion. It will
be extracting data from the stored copy of the web page the add-in has, NOT
a current version of the web page your browser might return.

And, keep in mind that because EXCEL is a MicroSoft product, it uses the
Internet Explorer Internet engine, so what happens in another browser like
FireFox may be completely irrelevant.

In the past, I've even seen errant servers, where whether you refresh the
add-in functions, or refresh a web page in a browser, each refresh of a web
page may alternate between a good version of the web page and a bad version
of the web page, because one of the servers of the data provider didn't get
data updates for some reason. So, any time your request came from that
errant server, you get a bad copy of the web page.

This has happened at least 3 or 4 times in the last few years. For example:

http://finance.groups.yahoo.com/group/smf_addin/message/15851
http://finance.groups.yahoo.com/group/smf_addin/message/9753

Fri Feb 1, 2013 12:24 am (PST) . Posted by:

"bozo" calhandon

Hi Randy,

Yes, the "Error" entries are in the Financial Sector.


> You can't grab the data by year without handling it yourself.
Such as retrieving the dates first, and then deciding which
columns of data you want.

That's kinda what I figured, as I contemplated the whole idea of see various financial data, for multiple years, displayed in Sparklines. The data isn't for comparison, so fiscal periods don't matter. All I want to see is a line going from the lower left to the upper right, regardless of what data point is being viewed. I'm much more a visual person than a number person. Same for my wife.

Thank you again,

-Don


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Thursday, January 31, 2013 7:10 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] GetElementNumber "Error"


The most common reason to get "Error" on elements 5296-5300 would be that you are trying to retrieve such data for a financial company. They don't have a line item called "Total Revenue".

You can't grab the data by year without handling it yourself. Such as retrieving the dates first, and then deciding which columns of data you want.

It wouldn't make sense to, anyway, since companies can have different fiscal years. Most end in December, following the calendar year, but I believe companies can define the fiscal year over any 12-month period? What does "2012" mean? For one company, it might mean the fiscal year ending January of 2012 and another it might mean the fiscal year ending December of 2012. And you can run into companies that CHANGE the ending date of their fiscal period. So, one fiscal year might be 6 months after the last fiscal year (I think this is extremely rare).

If you really want to compare compatible annual periods, you'd probably need to go to rolling quarterly periods. But, you'll still run into issues, because different companies have different reporting periods, so one company might report their new quarter in mid-January, while another does in in mid-February (or later). And, on quarterly reporting periods, you do have to be careful about whether a particular line item is being reported for the quarter, or for the Year-to-date cumulative effect since the last fiscal year ended.

Details, details, details...
On Thu, Jan 31, 2013 at 6:05 PM, bozo bozo@donandcarla.com> wrote:

In looking deeper into the 5296-5300 element numbers (Total Revenue from the Annual Reports page on AdvFN), the 2 stock that are returning "Error" are because their FY1 - FY5 dates are different than the rest.

How should these formulas be entered to that the same years are returned for all stocks (ie. 2008-2012)?


Fri Feb 1, 2013 12:47 am (PST) . Posted by:

"bozo" calhandon

Thank you, Randy. I'll look into both tomorrow.

-Don


----------------------------------------------------------
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, February 01, 2013 12:54 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] GetElementNumber "Error"

On Fri, Feb 1, 2013 at 12:27 AM, bozo bozo@donandcarla.com> wrote:

Thank you for all of these troubleshooting tips, Randy. They have been duly noted in the "SMF Add-In Notes" document I started a while back.

> And, keep in mind that smfForceRecalculation can be used to ...

I keep seeing this mentioned, so I did search the Messages section on this topic earlier today, but all I could find is where you told someone to use the smfForceRecalculation Macro - but there are no Macros showing up for the SMF add-in in my Developer / Macros pop-up, and nothing listed in the VB code section as a named function either. So, how do I actually force a recalc for SMF?

That's why I said to go to the LINKS area of the Yahoo group -- there are two items there, one dealing with smfForceRecalculation, and one dealing with IE cached web pages. Both affect web page updating of the add-in.

The macros don't show up in the macros pop-up because it doesn't show add-in macros. Note that the drop-down box under the list of macros defaults to "All Open Workbooks" -- an add-in is NOT an open workbook. There is no option there to list *ALL* available macros, including those in add-ins.

> Right now, all of those elements are working fine for me,

And for me, they are still showing "Error" and "N/A". I've done everything short of rebooting my PC, which is next on the list (sigh).

Read the two items on smfForceRecalculation first. Either, or both, might solve your problem.

I've NEVER needed to reboot my PC to fix something with the add-in. Heck, I've probably only rebooted my PC about 5 times in the last two years. I leave it on 24 hours a day. because I'm almost always near it.

But exiting and restarting EXCEL will do about the same thing smfForceRecalculation does, because it resets the VBA environment for the add-in.
> The best way to verify anything would be to look at the content of the web page the add-in has stored,

How do I do this?

That was the whole point of the smfGetTagContent-Quick-Webpage-Examination.xls workbook discussion. It will be extracting data from the stored copy of the web page the add-in has, NOT a current version of the web page your browser might return.

And, keep in mind that because EXCEL is a MicroSoft product, it uses the Internet Explorer Internet engine, so what happens in another browser like FireFox may be completely irrelevant.

In the past, I've even seen errant servers, where whether you refresh the add-in functions, or refresh a web page in a browser, each refresh of a web page may alternate between a good version of the web page and a bad version of the web page, because one of the servers of the data provider didn't get data updates for some reason. So, any time your request came from that errant server, you get a bad copy of the web page.

This has happened at least 3 or 4 times in the last few years. For example:

http://finance.groups.yahoo.com/group/smf_addin/message/15851
http://finance.groups.yahoo.com/group/smf_addin/message/9753

Thu Jan 31, 2013 11:33 pm (PST) . Posted by:

"gz_cp" gz_cp

Thank you very much.

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> You can get the whole time series with something like:
>
> =smfGetCSVFile("https://research.stlouisfed.org/fred2/data/SP500.csv")
>
> On Wed, Jan 30, 2013 at 9:07 PM, gz_cp wrote:
>
> >
> > I just found that you have smfGetEconData, but does it only return data
> > for one specific date?
> >
> > Could you make a function to get the whole time series in one function
> > call just like RCHGetHTMLTable where we can use an array function?
> >
> > I asked because for high frequency data like SP500, if we want to get 50
> > years of history, using smfGetEconData will be too resource consuming
> > because it needs tens of thousand functions running at the same time.
> >
>

Fri Feb 1, 2013 12:33 am (PST) . Posted by:

"gz_cp" gz_cp

Randy,

I remembered I needed to set somewhere in IE's Options so that everytime I run the Add-on functions it always tries to get the latest data rather than reading from the cache for old data.

But I couldn't find where in Option I should set in my IE9 now.

Could you please remind me?

Thank you.

XP

Fri Feb 1, 2013 12:45 am (PST) . Posted by:

"bozo" calhandon

Try ...

Tools - Internet Options - General Tab, "Browsing History" section, click
"Settings"; button, select "Every time I visit the webpage".



From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of gz_cp
Sent: Friday, February 01, 2013 1:33 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Internet Explorer Cache Setting


Randy,

I remembered I needed to set somewhere in IE's Options so that everytime I
run the Add-on functions it always tries to get the latest data rather than
reading from the cache for old data.

But I couldn't find where in Option I should set in my IE9 now.

Could you please remind me?

Thank you.

XP

Tidak ada komentar:

Posting Komentar