[3월 1주차-3/5]파이썬과 MySQL을 활용한 주식 분석 시스템 구축하기

주식 시장에서 데이터를 분석하는 것은 투자 결정을 내리는 데 중요한 요소입니다. 이번 글에서는 파이썬(Python)과 MySQL을 활용하여 주식 데이터를 수집하고 분석하는 시스템을 구축하는 방법을 소개합니다.

---

1. 프로젝트 개요

이 프로젝트에서는 Yahoo Finance에서 주식 데이터를 가져와 MySQL에 저장한 후, SQL을 이용해 다양한 분석을 수행하는 과정을 다룹니다. 이를 통해 투자 의사결정을 돕는 데이터를 손쉽게 조회할 수 있도록 합니다.

📌 주요 기능

• 기업 목록을 MySQL 데이터베이스에서 가져오기
• Yahoo Finance API를 활용하여 주식 데이터 수집
• 수집한 데이터를 MySQL에 저장
• SQL을 이용한 데이터 분석 (52주 최고가 및 최저가 분석 등)

---

2. MySQL 데이터베이스 설정

먼저 MySQL 데이터베이스를 설정하고 필요한 테이블을 생성합니다.

create database us_stock;
use us_stock;

CREATE TABLE nasdaq_company(
    symbol VARCHAR(255),                -- 심벌이름
    company_name VARCHAR(255),          -- 기업이름
    country VARCHAR (255),              -- 기업국가
    ipo_year INT,                       -- IPO 년도
    sector VARCHAR(255),
    industry VARCHAR(255),              -- 산업군
    last_crawel_date_stock DATETIME,    -- 마지막 크롤링한 날짜
    is_delete VARCHAR(5),
    open DECIMAL(18,2),                 -- 시작가
    high DECIMAL(18,2),                 -- 장중 최고가
    low DECIMAL(18,2),                  -- 장중 최저가
    close DECIMAL(18,2),                -- 종가
    adj_close DECIMAL(18,2),            -- 시간외 종가
    volume BIGINT
);
alter table nasdaq_company add primary key(symbol);

CREATE TABLE stock(
    date DATETIME,              -- 주가 입력 날짜
    symbol VARCHAR(255),        -- 심벌이름
    open DECIMAL(18,2),         -- 시작가
    high DECIMAL(18,2),         -- 장중 최고가
    low DECIMAL(18,2),          -- 장중 최저가
    close DECIMAL(18,2),        -- 종가
    adj_close DECIMAL(18,2),    -- 시간외 종가
    volume BIGINT
);

create index ix_stock_1 on stock(date, symbol);
create index ix_stock_2 on stock(symbol, date);

/*
나스닥에서는 상장 또는 폐지 기업이 수시로 발생하기 때문에 주기적인 업데이트 필요.
*/

insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('TSLA', 'Tesla Inc. Common Stock', 2010, 'Capital Goods', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('MSFT', 'Microsoft Corporation Common Stock', 1986, 'Technology', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('AMZN', 'Amazon.com Inc. Common Stock', 1997, 'Consumer Services', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('AAPL', 'Apple Inc. Common Stock', 1980, 'Technology', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('INTC', 'Intel Corporation Common Stock', 1999, 'Technology', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('NVDA', 'NVIDIA Corporation Common Stock', 1999, 'Technology', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('AMD', 'Advanced Micro Devices Inc. Common Stock', 1999, 'Technology', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('META', 'Meta Platforms, Inc.', 2012, 'Technology', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('AMPG', 'AMPG, Inc.', 2012, '', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('CAR', 'CAR, Inc.', 2012, '', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('UAN', 'UAN, Inc.', 2012, '', 'K2');
insert into nasdaq_company (symbol, company_name, ipo_year, sector, industry) values ('BHR', 'BHR, Inc.', 2012, '', 'K2');

select * from nasdaq_company;
select * from stock;

 

---

3. Python을 활용한 주식 데이터 수집

📌 필요 라이브러리 설치

Python에서 Yahoo Finance 데이터를 가져오기 위해 yfinance 라이브러리를 사용합니다.

pip install yfinance

📌 주식 데이터 수집 코드

import yfinance as yf
import pandas as pd
import pymysql

from datetime import datetime, timedelta


# mysql에 연결
host = 'localhost'
user = 'root'
password = '*****'
db_name = 'us_stock'

mysql_conn = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=db_name,
                             charset='utf8')

# 실제 주식 데이터를 가져오는 함수 생성. getStock(시작날짜, 종료 날짜)
def getSock(_symbol, _start_date, _end_date):
    # MySQL 데이터베이스와 연결을 설정하고 커서를 생성
    mysql_cur = mysql_conn.cursor()
    
    # 수집하려는 날짜 범위 내 기존 데이터가 있는 경우 삭제하여 중복 저장을 방지.
    mysql_cur.execute("delete from us_stock.stock where date >= %s and date <= %s and symbol = %s", (_start_date, _end_date, _symbol))
    mysql_conn.commit()
    
    try:
        # yf.download() 함수를 사용하여 symbol, start_date, end_date를 설정하여 데이터를 가져옴
        stock_price = yf.download(_symbol, start=_start_date, end = _end_date)
        print(stock_price)
        
        # 가져온 데이터(DataFrame)를 한 행씩 반복하면서 개별 변수에 저장
        for index, row in stock_price.iterrows():
            _date = index.strftime("%Y-%m-%d")
            _open = float(row["Open"])
            _high = float(row["High"])
            _low = float(row["Low"])
            _close =float(row["Close"])
            _adj_close =float(333333)
            _volume = float(row["Volume"])
            
            mysql_cur.execute("insert into us_stock.stock (date, symbol, open, high, low, close, adj_close, volume) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", (_date, _symbol, _open, _high, _low, _close, _adj_close, _volume))
        mysql_conn.commit()
        
        # 마지막으로 가져온 데이터를 nasdaq_company 테이블에 업데이트하여 다음 크롤링 시 참조할 수 있도록 함
        mysql_cur.execute("update us_stock.nasdaq_company set open = %s, high = %s, low = %s, close = %s, adj_close = %s, volume = %s, last_crawel_date_stock = %s where symbol = %s", (_open, _high, _low, _close, _adj_close, _volume, _date, _symbol))
        mysql_conn.commit()
        
        
    except Exception as e:
        print('error for getStock(): ' + str(e))
        mysql_conn.commit()
        mysql_conn.close()
        
        return {'error for getStock(0) ': str(e)}


# 크롤링한 데이터를 DB로 읽어오는 함수 생성
def getCompany():
    # MySQL 데이터베이스와 연결을 설정하고 커서를 생성
    mysql_cur = mysql_conn.cursor()
    
    # 크롤링이 실행되는 날짜를 기준으로 today 변수를 정의하며, 크롤링 대상 데이터를 정확하게 조회하기 위해 +1일을 더해 설정
    today = datetime.today() + timedelta(days=1)
    
    try:
        # SQL 쿼리를 실행하여 크롤링할 기업 목록을 조회한 후 실행된 쿼리의 결과를 results 변수에 저장
        mysql_cur.execute("select symbol, company_name, ipo_year, last_crawel_date_stock from us_stock.nasdaq_company where is_delete is null;")
        results = mysql_cur.fetchall()
        print(results)
        
        # 조회된 데이터를 반복문을 통해 하나씩 읽으며 변수에 할당
        for row in results:
            _symbol = row[0]
            _company_name = row[1]
            
            # 기업이 nasdaq_company 테이블에 존재하지만 last_crawel_date_stock 값이 NULL인 경우
            # →해당 기업의 데이터를 처음 크롤링하는 것이므로 1970년부터 모든 데이터를 가져옴
            if row[2] is None or row[2] == 0:
                _ipo_year = '1970'
                
            else:
                _ipo_year = row[2]
            
            # last_crawel_date_stock 값이 존재하는 경우 
            # → 마지막 크롤링한 날짜부터 최신 데이터까지 가져오도록 설정
            if row[3] is None:
                _last_crawel_date_stock = str(_ipo_year) + '-01-01'
            else:
                _last_crawel_date_stock = row[3]
                
            print(_symbol)
            if "." in _symbol:
                print(_symbol)
            else:
                if "/" in _symbol:
                    print(_symbol)
                else:
                    getSock(_symbol, _last_crawel_date_stock, today.strftime("%Y-%m-%d"))
    
    except Exception as e:
        print('error for getCompany(): ' + str(e))
        mysql_conn.commit()
        mysql_conn.close()
        
        return {'error for getCompany() ': str(e)}

# 파일 실행할 때 처음 실행될 함수 
if __name__ == '__main__':
    getCompany()

위 코드를 실행하면 MySQL 데이터베이스에 최근 1년간의 주식 데이터가 저장됩니다.

---

4. SQL을 이용한 주가 분석

📌 52주 최고가 및 최저가 분석

이제 MySQL에서 52주 동안의 최저가, 최고가 및 변동률을 분석하는 SQL 쿼리를 실행해 보겠습니다.

-- 52주간의 주식 최저가와 최고가 조회
select
   symbol,
    CAST(MIN(close) as decimal(18,2)) as w52_min,
    CAST(MAX(close) as decimal(18,2)) as w52_max,
    CAST(MAX(close) - MIN(close) as decimal(18,2)) as 'w52_diff_price($)',
    CAST((MAX(close) - MIN(close)) / MIN(close) * 100 as decimal(18,2)) as 'w52_diff_ratio(%)'

from stock
where date >= DATE_ADD('2023-10-04', interval -52 week)
   and date <= '2023-10-04'
group by symbol;

이 쿼리는 각 주식의 52주 최저가, 최고가 및 변동률을 계산하여 변동성이 큰 종목을 찾는 데 활용할 수 있습니다.

---

📌 하루 동안의 종목 변화 분석하기

-- 1일간의 시작가와 종가를 비교한 정보 조회
SELECT
    symbol,
    CAST(open AS DECIMAL(18,2)) AS open,
    CAST(close AS DECIMAL(18,2)) AS close,
    CAST((open - close) AS DECIMAL(18,2)) AS 'diff_price($)',
    CAST(((close - open) / open * 100) AS DECIMAL(18,2)) AS 'diff_ratio(%)',
    '' AS '--',
    CAST(low AS DECIMAL(18,2)) AS low,
    CAST(high AS DECIMAL(18,2)) AS high,
    CAST((high - low) AS DECIMAL(18,2)) AS 'diff_high_price($)',
    CAST(((high - low) / low * 100) AS DECIMAL(18,2)) AS 'diff_high_ratio(%)'
FROM stock
WHERE date = '2023-10-04';

-- 하루동안 10% 이상 가격이 오른 종목 조회
select
    symbol,
    CAST(open AS DECIMAL(18,2)) AS open,
    CAST(close AS DECIMAL(18,2)) AS close,
    CAST((open - close) AS DECIMAL(18,2)) AS 'diff_price($)',
    CAST(((close - open) / open * 100) AS DECIMAL(18,2)) AS 'diff_ratio(%)',
    '' AS '--',
    CAST(low AS DECIMAL(18,2)) AS low,
    CAST(high AS DECIMAL(18,2)) AS high,
    CAST((high - low) AS DECIMAL(18,2)) AS 'diff_high_price($)',
    CAST(((high - low) / low * 100) AS DECIMAL(18,2)) AS 'diff_high_ratio(%)'
from stock
where
	date = '2022-02-24' and
    cast(((close-open)/open *100) as decimal(18,2)) >=10
order by cast(((close-open)/open *100) as decimal(18,2)) desc;

---

📌 전일 대비 종목의 변화 분석하기

-- 전일 대비 증감과 즘감률 조회
select
	a.symbol,
    a.date as a_date,
    cast(a.close as decimal(18,2)) as a_close,
    '' AS '--',
    b.date as b_date,
    cast(b.close as decimal(18,2)) as b_close,
    '' AS '--',
	CAST((b.close - a.close) AS DECIMAL(18,2)) AS 'diff_high_price($)',
    CAST(((b.close - a.close) / b.close * 100) AS DECIMAL(18,2)) AS 'diff_high_ratio(%)'
from stock as a
	inner join stock as b on a.symbol = b.symbol and a.date = date_add(b.date, interval -1 day)
where a.date = '2023-10-04';

---

📌 주가가 연속 상승한 종목 분석하기

-- 1. 특정 기간동안 종목별 등락을 저장하는 테이블
create temporary table temp1
select
	a.symbol,
    a.close as a_close,
    b.close as b_close,
    b.close - a.close as close_diff,
    (b.close - a.close) / a.close *100 as ratio_diff
    
from (select symbol, close from stock where date = '2021=02-17')as a
	inner join (select symbol, close from stock where date = '2021-02-24') as b
    on a.symbol = b.symbol;
    
select * from temp1;

-- 2. 10% 상승한 종목들의 정보를 저장하는 테이블 생성
create temporary table temp2
select
	row_number() over (partition by a.symbol order by date asc) as num,
	a.symbol,
    b.date,
    b.close
from temp1 as a
	inner join stock as b on a.symbol = b.symbol
where a.ratio_diff>=10
	and b.date >= '2021-02-17'
    and b.date <= '2021-02-24';

create temporary table temp2_1
select
	row_number() over (partition by a.symbol order by date asc) as num,
	a.symbol,
    b.date,
    b.close
from temp1 as a
	inner join stock as b on a.symbol=b.symbol
where a.ratio_diff >=10
	and b.date >= '2021-02-17'
    and b.date <= '2021-02-24';
    
select * from temp2;

-- 3. symbol 열을 기준으로 전일 데이터 전일 대비 상승한 종목 데이터를 저장하는 테이블
create temporary table temp3
select
	b.symbol,
    a.date as a_date,
    a.close as a_close,
    b.date as b_date,
    b.close as b_close,
    b.close - a.close as close_diff,
    (b.close - a.close) / a.close *100 as ratio_diff
from temp2 as a
	inner join temp2_1 as b on a.symbol=b.symbol and a.num=b.num-1
order by b.symbol, b.date;

select * from temp3;

-- 4. 주가가 한 번도 하락하지 않은 데이터를 저장하는 테이블 생성
create temporary table temp3_1
select symbol from temp3 where ratio_diff < 0 group by symbol;

create temporary table temp4
select
	symbol,
    a_date,
    round(a_close,2) as a_close,
    b_date,
    round(b_close,2) as b_close,
    round(close_diff,2) as close_diff,
    round(ratio_diff,2) as ratio_diff
from temp3
where symbol not in (select symbol from temp3_1);

select * from temp4;

-- 5. nasdaq_company 테이블과 임시 테이블을 조인해 최종 정보 출력
select
	a.symbol,
    d.company_name,
    d.industry,
    round(a.a_close,2) as a_close,
    round(a.b_close,2) as b_close,
    round(a.close_diff,2) as close_diff,
    round(a.ratio_diff,2) as ratio_diff
from temp1 as a
	inner join (select symbol from temp2 group by symbol) as b on a.symbol = b.symbol
	inner join (select symbol from temp4 group by symbol) as c on a.symbol = c.symbol
	inner join nasdaq_company as d on a.symbol = d.symbol
order by ratio_diff desc;

 

---

6. 결론

이번 글에서는 파이썬과 MySQL을 활용한 주식 데이터 분석 시스템을 구축하는 방법을 소개했습니다. 이 시스템을 통해 주식 데이터를 효율적으로 수집하고 분석할 수 있으며, 다양한 확장 기능을 추가하여 더욱 정교한 분석이 가능합니다. 📌

 

7. python으로,,,

import pandas as pd
import pymysql
from datetime import datetime, timedelta

# mysql에 연결
host = 'localhost'
user = 'root'
password = 'rubi'
db_name = 'us_stock'

conn = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=db_name,
                             charset='utf8')

# cursor 생성
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 쿼리 작성해서 원하는 데이터 선택
query = """ select * from stock; """

# execute()로 query 실행
cursor.execute(query)
execute_result = cursor.fetchall()
stock = pd.DataFrame(execute_result)
conn.close()


# 주어진 날짜를 기준으로 지난 52주 동안의 최고가와 최저가를 반환하는 함수
stock.head()
stock.info()

cols_to_convert = ['open', 'high', 'low', 'close', 'adj_close']
stock[cols_to_convert] = stock[cols_to_convert].astype(float)

stock['diff_price'] = stock['close']-stock['open']
stock['diff_ratio'] = round((stock['close']-stock['open']) / stock['open'] * 100, 2)

def get_52_week_high_low(stock, date):
    target_date = pd.to_datetime(date)
    
    start_date = target_date - pd.DateOffset(weeks=52)
    
    # 52주 기간 내 데이터 필터링
    filtered_df = stock[(stock['date'] >= start_date) & (stock['date'] <= target_date)]
    
    # 종목(symbol)별 52주 최고가 및 최저가 계산
    result = filtered_df.groupby('symbol').agg(
        high_52_week=('high', 'max'),
        low_52_week=('low', 'min')
    ).reset_index()
    
    return result


result = get_52_week_high_low(stock, '2025-03-04')
print(result)


# 하루동안 10% 이상 가격이 오른 종목 시작가와 종가를 비교한 정보 조회
def get_day_open_close_diff(stock, date):
    target_date = pd.to_datetime(date)
    
    filtered_df = stock[(stock['date'] == target_date) & (stock['diff_ratio'] >= 10)]
    
    # 종목별 시작가(open), 종가(close), 가격 차이(diff) 계산
    result = filtered_df[['symbol', 'open', 'close', 'diff_price', 'diff_ratio']]
    
    return result

result = get_day_open_close_diff(stock, '2022-02-24')
print(result)

# 전일 대비 종목의 변화 분석하기
def get_previous_day_stock_change(stoc, date):
    target_date = pd.to_datetime(date)
    previous_date = target_date + pd.DateOffset(days=1)
    
    df1 = stock[['date', 'symbol', 'close']][stock['date'] == date]
    df2 = stock[['date','symbol', 'close']][stock['date'] == previous_date]
    merged_df = pd.merge(df1, df2, on='symbol', suffixes=('_a', '_b'))

    merged_df['diff_price'] = merged_df['close_b'] - merged_df['close_a']
    merged_df['diff_ratio'] = (merged_df['diff_price'] / merged_df['close_b']) * 100
    
    return merged_df
    
result = get_previous_day_stock_change(stock, '2023-10-04')
print(result)

# 주가가 1주일 동안 연속적으로 상승한 종목 분석
def get_continuous_price_increase_stocks(stock, date):
    target_date = pd.to_datetime(date)
    start_date = target_date - pd.DateOffset(weeks=1)
    
    filtered_df = stock[(stock['date'] >= start_date) & (stock['date'] <= target_date)]
    
    continuous_rise_stocks = []

    for symbol, group in filtered_df.groupby('symbol'): 
        group['price_increase'] = group['close'].diff() > 0
        
        if group['price_increase'].sum() == 5:
            continuous_rise_stocks.append(symbol)
    
    filtered_df.isin(continuous_rise_stocks)
    result = filtered_df[filtered_df['symbol'].isin(continuous_rise_stocks)].groupby('symbol').agg(start_close=('close', 'min'), close = ('close', 'max'))

    return result

result = get_continuous_price_increase_stocks(stock, '2021-02-24')
print(result)