Users' Knowledge Base

Objective 

The objective, for this post, is to outline a basic method for setting up a custom database, using ASCII importation to install the categorized structure.

A Global 1200 database, suitable for use with Yahoo data, is used as the example.

Note: it is an outline only and the procedure is not taken to completion i.e. it is not set-up as a working database (interested readers can complete that task for themselves).

 Pre-Requisites

It is assumed that readers are familiar with earlier posts in the UKB >> Database Management series (the basic skills introduced there are not recapped in this post) and also the ASCII Importer Reference (from the Users Guide).

http://www.amibroker.com/guide/d_ascii.html

 Introduction

"American Standard Code for Information Interchange (ASCII) allows digital devices to communicate with each other and to process, store, and communicate character-oriented information" ¹.

It is the format used to import data into AmiBroker, via AmiQuote, or manually, via the ASCII Import function.

In this example, a list of the Global 1200 constituents, including Market and Sector categorization, is downloaded from the Standard and Poors (US) site and pre-conditioned into an ASCII compliant format, before importing it into AmiBroker.

Note: the example is a basic one, but, users can easily extend the method to more complex databases.

Preparation

Step1

A constituent list, in Comma Separated Value (CSV) format, can be downloaded from the Standard and Poors Homesite (US):

http://www2.standardandpoors.com/portal/site/sp/en/us/page.home/home/0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0.html

Go to Indices >>  + Equity Indices >> S and P Global Indices >> S and P Global 1200

OR follow the link below to go directly to the page:

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

Step 2

Click on Download Table, at the top of the page, to save a local copy of the Constituent List (save it in a spreadsheet compliant format)

Note: not all of the Countries, with stocks in the Global 1200, are supported by Yahoo!Finance (refer to the following link for a list of Yahoo’s international exchanges  – http://finance.yahoo.com/exchanges)

Step 3

Prepare the file for ASCII Import, using the spreadsheet functions:

a) Trim excess ‘whitespace’ from the symbols, manually enter Yahoo exchange suffixes and append them to the symbols, sort by Market (Country) and manually add a column for the Market ID (0-23).

b) Delete symbols that are from unsupported exchanges, sort by Sector and manually add a column for the Sector ID’s (0-9).

c) Copy the Fullname, Ticker, Market ID and Sector ID columns to a separate Global1200.CSV file, ready for importing.

Note: Refer to the attached files 24-March-2008_GBL1200.xls and Global1200.xls (the Global1200 file is attached in .xls format as the UKB does not permit uploading of .csv files).

Step 4

Prepare a broker.industries and broker.sector file, using the template in the 24-March-2008_GBL1200.xls file, and save them in the root of the Programs/AmiBroker directory (overwrite the existing broker.industries and broker.sector files).

Step 5

Prepare an ASCII formats file and save it in the Programs/AmiBroker/Formats folder.

The file, for this example, needs to be in the following format (the file can be written in a plain text editor, like NotePad, and saved as Global1200.format):

Note: The file name will be automatically added to the import.types list, which is also in the Programs/AmiBroker/Formats folder.

Step 6

Create a new EOD database, called Data_Global1200

Go to File >> Import ASCII and elect to Open the Global1200.csv file as file type Global1200(*.*)

Note: the import.types (formats file) list will be the default Files of type list in the file browser window that opens.

The symbol list and database structure, as defined in the Global1200.csv file, will now be set-up in the current database.

Note: the Markets and Groups categories will remain as the default settings (they can only be renamed manually via Symbol >> Categories).

Step 7

Data can now be downloaded, for the database, from the Yahoo historical server.

On downloading some symbols will return a 404 error.

In some cases the errors can be repaired by ‘researching’ via the Yahoo sites e.g. Hong Kong stocks use four digits and require leading zeros, which are not included in the symbols used by Standard and Poors.

In other cases there are multiple exchanges, supported by Yahoo, for a given country, so the ticker may need an alternative suffix (for this example the author assumed all tickers included in the Global 1200 were listed on the constituent countries major exchange).

ATTACHED FILES:

24-mar-2008_gbl1200.xls
global1200.xls

_{Browse the spreadsheet files on line, or, right click and select Save As to download them as an editable version.}

REFERENCES:

1 – http://en.wikipedia.org/wiki/ASCII

Filed by brian_z at 9:44 am under Database Management
Comments Off on ASCII Import – Standard and Poor’s Global 1200

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).

Filed by brian_z at 11:05 am under Database Management
Comments Off on Setup A Custom Database – Nasdaq

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

An unsorted, unqualified list of FAQ’s relating to anything and everything to do with database management.

The list is dynamic and subject to change without notice.

Q6 (a). What happens when my original (MS) data source adds, or deletes, a stock code or merges two codes together?
Do I have to take care of this myself?

A6.  Use the METASTOCK PLUGIN  http://www.amibroker.com/guide/h_extsources.html  to read the MS database directly (so you don’t need to re-import all the time).

Re: understanding categories.

Categories are NOT folders (like in Windows Explorer). The difference is fundamental. In the Explorer a file appears (usually) only once in the given
tree leaf. In the Workspace symbol tree a given symbol appears multiple times because it appears in every category leaf that it belongs to, even if this is the same symbol and it exists only as a single entity. One symbol belongs to MANY categories at the same time. For example AAPL (Apple Inc.) will belong to"Stocks" group category, "Nasdaq" market category, "Information" sector category, "Comp-Computer Mfg" industry category, and may also belong to several watch lists and favorites category ALL at the same time.That’s why ONE symbol will appear in many leaves of the symbol tree. Now if you delete the SYMBOL it will of course disappear from ALL categories because you have deleted the SYMBOL.

There are two types of categories:

1. with mutually exclusive membership: groups,markets, sectors/industries – it means that symbol MUST belong to ONE group, ONE market, and ONEsector/industry at a time – you can move the symbol from one group/market/sector/industry to another but you can not remove this assignment – create "Unassigned" group/market/sector/industry instead.
2. with free membership: watch lists/favorites/indexes – it means that a symbol may belong to ANY number (including zero) of watchlists (and to favorite/indexcategory too)In this case you can remove this assignment by Watch List -> RemoveWatch lists are covered in detail in the User’s Guide:
http://www.amibroker.com/guide/h_watchlist.html     (the same is available when you press F1 from AmiBroker)

Another thing: if you use "DELETE" button in Symbol->Categoriesit displays the warning:"This will delete selected symbols FROM THE DATABASE".Again: it will delete FROM THE DATABASE, not from the category.

Tomasz Janeczko – amibroker.com

Authors comments: As a rule of thumb, where data is accessed directly from an external source, WITHOUT a local (AmiBroker) database enabled, changes made to the database by the provider will be reflected at the AB user interface.

Where data is accessed from an external source, WITH a local (AmiBroker) database enabled, database changes will be at the discretion of the user. Depending on the access method, new symbols will be automatically added to the database e.g. when using the MS plug-in, and redundant symbols, or symbols that change, will remain in the database (with their data). The user has the option to merge changed symbols or delete old symbols (the symbol lists are stored in the local AmiBroker database, independently of the plug-in).

Where a local AmiBroker database is used as a standalone (data is updated via AmiQuote) the user has complete control over which symbols to add and delete etc (once again redundant symbols, and their data, will remain in the database unless manually deleted).

Q6 (b). How can I merge two symbols together after a name change?

A6 (b). Pick the symbol, that you want to retain in the database (master symbol), from the All symbols list and select Symbols >> Merge from the AB file menu. Click on the symbol, that you want to assimilate into the master, from the drop-down list in the Merge quotes window that opens, select from the options available (overwrite duplicate quotes, delete the slave symbol after assimilation or assign an alias name) and then click on OK.

Q5. Does anybody know how to check the number of stock symbols in a database?

A5. Hover the mouse pointer over the Data information section of the AmiBroker Status Bar (in the bottom right of the AB screen). A tool-tip will report on the database file name and path, the number of symbols in the database and the number of quotes in the current symbol.

Q4. Currently, my EOD data is supplied in Metastock format from an external supplier (JustData)…how can I setup to use it in AmiBroker?

It is stored in the following folders/directories on my pc:
C: – MyDatabases – Metastock –
ASX – 0
ASX – 1
ASX – A
ASX – B
ASX – C, etc, etc
LSE – 0
LSE – 1
LSE – A
LSE – B, etc, etc
World Indices

At the moment I trade multiple exchanges (ASX, LSE) as shown above.
To use this data within Amibroker, I am aware that I can use the Metastock Plug-in to recreate my databases in Amibroker, as per this link http://www.amibroker.com/guide/h_extsources.html

However, when it comes to creating the equivalent databases in Amibroker, how would others recommend I create them?

For example, would I need a different Amibroker Database for:
1. each current folder (as listed above),
2. 1 for all the ASX data, 1 for all the LSE data, etc
3. 1 database which captures ALL my data (LSE, ASX & Indices)?

If option 3 is suitable, then how would I distinguish in Amibroker which exchange a security belongs to (LSE or ASX)?

A4. Authors comments – AmiBroker doesn’t provide for easy ways to reference symbols that are in one database from within another, so with that restriction in mind it depends on how you want to use the data. If you are going to cross reference World indices to local or international markets, then you would be better off merging them into one database. This takes some work, so if you are not going to do inter-market analysis it is a matter of personal choice whether the benefits are worth the effort.

From an organizational point of view, international exchanges can be ‘managed’ at the Market, Group or Watch list level (refer to Users’ Knowledge Base >> Setup A Custom Database for assistance with organizing Categories within AmiBroker).

Adapting some of the basic procedures outlined in that tutorial you can either:

1) Create a new local AmiBroker database and use the Metastock Wizard to import the symbols into Watch lists, folder by folder, and then assign them to Categories from there (with the help of categoryAddSymbol).

2) Create a new database and access the symbols using the Metastock plug-in (this requires nominating the folders that will be referenced by selecting them in the File >> Database settings >> Configure window). From there you will need to import Watch lists, that correspond to the Metastock folders, and then assign them to Categories (using categoryAddSymbol once again).

Method 1 will create a static database, for historical back-testing only, and method 2 will create a database that dynamically updates to the Metastock database.

Both database types rely on organizing the symbols into Categories, after the initial setup, if you want to replicate Metastock folders.

Q3. I have a big database with intraday 1-sec quotes. How can I change the quotes interval in the database to shrink the database size ?

A3. You can use Exploration to generate any desired interval (set Periodicity in AA) and export to ASCII then import back to a new database.
http://www.amibroker.com/kb/2006/03/04/how-to-export-quotations-from-amibroker-to-csv-file/

Tomasz Janeczko – amibroker.com

AmiBrokerYahooGroup message #116071 "How can I convert AmiBroker tick database to AmiBroker 1-minute dat" http://finance.groups.yahoo.com/group/amibroker/message/116071

Q2. Is it possible to enter negative values via the Quote Editor?

A2. There is special flag $ALLOWNEG 1 that you can use to allow negative data during ASCII import.

Tomasz Janeczko – amibroker.com

AmiBrokerYahooGroup  #116322 "How to enter negative values in quote editor?"  http://finance.groups.yahoo.com/group/amibroker/message/116322

Q1. Is it possible to import tick data with the Wizard in AB?

A1. Tick mode _is_ supported by the wizard.
You just need to add $TICKMODE 1 in the "Additional Commands" field in the wizard and use "Close" for Last tick price and "Volume" for last tick size.

Tomasz Janeczko – amibroker.com

AmiBrokerYahooGroup message #116178  "Import TickData"  http://finance.groups.yahoo.com/group/amibroker/message/116178

Filed by brian_z at 12:27 am under Database Management
Comments Off on Database Management FAQ’s

Filed by brian_z at 4:15 am under Database Management
Comments Off on Setup A Custom Database (v3)

Small databases, with a limited number of symbols and limited data, can be useful for solving complex problems or designing systems within AmiBroker. A small amount of data can be easier to use, for debugging code or proving strategies, than larger amounts. For AmiBroker users, Yahoo data is readily available for this purpose.

In this tutorial new users are shown a simple method to create small quality controlled databases that are suitable for testing basic AFL functions and procedures.

FORMULAS FEATURED: BarCount, BarIndex();

INCLUDING: creating, copying, naming, deleting and backing up databases; managing databases from AmiBrokers? File commands or Windows Explorer; database naming conventions, creating small databases using AmiQuote; manipulating data with Quote Editor; understanding errors reported by the Database Purify Tool, plotting AFL functions as a training aid and more …………….

Attached Files:

• afltestingdatabase.doc (Microsoft Word 2002 document)
• p_barcount.afl
• p_barindex.afl

Filed by brian_z at 3:02 am under Database Management
Comments Off on AFL Testing Databases

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