Analyzing top gainer loser data

Housekeeping with mysql

1. Downloading data from wsj using python script here
2. Concatenating daily files

   # Below command concatenates files and skips the first row of each file
   $ tail -q -n +2 *.tsv > /tmp/stock_aggr.tsv

3. 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
   );
   

4. Load data in table

   LOAD DATA INFILE '/tmp/output_file.tsv' INTO TABLE stocks.gainer_loser FIELDS TERMINATED BY '\t';

    

Sample queries

1. 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;