def markAttendance(prn, classroom, course_name, date_time=datetime.now().strftime("%Y-%m-%d %H:%M:%S")): connection = establishConnection() cursor = connection.cursor() cursor.execute( """SELECT course_code FROM course WHERE course_name = '%s'""" % (course_name)) r = cursor.fetchone() if not r: print( f'Error: Course {course_name} does not exist in the database... Check spelling' ) return 'Failed: Unsuccessful in marking attendnace of student...' else: course_code = r[0] cursor.execute("""INSERT INTO attends VALUES(%s, '%s', '%s', %s) ; """ % (prn, date_time, classroom, course_code)) cursor.close() connection.commit() connection.close() return f"Attendance of student with PRN {prn} for course {course_name} dated on {date_time} in venue {classroom} is succesfully recorded!"
def recordLectureConductedByFaculty(lecture, faculty_name, course_name): connection = establishConnection() cursor = connection.cursor() cursor.execute( """ SELECT emp_id FROM faculty WHERE faculty_name = '%s' """ % (faculty_name)) r = cursor.fetchone() if not r: return 'Error: Faculty name {faculty_name} not found in the database' else: emp_id = r[0] cursor.execute( """SELECT course_code FROM course WHERE course_name = '%s' """ % (course_name)) r = cursor.fetchone() if not r: return 'Error: Course name {course_name} not found in the database' else: course_code = r[0] cursor.execute("""INSERT INTO conducts VALUES('%s', '%s', %s, %s)""" % (lecture[1], lecture[0], emp_id, course_code)) cursor.close() connection.commit() connection.close() return f"Lecture conducted by faculty {faculty_name} dated {lecture[1]} in classroom {lecture[0]} is recorded in the database."
def conexao(): import connection connection = connection.conexao() cursor = connection.cursor() cursor.execute("SELECT * from cnpj order by id_cnpj desc") id_result = cursor.fetchone() id_cnpj_in = id_result[0] id_cnpj = id_cnpj_in + 1 return id_cnpj
def recordLecture(classroom, date_time=datetime.now().strftime("%Y-%m-%d %H:%M:%S")): connection = establishConnection() cursor = connection.cursor() cursor.execute("INSERT INTO lecture VALUES('%s', '%s')" % (date_time, classroom)) lecture = (classroom, date_time) cursor.close() connection.commit() connection.close() return lecture
def get_booking_history(): connection = psycopg2.connect( database="postgres", user="******", password="******", host="postgres.cviulopflptv.us-east-1.rds.amazonaws.com", port='5432') b_history = connection.cursor() new_dict = {} emailid = request.args['user'] print(emailid) userdet_query = "select ud.firstname, ud.lastname from userdetails ud where ud.emailid='{}'".format( str(emailid)) if emailid != '': b_history.execute(userdet_query) user_det = b_history.fetchall() for res in user_det: firstname = res[0] lastname = res[1] book_history_query = "select tk.ticketid, ct.sourcecityname, d.destname, d.destprov, s.journeydate, s.starttime, tk.seatsbooked, tk.payment, ud.firstname, ud.lastname from tickets tk, schedule s, cities ct, destination d, userdetails ud where tk.emailid=ud.emailid and tk.scheduleid=s.scheduleid and tk.destid=d.destid and s.sourcecityid=ct.sourcecityid and tk.emailid='{}' order by s.journeydate desc".format( str(emailid)) b_history.execute(book_history_query) book_history_res = b_history.fetchall() all_book_history = [] for res in book_history_res: bk_hist_list = [] bk_hist_list.append(str(res[0])) bk_hist_list.append(str(res[1])) bk_hist_list.append(str(res[2])) bk_hist_list.append(str(res[3])) bk_hist_list.append(str(res[4])) bk_hist_list.append(str(res[5])) bk_hist_list.append(str(res[6])) bk_hist_list.append(str(res[7])) bk_hist_list.append(str(res[8])) bk_hist_list.append(str(res[9])) all_book_history.append(bk_hist_list) new_dict['emailid'] = emailid new_dict['firstname'] = firstname new_dict['lastname'] = lastname new_dict['all_book_history'] = all_book_history return new_dict else: return new_dict
def connect(db_host, db_name, db_user, db_pwd): for x in range(5): print("Attempting to connect...") try: connection = mysql.connector.connect(host=db_host, database=db_name, user=db_user, password=db_pwd) if connection.is_connected(): db_Info = connection.get_server_info() print("Connected to MySQL Server version ", db_Info) cursor = connection.cursor() cursor.execute("select database();") record = cursor.fetchone() print("Your connected to database: ", record) return connection except Error as e: print("Error while connecting to MySQL", e) time.sleep(5)
def confirm_signup(): user = '' if session.get('email'): user = session['email'] print("into signup") otp = request.form['otp'] device = 'web' if 'device' in request.args: device = request.args['device'] data = {'email': user, 'otp': otp} response = requests.post("http://3.80.19.25:3000/confirmsignup", json=data) print(response.json()) if device != 'mobile': if response.json() == 'SUCCESS': connection = psycopg2.connect( database="postgres", user="******", password="******", host="postgres.cviulopflptv.us-east-1.rds.amazonaws.com", port='5432') schd = connection.cursor() name = session['user_temp_name'] phone = session['phonenumber_temp'] schd.execute( "INSERT INTO userdetails (username,firstname,lastname,sex,emailid, phone, active_status,usertype) VALUES(%s,%s,%s,%s,%s,%s,%s,%s);", [ name, name, name, 'm/f', session['email'], phone, True, "user" ]) connection.commit() session['token_id'] = user session['email'] = '' return render_template('home.html', user=user) else: session['email'] = '' return render_template('home.html', user=user) else: if response.json() == 'SUCCESS': return True else: return False
NUM_SEQ_UNICO = int(row['NUM_SEQ_UNICO']) #print(type(NUM_SEQ_UNICO)) DSC_IMG_URL = row['DSC_IMG_URL'] #print(type(DSC_IMG_URL)) NUM_CPF = row['NUM_CPF'] if NUM_CPF == None or NUM_CPF == '': NUM_CPF = None else: NUM_CPF = int(row['NUM_CPF']) #print(type(NUM_CPF),NUM_CPF,'cpf') except: print('Erro de tipo de dados') NUM_CODIGO = str(NUM_CODIGO) #print(type(NUM_CODIGO)) cursor = connection.cursor() # cria um cursor cursor.execute("SELECT NUM_CODIGO from gtin_sefaz where NUM_CODIGO =" + NUM_CODIGO) # consulta sql result = cursor.fetchone() # busca o resultado da consulta NUM_CODIGO = int(NUM_CODIGO) try: if result == None: cursor = connection.cursor() cursor.execute( "SELECT seq_id from gtin_sefaz order by seq_id desc") id_result = cursor.fetchone() seq_id_in = id_result[0] SEQ_ID = seq_id_in + 1
def book_ticket(): connection = psycopg2.connect( database="postgres", user="******", password="******", host="postgres.cviulopflptv.us-east-1.rds.amazonaws.com", port='5432') schd = connection.cursor() cardNumber = request.form['cardNumber'] expiryMonth = request.form['expiryMonth'] expiryYear = request.form['expiryYear'] cvCode = request.form['cvCode'] number_of_travellers = request.form['passengers'] scheduleid = request.form['scheduleid'] username = request.form['user'] bus_dict = {} active_status = True if username != '': if cardNumber == '1111111111111111' and expiryYear > '19' and expiryMonth > '03': print("into if") schd.execute( "SELECT s.sourcecityid, s.destid, s.journeydate, s.starttime, s.busid, s.scheduleid,s.price FROM schedule s WHERE scheduleid = %s", [scheduleid]) results = schd.fetchall() for res in results: bus_dict = {} source_city_id = res[0] destination_id = res[1] journeydate = res[2] starttime = res[3] busid = res[4] price = res[6] bus_dict['source_city_id'] = source_city_id bus_dict['destination_id'] = destination_id bus_dict['journeydate'] = journeydate bus_dict['starttime'] = starttime bus_dict['busid'] = busid bus_dict['scheduleid'] = res[5] bus_dict['price'] = int(res[6]) * int(number_of_travellers) bus_dict['username'] = username bus_dict['passengers'] = number_of_travellers print("Source City: " + source_city_id) print("Destination Name: " + destination_id) print("Bus start time: " + str(starttime)) print("Bus id: " + str(busid)) booking_time = int(time.time()) schd.execute( "INSERT INTO tickets (emailid,destid,seatsbooked,active_status,scheduleid,payment,booking_time) VALUES (%s,%s,%s,%s,%s,%s,%s);", [ username, destination_id, int(number_of_travellers), active_status, int(scheduleid), bus_dict['price'], booking_time ]) connection.commit() schd.execute( "UPDATE schedule SET seatsavailable = seatsavailable - %s WHERE scheduleid = %s", [int(number_of_travellers), int(scheduleid)]) connection.commit() ticket_query = "select tk.ticketid, ud.firstname, ud.lastname, ud.emailid, ct.sourcecityname, d.destname, d.destprov, s.journeydate, s.starttime, tk.seatsbooked, tk.payment from userdetails ud, tickets tk, destination d, schedule s, cities ct where ud.emailid=tk.emailid and tk.destid=d.destid and tk.scheduleid=s.scheduleid and s.sourcecityid=ct.sourcecityid and tk.emailid='{}' order by booking_time desc Limit 1".format( username) schd.execute(ticket_query) ticket_results = schd.fetchall() for res in ticket_results: pdf_ticketid = str(res[0]) pdf_name = str(res[1]) pdf_email = str(res[3]) pdf_source = str(res[4]) pdf_dest = str(res[5]) + ", " + str(res[6]) pdf_departure_date = str(res[7]) pdf_departure_time = str(res[8]) pdf_seatsbooked = str(res[9]) pdf_payment = "$" + str(res[10]) pdfName = username.split( '@')[0] + '_' + pdf_ticketid + '_ticket.pdf' pdfTitle = "Hi " + pdf_name + "! Here's your ticket." bookingReference = 'Ticket ID#: ' + pdf_ticketid name = 'Email: ' + pdf_email source = 'From: ' + pdf_source destination = 'To: ' + pdf_dest departureDay = 'Departure Day: ' + pdf_departure_date departureTime = 'Departure Time: ' + pdf_departure_time numberOfTravellers = 'Number Of Travellers: ' + pdf_seatsbooked amount_paid = 'Amount paid: ' + pdf_payment contactUs = 'Customer Support: [email protected]' pdf = canvas.Canvas(pdfName) pdf.setTitle(pdfTitle) pdf.setFont("Helvetica-Bold", 28) pdf.drawCentredString(280, 800, 'Travel Ticket') pdf.line(170, 790, 400, 790) pdf.setFont("Courier", 14) pdf.drawString(50, 740, pdfTitle) pdf.drawString(50, 720, bookingReference) pdf.drawString(50, 700, name) pdf.drawString(50, 680, source) pdf.drawString(50, 660, destination) pdf.drawString(50, 640, departureDay) pdf.drawString(50, 620, departureTime) pdf.drawString(50, 600, numberOfTravellers) pdf.drawString(50, 580, amount_paid) pdf.setFont("Courier-Bold", 12) pdf.drawString(50, 100, contactUs) pdf.save() pdf_name = pdfName msg = EmailMessage() from_ = "*****@*****.**" pwd = "canatour@123" msg['Subject'] = "Booking Successful - Canatour" msg['From'] = from_ msg['To'] = username msg.set_content( 'Please find attached your travel ticket. Hope you enjoy the journey.' ) with open(pdf_name, 'rb') as f: file_data = f.read() file_name = f.name msg.add_attachment(file_data, maintype='application', subtype='octet-stream', filename=file_name) with smtplib.SMTP_SSL('smtp.gmail.com') as smtp: smtp.login(from_, pwd) smtp.send_message(msg) return bus_dict else: print("into else") return bus_dict else: return "No user logged in"
connection = connection.conexao() PATH = 'GS1BRcopia.xml' tree = ET.parse(PATH) root = tree.getroot() id_segmento = 0 id_familia = 0 id_classe = 0 id_bloco = 0 for child in root.findall("segment"): cursor = connection.cursor() cursor.execute("SELECT * from gpc_segmento order by id_segmento desc") id_result = cursor.fetchone() id_segmento_in = id_result[0] id_segmento = id_segmento_in + 1 gpc_segmento_id_segmento = id_segmento '''Mostra todos os segmentos''' if 'code' in child.keys(): segmento = [] s = child.items() segmento.append(s) cod_segmento = segmento[0][0][1] desc_segmento = segmento[0][1][1] print('segmento: ',cod_segmento,desc_segmento) '''INSERE SEGMENTO - tabela gpc_segmento'''
import os import sys import site import timelib import settings # Set this to the path to your python library sys.path.append('/path/to/django/lib') import connection, transaction cursor = connection.cursor() def convert_chars(text): conv_table = [ ["\xc3\x84\xc3\xba", "“"], ["\xc3\x84\xc3\xb9", "”"], ["\xc3\x84\xc3\xae", "—"], ["\xe2\x80\x9a\xc3\x84\xc3\xb4", "’"], ["\xe2\x88\x9a\xc2\xba", "ü"], ["\xe2\x80\x9a\xc3\x84\xc2\xa2", "•"], ["\xe2\x80\x9a\xc3\x84\xc2\xb6", "…"], ["\xe2\x80\x9a\xc3\x84\xe2\x89\xa4","′"], ["\xe2\x88\x9a\xc2\xa9","é"], ["\xe2\x88\x9a\xc2\xb0","á"], ["\xe2\x80\x9a\xc3\x84\xc3\xb2","‘"], ["\xe2\x80\x9a\xc3\x84\xe2\x89\xa5","″"], ["\xe2\x88\x9a\xe2\x89\xa0","í"], ["\xe2\x80\x9a\xc3\x84\xc3\xb2","‘"], ["\xe2\x88\x9a\xc3\x9f","ç"], ["\xe2\x88\x9a\xe2\x89\xa5","ó"],
def neuralcalculations(connection, ticker, date): date_parts = date.split("-") # Explode Date year = date_parts[0] # Grab The Date month = date_parts[1] # Grab The Date day = date_parts[2] # Grab The Date Today = datetime.today().strftime('%Y-%m-%d') # Grab Todays Date Entry = random.randint(2000000000, 60000000000) DayOfWeek = day # Day of Week TickerSymbol = ticker #Ticker Symbol day_name = datetime.strptime( (date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date trading_day = 0 if (day_name == "Monday"): trading_day = 0.2 elif (day_name == "Tuesday"): trading_day = 0.4 elif (day_name == "Wednesday"): trading_day = 0.6 elif (day_name == "Thursday"): trading_day = 0.8 elif (day_name == "Friday"): trading_day = 1 else: print("Fatal Error: Invalid Date Input Received" ) # Kill Program, Request Not Allowed sys.exit("Fatal Error") if ((month == "3") or (month == "4") or (month == "5")): trading_season = 0.25 elif ((month == "6") or (month == "7") or (month == "8")): trading_season = 0.50 elif ((month == "9") or (month == "10") or (month == "11")): trading_season = 0.75 elif ((month == "12") or (month == "1") or (month == "2")): trading_season = 1.0 else: print("Fatal Error: Could Not Calculate " + day_name + " Accurately") # Kill Program, Something Went Very Wrong sys.exit("Fatal Error") # Calculate Moving Average 200 Trading Days * From Date * # *************************************************************************** with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s ORDER BY Date DESC" cursor.execute(sql, (ticker)) connection.commit() MovingAvgBig = cursor.fetchall() days_list = rangedatelist( date, 286) # Get List of Backdates In This Range (Including Weekends) MAB_Total = 0.0 MAB_Averg = 0.0 MAB_Count = 0 for day in MovingAvgBig: MAB_Closing = day['Closing_Price'] MAB_Date = day['Date'] MAB_Date = datetime.strptime(MAB_Date, '%Y-%m-%d') # Convert to Datetime if ((MAB_Date in days_list) and (MAB_Count < 200)): MAB_Count = MAB_Count + 1 MAB_Total = MAB_Total + float(MAB_Closing) print("Counter Check: ", MAB_Count) MAB_Averg = (MAB_Total / MAB_Count) MAB_Averg = round(MAB_Averg, 2) print("200 Day Moving Average: ", MAB_Averg) update.large_moving_avg(connection, MAB_Averg, ticker) # Calculate Moving Average 200 Trading Days * From Date * # *************************************************************************** # Calculate Moving Average 5 Trading Days * From Date * # *************************************************************************** with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s ORDER BY Date DESC" cursor.execute(sql, (ticker)) connection.commit() MovingAvgLittle = cursor.fetchall() drop_today = datetime.strptime(date, '%Y-%m-%d') back_today = drop_today - timedelta(days=1) back_today = back_today.strftime('%Y-%m-%d') days_list = rangedatelist( back_today, 7) # Get List of Backdates In This Range (Including Weekends) MAL_Total = 0.0 MAL_Averg = 0.0 MAL_Count = 0 for day in MovingAvgLittle: MAL_Closing = day['Closing_Price'] MAL_Date = day['Date'] MAL_Date = datetime.strptime(MAL_Date, '%Y-%m-%d') # Convert to Datetime if ((MAL_Date in days_list) and (MAL_Count < 5)): MAL_Count = MAL_Count + 1 MAL_Total = MAL_Total + float(MAL_Closing) print("Counter Check: ", MAL_Count) MAL_Averg = (MAL_Total / MAL_Count) MAL_Averg = round(MAL_Averg, 2) print("5 Day Moving Average: ", MAL_Averg) update.small_moving_avg(connection, MAL_Averg, ticker) # Calculate Moving Average 5 Trading Days * From Date * # *************************************************************************** # Calculate Volume Average 90 Trading Days * From Date * # *************************************************************************** with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s ORDER BY Date DESC" cursor.execute(sql, (ticker)) connection.commit() MovingAvgVolume = cursor.fetchall() days_list = rangedatelist( date, 130) # Get List of Backdates In This Range (Including Weekends) MAV_Total = 0.0 MAV_Averg = 0.0 MAV_Count = 0 for day in MovingAvgVolume: MAV_Volume = day['Volume'] MAV_Date = day['Date'] MAV_Date = datetime.strptime(MAV_Date, '%Y-%m-%d') # Convert to Datetime if ((MAV_Date in days_list) and (MAV_Count < 90)): MAV_Count = MAV_Count + 1 MAV_Total = MAV_Total + float(MAV_Volume) print("Counter Check: ", MAV_Count) MAV_Averg = (MAV_Total / MAV_Count) MAV_Averg = round(MAV_Averg) print("Average Volume 90 Days: ", MAV_Averg) update.avg_volume_long(connection, MAV_Averg, ticker) # Calculate Volume Average 90 Trading Days * From Date * # *************************************************************************** # Calculate # of Tweets From Today ******************************************* DailyTweets_Count = 0 with connection.cursor() as cursor: sql = "SELECT * FROM `StockTweets` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, date)) connection.commit() today_tweets = cursor.fetchall() DailyTweets_Count = cursor.rowcount update.today_tweets(connection, DailyTweets_Count, ticker) # Calculate # Of Tweets From Today ******************************************* # Calculate Sentiment For The Day # *************************************************************************** # SHORT TERM SENTIMENT ANALYSIS # -------------------------------------------- # Short Term Sentiment Scale # 0 Extremelly Negative Sentiment <-40 # 0.1 -40 - -30 # 0.2 -30 - -20 # 0.3 -20 - -10 # 0.4 -10 - -0 # 0.5 Neutral 0 # 0.6 0 - 10 # 0.7 10 - 20 # 0.8 20 - 30 # 0.9 30 - 40 # 1 Significant Positive Sentiment >50 # -------------------------------------------- with connection.cursor() as cursor: sql = "SELECT * FROM `StockTweets` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, date)) connection.commit() DaySentiment = cursor.fetchall() Daily_Sentiment = 0 Daily_Count = 0.001 for tweet in DaySentiment: Temp_Likes = 0 Temp_Likes = float(tweet["Total_Likes"]) Temp_Sentiment = 0 Temp_Sentiment = float(tweet["Tweet_Sentiment"]) Daily_Count = Daily_Count + 1 while (Temp_Likes != 0): if ((Temp_Sentiment <= 90) and (Temp_Sentiment >= -90)): # Prevent Overbounds Temp_Sentiment = (Temp_Sentiment * 0.15) + Temp_Sentiment Temp_Likes = Temp_Likes - 1 Daily_Sentiment = Daily_Sentiment + Temp_Sentiment SentimentNormalized = (Daily_Sentiment / Daily_Count ) # Calculate One Day Sentiment SentimentNormalized = round(SentimentNormalized) if (SentimentNormalized < -40): SentimentNormalized = float(0.0) elif ((SentimentNormalized >= -40) and (SentimentNormalized < -30)): SentimentNormalized = float(0.1) elif ((SentimentNormalized >= -30) and (SentimentNormalized < -20)): SentimentNormalized = float(0.2) elif ((SentimentNormalized >= -20) and (SentimentNormalized < -10)): SentimentNormalized = float(0.3) elif ((SentimentNormalized >= -10) and (SentimentNormalized < 0)): SentimentNormalized = float(0.4) elif (SentimentNormalized == 0): SentimentNormalized = float(0.5) elif ((SentimentNormalized > 0) and (SentimentNormalized < 10)): SentimentNormalized = float(0.6) elif ((SentimentNormalized >= 10) and (SentimentNormalized < 20)): SentimentNormalized = float(0.7) elif ((SentimentNormalized >= 20) and (SentimentNormalized < 30)): SentimentNormalized = float(0.8) elif ((SentimentNormalized >= 30) and (SentimentNormalized <= 40)): SentimentNormalized = float(0.9) elif (SentimentNormalized > 40): SentimentNormalized = float(1.0) else: print("Fatal Error: Could Not Calculate Sentiment Accurately") sys.exit("Fatal Error") print("Sentiment For " + day_name + "", SentimentNormalized) # Calculate Sentiment For The Day ******************************************* # Calculate Sentiment + Volume Average 30 Actual Days * From Date * # *************************************************************************** # LONG TERM SENTIMENT ANALYSIS # -------------------------------------------- # Calculate and Normalize 30 Day Sentiment Average # Long Term Sentiment Scale # 0 General Negative Sentiment <95 # 0.1 96 # 0.2 97 # 0.3 98 # 0.4 99 # 0.5 Average 100 # 0.6 101 # 0.7 102 # 0.8 103 # 0.9 104 # 1 General Positive Sentiment >105 # -------------------------------------------- days_list = rangedatelist( date, 29) # Get List of Backdates In This Range (Including Weekends) SAB_Total = 0.0 SAB_Averg = 0.0 SAB_Count = 0 TVA_Count = 0 # Daily Tweet Volume Count TVA_Days = 0.001 # Daily Tweet Volume Total Days TVA_Total = 0 # Tweet Volume Average Total for day in days_list: # For Each Day In Range, Calculate Sentiment Analysis TVA_Days = TVA_Days + 1 SAB_Count = SAB_Count + 1 day = day.strftime('%Y-%m-%d') with connection.cursor() as cursor: sql = "SELECT * FROM `StockTweets` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, day)) connection.commit() one_day = cursor.fetchall() Daily_Sentiment = 0 Daily_Count = 0.001 for tweet in one_day: TVA_Count = TVA_Count + 1 Temp_Likes = 0 Temp_Likes = float(tweet["Total_Likes"]) Temp_Sentiment = 0 Temp_Sentiment = float(tweet["Tweet_Sentiment"]) Daily_Count = Daily_Count + 1 while (Temp_Likes != 0): if ((Temp_Sentiment <= 90) and (Temp_Sentiment >= -90)): # Prevent Overbounds Temp_Sentiment = (Temp_Sentiment * 0.15) + Temp_Sentiment Temp_Likes = Temp_Likes - 1 Daily_Sentiment = Daily_Sentiment + Temp_Sentiment if (Daily_Sentiment == 0): TVA_Days = TVA_Days - 1 # Fix Averages If No Tweets On That Day Tweet_Sentiment = (Daily_Sentiment / Daily_Count ) # Calculate One Day Sentiment SAB_Total = SAB_Total + Tweet_Sentiment # Add One Day Sentiment To SAB Total TVA_Total = (TVA_Count / TVA_Days) SAB_Averg = (SAB_Total / SAB_Count) SAB_Averg = round(SAB_Averg) SAB_Averg = SAB_Averg + 100 print("30 Day Sentiment", SAB_Averg) update.avg_sentiment_short(connection, SAB_Averg, ticker) print("30 Day Avg Tweets", TVA_Total) update.avg_tweets_short(connection, TVA_Total, ticker) if (SAB_Averg <= 95): SentimentLongTerm = float(0.0) elif (SAB_Averg == 96): SentimentLongTerm = float(0.1) elif (SAB_Averg == 97): SentimentLongTerm = float(0.2) elif (SAB_Averg == 98): SentimentLongTerm = float(0.3) elif (SAB_Averg == 99): SentimentLongTerm = float(0.4) elif (SAB_Averg == 100): SentimentLongTerm = float(0.5) elif (SAB_Averg == 101): SentimentLongTerm = float(0.6) elif (SAB_Averg == 102): SentimentLongTerm = float(0.7) elif (SAB_Averg == 103): SentimentLongTerm = float(0.8) elif (SAB_Averg == 104): SentimentLongTerm = float(0.9) elif (SAB_Averg >= 105): SentimentLongTerm = float(1.0) else: print("Fatal Error: Could Not Calculate Sentiment Accurately") sys.exit("Fatal Error") # Calculate Sentiment + Volume Average 30 Actual Days * From Date * # *************************************************************************** # Grab Pricing For Two Trading Days Requested and Following # *************************************************************************** if ((day_name == "Saturday") or (day_name == "Sunday")): print("Error: You cannot request a neural programming for a weekend") else: safety_check = 0 with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, date)) connection.commit() prices_on_date = cursor.fetchall() safety_check = cursor.rowcount if (safety_check == 0): print("Error: No Date Record Exists For The Date Requested") sys.exit("Fatal Error #202") # Kill Program, Bad User Input else: for pricing in prices_on_date: ThisDay_Volume = pricing["Volume"] ThisDay_Closing = pricing["Closing_Price"] ThisDay_HighPr = pricing["High_Price"] ThisDay_LowPr = pricing["Low_Price"] print("Lookup Volume: ", ThisDay_Volume) print("Lookup High: ", ThisDay_HighPr) print("Lookup Low: ", ThisDay_LowPr) print("Lookup Close: ", ThisDay_Closing) start_date = datetime.strptime(date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-1) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if (no_weekends == "Saturday"): start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-2) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if ((no_weekends == "Saturday") or (no_weekends == "Sunday")): print("Fatal Error: Could Not Calculate Date Accurately" ) # Kill Program, Something Went Very Wrong sys.exit("Fatal Error #203") elif (no_weekends == "Sunday"): start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-1) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if ((no_weekends == "Saturday") or (no_weekends == "Sunday")): print("Fatal Error: Could Not Calculate Date Accurately" ) # Kill Program, Something Went Very Wrong sys.exit("Fatal Error #204") print("Next Day: ", no_weekends) print("Next Day: ", forward_date) with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, forward_date)) connection.commit() prices_on_forward_date = cursor.fetchall() safety_check = cursor.rowcount NextDay_Volume = 0.01 NextDay_HighPr = 0.01 NextDay_LowPr = 0.01 NextDay_Closing = 0.01 for pricing in prices_on_forward_date: NextDay_Volume = pricing["Volume"] NextDay_Closing = pricing["Closing_Price"] NextDay_HighPr = pricing["High_Price"] NextDay_LowPr = pricing["Low_Price"] if (NextDay_Closing == 0.01): # For some reason no price record exists for this date, due to Market Holiday Possibly, Push Forward Again start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-1) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if (no_weekends == "Saturday"): start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-2) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if ((no_weekends == "Saturday") or (no_weekends == "Sunday")): print("Fatal Error: Could Not Calculate Date Accurately" ) # Kill Program, Something Went Very Wrong sys.exit("Fatal Error #205") elif (no_weekends == "Sunday"): start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-1) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if ((no_weekends == "Saturday") or (no_weekends == "Sunday")): print("Fatal Error: Could Not Calculate Date Accurately" ) # Kill Program, Something Went Very Wrong sys.exit("Fatal Error #206") print("New Forward Date Selected: ", forward_date) with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, forward_date)) connection.commit() prices_on_forward_date = cursor.fetchall() safety_check = cursor.rowcount for pricing in prices_on_forward_date: NextDay_Volume = pricing["Volume"] NextDay_Closing = pricing["Closing_Price"] NextDay_HighPr = pricing["High_Price"] NextDay_LowPr = pricing["Low_Price"] if (NextDay_Closing == 0.01): print("Fatal Error: Forward Date Price Data Not Available") sys.exit("Fatal Error #207") print("Next Day-----------------------", no_weekends) print("Lookup Volume: ", NextDay_Volume) print("Lookup High: ", NextDay_HighPr) print("Lookup Low: ", NextDay_LowPr) print("Lookup Close: ", NextDay_Closing) # Grab Pricing For Two Trading Days Requested and Following # *************************************************************************** # TRADING VOLUME ANALYSIS # *************************************************************************** # -------------------------------------------- # Calculate and Normalize Volume Trading # Trading Volume Scale # 0 Unusually Low Volume 0,1 # 0.1 2,3 # 0.2 4,5 # 0.3 6,7 # 0.4 8,9 # 0.5 Average 10 # 0.6 11,12 # 0.7 13,14 # 0.8 15,16 # 0.9 17,18 # 1 Unusually High Volume 19,20 # -------------------------------------------- ThisDay_Volume = int(ThisDay_Volume) MAV_Averg = int(MAV_Averg) if (ThisDay_Volume > MAV_Averg): Sub_Process = (ThisDay_Volume / MAV_Averg) Sub_Process = 10 * Sub_Process VolumeNormalized = Sub_Process if (Sub_Process > 20): # Maximum Volume Increase Is Double Sub_Process = 20 VolumeNormalized = Sub_Process elif (Sub_Process < 10): # This shouldn't ever happen Sub_Process = 10 VolumeNormalized = Sub_Process elif (ThisDay_Volume < MAV_Averg): Sub_Process = (ThisDay_Volume / MAV_Averg) Sub_Process = 10 * Sub_Process VolumeNormalized = Sub_Process if (Sub_Process < 0): # No Negatives Allowed Sub_Process = 0 VolumeNormalized = Sub_Process elif (Sub_Process > 10): # This shouldn't ever happen Sub_Process = 10 VolumeNormalized = Sub_Process else: VolumeNormalized = 10 VolumeNormalized = round(VolumeNormalized) if ((VolumeNormalized == 0) or (VolumeNormalized == 1)): VolumeNormalized = float(0.0) elif ((VolumeNormalized == 2) or (VolumeNormalized == 3)): VolumeNormalized = float(0.1) elif ((VolumeNormalized == 4) or (VolumeNormalized == 5)): VolumeNormalized = float(0.2) elif ((VolumeNormalized == 6) or (VolumeNormalized == 7)): VolumeNormalized = float(0.3) elif ((VolumeNormalized == 8) or (VolumeNormalized == 9)): VolumeNormalized = float(0.4) elif (VolumeNormalized == 10): VolumeNormalized = float(0.5) elif ((VolumeNormalized == 11) or (VolumeNormalized == 12)): VolumeNormalized = float(0.6) elif ((VolumeNormalized == 13) or (VolumeNormalized == 14)): VolumeNormalized = float(0.7) elif ((VolumeNormalized == 15) or (VolumeNormalized == 16)): VolumeNormalized = float(0.8) elif ((VolumeNormalized == 17) or (VolumeNormalized == 18)): VolumeNormalized = float(0.9) elif ((VolumeNormalized == 19) or (VolumeNormalized == 20)): VolumeNormalized = float(1.0) else: print("Fatal Error: Could Not Calculate Volume Accurately") sys.exit("Fatal Error") # *************************************************************************** # Calculate and Normalize Daily Sentiment # *************************************************************************** # SHORT TERM TWEET VOLUME # -------------------------------------------- # Calculate and Normalize Tweet Volume # Tweet Volume Scale # 0 Unusually Low Volume 0,1 # 0.1 2,3 # 0.2 4,5 # 0.3 6,7 # 0.4 8,9 # 0.5 Average 10 # 0.6 11,12 # 0.7 13,14 # 0.8 15,16 # 0.9 17,18 # 1 Unusually High Volume 19,20 # -------------------------------------------- DailyTweets_Count = int(DailyTweets_Count) TVA_Total = int(TVA_Total) if (DailyTweets_Count > MAV_Averg): Sub_Process = (DailyTweets_Count / TVA_Total) Sub_Process = 10 * Sub_Process TweetsNormalized = Sub_Process if (Sub_Process > 20): # Maximum Volume Increase Is Double Sub_Process = 20 TweetsNormalized = Sub_Process elif (Sub_Process < 10): # This shouldn't ever happen Sub_Process = 10 TweetsNormalized = Sub_Process elif (DailyTweets_Count < TVA_Total): Sub_Process = (DailyTweets_Count / TVA_Total) Sub_Process = 10 * Sub_Process TweetsNormalized = Sub_Process if (Sub_Process < 0): # No Negatives Allowed Sub_Process = 0 TweetsNormalized = Sub_Process elif (Sub_Process > 10): # This shouldn't ever happen Sub_Process = 10 TweetsNormalized = Sub_Process else: TweetsNormalized = 10 TweetsNormalized = round(TweetsNormalized) if ((TweetsNormalized == 0) or (TweetsNormalized == 1)): TweetsNormalized = float(0.0) elif ((TweetsNormalized == 2) or (TweetsNormalized == 3)): TweetsNormalized = float(0.1) elif ((TweetsNormalized == 4) or (TweetsNormalized == 5)): TweetsNormalized = float(0.2) elif ((TweetsNormalized == 6) or (TweetsNormalized == 7)): TweetsNormalized = float(0.3) elif ((TweetsNormalized == 8) or (TweetsNormalized == 9)): TweetsNormalized = float(0.4) elif (TweetsNormalized == 10): TweetsNormalized = float(0.5) elif ((TweetsNormalized == 11) or (TweetsNormalized == 12)): TweetsNormalized = float(0.6) elif ((TweetsNormalized == 13) or (TweetsNormalized == 14)): TweetsNormalized = float(0.7) elif ((TweetsNormalized == 15) or (TweetsNormalized == 16)): TweetsNormalized = float(0.8) elif ((TweetsNormalized == 17) or (TweetsNormalized == 18)): TweetsNormalized = float(0.9) elif ((TweetsNormalized == 19) or (TweetsNormalized == 20)): TweetsNormalized = float(1.0) else: print("Fatal Error: Could Not Calculate Volume Accurately") sys.exit("Fatal Error") # Calculate and Normalize Volume Trading # *************************************************************************** # Calculate Results From The Next Day # *************************************************************************** # 0.0 Stock price decreased # 0.2 Stock price decreased by <1% # 0.3 # 0.4 # ——————————————————— # 0.4 # 0.5 # 0.6 Stock price increased by <1% # 1.0 Stock price increased ThisDay_Closing = float(ThisDay_Closing) NextDay_Closing = float(NextDay_Closing) if (ThisDay_Closing <= NextDay_Closing): # Stock price increased change_percent = (( (NextDay_Closing - ThisDay_Closing) / ThisDay_Closing) * 100) if (change_percent > 1.0): Output1 = 1.0 Output2 = 1.0 else: Output1 = 0.7 Output2 = 0.7 if (ThisDay_Closing > NextDay_Closing): # Stock price decreased change_percent = (( (ThisDay_Closing - NextDay_Closing) / ThisDay_Closing) * 100) if (change_percent > 1.0): Output1 = 0.0 Output2 = 0.0 else: Output1 = 0.3 Output2 = 0.3 if (ThisDay_Closing <= NextDay_Closing): # Stock price increased change_percent = (( (NextDay_Closing - ThisDay_Closing) / ThisDay_Closing) * 100) if (change_percent > 2.0): Output3 = 1.0 Output4 = 1.0 else: Output3 = 0.7 Output4 = 0.7 if (ThisDay_Closing > NextDay_Closing): # Stock price decreased change_percent = (( (ThisDay_Closing - NextDay_Closing) / ThisDay_Closing) * 100) if (change_percent > 2.0): Output3 = 0.0 Output4 = 0.0 else: Output3 = 0.3 Output4 = 0.3 if (ThisDay_Closing <= NextDay_Closing): # Stock price increased Output5 = 1.0 Output6 = 1.0 if (ThisDay_Closing > NextDay_Closing): # Stock price decreased Output5 = 0.0 Output6 = 0.0 if (ThisDay_Closing > MAB_Averg): AboveBigMoving = 1 else: AboveBigMoving = 0 if (ThisDay_Closing < MAL_Averg): BelowLittleMoving = 1 else: BelowLittleMoving = 0 # Calculate Results From The Next Day # *************************************************************************** # Algorithm Results # *************************************************************************** print("\n\n") print("Neural Network Normalized Data: ") print("----------------------------------------------") print("Entry ", Entry) print("Volume Normalized: [0,20] ", VolumeNormalized) print("Tweets Normalized: [0,20] ", TweetsNormalized) print("Sentiment Analysis: [0,200] ", SentimentNormalized) print("Above Big Moving: [0,1] ", AboveBigMoving) print("Below Little Moving: [0,1] ", BelowLittleMoving) print("Trading Day: [1,5] ", trading_day) print("Trading Season: [Sp, Su, Fa, Wi] ", trading_season) print("Moving Avg Sentiment: [0,200] ", SAB_Averg) print("Results: [", Output1, ",", Output2, "]", " Up1%/Up/Down/Down1%") print("Results: [", Output3, ",", Output4, "]", " Up2%/Up/Down/Down2%") print("Results: [", Output5, ",", Output6, "]", " Up/Down") try: with connection.cursor() as cursor: sql = "SELECT * FROM `NeuralNetwork` WHERE `Date`=%s AND Ticker=%s" cursor.execute(sql, (date, ticker)) result = cursor.fetchone() if result is None: print("Success: Inserting Neural Data Set: ", ticker) with connection.cursor() as cursor: sql = "INSERT INTO `NeuralNetwork` (`Date`, `Entry`, `TradingDay`, `TradingSeason`, `VolumeNormalized`, `AboveBigMoving`, `BelowLittleMoving`, `TweetsVolumeNormalized`, `Sentiment`, `Ticker`, `Output1`, `Output2`, `Output3`, `Output4`, `Output5`, `Output6`, `Sentiment30`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute( sql, (date, Entry, trading_day, trading_season, VolumeNormalized, AboveBigMoving, BelowLittleMoving, TweetsNormalized, SentimentNormalized, ticker, Output1, Output2, Output3, Output4, Output5, Output6, SentimentLongTerm)) connection.commit() update.last_input(connection, ticker) else: print("Error: Neural Data Set Exists For The Date ", ticker, date) finally: print("::: SAVING DATA ::: DO NOT CLOSE CONNECTION :::")
def neuralcalculations(connection, ticker, date): try: network = Network.load("neuralnetwork.json") except Exception as e: print("#101 Error: Neural Network Does Not Exist") sys.exit("Fatal Error") # Kill Program, Bad User Input date_parts = date.split("-") # Explode Date year = date_parts[0] # Grab The Date month = date_parts[1] # Grab The Date day = date_parts[2] # Grab The Date Today = datetime.today().strftime('%Y-%m-%d') # Grab Todays Date Entry = random.randint(2000000000, 60000000000) DayOfWeek = day # Day of Week TickerSymbol = ticker # Ticker Symbol day_name = datetime.strptime( (date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date trading_day = 0 if (day_name == "Monday"): trading_day = 0.2 elif (day_name == "Tuesday"): trading_day = 0.4 elif (day_name == "Wednesday"): trading_day = 0.6 elif (day_name == "Thursday"): trading_day = 0.8 elif (day_name == "Friday"): trading_day = 1 else: print("#102 Fatal Error: Invalid Date Input Received" ) # Kill Program, Request Not Allowed sys.exit("Fatal Error") print(month) if ((month == "03") or (month == "04") or (month == "05")): trading_season = 0.25 elif ((month == "06") or (month == "07") or (month == "08")): trading_season = 0.50 elif ((month == "09") or (month == "10") or (month == "11")): trading_season = 0.75 elif ((month == "12") or (month == "01") or (month == "02")): trading_season = 1.0 else: print("#103 Fatal Error: Could Not Calculate " + day_name + " Accurately") # Kill Program, Something Went Very Wrong sys.exit("Fatal Error") # Run this from Price records which will be updated LIVE # Calculate Live Stock Information ******************************************* with connection.cursor() as cursor: sql = "SELECT * FROM `StockPrices` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, date)) connection.commit() live_data = cursor.fetchall() for live in live_data: stock_price_live = live["Price"] stock_price_incr = live["Increase"] stock_price_volm = live["Volume"] MAV_Averg = live["AvgVolumeNinety"] MAL_Averg = live["ShortMovingAvg"] MAB_Averg = live["LongMovingAvg"] # Calculate Live Stock Information ******************************************* # Calculate # of Tweets From Today ******************************************* with connection.cursor() as cursor: sql = "SELECT * FROM `StockTweets` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, date)) connection.commit() today_tweets = cursor.fetchall() DailyTweets_Count = cursor.rowcount # Calculate # Of Tweets From Today ******************************************* # Calculate Sentiment For The Day # *************************************************************************** # SHORT TERM SENTIMENT ANALYSIS # -------------------------------------------- # Short Term Sentiment Scale # 0 Extremelly Negative Sentiment <-40 # 0.1 -40 - -30 # 0.2 -30 - -20 # 0.3 -20 - -10 # 0.4 -10 - -0 # 0.5 Neutral 0 # 0.6 0 - 10 # 0.7 10 - 20 # 0.8 20 - 30 # 0.9 30 - 40 # 1 Significant Positive Sentiment >50 # -------------------------------------------- with connection.cursor() as cursor: sql = "SELECT * FROM `StockTweets` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, date)) connection.commit() DaySentiment = cursor.fetchall() Daily_Sentiment = 0 Daily_Count = 0.001 for tweet in DaySentiment: Temp_Likes = 0 Temp_Likes = float(tweet["Total_Likes"]) Temp_Sentiment = 0 Temp_Sentiment = float(tweet["Tweet_Sentiment"]) Daily_Count = Daily_Count + 1 while (Temp_Likes != 0): if ((Temp_Sentiment <= 90) and (Temp_Sentiment >= -90)): # Prevent Overbounds Temp_Sentiment = (Temp_Sentiment * 0.15) + Temp_Sentiment Temp_Likes = Temp_Likes - 1 Daily_Sentiment = Daily_Sentiment + Temp_Sentiment SentimentNormalized = (Daily_Sentiment / Daily_Count ) # Calculate One Day Sentiment SentimentNormalized = round(SentimentNormalized) if (SentimentNormalized < -40): SentimentNormalized = float(0.0) elif ((SentimentNormalized >= -40) and (SentimentNormalized < -30)): SentimentNormalized = float(0.1) elif ((SentimentNormalized >= -30) and (SentimentNormalized < -20)): SentimentNormalized = float(0.2) elif ((SentimentNormalized >= -20) and (SentimentNormalized < -10)): SentimentNormalized = float(0.3) elif ((SentimentNormalized >= -10) and (SentimentNormalized < 0)): SentimentNormalized = float(0.4) elif (SentimentNormalized == 0): SentimentNormalized = float(0.5) elif ((SentimentNormalized > 0) and (SentimentNormalized < 10)): SentimentNormalized = float(0.6) elif ((SentimentNormalized >= 10) and (SentimentNormalized < 20)): SentimentNormalized = float(0.7) elif ((SentimentNormalized >= 20) and (SentimentNormalized < 30)): SentimentNormalized = float(0.8) elif ((SentimentNormalized >= 30) and (SentimentNormalized <= 40)): SentimentNormalized = float(0.9) elif (SentimentNormalized > 40): SentimentNormalized = float(1.0) else: print("#104 Fatal Error: Could Not Calculate Sentiment Accurately") sys.exit("Fatal Error") print("Sentiment For " + day_name + "", SentimentNormalized) # Calculate Sentiment For The Day ******************************************* # Calculate Prediction Date ************************************************* start_date = datetime.strptime(date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-1) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if (no_weekends == "Saturday"): start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-2) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if ((no_weekends == "Saturday") or (no_weekends == "Sunday")): print("#105 Fatal Error: Could Not Calculate Date Accurately" ) # Kill Program, Something Went Very Wrong sys.exit("Fatal Error") elif (no_weekends == "Sunday"): start_date = datetime.strptime(forward_date, '%Y-%m-%d') forwardgo = start_date - timedelta(days=-1) forward_date = forwardgo.strftime('%Y-%m-%d') no_weekends = datetime.strptime( (forward_date), '%Y-%m-%d').strftime('%A') # Grab Name of Requested Date if ((no_weekends == "Saturday") or (no_weekends == "Sunday")): print("#106 Fatal Error: Could Not Calculate Date Accurately" ) # Kill Program, Something Went Very Wrong sys.exit("Fatal Error") forward_date_id = datetime.strptime((forward_date), '%Y-%m-%d').strftime('%d') # Calculate Prediction Date ************************************************* # Calculate Sentiment + Tweet Volume Average 30 Actual Days * From Date * # *************************************************************************** # LONG TERM SENTIMENT ANALYSIS # -------------------------------------------- # Calculate and Normalize 30 Day Sentiment Average # Long Term Sentiment Scale # 0 General Negative Sentiment <95 # 0.1 96 # 0.2 97 # 0.3 98 # 0.4 99 # 0.5 Average 100 # 0.6 101 # 0.7 102 # 0.8 103 # 0.9 104 # 1 General Positive Sentiment >105 # -------------------------------------------- days_list = rangedatelist( date, 29) # Get List of Backdates In This Range (Including Weekends) SAB_Total = 0.0 SAB_Averg = 0.0 SAB_Count = 0 TVA_Count = 0 # Daily Tweet Volume Count TVA_Days = 0.001 # Daily Tweet Volume Total Days TVA_Total = 0 # Tweet Volume Average Total for day in days_list: # For Each Day In Range, Calculate Sentiment Analysis TVA_Days = TVA_Days + 1 SAB_Count = SAB_Count + 1 day = day.strftime('%Y-%m-%d') with connection.cursor() as cursor: sql = "SELECT * FROM `StockTweets` WHERE `Ticker`=%s AND `Date`=%s" cursor.execute(sql, (ticker, day)) connection.commit() one_day = cursor.fetchall() Daily_Sentiment = 0 Daily_Count = 0.001 for tweet in one_day: TVA_Count = TVA_Count + 1 Temp_Likes = 0 Temp_Likes = float(tweet["Total_Likes"]) Temp_Sentiment = 0 Temp_Sentiment = float(tweet["Tweet_Sentiment"]) Daily_Count = Daily_Count + 1 while (Temp_Likes != 0): if ((Temp_Sentiment <= 90) and (Temp_Sentiment >= -90)): # Prevent Overbounds Temp_Sentiment = (Temp_Sentiment * 0.15) + Temp_Sentiment Temp_Likes = Temp_Likes - 1 Daily_Sentiment = Daily_Sentiment + Temp_Sentiment if (Daily_Sentiment == 0): TVA_Days = TVA_Days - 1 # Fix Averages If No Tweets On That Day Tweet_Sentiment = (Daily_Sentiment / Daily_Count ) # Calculate One Day Sentiment SAB_Total = SAB_Total + Tweet_Sentiment # Add One Day Sentiment To SAB Total TVA_Total = (TVA_Count / TVA_Days) SAB_Averg = (SAB_Total / SAB_Count) SAB_Averg = round(SAB_Averg) SAB_Averg = SAB_Averg + 100 print("30 Day Sentiment", SAB_Averg) print("30 Day Avg Tweets", TVA_Total) if (SAB_Averg <= 95): SentimentLongTerm = float(0.0) elif (SAB_Averg == 96): SentimentLongTerm = float(0.1) elif (SAB_Averg == 97): SentimentLongTerm = float(0.2) elif (SAB_Averg == 98): SentimentLongTerm = float(0.3) elif (SAB_Averg == 99): SentimentLongTerm = float(0.4) elif (SAB_Averg == 100): SentimentLongTerm = float(0.5) elif (SAB_Averg == 101): SentimentLongTerm = float(0.6) elif (SAB_Averg == 102): SentimentLongTerm = float(0.7) elif (SAB_Averg == 103): SentimentLongTerm = float(0.8) elif (SAB_Averg == 104): SentimentLongTerm = float(0.9) elif (SAB_Averg >= 105): SentimentLongTerm = float(1.0) else: print("#201 Fatal Error: Could Not Calculate Sentiment Accurately") sys.exit("Fatal Error") # Calculate Sentiment + Volume Average 30 Actual Days * From Date * # *************************************************************************** # TRADING VOLUME ANALYSIS # *************************************************************************** # -------------------------------------------- # Calculate and Normalize Volume Trading # Trading Volume Scale # 0 Unusually Low Volume 0,1 # 0.1 2,3 # 0.2 4,5 # 0.3 6,7 # 0.4 8,9 # 0.5 Average 10 # 0.6 11,12 # 0.7 13,14 # 0.8 15,16 # 0.9 17,18 # 1 Unusually High Volume 19,20 # -------------------------------------------- ThisDay_Volume = int(stock_price_volm) MAV_Averg = int(MAV_Averg) # Pulled From LiveDatabase if (ThisDay_Volume > MAV_Averg): Sub_Process = (ThisDay_Volume / MAV_Averg) Sub_Process = 10 * Sub_Process VolumeNormalized = Sub_Process if (Sub_Process > 20): # Maximum Volume Increase Is Double Sub_Process = 20 VolumeNormalized = Sub_Process elif (Sub_Process < 10): # This shouldn't ever happen Sub_Process = 10 VolumeNormalized = Sub_Process elif (ThisDay_Volume < MAV_Averg): Sub_Process = (ThisDay_Volume / MAV_Averg) Sub_Process = 10 * Sub_Process VolumeNormalized = Sub_Process if (Sub_Process < 0): # No Negatives Allowed Sub_Process = 0 VolumeNormalized = Sub_Process elif (Sub_Process > 10): # This shouldn't ever happen Sub_Process = 10 VolumeNormalized = Sub_Process else: VolumeNormalized = 10 VolumeNormalized = round(VolumeNormalized) if ((VolumeNormalized == 0) or (VolumeNormalized == 1)): VolumeNormalized = float(0.0) elif ((VolumeNormalized == 2) or (VolumeNormalized == 3)): VolumeNormalized = float(0.1) elif ((VolumeNormalized == 4) or (VolumeNormalized == 5)): VolumeNormalized = float(0.2) elif ((VolumeNormalized == 6) or (VolumeNormalized == 7)): VolumeNormalized = float(0.3) elif ((VolumeNormalized == 8) or (VolumeNormalized == 9)): VolumeNormalized = float(0.4) elif (VolumeNormalized == 10): VolumeNormalized = float(0.5) elif ((VolumeNormalized == 11) or (VolumeNormalized == 12)): VolumeNormalized = float(0.6) elif ((VolumeNormalized == 13) or (VolumeNormalized == 14)): VolumeNormalized = float(0.7) elif ((VolumeNormalized == 15) or (VolumeNormalized == 16)): VolumeNormalized = float(0.8) elif ((VolumeNormalized == 17) or (VolumeNormalized == 18)): VolumeNormalized = float(0.9) elif ((VolumeNormalized == 19) or (VolumeNormalized == 20)): VolumeNormalized = float(1.0) else: print("#202 Fatal Error: Could Not Calculate Volume Accurately") sys.exit("Fatal Error") # *************************************************************************** # Calculate and Normalize Daily Sentiment # *************************************************************************** # SHORT TERM TWEET VOLUME # -------------------------------------------- # Calculate and Normalize Tweet Volume # Tweet Volume Scale # 0 Unusually Low Volume 0,1 # 0.1 2,3 # 0.2 4,5 # 0.3 6,7 # 0.4 8,9 # 0.5 Average 10 # 0.6 11,12 # 0.7 13,14 # 0.8 15,16 # 0.9 17,18 # 1 Unusually High Volume 19,20 # -------------------------------------------- DailyTweets_Count = int(DailyTweets_Count) TVA_Total = int(TVA_Total) if (DailyTweets_Count > MAV_Averg): Sub_Process = (DailyTweets_Count / TVA_Total) Sub_Process = 10 * Sub_Process TweetsNormalized = Sub_Process if (Sub_Process > 20): # Maximum Volume Increase Is Double Sub_Process = 20 TweetsNormalized = Sub_Process elif (Sub_Process < 10): # This shouldn't ever happen Sub_Process = 10 TweetsNormalized = Sub_Process elif (DailyTweets_Count < TVA_Total): Sub_Process = (DailyTweets_Count / TVA_Total) Sub_Process = 10 * Sub_Process TweetsNormalized = Sub_Process if (Sub_Process < 0): # No Negatives Allowed Sub_Process = 0 TweetsNormalized = Sub_Process elif (Sub_Process > 10): # This shouldn't ever happen Sub_Process = 10 TweetsNormalized = Sub_Process else: TweetsNormalized = 10 TweetsNormalized = round(TweetsNormalized) if ((TweetsNormalized == 0) or (TweetsNormalized == 1)): TweetsNormalized = float(0.0) elif ((TweetsNormalized == 2) or (TweetsNormalized == 3)): TweetsNormalized = float(0.1) elif ((TweetsNormalized == 4) or (TweetsNormalized == 5)): TweetsNormalized = float(0.2) elif ((TweetsNormalized == 6) or (TweetsNormalized == 7)): TweetsNormalized = float(0.3) elif ((TweetsNormalized == 8) or (TweetsNormalized == 9)): TweetsNormalized = float(0.4) elif (TweetsNormalized == 10): TweetsNormalized = float(0.5) elif ((TweetsNormalized == 11) or (TweetsNormalized == 12)): TweetsNormalized = float(0.6) elif ((TweetsNormalized == 13) or (TweetsNormalized == 14)): TweetsNormalized = float(0.7) elif ((TweetsNormalized == 15) or (TweetsNormalized == 16)): TweetsNormalized = float(0.8) elif ((TweetsNormalized == 17) or (TweetsNormalized == 18)): TweetsNormalized = float(0.9) elif ((TweetsNormalized == 19) or (TweetsNormalized == 20)): TweetsNormalized = float(1.0) else: print("#203 Fatal Error: Could Not Calculate Volume Accurately") sys.exit("Fatal Error") # Calculate and Normalize Volume Trading # *************************************************************************** # *************************************************************************** # Check Above or Below Moving Averages if (stock_price_live > MAB_Averg): AboveBigMoving = 1 else: AboveBigMoving = 0 if (stock_price_live < MAL_Averg): BelowLittleMoving = 1 else: BelowLittleMoving = 0 # Check Above or Below Moving Averages # *************************************************************************** # Algorithm Results # *************************************************************************** print("\n\n") print("Neural Network Normalized Data: ") print("----------------------------------------------") print("Entry ", Entry) print("Volume Normalized: [0,1] ", VolumeNormalized) print("Tweets Normalized: [0,1] ", TweetsNormalized) print("Sentiment Analysis: [0,1] ", SentimentNormalized) print("Above Big Moving: [0,1] ", AboveBigMoving) print("Below Little Moving: [0,1] ", BelowLittleMoving) print("Trading Day: [0,1] ", trading_day) print("Trading Season: [Sp, Su, Fa, Wi] ", trading_season) print("Moving Avg Sentiment: [0,200] ", SAB_Averg) outputs = network.process([ trading_day, trading_season, VolumeNormalized, AboveBigMoving, BelowLittleMoving, VolumeNormalized, SentimentNormalized, SentimentLongTerm ]) result1 = round(outputs[0], 2) result2 = round(outputs[1], 2) print("Results") print("--------------------------------------------------") print("Prediction: [", result1, ",", result2, "]") print("\n") try: with connection.cursor() as cursor: sql = "SELECT * FROM `Predictions` WHERE `PredictionDate`=%s AND Ticker=%s" cursor.execute(sql, (forward_date, ticker)) result = cursor.fetchone() if result is None: print("Success: Inserting Prediction To Records: ", ticker) with connection.cursor() as cursor: sql = "INSERT INTO `Predictions` (`Date`, `PredictionDate`, `Day_ID`, `PredictionDateName`, `Ticker`, `InitialPrice`, `Entry`, `Pred_Output1`, `Pred_Output2`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute( sql, (Today, forward_date, forward_date_id, no_weekends, ticker, stock_price_live, Entry, result1, result2)) connection.commit() else: print("Success: Updating Prediction In Records: ", ticker) unique_entry = result["Entry"] cursor = connection.cursor() sql = "UPDATE Predictions SET Date=%s WHERE Entry=%s" cursor.execute(sql, (Today, unique_entry)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET InitialPrice=%s WHERE Entry=%s" cursor.execute(sql, (stock_price_live, unique_entry)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET PredictionDate=%s WHERE Entry=%s" cursor.execute(sql, (forward_date, unique_entry)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET PredictionDateName=%s WHERE Entry=%s" cursor.execute(sql, (no_weekends, unique_entry)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET Pred_Output1=%s WHERE Entry=%s" cursor.execute(sql, (result1, unique_entry)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET Pred_Output2=%s WHERE Entry=%s" cursor.execute(sql, (result2, unique_entry)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET Day_ID=%s WHERE Entry=%s" cursor.execute(sql, (forward_date_id, unique_entry)) connection.commit() finally: # Adjusted for possibility that network is not fully trained yet. # Fine tune this as time goes forward if ((result1 >= 0.0) and (result1 <= 0.15)): approx = 0.0 elif ((result1 > 0.15) and (result1 < 0.45)): approx = 0.3 elif ((result1 >= 0.45) and (result1 < 0.55)): approx = -1.0 elif ((result1 >= 0.55) and (result1 < 0.85)): approx = 0.7 elif ((result1 >= 0.85) and (result1 <= 1.00)): approx = 1.0 if (approx == 0.0): prediction_event = "Significant Decrease" elif (approx == 0.3): prediction_event = "Decrease" elif (approx == 0.7): prediction_event = "Increase" elif (approx == 1.0): prediction_event = "Significant Increase" elif (approx == -1.0): prediction_event = "Inconclusive" confidence = 0 boost = 0.0 if (AboveBigMoving == 1): boost = boost + 0.13 if (BelowLittleMoving == 1): boost = boost + 0.13 if (approx == 0.0): if (result1 == 0.0): confidence = 10 if (result1 == 0.01): confidence = 9 if (result1 == 0.02): confidence = 9 if (result1 == 0.03): confidence = 9 if (result1 == 0.04): confidence = 8 if (result1 == 0.05): confidence = 8 if (result1 == 0.06): confidence = 7 if (result1 == 0.07): confidence = 7 if (result1 == 0.08): confidence = 6 if (result1 == 0.09): confidence = 6 if (result1 == 0.10): confidence = 6 if (result1 == 0.11): confidence = 6 if (result1 == 0.12): confidence = 6 if (result1 == 0.13): confidence = 6 if (result1 == 0.14): confidence = 6 if (result1 == 0.15): confidence = 6 if (approx == 0.3): if (result1 == 0.16): confidence = 5 if (result1 == 0.17): confidence = 5 if (result1 == 0.18): confidence = 5 if (result1 == 0.19): confidence = 5 if (result1 == 0.20): confidence = 5 if (result1 == 0.21): confidence = 5 if (result1 == 0.22): confidence = 6 if (result1 == 0.23): confidence = 6 if (result1 == 0.24): confidence = 7 if (result1 == 0.25): confidence = 7 if (result1 == 0.26): confidence = 7 if (result1 == 0.27): confidence = 8 if (result1 == 0.28): confidence = 8 if (result1 == 0.29): confidence = 9 if (result1 == 0.30): confidence = 9 if (result1 == 0.31): confidence = 9 if (result1 == 0.32): confidence = 8 if (result1 == 0.33): confidence = 7 if (result1 == 0.34): confidence = 6 if (result1 == 0.35): confidence = 6 if (result1 == 0.36): confidence = 5 if (result1 == 0.37): confidence = 5 if (result1 == 0.38): confidence = 4 if (result1 == 0.39): confidence = 3 if (result1 == 0.40): confidence = 3 if (result1 == 0.41): confidence = 2 if (result1 == 0.42): confidence = 2 if (result1 == 0.43): confidence = 1 if (result1 == 0.44): confidence = 1 if (approx == -1.0): if (result1 == 0.45): confidence = 0 if (result1 == 0.46): confidence = 0 if (result1 == 0.47): confidence = 0 if (result1 == 0.48): confidence = 0 if (result1 == 0.49): confidence = 0 if (result1 == 0.50): confidence = 0 if (result1 == 0.51): confidence = 0 if (result1 == 0.52): confidence = 0 if (result1 == 0.53): confidence = 0 if (result1 == 0.54): confidence = 0 if (approx == 0.7): if (result1 == 0.55): confidence = 1 if (result1 == 0.56): confidence = 1 if (result1 == 0.57): confidence = 2 if (result1 == 0.58): confidence = 2 if (result1 == 0.59): confidence = 2 if (result1 == 0.60): confidence = 3 if (result1 == 0.61): confidence = 3 if (result1 == 0.62): confidence = 4 if (result1 == 0.63): confidence = 5 if (result1 == 0.64): confidence = 5 if (result1 == 0.65): confidence = 6 if (result1 == 0.66): confidence = 7 if (result1 == 0.67): confidence = 8 if (result1 == 0.68): confidence = 8 if (result1 == 0.69): confidence = 9 if (result1 == 0.70): confidence = 9 if (result1 == 0.71): confidence = 9 if (result1 == 0.72): confidence = 8 if (result1 == 0.73): confidence = 8 if (result1 == 0.74): confidence = 7 if (result1 == 0.75): confidence = 7 if (result1 == 0.76): confidence = 6 if (result1 == 0.77): confidence = 6 if (result1 == 0.78): confidence = 6 if (result1 == 0.79): confidence = 5 if (result1 == 0.80): confidence = 5 if (result1 == 0.81): confidence = 5 if (result1 == 0.82): confidence = 5 if (result1 == 0.83): confidence = 5 if (result1 == 0.84): confidence = 5 if (approx == 1.0): if (result1 == 0.85): confidence = 5 if (result1 == 0.86): confidence = 5 if (result1 == 0.87): confidence = 5 if (result1 == 0.88): confidence = 5 if (result1 == 0.89): confidence = 5 if (result1 == 0.90): confidence = 5 if (result1 == 0.91): confidence = 6 if (result1 == 0.92): confidence = 6 if (result1 == 0.93): confidence = 6 if (result1 == 0.94): confidence = 6 if (result1 == 0.95): confidence = 7 if (result1 == 0.96): confidence = 8 if (result1 == 0.97): confidence = 9 if (result1 == 0.98): confidence = 9 if (result1 == 0.99): confidence = 9 if (result1 == 1.00): confidence = 9 confidence = (confidence + (confidence * boost)) cursor = connection.cursor() sql = "UPDATE Stocks SET Prediction=%s WHERE Ticker=%s" cursor.execute(sql, (prediction_event, ticker)) connection.commit() cursor = connection.cursor() sql = "UPDATE Stocks SET Prediction_Day=%s WHERE Ticker=%s" cursor.execute(sql, (no_weekends, ticker)) connection.commit() cursor = connection.cursor() sql = "UPDATE Stocks SET Confidence=%s WHERE Ticker=%s" cursor.execute(sql, (confidence, ticker)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET Confidence=%s WHERE PredictionDate=%s AND Ticker=%s" cursor.execute(sql, (confidence, forward_date, ticker)) connection.commit() cursor = connection.cursor() sql = "UPDATE Predictions SET Prediction=%s WHERE PredictionDate=%s AND Ticker=%s" cursor.execute(sql, (prediction_event, forward_date, ticker)) connection.commit() print("::: SAVING DATA ::: DO NOT CLOSE CONNECTION :::")
network = Network() network.add_layer( 8, 8, Network.ACTIVATION_SIGMOID) # Hidden Layer, 10 Neurons, 8 inputs network.add_layer( 2, 8, Network.ACTIVATION_SIGMOID) # Output Layer, 2 Neurons, 8 inputs print("Creating Neural Network...") ITERATIONS = 500 # Number of iterations per training session LEARN_RATE = 0.03 # The rate the network learns on each iteration THRESHOLD = 0.001 # If this precision is reached, the training session is instantly complete # Perform a quick training session for i in range(0, ITERATIONS, 1): error = 0 print("Learning to fly...") with connection.cursor() as cursor: sql = "SELECT * FROM `NeuralNetwork` ORDER BY Date DESC LIMIT 150" cursor.execute(sql, ()) connection.commit() NeuralTraining = cursor.fetchall() for set in NeuralTraining: print("Finding hidden Robots...") train = [ set["TradingDay"], set["TradingSeason"], set["VolumeNormalized"], set["AboveBigMoving"], set["BelowLittleMoving"], set["TweetsVolumeNormalized"], set["Sentiment"], set["Sentiment30"] ] Output1 = set["Output1"] Output2 = set["Output2"] error += network.train(train, [Output1, Output2], LEARN_RATE)