Messages In This Digest (15 Messages)
- 1a.
- Re: Incomplete Update and Missing Values From: Randy Harmelink
- 2a.
- SEC.gov From: rb_luther
- 2b.
- Re: SEC.gov From: Randy Harmelink
- 2c.
- Company and It's Competitors From: Au
- 2d.
- Re: Company and It's Competitors [1 Attachment] From: Randy Harmelink
- 2e.
- Re: SEC.gov From: rb_luther
- 2f.
- Re: Company and It's Competitors From: Au
- 2g.
- Re: Company and It's Competitors From: Randy Harmelink
- 2h.
- Re: Company and It's Competitors From: Au
- 2i.
- Re: Company and It's Competitors From: Randy Harmelink
- 2j.
- Re: SEC.gov From: Randy Harmelink
- 3a.
- Excel 2010 From: Nolan Madson
- 3b.
- Re: Excel 2010 From: mkorgie
- 4a.
- Google option quotes? From: toronto667788
- 4b.
- Re: Google option quotes? From: Randy Harmelink
Messages
- 1a.
-
Re: Incomplete Update and Missing Values
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 10:38 am (PDT)
On Tue, Jun 14, 2011 at 5:36 PM, Bob <bbockjr@yahoo.com > wrote:
> As you suggested, I created a new worksheet from scratch, entered the
formula, saved it, and
> reopened it and I did not get the error. That was promising.
That's what should happen.
> Then, I opened the spreadsheet with the one formula, got the error,
deleted the cell, saved it,
> reopened it and did not get the error. So it's probably the formula.
Something not right there.
> I tried creating a new workbook and copying each of the sheets in my
original workbook to it and
> I got a "NAME!" error in all of the cells containing the formula (because
the add-in at the time was
> in c:\Program Files\SMF Add-In instead of c:\Users\Bob\SMF_Add-In) . The
link address was
> contained in all of the formula cells. I used the Replace function to
remove it from all cells, saved
> the file, but when reopening, the error again occurred.
If you are getting #NAME! errors when copying, I would think you would be
copying cells that HAVE the hard-coded location in them. Otherwise, if you
are copying normal looking add-in functions and getting #NAME! errors, that
would indicate to me that the add-in has not been activated properly within
EXCEL with the add-in manager.
So this makes no sense to me.
> I seem to be able to copy smaller parts of a worksheet to a new blank one
and I don't get the error, but when I
> add more sections containing the formula, or additional sheets, I get the
error again. I'm still stumped.
Sounds like you are copying more than just the formulas.
> I can send you two files. The one called RCHTestDate1.xlsx is the one that
gives me the link error.
> RCHTestDate2.xlsx contains the same formula but does not. Can you supply
your e-mail address
> or let me know how to send them to you?
Just upload them to the files area of the group. I can delete them after
viewing them.
> I'd like to see if you get the same error. If you need files that have the
add-in located in
> C:\Program Files\SMF Add-In, I can move mine back to there and recreate
these files.
The add-in can be located anywhere. It just causes problems if the file
being opened had the add-in in a different location with the file was saved.
If that is true, you need to manually edit out all of the unresolved links
(i.e. the hard-coded location of the add-in that was saved with the file).
> If you can find any difference or have any other suggestions for me to
try, please let me know.
Things aren't making sense to me.
- 2a.
-
SEC.gov
Posted by: "rb_luther" rb_luther@yahoo.com rb_luther
Wed Jun 15, 2011 11:50 am (PDT)
Randy,
Is it possible to modify the smf_addin to retrieve annual and quarterly data directly from the SEC.gov website? (I know there are various formats that companies report their data in, but once a database of the format for each company was created, any and all custom calculations using financial data would be possible, and can be retrieved in a timely fashion, instead of having to wait for secondary websites to report the data.).
The reason I ask is because I use the "smfGetADVFNElement" function to retrieve ROE and ROA data for multiple years for various ticker symbols under their "financials" section.
Recently ADVFN.com has removed this data from their website for half of the ticker symbols I have searched so I can no longer retrieve the data. If you look under the quarterly or annual data for Ford (F) the ROE and ROA is present, but if you search for bank of america (BAC)the ROE and ROA for multiple years has vanished.
I know there is a msnmoney function in the smf_addin to retieve ROE data for multiple years, but it seems that it may be more efficient in the long run to go straight to the horses mouth (sec website) to retrieve various data, without the risk of one day them deciding to no longer post the data, as other websites such as ADVFN have done in the past.
I know retriving data from the SEC might be one hell of a task for all companies, but it seems like it may be more of a permanent fix to multiple websites deciding to change their format, or adding and removing data at their leisure.
I have a very basic understanding of visual basic used to create macros in excel, and scraping, but not enough understanding to weigh the pros and cons of such a venture into the SEC website.
Please let me know your thoughts.
Thanks.
Rob
- 2b.
-
Re: SEC.gov
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 12:26 pm (PDT)
Did BAC ever have ROE or ROA on AdvFN? Financial companies have different
line items than manufacturing companies. I'm not sure it would make sense to
compare ROE and ROA between F and BAC. Assets and equity don't have the same
meaning to the two companies.
In any case, last time I looked at getting data from the SEC, it was more
trouble than it was worth. They only had the quarterly and annual
statements, not a consolidated statement of historical data. And that
historical data was not of the same format. At the time, I was hoping XBRL
would build up enough history for things to be more easily accessible.
Can you give me an example of a URL you want to extract data from?
On Wed, Jun 15, 2011 at 11:49 AM, rb_luther <rb_luther@yahoo.com > wrote:
>
> Is it possible to modify the smf_addin to retrieve annual and quarterly
> data directly from the SEC.gov website? (I know there are various formats
> that companies report their data in, but once a database of the format for
> each company was created, any and all custom calculations using financial
> data would be possible, and can be retrieved in a timely fashion, instead of
> having to wait for secondary websites to report the data.).
>
> The reason I ask is because I use the "smfGetADVFNElement" function to
> retrieve ROE and ROA data for multiple years for various ticker symbols
> under their "financials" section.
>
> Recently ADVFN.com has removed this data from their website for half of the
> ticker symbols I have searched so I can no longer retrieve the data. If you
> look under the quarterly or annual data for Ford (F) the ROE and ROA is
> present, but if you search for bank of america (BAC)the ROE and ROA for
> multiple years has vanished.
>
> I know there is a msnmoney function in the smf_addin to retieve ROE data
> for multiple years, but it seems that it may be more efficient in the long
> run to go straight to the horses mouth (sec website) to retrieve various
> data, without the risk of one day them deciding to no longer post the data,
> as other websites such as ADVFN have done in the past.
>
> I know retriving data from the SEC might be one hell of a task for all
> companies, but it seems like it may be more of a permanent fix to multiple
> websites deciding to change their format, or adding and removing data at
> their leisure.
>
> I have a very basic understanding of visual basic used to create macros in
> excel, and scraping, but not enough understanding to weigh the pros and cons
> of such a venture into the SEC website.
>
> Please let me know your thoughts.
>
- 2c.
-
Company and It's Competitors
Posted by: "Au" uanant@ymail.com uanant@ymail.com
Wed Jun 15, 2011 12:53 pm (PDT)
[Attachment(s) from Au included below]
Randy,
I am attaching a jpg along with this mail. I have similar structure in mind.
Now each ticker will have around 9 to 10 competitor's. If I pull the data for a company like IBM along with its competitor's information do you think I may overload the system, I did not try if it works or not but just wanted to check with you if it is putting too much data demand.
I use RCHGetElementNumber("Ticker" ,xxxx) to get data from advfn and there are around 350 elements in one year.
I was working on a model so that it would give a nice comparison for any company with respect to it's competitors.
Thanks for your help
Aku
Attachment(s) from Au
1 of 1 Photo(s)
- 2d.
-
Re: Company and It's Competitors [1 Attachment]
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 2:01 pm (PDT)
Keep in mind that it's not the number of data elements you are grabbing, but
the number of web pages that you grab. Getting the 10 years of annual
information from AdvFN is only two web pages per ticker symbol. So that's
not really an excessive data request. However, one of the reasons AdvFN
elements take a little more time to extract is that the web page contains a
lot of data for the add-in to sift through. But that load is on YOUR
machine, not on the web site you're getting the web page from.
You may want to consider loading the table with the smfUpdateDownloadTable
process (see the "Links" area of the group for info on it), because it loads
values instead of having formulas that would recalculate every time you open
the workbook.
Another thing to consider is that your companies may not have the same
fiscal year-ends.
On Wed, Jun 15, 2011 at 12:53 PM, Au <uanant@ymail.com > wrote:
> Now each ticker will have around 9 to 10 competitor's. If I pull the data
> for a company like IBM along with its competitor's information do you think
> I may overload the system, I did not try if it works or not but just wanted
> to check with you if it is putting too much data demand.
> I use RCHGetElementNumber("Ticker" ,xxxx) to get data from advfn and there
> are around 350 elements in one year.
> I was working on a model so that it would give a nice comparison for any
> company with respect to it's competitors.
>
>
- 2e.
-
Re: SEC.gov
Posted by: "rb_luther" rb_luther@yahoo.com rb_luther
Wed Jun 15, 2011 2:56 pm (PDT)
Randy,
BAC used to have ROE and ROA. I wasn't comparing F and BAC, I was just using an example of a company with and without the data present.
The XBRL historical data on the SEC website will be nice, but we will not be able to retrieve 10 years of historical data in the XBRL format for about another 9 years or so.
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/000095013 702002495/ c69084ex13. txt
2010 BKE annual Report:
http://www.sec.gov/Archives/ edgar/data/ 885245/000115752 311001807/ a6663779. htm
Thanks.
Rob
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Did BAC ever have ROE or ROA on AdvFN? Financial companies have different
> line items than manufacturing companies. I'm not sure it would make sense to
> compare ROE and ROA between F and BAC. Assets and equity don't have the same
> meaning to the two companies.
>
> In any case, last time I looked at getting data from the SEC, it was more
> trouble than it was worth. They only had the quarterly and annual
> statements, not a consolidated statement of historical data. And that
> historical data was not of the same format. At the time, I was hoping XBRL
> would build up enough history for things to be more easily accessible.
>
> Can you give me an example of a URL you want to extract data from?
>
> On Wed, Jun 15, 2011 at 11:49 AM, rb_luther <rb_luther@...> wrote:
>
> >
> > Is it possible to modify the smf_addin to retrieve annual and quarterly
> > data directly from the SEC.gov website? (I know there are various formats
> > that companies report their data in, but once a database of the format for
> > each company was created, any and all custom calculations using financial
> > data would be possible, and can be retrieved in a timely fashion, instead of
> > having to wait for secondary websites to report the data.).
> >
> > The reason I ask is because I use the "smfGetADVFNElement" function to
> > retrieve ROE and ROA data for multiple years for various ticker symbols
> > under their "financials" section.
> >
> > Recently ADVFN.com has removed this data from their website for half of the
> > ticker symbols I have searched so I can no longer retrieve the data. If you
> > look under the quarterly or annual data for Ford (F) the ROE and ROA is
> > present, but if you search for bank of america (BAC)the ROE and ROA for
> > multiple years has vanished.
> >
> > I know there is a msnmoney function in the smf_addin to retieve ROE data
> > for multiple years, but it seems that it may be more efficient in the long
> > run to go straight to the horses mouth (sec website) to retrieve various
> > data, without the risk of one day them deciding to no longer post the data,
> > as other websites such as ADVFN have done in the past.
> >
> > I know retriving data from the SEC might be one hell of a task for all
> > companies, but it seems like it may be more of a permanent fix to multiple
> > websites deciding to change their format, or adding and removing data at
> > their leisure.
> >
> > I have a very basic understanding of visual basic used to create macros in
> > excel, and scraping, but not enough understanding to weigh the pros and cons
> > of such a venture into the SEC website.
> >
> > Please let me know your thoughts.
> >
>
- 2f.
-
Re: Company and It's Competitors
Posted by: "Au" uanant@ymail.com uanant@ymail.com
Wed Jun 15, 2011 3:17 pm (PDT)
You mean using RCHGetTableCell("http://finance. yahoo.com/ ",1,">Market Cap") instead of RCHGetElementNumberq/ks?s=~~ ~~~ ("Ticker" , Number)
_____________________ _________ __
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Wednesday, June 15, 2011 5:00 PM
Subject: Re: [smf_addin] Company and It's Competitors
Keep in mind that it's not the number of data elements you are grabbing, but the number of web pages that you grab. Getting the 10 years of annual information from AdvFN is only two web pages per ticker symbol. So that's not really an excessive data request. However, one of the reasons AdvFN elements take a little more time to extract is that the web page contains a lot of data for the add-in to sift through. But that load is on YOUR machine, not on the web site you're getting the web page from.
You may want to consider loading the table with the smfUpdateDownloadTable process (see the "Links" area of the group for info on it), because it loads values instead of having formulas that would recalculate every time you open the workbook.
Another thing to consider is that your companies may not have the same fiscal year-ends.
On Wed, Jun 15, 2011 at 12:53 PM, Au <uanant@ymail.com > wrote:
Now each ticker will have around 9 to 10 competitor's. If I pull the data for a company like IBM along with its competitor's information do you think I may overload the system, I did not try if it works or not but just wanted to check with you if it is putting too much data demand.
>I use RCHGetElementNumber("Ticker" ,xxxx) to get data from advfn and there are around 350 elements in one year.
>I was working on a model so that it would give a nice comparison for any company with respect to it's competitors.
>
- 2g.
-
Re: Company and It's Competitors
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 3:21 pm (PDT)
Not sure what you're asking? In most cases, RCHGetElementNumber() is just an
RCHGetTableCell() function with saved parameters.
On Wed, Jun 15, 2011 at 3:17 PM, Au <uanant@ymail.com > wrote:
>
> You mean using RCHGetTableCell("http://finance. yahoo.com/ ",1,">Marketq/ks?s=~~ ~~~
> Cap") instead of RCHGetElementNumber("Ticker" , Number)
>
- 2h.
-
Re: Company and It's Competitors
Posted by: "Au" uanant@ymail.com uanant@ymail.com
Wed Jun 15, 2011 3:40 pm (PDT)
In your reply below I could not understand this part "You may want to consider loading the table with the smfUpdateDownloadTable proces"
_____________________ _________ __
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Wednesday, June 15, 2011 5:00 PM
Subject: Re: [smf_addin] Company and It's Competitors
Keep in mind that it's not the number of data elements you are grabbing, but the number of web pages that you grab. Getting the 10 years of annual information from AdvFN is only two web pages per ticker symbol. So that's not really an excessive data request. However, one of the reasons AdvFN elements take a little more time to extract is that the web page contains a lot of data for the add-in to sift through. But that load is on YOUR machine, not on the web site you're getting the web page from.
You may want to consider loading the table with the smfUpdateDownloadTable process (see the "Links" area of the group for info on it), because it loads values instead of having formulas that would recalculate every time you open the workbook.
Another thing to consider is that your companies may not have the same fiscal year-ends.
On Wed, Jun 15, 2011 at 12:53 PM, Au <uanant@ymail.com > wrote:
Now each ticker will have around 9 to 10 competitor's. If I pull the data for a company like IBM along with its competitor's information do you think I may overload the system, I did not try if it works or not but just wanted to check with you if it is putting too much data demand.
>I use RCHGetElementNumber("Ticker" ,xxxx) to get data from advfn and there are around 350 elements in one year.
>I was working on a model so that it would give a nice comparison for any company with respect to it's competitors.
>
- 2i.
-
Re: Company and It's Competitors
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 3:47 pm (PDT)
As I noted, check out the "Links" area of the group for more info on that
process.
On Wed, Jun 15, 2011 at 3:40 PM, Au <uanant@ymail.com > wrote:
>
> In your reply below I could not understand this part "You may want to
> consider loading the table with the smfUpdateDownloadTable proces"
>
- 2j.
-
Re: SEC.gov
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 4:08 pm (PDT)
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@yahoo.com > 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/000095013 702002495/ c69084ex13. txt
>
> 2010 BKE annual Report:
>
> http://www.sec.gov/Archives/ edgar/data/ 885245/000115752 311001807/ a6663779. htm
>
- 3a.
-
Excel 2010
Posted by: "Nolan Madson" nolanmadson@yahoo.com NolanMadson
Wed Jun 15, 2011 1:29 pm (PDT)
I've been one of the minority who have not been able to use the add-in with
Excel 2010. Too bad, it seems like such a VERY useful tool.
Nolan
- 3b.
-
Re: Excel 2010
Posted by: "mkorgie" mkorgie@southwind.net mkorgie
Wed Jun 15, 2011 2:35 pm (PDT)
GREAT tool! I'm using it successfully on two different (office and home) versions of Excel 2007 and 2010.
Sounds like it may be an installtion or configuration issue.
Mike
--- In smf_addin@yahoogroups.com , Nolan Madson <nolanmadson@...> wrote:
>
> I've been one of the minority who have not been able to use the add-in with
> Excel 2010. Too bad, it seems like such a VERY useful tool.
>
> Nolan
>
- 4a.
-
Google option quotes?
Posted by: "toronto667788" toronto667788@yahoo.ca toronto667788
Wed Jun 15, 2011 7:24 pm (PDT)
Hi All,
Is there any template to get the google real time option quotes?
please help, I did try to find from the "File" folder, but did
I missed something?
I am using "smfgetyahoooptionquote" function fine, but the issue is it's not refresh at all. to get refresh data, I have to exit Excel, and then open the template again, it seem not too good, any help about how to refresh the data?
Thanks in advance for your help. This is a very good Excel add-ins Group.
Cheers,
Donna
- 4b.
-
Re: Google option quotes?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Wed Jun 15, 2011 10:18 pm (PDT)
I wasn't aware the Google option quotes were real-time. However, the beta
version of the add-in, which can be found in the "Works in Progress" folder
of the files area of the group, does have a new function that allows getting
the Google option quotes, and allows smfGetOptionQuotes() to use Google as
the data source for quote options.
For refreshing data, check out the "Links" area of the group for information
on the smfForceRecalculation macro.
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?
> please help, I did try to find from the "File" folder, but did
> I missed something?
>
> I am using "smfgetyahoooptionquote" function fine, but the issue is it's
> not refresh at all. to get refresh data, I have to exit Excel, and then
> open the template again, it seem not too good, any help about how to refresh
> the data?
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar