<jupyter_start><jupyter_text>In this and following section 6 notebooks. I want to begin to use this model and daily and track its performance over time. Since we have streamlined the prediction process in section 4, we can now use similar methods to create a database of predictions on a daily basis. Using some other python libraries and methods I will also create a script that will show us a visual representaton of how the model has been performing over time, its current accuracy metric performance, as well as potential return on investment if we were to deploy this model to execute trades (and this functionality also exists within the pyhthon-binance libray!)<jupyter_code># the following below is copied from our predictor.py script. It will collect transform and input daily data 
# and give a printout about predicted price movement direction and level of confidence. Taking this script and
# modifying the results to our desired outputs should not take much


# import all of our required libraries for necessary data processing and data requests

import numpy as np
import pandas as pd
from binance.client import Client
import joblib
from sqlite3 import Error


# define our function to retrieve klines data from binance API

def get_data():
    
    '''
    This function will execute API call to Binance to retrieve data.
    We will export the results of this data into the appropriately named dataframe for further feature engineering.
    '''
    
    client = Client()
    # establishing our blank client
    
    candles = client.get_klines(symbol='BTCUSDT', interval=Client.KLINE_INTERVAL_1DAY, limit=91)
    # we only need to request the most recent 90 days to calculate our prediction data
    
    data = pd.DataFrame(candles, columns=['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Close time', 'Quote asset volume', 'Number of trades', 'Taker buy base volume', 'Taker buy quote volume', 'Ignore'])
    # these column labels are as labelled on the Binance API documentation
    
    data.drop(['Close time', 'Ignore'], axis=1, inplace=True)
    # dropping unneeded columns
    
    data['Date'] = data['Date'].apply(lambda x: pd.to_datetime(x, unit='ms'))
    # converting to proper date format for better visual reference
    
    data.set_index('Date', inplace=True)
    # setting index to date
    
    data = data.astype('float64')
    # converting from object type to float type
    
    return data





# we will define a function to run prior to calcualting our averages

def feat_eng(X_df):
    '''
    Intakes "X" portion of data and outputs selected engineered features
    '''
    
    X_df['High/Low'] = X_df['High'] - X_df['Low']
    X_df['volX'] = X_df['Quote asset volume'] / X_df['Volume']
    X_df['quote-buy'] = X_df['Taker buy quote volume'] / X_df['Taker buy base volume']
    
    return X_df





# lets define a function to create our moving averages and incoroprate them into our dataframe

def get_sma(X_df):
    '''
    This function intakes the "X" portion of the data and returns the data with moving average columns applied
    '''
    
    SMAs = [7,30,90]                                                     # 7, 30, and 90 day simple moving averages
    for val in SMAs:
        X_df[str(val)+'sma'] = X_df['Close'].rolling(f'{val}D').mean()   # using the pandas rolling function to calculate mean values over each desired SMA value
        
    return X_df




# Now we want to take the most recent data point possible to make our prediction from

def X_input(X_df):
    x_input = X_df[-1:]        # take the most recent value after calculations for passing into model
    
    return x_input


# now to create a function that ties all of these together and gives us our desired input for the model

def to_predict():
    
    data = get_data()
    data_features = feat_eng(data)
    data_all = get_sma(data_features)
    x_input = X_input(data_all)
    
    return x_input




'''
This now gives us all functions and libraries needed to create our input for the model to predict.
'''

X = to_predict()

# now we must load our saved model using pickle

with open("final_model.pkl", "rb") as file:
    model = joblib.load(file)
    
    
predicted = model.predict_proba(X)

# all of this is no longer needed, instead we are going to be using the output of the prediction
# this will be used to add to our data intaken from the binance API and to create our database table
# following database creation, we will then define pathways where the model will intake data from our created database,
# and use that data to create visual representations of model performance over time


# if (predicted[0][0] < predicted[0][1]) & (predicted[0][1] > 0.6):
#     print(f'*********************\n\n\n\nThe price of Bitcoin is predicted to go UP tomorrow!\nI am quite confident about this!\nAt this confidence I am correct {53/(53+36)*100:.2f}% of the time!\n\n\n\nThis is not finanical advice. I am not a financial advisor. All information here is for entertainment purposes only.\n\n\n\n*********************')
    
# elif (predicted[0][0] < predicted[0][1]) & (predicted[0][1] > 0.55):
#     print(f'*********************\n\n\n\nThe price of Bitcoin is predicted to go UP tomorrow!\nI am sort of confident about this!\nAt this confidence I am correct {36/(36+27)*100:.2f}% of the time!\n\n\n\nThis is not finanical advice. I am not a financial advisor. All information here is for entertainment purposes only.\n\n\n\n*********************')
              
# elif (predicted[0][1] < predicted[0][0]) & (predicted[0][0] > 0.6):
#     print(f'*********************\n\n\n\nThe price of Bitcoin is predicted to go DOWN tomorrow!\nI am quite confident about this!\nAt this confidence I am correct {61/(61+44)*100:.2f}% of the time!\n\n\n\nThis is not finanical advice. I am not a financial advisor. All information here is for entertainment purposes only.\n\n\n\n*********************')
          
# elif (predicted[0][1] < predicted[0][0]) & (predicted[0][0] > 0.55):
#     print(f'*********************\n\n\n\nThe price of Bitcoin is predicted to go DOWN tomorrow!\nI am sort of confident about this!\nAt this confidence I am correct {38/(38+31)*100:.2f}% of the time!\n\n\n\nThis is not finanical advice. I am not a financial advisor. All information here is for entertainment purposes only.\n\n\n\n*********************')
          
# elif predicted[0][0] < predicted[0][1]:
#     print(f'*********************\n\n\n\nThe price of Bitcoin is predicted to go UP tomorrow!\nI am not very confident about this!\nAt this confidence I am correct {67/(67+53)*100:.2f}% of the time!\n\n\n\nThis is not finanical advice. I am not a financial advisor. All information here is for entertainment purposes only.\n\n\n\n*********************')

# elif predicted[0][1] < predicted[0][0]:
#     print(f'*********************\n\n\n\nThe price of Bitcoin is predicted to go DOWN tomorrow!\nI am not very confident about this!\nAt this confidence I am correct {44/(44+36)*100:.2f}% of the time!\n\n\n\nThis is not finanical advice. I am not a financial advisor. All information here is for entertainment purposes only.\n\n\n\n*********************')
    
# else:
#     pass

# our "to_predict" function gives us our desired output for prediction. 

X
model.predict_proba(X)[0]
# now to create a function that will intake our "to_predict variable", and give us an output of that same dataframe
# having 2 new columns, predicted label and confidence of prediction

def add_prediction(X_df):
    pred = model.predict_proba(X_df)[0]                              # this gives us our predictor array of confidence 
    # create our new columns based on prediction output
    X_df['Prediction'] = 1 if pred[1] > pred[0] else 0               # predicted class based on higher confidence
    X_df['Confidence'] = pred[1] if pred[1] > pred[0] else pred[0]   # confidence score (probability) of larger class
    
    return X_df                                                      # output modified X_df dataframe to be exported into database


# test run of new function

add_prediction(X)<jupyter_output><empty_output><jupyter_text>Function works exactly as intended. New columns work perfectly. Next steps will be to create a function that will evaluate previous daily predictions and append the coorect result to our dataframe. This will allow us to track accuracy moving forward.<jupyter_code>def eval_prediction(X_yesterday, X_df):
    '''
    This function will intake our modified X dataframe from the previous day as well as our current prediction
    and output a new column which gives the correct label, as well as if the model predicted correctly or not.
    
    '''
    X_yesterday['True_Label'] = 1 if X_df['Close'].values > X_yesterday['Close'].values else 0                         # this gives us the correct label
    X_yesterday['Correct_Pred'] = 1 if X_yesterday['Prediction'].values == X_yesterday['True_Label'].values else 0      # this gives a 1 for a correct prediction and a 0 for incorrect
    
    return X_yesterday
# creating a function which will pull the previous days data instead of today 

def X_input_yesterday(X_df):
    x_input = X_df[-2:-1]        # take the most recent value after calculations for passing into model
    
    return x_input


# now to create a function that ties all of these together and gives us our desired input for the model

def to_predict_yesterday():
    
    data = get_data()
    data_features = feat_eng(data)
    data_all = get_sma(data_features)
    x_input_yesterday = X_input_yesterday(data_all)
    
    return x_input_yesterday

X_yesterday = to_predict_yesterday()
X_yesterday
# now we will use our new functions to add predictions to yesterdays data, and then attempt to evaluate its performance

X_yesterday = add_prediction(X_yesterday)
X_yesterday
# now to test evaluation

X_evaluated = eval_prediction(X_yesterday, X)<jupyter_output><empty_output><jupyter_text>Now we have function which can intake our desired data and give us an evaluation of the previous prediction.The next step will be to create a function will connect to and update a database daily with our new evaluated data. From this database we can then access data and create a new set of functions which will display performance over time and quantify our gains and losses.<jupyter_code>import sqlite3
# defining connection and cursor

connection = sqlite3.connect('predictor_data.db')
cursor = connection.cursor()

# create our inital table

command1 = """CREATE TABLE IF NOT EXISTS model_data (
                id integer AUTO_INCREMENT PRIMARY KEY, 
                date varchar(255),
                open float,
                high float,
                low float,
                close float,
                volume float,
                QaV float,
                trades integer,
                takerBase float,
                takerQuote float,
                highLow float,
                volX float,
                quoteBuy float,
                sevensma float,
                thirtysma float,
                ninetysma float,
                Pred integer,
                Conf float,
                True integer,
                Correct integer);"""

cursor.execute(command1)
#not needed

# # now we need to create a function that will connect to our database 
# # and input our desired data for later retrieval

# def store_data(X_evaluated):
#     '''
#     This function will intake the fully modified version of our data including correct 
#     predictions and all info.
#     It will write this data to our newly created database.

#     '''
#     connection = sqlite3.connect('predictor_data.db')
#     cursor = connection.cursor()
    
#     command = f"""
#                 INSERT INTO model_data (
#                 date, open, high, low, close, volume, QaV, trades, takerBase,
#                 takerQuote, highLow, volX, quoteBuy, sevensma, thirtysma,
#                 ninetysma, Pred, Conf, True, Correct
#                 )
#                 VALUES (
#                 {str(X_evaluated.index[0]).split(' ')[0]},
#                 {X_evaluated['Open'].values[0]},
#                 {X_evaluated['High'].values[0]},
#                 {X_evaluated['Low'].values[0]},
#                 {X_evaluated['Close'].values[0]},
#                 {X_evaluated['Volume'].values[0]},
#                 {X_evaluated['Quote asset volume'].values[0]},
#                 {X_evaluated['Number of trades'].values[0]},
#                 {X_evaluated['Taker buy base volume'].values[0]},
#                 {X_evaluated['Taker buy quote volume'].values[0]},
#                 {X_evaluated['High/Low'].values[0]},
#                 {X_evaluated['volX'].values[0]},
#                 {X_evaluated['quote-buy'].values[0]},
#                 {X_evaluated['7sma'].values[0]},
#                 {X_evaluated['30sma'].values[0]},
#                 {X_evaluated['90sma'].values[0]},
#                 {X_evaluated['Prediction'].values[0]},
#                 {X_evaluated['Confidence'].values[0]},
#                 {X_evaluated['True_Label'].values[0]},
#                 {X_evaluated['Correct_Pred'].values[0]}
#                 );"""
#     cursor.execute(command)
#     cursor.close()
    
store_data(X_evaluated)
X_evaluated['Open'].values[0]
str(X_evaluated.index[0]).split(' ')[0]<jupyter_output><empty_output><jupyter_text>When troubleshooting, came across a pandas extension that will write our data to database for us. Going to attempt to create the function using this instead as it will hopeully bypass some formatting issues.<jupyter_code># define a new function to intake our X_evaluated dataframe and write it to our new 
# SQL database for future use

def to_database(X_evaluated):
    '''
    This function takes in our fully evaluated predictions and writes them 
    to an SQL database for further reference.
    '''
    
    conn = None
    try:
        conn = sqlite3.connect('bitcoin_model.db')
        print('Connected Successfully!')
    
    except Error as e:
        print(e)
        
    X_evaluated.to_sql('predictions', con=conn, if_exists='append')
    
        
    conn.close()
to_database(X_evaluated)<jupyter_output>Connected Successfully!
{'fraction_non_alphanumeric': 0.10446503791069923, 'fraction_numerical': 0.012707104745857905, 'mean_word_length': 3.0126760563380284, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '494458', 'license': 'MIT', 'n_tokens_mistral': 4115, 'n_tokens_neox': 3916, 'n_words': 1548, 'path': '6-1_Model-Tracking-and-Database-Creation.ipynb', 'repo_name': 'shmaze/LHL-Final-Project', 'size': '14244'}