Downloading data from wsj using python script here
Concatenating daily files
# Below command concatenates files and skips the first row of each file
$ tail -q -n +2 *.tsv > /tmp/stock_aggr.tsv
Creating table in mysql
drop table stocks.gainer_loser;
create table stocks.gainer_loser(
day_rank SMALLINT,
ticker TEXT,
company TEXT,
price DECIMAL,
change_price DECIMAL,
change_percent DECIMAL,
volume TEXT,
stock_type TEXT,
trade_date TEXT
);
Load data in table
LOAD DATA INFILE '/tmp/output_file.tsv' INTO TABLE stocks.gainer_loser FIELDS TERMINATED BY '\t';
Sample queries
Finding stocks appearing in gainer and loser list in last X days
select * from (
select ticker, stock_type, COUNT(1) as x from stocks.gainer_loser
where STR_TO_DATE(trade_date, '%Y%m%d') BETWEEN NOW() - INTERVAL 100 DAY AND NOW()
group by ticker, stock_type
order by ticker, count(1) desc
) a
where a.x > 10;