주식 *

import pandas as pd
from pyhive import hive
import re
money = 0
cn = 1
cc={}
cursor = hive.connect('localhost').cursor()

def yy(x):
    y='2999-12-31'
    if x == '2019':
        y = '2020-01-01'
    elif x == '2020_1':
        y = '2020-04-01'
    elif x == '2020_2':
        y = '2020-07-01'
    elif x == '2020_3':
        y = '2020-10-01'
    elif x == '2020_4':
        y = '2021-01-01'
    return y
        
# 
def stock(a, b,*args):
    global money
    global cn
    global cc
    money = a
    cn = b
    cc = {}
    sto = args
    for i in sto:
        sql(i)

def sql(x):
    global money
    global cn
    global cc
    # hive 에서 조건에 맞는 회사 검색을 위한 sql 쿼리
    sql = f"""
    with TBL1 as (
    SELECT T1.STK_CD, T1.STK_NM, T1.EX_CD, T1.SEC_NM, H1.DT,H1.C_PRC,
    (T3.TOTAL_SHARES * H1.C_PRC / T4.FIN_ITM_VAL) AS PER,
    (H1.C_PRC / T3.BPS) AS PBR,
    (T3.TOTAL_SHARES * H1.C_PRC / T3.CF_{x}) AS PFCR,
    (T3.TOTAL_SHARES * H1.C_PRC /T5.FIN_ITM_VAL) AS PSR
    FROM STOCK T1
    INNER JOIN FINANCE_C T3
    ON (T1.STK_CD = T3.STK_CD)
    INNER JOIN (SELECT T2.STK_CD, T2.FIN_ITM_VAL
    FROM FINANCE_Y T2
    INNER JOIN FINANCE_C T3
    ON (T2.STK_CD = T3.STK_CD)
    WHERE T2.FIN_ITM_NM = '당기순이익'
    AND T2.YY = '{x}'
    ) T4
    ON (T1.STK_CD = T4.STK_CD)
    INNER JOIN (SELECT T2.STK_CD, T2.FIN_ITM_VAL
    FROM FINANCE_Y T2
    INNER JOIN FINANCE_C T3
    ON (T2.STK_CD = T3.STK_CD)
    WHERE T2.FIN_ITM_NM = '매출액'
    AND T2.YY = '{x}'
    ) T5
    ON (T1.STK_CD = T5.STK_CD)
    INNER JOIN HISTORY_DT H1
    ON (T1.STK_CD = H1.STK_CD)
    WHERE H1.DT = (SELECT Max(T1.DT)
                    FROM HISTORY_DT T1
                    WHERE T1.DT <'{yy(x)}')
    ), TBL2 AS (
        SELECT *,
    rank() over ( partition by sec_nm order by per Asc) as PER_R,
    RANK() OVER(ORDER BY PBR ASC) as PBR_R,
    RANK() OVER(ORDER BY PFCR ASC) as PFCR_R,
    RANK() OVER(ORDER BY PSR ASC) as PSR_R
        FROM TBL1
        WHERE PER is not null
        AND PER > 0
        AND PBR is not null
        AND PBR > 0
        AND PFCR is not null
        AND PFCR > 0 
        AND PSR is not null
    ), TBL3 AS (
        SELECT *,RANK() OVER(ORDER BY (PER_R + PBR_R +PFCR_R +PSR_R)/4 ASC) AS TOTAL_R
    from TBL2
    )
     select * from tbl3 t3
     where T3.EX_CD = 'KD'
     order by TOTAL_R
    """
    cursor.execute(sql)
    result = cursor.fetchall()
    desc = cursor.description
    # 데이터프레임으로 만들기
    column_name = list( zip(*desc) )[0]
    column_name = [element.upper() for element in column_name]
    column_name = [re.sub('\S*\.',"",element)  for element in column_name]
    df = pd.DataFrame(result)
    df.columns = column_name
    df["DT"] = pd.to_datetime(df["DT"])
    df["C_PRC"] = df["C_PRC"].astype("int64")
    # 조건에 맞는 상위권 cn개의 회사들을 list로 저장
    c_list = list(df['STK_CD'][:cn])
    
#     print(c_list)
#     df.to_csv(f'./result_{x}.csv',encoding='utf-8',index=False)
    
    # 선정된 회사의 주식 정보를 불러 오기 위해 데이터 재검색
    sql = f"""
    SELECT H1.STK_CD, H1.DT, H1.C_PRC
    FROM HISTORY_DT H1
    WHERE H1.DT = (SELECT Max(T1.DT)
                    FROM HISTORY_DT T1
                    WHERE T1.DT <'{yy(x)}')
    """
    cursor.execute(sql)
    result = cursor.fetchall()
    desc = cursor.description
    column_name = list( zip(*desc) )[0]
    column_name = [element.upper() for element in column_name]
    column_name = [re.sub('\S*\.',"",element)  for element in column_name]
    df = pd.DataFrame(result)
    df.columns = column_name
    df["DT"] = pd.to_datetime(df["DT"])
    df["C_PRC"] = df["C_PRC"].astype("int64")
    
    
    # 현재 구매된 회사와 저평가 된 회사 리스트 비교
    # cc : 현재 보유한 주식 회사 리스트
    # 보유한 주식 회사가 저평가 된 회사 리스트에 있으면 tmp_cc 리스트에 임시저장
    tmp_cd = []
    tmp_cc = {}
    for i in c_list:
        if i in cc:
            tmp_cc[i]=cc[i]
            del cc[i]
        else:
            tmp_cd.append(i)
    
    # 현재 구매된 회사주식이 저평가된 회사 리스트에 없으면 판매 후 저평가된 회사 주식 구매.
    # 저평가된 회수 주식은 판매 후 남은 가격으로 균등 구매
    
    if len(cc.keys()) !=0:
        lcn = len(cc.keys())
    else:
        lcn = cn
    for j in cc.keys():
        money = money + int(cc[j]*df['C_PRC'][df['STK_CD'] == j])
    mn = int(money/lcn)
    for k in tmp_cd:
        tmp_cc[k]=int(mn/df['C_PRC'][df['STK_CD'] == k])
        money = money - int(tmp_cc[k]*df['C_PRC'][df['STK_CD'] == k])
    cc = tmp_cc
    
    # moneyt  = 현재 들고 있는 주식의 총가격
    moneyt = money
    for j in cc.keys():
        moneyt = moneyt + int(cc[j]*df['C_PRC'][df['STK_CD'] == j])
    print( x, moneyt)