# whole-NSE EQ-series totals from sec_bhavdata_full (turnover, delivery value, breadth) - tests IPO-funding liquidation hypothesis
import pandas as pd,glob
r=[]
for f in sorted(glob.glob('data/nse/sec_bhavdata_full_*.csv')):
    t=pd.read_csv(f); t.columns=[c.strip() for c in t.columns]; t=t[t.SERIES.str.strip()=='EQ']
    for c in ['PREV_CLOSE','CLOSE_PRICE','DELIV_QTY','AVG_PRICE','TURNOVER_LACS']: t[c]=pd.to_numeric(t[c],errors='coerce')
    d=pd.to_datetime(t.DATE1.str.strip().iloc[0],format='%d-%b-%Y').strftime('%Y-%m-%d')
    ch=t.CLOSE_PRICE/t.PREV_CLOSE-1
    r.append(dict(date=d,turnover_cr=t.TURNOVER_LACS.sum()/100,deliv_val_cr=(t.DELIV_QTY*t.AVG_PRICE).sum()/1e7,adv=(ch>0).sum(),dec=(ch<0).sum(),median_chg_pct=100*ch.median(),
        n_down_gt3pct=(ch<-0.03).sum()))
x=pd.DataFrame(r).drop_duplicates('date').set_index('date').sort_index(); x['deliv_pct_value']=100*x.deliv_val_cr/x.turnover_cr
for c in ['turnover_cr','deliv_val_cr']: x[c+'_20d']=x[c].shift(1).rolling(20,min_periods=15).mean()
x.to_csv('results/market_breadth.csv'); print(x.tail(11).round(1).to_string())