<jupyter_start><jupyter_text>Price Action Indicators<jupyter_code>import os
import sqlite3
import pandas as pd
import numpy as np

import matplotlib as plot
import mplfinance as mpf
import plotly.graph_objects as go

#%matplotlib inline

database_name = "TSX_Quality.sqlite"<jupyter_output><empty_output><jupyter_text>Load our Data<jupyter_code># Load from price table
# Convert string columns to float values
# Replace "-" prices with Close values
# Make sure al dates are sorted in ascending order (TA libraries need this order)
# Heavy function to retreive all prices from database (more than 6 million rows, takes many seconds to execute)
def get_all_prices():
    conn = sqlite3.connect(database_name)
    
    # Make sur all dates are in ascending order
    sql = f"SELECT * FROM Prices_Daily ORDER BY UPPER(Ticker) ASC, Date ASC"
    prices = pd.read_sql_query(sql, conn, index_col="Date")
    prices.index = pd.to_datetime(prices.index)
    
    # Clean up CSV data to make sure we have only floats and no "-" values
    prices.replace("-", np.NaN, inplace=True)
    #prices["Volume"].replace(0, np.NaN, inplace=True)
    prices["Open"] = prices["Open"].astype(float)
    prices["High"] = prices["High"].astype(float)
    prices["Low"]  = prices["Low"].astype(float)
    prices["Close"]= prices["Close"].astype(float)
    
    # Required for finTA
    prices.rename(columns={"Open":"open", "High":"high", "Low":"low", "Close":"close", "Volume":"volume"}, inplace=True)
    prices.index.rename("date", inplace=True)

    prices_per_ticker = prices.groupby(["Ticker"])

    return prices_per_ticker

def get_prices_for(symbol):
    conn = sqlite3.connect(database_name)
    
    # Make sur all dates are in ascending order
    sql = f"SELECT * FROM Prices_Daily WHERE Ticker = '{symbol}' ORDER BY UPPER(Ticker) ASC, Date ASC"
    prices = pd.read_sql_query(sql, conn, index_col="Date")
    prices.index = pd.to_datetime(prices.index)
    
    # Clean up CSV data to make sure we have only floats and no "-" values
    prices.replace("-", np.NaN, inplace=True)
    #prices["Volume"].replace(0, np.NaN, inplace=True)
    prices["Open"] = prices["Open"].astype(float)
    prices["High"] = prices["High"].astype(float)
    prices["Low"]  = prices["Low"].astype(float)
    prices["Close"]= prices["Close"].astype(float)
    
    # Required for finTA
    prices.rename(columns={"Open":"open", "High":"high", "Low":"low", "Close":"close", "Volume":"volume"}, inplace=True)
    prices.index.rename("date", inplace=True) 
    
    return prices   

def get_all_symbols():
    conn = sqlite3.connect(database_name)
    sql = f"SELECT * FROM Symbols ORDER BY UPPER(Ticker) ASC"
    symbols_df = pd.read_sql_query(sql, conn)
    
    return symbols_df

#prices_df = get_all_prices()
# Access specific company using .get_group("TickerSymbol")

data = get_all_symbols()
filtre_tsx = data["exchange"] == "tsx"
tsx_symbols = data[filtre_tsx]
tsx_string = "["
for symbol in tsx_symbols["ticker"]:
    if symbol is not None:
        symbol_yahoo = symbol.replace(".", "-") + ".TO"
        tsx_string = tsx_string + symbol_yahoo + ","

tsx_string = tsx_string[:-1] + "]"
tsx_string
# prices_per_ticker.get_group("SHOP").loc["2021-03" : "2021-05"]   #Deprecated method sort dataframe before using slicing on index
# prices_per_ticker.get_group("SHOP").sort_index().loc["2016-03" : "2016-05"]
#prices_df.get_group("SHOP")
# prices_per_ticker.get_group("SHOP").sort_index().loc["2015"]["High"].max()
#prices_df.get_group("SHOP").sort_index().loc["2020"]["low"].min()
# 2228.73  : MAX All years      MIN   25.85
# 1742.37  : MAX 2022           MIN 1424.95
# 2228.73  : MAX 2021           MIN 1248.55
# 1658.79  : MAX 2020           MIN  435.03
#  544.00  : MAX 2019           MIN  174.59
#  232.65  : MAX 2018           MIN  126.65
#  151.88  : MAX 2017           MIN   56.60
#   60.93  : MAX 2016           MIN   25.85
#   53.50  : MAX 2015           MIN   30.00
# prices_2 = prices_df.get_group("SHOP").copy()
# prices_2["SMA"] = prices_2["close"].rolling(window=5).mean()
# prices_2.tail(5)
def run_indicators(prices):
    data_df = prices.copy()
    symbol = data.iloc[0]["Ticker"]
    data_df.drop("Ticker", axis=1, inplace=True)
    #data_df.rename(columns={"Open":"open", "High":"high", "Low":"low", "Close":"close", "Volume":"volume"}, inplace=True)
    #data_df.index.rename("date", inplace=True)
    #data_df.sort_index(ascending=True, inplace=True)
    
    indicators_df = pd.DataFrame()
    # Return a series
    try:
        for period in ["200", "100", "50", "30", "20", "15", "9", "5"]:
            indicators_df[f"SMA{period}"] = TA.SMA(data_df, int(period))
    except Exception:
        print(f"Error calculating SMA{period}")
    try:
        for period in ["200", "100", "50", "30", "20", "15", "9", "5"]:
            indicators_df[f"EMA{period}"] = TA.EMA(data_df, int(period))
    except Exception:
        print(f"Error calculating EMA{period}")
    try:
        for period in ["200", "100", "50", "30", "20", "15", "9", "5"]:
            indicators_df[f"VAMA{period}"] = TA.VAMA(data_df, int(period))
    except Exception:
        print(f"Error calculating VAMA{period}")
    try:
        for period in ["200", "100", "50", "30", "20", "15", "9", "5"]:
            indicators_df[f"WMA{period}"] = TA.WMA(data_df, int(period))
    except Exception:
        print(f"Error calculating WMA{period}")
    try:
        for period in ["30", "20", "14", "10", "7"]:
            indicators_df[f"RSI{period}"] = TA.RSI(data_df, int(period))
    except Exception:
        print(f"Error calculating RSI{period}")
    try:
        for period in ["30", "20", "14", "10", "7"]:
            indicators_df[f"ATR{period}"] = TA.ATR(data_df, int(period))
    except Exception:
        print(f"Error calculating ATR{period}")        

    data_df = data_df.join(indicators_df)

    try:
        data_df["UpTrend"] = data_df["close"] > data_df["SMA200"]
        data_df["DownTrend"] = data_df["close"] <= data_df["SMA200"]
    except Exception:
        data_df["UpTrend"] = False
        data_df["DownTrend"] = False
        print(f"{symbol} : Error identifying UP/Down Trend")

    data_df.insert(0,"ticker", symbol, allow_duplicates=True)
    return data_df


# data = prices_df.get_group("AFN.DB.G").copy()
# data.drop( data.loc[data["volume"] == 0].index, inplace=True )
# run_indicators(data).tail(5)

def run_other_indicators(prices):
    data_df = prices.copy()
    symbol = data.iloc[0]["Ticker"]
    data_df.drop("Ticker", axis=1, inplace=True)   
    
    indicators_df = pd.DataFrame()
    # Returns a Dataframe
    try:
        MACD = TA.MACD(data_df)
        MACD.rename(columns={"SIGNAL":"MACD_SIGNAL"}, inplace=True)
        data_df = data_df.join(MACD)
    except Exception:
        data_df["MACD"] = np.NaN
        data_df["MACD_SIGNAL"] = np.NaN
        print(f"Error calculating MACD")

    try:
        VW_MACD = TA.MACD(data_df)
        VW_MACD.rename(columns={"MACD":"VW_MACD"}, inplace=True)
        VW_MACD.rename(columns={"SIGNAL":"VW_MACD_SIGNAL"}, inplace=True)    
        data_df = data_df.join(VW_MACD)
    except Exception:
        data_df["VW_MACD"] = np.NaN
        data_df["VW_MACD_SIGNAL"] = np.NaN
        print(f"Error calculating VW_MACD (Value Weighted MACD)")

    try:
        BBANDS = TA.BBANDS(data_df) # Standard 20 day period, with 2 standard deviations
        data_df = data_df.join(BBANDS)
    except Exception:
        data_df["BB_UPPER"]  = np.NaN
        data_df["BB_MIDDLE"] = np.NaN
        data_df["BB_LOWER"]  = np.NaN
        print(f"Error calculating BBANDS (Bollinger Bands)")

symbols = get_all_symbols()
print(symbols.shape)
list1 = symbols[1:101]["ticker"].tolist()
str1 = "("
for s in list1:
    str1 += f"'{s}',"
str1 += ")"   
print(str1)
def strategy_1(prices):
    """ <NAME> : https://www.youtube.com/watch?v=W8ENIXvcGlQ
    CONDITIONS:
    1) Close price > SMA200
    2) RSI10 < 30
    3) Next Day = BUY
    """
    
    for i in prices.index:
        print(f"{i}")

    return True

# conn = sqlite3.connect(database_name)
# symbol = "SHOP"
# SQL = f"SELECT * FROM Prices_Daily WHERE ticker ='{symbol}' ORDER BY Date ASC"
# data = pd.read_sql_query(SQL, conn)

symbols = get_all_symbols()
list1 = symbols[1:101]["ticker"].tolist()

for symbol in list1:
    data = get_prices_for(symbol)
    indicators = run_indicators(data)
    
    conn = sqlite3.connect(database_name)
    indicators.to_sql("Indicators", conn, if_exists="append")
    print(f"{symbol} : Saved indicators to DB - {data.shape}")

data_test = get_prices_for("AFN.DB.G")
# data_test    # 609 rows
mask = data_test["volume"] < 1
# data_test.loc[mask]  # 67 to drop

data_test.drop(data_test.loc[mask].index, inplace=True)
data_test.dropna
data_test.resample("24H")
data_test
indic = TA.MACD(data_test)
indic.rename(columns={"SIGNAL":"MACD_SIGNAL"}, inplace=True)
indic<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1549501065141832, 'fraction_numerical': 0.03408453862540643, 'mean_word_length': 3.525621511922882, 'pattern_counts': {'":': 19, '<': 13, '<?xml version=': 0, '>': 12, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '537811', 'license': 'MIT', 'n_tokens_mistral': 3410, 'n_tokens_neox': 3232, 'n_words': 686, 'path': 'DataPriceIndicators.ipynb', 'repo_name': 'poivronjaune/stock_screener', 'size': '8923'}