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.