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
1 Comment

(2 votes, average: 3.00 out of 5)

 Loading ...

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
No Comments

(No Ratings Yet)

 Loading ...

OBJECTIVE

The objective for this tutorial is to setup a Nasdaq Composite database including Group, Sector , Index, and Watch-list categorization.

SPECIFICATIONS

• AmiBroker: Standard v5.0 
• Amiquote: v1.94 registered
• Time Frame: Daily
• Data Source: Yahoo
• Data Type: US Stocks
• OS: Windows XP (Home)
• Software: Microsoft Office XP Professional 2002
• Browser: Internet Explorer v7.0

PRE-REQUISITES

Readers should be familiar with the Users’ Knowledge Base (UKB) posts, Exchanges >> Nasdaq’s Stock Screener and Indexes >> The Nasdaq Indexes, that explain how to download the ticker lists, used in this example, from the Nasdaq site.

INTRODUCTION

This article builds on the ‘template’ established in the UKB post Database Management >> Setup A Custom Database by applying the same ‘top-down’ method, of setting up a database, to the broad based Nasdaq Composite Index (^IXIC). The ‘template’ has been adapted to suit the new Watch list procedures introduced in AmiBroker v 5.0 and also to accommodate the available data set for the Nasdaq Exchange.

Note: An unequivocal method is given, to avoid confusing readers, but in reality there are many possible custom databases and several ways of constructing each of them within AmiBroker. Readers will need to creatively apply the skills and tools demonstrated in this post, and possibly a few more besides, when building custom databases of their own. 

PREPARATION

The component lists, used in this example, are recorded in Table 1 (down-loadable copies are attached near the end of this post).

Table 1 

As stated in the pre-requistes section, they were downloaded from the Nasdaq Exchange site.  Most of them can also be downloaded, in batches, from the Yahoo!Finance site http://finance.yahoo.com/indices?e=nasdaq by selecting the Components link on that page ( a one-off check showed that the lists used by the author and the lists available at Yahoo did not tally but they they were downloaded at different times, within the range of a week or two).

Note: The NasdaqHealthcare sector is not included in the Yahoo listings (the Healthcare Index symbol, ^IXHC, does, however, return prices and a component list when entered in a Get Quotes search). For this example, the Nasdaq lists were preferred, over the Yahoo lists, because of the superior downloading capabilities and (assumed) superior accuracy of the Nasdaq versions.

The list of sub-indexes, that are derived from the Nasdaq Composite, wasn’t available as a download at either site and was manually compiled by the author (refer to Table 2).

Table 2

MEMBERSHIP TESTS

Some planning is required when assigning symbols to Categories as the sub-Categories need to be unique and the order in which the assignments are made can affect the outcome (if the sub-groups are not unique symbols with joint membership will be moved to the most recent assignment leaving the sub-groups that are assigned first short). Mutual lists, that are not exclusive need, to be incorporated into the database at the Watch list level, where joint membership of sub-Categories is permitted. It is, therefore, wise to test membership at all levels before proceeding with the setup.

A test of all component lists , for mutual exclusivity, can be carried out, in Excel. Example files, suitable for this purpose, can be downloaded from the Attached Files section of this post (refer to MembershipTestGroups.xls, MembershipTestSectors.xls and MembershipTestSubIndexes.xls).

Using the membership test file the following results were obtained:

• 155 symbols in the Composite list do not have a match in the National Market and Capital Market (Nat/Cap) lists.
• 50 symbols in the Nat/Cap list do not have a match in the Composite list.
• The Nat/Cap lists are unique, relative to each other.
• There are two symbols in the Composite list that are not included in the Sector lists  i.e. APRO and FTSW.
• 170 Biotech symbols are also members of the Healthcare sector.
• All the other Sector lists are unique, relative to each other.
• The adjusted count, when BioTech symbols are deducted from the Healthcare count, totals 3092 which equals the Composite total of 3094 minus 2 (refer to Table 3). 
• The Nasdaq100 and the NasdaqFinancial100 are unique lists that are part of the NasdaqComposite.
• The Nasdaq100 Technology and the Nasdaq100 Ex-Tech are unique lists that together make up the Nasdaq100.

DATABASE DESIGN       

Based on the membership test results the rational for the database design is:

• The Composite members will makeup the global database, at the All Symbols level, with the remaining Categories subordinate to that list.
• Since the Composite Index is based on one market the Market list is a surrogate for the global database. 
• 14 Indexes, that track different subsets of the Composite, are not included in any lists and will be added to the database at the global level. This will increase the total number of symbols in the database to 3108 == 3094 (Composite) + 14 (Indexes).
• The Nat/Cap markets are major, mutually exclusive, lists that will comprise the next tier of categorization at the Group level.
• 155 symbols, that are not in the Nat/Cap lists, will remain unclassified at the Group level.
• The 50 symbols that are exclusive to the Nat/Cap list will not be added to the database so that the database remains faithful to the Composite at the global level.
• Sectors and Industries will be entered in a one to one relationship, since no information is available for Industry classifications.
• As symbols can not be members of more than one sub-Category, at the Sector/Industry level, the 170 symbols that have joint membership of the BioTech and Healthcare sectors will not be included in Healthcare (this will mean that the Healthcare sector, for this database, will not be a true surrogate of the Healthcare Sector Index).
• The complete Healthcare Sector list and the Nasdaq100 sub-Indexes will be included as Watch lists. 

The operations required to setup the database are:

• - save a copy of the original broker files in the C:/Program Files/AmiBroker (installation) folder,
• - save the database specific broker files in the installation folder,
• - backup the database specific broker files into a spare folder in the installation folder (optional), 
• - create a new database and manually name the Markets and Groups,
• - use the ASCII Wizard to import the Composite symbols (this adds symbols to the database, complete with Information),
• - import the Indexes into a Watch list folder using the ASCII Wizard (this adds symbols to the database, complete with Information),
• - use the ASCII Wizard to assign the Nat/Cap symbols to their Groups (this moves the symbols from the Unclassified sub-category to the Nat/Cap sub-categories within Groups),
• - backup the database specific files (constituent lists) into a spare folder in the installation folder (optional), 
• - import the Sector lists into ‘temporary’ Watch lists, via the Watchlists folder,
• - import current data to the database,
• - perform quality control checks (test for NoQuotes and global database membership),
• - repair or delete NoQuotes, 
• - and then assign symbols to sectors using the X_categoryAddSymbols  formula,
• - quality check the sector assignments,
• - empty the ‘temporary’ Watch lists,
• - assign index symbols to the Indexes folder (move them from the temporary Indexes WL using the X_categoryAddSymbols  formula),
• - manually assign the index symbols their respective Sector/Industry,
• - import the Composite sub-Indexes to ‘permanent’ Watch list folders,
• - and finally import historical data as required.

Refer to Table 3 for a ‘summary’ of the process.

Table 3

Note: The Capital and National Market files do contain Symbol and Fullname information but since they are only being moved, from to one sub-Category to another, the Fullname can be ignored (the symbols already exist in the database and the Fullname information field was populated at the global level).

The Group assignment could have been done in other ways, however, for this example the Import Wizard was used, for this task, to demonstrate it’s assignment capabilities.

Once the user has a database structure ‘mapped out’ the creation process can start (additional effort at the ‘design’ stage will often save time later as unexpected occurrences, during the setup, can force a re-run or two.

CREATE A NEW DATABASE

The first step in the process is to create a new database.

To create the database:

1) Make a backup copy of the original broker.industries and broker.sector files in the C:/Program Files/AmiBroker (installation) folder (or the equivalent).

2) Download the broker.industries and broker.sector files attached to this post and Save them in the installation folder to replace the original versions.

Note: A handy tip is to create a new folder in the AmiBroker directory and save copies of the database specific broker files there, as a backup, before pasting them into the ‘working’ directory (save them in a Files folder or in a files folder with the same name as the database e.g. Data_NasdaqComposite_Files). The database backup files are then only one click away from the AmiBroker directory if they need to be checked, or reinstalled to the root folder following a mishap.

3) Create a new database called Data_NasdaqComposite, using the default EOD settings.

When the database is created the Sectors and Industries lists will automatically be set to the broker file specifications (the Sectors and Industries have a one to one relationship as no Industry information is available, for this example).

The Markets and Groups categories are not established when the database is created and they need to be set manually.

4) Check both categories and Edit the first name in the list as required  (for this example the Markets default needs to be Nasdaq and the Groups default needs to be Unclassified).

Once the Markets and Groups have be named symbols, and symbol information, can be imported into the database at the global level.

To import from a CSV file using the Wizard:

1) Open the Import Wizard using the File > Import Wizard command from the menu bar.

2) Click on Pick files.

3) Select the file to be imported from the file browser window that pops up and click on Open.

Note: NasdaqComposite.csv is a copy of index_components.csv that was downloaded in the post: UKB >> The Nasdaq Indexes

The selected file will be listed in the Import Wizard’s information pane.

4) Click on Next >.

The Define Fields window will open with a ‘preview’ of the file format in the Data file sample pane.

In this example the file has a two header rows, the first data column contains the Name (Fullname) and the second column contains the Symbol (Ticker). The data columns are separated by a comma and there is no quotation data included.

5) Define the fields according to the file format:

•           use the drop-down menus to set Column 1 to Fullname, Column 2 to Ticker and the remaining columns to Skip (ignore) anything else,
•           set the Separator to Comma(,),
•           pick Unclassified from the Group drop-down menu,
•           skip the first two lines (title rows) by entering 2 into the Skip first: input box,
•           check Log errors (optional),
•           check Automatically add new symbols (all of the symbols are new to the database and need to be added),
•           check No quotation data (the list does not contain any quotation data as only Tickers and Fullnames are to be imported).

When the settings are correct click on Next > and an Additional Settings window will open.

6) For this example there is no need to save the import format.

    Click on Finish.

7) Click on Next > to complete the Symbol/Information importation.

All of the symbols are arranged in one market (Nasdaq), Unclassified at the Group level and listed under the default Sector/Industry (NasdaqBank 0) as expected.

Since the Indexes are not included in the composite list they also need to be imported, in ASCII format, to add the symbols and symbol specific information to the global database.

To enter the Index symbols to a Watch list and the database:

1) Use the Import Wizard to import the data from the NasdaqIndexes.CSV file (set the Group sub-category to Unclassified and the Watch list (WL) to List 0 using the drop-down menus) .

2) The index symbols will be imported into Watch list 0 and enter the database under the Nasdaq|Unclassified|NasdaqBank 0 sub-categories.

3) Rename the Watch list to Indexes.

4) Save the database using the File >> Save Database command.

Note: It isn’t essential to save at this stage but it is a good precaution to take before continuing on.

This establishes Data_NasdaqComposite as a ‘global’ database that represents a broad cross-section of Nasdaq listings plus the associated indexes.

From there it can be categorized according to personal preference.

CATEGORIZE THE DATABASE CONSTITUENTS

Setup Group Assignments

From the membership test results the outcome of entering the Group assignments should be to move most of the symbols from the Unclassified group to the National Market & Capital Market groups, leaving 155 symbols and 14 indexes behind as Unclassified. The 50 symbols that are exclusive to the Nat/Cap list will not be added to the database.

To assign symbols to Groups using the ASCII Wizard:

1) Enter a name for the Groups, in the order required, and click on O.K.

Note: The order is permanent i.e. it will not be automatically sorted by alphabetical order in the Workspace Symbol Tree.

2) Open the NationalMarket component file in the ASCII Wizard and pick National Market 1 from the Group drop-down menu, to assign the symbols to the relevant group. Then deselect Automatically add new symbols to prevent symbols that are unique to the NM list from being added to the database.

Select the other settings as determined by the file format.

Note: The lists are from a different source to the other lists, used in this example, and the columns are in a different order such that the Symbol column precedes the Name column (always look at the preview pane carefully and don’t take the formats for granted).

Since the Import Wizard is being used for assignment, rather than information input, there is no need to use any data field other than the Ticker (the Fullname data is ‘skipped’).

3) Click on Next >

An error report window will pop-up to notify the user that some symbols in the list were not imported.

4) Click on Yes to read the report.

Note: Readers who are working their way through the post might find minor differences, between their outcomes and the images used, as the author wrote the post over a period of weeks, using two computers and different file versions.

The report is in plain text format and it can be saved, or printed, for future reference.

5) Repeat the Group assignment procedure, using the Capital Market file, to finalize the Groups re-organization.

If a mistake is made in the number of lines skipped, when assigning symbols to sub-categories with the Wizard, the error can be corrected manually.

To manually reassign an Unclassified symbol e.g. EGHT to another sub-category:

1) Go to Symbol >> Organize assignments on the menu bar (the Assignments organizer window will open).

2) Select the Groups radio button then use the drop-down menu to pick Unclassified from the left hand list and Capital Market 2 from the right hand.

3) Scroll down the Unclassified list to find EGHT and select it before clicking on the Selected right arrow button to move the symbol to the nominated group.

When the Workspace Symbol Tree is refreshed it will reflect the new assignment for EGHT.

SETUP SECTOR AND INDUSTRY ASSIGNMENTS

Setting up the Sectors and Industries is a little more complicated and requires the use of an AFL formula.

The formula does the work of assigning symbols to the sectors but before it can be used some preparation is required:

• downloaded Industry lists, in CSV or Excel format, need to be converted into a Watch-list compliant (.tls) format and saved into the Database Watchlist Folder (in the installation directory),
• price data needs to be imported and redundant symbols deleted (AFL formulas will not work with symbols that do not contain quotes).  

Once those criteria have been meet the database Watchlist folder lists can be simultaneously imported into the Symbol Tree Watch lists (from AmiBroker v5.00 upwards).

Note: it wasn’t the intention of the developer that users would take this path so it does involve a minor ‘workaround’ (use at your own risk – the author has experienced a minor glitch or two using this procedure).

To import Symbols into the Symbol Tree Watch lists:

1) Open the relevant file that contains the Sector specific symbol lists e.g. NasdaqBank 0.xls

2) Copy the symbols from the spreadsheet column and Paste them into a text editor, like NotePad, (this will produce a plain text file with a single symbol per line).

2) Edit out the title rows, if any, and Save the file as a .tls file in a backup folder in the AmiBroker directory (they can be stored in the same folder as the backup broker files e.g. Data_NasdaqComposite_Files).

3) Close AmiBroker.

4)  Copy and Paste the Sector list files into the Database Watchlists Folder e.g. C:\Program Files\AmiBroker\Data_NasdaqComposite\WatchLists (it isn’t necessary to save them in any particular order).

3) Open AmiBroker again and the ticker lists will have been added to the Watchlist Master File (index.txt) which is also located in the Database WatchLists Folder.

Note: They will be added from List 64 on (inclusive). The first 64 lists (0-63 inclusive) have been reserved to maintain backward compatibility with older versions.

They will also take up their rightful place in the Workspace panel.

If the mouse pointer is hovered over a Watch list folder a tool-tip, with a report on the WL number and the symbol tally, will appear (the number of symbols per WL can be cross referenced to the records made at the design stage – refer to Table 3).

Downloading data and quality checking the database

Assigning symbols to Industries requires use of an AFL formula. Formulas will only work with symbols that contain at least one data point so it is necessary to import some data to the database. Current data will be used for this purpose since it downloads a lot quicker than historical data.

To download current data:

1) Open AmiQuote while AmiBroker is open with Data_NasdaqCombined as the selected database.

2) Get the tickers from AmiBroker using the Tools menu option and Automatically import data with Yahoo current as the select source (this will add one quote to all of the symbols that traded on the day).

Note: It is recommended to wait until after the daily close to download current data, for this purpose, so that stocks with low volume have a chance to register some trading activity during the day.

Once data has been imported it also provides an opportunity to remove, or repair, any redundant symbols in the database before downloading historical data (historical data will download faster if AmiQuote doesn’t have to report a lot of errors).

A list of the redundant symbols in the database can be made by using AmiBroker’s Data Purify tool.

To identify and quarantine redundant symbols:

1) Open the Database Purify tool (DPT) and enter the first symbol, from the All Symbols list, as the Reference symbol.

Note: Check that the symbol chosen as the reference does contain, at least, one quote.

2) Uncheck all of the report options, except for Report missing quotes.

3) Select All symbols and Last n = 1 as the settings and then click on Analyse.

A list of all stocks that DO NOT have a current quote will be returned.

4) Right click anywhere inside the list and pick Add all symbols to watch list from the context menu that opens.

Note: The DPT does not report a symbol count in the output list. Since AmiBroker doesn’t recognize No Quotes anywhere else the only way to obtain a count, for those who are using AB 4.91 and below, is to Copy the ‘list’ and Paste it into a spreadsheet program (for AB v4.92 and above the WL tool-tip will report the number of No Quotes symbols).

5) Select a vacant watch list to receive the output and click on OK.

Note: The importance of naming all Watch list folders as soon as they are ‘filled’ can readily be seen. In this example, if List 1 already contains data the outcome will be a bit of a mess that the user has to clean up (as noted by Dr Howard Bandy in his book Quantitative Trading Systems). So, in ‘real life’ the smart thing to do would be to name a Watch list folder "NoQuotes", prior to starting the database quality checks, and then send the DPT catch there.

All of the symbols identified as having no data will now be listed in Watch list 1.

Note: The redundant symbols will still remain in other Watch lists and Categories (assigning a symbol to a Watch list does not move it from anywhere else).

Sometimes a symbol list can contain a ‘rogue’ (unwanted) symbol that sneaks into the database by the backdoor (WL importing). Any symbols that were added to the database, via the Watch lists, will not have an associated Fullname (the Watch lists only contain Tickers without any additional information) so a test for symbols without a Fullname will find them).

A quick test, for global membership, can be carried out using the Explorer. For this example the test will be run against All symbols using the X_FullName formula.

To test All symbols in the global database for ‘true’ membership:

1) Run an exploration on All symbols using X_Fullname.

2) Sort the Results by clicking on the Fullname column. If there are any ‘rogue’ symbols present they will be sorted to the top of the list and have a blank in the Fullname cell. In this example there are no symbols without a Fullname which indicates that the database design and importation procedures were tight.

The number of symbols in the global database, that contain data, can be noted from the Status Bar at the bottom of the AA window.

Summing the symbol numbers, for stocks with and without data (NoQuotes), should equal the global database count (the Composite + Indexes tallies). For this database 2895 + 213 = = 3094 + 14 == 3108 (all present and correct!).

To construct a ‘working’ database it is advisable to ‘manually’ check each redundant symbol e.g. against Nasdaq’s lists or Yahoo’s lists. The reason for ‘failure’ to download current data should be found and corrections made, according to the preferences of the database owner (in some cases the symbol doesn’t exist and in others it will exist but have no volume for the day). The solutions, to problems encountered, can be wide ranging e.g. for traders who want liquidity, all ‘no volume for the day stocks’ can be deleted (they will be all that is left over in the ‘NoQuotes’ folder after ‘symbol does not exist’ stocks have been deleted or repaired), while those who want to trade the small caps, or low volume stocks, can ‘run’ a short historical download for the leftover ‘no daily volume’ stocks and decide what action to take on the results they obtain.

While it might not be a realistic option, for a ‘working’ database, all ‘NoQuotes’ symbols will be deleted for this example.

To delete NoQuotes:

1) Open the Symbol >> Organize assignments window.

2) Select the Watch lists radio button.

3) Pick NoQuotes from the left hand drop-down.

4) Hold down the Shift key while selecting the first and last entry in the list (the list will turn blue to indicate that all of the symbols have been selected).

5) Click on the Delete button.

A warning message will ask for confirmation of the delete.

6) Click on OK.

The selected symbols will be removed from all levels of the database.

The tool-tip can be used to sum the new Watch list counts and check the total against the global database total of 2895.

Assigning Watch list constituents to Industries

Once all of the symbols in the database contain data they can be assigned to their Industries using X_categoryAddSymbols v4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

/*X_CategoryAddSymbols v4*/   
//assigns Watch list members to the Industry with exactly the same name as the Watch list    
//Watch lists and Industries can be in any order    
//manually enter the first and (last Watch list number +1) for Watch lists that contain assignment symbols    
//tested OK with AB v5.0  
Filter = 1;  
for(WL = 64; WL < 73; WL++)//manually input the first and (last WL number + 1)  
&#160;&#160; {  
&#160;&#160;&#160; WLName = CategoryGetName(categoryWatchlist,WL);    
&#160;&#160;&#160; Ticker = WriteIf(InWatchList(WL), Name(),"");     
&#160;&#160;&#160; AddTextColumn(Ticker, WLName);     
&#160;&#160;&#160; IN = CategoryFind(WLName,categoryIndustry); //IndustryNumber to match selected WL Name  
&#160;&#160;&#160;&#160;&#160;&#160; if(InWatchList(WL))    
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; {     
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CategoryAddSymbol("",categoryIndustry,IN);     
&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; }  
&#160;&#160; }

To assign symbols to their industries:

1) Hover the mouse pointer over the first and last Watch list folders, that contain assignment lists, to check the WL number.

2) Enter the first and (last Watch list number + 1) to the formula, as the loop variable and the counter upper limit.

3) Run X_categoryAddSymbols v4 in Explorer with All symbols, n last days and N = 1 selected.

The symbols will be assigned to Industries, according to their Watch list membership (the Watch lists constituents will be reported in the AA window).

4) Click on the Result pane Titles to sort the column by Watch list and scroll down to manually cross check WL membership against Sector membership.

The Results can also be exported to a spreadsheet for printing or saving for reference.

Quality checks on the assignment process can also be run using X_Fullname.

To check Sector assignments against Watch lists:

1) Check use filter in the AA window and click on Define to set the filter criteria.

The Filter settings window will open.

2) Click on Clear to remove the previous settings (Includes and Excludes should both be free of redundant settings).

3) Select a Sector from the drop-down list and click on OK.

4) Run an Exploration using n last days and n = 1.

5) Leave the Automatic Analysis window open and right click on the corresponding Watch list folder, to open the context menu, then alphabetically Sort the list.

The Sector count, from the AA window status bar, can be checked against the corresponding Watch list count and the list members can be checked against each other by scrolling the lists.

Once the Sectors have been checked, and all of the symbols assigned correctly, the ‘temporary’ Watch lists can be emptied.

To empty Watch lists:

  1) Right click on the Watch list folder and select Erase (make empty) from the context menu that opens.

2) Click on Yes to continue when a warning message opens.

Alternatively, to empty a larger number of Watch lists:

1) Close AmiBroker.

2)  Delete all the temporary Watch list (.tls) files from the C:/Program Files/AmiBroker/WatchLists folder.

3)  Open AmiBroker (the ‘temporary’ Watch lists will be empty and no longer visible in the Symbol Tree).

Note: Users can elect to view empty WL’s by right clicking on a WL folder and deselecting Hide Empty watchlists from the context menu that opens.

The next step, in setting up the Nasdaq Composite Database, is to move the symbols in the Indexes WL to the Indexes Category.

To assign symbols to the Index folder:

1) Open AA and set the filter to the Indexes Watch list.

2) Run categoryAddSymbol v5.0 as an Exploration.

The index symbols listed in Watch list 0 will be assigned to the Indexes folder.

3) Watch list 0 can now be emptied.

That completes the reorganization of the index symbols into their own category. They will also be listed at the top of the default sub-category in all the other categories (automatic alphabetical sorting brings them to the top). As they are an exclusive group they can be moved to their own sub-category at any level. For this example they will be left in the Unclassified sub-category at the Group level and individually moved to their corresponding Sector/Industry (this could also have been done earlier by including them in the Industry lists).

To move the Index symbols from the default Industry sub-category to their own Industry:

1) Go to Symbols >> Organize assignments and select the Industries radio button on the Assignments Organizer window that opens.

2) Pick NasdaqBank 0 (the default) from the left hand drop-down list and then select the index symbol to move.

3) Pick the target industry from the right hand drop-down and then click on the left to right arrow button to move the symbol to it.

4) Repeat the procedure for all of the indexes that have a corresponding industry sub-category.

After all of the Sector indexes have been assigned five index symbols remain in NasdaqBank 0 (the composite index and four N100 indexes). They can be moved by naming an Industry as Unclassified and using the Assignments organizer to move them to it.

When all of the assignments are complete, and the watch list folders have been emptied, the symbols that comprise the permanent watch lists can be imported.

To import ‘permanent’ Watch lists:

1) Rename WL’s to match the lists that are to be imported.

2) Go to Menu bar >> Symbol >> Watch list >> Import and the Select watch list(s) window will open.

3) Select the File radio button and click on OK (a file browser window will open).

4)  Choose the required file from the list and click on OK (the symbols in the file will be imported into the selected WL).

Note: The file needs to be a text file with one ticker per line.

5) Repeat the process for the remaining WL’s including the original (complete) Healthcare Sector list.

Finally, the database can be saved and historical data downloaded by opening Amiquote, getting the ticker list from the open database and downloading the required range of quotes in the usual way.

CONCLUSION

That completes this tutorial that demonstrates one method of creating a database. It includes examples of the full range of graphical tools and basic skills that users require to allow them to customize their own databases, according to their needs and the data at their disposal. Some alternative methods of setting up a database will be dealt with, at a basic level, in future posts (including user defined ASCII importing and, possibly, low level ‘automation’).

ATTACHED FILES:

broker industries and broker sector files: brokersectors.txt and brokerindustries.txt

Nasdaq composite file: nasdaqcomposite.xls

Nasdaq markets file: nasdaqmarketssept2007.xls

Nasdaq National and Capital markets files: nasdaqcapitalsept2007.xls  and  nasdaqnationalsept2007.xls

Excel file used to compare Group membership: membershiptestgroups.xls

Excel file used to compare Sector membership: membershiptestsectors.xls

Excel file used to compare Index membership: membershiptestsubindexes.xls

Nasdaq Sector Indices files:

 nasdaqbank-0.txt

nasdaqbiotech-1.txt

nasdaqcomputer-2.txt

nasdaqhealthcareminusbio-3.txt

nasdaqindustrial-4.txt

nasdaqinsurance-5.txt

 nasdaqotherfinance-6.txt

nasdaqtelecommunications-7.txt

 nasdaqtransportation-8.txt

nasdaqhealthcare.txt

Nasdaq Sub-Indexes files:

nasdaq100.txt

nasdaq100extech.txt

nasdaq100tech.txt

nasdaqfinancial100.txt

Filed by brian_z at 11:05 am under Database Management
2 Comments

(No Ratings Yet)

 Loading ...

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
No Comments

(2 votes, average: 1.00 out of 5)

 Loading ...

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
No Comments

(No Ratings Yet)

 Loading ...

OBJECTIVE

The objective for this tutorial is to setup an example database, including grouping and sector classification. Some basic database management skills are demonstrated and then built on in a later Users’ Knowledge Base (UKB) article: Setup A Custom Database – Nasdaq

SPECIFICATION

• - AmiBroker: Standard v4.9
• - Amiquote: v1.94 registered
• - Time Frame: Daily
• - Data Source: Yahoo
• - Data Type: US Stocks
• - OS: Windows XP (Home)
• - Software: Microsoft Office XP Professional 2002
• - Browser: Internet Explorer v7.0

PRE-REQUISITES

It is assumed that readers are familiar with the preceding articles in the UKB >> Database Management sub-category and also the AmiBroker Help >> Tutorial >> Database Management section. 

Note: To see articles in their order of precedence it is necessary to go the the UKB Homepage and browse the Table Of Contents. In all other places, including RSS feeds, UKB searches and category filings, the posts are sorted by the publication date, which is often the reverse of the order of precedence.

INTRODUCTION

AmiBroker is a flexible program, that, in many areas, allows owners to use it according to their personal preferences. This is nowhere more evident than when sourcing data. Data can be obtained from providers, who manage the classification and categorization of stocks for their subscribers, or, for those who want to ‘do their own thing’, custom databases can be setup using alternative or unique categories.

There is no set nomenclature, or structure, for categorizing database constituents, other than the limits set by the program and by the way in which the markets work:

• - Markets, Groups, Sectors and Industries  are major, market orientated, categories.
• - Watchlists and Favorites are minor, personally orientated, categories.
• - Indexes is a market orientated category grouped with the personals. 
• - All categories can contain sub-categories except for Favorites and Indexes.
• - Sub-categories under the major categories are mutually exclusive i.e symbols can only belong to one sub-category at a time.
• - When symbols are re-classified within a major category they are effectively moved from one sub-category to another.
• - Watch list sub-categories are not mutually exclusive i.e. symbols can belong to more than one sub-category.
• - Sub-categories only go down one level, except for Sectors, that goes down two i.e. Sectors > Industries.
• - Unclassified (Unassigned, Undefined) should be used for symbols that don’t fit neatly into a defined sub-category.  
• - When symbols are assigned to two or more sub-categories the most recent operation will take precedence.
• - Only sub-categories that contain symbols will be visible, as a folder, in the Workspace Symbols List. 

This example imports a list of the Dow Jones Industrial Average (^DJI) members and ‘divides’ it into some arbitrary groupings, to demonstrate the principles of categorization.

Note: The database comprises 32 symbols, some of which are redundant i.e. there is no data available for that symbol (redundant symbols have been included to demonstrate a point). It has been broken into two groups (Group A and Group B) and 6 sectors (DJI 0-5) containing five symbols each.  A redundant symbol has been added to the last two sectors (4&5).

CREATE A NEW DATABASE.

The first step in the process is to create a new database.

Sector and industry classifications are put in place when a database is created, so it is necessary to think about the required classifications before hand.

To create a database without classifications:

• 1) Make a backup copy of the original broker.industries and broker.sectors files in the C:/Program Files/AmiBroker folder (or the equivalent) then delete the original versions.
• 2) Download the resetindustries.txt and resetsectors.txt files attached to this post and save them in the C:/Program Files/AmiBroker folder to replace the original versions (rename them as broker.sectors and broker.industries).

Note: The files can be opened and read in a text editing program like Notepad.

3) Create a new database called Data_CustomExample using the default EOD settings.

When the database is created the Sectors and Industries sub-categories will automatically be set to the broker file specifications i.e. in this example the first sub-category in Sectors and Industries will become Unclassified and the remainder will be set to the defaults i.e. Sector 1 – 63 and Industry 1 – 255 (this removes any redundant settings, for those categories, from the Symbol >> Categories or Symbol >> Organize assignments windows).

Note: Redundant titles can be retained in the Categories or Organize assignments lists from database to database. In practice they do not have any adverse effect on the current database. In this example, the Sectors and Industries sub-categories have been reset to the defaults so that readers, who are following the example, will achieve exactly the same outcome as shown.  Normally the broker files don’t need to include the default names; the end result, in the Workspace Symbols Tree, would have been exactly the same if the broker files had listed Unclassified as the sole sub-category.

The Markets and Groups categories are not established when the database is created and they need to be set manually (if they are not reset the defaults will automatically be used, including any redundant sub-categories that remain from the previous database).

Note: When symbols are first imported into a new database they are entered under the first sub-category in each category by default (the exception is Groups that defaults to Group 255).

4) Go to Symbols >> Categories >> Markets/Groups and Edit the first name in the list as required then click on OK to save the changes.

For this example the Markets default needs to be NYSE and the Groups first sub-category needs to be Unclassified. 

Once the Markets and Groups have be named symbols can be imported into the database.

In this case a list of symbols, in Comma Separated Values (CSV) format, will be entered into the database using the Import Wizard.

Note: The lists used in this example can be downloaded, in Excel format, from the Attached Files section at the end of this post.  To create text files, suitable for importing into watchlists, copy the symbols from the relevant Excel column and paste them into a text editor, like NotePad.

To import from a CSV file using the Wizard:

1) Before importing symbol lists check the source file:

• - If the file is in Excel format Save it As a CSV file before proceeding.
• - It should contain only one Worksheet so Delete any additional Sheets.
• - Copy any columns that are constructed from underlying formulas and Paste special as Values to stabilize the  data.
• - Remove any extraneous material (Delete any notes or comments, other than the title row, and also any redundant columns).
• - Save the modified CSV file locally, using a meaningful name that associates the contents with an Industry sub-category.

Note: The ASCII Import Wizard and watch list importation use different formats. ASCII import allows for importing data to several fields, including the fullname, while watch lists are limited to ticker lists only. As a rule of thumb ASCII import should be used for global importing, and attribution of information values, while watch lists importation should be used to manage categorization i.e. move symbols from one category to another.

WARNING: THE IMPORT PROCESS WILL STRIP ANY ADDITIONAL  WORKSHEETS FROM THE FILE IF THEY ARE NOT MANUALLY REMOVED FIRST.

• 2) Open the Import Wizard using the File > Import Wizard command from the AmiBroker menu bar.
• 3) Click on Pick files.

4) Select the file to be imported from the file browser window that pops up and click on Open.

The selected file will be listed in the Wizard’s information pane.

5) Click on Next >.

The Define Fields window will open with a ‘preview’ of the file format in the Data file sample pane.

Note: In this example the file has a single header row (title), the first data column contains the Symbol (Ticker) and the second column contains the Name (Fullname). The data columns are separated by a comma and there is no quotation data included.

6) Define the fields according to the file format:

• - use the drop-down menus to set Column 1 to Ticker, Column 2 to Fullname and the remaining columns to Skip (ignore) anything else,
• - set the Separator to Comma(,),
• - pick Unclassified from the Group drop-down menu,
• - skip the first line (title row) by entering 1 into the Skip first: input box,
• - check Log errors (optional),
• - check Automatically add new symbols (all of the symbols are new to the database and need to be added),
• – check No quotation data (the list does not contain any quotation data as only Tickers and Fullnames are to be imported).

When the settings are correct click on Next > and the Additional Settings window will open.

• 7) For this example there is no need to save the import format. Click on Finish to complete the symbol/information importation.
• 8) Save the database using the File >> Save Database command from the menu bar.

Note: It isn’t essential to save at this stage but it is a good precaution to take before continuing on.

All of the symbols are arranged in one market (NYSE), and they are Unclassified at the Group/Sector/Industry level, as expected.

This establishes Data_CustomExample as a global DJI database.

From there it can be categorized according to personal preference.

CATEGORIZE THE DATABASE CONSTITUENTS

Setup Group Assignments

Some planning is also required when assigning symbols to groups as the sub-groups need to be unique and the order in which the assignments are made can affect the outcome.

For this example the ^DJI database will be separated into Group A and Group B listings (once again this is for demonstration purposes only and it has no practical application in the ‘real’ trading world).

To assign symbols to Groups:

1) Enter a name for the groups, in the order required, and click on O.K.

Note: The order is permanent i.e. sub-categories will not be alphabetically sorted in the Workspace window.

2) Import the A and B list symbols using the Wizard.

Pick Group A from the Group drop-down menu to assign the symbols to the relevant group.

Note: There is no need to check Automatically add new symbols since the groups are sub-sets of the global database (there are no new symbols since all the symbols in the group lists were already imported at the market level) .

Repeat the import procedure using the Group B file.

The Group assignments have now been installed (all of the symbols have been moved from Unclassified to the relevant group folder and the Unclassified group folder is empty).

Setup Industry and Sector Assignments.

Setting up the sectors and industries is a little more complicated and requires the use of an AFL formula.

The formula does the work of assigning the symbols to sectors but before it can be used some preparation is required:

• - the Sector and Industry sub-categories need to be named,
• - the watch lists need to be named to correspond to the Industry names (this is optional but it is a good practice to adopt to make sure that symbols end up in the right place).
• - downloaded lists, in CSV or Excel format, need to be converted into a watch list compliant format and imported into the database (once again it is recommended that the files are named to match the industry names),
• - price data needs to be imported  (AFL formulas will not work with symbols that do not contain quotes). 

Note: A handy tip, that the author picked up from Richard Dale of Norgate Investor Serices, is to append the sub-category number to the title e.g. when renaming List 0 to indicate that the folder contains DJIA components use the title DJIA 0 or DJIA (0) etc (once again this helps to ensure that symbols end up in the right place after a hard day at the office).

To name sector, industry and watchlist sub-categories:

1) Name the Watch lists so that they correspond to the Industry sub-category names.

Note: Start at the head of the list and do not leave any unnamed sub-categories in between the first and last ‘named’ item.

2) Repeat the exercise for the Sectors sub-categories.

3) As there is no information available on industry classifications, for this database,  name the Industries in exactly the same way (this will establish a one to one relationship between the Sectors and the Industries).

After the Sectors and Industries have been named the Sector that corresponds to the Industry will be listed in the Settings pane (click on any industry sub-category to see the associated sector).

The default Sector/Industry, in the Workspace Symbols Tree, has changed from Unclassified to Djia0.

Note: The smart way to setup the Sectors and Industries would have been to list them in the broker files, prior to initializing the database. The names were entered manually, in this example, for demonstration purposes only (refer to AmiBroker Help >> Contents >> Tutorials >> Database Management >> Understanding Categories >> How to define your own sectors and industries for assistance with setting up 1-1 ‘sector to industry’ broker files).

To prepare a watch list compliant file from a spreadsheet symbol list:

• 1) Select the column, to be copied, by clicking on the Column Title e.g. "A".
• 2) Right click inside the selected area and Copy the symbol list using the context menu that opens.
• 3) Open a text editor, like NotePad, position the cursor in the white space of a new "blank" page, right click and select Paste from the context menu that opens  (this will produce a plain text file with a single symbol per line – formats from the spreadsheet will be stripped from the text version).
• 4) Edit out the title rows, or extraneous text, if any, and save the file locally.

To import symbol lists to the corresponding watch lists:

1) In the AmiBroker Workspace right click on any symbol, in any symbol list, and select Watch list >> import from the context menu that opens (a Select Watch list(s) window will open).

Note: The folders in AmiBroker’s Symbols Tree do not have a right click >> context menu, with the exception of watch list folders that contain symbols. The alternative path to the Watch list menu is  Symbol >> Watch list from the menu bar.

2) Select the Watch list that is going to receive the imports, select the Import/Export from /to: File radio button and click on OK (a file browser window will open).

3) Pick the text file that contains the ticker list to be imported and click on Open.

The watch list folder, containing the imported symbols, will automatically be established in the Workspace(repeat the import process for the remaining industry orientated watch lists).

Note: At this stage all symbols are still classified under the default Sector/Industry i.e. Djia0

It is commonplace for constituent lists to contain redundant symbols. In some cases the symbol has been changed and in others the company has ceased to trade or has undergone restructuring of some kind. Symbols that contain no quotes will not be assigned to categories by AFL formulas so it is necessary to download some data to the database to give the assignment code something to work with.

Current data will download from Yahoo ‘orders of magnitude’ faster than historical data. Since only one quote is required, for AFL formulas to function, it makes good sense to use the Yahoo Current option for the download, especially when the procedure is applied to ‘working’ databases containing thousands of symbols.

To download temporary data:

• 1) Open AmiQuote while AmiBroker is open with Data_CustomExample as the current database.
• 2) Get the tickers from AmiBroker using the AmiQuote Tools menu and Automatically import data with Yahoo Current as the selected source. The status will be recorded as the download proceeds.

Note: AmiQuote does not show error messages, in current mode, for symbols that are redundant, or have no data to download.

The current data for the day will only be available on the Yahoo server for a limited time (it is reset to zero in time for the new trading day). In one trial, conducted by the author, current data, for the American market, ceased to be available from 4 AM Eastern Time (USA).

A quick check can be made to see if the current quotes are extant at the time that a download is required.

To check the availability of current quotes:

1) Enter a valid stock symbol in the Quotes input box, at any Yahoo! Finance page, and then click on Get  Quotes.

The Quotes Summary page will open.

2) Look at the Change report (if the current data is still on the server the change % will be shown).

Note: When the current data is ‘deleted’ from the server the Change % report, and other outputs in the quotes panel at Y!Finance, reset to zero or N/A (the time might vary from day to day or with different exchanges for the American market). For international markets allow approximately four hours before ‘the opening bell’ (the author only checked a limited number of international exchanges – readers should check their own exchange for themselves).

Once the global database contains all of the required symbols, complete with data, the following AFL formula can be used to add symbols to a nominated industry, watch list by watch list.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

/*X_CategoryAddSymbols*/  
   
/*Assigns tickers to Industries, according to Watchlist membership,   
on a one to one basis i.e. tickers in Watchlist 0 are assigned to Industry 0 etc.*/  
   
Filter = 1;  
   
for(W = 0; W < 63; W++)//"W", the Watchlist number, is limited to 63 for v4.9 and below.  
{  
&#160;&#160; if(InWatchList(W))  
&#160;&#160;&#160;&#160;&#160; {   
&#160;&#160;&#160;&#160;&#160;&#160; CategoryAddSymbol("",categoryIndustry,W);    
&#160;&#160;&#160;&#160;&#160; }  
}  
AddColumn(Close,"Close",1.2);

To use watch list membership to assign symbols to industry groups:

1) Run an exploration using the X_CategoryAddSymbols formula, with all symbols selected and the Range set to n last days = 1.

Note: AddColumn(Close), in the formula, serves no practical purpose, in terms of assigning the symbols to the industry sub-categories; it is only there to allow ‘Explorer’ to run.

Sectors and Industry classifications are automatically assigned as the formula is processed.

Note: SBC and HWP are not assigned because they do not contain data (‘no quotes’ symbols remain ‘behind’ in the default industry folder i.e. Djia0, while all valid symbols are ‘moved’ to their nominated industry folder).

2) Manually check and repair or delete ‘no quotes’ symbols from the database.

Note: The quotes status, for databases containing only the current days data, can be seen in chart window for the selected symbol.

When the database quality has been confirmed, historical data can be downloaded to create a ‘working’ database.

Note: To download historical data; open Amiquote, get the tickers from AmiBroker  and then download with the date range set to capture the required history (the historical download will overwrite the temporary current values).

POST CREATION FIXES

After a database is created it sometimes becomes necessary to make changes or ‘repair’ any errors that are found.

There are a lot of different situations that can arise and they often require a creative approach to find ways to work around them. As a rule of thumb; use ASCII import to add symbols to the database, so that the information values can be appended, use watch-lists to categorize symbols in bulk or Symbols >> Assignments Organizer to manually move small numbers of symbols around and for data acquisition; import new symbol lists into AmiQuote (open a plain text list in AmiQuote as a .tls file) then download data for the new symbols and push it into the database, using the Automatic import checkbox in AmiQuote, (this approach allows a match-up between the imported date range and the existing range of the database).

One example from this database.

MSFT and  INTC are Nasdaq listings that are in the NYSE Market folder.

To move symbols from one market to another ‘after the event’:

• 1) Import a ticker list of symbols to be moved into a ‘vacant’ Watch list folder e.g in this example List 6 is used since the first six Watch lists (0-5) still contain the sector component lists.
• 2) Run an exploration using the following AFL formula (an AFL version is attached in the Attached Files section at the end of this post).

After categorization of the database is complete any redundant Watch lists can be emptied.

To empty redundant Watch lists:

• 1) Right click on the Watch list folder.
• 2) Select Erase (make empty) from the context menu that opens.
• 3) Nominate the Watch list in the Select watch list(s) window that opens.

The symbols will be removed from the Watch list, but not the database.

Note: Empty Watch list folders will not be present in the Workspace Symbols Tree next time the database is opened.

CONCLUSION

That concludes this tutorial that demonstrates some basic database management procedures.

The method chosen is only one of several ways of achieving the same, or similar, outcomes e.g. the procedure could be described as a top-down approach but the bottom-up path, where the global database is compiled by importing Watch lists via the Import Wizard, could just as easily have been used.

It isn’t necessarily the best possible way to go about it and users should experiment for themselves by mixing and matching the various data management tools available.

The base method will be used as a template, for a subsequent post, that will construct a ‘working’ Nasdaq Composite database (some slightly more advanced points, that expand on some of the key concepts introduced so far, will be detailed there).

APPENDIX

Logic for X_categoryAddSymbols

Filter = 1;

Passes all symbols for processing.

for(W = 0; W < 63; W++)

Is a form of counter (counters are standard fare in programming languages).

It comprises three statements:

• W = 0 assigns the number 0 to the variable W as the starting value,
• W < 63 sets an upper limit to the count,
• and W++ adds one to the count after the  following statements are executed.

if(InWatchList(W))

Places a condition on the statement to be executed i.e. the expression must be true for the following to be applied.

• {
• CategoryAddSymbol("",categoryIndustry,W);
  }

The statement to be executed, (more than one statement could be included within the curly brackets to form a compound statement).

AddColumn(Close,"Close",1.2);

Serves no purpose in assigning symbols to categories. It is required to allow the exploration to process the code.

More often than not, in example code, "i" is used as the variable in a counter. It has been ‘borrowed’ from mathematics where, by convention, "i" represents the index of summation in Summation Notation.

The ‘counter’ looping, used in X_categoryAddSymbols, is analogous to ‘walking through’ an index of the variables, when the execution of the code is considered as a sequence of steps.

Note: for this example the upper limit for the count was set to 63, which is the maximum number of Watch lists available in AB v4.9.

The processor walks from line to line repeating the code ‘module’ 63 times until the loop is exited ‘on the count’.

Code extension for X_categoryAddSymbols

Building on the basic module X_categoryAddSymbols v1 adds Watch list counting to the start of the code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

/*X_CategoryAddSymbols v1*/  
//Assigns tickers to Industries, according to Watchlist membership, on a one   
//to one basis i.e. tickers in Watchlist 0 are assigned to Industry 0 etc.    
//Adds auto Watch list count to X_CategoryAddSymbols v0    
//tested OK on AB v4.9  
   
Filter = 1;  
Count = 0;  
   
for(i = 0; i < 63; i++)//"W", the Watchlist number, is limited to 63 for v4.9 and below.  
&#160;&#160;&#160; if(StrLeft(CategoryGetName(categoryWatchlist,i),4) != "List")  
&#160;&#160;&#160; {   
&#160;&#160;&#160; Count = Count + 1;    
&#160;&#160;&#160; }  
&#160;  
for(W = 0; W < Count; W++)  
&#160;&#160;&#160; if(InWatchList(W))  
&#160;&#160;&#160; {   
&#160;&#160;&#160; CategoryAddSymbol("",categoryIndustry,W);    
&#160;&#160;&#160; }  
&#160;  
AddColumn(Close,"Close",1.2);

Code Extension 2 for X_categoryAddSymbols

Adds a Watch list report to the Exploration.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

/*X_CategoryAddSymbols v2*/  
//Assigns tickers to Industries, according to Watchlist membership, on a one   
//to one basis i.e. tickers in Watchlist 0 are assigned to Industry 0 etc.    
//Adds an Exploration report to X_CategoryAddSymbols v1    
//Tested OK on AB v4.9  
   
Filter = 1;   
Count = 0;  
   
for(i = 0; i < 63; i++)//"W", the Watchlist number, is limited to 63 for v4.9 and below.   
&#160;&#160; {    
&#160;&#160;&#160; if(StrLeft(CategoryGetName(categoryWatchlist,i),4) != "List")    
&#160;&#160;&#160;&#160;&#160; {    
&#160;&#160;&#160;&#160;&#160;&#160; Count = Count + 1;    
&#160;&#160;&#160;&#160;&#160; }    
&#160;&#160; }  
&#160;  
for(W = 0; W < Count; W++)   
&#160;&#160; {    
&#160;&#160;&#160; WLName = CategoryGetName(categoryWatchlist,W);    
&#160;&#160;&#160; Ticker = WriteIf(InWatchList(W),Name(),"");    
&#160;&#160;&#160; AddTextColumn(Ticker,WLName);  
&#160;&#160;&#160;&#160;&#160; if(InWatchList(W))   
&#160;&#160;&#160;&#160;&#160; {    
&#160;&#160;&#160;&#160;&#160; CategoryAddSymbol("",categoryIndustry,W);    
&#160;&#160;&#160;&#160;&#160; }    
&#160;&#160; }

Use the Exploration Results window to cross check the symbols that have been assigned to Sectors/Industries, against the Watch lists, or export the results to a Comma Separated Values (CSV) file for saving or printing.

ATTACHED FILES:

• broker reset files: resetindustries.txt and resetsectors.txt
• broker files: brokerindustries.txt and  brokersectors.txt
• Excel file used to create broker reset templates: resetcategories.xls
• DJI file with nominal groups and industries included: dji.xls
• AFL formula for Industry assignment: x_categoryaddsymbols.afl
• AFL formula for Market assignment (manual version): x_categoryaddsymbols3.afl
• AFL formula for Industry assignment updated with auto Watch list count: x_categoryaddsymbols-v1.afl
• AFL formula for Industry assignment updated with Watch list reporting: x_categoryaddsymbols-v2.afl
• _{Click on the link to browse spreadsheet files on line, or, Save As to download an editable version (requires Excel to edit or Excel Viewer to read).}
• _{Click on the link to browse text files on line, or, right click and Save Target As to download an editable version (requires NotePad or similar).}
• _{To download .afl files; right click on the link  and Save Target As ‘filename’ at Program Files/AmiBroker/Formulas/Custom to access them as formulas in the AmiBroker Charts list.}

•  _{Version 1 – Oct6/2007 – updated from a draft, added formula for ‘manual’ assignment to markets, uploaded afl files}
• _{Version 2 – Oct10/2007 – added an Appendix containing formula logic and a new version of the code}
• _{Version 3 – Oct17/2007 – added categoryAddSymbols v2}

Filed by brian_z at 4:15 am under Database Management
3 Comments

(No Ratings Yet)

 Loading ...

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
5 Comments

(No Ratings Yet)

 Loading ...

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
No Comments

(1 votes, average: 5.00 out of 5)

 Loading ...

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
No Comments

(No Ratings Yet)

 Loading ...

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
No Comments

(No Ratings Yet)

 Loading ...