2MmrNfkG

1#simplified updatecodes
2import zipfile, urllib.request, operator, sqlite3, csv, pandas, quandl, datetime
3quandl.ApiConfig.api_key = 'yiSaMst67pW9Xesg9Zgy'
4
5class UpdateCodes:
6    def __init__(self):
7        self.stime = datetime.datetime.now()
8        self.url = r'https://www.quandl.com/api/v3/databases/NSE/codes?api_key=yiSaMst67pW9Xesg9Zgy'# url of the zip file
9        self.conn = sqlite3.connect('stockx_new.db')# connect to database
10        self.c = self.conn.cursor()# create a cursor
11        self.c.execute('DELETE FROM codes')# empty the codes table to insert new values for each stock
12        print('Running Update Codes')
13    
14    def fetchcsv(self):
15        self.local_filename, self.headers = urllib.request.urlretrieve(self.url)# getting the zipfile
16        self.zip_ref = zipfile.ZipFile(self.local_filename, 'r')
17        self.csvfile = self.zip_ref.namelist()
18        self.csvfile = self.csvfile[0]
19        self.csvfile = self.zip_ref.extract(self.csvfile)# extracting the zipfile
20        self.file = open(self.csvfile, 'r')
21        self.file = csv.reader(self.file, delimiter = ',')
22    
23    def fetchdf(self):
24        for self.row in self.file:
25            self.df = quandl.get(self.row[0])
26            self.df = self.df.fillna(value = 1)
27            self.nse_code = self.row[0][4:]
28            self.quandl_code = self.row[0]
29            self.name = self.row[1]
30            self.calc()
31            self.commitz()
32        self.finish()
33
34    def calc(self):
35        self.date = str(self.df.index[-1].date().year) + '-' + str(self.df.index[-1].date().month) + '-' + str(self.df.index[-1].date().day)# 1
36        self.price = self.df['Close'].iloc[-1]
37        self.high_max = self.df['High'].max()
38        self.low_max = self.df['Low'].min()
39        self.dg = self.df.tail(248)#one year's data
40        self.high_52 = self.dg['High'].max()# one years max high
41        self.low_52 = self.dg['Low'].min()# one years max low
42        self.cagr = round((self.dg['Close'].iloc[-1] / self.dg['Close'].iloc[0]) - 1,3)# one year CAGR
43        self.change = round(((self.df['Close'].iloc[-1] - self.df['Close'].iloc[-2]) / self.df['Close'].iloc[-1]) * 100,3)# last change
44        self.volatility = round(((self.df['High'].iloc[-1] - self.df['Low'].iloc[-1]) / self.df['Low'].iloc[-1]) * 100,3)#last day's volatility
45    
46    def commitz(self):
47        self.c.execute('CREATE TABLE IF NOT EXISTS codes( date TEXT, name TEXT, quandl_code TEXT, nse_code TEXT, price REAL, high_max REAL, low_max REAL, high_52 REAL, low_52 REAL, cagr REAL, change REAL, volatility REAL)')
48        self.c.execute('INSERT INTO codes (date, name, quandl_code, nse_code, price, high_max, low_max, high_52, low_52, cagr, change, volatility) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' ,(self.date, self.name, self.quandl_code, self.nse_code, self.price, self.high_max, self.low_max, self.high_52, self.low_52, self.cagr, self.change, self.volatility))
49        self.conn.commit()
50        print(self.name)
51    
52    def finish(self):
53        self.c.close()
54        self.conn.close()
55        print('start time : ', stime)
56        print('end time: ', datetime.datetime.now())
57
58obj = UpdateCodes()
59obj.fetchcsv()
60obj.fetchdf()
61obj.finish()