def update1YearForAll():
wb = openpyxl.load_workbook(
'C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx'
) #if file exists
sheet = wb.active
api = getApi()
myTickers = wiki500.getWiki500()
params = getParams()
for i, v in enumerate(myTickers):
myURL = 'https://api.polygon.io/v1/open-close/{}/{}'.format(
v, stockDataFrames.get1Y())
resp = api.polygon._session.request('GET', myURL, params=params)
if (i + 1) % 26 == 0:
time.sleep(5)
jjson = resp.json()
#print(jjson)
try:
rowC = 'C' + str(i + 2)
print(jjson['symbol'])
print(jjson['low'])
sheet[rowC] = jjson['low']
except:
rowC = 'C' + str(i + 2)
print("Error occured at {}".format(rowC))
sheet[rowC] = "N/A"
wb.save('C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx')
def write500TickersExcel():
wb = openpyxl.Workbook() #if file is being created first time
sheet = wb.active
myTickers = wiki500.getWiki500()
for i, v in enumerate(myTickers):
cell = 'A' + str((i + 2))
sheet[cell] = v
wb.save('C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx')
def update500TickersExcel():
wb = openpyxl.load_workbook(
'C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx'
) #if file exists
sheet = wb.active
myTickers = wiki500.getWiki500()
for i, v in enumerate(myTickers):
cell = 'A' + str((i + 2))
sheet[cell] = v
wb.save('C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx')
def openMarket():
wb = openpyxl.load_workbook(
'C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx'
) #if file exists
sheet = wb.active
api = getApi()
myTickers = sorted(wiki500.getWiki500())
params = getParams()
for i, v in enumerate(myTickers):
myURL = 'https://api.polygon.io/v2/snapshot/locale/us/markets/stocks/tickers/{}'.format(
v)
try:
resp = api.polygon._session.request('GET', myURL, params=params)
if (i + 1) % 100 == 0:
time.sleep(5)
jjson = resp.json()
#print(jjson)
jjson = resp.json()
print(json.dumps(jjson, indent=4))
try:
rowG = 'G' + str(i + 2)
if jjson['ticker']['lastTrade']['p'] == 0:
print('Val was 0')
else:
sheet[rowG] = jjson['ticker']['lastTrade']['p']
except:
rowG = 'G' + str(i + 2)
print("Error occured at {}".format(rowG))
sheet[rowG] = "N/A"
except:
rowG = 'G' + str(i + 2)
print("Error occured at {}".format(rowG))
wb.save('C:/Users/aj282/OneDrive/Desktop/pythonStocks/my500Xl.xlsx')