{"id":1761,"date":"2008-03-25T09:44:08","date_gmt":"2008-03-25T09:44:08","guid":{"rendered":"http:\/\/www.amibroker.org\/userkb\/2008\/03\/25\/ascii-import-standard-and-poors-global-1200\/"},"modified":"2008-03-27T21:44:57","modified_gmt":"2008-03-27T21:44:57","slug":"ascii-import-standard-and-poors-global-1200","status":"publish","type":"post","link":"http:\/\/www.amibroker.org\/editable_userkb\/2008\/03\/25\/ascii-import-standard-and-poors-global-1200\/","title":{"rendered":"ASCII Import – Standard and Poor’s Global 1200"},"content":{"rendered":"

Objective<\/u><\/strong> <\/p>\n

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.<\/p>\n

A Global 1200 database, suitable for use with Yahoo<\/em> data, is used as the example.<\/p>\n

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).<\/p>\n

 Pre-Requisites<\/strong><\/u><\/p>\n

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

http:\/\/www.amibroker.com\/guide\/d_ascii.html<\/a><\/p>\n

 Introduction<\/u><\/strong><\/p>\n

\n

"American Standard Code for Information Interchange (ASCII) <\/strong>allows digital devices to communicate with each other and to process, store, and communicate character-oriented information" 1<\/sup>.<\/p>\n<\/blockquote>\n

It is the format used to import data into AmiBroker<\/em>, via AmiQuote<\/em>, or manually, via the ASCII Import<\/em> function.<\/p>\n

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

Note: the example is a basic one, but, users can easily extend the method to more complex databases.<\/p>\n

Preparation<\/u><\/strong><\/p>\n

<\/u><\/strong><\/p>\n

Step1<\/u><\/strong><\/p>\n

A constituent list, in Comma Separated Value (CSV) format, can be downloaded from the Standard and Poors Homesite<\/em> (US):<\/p>\n

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<\/a><\/p>\n

Go to Indices >>  + Equity Indices >> S and P Global Indices >> S and P Global 1200<\/strong><\/p>\n

OR follow the link below to go directly to the page:<\/p>\n

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<\/a><\/p>\n

 <\/p>\n

Step 2<\/u><\/strong><\/p>\n

Click on Download Table, <\/strong>at the top of the page, to save a local copy of the Constituent List<\/em> (save it in a spreadsheet compliant format)<\/p>\n

\"ASC001\"<\/a><\/p>\n

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

 <\/p>\n

Step 3<\/u><\/strong><\/p>\n

Prepare the file for ASCII Import, using the spreadsheet functions:<\/p>\n

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

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

c) Copy the Fullname<\/strong>, Ticker<\/strong>, Market ID<\/strong> and Sector ID<\/strong> columns to a separate Global1200.CSV<\/strong> file, ready for importing.<\/p>\n

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

 <\/p>\n

Step 4<\/u><\/strong><\/p>\n

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

 <\/p>\n

Step 5<\/u><\/strong><\/p>\n

Prepare an ASCII formats file and save it in the Programs\/AmiBroker\/Formats<\/strong> folder.<\/p>\n

The file, for this example, needs to be in the following format (the file can be written in a plain text editor, like NotePad<\/em>, and saved as Global1200.format<\/strong>):<\/p>\n

\"ASC002\"<\/a><\/p>\n

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

 <\/p>\n

Step 6<\/u><\/strong><\/p>\n

Create a new EOD database, called Data_Global1200<\/strong><\/p>\n

Go to File >> Import ASCII<\/strong> and elect to Open<\/strong> the Global1200.csv<\/strong> file as file type Global1200(*.*)<\/strong><\/p>\n

Note: the import.types<\/em> (formats file) list will be the default Files of type<\/em> list in the file browser window that opens.<\/p>\n

\"ASC003\"<\/a><\/p>\n

The symbol list and database structure, as defined in the Global1200.csv file, will now be set-up in the current database.<\/p>\n

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

\"ASC005\"<\/a><\/p>\n

Step 7<\/u><\/strong><\/p>\n

Data can now be downloaded, for the database, from the Yahoo historical server.<\/p>\n

On downloading some symbols will return a 404 error.<\/p>\n

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.<\/p>\n

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).<\/p>\n

 <\/p>\n

ATTACHED FILES:<\/p>\n

24-mar-2008_gbl1200.xls<\/a>
global1200.xls<\/a><\/p>\n

Browse the spreadsheet files on line, or, right click and select <\/sub>Save As <\/sub>to download them as an editable version.<\/sub><\/em><\/p>\n

REFERENCES:<\/p>\n

1 – http:\/\/en.wikipedia.org\/wiki\/ASCII<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[131],"tags":[],"_links":{"self":[{"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/posts\/1761"}],"collection":[{"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/comments?post=1761"}],"version-history":[{"count":0,"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/posts\/1761\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/media?parent=1761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/categories?post=1761"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.amibroker.org\/editable_userkb\/wp-json\/wp\/v2\/tags?post=1761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}