Posted by: Sourav | April 25, 2017

Automated Moving Averages – Simple and Exponential using stock data from yahoo finance,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’)
ws.cell(row=1, column=14,value=’10-day SMA’)
ws.cell(row=1, column=15,value=’Smoothing Constant 2/(10 + 1)’)
ws.cell(row=1, column=16,value=’10-day EMA’)
wb.save(‘google_stock_data.xlsx’)
cell = ws[“B2”]

for row in ws.iter_rows(min_row=2, min_col=3, max_col=3):
for cell in row:

xy = coordinate_from_string(cell.coordinate) # returns (‘A’,4)
colnum = column_index_from_string(xy[0]) # returns 1
rownum = xy[1]
tempval2=float(ws.cell(row=rownum, column=colnum).value)
ws.cell(row=rownum, column=colnum,value=tempval2).number_format = ‘%s%s’

tempval=0.0
values=[]
returnval=0.00
count=0
cell = ws[“A2″]
xycell = coordinate_from_string(cell.coordinate)
rowstorage=int(xycell[1])
#print (rowstorage)

tempcount=2

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(rownum)

#print(str(colnum) +’ ‘+str(rownum)+’ has a value ‘+ws.cell(row=rownum, column=colnum).value)

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

break
if (int(rownum)>=rowstorage+9):
#print(‘inside’)
straddress=’C’ +str(tempcount)+’:C’ + str(rownum)
#print(straddress)

ws.cell(row=rownum, column=14,value=”=AVERAGE(“+straddress+”)”).number_format = ‘0.00’
if (int(rownum)==rowstorage+9):
tempu=ws.cell(row=rownum, column=14).value
ws.cell(row=rownum, column=16,value=tempu).number_format = ‘0.00’

tempcount+=1
if (int(rownum)>=rowstorage+10):
ws.cell(row=rownum, column=15,value=(2/(10+1))).number_format = ‘0.00’

## if (int(rownum)>=rowstorage+10):
## tempu2=(ws.cell(row=rownum-1, column=16).value)
##
## tempu3=(ws.cell(row=rownum, column=3).value)
## tempu4=(ws.cell(row=rownum, column=15).value)
## print(tempu2)
## print(tempu3)
## print(tempu4)
## ws.cell(row=rownum, column=16,value=tempu4*(tempu3-tempu2)+tempu2).number_format = ‘0.00’
cell = ws[“A2″]
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+”)”
ws.column_dimensions[‘N’].width = 15

ws.column_dimensions[‘O’].width = 20

ws.column_dimensions[‘P’].width = 15
wb.save(‘google_stock_data.xlsx’)

wb.close()

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: