Getting data for analysis in mySQL

Steps to have the latest data available for analysis

• Download historicos from megabolsa.com.
• Extract to a directory mamed historicos.
• Download additional daily data from here and place them in a directory named diarios.
• First, treat historic data and create all.txt.cut with these 2 commands:

$ ../atem-0.3.5-static-linux/atem64  --field-separator='|'   --format=-time ../historicos > all.txt
$ cut -d'|' -f -7 all.txt | awk -F'|' '{$(NF)="0|0|0" FS $(NF);}1' OFS='|' | awk -F'|' '{$(3)="0" FS $(3);}1' OFS='|' > all.txt.cut

• Then combine the daily close files in tmp.txt.cut with these 2 commands

$ awk -F',' '{x=$2;yy=substr(x,1,4);mm=substr(x,5,2);dd=substr(x,7,2); $2=yy"-"mm"-"dd}1' OFS='|' ../diarios/*.txt | sort > tmp.txt
$ awk -F'|' '{$(NF)="0|0|0" FS $(NF);}1' OFS='|' tmp.txt | awk -F'|' '{$(3)="0" FS $(3);}1' OFS='|' > tmp.txt.cut

• Finally, create the database:

$ rm val.db
$ sqlite3 val.db < 0create.sql
$ sqlite3 val.db < 1fill.sql


The contents of 0create.sql
CREATE TABLE stockprices (
        symbol          varchar(12) not null default '',
        date            date default null,
        previous_close  real default null,
        day_open        real default null,
        day_high        real default null,
        day_low            real default null,
        day_close       real default null,
        day_change      real default null,
        bid             real default null,
        ask             real default null,
        volume          numeric default null
    );
CREATE UNIQUE INDEX stockprices_pkey
        on stockprices (symbol, date);


The contents of 1fill.sql
.import tmp.txt.cut stockprices
.import all.txt.cut_cleo stockprices

Convert Metastock data to mySQL

Metastock is a well-known stock analysis program with a proprietary data format. Some web pages, for instance megabolsa, provide historical data in metastock format. To be able to make our own tools, we will need a better format. The tool mySQL is a reasonable source for this. It is widely available everywhere.

To extract the metastock information, atem is a useful tool which can be downloaded from here.

Next we illustrate the procedure to load the metastock data stored in directory hist for a given stock (spanish Tubacex in this example) to a mySQL database:

Display help for atem64
./atem64 --help

List the different ticker symbols in the directory
./atem64 --symbols --field-separator=,  ../hist

./atem64 --symbols --field-separator=,  ../hist | grep TUB
Produces this output: TUB,Tubacex,D,1986-02-14,2013-12-31,89,F89.DAT,63,156,M

This is useful to locate the file for tubacex: F89.DAT

./atem64 --fdat=89  --field-separator=,  ../hist
Print on screen the CSV file corresponding to TUB

Generate tub.txt file with a | separator
Remove time information for end-of-day data. Change date-from as needed.
./atem64 --fdat=89  --field-separator='|'  --date-from=2013-01-01 --format=-time hist |grep TUB > tub.txt

Some raw processing of the generated file:

1.Cut the last column from tub.txt
2.Insert 0 for day_change, bid, ask before volume information
3.Insert 0 for previous close, after date and before open

cut -d'|' -f -7 tub.txt | awk -F'|' '{$(NF)="0|0|0" FS $(NF);}1' OFS='|' | awk -F'|' '{$(3)="0" FS $(3);}1' OFS='|' > tub.txt.cut

Finally,create the table with this
rm val.db
sqlite3 val.db < createtable.sql
sqlite> .import tub.txt.cut stockprices

where createtable.sql has the following content:

CREATE TABLE stockprices (
        symbol          varchar(12) not null default '',
        date            date default null,
        previous_close  real default null,
        day_open        real default null,
        day_high        real default null,
        day_low         real default null,
        day_close       real default null,
        day_change      real default null,
        bid             real default null,
        ask             real default null,
        volume          numeric default null
    );
CREATE UNIQUE INDEX stockprices_pkey
        on stockprices (symbol, date);

This table should be compatible with the beancounter format.