Users' Knowledge Base

A list of unqualified data resources for tracking stock changes (de-listings, name changes, new issues, acquisitions, events, adjustments etc), with a bias toward the American market.

This is an unofficial resource guide. For a complete list of AmiBroker supported data providers refer to: http://www.amibroker.com/guide/h_quotes.html

The list is dynamic and subject to change without notice. It is ordered according to when the author researched the site and it is not sorted by rank or order of merit.

NOTE : MOST OF THE LINKS CONTAIN COMMERCIAL REFERENCES OR LINK TO COMMERCIAL SITES.

THE AUTHOR HAS NO COMMERCIAL AFFILIATIONS OF ANY KIND AND RECEIVES NO GRATUITIES, OR BENEFITS, FROM ANY COMMERCIAL ORGANIZATION OR INDIVIDUAL INVOLVED IN COMMERCIAL ACTIVITIES. NOR IS HE PERSONALLY ENGAGED IN ANY COMMERCIAL ACTIVITIES, RELATED TO TRADING, IN ANY WAY. THE LINKS ARE PROVIDED AS A RESOURCE AND FOR EDUCATIONAL PURPOSES. THEY ARE NOT A RECOMMENDATION, ON THE PART OF THE AUTHOR, NOR DO THEY CONSTITUTE INVESTMENT ADVICE AND SHOULD NOT BE CONSTRUED AS SUCH.

8) Nasdaq

A list of historical component changes to the Nasdaq 100 is available from the Nasdaq home site.

http://www.nasdaq.com/indexshares/historical_data.stm

7) Stockcharts.com

Stockcharts.com maintains a searchable list of "all recent IPOs, stock splits, distributions, and dividends".

http://stockcharts.com/charts/adjusthist.html

6) JustData (Australia, USA, London, International ?)

a) Historical Databases

Metastock format databases, that include delisted stocks, going back over 10 years for the Australian Securities Exchange (ASX), New York Stock Exchange (NYSE), London Stock Exchange (LSE) etc.

Note: the quality varies.

BodhiGold downloader allows user selected adjustments to equity data (limited to the ASX only?):

http://www.justdata.com.au/Training/Tutorials/Features/history_adjustments.htm

It also includes the option to display expired symbols, name changes, events, fundamental information, and new issues.

b) Back Office Data

"JustData, in co-operation with Exchange Data International (EDI) have made available numerous Worldwide products……….including data for over 100 exchanges and thousands of securities e.g. Corporate Actions, Adjustment Factors, Dividends, Dividend Re-investment Plans, Closing Prices, Codefile (covers all Traded, Coded and Defunct securities listed on the LSE Domestic & International Markets), Sedol Tracker (designed to track changes to the static securities database maintained by the London Stock Exchange. There are normally around 500 to 1000 changed records issued per day), Public Holidays, Depositary Receipts, Shares Outstanding, Security Reference File (provides up-to-date information on almost 450,000 securities, including all securities currently traded on the London Stock Exchange’s domestic and international markets)".

http://www.justdata.com.au/Corporate/Funds/fund_products.htm#actions

An example of an adjustment factor file, for the NYSE, is available here:

http://www.justdata.com.au/Corporate/Funds/images/events_nys.txt

5) Commodity Services Inc (USA)

http://www.csidata.com/ua/pricing/index.html

Historical equity databases for US, and non-US, equities and indices (mainly the London and Toronto exchanges).

Note: De-listed stocks are only available with annual subscriptions and at an additional fee.

4) OTC Bulletin Board (USA)

A list of new issues, symbol and name changes, and deleted issues for OTCBB securities is available from this page.

http://www.otcbb.com/dailylist/

3) Zacks Professional Services (USA)

Historical equity databases for survivor and non-survivor companies, including price and fundamental data.

Analyst estimate databases that ‘avoid’ look-ahead bias.

http://nt3.zacks.com/default.htm

2) deListed (Australia)

http://www.delisted.com.au/aboutus.aspx

This website is published by deListed, a division of BRG Pacific Pty Limited (ABN 64 003 142 372), holder of Australian Financial Services Licence No: 264673.

deListed provides updates and information on failed companies including those in external administration and companies suspended from ASX, NZX, NSX and BSX. It also has all historical name changes and delistings for these exchanges and carries administrators/liquidators declarations for Australian companies for tax purposes.

Background

deListed and this website delisted.com.au were established by BRG director, Tony McLean, in response to problems experienced by shareholders when their investments turned sour. Tony was the Australian Shareholders’ Association’s CEO for nine years until the end of 2001. In that role he often assisted angry and frustrated shareholders in delisted companies. Having seen their investment fail, these shareholders were then left in the dark about the fate of their companies. Company directors denied responsibility and could not be found. Shareholders did not know who was responsible. They were unable to establish if there was any chance of a recovery. It was difficult to track down external administrators. Shareholders often waited years for a liquidator’s declaration. (The issue of this declaration is a Capital Gains Tax Event enabling the crystallisation of a capital loss for tax purposes.) Many shareholders never received this declaration or knew that it had been issued. The eventual launching of this website on 1 September 2002 had its genesis in a commitment to a group of ASA members in 1998, following a spate of requests for information.

1) Norgate Investor Services (Australia)

For subscribers to Australian Securities Exchange (ASX) EOD data, "de-listed securities are moved from the main database and maintained in a ‘De-listed Securities’ folder……..Separate de-listed stocks to enable you to develop trading strategies across the entire universe of stocks including those that have been delisted (includes all delisted stocks back to 1992)".

http://www.premiumdata.net/

Filed by brian_z at 7:23 am under Database Management Resources
Comments Off on Tracking Stock Changes (de-listings, name changes, events, adjustments etc).

As far as the major indexes go, the Nasdaq Indexes are the odd ones out for the American market in that they are owned and managed by the exchange and they are also mainly comprised of in-house issues (Nasdaq listed issues).

Ticker lists are available at the Nasdaq home site for the majority of their indexes: http://www.nasdaq.com/

To download Nasdaq ticker lists for use with AmiBroker:

1) Go to Home >> Market Activity >> Nasdaq Indexes to open the Nasdaq Indexes page: http://dynamic.nasdaq.com/services/indexes/default.aspx

2) Click on a title in the Nasdaq Indexes list to obtain information on the index, including component lists.

Note: The titles in the list are links although they don’t have the visual cues usually associated with links.

An information page for the referenced index will open.

3) Click on Download List to Spreadsheet.

A File Download window will open.

4) Save the file to a local drive.

That’s all there is to it.

Refer to UKB >> Setup A Custom Database – Nasdaq for an example that uses downloaded Nasdaq Indices lists to create a custom database.

ATTACHED FILE:

Nasdaq Composite Index Component List: index_component.xls

Note: The file was downloaded in CSV format but changed to Excel as the Users’ Knowledge Base site security filter doesn’t allow CSV files to be uploaded.

_{Written using Windows XPHome, Internet Explorer v7 and Excel 2002.}

Filed by brian_z at 8:54 pm under Database Management Resources
Comments Off on Indexes – Nasdaq

A stock screener that accesses an EDGAR Online database is available from the Nasdaq Exchange site.

http://www.nasdaq.com/

From the Homepage follow the Investor Tools >> Stock Screener link in the left hand sidebar to open the screener.

A list of constituents for several markets, including the  Nasdaq National and Capital Markets, can be obtained by searching.

To create a National or Capital Market list:

1) Open the Nasdaq screener.

2) Select the required market from the Stock Market drop-down list, enter >=0 into the Close Price input box, as the search criteria, and run the screen by clicking on the Run Query button at the bottom of the page (leave all the other input boxes blank).

An HTML list of stocks will be returned by the search.

3) Select all stocks in the HTML list, right click inside the selected area and pick Copy form the context menu that opens.

Note: To select all of the HTML list position the cursor on the last letter in the right hand bottom corner (this starts the selection with text rather than a hyperlink), left click the mouse button and drag the cursor up and across to the left while holding the left mouse button down.

4) Enter the copied list into a blank Excel spreadsheet using Paste Special as Text.

5) Manipulate the data into the required format using Excels built-in functions.

The list can then be converted from Excel to an AmiBroker compliant format as required.

To convert an Excel list into an AmiBroker compliant format:

1) Click on the title at the head of a column e.g. "A" (or columns) to select the contents.

2) Right click inside the selected area and pick Copy from the context menu that opens.

3) Paste the copied list into a text file, using a program like Notepad, and save it for later use.

OR

1) Delete any additional Worksheets (ASCII import will only recognize one Worksheet in a Workbook).

Note: Where extra Worksheets contain valuable information make spare copies of the file first.

2) Delete any extraneous material in the lists e.g. comments, redundant columns or additional headers or footers.

Note: As a precaution copy any ‘wanted’ columns that have underlying formulas and Paste Special as Values first to ‘preserve’ the data i.e. change it to a permanent, independent format.

3) Save As a Comma Separated Values (CSV) file for later use (also known as Comma Delimited).

Note: When an Excel file, that contains one Worksheet, is saved as a CSV file the Worksheet will automatically be renamed to match the filename.

WARNING: ASCII IMPORT WILL STRIP ADDITIONAL WORKSHEETS FROM A WORKBOOK IF THEY ARE NOT MANUALLY REMOVED FIRST.

The attached file contains raw data and example formulas for changing the format of Nasdaq screened downloads (a basic knowledge of Excel and Excel’s functions is assumed).

Right click on the attached file and Save Target As to download it to a local drive.

ATTACHED FILE:

Nasdaq screened list    nasdaqmarketssept2007.xls

_{Written using Windows XPHome, Internet Explorer v7 and Excel 2002.}

Filed by brian_z at 1:20 am under Database Management Resources
Comments Off on Screener – Nasdaq Stock

http://www.djindexes.com/

Home of the most famous family of indexes in the world.

Like all of the ‘index sites’ there is a lot of information available there but the objects of interest, for this post, are index component lists.

To download DJ component lists:

1) Go to the HomePage >> Indexes >> Dow Jones Averages to open the Dow Jones Averages page or follow the  link:

http://www.djindexes.com/mdsidx/?event=showAverages

2) Click on the Components link in the MORE INFO column.

A Download File window will open.

3) Save the file to a local drive.

Since 2004 on, Dow Jones has also managed the Wiltshire indexes, a group of indexes based on the Dow Jones Wiltshire 5000 which comprises "all U.S. equity securities with readily available prices".

The Wiltshire indexes can be linked to  from the Dow Jones homepage and the partner page for the indexes is: http://www.wilshire.com/Indexes/ 

The component lists for the indexes are only available by subscription.

Filed by brian_z at 7:41 am under Database Management Resources
Comments Off on Indexes – Dow Jones

Standard & Poor’s (S&P) manage a range of U.S. and global indexes including ‘headline’ equity indices for the U.S., Canadian and Australian markets.

The http://www2.standardandpoors.com site is a portal to international S&P sites. Index lists and information on indices and investment related matters is available from the  various sites.

This article solely references the U.S. site to download a current S&P500 constituent list.

To download an S&P500 constituents list:

1) Go to U.S. Homepage  >> Indices >> Equity Indices >> United States and select S&P500 from the Major Indices HTML list. (This opens an S&P500 information panel with Overview as the default) or take the shortcut:

http://www2.standardandpoors.com/portal/site/sp/en/us/page.topic/indices_500/2,3,2,2,0,0,0,0,0,1,3,0,0,0,0,0.html

Note: Additional information on the S&P500, including Index Changes, can be obtained by clicking on the alternative tabs.

2) Click on Constituents List in the Overview sub-panel.

3) Click on the Download Table link in the header of the list that opens.

A Download File window will open.

4) Save the file to a local drive.

An example AmiBroker application that uses an S&P download will be included in future posts.

ATTACHED FILE:

S&P500 Constituent List: sandp500sept2007.xls 

Note: The file was uploaded as an Excel file as the CSV file-type used, in this case, did not pass the site upload security filter.

_{Written using Windows XPHome, Internet Explorer v7 and Excel 2002.}

Filed by brian_z at 1:38 am under Database Management Resources
Comments Off on Indexes – Standard and Poor’s

The Russell Investment Group produces a range of Global and U.S. equity indexes.

At their http://www.russell.com/Indexes/ site they have the following to say about their American indexes:

"Russell produces a family of U.S. equity indexes. The indexes are market cap-weighted and include only common stocks incorporated in the United States and its territories. All U.S. indexes are subsets of the Russell 3000E Index, which represents approximately 99% of the U.S. equity market…..Excluding stocks trading below $1.00, pink sheet and bulletin board stocks, closed-end mutual funds, limited partnerships, royalty trusts, etc, non-U.S. incorporated stocks, foreign stocks, American Depositary Receipts (ADRs)".

For additional information on the Russell Indexes refer to: http://en.wikipedia.org/wiki/Russell_Indexes

Detailed information, including methodologies and membership lists, is available from the above link or the Russell home site: http://www.russell.com/us/SiteNav.asp

To obtain membership lists for the US market go to the HomePage >> Indexes >> Index Membership >> Membership Lists or follow the link: http://www.russell.com/Indexes/membership/default.asp

The lists are downloadable in PDF format.

To manually extract AmiBroker compliant component lists from the PDF files:

• 1) Open the PDF and change the View >> Page Layout to Continuous from the Adobe Reader menu bar.
• 2) Pick Select All and then Copy, (also from the Adobe Reader menu bar).
• 3) Paste the copied selection into a blank Excel worksheet and manipulate the data using Excels built-in functions.

An example list can be downloaded by right clicking on the link below and clicking on Save Target As to save the file to a local drive.

Note: The attached file assumes a basic knowledge of Xcel and Xcel functions.

ATTACHED FILE:

Russell (US) 3000 Index:  russell3000.xls

_{Written using Adobe Reader v7.0 and Xcel 2002}

Filed by brian_z at 3:49 am under Database Management Resources
Comments Off on Indexes – Russell

Hemscott Inc is the owner of the stock classification system used by Yahoo at their Y!Finance (USA) site as well as by other data providers.

As AmiBroker user Dick Hoierman noted in a recent AmiBroker group discussion;  "Hemscott has more or less become the standard as TC2007 and others have switched to it." ¹  which makes the topic one of interest for many AmiBroker owners.

Interested readers can find  Hemscott Industry Group listings at the Hemscott site: http://www.hemscottdata.com/ 

To access the list go to the Homepage >> Financial Databases >> North American Industry Groups >> Hemscott Industry Groups which is the path to an HTML version: http://www.hemscottdata.com/fin_database/na_hemscott_industry_group.html 

Unfortunately a downloadable version is not available at the site but a ‘working’ version can be obtained by manual, albeit tedious, means.

To make a text copy of the HTML list:

• 1) Manually select and copy the relevant ‘text’ from the web page.
• 2) Paste it into a blank Xcel worksheet.
• 3) Use the copy, paste, delete, sort features and text functions to manipulate the data into the desired format.
• 4) Once the lists are suitable for use as an AmiBroker industry, or sector file, select a column, copy the contents and paste it into a text editor e.g. NotePad.
• 5) Save the text file in the C:/Program Files/AmiBroker folder (or the equivalent) as Hemscottbroker.industries or Hemscottbroker.sectors for use as required.

To download a spreadsheet, or a text file, with the current listings right click on the links below and select Save Target As to download them to a local drive.

Note: The spreadsheet is ‘raw’ and assumes a knowledge of basic Xcel and Xcel functions (refer to the Notes worksheet for instructions). The text files are intended as ‘Master’ copies as they contain lists with different ‘formats’. Users need to delete the unwanted sections from the text files.

ATTACHED FILES:

• Hemscott Industry Groups spreadsheet: hemscottclassificationssept2007.xls
• Hemscott broker industries text file: hemscottbrokerindustriessept2007.txt
• Hemscott broker sectors text file: hemscottbrokersectorssept2007.txt

•  _{Version 1 – Sept14/2007}
• _{Version 2 – sept16/2007 – instructions rewritten – updated attached files to include additional workings and AmiBroker compliant numbering}

Written using MicroSoft XPHome, Internet Explorer v7 and Excel 2002.

1 Dick Hoierman – AmiBrokerYahooGroup – message #115044 – US-Stocks database template

Filed by brian_z at 5:27 am under Database Management Resources
Comments Off on Sector Classifications – Hemscott (v2)

American citizens can invest in the shares of hundreds of major overseas-based companies, including names such as British Petroleum, Sony, and Toyota, via American Depository Receipts (ADRs) that are traded on the US stock markets in US dollars.

For an expansive definition of ADRs refer to: WikiPedia >> American Depository Receipts

An article at the www.sec.gov site has the following advice for Americans.

The stocks of most foreign companies that trade in the U.S. markets are traded as American Depositary Receipts (ADRs) issued by U.S. depositary banks. Each ADR represents one or more shares of a foreign stock or a fraction of a share. If you own an ADR you have the right to obtain the foreign stock it represents, but U.S. investors usually find it more convenient to own the ADR. The price of an ADR corresponds to the price of the foreign stock in its home market, adjusted for the ratio of ADRs to foreign company shares.

Owning ADRs has some advantages compared to owning foreign shares directly:

• When you buy and sell ADRs you are trading in the U.S. market. Your trade will clear and settle in U.S. dollars.
• The depositary bank will convert any dividends or other cash payments into U.S. dollars before sending them to you.
• The depositary bank may arrange to vote your shares for you as you instruct.

On the other hand there are some disadvantages:

• It may take a long time for you to receive information from the company because it must pass through an extra pair of hands. You may receive information about shareholder meetings only a few days before the meeting, well past the time when you could vote your shares.
• Depositary banks charge fees for their services and will deduct these fees from the dividends and other distributions on your shares.
• The depositary bank also will incur expenses, such as for converting foreign currency into U.S. dollars, and usually will pass those expenses on to you.

To download the complete PDF link to: SEC guide to foreign investing for U.S. citizens(PDF)

Note: The PDF contains a very basic, albeit somewhat dated, table to highlight the negative correlation that sometimes exists between international equity markets and the US equity market.

A list of current ADRs can be downloaded as a spreadsheet from: http://www.adr.com

On the first visit to the site it is necessary to accept the terms of access (there is no fee or login and personal, or contact details, do not have to be provided).

To download the file:

From the adr.com Homepage go to the DR Universe tab >> Download Universe to open the following link: http://www.adr.com/jpmorgan/gau/adr_gau.xls 

Note: For accessing ADR data from Yahoo – generally ADRs that are listed on the major markets use the ticker as it is listed in the adr_gau file, while those that are traded on the OTC market use an extension e.g. AGRPY.PK

Filed by brian_z at 4:11 am under Database Management Resources
Comments Off on Instruments – American Depository Receipts