I can calculate how many stocks that is in the 52 weeks new high or new low for the last trading day. But I need to calculate from the first day in the csv file till the last day in the csv.
Example:
02-01-2014 , 10 stocks 52 weeks high and 45 is 52 weeks low
03-01-2014, 23 stocks 52 weeks high and 56 stocks 52 weeks low
04-01-2014, 34 stocks 52 weeks high and 34 stocks 52 weeks low.
import pandas as pd
import numpy as np
import csv
import datetime
import matplotlib.pyplot as plt
import talib as ta
import stocklist
now = datetime.datetime.now()
STOCKS = ['Abc','cdf','gg','D','AN','OX']
Stockslen = len(STOCKS)
h_cnt=0
l_cnt=0
#Creating 5 df for data analysis
df_today52w_High = pd.DataFrame(columns=['Stock','Today 52w_High'])
df_today52w_Low = pd.DataFrame(columns=['Stock','Today 52w_Low'])
for x in range (len(STOCKS)):
print "############### "
print STOCKS [x]
print "###############"
q_data = pd.read_csv(STOCKS [x]+".csv", index_col='Stock', usecols =[0,1,3,4,5,6,7])
high = q_data.High
h=np.array(high)
date_ = q_data.Date
dt = np.array(date_)
open_ = q_data.Open
o = np.array(open_)
low = q_data.Low
l = np.array(low)
close = q_data.Close
c = np.array(close)
if h[-1] == ta.MAX(h,252)[-1]:
df_today52w_High.loc[len(df_today52w_High)] = [STOCKS[x],1]
h_cnt += 1
print h_cnt
else:
df_today52w_High.loc[len(df_today52w_High)] = [STOCKS[x],0]
if l[-1] == ta.MIN(l,252)[-1]:
df_today52w_Low.loc[len(df_today52w_Low)] = [STOCKS[x],1]
l_cnt += 1
print l_cnt
else:
df_today52w_Low.loc[len(df_today52w_Low)] = [STOCKS[x],0]
df_new = pd.merge(df_today52w_High,df_today52w_Low,how='outer',on='Stock')
df_new['52w high']= h_cnt
df_new['52w low']= l_cnt
The csv in the STOCKS has format as below. I have 300 stocks in the STOCKS list. I just shows a few here.
Stock,Date,Time,Open,High,Low,Close,Volume
AAX,2014-01-02,00:00:00,1.0,1.02,1.0,1.01,3251900
AAX,2014-01-03,00:00:00,1.01,1.05,1.01,1.03,8416100
AAX,2014-01-06,00:00:00,1.04,1.05,1.02,1.03,2625200
AAX,2014-01-07,00:00:00,1.03,1.03,1.01,1.01,2539700
AAX,2014-01-08,00:00:00,1.02,1.02,1.0,1.02,2072700
AAX,2014-01-09,00:00:00,1.02,1.02,1.0,1.01,2589600
AAX,2014-01-10,00:00:00,1.01,1.01,1.0,1.01,2057200
AAX,2014-01-13,00:00:00,1.01,1.01,1.0,1.0,1284000
AAX,2014-01-15,00:00:00,1.0,1.01,1.0,1.0,1938100
.
.
AAX,2016-02-29,00:00:00,0.25,0.26,0.24,0.25,63660600
AAX,2016-03-01,00:00:00,0.25,0.26,0.25,0.26,100823200
AAX,2016-03-02,00:00:00,0.27,0.28,0.26,0.28,57543300
AAX,2016-03-03,00:00:00,0.28,0.29,0.27,0.28,113837600
AAX,2016-03-04,00:00:00,0.29,0.3,0.28,0.3,138182600
Instead of df using writerow
if h[y]== ta.MAX(h,20)[y]:
csvout = open('52w_h.csv', 'a')
csvwrite = csv.writer(csvout)
csvwrite.writerow([STOCKS [x][0]]+[dt[y]]+["1"])
csvout.close()
else:
csvout = open('52w_h.csv', 'a')
csvwrite = csv.writer(csvout)
csvwrite.writerow([STOCKS [x][0]]+[dt[y]]+["0"])
csvout.close()
Then u can group the date using groupby
a = pd.read_csv("52w_h.csv")
b = a.groupby('Date')
df_h= b['52wh'].sum()
Output:
2016-04-06 160
2016-04-07 170
2016-04-08 142