Automate finding Return,Average,Variance,Standard Deviation from historical stock data for a given company between two given dates using python, openpyxl, yahoo_finance and pandas,Python teacher Sourav,Kolkata 09748184075

import yahoo_finance
import sys
import csv
import xlsxwriter
import openpyxl
import xlrd
import pandas as pd
from openpyxl.utils import coordinate_from_string, column_index_from_string

symbol = yahoo_finance.Share(“GOOG”)
google_data = symbol.get_historical(“2015-06-25”, “2016-06-30”)
google_df = pd.DataFrame(google_data)

# Output data into CSV
google_df.to_csv(“/home/sourav/google_stock_data.csv”)
#wb=xlsxwriter.Workbook(“google_stock_data.csv”.replace(“csv”,”xlsx”))
wb = openpyxl.Workbook()
ws = wb.active
ws.title=’google_stock_data_experiment’
f = open(‘google_stock_data.csv’)
reader = csv.reader(f, delimiter=’,’)
for row in reader:
ws.append(row)
f.close()

wb.save(‘google_stock_data.xlsx’)
ws=wb.active
ws.cell(row=1, column=10,value=’Trend’)
ws.cell(row=1, column=11,value=’Return’)
wb.save(‘google_stock_data.xlsx’)
tempval=0.0
values=[]
returnval=0.00
count=0
for row in ws.iter_rows(min_row=1, min_col=3, max_col=3):

for cell in row:
count+=1
#print(cell.coordinate)
xy = coordinate_from_string(cell.coordinate) # returns (‘A’,4)
colnum = column_index_from_string(xy[0]) # returns 1
rownum = xy[1]
#print(str(colnum) +’ ‘+str(rownum)+’ has a value ‘+ws.cell(row=rownum, column=colnum).value)

if ws.cell(row=rownum, column=colnum).value==”:

break
try:

val = float(cell.value)
#print(‘val is ‘+str(val)+ ‘ and tempval is ‘+str(tempval))
if tempval!=0.0:

if (val>tempval) and (rownum!=1):

ws.cell(row=rownum, column=colnum+7,value=’UP’)
tempval=val
elif tempval>val and (rownum!=1):
#print(tempval)
ws.cell(row=rownum, column=colnum+7,value=’DOWN’)
tempval=val
elif (tempval==val) and (rownum!=1):

ws.cell(row=rownum, column=colnum+7,value = ‘same’)
tempval=val
else:
pass

else:
tempval=val

#print (val)
if ws.cell(row=rownum, column=colnum).value!=” and ws.cell(row=rownum+1, column=colnum).value!=”:
try:
val1=float(ws.cell(row=rownum, column=colnum).value)
val2=float(ws.cell(row=rownum+1, column=colnum).value)
returnval=(val1/val2)-1

except:
returnval=0.0
#ws.cell(row=rownum, column=colnum+8,value=returnval)
ws.cell(row=rownum, column=colnum+8,value=returnval).number_format = ‘0.00%’
else:
pass

except ValueError:
pass
#print (count)
straddress=’K2:K’ + str(count)
#print (straddress)
ws[“M2”] = “=AVERAGE(“+straddress+”)”
ws[“M3”] = “=VARP(“+straddress+”)”
ws[“M4”] = “=STDEV(“+straddress+”)”
wb.save(‘google_stock_data.xlsx’)

wb.close()