March 25, 2008
ASCII Import – Standard and Poor’s Global 1200
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" 1.
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):
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:
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