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