[데이터 수집] 주식 데이터 수집(특정 시간 단위로)

회사를 다니면서 주식을 하기는 신경 써야 할 사항들이 많이 있습니다.

틈틈히 주가가 오르거나 내리거나 하는것을 신경 쓰기가 참 힘들죠~

 

오늘은 위 상황을 해결해 하기 위해 아래와 같은 단계로 문제를 해결 하고자 합니다.

 

1. 종목 데이터 수집(특정 시간 단위로)

2. 예상 금액 범위에 도달 했을때 알림 메세지 보내기(텔레그램)

3. 분석(어떤 방법으로 분석할지 아직 모름)

 

[종목 데이터 수집(특정 시간 단위로)]

1. 저장할 테이블 생성

DROP TABLE IF EXISTS dbinc.TB_STOCK_PRICE; CREATE TABLE dbinc.TB_STOCK_PRICE ( SEQ INT auto_increment NOT NULL COMMENT '시퀀스', NAME VARCHAR(100) NOT NULL COMMENT '주식명', CODE CHAR(6) NOT NULL COMMENT '코드', CURRENT_PRICE INT NULL COMMENT '현재금액', PRICE_CHANGE INT NULL COMMENT '변동금액', RATE_CHANGE FLOAT NULL COMMENT '변동률', PREV_CLOSE_PRICE INT NOT NULL COMMENT '이전종가', PRIMARY KEY (SEQ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

2. python으로 테이블에 데이터 입력

 -> python으로 네이버증권 사이트를 스크랩핑하는 소스(chatgpt 활용) 

import requests from bs4 import BeautifulSoup from time import sleep import certifi import warnings from urllib3.exceptions import InsecureRequestWarning from datetime import datetime, time import mariadb  # MariaDB connector for Python # Suppress only the InsecureRequestWarning from urllib3 warnings.simplefilter('ignore', InsecureRequestWarning) # MariaDB database connection details db_config = {       'user': 'root',     'password': 'test1',     'host': 'localhost',     'port': 3306,     'database': 'stock' } # List of stock codes for Naver Finance finance_codes = ["005930", "012030"]  # Samsung Electronics # Function to connect to the MariaDB database def get_db_connection():     try:         return mariadb.connect(**db_config)     except mariadb.Error as e:         print(f"Error connecting to MariaDB Platform: {e}")         return None # Function to insert stock data into the database def insert_stock_data(stock_name, stock_code, current_price, price_change, rate_change, prev_close_price):     db_conn = get_db_connection()     if db_conn is None:         return     try:         cursor = db_conn.cursor()         # SQL query to insert data into the table         sql_insert_query = """             INSERT INTO TB_STOCK_PRICE (NAME, CODE, CURRENT_PRICE, PRICE_CHANGE, RATE_CHANGE, PREV_CLOSE_PRICE)             VALUES (?, ?, ?, ?, ?, ?)         """         # Execute the SQL command         cursor.execute(sql_insert_query, (stock_name, stock_code, current_price, price_change, rate_change, prev_close_price))         # Commit the transaction         db_conn.commit()         cursor.close()         db_conn.close()         # print(f"Inserted {stock_name} data into database successfully.")     except mariadb.Error as err:         print(f"Error: {err}") def get_stock_info(stock_code):     try:         # URL for the given stock code on Naver Finance         naver_finance_url = f"https://finance.naver.com/item/main.naver?code={stock_code}"         # print(certifi.where())         # Send a GET request to Naver Finance using certifi's CA bundle         response = requests.get(naver_finance_url, verify=False)         response.raise_for_status()  # Check if the request was successful         # Parse the HTML response using BeautifulSoup         soup = BeautifulSoup(response.text, 'html.parser')         # Extract the stock name         stock_name_tag = soup.find('div', class_='wrap_company').find('h2')         stock_name = stock_name_tag.text.strip() if stock_name_tag else "Unknown Stock"         # Extract the current stock price         price_tag = soup.find('p', class_='no_today').find('span', class_='blind')         current_price = int(price_tag.text.strip().replace(",", "")) if price_tag else "N/A"         # Extract the previous day's closing price         prev_close_tag = soup.find('td', class_='first').find('span', class_='blind')         prev_close_price = int(prev_close_tag.text.strip().replace(",", "")) if prev_close_tag else "N/A"         # Extract the price difference (increase/decrease amount)         price_change_tag = soup.find('p', class_='no_exday').find_all('span', class_='blind')[0]         price_change = int(price_change_tag.text.strip().replace(",", "")) if price_change_tag else "N/A"         # # Extract the rate of change         rate_change_tag = soup.find('p', class_='no_exday').find_all('span', class_='blind')[1]         rate_change = rate_change_tag.text.strip() if rate_change_tag else "N/A"         return stock_name, current_price, prev_close_price, price_change, rate_change     except requests.exceptions.RequestException as req_err:         print(f"Request error for stock {stock_code}: {req_err}")     except AttributeError as attr_err:         print(f"Parsing error for stock {stock_code}: Could not find the necessary HTML element. {attr_err}")     except Exception as e:         print(f"An error occurred for stock {stock_code}: {e}")     return "Unknown Stock", "N/A", "N/A", "N/A" def main():     start_time = time(9, 0)   # 09:00     end_time = time(15, 30)   # 15:30     print(datetime.now().time())     while start_time <= datetime.now().time() <= end_time:         # Get the current viewing time         current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")         print(f"\nViewing Time: {current_time}")         for code in finance_codes:             stock_name, current_price, prev_close_price, price_change, rate_change = get_stock_info(code)             if stock_name != "Unknown Stock":                 price_sign = "+" if price_change >= 0 else ""                 # current_price = f"{current_price:,}"                 print(f"{stock_name}, {current_price:,}원, {prev_close_price:,}원, {price_sign}{price_change} ({rate_change})")                                   # Insert data into the database                 insert_stock_data(stock_name, code, current_price, price_change, rate_change, prev_close_price)             else:                 print(f"Failed to retrieve stock information for code {code}.")         # Wait for 1 minute before the next update         sleep(60) if __name__ == "__main__":     main()

 

2.1. 종목 코드는 검색해서 찾아서 넣으면 됨

 

 

2.2. 특정 시간(09:00 ~ 15:30)만 데이터 수집

2.3. 실행시 백그라운드로 실행(pythonw get_stock_price.py) 

2.4. 데이터 조회

 

혹시 해당 파일을 실행했을 때 오류가 있거나 하면 댓글 달아 주세요~^^

주식_테이블 생성 및 조회.sql

0.00MB

get_stock_price.py

0.01MB