vindex vindex - 7 months ago 37
Python Question

Python: for loop to find how many stocks hits 52 weeks high and low

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

Answer

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
Comments