Code

import os
import mysql.connector
import xlwt

con = mysql.connector.connect(
  host="localhost",
  user="root",
  password="12345678",
  database="kinz7761_project"
)

ma_arr = [5,20,50,60,100,120,200]

cursor = con.cursor()
sql = "select Date,Ticker,Close from saham_screening where Date='2020-11-24' limit 1"
cursor.execute(sql)
record = cursor.fetchall()
for row in record:
    date = row[0]
    ticker = row[1]
    close = row[2]
    sma = []
    wma = []
    print(ticker)
    
    for ma in ma_arr:
        sql = "select Close from saham_screening where Date <= '"+str(date.strftime("%Y/%m/%d"))+"' and Ticker='"+ticker+"' order by Date Desc Limit "+str(ma)
        cursor.execute(sql)
        record2 = cursor.fetchall()
        if len(record2) == ma :
            #SMA
            close_sma = 0
            for row2 in record2:
                close_sma = close_sma + row2[0]

            sma.append(round(close_sma/ma,0))
            
            #WMA
            close_wma = 0
            ma_wma = ma
            period_wma = 0
            
            
            for row3 in record2: 
                close_wma = close_wma + (ma_wma * row3[0])
                period_wma = period_wma + ma_wma
                ma_wma = ma_wma -1

            wma.append(round(close_wma/period_wma,0))
    
    
    #Envelope
    sql = "select Close from saham_screening where Date <= '"+str(date.strftime("%Y/%m/%d"))+"' and Ticker='"+ticker+"' order by Date Desc Limit 5"
    cursor.execute(sql)
    record3 = cursor.fetchall()
    close_envelope = 0
    for row4 in record3:
        close_envelope = close_envelope + row4[0]
    sma_envelope = close_envelope/5
    deviasi = 5/100

    upper_envelope = round(float(sma_envelope) + (float(sma_envelope) * float(deviasi)),0)
    mid_envelope = round(float(sma_envelope),0)
    lower_envelope = round(float(sma_envelope) - (float(sma_envelope) * float(deviasi)),0)

    """
    #Stochastic
    cursor.execute("Select a.Low,a.High from (select Date,Low,High FROM saham_screening where date <= '"+str(date.strftime("%Y/%m/%d"))+"' and Ticker='"+ticker+"' order by Date Desc Limit 14) as a order by a.Date asc Limit 1")
    record4 = cursor.fetchall()
    stochastic_k = 0
    stochastic_k2 = 0
    for row5 in record4:
        stochastic_k = round(((close - row5[0]) / (row5[1] - row5[0])) * 100,0)

    cursor.execute("select Date FROM saham_screening where date < '"+str(date.strftime("%Y/%m/%d"))+"' and Ticker='"+ticker+"' order by Date Desc Limit 2")
    record5 = cursor.fetchall()
    for row6 in record5:
        cursor.execute("Select a.Low,a.High from (select Date,Low,High FROM saham_screening where date <= '"+str(row6[0].strftime("%Y/%m/%d"))+"' and Ticker='"+ticker+"' order by Date Desc Limit 14) as a order by a.Date asc Limit 1")
        record6 = cursor.fetchall()
        for row7 in record6:
            stochastic_k2 = stochastic_k2 + round(((close - row7[0]) / (row7[1] - row7[0])) * 100,0)
    
       
    stochastic_d = round((stochastic_k + stochastic_k2)/3,0)
    print("--Stochastic----")
    print(stochastic_k)
    print(stochastic_d)
    """

    sql = "update saham_screening set sma5="+str(sma[0])+",sma20="+str(sma[1])+",sma50="+str(sma[2])+",sma60="+str(sma[3])+",sma100="+str(sma[4])+",sma120="+str(sma[5])+",sma200="+str(sma[6])+",wma5="+str(wma[0])+",wma20="+str(wma[1])+",wma50="+str(wma[2])+",wma60="+str(wma[3])+",wma100="+str(wma[4])+",wma120="+str(wma[5])+",wma200="+str(wma[6])+",Envelope_Upper="+str(upper_envelope)+",Envelope_Mid="+str(mid_envelope)+",Envelope_Lower="+str(lower_envelope)+" where Date = '"+str(date.strftime("%Y/%m/%d"))+"' and Ticker='"+ticker+"' "
    cursor.execute(sql)
          

con.commit()

cursor.close()
con.close()