# views.py for autoupdate using tiingo. ''' Purpose: This routine will open the master spreadsheet and read the ticker values from the 'Value' sheet, construct a tiingo call and retrieve the newest available prices and save them in the value column. Author: W. Roberts Initial: 11 May 2018 To be done: Change all the Excel ss references to odf references Open and read the values sheet of the master spread sheet tiingo lookup of master spreadsheet.Values tickers to obtain price record price in price column of appropriate ticker. Optional: create a postgres database to record historical stock prices -- but probably not as this can be found elsewhere pretty easily. We'll see. ''' from flask import render_template, Blueprint import requests import time from datetime import datetime, timedelta import ezodf from ezodf import newdoc, Sheet import decimal from decimal import Decimal from decimal import getcontext # The following should be moved to the .cfg file _TIINGO_flag = True _TIINGO_KEY = '7dccb22a99e2e290a405c363117ea61a207e0662' _TIINGO_url = 'https://api.tiingo.com/tiingo/daily/{}/prices' _TIINGO_url_iex = 'https://api.tiingo.com/iex/{}' _TIINGO_headers = { 'Authorization': 'Token ' + _TIINGO_KEY, 'Content-Type': 'application/json', } #Master Spreadsheet URI _FINANCE_MASTER_SPREADSHEET_TEST_URI = \ '/Users/brtc/sw/fl_smf/pvt/FinancialPlanning-2018-TESTFILE.ods' _FINANCE_MASTER_SPREADSHEET_PROD_URI = \ '/Users/brtc/Documents/wr/Retirement/FinancialPlanning-2026XXXXCurrent.ods' # Configure Blueprints market_watch_blueprint = Blueprint('market_watch', __name__, template_folder='templates') # # is_none(x) function # This function looks at the value of the cell.value argument. If it is None, it is set to zero. # If it is not None, then return the value of the argument. # def is_none(x): if x is not None: return x else: return 00 # ############################################################################################ # The ss class is a unified spreadsheet row variable with row location stored to facilitate loading the ticker values # from TIINGO.com eventually (if I ever get around to it) I'll re-write the capture to do a bulk load of TIINGO # tickers and values into the spreadsheet. class ss: ticker = '' value = 0.00 new_value = 0.00 loc = '' #adding timestamp update_time = '' # ############################################ {Main} ################################################################ @market_watch_blueprint.route('/look') def market_watch(): # Open Master Spreadsheet doc = ezodf.opendoc(_FINANCE_MASTER_SPREADSHEET_PROD_URI) print('***DEBUG***', doc, _FINANCE_MASTER_SPREADSHEET_PROD_URI) sheet = doc.sheets['Value'] # Now fetch the tickers and display them in the table. no_rows = sheet.nrows() ticker_list = [] ticker_value = [] ddx = [] decimal.getcontext().prec = 4 for index in range(0, no_rows): row = sheet.row(index) ddx.append(ss()) # get Ticker and present value from mss (master spreadsheet) ticker = is_none(row[0].value) price = is_none(row[1].value) ddx[index].ticker = ticker ddx[index].value = price ddx[index].loc = index if row[0].value != None: ticker_list.append(ticker) ticker_value.append(price) url = _TIINGO_url_iex.format(ticker) # url = _TIINGO_url.format(ticker) r = requests.get(url, headers=_TIINGO_headers) if r.status_code != 200: print("Bad request: status:",r.status_code, r.url) ddx[index].new_value = price continue rj = r.json() if rj: try: print(r.status_code, ticker.upper(), rj[0]['timestamp'][0:10],rj[0]['timestamp'][11:16] ,rj[0]['tngoLast']) ddx[index].new_value = rj[0]['tngoLast'] ddx[index].update_time = rj[0]['timestamp'][0:10]+' '+ rj[0]['timestamp'][11:16] sheet[index,1].set_value(ddx[index].new_value, 'float') except KeyError: print(r.status_code, ticker.upper(), "Couldn't get the ticker") ddx[index].new_value = price # keep old value -- don't update a bad ticker continue except IndexError: print(r.status_code, r.url, ticker.upper(), "Badness here: Index Error") except TypeError: print("Type Error", ) print(rj) ddx[index].new_value = price # keep old value -- don't update a bad ticker continue else: print("RJ has NO stuff.", r.url, index, "Keeping Backup Data", price) ddx[index].new_value = price #keep old data # At this point we have a list of present tickers and their values. We have displayed the old ones on the form. # We now need to look up the tiingo data using the ticker_list and save it when it comes back. # It may be better to create a class structure for ticker_list containing ticker_list.ticker, ticker_list.value, # ticker_list.position to keep everything tidy, where position is the spreadsheet row # for the ticker. # We are now going to open a brand new shiny spreadsheet and right the values on the fresh sheet. # If this works, we will try a real run against a copy of the production sheet. ods = newdoc(doctype ='ods', filename='/Users/brtc/sw/fl_smf/pvt/test.ods') ods.sheets += Sheet('Sheet1') mysheet = ods.sheets[0] mysheet.append_rows(150) windex = 0.00 oldwindex = 0.00 print ('Index ', ods.docname, len(ddx), mysheet.name, doc.sheets[0].name) # label it mysheet[2,0].set_value('Ticker') mysheet[2,1].set_value('New Price') mysheet[2,2].set_value('Old Price') mysheet[2,3].set_value('Difference') for index in range (3, no_rows): if ddx[index].ticker != 0: tt = ddx[index].ticker pp = Decimal(ddx[index].value) nv = Decimal(ddx[index].new_value) mysheet[index, 0].set_value(tt) mysheet[index, 2].set_value(Decimal(pp)*1) mysheet[index, 1].set_value(Decimal(nv)*1) windex = windex + ddx[index].new_value oldwindex = oldwindex + ddx[index].value print('posting ', mysheet[index, 0].value, mysheet[index, 1].value, mysheet[index, 2].value) else: tt = ' ' pp = '' nv = '' print('posting ', mysheet[index,0].value, mysheet[index,1].value, mysheet[index,2].value) ods.save() doc.save() return render_template('value_query.html', values=sheet, tiingo = ddx, updatetime=time.asctime(time.localtime(time.time())), windex = windex, oldwindex = oldwindex) # # # now we need a new function to process the output to the spreadsheet. This will eventually replace getp(). # # for now we'll plan on calling it writess() # testing writess() print(time.asctime(time.localtime(time.time()))) yesterday = (datetime.today() - timedelta(1)).strftime('%Y-%m-%d') # TIINGO.com print('Completed @', time.asctime(time.localtime(time.time())))