Jumat, 17 Juni 2011

[smf_addin] Digest Number 1859

Messages In This Digest (14 Messages)

Messages

1a.

Re: SEC.gov

Posted by: "rb_luther" rb_luther@yahoo.com   rb_luther

Thu Jun 16, 2011 5:10 am (PDT)



Thanks for the input Randy. I really appreciate it. I just figured this group could "pigeon-hole" the data as the other sites have done, so that way we are not at the mercy of anyone.

I understand the monster task this would be. Thanks again for answering my question.

Rob

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> When I looked at that a few years ago, it was a nightmare. What would work
> for one statement might not work for another. What would work for one
> company might not work for another. It just wasn't worth the effort.
>
> You'll only find the phrase "Net Income" several dozen times, if not more.
>
> It's one of the reasons we use processing statements from places like AdvFN,
> Yahoo, MSN, etc. Someone else has gone through all the work of interpreting
> the financial statements and "pigeon-holing" the data where it apparently
> belongs. However, in some cases, it leaves you at their mercy as well. Your
> loss of ROA and ROE from AdvFN, for example. Or, "Cash and Equivalents" on
> Yahoo includes "Restricted Cash", so it won't be comparable to "Cash and
> Equivalents" on another service that breaks out "Restricted Cash" as a
> separate item.
>
> And, as you'll note from your URLs, you also need a way to look up each
> individual statement by ticker symbol.
>
> On Wed, Jun 15, 2011 at 2:55 PM, rb_luther <rb_luther@...> wrote:
>
> >
> > The only way to retrieve the SEC data would be to retrieve each companies
> > annual and quarterly reports and then pull data from it, essentially
> > creating your own consolidated statement of historical data, which could
> > then be manipulated to calculate any ratios and averages you would like, for
> > any stock you would like. The advantage of this is that you do not rely on
> > secondary sources of information, with the chance that those sources remove
> > content.
> >
> > I see what you are saying about the historical data, say, in 2003 being a
> > differnt format than 2011. Even though it's in a different format can you do
> > a character search for say, Net Income?
> >
> > An elementary example would be to retrieve annual reports from 2002 - 2011
> > for say "BKE" on the SEC website. Once retrieved pull the "net income" line
> > and the "shareholder's equity" line, so in excel you could call a function
> > to retrieve the net income and divide it by the shareholder's equity
> > function for each of the 10 previous years to get the ROE of BKE for the
> > past 10 years. Taking it a step further would be to retrieve all line items
> > in the annual and quarterly reports, and by changing a ticker symbol in your
> > spreadsheet you could retrieve the data for the new ticker.
> >
> > As I write this, it is becoming apparent to me the extensive work involved,
> > but the outcome would be priceless. No more retrieving data from numerous
> > secondary sources, being able to retive data as soon as it is published,
> > less maintenance to the smf_addin due to numerous website formating changes,
> > and the risk of data being removed all together.
> >
> > What do you think? Is it practical? Thanks for the input. I appreciate it.
> >
> > If your up for the challenge, I would be more than willing to help out any
> > way that I can. Let me know.
> >
> > Here are some url's that would be the first run at retrieving the net
> > income and shareholder's equity from annual reports.
> >
> > 2001 BKE annual report:
> >
> > http://www.sec.gov/Archives/edgar/data/885245/000095013702002495/c69084ex13.txt
> >
> > 2010 BKE annual Report:
> >
> > http://www.sec.gov/Archives/edgar/data/885245/000115752311001807/a6663779.htm
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
2a.

Extracting data from E-Trade

Posted by: "PhillipG" philgiz@zoominternet.net   philgiz_2000

Thu Jun 16, 2011 5:35 am (PDT)



Hi Randy..
I have an account with E-trade and am trying to extract some data from their site but get 'error' messages when I try to run it. I am using the 'rchgettablecell' function which works well with Yahoo, Smart Money online and others. I am also logged into my account. Any suggestions would be appreciated.
Phil

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2b.

Re: Extracting data from E-Trade

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jun 16, 2011 6:45 am (PDT)



All I can suggest is to make sure you are logged in, using the EXCEL Web
Query process within EXCEL (keyboard shortcut alt+d+d+w). Logging in with
something like FireFox won't do the trick -- it uses different cookies.

Otherwise, you can use the smfGetTagContent() function to "explore" the
page. For example:

=smfGetTagContent(URL,"table",n)

...varying n from 1 to whatever, will allow you to look at the tables on the
web page that are available to the add-in.

On Thu, Jun 16, 2011 at 5:35 AM, PhillipG <philgiz@zoominternet.net> wrote:

> I have an account with E-trade and am trying to extract some data from
> their site but get 'error' messages when I try to run it. I am using the
> 'rchgettablecell' function which works well with Yahoo, Smart Money online
> and others. I am also logged into my account. Any suggestions would be
> appreciated.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2c.

Re: Extracting data from E-Trade

Posted by: "Phil" philgiz@zoominternet.net   philgiz_2000

Thu Jun 16, 2011 7:34 am (PDT)



Thanks Randy for your quick response.
Phil

From: Randy Harmelink
Sent: Thursday, June 16, 2011 9:45 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Extracting data from E-Trade

All I can suggest is to make sure you are logged in, using the EXCEL Web Query process within EXCEL (keyboard shortcut alt+d+d+w). Logging in with something like FireFox won't do the trick -- it uses different cookies.

Otherwise, you can use the smfGetTagContent() function to "explore" the page. For example:

=smfGetTagContent(URL,"table",n)

...varying n from 1 to whatever, will allow you to look at the tables on the web page that are available to the add-in.

On Thu, Jun 16, 2011 at 5:35 AM, PhillipG <philgiz@zoominternet.net> wrote:

I have an account with E-trade and am trying to extract some data from their site but get 'error' messages when I try to run it. I am using the 'rchgettablecell' function which works well with Yahoo, Smart Money online and others. I am also logged into my account. Any suggestions would be appreciated.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
3a.

Re: Google option quotes?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jun 16, 2011 6:53 am (PDT)



I just checked the option quotes for MMM on Google. At the bottom of the
page, it says:

"Option data delayed by 15 minutes."

So, not real time option quotes?

On Wed, Jun 15, 2011 at 6:48 PM, toronto667788 <toronto667788@yahoo.ca>wrote:

>
> Is there any template to get the google real time option quotes?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
4.1.

Re: RCHGetYahooHistory, MA slope

Posted by: "Dan Lancaster" dansgonefishing@yahoo.com   dansgonefishing

Thu Jun 16, 2011 11:51 am (PDT)





I use MA slope for buy/sell signals. I agree with Randy. You should download the price history with one RCHGetYahooHistory call then do all of your calculations in the spreadsheet using OFFSET, AVERAGE and SLOPE. The download will be quicker and you will have a lot more flexibility in your analysis calculations. For example, the Excel functions allow cell references so you can set up driving cells to vary the time period over which the average is calculated and the time period over which the slope is calculated. Both had a big impact on the results of my back testing of this method.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Not using the same method. There are times I would like to use the OFFSET()
> function to extract data from the function, but OFFSET() only accepts a
> range as input. However, INDEX() does allow either a range or an array as
> an input, so you could do it this way:
>
> =INDEX(smfTech(RCHGetYahooHistory("MMM",,,,,,,,,,1,1,100,6),"SMA",50),100)
> =INDEX(smfTech(RCHGetYahooHistory("MMM",,,,,,,,,,1,1,100,6),"SMA",50),99)
>
> The first gets you the current 50-day SMA. The second gets you the previous
> 50-day SMA.
>
> Another option would be to mathematically compute it. Today's 50-day SMA and
> yesterday's 50-day SMA have 49 common days that went into their calculation.
> So, really, you're just looking at the change between today's closing price
> and the the closing price from 51 days ago, and then dividing that
> difference by 50. So:
>
> =(RCHGetYahooHistory("MMM",,,,,,,,"A",0,,,1,1)-INDEX(RCHGetYahooHistory("MMM",,,,,,,,"A",0,,,51,1),51))/50
>
> I would just drop the division by 50 and call it a 50-day rate-of-change.
>
> On Tue, Jun 14, 2011 at 5:31 PM, Jacob Jose <pepecan47@...> wrote:
>
> >
> > Randy, is there any way to get the last 51 days of data and do the
> > AVERAGE of day 2 to 51? I don't see how to automate my workbook if I have
> > to provide the dates hard coded.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (28)
5a.

MSN-10-Year-Summaries errors in elements 153-192 and 203-212

Posted by: "sacrifice_me" sacrifice_me@yahoo.com   sacrifice_me

Thu Jun 16, 2011 1:50 pm (PDT)



Hello guys,

I am new around here. Sorry, if I this has been discussed before but I didn't find an answer.

I just downloaded the SMF-Template-MSN-10-Year-Summaries.xls and figured that elements 153 through 192 and 203 through 212 are coming up with 'error'. Does anyone know more about this?

Other than this, I don't have problems with the add-in and I'd like to congratulate Randy on his work on this great tool.

Regards,
Mityo

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5b.

Re: MSN-10-Year-Summaries errors in elements 153-192 and 203-212

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jun 16, 2011 1:53 pm (PDT)



The template from the files area is working fine for me using ticker MMM.

I suspect you just need the new smf-elements-1.txt file from the "Works in
Progress" folder of the Files area of the group. I had to redefine a number
of MSN elements back in January when MSN changed the structure of their web
site.

On Thu, Jun 16, 2011 at 1:18 PM, sacrifice_me <sacrifice_me@yahoo.com>wrote:

>
> I just downloaded the SMF-Template-MSN-10-Year-Summaries.xls and figured
> that elements 153 through 192 and 203 through 212 are coming up with
> 'error'. Does anyone know more about this?
>
> Other than this, I don't have problems with the add-in and I'd like to
> congratulate Randy on his work on this great tool.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
6a.

Fields off by a factor of 1000?

Posted by: "toolshed37" divisortheory@gmail.com   toolshed37

Thu Jun 16, 2011 6:13 pm (PDT)



Is this expected? When I run, for example:

=RCHGetElementNumber("IBM", 941)

It tells me $197,030,000.00, even though it should be billions. I realize financial data is often reported in thousands, but wouldn't it be better to return the real number for purposes of computation?

It's also possible this is simply a bug.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
6b.

Re: Fields off by a factor of 1000?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jun 16, 2011 6:47 pm (PDT)



I had a choice of how to convert numbers with a "B" or an "M" suffix. Since
most of the other numbers I was running in to at the time were being
expressed in thousands of dollars, I decided to keep that consistency. But
it does mean that sometimes the results are unusual.

On Thu, Jun 16, 2011 at 5:49 PM, toolshed37 <divisortheory@gmail.com> wrote:

> Is this expected? When I run, for example:
>
> =RCHGetElementNumber("IBM", 941)
>
> It tells me $197,030,000.00, even though it should be billions. I realize
> financial data is often reported in thousands, but wouldn't it be better to
> return the real number for purposes of computation?
>
> It's also possible this is simply a bug.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
7a.

Errors: SMF-Template-Morningstar-Key-Ratios.xls

Posted by: "Charley Kyd" kyd@exceluser.com   charleykyd

Thu Jun 16, 2011 6:43 pm (PDT)



In SMF-Template-Morningstar-Key-Ratios.xls, I'm getting "Error" results in rows 4-17.

I began testing in cell D4. I found that RCHGetURLData1(pURL) fails, but RCHGetURLData2(pURL) works.

(It looks like any function that calls...
http://financials.morningstar.com/ratios/r.html?t=IBM
...fails with aparm(11)=0.)

What's my recommend course of action? Do I create my own versions of each failed data element? Change yours? Or take some other action?

Thanks.

Charley Kyd

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
7b.

Re: Errors: SMF-Template-Morningstar-Key-Ratios.xls

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jun 16, 2011 6:53 pm (PDT)



It was one of the reasons I programmed the possibility of using one or the
other. However, the results are not consistent between different versions of
IE. It also means scripting runs, images are processed, and sometimes
pop-ups occur.

You can change your element definitions -- create a new file that overrides
the original definitions and tell it to use the other method.

On Thu, Jun 16, 2011 at 6:42 PM, Charley Kyd <kyd@exceluser.com> wrote:

> In SMF-Template-Morningstar-Key-Ratios.xls, I'm getting "Error" results in
> rows 4-17.
>
> I began testing in cell D4. I found that RCHGetURLData1(pURL) fails, but
> RCHGetURLData2(pURL) works.
>
> (It looks like any function that calls...
> http://financials.morningstar.com/ratios/r.html?t=IBM
> ...fails with aparm(11)=0.)
>
> What's my recommend course of action? Do I create my own versions of each
> failed data element? Change yours? Or take some other action?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
8a.

Optional argument to ignore caching?

Posted by: "Charley Kyd" kyd@exceluser.com   charleykyd

Thu Jun 16, 2011 6:51 pm (PDT)



Randy,

I see that you offer five functions that cache pages. Generally, that's a good thing. But at times, I would like to return one value from each of thousands of pages.

It would be nice if you offered an optional argument that caused your functions not to cache.

Thanks.

Charley Kyd

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
8b.

Re: Optional argument to ignore caching?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jun 16, 2011 7:01 pm (PDT)



If I suspected more than a few people were regularly collecting data from
that many web pages, I would shut down the add-in immediately. I have no
desired to lose access to the free data that services have made available.
It would be easy enough for them to make the data unavailable to something
like the add-in functions.

It was one of the things I considered when I decided to share the add-in. I
almost didn't.

You should be able to pare down the number of stocks you deal with through
the various screening tools available.

On Thu, Jun 16, 2011 at 6:50 PM, Charley Kyd <kyd@exceluser.com> wrote:

>
> I see that you offer five functions that cache pages. Generally, that's a
> good thing. But at times, I would like to return one value from each of
> thousands of pages.
>
> It would be nice if you offered an optional argument that caused your
> functions not to cache.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! News

Get it all here

Breaking news to

entertainment news

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

New web site?

Drive traffic now.

Get your business

on Yahoo! search.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Find useful articles and helpful tips on living with Fibromyalgia. Visit the Fibromyalgia Zone today!

Diposting oleh EXCEL FEED LEARNING di 03.38

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)