Jennifer Yoon's blog

Stock Index & List data stored on Amazon S3

Stock Index and Lists

NYSE Composite file, July 2007 download (1963 companies), source is NYSE website

http://s3.amazonaws.com/SWVox/NYSEComposite.xls

NASDAQ Composite file, July 2007 download (3113 companies), source is NASD website

http://s3.amazonaws.com/SWVox/NasdaqComposit.csv

AMEX Equities file, July 2007 download (866 companies, may include foreign companies), source is AMEX website.

http://s3.amazonaws.com/SWVox/AmexEquityList_JUL2007.xls

So, the current total number of companies from the three main stock exchanges is 5,942, (some of the AMEX companies may be foreign).  This list does not include the 3,465 U.S. companies listed only on OTC Bulletin Board as of June 2007 (Also called pink sheet stocks. Notice the over 6,000 companies on OTC BB in 1999? Wow!).

http://s3.amazonaws.com/SWVox/OTCBB_number.htm

Yahoo Filtered result for U.S. equities with $1 billion or less in market cap (2000 companies), source is Yahoo Finance -- Stock Screen tool.  Also link for $250 or less (bottom 1000 companies).

http://s3.amazonaws.com/SWVox/Nasdaq_1Bn_or_less.xls

http://s3.amazonaws.com/SWVox/Nasdaq_250M_or_less.csv

Yahoo Filtered results for small-cap growth and value.  Earlier this year, there was a published research that indicate that (in aggregate) small-cap value was a better investment over the long-term (past 2-3 decades) than small-cap growth.  There were more flameouts in small-cap growth.

http://s3.amazonaws.com/SWVox/SmallCapValue.csv

http://s3.amazonaws.com/SWVox/SmallCapGrowth.xls

Chinese stocks traded on US exchanges (30 companies), from Yahoo Filter.

http://s3.amazonaws.com/SWVox/China30_traded_on_US.xls

Comparison of major equity indexes by Wilshire Associates, June 2003.  Notice there are a lot more stocks than today.  There were even more during 1999 to 2001.

http://s3.amazonaws.com/SWVox/MajorIndexes.pdf

------------ Uploaded by Jennifer Yoon, 7/6 - 7/10/2007 ---------------

Links: SEC's XBRL filing format push, US GAAP taxonomies

[Draft]

Links:

SEC Speech, Drilling for Disclosure:   http://sec.gov/news/speech/2007/spch062507jww.htm

 

Final taxonomy for US GAAP for commercial and industrial companies (we will use most), 2005 release.  http://xbrl.org/us/fr/gaap/ci/2005-02-28/us-gaap-ci%20Summary%20Page.htm

 

Link to taxonomies list.

http://xbrl.org/frontend.aspx?clk=SLK&val=45

Data sources for SEC EDGAR 10-Q and 10-K (U.S. company quarterly & annual reports), Part 1

This is a review of data sources for U.S. public company financial statements -- annual report 10-K and quarterly report 10-Q -- that is filed with the SEC via its EDGAR system.  This is a work-in-progress review of Edgar data sources.  This review is primarily about obtaining financial statement sections, such as Balance Sheet, Income Statement, and Statement of Cash Flows, in an easy to analyse spreadsheet format.

The original source files NOT converted to a spreadsheet format can be downloaded, free of charge, from the SEC at http://www.sec.gov/edgar/searchedgar/companysearch.html and http://www.sec.gov/edgar.shtml.

The EDGAR system (Electronic Data Gathering, Analysis, and Retrieval system) is owned and operated by the U.S. SEC (Securities and Exchange Commission) in order to help the commission carry out its duties of regulating the U.S. capital markets and for the benefit of the investing public.  In June 1999, the SEC first began accepting live filings of 10-Q and 10-K company financial reports in HTML and/or ASCII text formats.  A few companies also provided unofficial copies in PDF format.  In May 2000, the SEC began accepting company filing using the internet.  In March 2005, the SEC began testing a tagged html format, XBRL, under its interactive data initiative with a voluntary group of 36 very large companies.  These test companies also continue to file their reports using HTML and/or ASCII formats.  Eventually, the SEC will provide all of its 10-K and 10-Q filings in an easy to use format, including a spreadsheet format of the financial statements. (Update: the SEC is strongly promoting the XBRL format and there has been significant movement just since May 2007.  See my separate post for more detail.)

So, the internet version of Edgar is only 8 years old.  Although it is difficult for me to remember the days when internet transactions were not ubiquitous, we need to remember that this is still a young medium.  Another phenomenon is the investing public.  Direct stock investment by the general public is a recent behavior.  Starting in the early 1980's the general public began to hold a significant portion of outstanding common stock. By 1990, newspaper articles reported that an estimated 50% of all outstanding common stocks were owned by individual investors.  However, those holdings were overwhelmingly indirect ownerships via mutual funds and 401K retirement funds.  Investment decisions were made by fund managers with specialized financial and accounting knowledge.  I believe the active direct stock ownership by individual investors followed the introduction of internet brokerage firms, such as E*Trade, Datek, and Ameritrade and began around 1996 (Ameritrade acquired Datek in 2002).  (All three companies began offering their internet brokerage service in 1996.)

(See Part 2)

Part 2 - Company financial statement sources,10-K and 10-Q (10K and 10Q)

(Part 2)  Edgar Data Sources Reviewed:

• 10K Wizard (www.10kwizard.com)

      So far, seems to provide reasonable "terms of use" allowing normal usage under "fair use" laws for personal use, including personal commercial use to do one's job.  Licensing fees seems to be best available so far, starting as low as $290 per year for a single person.  Also allows redistribution on intranet or extranet under commercial licensing terms.  Have contacted the company but have not heard back yet.

 

• PrecisionIR (http://precisionir.com)  Provides physical-mail delivery service, free to Wall Street Journal print readers, do not need to be a WSJ subscriber.

Got its start by delivering a physical copy of annual report and mutual fund prospectuses by mail. (The Annual Reports Service and the Fund Info Service are their trademarks.)  I used this service myself and it was indispensible before internet.  It was the only place to get an annual report FedExed for next day delivery.  However, this company does not seem to have made the transition to the internet age.  The website offering  downloadable XLS format files are full of errors and generally does not work.  Limited coverage of companies is more noticeable when there are other providers.  Site is primarily useful for large companies.  Said to cover 3,500 participants, but there is no breakdown of participants by mutual funds, public companies, U.S., and international participants.  No way to know how many U.S. Edgar 10-Q and 10-K filers are covered.

(Update: the files available from Precision IR has changed during my review period.  The site no longer provides quarterly reports and only provides the most recent annual report.  These are available generally in PDF format, no longer available in spreadsheet format.  About 1/3 of the available filing were directly prepared by the filing company, about 1/3 were prepared by 10KWizard, and about 1/3 were prepared by EdgarOnline.  Note EdgarOnline prepared files have nasty "terms of use" restrictions and copyrights the filings even though the content should be the property of the filing companies and not Edgar-Online.  Files prepared by 10KWizard or by the companies themselves do not have nasty restrictions on its use, as it should be.) 

• SNL Financial (http://www.snlfinancial.com/default.aspx)

Provides complete set of financial statements in downloadable XLS format; however, coverage is limited to several industy sectors: Financial Institutions, Real Estate, Energy, and Media and Communications.  

I was not able to test currently available file formats from its web demo.  All spreadsheet features are classified as premium services and users must call the company to request to try its premium services.  The sign-up process for the demo, that did not include any spreadsheet feature, was quite invasive, requesting a lot of personal information.  However, I had previously used their service during 2000 and 2001 while working at KPMG.  I can attest to the thoroughness and accuracy of the downloaded financial statements in Excel format.  At that time, I was able to install an Excel Plug-In on my personal computer to automate the download of multiple files.  The Plug-In could be customized to download specified fields and table formats.  Fees were prohibitively expensive, costing $1000 per person per month for full access. NOT available for re-distribution on the web.  I am still in contact with this company to obtain further information about its Unlimited Service plan. 

Company also provides market news and commentary.  Based on my quick reading, newsletters had best, indepth coverage in the financial sector.  Energy sector coverage seemed weak and spotty.  Media & Communications sector seemed best for large, old-line companies. 

• Edgar-Online (http://edgaronline.com) (provides financial statement data to Yahoo Finance)

The company appears to provide several different levels of service.  Thus far, I was able to fully test only what is provided on Yahoo Finance.  This set of financial statements is an abridged version of the original filing.  Based on a comparison of accounting line items for two sample companies, only about 40%-50% of line items matched the original filing.  Several of the line items were aggregated into one line item.  For example, the hardware sales revenue and (hardware) service revenue items were combined into a total revenue item (see Audible 2006 10-K, image file to upload). 

Not yet tested Edgar Pro service (http://edgaronline.com/products/edgarpro.aspx), $1,200 per year per user.

Update: Edgar Pro does NOT provide the full financial statement data, is NOT available in excel format. Must purchase I-Metrix Architect or Pro to get the full financial statement data.  The I-Metrix Pro is $4,800 per year per user and is for internal use only, no redistribution on the web.  The I-Metrix Architect is availalbe for re-distribution.  Licensing fees include an upfront annual payment of $36,000 per year plus a per user per month payment that varies for each licensee.  There is no cap on the per user fee, so it could run up to several million dollars per year!  Also attaches very restrictive "terms of use" on all services provided by the company unless specifically exempted.  Restrictions generally prohibit "fair use."

Edgar I-Metrix Architect service (new, http://www.edgar-online.com/investor/news/061803.aspx).  Data available for redistribution by financial internet companies.  Provides full financial statement data in several easy to use formats from their database.  Users can plug into the company's database to conduct own database queries.  Output files available in Excel spreadsheet, XML, and CSV text formats. 

Database of company files is said to be fully XBRL compliant.  However, a 3rd party reviewer found numerous errors of the company's own XBRL financial reports submitted to the SEC (source file to be uploaded).  So the XBRL documents of other companies (as converted by Edgar Online) may also be prone to error.

Edgar Data Sources - Not Yet Reviewed: 

 

• @Edgar (www.atedgar.com), LiveEdgar (www.gsionline.com), Annual Report Service (www.annualreportservice.com), AnnualReports.com (www.annualreports.com), SEC Info (www.secinfo.com
• ValueLine, Bloomberg Terminal, E*Trade, Fidelity, MorningStar, Merrill Lynch Direct, StateStreet

--

• Price Waterhouse Coopers, EdgarScan (http://edgarscan.pwcglobal.com/servlets/edgarscan)

To be continued.  Very good source for spreadsheet formats, although missing some quarters and line items.

• Briefingbook.com (available to Wall Street Journal online subscribers)

To be continued.  Provides abridged financial statements.  May be missing some accounting line items from the original filing.  Generally a paid service.  Provides financial statement data to WSJOnline.com subscribers.

Other Related Data Sources:

 

• Capital IQ (http://capitaliq.com/) a Division of Standard & Poors' Company

Provides key statistics and financial ratios.  Provides key statistics to Yahoo Finance.  Flexible file formats available, including comma separated text files.  Internal source uses a database.  Can specify fields desired in output file. Said to cover 12,000 U.S. companies that file a report with the SEC.  Paid service - license/subscription fee varies depending on intended use.  Available for re-distribution on a website.  Sales rep not responsive to my follow-up questions.  Based on my current limited contact, this company does not appear to provide the full financial statement tables.  (Upload image file of sample output on Yahoo Finance.)

• StrikeIron (http://www.strikeiron.com) 

      Located in Silicon Valley, this company is a very good source for financial and economic data.  Also provides security related data such as identity verification through the use of physical address and phone number.  All data can be integrated using an Excel Plug-In to automate output formats and downloads.  Offers very good web integration interface options.  Unfortunately, the current Edgar download service is limited to auto-download of multiple source files based on a table of tickers or company names. Files are not converted into a spreadsheet format.  This is just an FTP download option, also available directly from the SEC.