data_korea.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d8a9bcab",
   "metadata": {},
   "source": [
    "# 국내 주식 데이터 수집\n",
    "\n",
    "이번 장에서는 극내 주식 데이터 중 주식티커와 섹터별 구성종목 및 퀀트 투자를 위한 핵심 데이터인 수정주가, 재무제표, 가치지표를 크롤링하는 방법을 알아보겠다.\n",
    "\n",
    "## 최근 영업일 기준 데이터 받기\n",
    "\n",
    "POST 방식으로 금융 데이터를 제공하는 일부 사이트에서는 쿼리 항목에 특정 날짜를 입력하면(예:20221230) 해당일의 데이터를 다운로드 할 수 있으며, 최근 영업일 날짜를 입력하면 가장 최근의 데이터를 받을 수 있다. 따라서 최근 영업일에 해당하는 항목을 매번 수기로 입력하기 보다는 자동으로 반영되게 할 필요가 있다. \n",
    "\n",
    "네이버 금융의 [국내증시 → 증시자금동향]에는 이전 2영업일에 해당하는 날짜가 있으며, 자동으로 날짜가 업데이트된다. 따라서 해당 부분을 크롤링 한후 날짜에 해당하는 쿼리 항목에 사용하면 된다.\n",
    "\n",
    "```\n",
    "https://finance.naver.com/sise/sise_deposit.nhn\n",
    "```\n",
    "\n",
    "```{figure} image/data_korea/recent_date.png\n",
    "---\n",
    "name: recent_date\n",
    "---\n",
    "최근 영업일 부분\n",
    "```\n",
    "\n",
    "개발자도구 화면을 이용해 해당 데이터가 있는 부분을 확인해보면 [클래스가 subtop_sise_graph2인 div 태그 → 클래스가 subtop_chart_note인 ul 태그 → li 태그 → 클래스가 tah인 span 태그]에 위치해 있다는 걸 알 수 있다. 이를 이용해 해당 데이터를 크롤링한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b47519bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  |  2022.08.03\n"
     ]
    }
   ],
   "source": [
    "import requests as rq\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "url = 'https://finance.naver.com/sise/sise_deposit.nhn'\n",
    "data = rq.get(url)\n",
    "data_html = BeautifulSoup(data.content)\n",
    "parse_day = data_html.select_one(\n",
    "    'div.subtop_sise_graph2 > ul.subtop_chart_note > li > span.tah').text\n",
    "\n",
    "print(parse_day)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a97113ed",
   "metadata": {},
   "source": [
    "1. 페이지 주소를 입력한다.\n",
    "2. `get()` 함수를 통해 해당 페이지 내용을 받아온다.\n",
    "3. `BeautifulSoup()` 함수를 이용해 해당 페이지의 HTML 내용을 BeautifulSoup 객체로 만든다.\n",
    "4. `select_one()` 메서드를 통해 해당 태그의 데이터를 추출하며, `text` 메서드를 이용해 텍스트 데이터만을 추출한다.\n",
    "\n",
    "위 과정을 통해 **|  yyyy.mm.dd** 형식의 데이터가 선택된다. 이 중 숫자 부분만을 뽑아 **yyyymmdd** 형태로 만들어주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "06f3fcb7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20220803\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "biz_day = re.findall('[0-9]+', parse_day)\n",
    "biz_day = ''.join(biz_day)\n",
    "\n",
    "print(biz_day)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f6ed493",
   "metadata": {},
   "source": [
    "1. `findall()` 메서드 내에 정규 표현식을 이용해 숫자에 해당하는 부분만을 추출한다. '[0-9]+' 는 모든 숫자를 의미하는 표현식이다.\n",
    "2. `join()` 함수를 통해 숫자를 합쳐준다.\n",
    "\n",
    "이를 통해 우리가 원하는 **yyyymmdd** 형태의 날짜가 만들어졌다. 해당 데이터를 최근 영업일이 필요한 곳에 사용하면 된다.\n",
    "\n",
    "## 한국거래소의 업종분류 현황 및 개별지표 크롤링\n",
    "\n",
    "주식 관련 데이터를 구하기 위해 가장 먼저 해야하는 일은 어떠한 종목들이 상장되어 있는가에 대한 정보를 구하는 것이다. 한국거래소에서 제공하는 업종분류 현황과 개별종목 지표 데이터를 이용하면 매우 간단하게 해당 정보를 수집할 수 있다.\n",
    "\n",
    "- KRX 정보데이터시스템 http://data.krx.co.kr/ 에서 [기본통계 → 주식 → 세부안내] 부분\n",
    "- [12025] 업종분류 현황: http://data.krx.co.kr/contents/MDC/MDI/mdiLoader/index.cmd?menuId=MDC0201020506\n",
    "- [12021] 개별종목: http://data.krx.co.kr/contents/MDC/MDI/mdiLoader/index.cmd?menuId=MDC0201020502\n",
    "\n",
    "해당 데이터들을 크롤링이 아닌 [Excel] 버튼을 클릭해 엑셀 파일로 받을 수도 있다. 그러나 매번 엑셀 파일을 다운로드하고 이를 불러오는 작업은 상당히 비효율적이며, 크롤링을 이용한다면 해당 데이터를 파이썬에서 바로 불러올 수 있다.\n",
    "\n",
    "### 업종분류 현황 크롤링\n",
    "\n",
    "먼저 업종분류 현황에 해당하는 페이지에 접속하여 F12를 눌러 개발자도구 화면을 열고 [다운로드] 버튼을 클릭한 후 [CSV]를 누른다. 개발자도구 화면의 [Network] 탭에는 {numref}`krx_1`와 같이 **generate.cmd**와 **download.cmd** 두 가지 항목이 생긴다.\n",
    "\n",
    "```{figure} image/data_korea/krx_1.png\n",
    "---\n",
    "name: krx_1\n",
    "---\n",
    "CSV 다운로드 시 개발자도구 화면\n",
    "```\n",
    "\n",
    "거래소에서 엑셀 혹은 CSV 데이터를 받는 과정은 다음과 같다.\n",
    "\n",
    "1. http://data.krx.co.kr/comm/fileDn/download_excel/download.cmd 에 원하는 항목을 쿼리로 발송하면 해당 쿼리에 해당하는 OTP(generate.cmd)를 받는다.\n",
    "2. 부여받은 OTP를 http://data.krx.co.kr/ 에 제출하면 이에 해당하는 데이터(download.cmd)를 다운로드한다.\n",
    "\n",
    "먼저 1번 단계를 살펴보자. [Headers] 탭의 'General' 항목 중 'Request URL' 부분이 원하는 항목을 제출할 주소다. [Payload] 탭의 Form Data에는 우리가 원하는 항목들이 적혀 있다. 이를 통해 POST 방식으로 데이터를 요청하면 OTP를 받음을 알 수 있다.\n",
    "\n",
    "```{figure} image/data_korea/krx_otp.png\n",
    "---\n",
    "name: krx_otp\n",
    "---\n",
    "OTP 생성 부분\n",
    "```\n",
    "\n",
    "다음으로 2번 단계를 살펴보자. 'General' 항목의 'Request URL'은 OTP를 제출할 주소다. 'Form Data'의 OTP는 1번 단계를 통해 부여받은 OTP에 해당한다. 이 역시 POST 방식으로 데이터를 요청하며 이를 통해 CSV 파일을 받아온다.\n",
    "\n",
    "```{figure} image/data_korea/krx_otp_2.png\n",
    "---\n",
    "name: krx_otp_2\n",
    "---\n",
    "OTP 제출 부분\n",
    "```\n",
    "\n",
    "위 과정 중 OTP를 받아오는 과정을 코드로 나타내면 다음과 같다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5ae28b80",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SkqvmXXK0SrilAazghYQaJj7CaiJA9Ue1WbAAO7jNzsRtSksuLS7Bnxpl86F7dAOkunw9BBwugQaSjGAcH15eSN3vF4ZiabecDu8sGdUJ+0tBgM+EFJCxYg3zco1gIgRZqIo4cIzoURnTI8+MmkJ4m8vFLhSKmM794gFu+ThsO31lY4woqehX8j6OlXFDcfHdV4NbYo4+D2Rwcfj24VnU3Zpq3ik/Dyw3FdyOXhJkBI=\n"
     ]
    }
   ],
   "source": [
    "import requests as rq\n",
    "from io import BytesIO\n",
    "import pandas as pd\n",
    "\n",
    "gen_otp_url = 'http://data.krx.co.kr/comm/fileDn/GenerateOTP/generate.cmd'\n",
    "gen_otp_stk = {\n",
    "    'mktId': 'STK',\n",
    "    'trdDd': biz_day,\n",
    "    'money': '1',\n",
    "    'csvxls_isNo': 'false',\n",
    "    'name': 'fileDown',\n",
    "    'url': 'dbms/MDC/STAT/standard/MDCSTAT03901'\n",
    "}\n",
    "headers = {'Referer': 'http://data.krx.co.kr/contents/MDC/MDI/mdiLoader'}\n",
    "otp_stk = rq.post(gen_otp_url, gen_otp_stk, headers=headers).text\n",
    "\n",
    "print(otp_stk)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a882c79",
   "metadata": {},
   "source": [
    "1. gen_otp_url 에 원하는 항목을 제출할 URL을 입력한다.\n",
    "2. 개발자도구 화면에 있는 쿼리 내용들을 딕셔너리 형태로 입력한다. 이 중 mktId의 'STK'는 코스피에 해당하며, 코스닥 데이터를 받고자 할 경우 'KSQ'를 입력하면 된다.\n",
    "3. 영업일을 뜻하는 trdDd에는 위에서 구한 최근 영업일 데이터를 입력한다.\n",
    "4. 헤더 부분에 리퍼러(Referer)를 추가한다. 리퍼러란 링크를 통해서 각각의 웹사이트로 방문할 때 남는 흔적이다. 거래소 데이터를 다운로드하는 과정을 살펴보면 첫 번째 URL에서 OTP를 부여받고, 이를 다시 두번째 URL에 제출했다. 그런데 이러한 과정의 흔적이 없이 OTP를 바로 두번째 URL에 제출하면 서버는 이를 로봇으로 인식해 데이터를 주지 않는다. 따라서 헤더 부분에 우리가 거쳐온 과정을 흔적으로 남겨야 데이터를 받을 수 있다. 이러한 리퍼러 주소는 개발자도구 화면에서도 확인할 수 있다({numref}`referer`).\n",
    "5. `post()` 함수를 통해 해당 URL에 쿼리를 전송하면 이에 해당하는 데이터를 받으며, 이 중 텍스트에 해당하는 내용만 불러온다.\n",
    "\n",
    "```{figure} image/data_korea/referer.png\n",
    "---\n",
    "name: referer\n",
    "---\n",
    "리퍼러 주소의 확인\n",
    "```\n",
    "\n",
    "위의 과정을 거쳐 생성된 OTP를 제출하면, 우리가 원하는 데이터를 다운로드할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c381353d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>업종명</th>\n",
       "      <th>종가</th>\n",
       "      <th>대비</th>\n",
       "      <th>등락률</th>\n",
       "      <th>시가총액</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>095570</td>\n",
       "      <td>AJ네트웍스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>서비스업</td>\n",
       "      <td>7970</td>\n",
       "      <td>-10</td>\n",
       "      <td>-0.13</td>\n",
       "      <td>373173691150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>006840</td>\n",
       "      <td>AK홀딩스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>16450</td>\n",
       "      <td>300</td>\n",
       "      <td>1.86</td>\n",
       "      <td>217922378450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>027410</td>\n",
       "      <td>BGF</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>4215</td>\n",
       "      <td>30</td>\n",
       "      <td>0.72</td>\n",
       "      <td>403446274065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282330</td>\n",
       "      <td>BGF리테일</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>유통업</td>\n",
       "      <td>184500</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.54</td>\n",
       "      <td>3188880657000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>138930</td>\n",
       "      <td>BNK금융지주</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>6640</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2164210033440</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드      종목명   시장구분   업종명      종가    대비   등락률           시가총액\n",
       "0  095570   AJ네트웍스  KOSPI  서비스업    7970   -10 -0.13   373173691150\n",
       "1  006840    AK홀딩스  KOSPI  기타금융   16450   300  1.86   217922378450\n",
       "2  027410      BGF  KOSPI  기타금융    4215    30  0.72   403446274065\n",
       "3  282330   BGF리테일  KOSPI   유통업  184500  1000  0.54  3188880657000\n",
       "4  138930  BNK금융지주  KOSPI  기타금융    6640     0  0.00  2164210033440"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "down_url = 'http://data.krx.co.kr/comm/fileDn/download_csv/download.cmd'\n",
    "down_sector_stk = rq.post(down_url, {'code': otp_stk}, headers=headers)\n",
    "sector_stk = pd.read_csv(BytesIO(down_sector_stk.content), encoding='EUC-KR')\n",
    "\n",
    "sector_stk.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e8c7d29",
   "metadata": {},
   "source": [
    "1. OTP를 제출할 URL을 down_url에 입력한다.\n",
    "2. `post()` 함수를 통해 위에서 부여받은 OTP 코드를 해당 URL에 제출한다.\n",
    "3. 받은 데이터의 content 부분을 `BytesIO()`를 이용해 바이너리스트림 형태로 만든 후, `read_csv()` 함수를 통해 데이터를 읽어온다. 해당 데이터는 EUC-KR 형태로 인코딩 되어 있으므로 이를 선언해준다.\n",
    "\n",
    "위 과정을 통해 sector_stk 변수에는 코스피 종목의 산업별 현황 데이터가 저장되었다. 코스닥 시장의 데이터도 동일한 과정을 통해 다운로드 받도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cee4bbf8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>업종명</th>\n",
       "      <th>종가</th>\n",
       "      <th>대비</th>\n",
       "      <th>등락률</th>\n",
       "      <th>시가총액</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>060310</td>\n",
       "      <td>3S</td>\n",
       "      <td>KOSDAQ</td>\n",
       "      <td>기계·장비</td>\n",
       "      <td>2995</td>\n",
       "      <td>-5</td>\n",
       "      <td>-0.17</td>\n",
       "      <td>138583181435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>054620</td>\n",
       "      <td>APS홀딩스</td>\n",
       "      <td>KOSDAQ</td>\n",
       "      <td>금융</td>\n",
       "      <td>11450</td>\n",
       "      <td>700</td>\n",
       "      <td>6.51</td>\n",
       "      <td>233513830450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>265520</td>\n",
       "      <td>AP시스템</td>\n",
       "      <td>KOSDAQ</td>\n",
       "      <td>반도체</td>\n",
       "      <td>17600</td>\n",
       "      <td>100</td>\n",
       "      <td>0.57</td>\n",
       "      <td>268953009600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>211270</td>\n",
       "      <td>AP위성</td>\n",
       "      <td>KOSDAQ</td>\n",
       "      <td>통신장비</td>\n",
       "      <td>12250</td>\n",
       "      <td>-150</td>\n",
       "      <td>-1.21</td>\n",
       "      <td>184758224000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>032790</td>\n",
       "      <td>BNGT</td>\n",
       "      <td>KOSDAQ</td>\n",
       "      <td>정보기기</td>\n",
       "      <td>4075</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>120352455875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드     종목명    시장구분    업종명     종가   대비   등락률          시가총액\n",
       "0  060310      3S  KOSDAQ  기계·장비   2995   -5 -0.17  138583181435\n",
       "1  054620  APS홀딩스  KOSDAQ     금융  11450  700  6.51  233513830450\n",
       "2  265520   AP시스템  KOSDAQ    반도체  17600  100  0.57  268953009600\n",
       "3  211270    AP위성  KOSDAQ   통신장비  12250 -150 -1.21  184758224000\n",
       "4  032790    BNGT  KOSDAQ   정보기기   4075    0  0.00  120352455875"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gen_otp_ksq = {\n",
    "    'mktId': 'KSQ',  # 코스닥 입력\n",
    "    'trdDd': biz_day,\n",
    "    'money': '1',\n",
    "    'csvxls_isNo': 'false',\n",
    "    'name': 'fileDown',\n",
    "    'url': 'dbms/MDC/STAT/standard/MDCSTAT03901'\n",
    "}\n",
    "otp_ksq = rq.post(gen_otp_url, gen_otp_ksq, headers=headers).text\n",
    "\n",
    "down_sector_ksq = rq.post(down_url, {'code': otp_ksq}, headers=headers)\n",
    "sector_ksq = pd.read_csv(BytesIO(down_sector_ksq.content), encoding='EUC-KR')\n",
    "\n",
    "sector_ksq.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "853220d8",
   "metadata": {},
   "source": [
    "코스피 데이터와 코스닥 데이터를 하나로 합친다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9bd1c9c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>업종명</th>\n",
       "      <th>종가</th>\n",
       "      <th>대비</th>\n",
       "      <th>등락률</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>095570</td>\n",
       "      <td>AJ네트웍스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>서비스업</td>\n",
       "      <td>7970</td>\n",
       "      <td>-10</td>\n",
       "      <td>-0.13</td>\n",
       "      <td>373173691150</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>006840</td>\n",
       "      <td>AK홀딩스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>16450</td>\n",
       "      <td>300</td>\n",
       "      <td>1.86</td>\n",
       "      <td>217922378450</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>027410</td>\n",
       "      <td>BGF</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>4215</td>\n",
       "      <td>30</td>\n",
       "      <td>0.72</td>\n",
       "      <td>403446274065</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282330</td>\n",
       "      <td>BGF리테일</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>유통업</td>\n",
       "      <td>184500</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.54</td>\n",
       "      <td>3188880657000</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>138930</td>\n",
       "      <td>BNK금융지주</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>6640</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2164210033440</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드      종목명   시장구분   업종명      종가    대비   등락률           시가총액       기준일\n",
       "0  095570   AJ네트웍스  KOSPI  서비스업    7970   -10 -0.13   373173691150  20220803\n",
       "1  006840    AK홀딩스  KOSPI  기타금융   16450   300  1.86   217922378450  20220803\n",
       "2  027410      BGF  KOSPI  기타금융    4215    30  0.72   403446274065  20220803\n",
       "3  282330   BGF리테일  KOSPI   유통업  184500  1000  0.54  3188880657000  20220803\n",
       "4  138930  BNK금융지주  KOSPI  기타금융    6640     0  0.00  2164210033440  20220803"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "krx_sector = pd.concat([sector_stk, sector_ksq]).reset_index(drop=True)\n",
    "krx_sector['종목명'] = krx_sector['종목명'].str.strip()\n",
    "krx_sector['기준일'] = biz_day\n",
    "\n",
    "krx_sector.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe6a1207",
   "metadata": {},
   "source": [
    "1. `concat()` 함수를 통해 두 데이터를 합쳐주며, `reset_index()` 메서드를 통해 인덱스를 리셋시킨다. 또한 `drop=True`를 통해 인덱스로 셋팅한 열을 삭제한다.\n",
    "2. 종목명에 공백에 있는 경우가 있으므로 `strip()` 메서드를 이용해 이를 제거해준다.\n",
    "3. 데이터의 기준일에 해당하는 [기준일] 열을 추가한다.\n",
    "\n",
    "### 개별종목 지표 크롤링\n",
    "\n",
    "개별종목 데이터를 크롤링하는 방법은 위와 매우 유사하며, 요청하는 쿼리 값에만 차이가 있다. 개발자도구 화면을 열고 [CSV] 버튼을 클릭해 어떠한 쿼리를 요청하는지 확인해보자.\n",
    "\n",
    "```{figure} image/data_korea/krx_2.png\n",
    "---\n",
    "name: krx_2\n",
    "---\n",
    "개별지표 OTP 생성 부분\n",
    "```\n",
    "\n",
    "OTP를 생성하는 부분인 [generate.cmd]를 확인해보면 [Payload] 탭의  'tboxisuCd_finder_stkisu0_6', 'isu_Cd', 'isu_Cd2' 등의 항목은 조회 구분의 개별추이 탭에 해당하는 부분이므로 우리가 원하는 전체 데이터를 받을 때는 필요하지 않은 요청값다. 이를 제외한 요청값을 산업별 현황 예제에 적용하면 해당 데이터 역시 손쉽게 다운로드할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "48749dd8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>종가</th>\n",
       "      <th>대비</th>\n",
       "      <th>등락률</th>\n",
       "      <th>EPS</th>\n",
       "      <th>PER</th>\n",
       "      <th>선행 EPS</th>\n",
       "      <th>선행 PER</th>\n",
       "      <th>BPS</th>\n",
       "      <th>PBR</th>\n",
       "      <th>주당배당금</th>\n",
       "      <th>배당수익률</th>\n",
       "      <th>기준일</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>060310</td>\n",
       "      <td>3S</td>\n",
       "      <td>2995</td>\n",
       "      <td>-5</td>\n",
       "      <td>-0.17</td>\n",
       "      <td>16.0</td>\n",
       "      <td>187.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>792.0</td>\n",
       "      <td>3.78</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>095570</td>\n",
       "      <td>AJ네트웍스</td>\n",
       "      <td>7970</td>\n",
       "      <td>-10</td>\n",
       "      <td>-0.13</td>\n",
       "      <td>1707.0</td>\n",
       "      <td>4.67</td>\n",
       "      <td>988.0</td>\n",
       "      <td>8.07</td>\n",
       "      <td>8075.0</td>\n",
       "      <td>0.99</td>\n",
       "      <td>270</td>\n",
       "      <td>3.39</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>006840</td>\n",
       "      <td>AK홀딩스</td>\n",
       "      <td>16450</td>\n",
       "      <td>300</td>\n",
       "      <td>1.86</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45961.0</td>\n",
       "      <td>0.36</td>\n",
       "      <td>200</td>\n",
       "      <td>1.22</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>054620</td>\n",
       "      <td>APS홀딩스</td>\n",
       "      <td>11450</td>\n",
       "      <td>700</td>\n",
       "      <td>6.51</td>\n",
       "      <td>1179.0</td>\n",
       "      <td>9.71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10088.0</td>\n",
       "      <td>1.14</td>\n",
       "      <td>100</td>\n",
       "      <td>0.87</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>265520</td>\n",
       "      <td>AP시스템</td>\n",
       "      <td>17600</td>\n",
       "      <td>100</td>\n",
       "      <td>0.57</td>\n",
       "      <td>3932.0</td>\n",
       "      <td>4.48</td>\n",
       "      <td>3562.0</td>\n",
       "      <td>4.94</td>\n",
       "      <td>12713.0</td>\n",
       "      <td>1.38</td>\n",
       "      <td>240</td>\n",
       "      <td>1.36</td>\n",
       "      <td>20220803</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드     종목명     종가   대비   등락률     EPS     PER  선행 EPS  선행 PER      BPS  \\\n",
       "0  060310      3S   2995   -5 -0.17    16.0  187.19     NaN     NaN    792.0   \n",
       "1  095570  AJ네트웍스   7970  -10 -0.13  1707.0    4.67   988.0    8.07   8075.0   \n",
       "2  006840   AK홀딩스  16450  300  1.86     NaN     NaN     NaN     NaN  45961.0   \n",
       "3  054620  APS홀딩스  11450  700  6.51  1179.0    9.71     NaN     NaN  10088.0   \n",
       "4  265520   AP시스템  17600  100  0.57  3932.0    4.48  3562.0    4.94  12713.0   \n",
       "\n",
       "    PBR  주당배당금  배당수익률       기준일  \n",
       "0  3.78      0   0.00  20220803  \n",
       "1  0.99    270   3.39  20220803  \n",
       "2  0.36    200   1.22  20220803  \n",
       "3  1.14    100   0.87  20220803  \n",
       "4  1.38    240   1.36  20220803  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests as rq\n",
    "from io import BytesIO\n",
    "import pandas as pd\n",
    "\n",
    "gen_otp_url = 'http://data.krx.co.kr/comm/fileDn/GenerateOTP/generate.cmd'\n",
    "gen_otp_data = {\n",
    "    'searchType': '1',\n",
    "    'mktId': 'ALL',\n",
    "    'trdDd': biz_day,\n",
    "    'csvxls_isNo': 'false',\n",
    "    'name': 'fileDown',\n",
    "    'url': 'dbms/MDC/STAT/standard/MDCSTAT03501'\n",
    "}\n",
    "headers = {'Referer': 'http://data.krx.co.kr/contents/MDC/MDI/mdiLoader'}\n",
    "otp = rq.post(gen_otp_url, gen_otp_data, headers=headers).text\n",
    "\n",
    "down_url = 'http://data.krx.co.kr/comm/fileDn/download_csv/download.cmd'\n",
    "krx_ind = rq.post(down_url, {'code': otp}, headers=headers)\n",
    "\n",
    "krx_ind = pd.read_csv(BytesIO(krx_ind.content), encoding='EUC-KR')\n",
    "krx_ind['종목명'] = krx_ind['종목명'].str.strip()\n",
    "krx_ind['기준일'] = biz_day\n",
    "\n",
    "krx_ind.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daa3e087",
   "metadata": {},
   "source": [
    "### 데이터 정리하기\n",
    "\n",
    "먼저 두 데이터에 공통으로 존재하지 않는 종목, 즉 하나의 데이터에만 존재하는 종목을 살펴보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "98080b1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['글로벌에스엠', '한국패러랠', '이지스밸류리츠', 'NH올원리츠', 'JTC', '맵스리얼티1', 'SK리츠', '엑세스바이오', '엘브이엠씨홀딩스', '에이리츠', '컬러레이', 'ESR켄달스퀘어리츠', '디앤디플랫폼리츠', 'SBI핀테크솔루션즈', '케이탑리츠', '크리스탈신소재', '코오롱티슈진', '로스웰', '모두투어리츠', '미투젠', '코람코더원리츠', '롯데리츠', '신한알파리츠', '네오이뮨텍', '프레스티지바이오파마', '골든센츄리', '미래에셋글로벌리츠', '이리츠코크렙', '제이알글로벌리츠', '신한서부티엔디리츠', 'NH프라임리츠', '미래에셋맵스리츠', '베트남개발1', '코람코에너지리츠', '윙입푸드', '마스턴프리미어리츠', '애머릿지', '오가닉티코스메틱', '한국ANKOR유전', '잉글우드랩', '바다로19호', '이스트아시아홀딩스', '씨케이에이치', '맥쿼리인프라', 'GRT', '헝셩그룹', '소마젠', '이지스레지던스리츠']\n"
     ]
    }
   ],
   "source": [
    "diff = list(set(krx_sector['종목명']).symmetric_difference(set(krx_ind['종목명'])))\n",
    "print(diff)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb425334",
   "metadata": {},
   "source": [
    "두 데이터의 종목명 열을 세트 형태로 변경한 후 `symmetric_difference()` 메서드를 통해 하나의 데이터에만 있는 종목을 살펴보면 위와 같다. 해당 종목들은 **선박펀드, 광물펀드, 해외종목** 등 일반적이지 않은 종목들이다. 다음으로 두 데이터를 합쳐준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f4bd8dfd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>업종명</th>\n",
       "      <th>종가</th>\n",
       "      <th>대비</th>\n",
       "      <th>등락률</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "      <th>EPS</th>\n",
       "      <th>PER</th>\n",
       "      <th>선행 EPS</th>\n",
       "      <th>선행 PER</th>\n",
       "      <th>BPS</th>\n",
       "      <th>PBR</th>\n",
       "      <th>주당배당금</th>\n",
       "      <th>배당수익률</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>095570</td>\n",
       "      <td>AJ네트웍스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>서비스업</td>\n",
       "      <td>7970</td>\n",
       "      <td>-10</td>\n",
       "      <td>-0.13</td>\n",
       "      <td>373173691150</td>\n",
       "      <td>20220803</td>\n",
       "      <td>1707.0</td>\n",
       "      <td>4.67</td>\n",
       "      <td>988.0</td>\n",
       "      <td>8.07</td>\n",
       "      <td>8075.0</td>\n",
       "      <td>0.99</td>\n",
       "      <td>270.0</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>006840</td>\n",
       "      <td>AK홀딩스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>16450</td>\n",
       "      <td>300</td>\n",
       "      <td>1.86</td>\n",
       "      <td>217922378450</td>\n",
       "      <td>20220803</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45961.0</td>\n",
       "      <td>0.36</td>\n",
       "      <td>200.0</td>\n",
       "      <td>1.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>027410</td>\n",
       "      <td>BGF</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>4215</td>\n",
       "      <td>30</td>\n",
       "      <td>0.72</td>\n",
       "      <td>403446274065</td>\n",
       "      <td>20220803</td>\n",
       "      <td>684.0</td>\n",
       "      <td>6.16</td>\n",
       "      <td>115.0</td>\n",
       "      <td>36.68</td>\n",
       "      <td>16393.0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>110.0</td>\n",
       "      <td>2.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282330</td>\n",
       "      <td>BGF리테일</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>유통업</td>\n",
       "      <td>184500</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.54</td>\n",
       "      <td>3188880657000</td>\n",
       "      <td>20220803</td>\n",
       "      <td>8547.0</td>\n",
       "      <td>21.59</td>\n",
       "      <td>12336.0</td>\n",
       "      <td>14.96</td>\n",
       "      <td>46849.0</td>\n",
       "      <td>3.94</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>1.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>138930</td>\n",
       "      <td>BNK금융지주</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>기타금융</td>\n",
       "      <td>6640</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2164210033440</td>\n",
       "      <td>20220803</td>\n",
       "      <td>2341.0</td>\n",
       "      <td>2.84</td>\n",
       "      <td>2778.0</td>\n",
       "      <td>2.39</td>\n",
       "      <td>28745.0</td>\n",
       "      <td>0.23</td>\n",
       "      <td>560.0</td>\n",
       "      <td>8.43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드      종목명   시장구분   업종명      종가    대비   등락률           시가총액       기준일  \\\n",
       "0  095570   AJ네트웍스  KOSPI  서비스업    7970   -10 -0.13   373173691150  20220803   \n",
       "1  006840    AK홀딩스  KOSPI  기타금융   16450   300  1.86   217922378450  20220803   \n",
       "2  027410      BGF  KOSPI  기타금융    4215    30  0.72   403446274065  20220803   \n",
       "3  282330   BGF리테일  KOSPI   유통업  184500  1000  0.54  3188880657000  20220803   \n",
       "4  138930  BNK금융지주  KOSPI  기타금융    6640     0  0.00  2164210033440  20220803   \n",
       "\n",
       "      EPS    PER   선행 EPS  선행 PER      BPS   PBR   주당배당금  배당수익률  \n",
       "0  1707.0   4.67    988.0    8.07   8075.0  0.99   270.0   3.39  \n",
       "1     NaN    NaN      NaN     NaN  45961.0  0.36   200.0   1.22  \n",
       "2   684.0   6.16    115.0   36.68  16393.0  0.26   110.0   2.61  \n",
       "3  8547.0  21.59  12336.0   14.96  46849.0  3.94  3000.0   1.63  \n",
       "4  2341.0   2.84   2778.0    2.39  28745.0  0.23   560.0   8.43  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kor_ticker = pd.merge(krx_sector,\n",
    "                      krx_ind,\n",
    "                      on=krx_sector.columns.intersection(\n",
    "                          krx_ind.columns).tolist(),\n",
    "                      how='outer')\n",
    "\n",
    "kor_ticker.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ec2f76b",
   "metadata": {},
   "source": [
    "`merge()` 함수는 on 조건을 기준으로 두 데이터를 하나로 합치며, `intersection()` 메서드를 이용해 공통으로 존재하는 [종목코드, 종목명, 종가, 대비, 등락률] 열을 기준으로 입력해준다. 또한 방법(how)에는 outer를 입력한다.\n",
    "\n",
    "마지막으로 일반적인 종목과 스팩, 우선주, 리츠, 기타 주식을 구분해주도록 한다.\n",
    "\n",
    "```{note}\n",
    "스팩(SPAC)이란 Special Purpose Acquisition Company의 약자로 기업인수를 목적으로 하는 페이퍼컴퍼니를 뜻한다. 대부분 증권사 주관으로 설립되며, 스팩이 먼저 투자자들의 자금을 모아 주식 시장에 상장이 되고 나면, 그 이후에 괜찮은 비상장기업을 찾아 합병하는 방식으로 최종 기업 인수가 이루어진다.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "dffd5930",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['엔에이치스팩19호' 'DB금융스팩10호' 'DB금융스팩8호' 'DB금융스팩9호' 'IBKS제12호스팩' 'IBKS제13호스팩'\n",
      " 'IBKS제16호스팩' 'IBKS제17호스팩' 'IBKS제18호스팩' 'SK5호스팩' 'SK6호스팩' '교보10호스팩'\n",
      " '교보11호스팩' '교보12호스팩' '교보9호스팩' '대신밸런스제10호스팩' '대신밸런스제11호스팩' '대신밸런스제12호스팩'\n",
      " '미래에셋대우스팩 5호' '미래에셋대우스팩3호' '미래에셋비전스팩1호' '삼성머스트스팩5호' '삼성스팩4호' '삼성스팩6호'\n",
      " '상상인제3호스팩' '신영스팩6호' '신영스팩7호' '신한제10호스팩' '신한제6호스팩' '신한제7호스팩' '신한제8호스팩'\n",
      " '신한제9호스팩' '에스케이증권7호스팩' '에이치엠씨제4호스팩' '에이치엠씨제5호스팩' '엔에이치스팩20호' '엔에이치스팩22호'\n",
      " '엔에이치스팩23호' '유안타제7호스팩' '유안타제8호스팩' '유진스팩6호' '유진스팩7호' '유진스팩8호' '이베스트스팩5호'\n",
      " '케이비제20호스팩' '케이비제21호스팩' '케이프이에스제4호' '키움제6호스팩' '하나금융14호스팩' '하나금융15호스팩'\n",
      " '하나금융16호스팩' '하나금융19호스팩' '하나금융20호스팩' '하나금융21호스팩' '하나금융22호스팩' '하나머스트7호스팩'\n",
      " '하이제6호스팩' '하이제7호스팩' '한국제10호스팩' '한화플러스제2호스팩']\n"
     ]
    }
   ],
   "source": [
    "print(kor_ticker[kor_ticker['종목명'].str.contains('스팩|제[0-9]+호')]['종목명'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2af9a060",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['BYC우' 'CJ4우(전환)' 'CJ씨푸드1우' 'CJ우' 'CJ제일제당 우' 'DB하이텍1우' 'DL우'\n",
      " 'DL이앤씨2우(전환)' 'DL이앤씨우' 'GS우' 'JW중외제약2우B' 'JW중외제약우' 'LG생활건강우' 'LG우'\n",
      " 'LG전자우' 'LG화학우' 'LX하우시스우' 'LX홀딩스1우' 'NH투자증권우' 'NPC우' 'S-Oil우' 'SK네트웍스우'\n",
      " 'SK디스커버리우' 'SK우' 'SK이노베이션우' 'SK증권우' 'SK케미칼우' '계양전기우' '금강공업우' '금호건설우'\n",
      " '금호석유우' '깨끗한나라우' '남선알미우' '남양유업우' '넥센우' '넥센타이어1우B' '노루페인트우' '노루홀딩스우'\n",
      " '녹십자홀딩스2우' '대교우B' '대덕1우' '대덕전자1우' '대상우' '대상홀딩스우' '대신증권2우B' '대신증권우'\n",
      " '대원전선우' '대한제당우' '대한항공우' '덕성우' '동부건설우' '동양2우B' '동양우' '동원시스템즈우' '두산2우B'\n",
      " '두산우' '두산퓨얼셀1우' '두산퓨얼셀2우B' '롯데지주우' '롯데칠성우' '미래에셋증권2우B' '미래에셋증권우' '부국증권우'\n",
      " '삼성SDI우' '삼성물산우B' '삼성전기우' '삼성전자우' '삼성중공우' '삼성화재우' '삼양사우' '삼양홀딩스우' '서울식품우'\n",
      " '성문전자우' '성신양회우' '세방우' '솔루스첨단소재1우' '솔루스첨단소재2우B' '신영증권우' '신풍제약우'\n",
      " '아모레G3우(전환)' '아모레G우' '아모레퍼시픽우' '유안타증권우' '유유제약1우' '유유제약2우B' '유한양행우'\n",
      " '유화증권우' '일양약품우' '진흥기업2우B' '진흥기업우B' '코리아써우' '코리아써키트2우B' '코오롱글로벌우' '코오롱우'\n",
      " '코오롱인더우' '크라운제과우' '크라운해태홀딩스우' '태양금속우' '태영건설우' '티와이홀딩스우' '하이트진로2우B'\n",
      " '하이트진로홀딩스우' '한국금융지주우' '한양증권우' '한진칼우' '한화3우B' '한화솔루션우' '한화우' '한화투자증권우'\n",
      " '현대건설우' '현대비앤지스틸우' '현대차2우B' '현대차3우B' '현대차우' '호텔신라우' '흥국화재2우B' '흥국화재우'\n",
      " '대호특수강우' '루트로닉3우C' '소프트센우' '해성산업1우']\n"
     ]
    }
   ],
   "source": [
    "print(kor_ticker[kor_ticker['종목코드'].str[-1:] != '0']['종목명'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "91377cd4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ESR켄달스퀘어리츠' 'NH올원리츠' 'NH프라임리츠' 'SK리츠' '디앤디플랫폼리츠' '롯데리츠' '마스턴프리미어리츠'\n",
      " '모두투어리츠' '미래에셋글로벌리츠' '미래에셋맵스리츠' '신한서부티엔디리츠' '신한알파리츠' '에이리츠' '이지스레지던스리츠'\n",
      " '이지스밸류리츠' '제이알글로벌리츠' '케이탑리츠' '코람코더원리츠' '코람코에너지리츠']\n"
     ]
    }
   ],
   "source": [
    "print(kor_ticker[kor_ticker['종목명'].str.endswith('리츠')]['종목명'].values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5464ccf6",
   "metadata": {},
   "source": [
    "1. 스팩 종목은 종목명에 '스팩' 혹은 '제n호' 라는 단어가 들어간다. 따라서 `contains()` 메서드를 통해 종목명에 '스팩'이 들어가거나 정규 표현식을 이용해 '제n호'라는 문자가 들어간 종목명을 찾는다.\n",
    "2. 국내 종목 중 종목코드 끝이 0이 아닌 종목은 우선주에 해당한다.\n",
    "3. 리츠 종목은 종목명이 '리츠'로 끝난다. 따라서 `endswith()` 메서드를 통해 이러한 종목을 찾는다. (메리츠화재 등의 종목도 중간에 리츠라는 단어가 들어가므로 `contains()` 함수를 이용하면 안된다.)\n",
    "\n",
    "해당 종목들을 구분하여 표기해주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "260e57a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>종가</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "      <th>EPS</th>\n",
       "      <th>선행EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>주당배당금</th>\n",
       "      <th>종목구분</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>095570</td>\n",
       "      <td>AJ네트웍스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>7970</td>\n",
       "      <td>373173691150</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>1707.0</td>\n",
       "      <td>988.0</td>\n",
       "      <td>8075.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>006840</td>\n",
       "      <td>AK홀딩스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>16450</td>\n",
       "      <td>217922378450</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>45961.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>027410</td>\n",
       "      <td>BGF</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>4215</td>\n",
       "      <td>403446274065</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>684.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>16393.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282330</td>\n",
       "      <td>BGF리테일</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>184500</td>\n",
       "      <td>3188880657000</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>8547.0</td>\n",
       "      <td>12336.0</td>\n",
       "      <td>46849.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>138930</td>\n",
       "      <td>BNK금융지주</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>6640</td>\n",
       "      <td>2164210033440</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>2341.0</td>\n",
       "      <td>2778.0</td>\n",
       "      <td>28745.0</td>\n",
       "      <td>560.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드      종목명   시장구분      종가           시가총액        기준일     EPS    선행EPS  \\\n",
       "0  095570   AJ네트웍스  KOSPI    7970   373173691150 2022-08-03  1707.0    988.0   \n",
       "1  006840    AK홀딩스  KOSPI   16450   217922378450 2022-08-03    None     None   \n",
       "2  027410      BGF  KOSPI    4215   403446274065 2022-08-03   684.0    115.0   \n",
       "3  282330   BGF리테일  KOSPI  184500  3188880657000 2022-08-03  8547.0  12336.0   \n",
       "4  138930  BNK금융지주  KOSPI    6640  2164210033440 2022-08-03  2341.0   2778.0   \n",
       "\n",
       "       BPS   주당배당금 종목구분  \n",
       "0   8075.0   270.0  보통주  \n",
       "1  45961.0   200.0  보통주  \n",
       "2  16393.0   110.0  보통주  \n",
       "3  46849.0  3000.0  보통주  \n",
       "4  28745.0   560.0  보통주  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "kor_ticker['종목구분'] = np.where(kor_ticker['종목명'].str.contains('스팩|제[0-9]+호'), '스팩',\n",
    "                              np.where(kor_ticker['종목코드'].str[-1:] != '0', '우선주',\n",
    "                                       np.where(kor_ticker['종목명'].str.endswith('리츠'), '리츠',\n",
    "                                                np.where(kor_ticker['종목명'].isin(diff),  '기타',\n",
    "                                                '보통주'))))\n",
    "kor_ticker = kor_ticker.reset_index(drop=True)\n",
    "kor_ticker.columns = kor_ticker.columns.str.replace(' ', '')\n",
    "kor_ticker = kor_ticker[['종목코드', '종목명', '시장구분', '종가',\n",
    "                         '시가총액', '기준일', 'EPS', '선행EPS', 'BPS', '주당배당금', '종목구분']]\n",
    "kor_ticker = kor_ticker.replace({np.nan: None})\n",
    "kor_ticker['기준일'] = pd.to_datetime(kor_ticker['기준일'])\n",
    "\n",
    "kor_ticker.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fef8a3ab",
   "metadata": {},
   "source": [
    "1. numpy 패키지의 `where()` 함수를 통해 각 조건에 맞는 종목구분을 입력한다. 종목명에 '스팩' 혹은 '제n호'가 포함된 종목은 스팩으로, 종목코드 끝이 0이 아닌 종목은 '우선주'로, 종목명이 '리츠'로 끝나는 종목은 '리츠'로, 선박펀드, 광물펀드, 해외종목 등은 '기타'로, 나머지 종목들은 '보통주'로 구분한다.\n",
    "2. `reset_index()` 메서드를 통해 인덱스를 초기화 한다.\n",
    "3. `replace()` 메서드를 통해 열 이름의 공백을 삭제한다.\n",
    "4. 필요한 열만 선택한다.\n",
    "5. SQL에는 NaN이 입력되지 않으므로, None으로 변경한다.\n",
    "6. 기준일을 `to_datetime()` 메서드를 이용해 yyyymmdd에서 yyyy-mm-dd 형태로 변경한다.\n",
    "\n",
    "이제 해당 정보를 DB에 저장한다. 먼저 MySQL에서 아래의 쿼리를 입력해 데이터베이스(stock_db)를 만든 후, 국내 티커정보가 들어갈 테이블(kor_ticker)을 만들어준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c1803cf",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "create database stock_db;\n",
    "\n",
    "use stock_db;\n",
    "\n",
    "create table kor_ticker\n",
    "(\n",
    "    종목코드 varchar(6) not null,\n",
    "    종목명 varchar(20),\n",
    "    시장구분 varchar(6),\n",
    "    종가 float,\n",
    "    시가총액 float,\n",
    "    기준일 date,\n",
    "    EPS float,\n",
    "    선행EPS float,\n",
    "    BPS float,\n",
    "    주당배당금 float,\n",
    "    종목구분 varchar(5),\n",
    "    primary key(종목코드, 기준일)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b79947fc",
   "metadata": {},
   "source": [
    "```{figure} image/data_korea/ticker_table.png\n",
    "---\n",
    "name: ticker_table\n",
    "---\n",
    "국내 티커정보 테이블 생성\n",
    "```\n",
    "\n",
    "파이썬에서 아래 코드를 실행하면 다운로드 받은 정보가 kor_ticker 테이블에 upsert 형태로 저장된다. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "19c23d82",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "\n",
    "con = pymysql.connect(user='root',\n",
    "                      passwd='1234',\n",
    "                      host='127.0.0.1',\n",
    "                      db='stock_db',\n",
    "                      charset='utf8')\n",
    "\n",
    "mycursor = con.cursor()\n",
    "query = f\"\"\"\n",
    "    insert into kor_ticker (종목코드,종목명,시장구분,종가,시가총액,기준일,EPS,선행EPS,BPS,주당배당금,종목구분)\n",
    "    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    종목명=new.종목명,시장구분=new.시장구분,종가=new.종가,시가총액=new.시가총액,EPS=new.EPS,선행EPS=new.선행EPS,\n",
    "    BPS=new.BPS,주당배당금=new.주당배당금,종목구분 = new.종목구분;\n",
    "\"\"\"\n",
    "\n",
    "args = kor_ticker.values.tolist()\n",
    "\n",
    "mycursor.executemany(query, args)\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fedefc1",
   "metadata": {},
   "source": [
    "```{figure} image/data_korea/sql_ticker.png\n",
    "---\n",
    "name: sql_ticker\n",
    "---\n",
    "국내 티커정보 테이블\n",
    "```\n",
    "\n",
    "## WICS 기준 섹터정보 크롤링\n",
    "\n",
    "일반적으로 주식의 섹터를 나누는 기준은 MSCI와 S&P가 개발한 **GICS**를 가장 많이 사용한다. 국내 종목의 GICS 기준 정보 역시 한국거래소에서 제공하고 있으나, 이는 독점적 지적재산으로 명시했기에 사용하는데 무리가 있다. 그러나 지수제공업체인 FnGuide Index에서는 GICS와 비슷한 **WICS 산업분류**를 발표하고 있다. WICS를 크롤링하여 필요한 정보를 수집해보도록 하자.\n",
    "\n",
    "```\n",
    "http://www.wiseindex.com/Index\n",
    "```\n",
    "\n",
    "먼저 웹페이지에 접속해 왼쪽에서 [WISE SECTOR INDEX → WICS → 에너지]를 클릭한다. 그 후 [Components] 탭을 클릭하면 해당 섹터의 구성종목을 확인할 수 있다.\n",
    "\n",
    "\n",
    "```{figure} image/data_korea/wise_index.png\n",
    "---\n",
    "name: wise_index\n",
    "---\n",
    "WICS 기준 구성종목\n",
    "```\n",
    "\n",
    "개발자도구 화면({numref}`wise_index_2`)을 통해 해당 페이지의 데이터전송 과정을 살펴보자. 일자를 선택하면 [Network] 탭의 'GetIndexComponets' 항목에 데이터 전송 과정이 나타난다. Request URL은 데이터를 가져오는 주소이며, 이를 분석하면 다음과 같다.\n",
    "\n",
    "```\n",
    "http://www.wiseindex.com/Index/GetIndexComponets?ceil_yn=0&dt=20210210&sec_cd=G10\n",
    "```\n",
    "\n",
    "1. http://www.wiseindex.com/Index/GetIndexComponets: 데이터를 요청하는 url이다.\n",
    "2. ceil_yn = 0: 실링 여부를 나타내며, 0은 비실링을 의미한다.\n",
    "3. dt=20220419: 조회일자를 나타낸다.\n",
    "4. sec_cd=G10: 섹터 코드를 나타낸다.\n",
    "\n",
    "```{figure} image/data_korea/wise_index_2.png\n",
    "---\n",
    "name: wise_index_2\n",
    "---\n",
    "WICS 페이지 개발자도구 화면\n",
    "```\n",
    "\n",
    "이번에는 Request URL에 해당하는 페이지를 열어보자.\n",
    "\n",
    "```{figure} image/data_korea/wise_index_3.png\n",
    "---\n",
    "name: wise_index_3\n",
    "---\n",
    "WICS 데이터 페이지\n",
    "```\n",
    "\n",
    "페이지에 출력된 글자들이 보이지만 매우 특이한 형태로 구성되어 있으며, 이는 JSON 형식의 데이터다. 기존에 우리가 살펴보았던 대부분의 웹페이지는 HTML 형식으로 표현되었다. HTML 형식은 문법이 복잡하고 표현 규칙이 엄격해 데이터의 용량이 커지는 단점이 있다. 반면 JSON 형식은 문법이 단순하고 데이터의 용량이 작아 빠른 속도로 데이터를 교환할 수 있다. 파이썬에서는 json 패키지를 사용해 매우 손쉽게 JSON 형식의 데이터를 크롤링할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ec0de521",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "import requests as rq\n",
    "import pandas as pd\n",
    "\n",
    "url = f'''http://www.wiseindex.com/Index/GetIndexComponets?ceil_yn=0&dt={biz_day}&sec_cd=G10'''\n",
    "data = rq.get(url).json()\n",
    "\n",
    "type(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9539e77a",
   "metadata": {},
   "source": [
    "1. f-string 포매팅을 이용해 dt 부분에는 위에서 구한 최근 영업일 데이터를 입력하여 URL을 생성한다. \n",
    "2. `get()` 함수를 통해 페이지의 내용을 받아오며, `json()` 메서드를 통해 JSON 데이터만 불러올 수 있다.\n",
    "\n",
    "파이썬에서는 JSON 데이터가 딕셔너리 형태로 변경된다. 어떠한 키가 있는지 확인해보자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "93aa4df1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys(['info', 'list', 'sector', 'size'])\n"
     ]
    }
   ],
   "source": [
    "print(data.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96075f2c",
   "metadata": {},
   "source": [
    "'info', 'list', 'sector', 'size' 중 list에는 해당 섹터의 구성종목 정보가, sector에는 각종 섹터의 코드 정보가 포함되어 있다. 하나씩 확인해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5b37c823",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'IDX_CD': 'G10',\n",
       " 'IDX_NM_KOR': 'WICS 에너지',\n",
       " 'ALL_MKT_VAL': 22486171,\n",
       " 'CMP_CD': '096770',\n",
       " 'CMP_KOR': 'SK이노베이션',\n",
       " 'MKT_VAL': 9579432,\n",
       " 'WGT': 42.6,\n",
       " 'S_WGT': 42.6,\n",
       " 'CAL_WGT': 1.0,\n",
       " 'SEC_CD': 'G10',\n",
       " 'SEC_NM_KOR': '에너지',\n",
       " 'SEQ': 1,\n",
       " 'TOP60': 3,\n",
       " 'APT_SHR_CNT': 51780716}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['list'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a6018a77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'SEC_CD': 'G25', 'SEC_NM_KOR': '경기관련소비재', 'SEC_RATE': 10.89, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G35', 'SEC_NM_KOR': '건강관리', 'SEC_RATE': 10.47, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G50', 'SEC_NM_KOR': '커뮤니케이션서비스', 'SEC_RATE': 8.46, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G40', 'SEC_NM_KOR': '금융', 'SEC_RATE': 7.82, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G10', 'SEC_NM_KOR': '에너지', 'SEC_RATE': 1.99, 'IDX_RATE': 100.0},\n",
       " {'SEC_CD': 'G20', 'SEC_NM_KOR': '산업재', 'SEC_RATE': 11.93, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G55', 'SEC_NM_KOR': '유틸리티', 'SEC_RATE': 1.26, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G30', 'SEC_NM_KOR': '필수소비재', 'SEC_RATE': 2.56, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G15', 'SEC_NM_KOR': '소재', 'SEC_RATE': 8.09, 'IDX_RATE': 0},\n",
       " {'SEC_CD': 'G45', 'SEC_NM_KOR': 'IT', 'SEC_RATE': 36.53, 'IDX_RATE': 0}]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['sector']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "278e6fc1",
   "metadata": {},
   "source": [
    "list 부분의 데이터를 데이터프레임 형태로 변경하도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "669ee006",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IDX_CD</th>\n",
       "      <th>IDX_NM_KOR</th>\n",
       "      <th>ALL_MKT_VAL</th>\n",
       "      <th>CMP_CD</th>\n",
       "      <th>CMP_KOR</th>\n",
       "      <th>MKT_VAL</th>\n",
       "      <th>WGT</th>\n",
       "      <th>S_WGT</th>\n",
       "      <th>CAL_WGT</th>\n",
       "      <th>SEC_CD</th>\n",
       "      <th>SEC_NM_KOR</th>\n",
       "      <th>SEQ</th>\n",
       "      <th>TOP60</th>\n",
       "      <th>APT_SHR_CNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>G10</td>\n",
       "      <td>WICS 에너지</td>\n",
       "      <td>22486171</td>\n",
       "      <td>096770</td>\n",
       "      <td>SK이노베이션</td>\n",
       "      <td>9579432</td>\n",
       "      <td>42.60</td>\n",
       "      <td>42.60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>G10</td>\n",
       "      <td>에너지</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>51780716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>G10</td>\n",
       "      <td>WICS 에너지</td>\n",
       "      <td>22486171</td>\n",
       "      <td>010950</td>\n",
       "      <td>S-Oil</td>\n",
       "      <td>3674027</td>\n",
       "      <td>16.34</td>\n",
       "      <td>58.94</td>\n",
       "      <td>1.0</td>\n",
       "      <td>G10</td>\n",
       "      <td>에너지</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>41655633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>G10</td>\n",
       "      <td>WICS 에너지</td>\n",
       "      <td>22486171</td>\n",
       "      <td>267250</td>\n",
       "      <td>HD현대</td>\n",
       "      <td>2530306</td>\n",
       "      <td>11.25</td>\n",
       "      <td>70.19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>G10</td>\n",
       "      <td>에너지</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>44236128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>G10</td>\n",
       "      <td>WICS 에너지</td>\n",
       "      <td>22486171</td>\n",
       "      <td>078930</td>\n",
       "      <td>GS</td>\n",
       "      <td>2011664</td>\n",
       "      <td>8.95</td>\n",
       "      <td>79.14</td>\n",
       "      <td>1.0</td>\n",
       "      <td>G10</td>\n",
       "      <td>에너지</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>49245150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>G10</td>\n",
       "      <td>WICS 에너지</td>\n",
       "      <td>22486171</td>\n",
       "      <td>112610</td>\n",
       "      <td>씨에스윈드</td>\n",
       "      <td>1360281</td>\n",
       "      <td>6.05</td>\n",
       "      <td>85.19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>G10</td>\n",
       "      <td>에너지</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>23615986</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  IDX_CD IDX_NM_KOR  ALL_MKT_VAL  CMP_CD  CMP_KOR  MKT_VAL    WGT  S_WGT  \\\n",
       "0    G10   WICS 에너지     22486171  096770  SK이노베이션  9579432  42.60  42.60   \n",
       "1    G10   WICS 에너지     22486171  010950    S-Oil  3674027  16.34  58.94   \n",
       "2    G10   WICS 에너지     22486171  267250     HD현대  2530306  11.25  70.19   \n",
       "3    G10   WICS 에너지     22486171  078930       GS  2011664   8.95  79.14   \n",
       "4    G10   WICS 에너지     22486171  112610    씨에스윈드  1360281   6.05  85.19   \n",
       "\n",
       "   CAL_WGT SEC_CD SEC_NM_KOR  SEQ  TOP60  APT_SHR_CNT  \n",
       "0      1.0    G10        에너지    1      3     51780716  \n",
       "1      1.0    G10        에너지    2      3     41655633  \n",
       "2      1.0    G10        에너지    3      3     44236128  \n",
       "3      1.0    G10        에너지    4      3     49245150  \n",
       "4      1.0    G10        에너지    5      3     23615986  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_pd = pd.json_normalize(data['list'])\n",
    "\n",
    "data_pd.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bc565a9",
   "metadata": {},
   "source": [
    "pandas 패키지의 `json_normalize()` 함수를 이용하면 JSON 형태의 데이터를 데이터프레임 형태로 매우 쉽게 변경할 수 있다. 이제 for문을 이용하여 URL의 sec_cd=에 해당하는 부분만 변경하면 모든 섹터의 구성종목을 매우 쉽게 얻을 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b90a40c",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "import time\n",
    "import json\n",
    "import requests as rq\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "\n",
    "sector_code = [\n",
    "    'G25', 'G35', 'G50', 'G40', 'G10', 'G20', 'G55', 'G30', 'G15', 'G45'\n",
    "]\n",
    "\n",
    "data_sector = []\n",
    "\n",
    "for i in tqdm(sector_code):\n",
    "    url = f'''http://www.wiseindex.com/Index/GetIndexComponets?ceil_yn=0&dt={biz_day}&sec_cd={i}'''    \n",
    "    data = rq.get(url).json()\n",
    "    data_pd = pd.json_normalize(data['list'])\n",
    "\n",
    "    data_sector.append(data_pd)\n",
    "\n",
    "    time.sleep(2)\n",
    "\n",
    "kor_sector = pd.concat(data_sector, axis = 0)\n",
    "kor_sector = kor_sector[['IDX_CD', 'CMP_CD', 'CMP_KOR', 'SEC_NM_KOR']]\n",
    "kor_sector['기준일'] = biz_day\n",
    "kor_sector['기준일'] = pd.to_datetime(kor_sector['기준일'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc0d4a8f",
   "metadata": {},
   "source": [
    "1. 섹터 정보가 들어갈 빈 리스트(data_sector)를 만든다.\n",
    "2. for문의 i에 섹터 코드를 입력하여 모든 섹터의 구성종목을 다운로드 받은 후 `append()` 메서드를 통해 리스트에 추가한다.\n",
    "3. `tqdm()` 함수를 통해 진행상황을 출력한다. \n",
    "4. `concat()` 함수를 이용해 리스트 내의 데이터프레임을 합친다.\n",
    "5. 필요한 열(섹터 코드, 티커, 종목명, 섹터명)만 선택한다.\n",
    "6. 데이터의 기준일에 해당하는 [기준일] 열을 추가한 후 datetime 형태로 변경한다.\n",
    "\n",
    "이제 SQL에 해당 정보가 들어갈 테이블을 만들어 준 후 저장을 해주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc68621e",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table kor_sector\n",
    "(\n",
    "    IDX_CD varchar(3),\n",
    "    CMP_CD varchar(6),\n",
    "    CMP_KOR varchar(20),\n",
    "    SEC_NM_KOR varchar(10),\n",
    "    기준일 date,\n",
    "    primary key(CMP_CD, 기준일)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9be59a1b",
   "metadata": {},
   "source": [
    "먼저 MySQL에서 국내 섹터정보가 들어갈 테이블(kor_sector)을 만들어준다. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50fe9957",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "import pymysql\n",
    "\n",
    "con = pymysql.connect(user='root',\n",
    "                      passwd='1234',\n",
    "                      host='127.0.0.1',\n",
    "                      db='stock_db',\n",
    "                      charset='utf8')\n",
    "\n",
    "mycursor = con.cursor()\n",
    "query = f\"\"\"\n",
    "    insert into kor_sector (IDX_CD, CMP_CD, CMP_KOR, SEC_NM_KOR, 기준일)\n",
    "    values (%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    IDX_CD = new.IDX_CD, CMP_KOR = new.CMP_KOR, SEC_NM_KOR = new.SEC_NM_KOR\n",
    "\"\"\"\n",
    "\n",
    "args = kor_sector.values.tolist()\n",
    "\n",
    "mycursor.executemany(query, args)\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc41f1aa",
   "metadata": {},
   "source": [
    "파이썬에서 위 코드를 실행하면 다운로드 받은 정보가 kor_sector 테이블에 upsert 형태로 저장된다. \n",
    "\n",
    "```{figure} image/data_korea/sql_sector.png\n",
    "---\n",
    "name: sql_sector\n",
    "---\n",
    "섹터 테이블\n",
    "```\n",
    "\n",
    "## 수정주가 크롤링\n",
    "\n",
    "주가 데이터는 투자를 함에 있어 반드시 필요한 데이터이며, 인터넷에서 주가를 수집할 수 있는 방법은 매우 많다. 그러나 일반적인 주가를 구할 수 있는 방법은 많지만, 퀀트 투자를 위한 백테스트나 종목선정을 위해서는 수정주가가 필요하다. 수정주가가 필요한 이유를 알아보기 위해 실제 사례를 살펴보도록 하자. 삼성전자는 2018년 5월 기존의 1주를 50주로 나누는 액면분할을 실시했고, 265만 원이던 주가는 다음날 50분의 1인 5만 3000원으로 거래되었다. 이러한 이벤트를 고려하지 않고 주가만 살펴본다면 마치 -98% 수익률을 기록한 것 같지만, 투자자 입장에서는 1주이던 주식이 50주로 늘어났기 때문에 자산에는 아무런 변화가 없다. 이를 고려하는 방법은 액면분할 전 모든 주가를 50으로 나누어 연속성을 갖게 만드는 것이며, 이를 '수정주가'라고 한다. 따라서 백테스트 혹은 퀀트 지표 계산에는 수정주가가 사용되어야 하며, 네이버 금융에서 제공하는 정보를 통해 모든 종목의 수정주가를 손쉽게 구할 수 있다. \n",
    "\n",
    "```{figure} image/data_korea/ss.png\n",
    "---\n",
    "name: ss\n",
    "---\n",
    "액면분할을 반영하지 않은 삼성전자의 주가 그래프\n",
    "```\n",
    "\n",
    "### 개별종목 주가 크롤링\n",
    "\n",
    "먼저 네이버 금융에서 특정종목(예: 삼성전자)의 [차트]탭을 선택한다.\n",
    "\n",
    "```\n",
    "https://finance.naver.com/item/fchart.nhn?code=005930\n",
    "```\n",
    "\n",
    "해당 차트는 주가 데이터를 받아와 화면에 그래프를 그려주는 형태다. 따라서 데이터가 어디에서 오는지 알기 위해 개발자도구 화면을 이용한다. 화면을 연 상태에서 [일]을 선택하면 나오는 항목 중 가장 상단 항목 [siseJson.naver?symbol=..]의 **Request URL**이 주가 데이터를 요청하는 주소다.\n",
    "\n",
    "```{figure} image/data_korea/naver_chart.png\n",
    "---\n",
    "name: naver_chart\n",
    "---\n",
    "네이버 금융 차트의 통신기록\n",
    "```\n",
    "\n",
    "URL은 다음과 같다.\n",
    "\n",
    "```\n",
    "https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20200214&endTime=20220422&timeframe=day\n",
    "```\n",
    "\n",
    "위 페이지에 접속하면 {numref}`naver_stock`와 같이 날짜별로 시가, 고가, 저가, 종가, 거래량, 외국인소진율이 있으며, 주가는 모두 수정주가 기준이다. URL에서 'symbol=' 뒤에 6자리 티커만 변경하면 해당 종목의 주가 데이터가 있는 페이지로 이동할 수 있으며, 이를 통해 우리가 원하는 모든 종목의 수정주가 데이터를 크롤링할 수 있다. 또한 'startTime=' 에는 시작일자를, 'endTime=' 에는 종료일자를 입력하여 원하는 기간 만큼의 데이터를 받을 수도 있다.\n",
    "\n",
    "```{figure} image/data_korea/naver_stock.png\n",
    "---\n",
    "name: naver_stock\n",
    "---\n",
    "주가 데이터 페이지\n",
    "```\n",
    "\n",
    "본격적인 주가 데이터 수집을 위해 먼저 DB에서 티커 데이터를 불러오도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c6a39a59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>종가</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "      <th>EPS</th>\n",
       "      <th>선행EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>주당배당금</th>\n",
       "      <th>종목구분</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000020</td>\n",
       "      <td>동화약품</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>10600.0</td>\n",
       "      <td>2.960740e+11</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>647.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12534.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000040</td>\n",
       "      <td>KR모터스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>703.0</td>\n",
       "      <td>6.758530e+10</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>385.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000050</td>\n",
       "      <td>경방</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>12850.0</td>\n",
       "      <td>3.522860e+11</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>872.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30033.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000060</td>\n",
       "      <td>메리츠화재</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>36150.0</td>\n",
       "      <td>4.222390e+12</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>5768.0</td>\n",
       "      <td>6211.0</td>\n",
       "      <td>22086.0</td>\n",
       "      <td>620.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000070</td>\n",
       "      <td>삼양홀딩스</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>72500.0</td>\n",
       "      <td>6.209100e+11</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>30711.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226314.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>보통주</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드    종목명   시장구분       종가          시가총액         기준일      EPS   선행EPS  \\\n",
       "0  000020   동화약품  KOSPI  10600.0  2.960740e+11  2022-08-03    647.0     NaN   \n",
       "1  000040  KR모터스  KOSPI    703.0  6.758530e+10  2022-08-03      NaN     NaN   \n",
       "2  000050     경방  KOSPI  12850.0  3.522860e+11  2022-08-03    872.0     NaN   \n",
       "3  000060  메리츠화재  KOSPI  36150.0  4.222390e+12  2022-08-03   5768.0  6211.0   \n",
       "4  000070  삼양홀딩스  KOSPI  72500.0  6.209100e+11  2022-08-03  30711.0     NaN   \n",
       "\n",
       "        BPS   주당배당금 종목구분  \n",
       "0   12534.0   180.0  보통주  \n",
       "1     385.0     0.0  보통주  \n",
       "2   30033.0   125.0  보통주  \n",
       "3   22086.0   620.0  보통주  \n",
       "4  226314.0  3000.0  보통주  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/stock_db')\n",
    "query = \"\"\"\n",
    "select * from kor_ticker\n",
    "where 기준일 = (select max(기준일) from kor_ticker) \n",
    "\tand 종목구분 = '보통주';\n",
    "\"\"\"\n",
    "ticker_list = pd.read_sql(query, con=engine)\n",
    "engine.dispose()\n",
    "\n",
    "ticker_list.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7d8153d",
   "metadata": {},
   "source": [
    "1. `create_engine()` 함수를 통해 데이터베이스에 접속하기 위한 엔진을 만든다.\n",
    "2. 티커 데이터를 불러오는 쿼리를 작성하며, 가장 최근일자에 해당하는 내용을 불러오기 위해 서브쿼리에 `select max(기준일) from kor_ticker`을 입력한다. 또한 종목구분에서 보통주에 해당하는 종목만 선택한다.\n",
    "3. `read_sql()` 함수를 통해 해당 쿼리를 보낸 후 데이터를 받아온다.\n",
    "4. 접속을 종료한다.\n",
    "\n",
    "이제 위에서 살펴본 주가 데이터 페이지를 크롤링 하도록 하겠다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "deec2b64",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>[['날짜'</th>\n",
       "      <th>'시가'</th>\n",
       "      <th>'고가'</th>\n",
       "      <th>'저가'</th>\n",
       "      <th>'종가'</th>\n",
       "      <th>'거래량'</th>\n",
       "      <th>'외국인소진율']</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[\"20170807\"</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>8410.0</td>\n",
       "      <td>8320.0</td>\n",
       "      <td>8380.0</td>\n",
       "      <td>35043.0</td>\n",
       "      <td>7.84]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[\"20170808\"</td>\n",
       "      <td>8380.0</td>\n",
       "      <td>8380.0</td>\n",
       "      <td>8290.0</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>28242.0</td>\n",
       "      <td>7.82]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[\"20170809\"</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>93844.0</td>\n",
       "      <td>7.76]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[\"20170810\"</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>8280.0</td>\n",
       "      <td>8120.0</td>\n",
       "      <td>8200.0</td>\n",
       "      <td>54760.0</td>\n",
       "      <td>7.77]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[\"20170811\"</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>8250.0</td>\n",
       "      <td>7980.0</td>\n",
       "      <td>8130.0</td>\n",
       "      <td>128133.0</td>\n",
       "      <td>7.73]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        [['날짜'    '시가'    '고가'    '저가'    '종가'     '거래량'  '외국인소진율']  \\\n",
       "0  [\"20170807\"  8330.0  8410.0  8320.0  8380.0   35043.0      7.84]   \n",
       "1  [\"20170808\"  8380.0  8380.0  8290.0  8330.0   28242.0      7.82]   \n",
       "2  [\"20170809\"  8330.0  8330.0  8150.0  8150.0   93844.0      7.76]   \n",
       "3  [\"20170810\"  8150.0  8280.0  8120.0  8200.0   54760.0      7.77]   \n",
       "4  [\"20170811\"  8150.0  8250.0  7980.0  8130.0  128133.0      7.73]   \n",
       "\n",
       "   Unnamed: 7  \n",
       "0         NaN  \n",
       "1         NaN  \n",
       "2         NaN  \n",
       "3         NaN  \n",
       "4         NaN  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from dateutil.relativedelta import relativedelta\n",
    "import requests as rq\n",
    "from io import BytesIO\n",
    "from datetime import date\n",
    "\n",
    "i = 0\n",
    "ticker = ticker_list['종목코드'][i]\n",
    "fr = (date.today() + relativedelta(years=-5)).strftime(\"%Y%m%d\")\n",
    "to = (date.today()).strftime(\"%Y%m%d\")\n",
    "\n",
    "url = f'''https://fchart.stock.naver.com/siseJson.nhn?symbol={ticker}&requestType=1\n",
    "&startTime={fr}&endTime={to}&timeframe=day'''\n",
    "\n",
    "data = rq.get(url).content\n",
    "data_price = pd.read_csv(BytesIO(data))\n",
    "\n",
    "data_price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15ed93f9",
   "metadata": {},
   "source": [
    "1. 먼저 i = 0을 입력한다. 향후 for문을 통해 i 값만 변경하면 모든 종목의 주가를 다운로드할 수 있다.\n",
    "2. `ticker_list['종목코드'][i]`를 통해 원하는 종목의 티커를 선택한다.\n",
    "3. 시작일(fr)과 종료일(to)에 해당하는 날짜를 만들어준다. `today()` 메서드를 이용해 오늘 날짜를 불러온 후, 시작일은 `relativedelta()` 클래스를 이용해 5년을 빼준다. (본인이 원하는 기간 만큼을 빼주면 된다.) 그 후 `strftime()` 메서드를 통해 'yyyymmdd' 형식을 만들어 준다. 종료일은 오늘 날짜를 그대로 사용한다.\n",
    "4. 티커, 시작일, 종료일을 이용해 주가 데이터가 있는 URL을 생성한다.\n",
    "5. `get()` 함수를 통해 페이지의 데이터를 불러온 후, content 부분을 추출한다.\n",
    "6. `BytesIO()`를 이용해 바이너리스트림 형태로 만든 후, `read_csv()` 함수를 통해 데이터를 읽어온다.\n",
    "\n",
    "결과를 확인해보면 날짜 및 주가, 거래량, 외국인소진율 데이터가 추출된다. 추가적으로 클렌징 작업을 해주도록 하겠다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6eb70741",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>날짜</th>\n",
       "      <th>시가</th>\n",
       "      <th>고가</th>\n",
       "      <th>저가</th>\n",
       "      <th>종가</th>\n",
       "      <th>거래량</th>\n",
       "      <th>종목코드</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-07</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>8410.0</td>\n",
       "      <td>8320.0</td>\n",
       "      <td>8380.0</td>\n",
       "      <td>35043.0</td>\n",
       "      <td>000020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-08</td>\n",
       "      <td>8380.0</td>\n",
       "      <td>8380.0</td>\n",
       "      <td>8290.0</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>28242.0</td>\n",
       "      <td>000020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-08-09</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>8330.0</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>93844.0</td>\n",
       "      <td>000020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-08-10</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>8280.0</td>\n",
       "      <td>8120.0</td>\n",
       "      <td>8200.0</td>\n",
       "      <td>54760.0</td>\n",
       "      <td>000020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-08-11</td>\n",
       "      <td>8150.0</td>\n",
       "      <td>8250.0</td>\n",
       "      <td>7980.0</td>\n",
       "      <td>8130.0</td>\n",
       "      <td>128133.0</td>\n",
       "      <td>000020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          날짜      시가      고가      저가      종가       거래량    종목코드\n",
       "0 2017-08-07  8330.0  8410.0  8320.0  8380.0   35043.0  000020\n",
       "1 2017-08-08  8380.0  8380.0  8290.0  8330.0   28242.0  000020\n",
       "2 2017-08-09  8330.0  8330.0  8150.0  8150.0   93844.0  000020\n",
       "3 2017-08-10  8150.0  8280.0  8120.0  8200.0   54760.0  000020\n",
       "4 2017-08-11  8150.0  8250.0  7980.0  8130.0  128133.0  000020"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "price = data_price.iloc[:, 0:6]\n",
    "price.columns = ['날짜', '시가', '고가', '저가', '종가', '거래량']\n",
    "price = price.dropna()\n",
    "price['날짜'] = price['날짜'].str.extract('(\\d+)')\n",
    "price['날짜'] = pd.to_datetime(price['날짜'])\n",
    "price['종목코드'] = ticker\n",
    "\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed9d24d3",
   "metadata": {},
   "source": [
    "1. `iloc()` 인덱서를 통해 날짜와 가격(시가, 고가, 저가, 종가), 거래량에 해당하는 데이터만을 선택한다.\n",
    "2. 열 이름을 변경한다.\n",
    "3. `dropna()` 함수를 통해 NA 데이터를 삭제한다.\n",
    "4. `extract()` 메서드 내에 정규 표현식을 이용해 날짜 열에서 숫자만을 추출한다.\n",
    "4. '날짜'열을 datetime 형태로 변경한다.\n",
    "5. '종목코드'열에 티커를 입력한다.\n",
    "\n",
    "데이터를 확인해보면 우리에게 필요한 형태로 정리되었다.\n",
    "\n",
    "### 전 종목 주가 크롤링\n",
    "\n",
    "위 과정을 응용해 모든 종목의 주가를 크롤링한 후 DB에 저장하는 과정을 살펴보도록 하겠다. 먼저 SQL에서 주가가 저장될 테이블(kor_price)을 만들어준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "110f2c13",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table kor_price\n",
    "(\n",
    "    날짜 date,\n",
    "    시가 double,\n",
    "    고가 double,\n",
    "    저가 double,\n",
    "    종가 double,\n",
    "    거래량 double,\n",
    "    종목코드 varchar(6),\n",
    "    primary key(날짜, 종목코드)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fa24f52",
   "metadata": {},
   "source": [
    "이제 파이썬에서 아래 코드를 실행하면 for문을 통해 전종목 주가가 DB에 저장된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aeae08b8",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "from datetime import date\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import requests as rq\n",
    "import time\n",
    "from tqdm import tqdm\n",
    "from io import BytesIO\n",
    "\n",
    "# DB 연결\n",
    "engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/stock_db')\n",
    "con = pymysql.connect(user='root',\n",
    "                      passwd='1234',\n",
    "                      host='127.0.0.1',\n",
    "                      db='stock_db',\n",
    "                      charset='utf8')\n",
    "mycursor = con.cursor()\n",
    "\n",
    "# 티커리스트 불러오기\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from kor_ticker\n",
    "where 기준일 = (select max(기준일) from kor_ticker) \n",
    "\tand 종목구분 = '보통주';\n",
    "\"\"\", con=engine)\n",
    "\n",
    "# DB 저장 쿼리\n",
    "query = \"\"\"\n",
    "    insert into kor_price (날짜, 시가, 고가, 저가, 종가, 거래량, 종목코드)\n",
    "    values (%s,%s,%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    시가 = new.시가, 고가 = new.고가, 저가 = new.저가,\n",
    "    종가 = new.종가, 거래량 = new.거래량;\n",
    "\"\"\"\n",
    "\n",
    "# 오류 발생시 저장할 리스트 생성\n",
    "error_list = []\n",
    "\n",
    "# 전종목 주가 다운로드 및 저장\n",
    "for i in tqdm(range(0, len(ticker_list))):\n",
    "\n",
    "    # 티커 선택\n",
    "    ticker = ticker_list['종목코드'][i]\n",
    "\n",
    "    # 시작일과 종료일\n",
    "    fr = (date.today() + relativedelta(years=-5)).strftime(\"%Y%m%d\")\n",
    "    to = (date.today()).strftime(\"%Y%m%d\")\n",
    "\n",
    "    # 오류 발생 시 이를 무시하고 다음 루프로 진행\n",
    "    try:\n",
    "\n",
    "        # url 생성\n",
    "        url = f'''https://fchart.stock.naver.com/siseJson.nhn?symbol={ticker}&requestType=1\n",
    "        &startTime={fr}&endTime={to}&timeframe=day'''\n",
    "\n",
    "        # 데이터 다운로드\n",
    "        data = rq.get(url).content\n",
    "        data_price = pd.read_csv(BytesIO(data))\n",
    "\n",
    "        # 데이터 클렌징\n",
    "        price = data_price.iloc[:, 0:6]\n",
    "        price.columns = ['날짜', '시가', '고가', '저가', '종가', '거래량']\n",
    "        price = price.dropna()\n",
    "        price['날짜'] = price['날짜'].str.extract('(\\d+)')\n",
    "        price['날짜'] = pd.to_datetime(price['날짜'])\n",
    "        price['종목코드'] = ticker\n",
    "\n",
    "        # 주가 데이터를 DB에 저장\n",
    "        args = price.values.tolist()\n",
    "        mycursor.executemany(query, args)\n",
    "        con.commit()\n",
    "\n",
    "    except:\n",
    "\n",
    "        # 오류 발생시 error_list에 티커 저장하고 넘어가기\n",
    "        print(ticker)\n",
    "        error_list.append(ticker)\n",
    "\n",
    "    # 타임슬립 적용\n",
    "    time.sleep(2)\n",
    "\n",
    "# DB 연결 종료\n",
    "engine.dispose()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "174582bb",
   "metadata": {},
   "source": [
    "1. DB에 연결한다.\n",
    "2. 기준일이 최대, 즉 최근일 기준 보통주에 해당하는 티커 리스트(ticker_list)만 불러온다.\n",
    "3. DB에 저장할 쿼리(query)를 입력한다.\n",
    "4. 오류 발생시 저장할 리스트(error_list)를 만든다.\n",
    "5. for문을 통해 전종목 주가를 다운로드 받으며, 진행상황을 알기위해 `tqdm()` 함수를 이용한다.\n",
    "6. URL 생성, 데이터 다운로드 및 데이터 클렌징 및 DB에 저장은 위와 동일하며, try except문을 통해 오류가 발생시 티커를 출력 후 error_list에 저장한다.\n",
    "7. 무한 크롤링을 방지하기 위해 한 번의 루프가 끝날 때마다 타임슬립을 적용한다.\n",
    "8. 모든 작업이 끝나면 DB와의 연결을 종료한다.\n",
    "\n",
    "작업이 끝난 후 SQL의 kor_price 테이블을 확인해보면 전 종목 주가가 저장되어 있다. 시간이 지나 위 코드를 다시 실행하면 upsert 형식을 통해 수정된 주가는 update를, 새로 입력된 주가는 insert를 한다.\n",
    "\n",
    "```{figure} image/data_korea/sql_price.png\n",
    "---\n",
    "name: sql_price\n",
    "---\n",
    "주가 테이블\n",
    "```\n",
    "\n",
    "## 재무제표 크롤링\n",
    "\n",
    "주가와 더불어 재무제표와 가치지표 역시 투자에 있어 핵심이 되는 데이터다. 해당 데이터는 여러 웹사이트에서 구할 수 있으며, 국내 데이터 제공업체인 FnGuide에서 운영하는 Company Guide 웹사이트에서 손쉽게 구할 수 있다.\n",
    "\n",
    "```\n",
    "http://comp.fnguide.com/\n",
    "```\n",
    "\n",
    "### 재무제표 다운로드\n",
    "\n",
    "먼저 웹사이트에서 개별종목의 재무제표를 탭을 선택하면 포괄손익계산서, 재무상태표, 현금흐름표 항목이 있으며, 티커에 해당하는 A005930 뒤의 주소는 불필요한 내용이므로, 이를 제거한 주소로 접속한다. A 뒤의 6자리 티커만 변경한다면 해당 종목의 재무제표 페이지로 이동하게 된다.\n",
    "\n",
    "```\n",
    "http://comp.fnguide.com/SVO2/ASP/SVD_Finance.asp?pGB=1&gicode=A005930\n",
    "```\n",
    "\n",
    "```{figure} image/data_korea/comp_guide.png\n",
    "---\n",
    "name: comp_guide\n",
    "---\n",
    "Company Guide 화면\n",
    "```\n",
    "\n",
    "우리가 원하는 재무제표 항목들은 모두 테이블 형태로 제공되고 있으므로 pandas 패키지의 `read_html()` 함수를 이용해 쉽게 추출할 수 있다. 먼저 삼성전자 종목의 페이지 내용을 불러오자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d1742a6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[  IFRS(연결)  2019/12  2020/12  2021/12  2022/03   전년동기 전년동기(%)\n",
       " 0      매출액   3072.0   2721.0   2930.0    853.0  718.0    18.7\n",
       " 1     매출원가   1856.0   1334.0   1437.0    406.0  360.0    12.9\n",
       " 2    매출총이익   1216.0   1387.0   1493.0    447.0  358.0    24.6,\n",
       "   IFRS(연결)  2021/06  2021/09  2021/12  2022/03   전년동기 전년동기(%)\n",
       " 0      매출액    758.0    691.0    763.0    853.0  718.0    18.7\n",
       " 1     매출원가    374.0    348.0    355.0    406.0  360.0    12.9\n",
       " 2    매출총이익    384.0    343.0    408.0    447.0  358.0    24.6,\n",
       "              IFRS(연결)  2019/12  2020/12  2021/12  2022/03\n",
       " 0                  자산   3764.0   4338.0   4478.0   4600.0\n",
       " 1  유동자산계산에 참여한 계정 펼치기   2355.0   2227.0   2202.0   2287.0\n",
       " 2                재고자산    334.0    395.0    362.0    357.0,\n",
       "              IFRS(연결)  2021/06  2021/09  2021/12  2022/03\n",
       " 0                  자산   4471.0   4479.0   4478.0   4600.0\n",
       " 1  유동자산계산에 참여한 계정 펼치기   2274.0   2180.0   2202.0   2287.0\n",
       " 2                재고자산    431.0    420.0    362.0    357.0,\n",
       "          IFRS(연결)  2019/12  2020/12  2021/12  2022/03\n",
       " 0    영업활동으로인한현금흐름    133.0    522.0    360.0    166.0\n",
       " 1           당기순손익     91.0    287.0    196.0     58.0\n",
       " 2  법인세비용차감전계속사업이익      NaN      NaN      NaN      NaN,\n",
       "          IFRS(연결)  2021/06  2021/09  2021/12  2022/03\n",
       " 0    영업활동으로인한현금흐름     63.0     48.0    161.0    166.0\n",
       " 1           당기순손익     70.0      8.0     62.0     58.0\n",
       " 2  법인세비용차감전계속사업이익      NaN      NaN      NaN      NaN]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/stock_db')\n",
    "query = \"\"\"\n",
    "select * from kor_ticker\n",
    "where 기준일 = (select max(기준일) from kor_ticker) \n",
    "\tand 종목구분 = '보통주';\n",
    "\"\"\"\n",
    "ticker_list = pd.read_sql(query, con=engine)\n",
    "engine.dispose()\n",
    "\n",
    "i = 0\n",
    "ticker = ticker_list['종목코드'][i]\n",
    "\n",
    "url = f'http://comp.fnguide.com/SVO2/ASP/SVD_Finance.asp?pGB=1&gicode=A{ticker}'\n",
    "data = pd.read_html(url, displayed_only=False)\n",
    "\n",
    "[item.head(3) for item in data]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36f34e60",
   "metadata": {},
   "source": [
    "1. 티커 리스트를 불러와 첫번째 티커를 선택한다.\n",
    "2. 재무제표 페이지에 해당하는 URL을 생성한다.\n",
    "3. `read_html()` 함수를 통해 테이블 데이터만을 가져온다. 페이지를 살펴보면 [+] 버튼을 눌러야만 표시가 되는 항목도 있으므로, `displayed_only = False`를 통해 해당 항목들도 모두 가져온다.\n",
    "\n",
    "위의 과정을 거치면 총 6개의 테이블이 들어오게 되며, 그 내용은 {numref}`fs_table`와 같다.\n",
    "\n",
    "```{table} 재무제표 테이블 내역\n",
    ":name: fs_table\n",
    "\n",
    "| 순서 | 내용 | \n",
    "| --- | --- |\n",
    "| 0 | 포괄손익계산서 (연간) |\n",
    "| 1 | 포괄손익계산서 (분기) |\n",
    "| 2 | 재무상태표 (연간) |\n",
    "| 3 | 재무상태표 (분기) |\n",
    "| 4 | 현금흐름표 (연간) |\n",
    "| 5 | 현금흐름표 (분기) |\n",
    "```\n",
    "\n",
    "먼저 연간 기준 포괄손익계산서, 재무상태표, 현금흐름표의 열 이름을 살펴보자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1eaa430c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['IFRS(연결)', '2019/12', '2020/12', '2021/12', '2022/03', '전년동기', '전년동기(%)'] \n",
      " ['IFRS(연결)', '2019/12', '2020/12', '2021/12', '2022/03'] \n",
      " ['IFRS(연결)', '2019/12', '2020/12', '2021/12', '2022/03']\n"
     ]
    }
   ],
   "source": [
    "print(data[0].columns.tolist(), '\\n',\n",
    "      data[2].columns.tolist(), '\\n',\n",
    "      data[4].columns.tolist()\n",
    "     )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ecffb4aa",
   "metadata": {},
   "source": [
    "포괄손익계산서 테이블에는 '전년동기', '전년동기(%)' 열이 있으며, 이는 필요하지 않은 내용이므로 삭제해주어야 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "8b7fe2c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>2019/12</th>\n",
       "      <th>2020/12</th>\n",
       "      <th>2021/12</th>\n",
       "      <th>2022/03</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>매출액</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>2721.0</td>\n",
       "      <td>2930.0</td>\n",
       "      <td>853.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>매출원가</td>\n",
       "      <td>1856.0</td>\n",
       "      <td>1334.0</td>\n",
       "      <td>1437.0</td>\n",
       "      <td>406.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>매출총이익</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>1387.0</td>\n",
       "      <td>1493.0</td>\n",
       "      <td>447.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>판매비와관리비계산에 참여한 계정 펼치기</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>1155.0</td>\n",
       "      <td>1269.0</td>\n",
       "      <td>356.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>인건비</td>\n",
       "      <td>400.0</td>\n",
       "      <td>415.0</td>\n",
       "      <td>468.0</td>\n",
       "      <td>134.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      계정  2019/12  2020/12  2021/12  2022/03\n",
       "0                    매출액   3072.0   2721.0   2930.0    853.0\n",
       "1                   매출원가   1856.0   1334.0   1437.0    406.0\n",
       "2                  매출총이익   1216.0   1387.0   1493.0    447.0\n",
       "3  판매비와관리비계산에 참여한 계정 펼치기   1120.0   1155.0   1269.0    356.0\n",
       "4                    인건비    400.0    415.0    468.0    134.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_fs_y = pd.concat(\n",
    "    [data[0].iloc[:, ~data[0].columns.str.contains('전년동기')], data[2], data[4]])\n",
    "data_fs_y = data_fs_y.rename(columns={data_fs_y.columns[0]: \"계정\"})\n",
    "\n",
    "data_fs_y.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc768e96",
   "metadata": {},
   "source": [
    "1. 포괄손익계산서 중 '전년동기'라는 글자가 들어간 열을 제외한 데이터를 선택한다.\n",
    "2. `concat()` 함수를 이용해 포괄손익계산서, 재무상태표, 현금흐름표 세개 테이블을 하나로 묶는다.\n",
    "3. `rename()` 메서드를 통해 첫번째 열 이름(IFRS 혹은 IFRS(연결)을 '계정'으로 변경한다.\n",
    "\n",
    "결산마감 이전에 해당 페이지를 크롤링 할 경우 연간 재무제표 데이터에 분기 재무제표 데이터가 들어오기도 하므로, 연간 재무제표에 해당하는 열 만을 선택해야 한다. 각 종목 별 결산월은 해당 페이지의 상단에서 확인할 수 있다.\n",
    "\n",
    "```{figure} image/data_korea/fiscal_end.png\n",
    "---\n",
    "name: fiscal_end\n",
    "---\n",
    "종목 별 결산월\n",
    "```\n",
    "\n",
    "이제 해당 데이터를 크롤링 해보도록 하겠다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "b0bd6888",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['12']\n"
     ]
    }
   ],
   "source": [
    "import requests as rq\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "\n",
    "page_data = rq.get(url)\n",
    "page_data_html = BeautifulSoup(page_data.content, 'html.parser')\n",
    "\n",
    "fiscal_data = page_data_html.select('div.corp_group1 > h2')\n",
    "fiscal_data_text = fiscal_data[1].text\n",
    "fiscal_data_text = re.findall('[0-9]+', fiscal_data_text)\n",
    "\n",
    "print(fiscal_data_text)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28d2fd7b",
   "metadata": {},
   "source": [
    "1. `get()` 함수를 통해 페이지의 데이터를 불러온 후, content 부분을 BeautifulSoup 객체로 만든다.\n",
    "2. 결산월 항목운 [corp_group1 클래스의 div 태그 하부의 h2 태그]에 존재하므로, `select()` 함수를 이용해 추출한다.\n",
    "3. fiscal_data 중 첫번째는 종목코드에 해당하고, 두번째가 결산 데이터에 해당하므로 해당 부분을 선택해 텍스트만 추출한다.\n",
    "4. 'n월 결산' 형태로 텍스트가 구성되어 있으므로, 정규 표현식을 이용해 숫자에 해당하는 부분만 추출한다.\n",
    "\n",
    "이를 통해 결산월에 해당하는 부분만이 선택된다. 이를 이용해 연간 재무제표에 해당하는 열만 선택해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "b1c8d7a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>2019/12</th>\n",
       "      <th>2020/12</th>\n",
       "      <th>2021/12</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>매출액</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>2721.0</td>\n",
       "      <td>2930.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>매출원가</td>\n",
       "      <td>1856.0</td>\n",
       "      <td>1334.0</td>\n",
       "      <td>1437.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>매출총이익</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>1387.0</td>\n",
       "      <td>1493.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>판매비와관리비계산에 참여한 계정 펼치기</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>1155.0</td>\n",
       "      <td>1269.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>인건비</td>\n",
       "      <td>400.0</td>\n",
       "      <td>415.0</td>\n",
       "      <td>468.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      계정  2019/12  2020/12  2021/12\n",
       "0                    매출액   3072.0   2721.0   2930.0\n",
       "1                   매출원가   1856.0   1334.0   1437.0\n",
       "2                  매출총이익   1216.0   1387.0   1493.0\n",
       "3  판매비와관리비계산에 참여한 계정 펼치기   1120.0   1155.0   1269.0\n",
       "4                    인건비    400.0    415.0    468.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_fs_y = data_fs_y.loc[:, (data_fs_y.columns == '계정') |\n",
    "                          (data_fs_y.columns.str[-2:].isin(fiscal_data_text))]\n",
    "data_fs_y.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d39ea67",
   "metadata": {},
   "source": [
    "열 이름이 '계정', 그리고 재무제표의 월이 결산월과 같은 부분만 선택한다. 이제 추가적으로 클렌징해야 하는 사항은 다음과 같다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f9d50219",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>2019/12</th>\n",
       "      <th>2020/12</th>\n",
       "      <th>2021/12</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>기타원가성비용</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>대손충당금환입액</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>매출채권처분이익</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>당기손익-공정가치측정 금융자산관련이익</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>금융자산손상차손환입</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      계정  2019/12  2020/12  2021/12\n",
       "10               기타원가성비용      NaN      NaN      NaN\n",
       "18              대손충당금환입액      NaN      NaN      NaN\n",
       "19              매출채권처분이익      NaN      NaN      NaN\n",
       "20  당기손익-공정가치측정 금융자산관련이익      NaN      NaN      NaN\n",
       "23            금융자산손상차손환입      NaN      NaN      NaN"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_fs_y[data_fs_y.loc[:, ~data_fs_y.columns.isin(['계정'])].isna().all(\n",
    "    axis=1)].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56826220",
   "metadata": {},
   "source": [
    "먼저 재무제표 값 중에서 모든 연도의 데이터가 NaN인 항목이 있다. 이는 재무제표 계정은 있으나 해당 종목들은 데이터가 없는 것들이므로 삭제해도 된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "0c242e97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "기타          4\n",
       "배당금수익       3\n",
       "파생상품이익      3\n",
       "이자수익        3\n",
       "법인세납부(-)    3\n",
       "Name: 계정, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_fs_y['계정'].value_counts(ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "624e21ae",
   "metadata": {},
   "source": [
    "또한 동일한 계정명이 여러번 반복된다. 이러한 계정은 대부분 중요하지 않은 것들이므로, 하나만 남겨두도록 한다. 이 외에도 클렌징이 필요한 내용들을 함수로 구성하면 다음과 같다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e12aa01b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_fs(df, ticker, frequency):\n",
    "\n",
    "    df = df[~df.loc[:, ~df.columns.isin(['계정'])].isna().all(axis=1)]\n",
    "    df = df.drop_duplicates(['계정'], keep='first')\n",
    "    df = pd.melt(df, id_vars='계정', var_name='기준일', value_name='값')\n",
    "    df = df[~pd.isnull(df['값'])]\n",
    "    df['계정'] = df['계정'].replace({'계산에 참여한 계정 펼치기': ''}, regex=True)\n",
    "    df['기준일'] = pd.to_datetime(df['기준일'],\n",
    "                               format='%Y-%m') + pd.tseries.offsets.MonthEnd()\n",
    "    df['종목코드'] = ticker\n",
    "    df['공시구분'] = frequency\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "927ee6f4",
   "metadata": {},
   "source": [
    "1. 입력값으로는 데이터프레임, 티커, 공시구분(연간/분기)가 필요하다.\n",
    "2. 먼저 연도의 데이터가 NaN인 항목은 제외한다.\n",
    "3. 계정명이 중복되는 경우 `drop_duplicates()` 함수를 이용해 첫번째에 위치하는 데이터만 남긴다.\n",
    "4. `melt()` 함수를 이용해 열로 긴 데이터를 행으로 긴 데이터로 변경한다.\n",
    "5. 계정값이 없는 항목은 제외한다.\n",
    "6. [계산에 참여한 계정 펼치기]라는 글자는 페이지의 [+]에 해당하는 부분이므로 `replace()` 메서드를 통해 제거한다.\n",
    "7. `to_datetime()` 메서드를 통해 기준일을 'yyyy-mm' 형태로 바꾼 후, `MonthEnd()`를 통해 월말에 해당하는 일을 붙인다.\n",
    "8. '종목코드' 열에는 티커를 입력한다.\n",
    "9. '공시구분' 열에는 연간 혹은 분기에 해당하는 값을 입력한다.\n",
    "\n",
    "연간 재무제표 항목에 위 함수를 적용하면 다음과 같은 결과를 확인할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "961c04ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>기준일</th>\n",
       "      <th>값</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>공시구분</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>3072.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>매출원가</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>1856.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>매출총이익</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>판매비와관리비</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>인건비</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>400.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        계정        기준일       값    종목코드 공시구분\n",
       "0      매출액 2019-12-31  3072.0  000020    y\n",
       "1     매출원가 2019-12-31  1856.0  000020    y\n",
       "2    매출총이익 2019-12-31  1216.0  000020    y\n",
       "3  판매비와관리비 2019-12-31  1120.0  000020    y\n",
       "4      인건비 2019-12-31   400.0  000020    y"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_fs_y_clean = clean_fs(data_fs_y, ticker, 'y')\n",
    "\n",
    "data_fs_y_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f049dd8",
   "metadata": {},
   "source": [
    "클렌징 처리가 된 데이터가 세로로 긴 형태로 변경되었다. 이제 분기 재무제표도 클렌징 처리를 해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "24091fd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>기준일</th>\n",
       "      <th>값</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>공시구분</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>758.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>매출원가</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>374.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>매출총이익</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>384.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>판매비와관리비</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>312.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>인건비</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>116.0</td>\n",
       "      <td>000020</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        계정        기준일      값    종목코드 공시구분\n",
       "0      매출액 2021-06-30  758.0  000020    q\n",
       "1     매출원가 2021-06-30  374.0  000020    q\n",
       "2    매출총이익 2021-06-30  384.0  000020    q\n",
       "3  판매비와관리비 2021-06-30  312.0  000020    q\n",
       "4      인건비 2021-06-30  116.0  000020    q"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 분기 데이터\n",
    "\n",
    "data_fs_q = pd.concat(\n",
    "    [data[1].iloc[:, ~data[1].columns.str.contains('전년동기')], data[3], data[5]])\n",
    "data_fs_q = data_fs_q.rename(columns={data_fs_q.columns[0]: \"계정\"})\n",
    "data_fs_q_clean = clean_fs(data_fs_q, ticker, 'q')\n",
    "\n",
    "data_fs_q_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c1e9b67",
   "metadata": {},
   "source": [
    "분기 데이터는 결산월에 해당하는 부분을 선택할 필요가 없으며, 이를 제외하고는 모든 과정이 연간 재무제표를 항목과 동일하다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "53783fe4",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_fs_bind = pd.concat([data_fs_y_clean, data_fs_q_clean])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90c3cd14",
   "metadata": {},
   "source": [
    "`concat()` 함수를 통해 두 테이블을 하나로 묶어준다.\n",
    "\n",
    "### 전종목 재무제표 크롤링\n",
    "\n",
    "위 과정을 응용해 모든 종목의 재무제표를 크롤링한 후 DB에 저장하는 과정을 살펴보도록 하겠다. 먼저 SQL에서 재무제표가 저장될 테이블(kor_fs)을 만들어준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "931e75d3",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table kor_fs\n",
    "(\n",
    "    계정 varchar(30),\n",
    "    기준일 date,\n",
    "    값 float,\n",
    "    종목코드 varchar(6),\n",
    "    공시구분 varchar(1),\n",
    "    primary key(계정, 기준일, 종목코드, 공시구분)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d95b3375",
   "metadata": {},
   "source": [
    "이제 파이썬에서 아래 코드를 실행하면 for문을 통해 전종목 재무제표가 DB에 저장된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd5805b0",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import requests as rq\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "from tqdm import tqdm\n",
    "import time\n",
    "\n",
    "# DB 연결\n",
    "engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/stock_db')\n",
    "con = pymysql.connect(user='root',\n",
    "                      passwd='1234',\n",
    "                      host='127.0.0.1',\n",
    "                      db='stock_db',\n",
    "                      charset='utf8')\n",
    "mycursor = con.cursor()\n",
    "\n",
    "# 티커리스트 불러오기\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from kor_ticker\n",
    "where 기준일 = (select max(기준일) from kor_ticker) \n",
    "\tand 종목구분 = '보통주';\n",
    "\"\"\", con=engine)\n",
    "\n",
    "# DB 저장 쿼리\n",
    "query = \"\"\"\n",
    "    insert into kor_fs (계정, 기준일, 값, 종목코드, 공시구분)\n",
    "    values (%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    값=new.값\n",
    "\"\"\"\n",
    "\n",
    "# 오류 발생시 저장할 리스트 생성\n",
    "error_list = []\n",
    "\n",
    "\n",
    "# 재무제표 클렌징 함수\n",
    "def clean_fs(df, ticker, frequency):\n",
    "\n",
    "    df = df[~df.loc[:, ~df.columns.isin(['계정'])].isna().all(axis=1)]\n",
    "    df = df.drop_duplicates(['계정'], keep='first')\n",
    "    df = pd.melt(df, id_vars='계정', var_name='기준일', value_name='값')\n",
    "    df = df[~pd.isnull(df['값'])]\n",
    "    df['계정'] = df['계정'].replace({'계산에 참여한 계정 펼치기': ''}, regex=True)\n",
    "    df['기준일'] = pd.to_datetime(df['기준일'],\n",
    "                               format='%Y-%m') + pd.tseries.offsets.MonthEnd()\n",
    "    df['종목코드'] = ticker\n",
    "    df['공시구분'] = frequency\n",
    "\n",
    "    return df\n",
    "\n",
    "\n",
    "# for loop\n",
    "for i in tqdm(range(0, len(ticker_list))):\n",
    "\n",
    "    # 티커 선택\n",
    "    ticker = ticker_list['종목코드'][i]\n",
    "\n",
    "    # 오류 발생 시 이를 무시하고 다음 루프로 진행\n",
    "    try:\n",
    "\n",
    "        # url 생성\n",
    "        url = f'http://comp.fnguide.com/SVO2/ASP/SVD_Finance.asp?pGB=1&gicode=A{ticker}'\n",
    "\n",
    "        # 데이터 받아오기\n",
    "        data = pd.read_html(url, displayed_only=False)\n",
    "\n",
    "        # 연간 데이터\n",
    "        data_fs_y = pd.concat([\n",
    "            data[0].iloc[:, ~data[0].columns.str.contains('전년동기')], data[2],\n",
    "            data[4]\n",
    "        ])\n",
    "        data_fs_y = data_fs_y.rename(columns={data_fs_y.columns[0]: \"계정\"})\n",
    "\n",
    "        # 결산년 찾기\n",
    "        page_data = rq.get(url)\n",
    "        page_data_html = BeautifulSoup(page_data.content, 'html.parser')\n",
    "\n",
    "        fiscal_data = page_data_html.select('div.corp_group1 > h2')\n",
    "        fiscal_data_text = fiscal_data[1].text\n",
    "        fiscal_data_text = re.findall('[0-9]+', fiscal_data_text)\n",
    "\n",
    "        # 결산년에 해당하는 계정만 남기기\n",
    "        data_fs_y = data_fs_y.loc[:, (data_fs_y.columns == '계정') | (\n",
    "            data_fs_y.columns.str[-2:].isin(fiscal_data_text))]\n",
    "\n",
    "        # 클렌징\n",
    "        data_fs_y_clean = clean_fs(data_fs_y, ticker, 'y')\n",
    "\n",
    "        # 분기 데이터\n",
    "        data_fs_q = pd.concat([\n",
    "            data[1].iloc[:, ~data[1].columns.str.contains('전년동기')], data[3],\n",
    "            data[5]\n",
    "        ])\n",
    "        data_fs_q = data_fs_q.rename(columns={data_fs_q.columns[0]: \"계정\"})\n",
    "\n",
    "        data_fs_q_clean = clean_fs(data_fs_q, ticker, 'q')\n",
    "\n",
    "        # 두개 합치기\n",
    "        data_fs_bind = pd.concat([data_fs_y_clean, data_fs_q_clean])\n",
    "\n",
    "        # 재무제표 데이터를 DB에 저장\n",
    "        args = data_fs_bind.values.tolist()\n",
    "        mycursor.executemany(query, args)\n",
    "        con.commit()\n",
    "\n",
    "    except:\n",
    "\n",
    "        # 오류 발생시 해당 종목명을 저장하고 다음 루프로 이동\n",
    "        print(ticker)\n",
    "        error_list.append(ticker)\n",
    "\n",
    "    # 타임슬립 적용\n",
    "    time.sleep(2)\n",
    "\n",
    "# DB 연결 종료\n",
    "engine.dispose()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "602384f7",
   "metadata": {},
   "source": [
    "1. DB에 연결한다.\n",
    "2. 기준일이 최대, 즉 최근일 기준 보통주에 해당하는 티커 리스트(ticker_list)만 불러온다.\n",
    "3. DB에 저장할 쿼리(query)를 입력한다.\n",
    "4. 오류 발생시 저장할 리스트(error_list)를 만든다.\n",
    "5. for문을 통해 전종목 재무제표를 다운로드 받으며, 진행상황을 알기위해 `tqdm()` 함수를 이용한다.\n",
    "6. URL 생성, 데이터 다운로드 및 데이터 클렌징 및 DB에 저장은 위와 동일하며, try except문을 통해 오류가 발생시 티커를 출력 후 error_list에 저장한다.\n",
    "7. 무한 크롤링을 방지하기 위해 한 번의 루프가 끝날 때마다 타임슬립을 적용한다.\n",
    "8. 모든 작업이 끝나면 DB와의 연결을 종료한다.\n",
    "\n",
    "작업이 끝난 후 SQL의 kor_fs 테이블을 확인해보면 전 종목의 재무제표가 저장되어 있다. 시간이 지나 위 코드를 다시 실행하면 upsert 형식을 통해 수정된 재무제표는 update를, 새로 입력된 재무제표는 insert를 한다.\n",
    "\n",
    "```{figure} image/data_korea/sql_fs.png\n",
    "---\n",
    "name: sql_fs\n",
    "---\n",
    "재무제표 테이블\n",
    "```\n",
    "\n",
    "## 가치지표 계산\n",
    "\n",
    "위에서 구한 재무제표 데이터를 이용해 가치지표를 계산할 수 있다. 흔히 가치지표로는 'PER', 'PBR', 'PCR', 'PSR', 'DY'가 사용된다.\n",
    "\n",
    "```{table} 가치지표의 종류\n",
    ":name: value_table\n",
    "\n",
    "| 지표 | 설명 | 필요한 재무제표 데이터 | \n",
    "| --- | --- | --- |\n",
    "| PER | Price to Earnings Ratio | Earnings (순이익) |\n",
    "| PBR | Price to Book Ratio | Book Value (순자산) |\n",
    "| PCR | Price to Cash Flow Ratio | Cash Flow (영업활동현금흐름) |\n",
    "| PSR | Price to Sales Ratio | Sales (매출액) |\n",
    "| DY | Dividend Yield | Dividened (배당) |\n",
    "```\n",
    "\n",
    "가치지표의 경우 연간 재무제표 기준으로 계산할 경우 다음 재무제표가 발표될 때까지 1년이나 기다려야 한다. 반면 분기 재무제표는 3개월 마다 발표되므로 최신 정보를 훨씬 빠르게 반영할 수 있다는 장점이 있으므로 일반적으로 최근 4분기 데이터를 이용해 계산하는 TTM(Trailing Twelve Months) 방법을 많이 사용한다. 먼저 예제로 삼성전자의 가치지표를 계산해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "02397482",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "\n",
    "# DB 연결\n",
    "engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/stock_db')\n",
    "\n",
    "# 티커 리스트\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from kor_ticker\n",
    "where 기준일 = (select max(기준일) from kor_ticker) \n",
    "\tand 종목구분 = '보통주';\n",
    "\"\"\", con=engine)\n",
    "\n",
    "# 삼성전자 분기 재무제표\n",
    "sample_fs = pd.read_sql(\"\"\"\n",
    "select * from kor_fs\n",
    "where 공시구분 = 'q'\n",
    "and 종목코드 = '005930'\n",
    "and 계정 in ('당기순이익', '자본', '영업활동으로인한현금흐름', '매출액');\n",
    "\"\"\", con=engine)\n",
    "\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9af7d62d",
   "metadata": {},
   "source": [
    "1. DB에 연결한다.\n",
    "2. 티커 리스트를 불러온다.\n",
    "3. 삼성전자의 분기공시 자료 중 가치지표 계산에 필요한 항목들만 불러온다.\n",
    "4. DB와의 연결을 종료한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "68511031",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>기준일</th>\n",
       "      <th>값</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>공시구분</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>96345.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>122933.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>108379.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>113246.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>636716.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      계정         기준일         값    종목코드 공시구분\n",
       "0  당기순이익  2021-06-30   96345.0  005930    q\n",
       "1  당기순이익  2021-09-30  122933.0  005930    q\n",
       "2  당기순이익  2021-12-31  108379.0  005930    q\n",
       "3  당기순이익  2022-03-31  113246.0  005930    q\n",
       "4    매출액  2021-06-30  636716.0  005930    q"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_fs = sample_fs.sort_values(['종목코드', '계정', '기준일'])\n",
    "\n",
    "sample_fs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f05d738",
   "metadata": {},
   "source": [
    "먼저 `sort_values()` 함수를 통해 재무제표 데이터를 종목코드, 계정, 기준일 순으로 정렬한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c2dceffe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>기준일</th>\n",
       "      <th>값</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>공시구분</th>\n",
       "      <th>ttm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>96345.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>122933.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>108379.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>113246.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>440903.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>636716.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>739792.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>765655.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>777815.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>2919978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>120865.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>185815.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>206345.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>104531.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>617556.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>자본</td>\n",
       "      <td>2021-06-30</td>\n",
       "      <td>2823240.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>자본</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2967660.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>자본</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>3049000.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>자본</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>3152910.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>11992810.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              계정         기준일          값    종목코드 공시구분         ttm\n",
       "0          당기순이익  2021-06-30    96345.0  005930    q         NaN\n",
       "1          당기순이익  2021-09-30   122933.0  005930    q         NaN\n",
       "2          당기순이익  2021-12-31   108379.0  005930    q         NaN\n",
       "3          당기순이익  2022-03-31   113246.0  005930    q    440903.0\n",
       "4            매출액  2021-06-30   636716.0  005930    q         NaN\n",
       "5            매출액  2021-09-30   739792.0  005930    q         NaN\n",
       "6            매출액  2021-12-31   765655.0  005930    q         NaN\n",
       "7            매출액  2022-03-31   777815.0  005930    q   2919978.0\n",
       "8   영업활동으로인한현금흐름  2021-06-30   120865.0  005930    q         NaN\n",
       "9   영업활동으로인한현금흐름  2021-09-30   185815.0  005930    q         NaN\n",
       "10  영업활동으로인한현금흐름  2021-12-31   206345.0  005930    q         NaN\n",
       "11  영업활동으로인한현금흐름  2022-03-31   104531.0  005930    q    617556.0\n",
       "12            자본  2021-06-30  2823240.0  005930    q         NaN\n",
       "13            자본  2021-09-30  2967660.0  005930    q         NaN\n",
       "14            자본  2021-12-31  3049000.0  005930    q         NaN\n",
       "15            자본  2022-03-31  3152910.0  005930    q  11992810.0"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_fs['ttm'] = sample_fs.groupby(\n",
    "    ['종목코드', '계정'], as_index=False)['값'].rolling(window=4,\n",
    "                                                 min_periods=4).sum()['값']\n",
    "\n",
    "sample_fs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2247f0e",
   "metadata": {},
   "source": [
    "1. 종목코드와 계정을 기준으로 `groupby()` 함수를 통해 그룹을 묶으며, `as_index=False`를 통해 그룹 라벨을 인덱스로 사용하지 않는다.\n",
    "2. `rolling()` 메서드를 통해 4개 기간씩 합계를 구하며, `min_periods` 인자를 통해 데이터가 최소 4개는 있을 경우에만 값을 구한다. 즉 4개 분기 데이터를 통해 TTM 값을 계산하며, 12개월치 데이터가 없을 경우는 계산을 하지 않는다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "fe63c118",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>기준일</th>\n",
       "      <th>값</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>공시구분</th>\n",
       "      <th>ttm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>113246.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>440903.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>매출액</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>777815.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>2919978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>104531.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>617556.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>자본</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>3152910.0</td>\n",
       "      <td>005930</td>\n",
       "      <td>q</td>\n",
       "      <td>2998202.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              계정         기준일          값    종목코드 공시구분        ttm\n",
       "3          당기순이익  2022-03-31   113246.0  005930    q   440903.0\n",
       "7            매출액  2022-03-31   777815.0  005930    q  2919978.0\n",
       "11  영업활동으로인한현금흐름  2022-03-31   104531.0  005930    q   617556.0\n",
       "15            자본  2022-03-31  3152910.0  005930    q  2998202.5"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "sample_fs['ttm'] = np.where(sample_fs['계정'] == '자본',\n",
    "                            sample_fs['ttm'] / 4, sample_fs['ttm'])\n",
    "sample_fs = sample_fs.groupby(['계정', '종목코드']).tail(1)\n",
    "\n",
    "sample_fs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87156847",
   "metadata": {},
   "source": [
    "1. '자본' 항목은 재무상태표에 해당하는 항목이므로 합이 아닌 4로 나누어 평균을 구하며, 타 헝목은 4분기 기준 합을 그대로 사용한다.\n",
    "2. 계정과 종목코드별 그룹을 나누 후 `tail(1)` 함수를 통해 가장 최근 데이터만 선택한다.\n",
    "\n",
    "가치지표 중 분모에 해당하는 재무제표 값들을 계산을 했으며, 분자에 해당하는 시가총액은 티커 리스트에서 구할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b4af615c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>ttm</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>005930</td>\n",
       "      <td>440903.0</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>매출액</td>\n",
       "      <td>005930</td>\n",
       "      <td>2919978.0</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>005930</td>\n",
       "      <td>617556.0</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>자본</td>\n",
       "      <td>005930</td>\n",
       "      <td>2998202.5</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             계정    종목코드        ttm       시가총액         기준일\n",
       "0         당기순이익  005930   440903.0  3659480.0  2022-08-03\n",
       "1           매출액  005930  2919978.0  3659480.0  2022-08-03\n",
       "2  영업활동으로인한현금흐름  005930   617556.0  3659480.0  2022-08-03\n",
       "3            자본  005930  2998202.5  3659480.0  2022-08-03"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_fs_merge = sample_fs[['계정', '종목코드', 'ttm']].merge(\n",
    "    ticker_list[['종목코드', '시가총액', '기준일']], on='종목코드')\n",
    "sample_fs_merge['시가총액'] = sample_fs_merge['시가총액']/100000000\n",
    "\n",
    "sample_fs_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fedd34aa",
   "metadata": {},
   "source": [
    "위에서 계산한 테이블과 티커 리스트 중 필요한 열만 선택해 테이블을 합친다. 재무제표 데이터의 경우 단위가 억원인 반면 시가총액은 원이므로, 시가총액을 억으로 나눠 단위를 맞춰준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "6cf78c82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>계정</th>\n",
       "      <th>종목코드</th>\n",
       "      <th>ttm</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "      <th>value</th>\n",
       "      <th>지표</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>당기순이익</td>\n",
       "      <td>005930</td>\n",
       "      <td>440903.0</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>8.299966</td>\n",
       "      <td>PER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>매출액</td>\n",
       "      <td>005930</td>\n",
       "      <td>2919978.0</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>1.253256</td>\n",
       "      <td>PSR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>영업활동으로인한현금흐름</td>\n",
       "      <td>005930</td>\n",
       "      <td>617556.0</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>5.925746</td>\n",
       "      <td>PCR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>자본</td>\n",
       "      <td>005930</td>\n",
       "      <td>2998202.5</td>\n",
       "      <td>3659480.0</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>1.220558</td>\n",
       "      <td>PBR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             계정    종목코드        ttm       시가총액         기준일     value   지표\n",
       "0         당기순이익  005930   440903.0  3659480.0  2022-08-03  8.299966  PER\n",
       "1           매출액  005930  2919978.0  3659480.0  2022-08-03  1.253256  PSR\n",
       "2  영업활동으로인한현금흐름  005930   617556.0  3659480.0  2022-08-03  5.925746  PCR\n",
       "3            자본  005930  2998202.5  3659480.0  2022-08-03  1.220558  PBR"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_fs_merge['value'] = sample_fs_merge['시가총액'] / sample_fs_merge['ttm']\n",
    "sample_fs_merge['지표'] = np.where(\n",
    "    sample_fs_merge['계정'] == '매출액', 'PSR',\n",
    "    np.where(\n",
    "        sample_fs_merge['계정'] == '영업활동으로인한현금흐름', 'PCR',\n",
    "        np.where(sample_fs_merge['계정'] == '자본', 'PBR',\n",
    "                 np.where(sample_fs_merge['계정'] == '당기순이익', 'PER', None))))\n",
    "\n",
    "sample_fs_merge"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd347f6d",
   "metadata": {},
   "source": [
    "분자(시가총액)를 분모(TTM 기준 재무제표 데이터)로 나누어 가치지표를 계산한 후, 각 지표명을 입력한다.\n",
    "\n",
    "마지막으로 배당수익률의 경우 티커리스트의 데이터를 통해 계산할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b63baf92",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>종목명</th>\n",
       "      <th>시장구분</th>\n",
       "      <th>종가</th>\n",
       "      <th>시가총액</th>\n",
       "      <th>기준일</th>\n",
       "      <th>EPS</th>\n",
       "      <th>선행EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>주당배당금</th>\n",
       "      <th>종목구분</th>\n",
       "      <th>DY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>005930</td>\n",
       "      <td>삼성전자</td>\n",
       "      <td>KOSPI</td>\n",
       "      <td>61300.0</td>\n",
       "      <td>3.659480e+14</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>5777.0</td>\n",
       "      <td>5900.0</td>\n",
       "      <td>43611.0</td>\n",
       "      <td>1444.0</td>\n",
       "      <td>보통주</td>\n",
       "      <td>0.023556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       종목코드   종목명   시장구분       종가          시가총액         기준일     EPS   선행EPS  \\\n",
       "260  005930  삼성전자  KOSPI  61300.0  3.659480e+14  2022-08-03  5777.0  5900.0   \n",
       "\n",
       "         BPS   주당배당금 종목구분        DY  \n",
       "260  43611.0  1444.0  보통주  0.023556  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticker_list_sample = ticker_list[ticker_list['종목코드'] == '005930'].copy()\n",
    "ticker_list_sample['DY'] = ticker_list_sample['주당배당금'] / ticker_list_sample['종가']\n",
    "\n",
    "ticker_list_sample.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3273427d",
   "metadata": {},
   "source": [
    "티커 리스트의 각종 데이터 중 주당배당금을 종가로 나누면 현재시점 기준 배당수익률을 쉽게 계산할 수 있다.\n",
    "\n",
    "### 전 종목 가치지표 계산\n",
    "\n",
    "위 코드를 응용해 전 종목 가치지표를 계산해보도록 하자. 먼저 SQL에서 가치지표가 저장될 테이블(kor_value)을 만들어준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1699a6ad",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table kor_value\n",
    "(\n",
    "종목코드 varchar(6),\n",
    "기준일 date,\n",
    "지표 varchar(3),\n",
    "값 double,\n",
    "primary key (종목코드, 기준일, 지표)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f241daa0",
   "metadata": {},
   "source": [
    "이제 파이썬에서 재무 데이터를 이용해 가치지표를 계산한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "d80e3b0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# DB 연결\n",
    "engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/stock_db')\n",
    "con = pymysql.connect(user='root',\n",
    "                      passwd='1234',\n",
    "                      host='127.0.0.1',\n",
    "                      db='stock_db',\n",
    "                      charset='utf8')\n",
    "mycursor = con.cursor()\n",
    "\n",
    "# 분기 재무제표 불러오기\n",
    "kor_fs = pd.read_sql(\"\"\"\n",
    "select * from kor_fs\n",
    "where 공시구분 = 'q'\n",
    "and 계정 in ('당기순이익', '자본', '영업활동으로인한현금흐름', '매출액');\n",
    "\"\"\", con=engine)\n",
    "\n",
    "# 티커 리스트 불러오기\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from kor_ticker\n",
    "where 기준일 = (select max(기준일) from kor_ticker) \n",
    "and 종목구분 = '보통주';\n",
    "\"\"\", con=engine)\n",
    "\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0eee30ec",
   "metadata": {},
   "source": [
    "1. DB에 연결한다.\n",
    "2. 분기 재무제표와 티커 리스트를 불러온다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d04dd3c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# TTM 구하기\n",
    "kor_fs = kor_fs.sort_values(['종목코드', '계정', '기준일'])\n",
    "kor_fs['ttm'] = kor_fs.groupby(['종목코드', '계정'], as_index=False)['값'].rolling(\n",
    "    window=4, min_periods=4).sum()['값']\n",
    "\n",
    "# 자본은 평균 구하기\n",
    "kor_fs['ttm'] = np.where(kor_fs['계정'] == '자본', kor_fs['ttm'] / 4,\n",
    "                         kor_fs['ttm'])\n",
    "kor_fs = kor_fs.groupby(['계정', '종목코드']).tail(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdc72a84",
   "metadata": {},
   "source": [
    "1. `sort_values()` 함수를 통해 종목코드, 계정, 기준일 순으로 정렬을 한다.\n",
    "2. 종목코드와 계정 별 그룹을 묶은 후, 롤링 합을 통해 TTM 값을 구한다.\n",
    "3. '자본' 항목은 재무상태표에 해당하는 항목이므로 합이 아닌 평균을 구하며, 타 항목은 4분기 기준 합을 그대로 사용한다.\n",
    "4. `tail(1)`을 통해 최근 데이터를 선택한다.\n",
    "\n",
    "이제 티커리스트의 시가총액 데이터를 이용해 가치지표를 계산해주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "8298d871",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>기준일</th>\n",
       "      <th>지표</th>\n",
       "      <th>값</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000020</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>PER</td>\n",
       "      <td>14.9532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000020</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>PSR</td>\n",
       "      <td>0.9660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000020</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>PCR</td>\n",
       "      <td>6.7597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000020</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>PBR</td>\n",
       "      <td>0.8314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드         기준일   지표        값\n",
       "0  000020  2022-08-03  PER  14.9532\n",
       "1  000020  2022-08-03  PSR   0.9660\n",
       "2  000020  2022-08-03  PCR   6.7597\n",
       "3  000020  2022-08-03  PBR   0.8314"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kor_fs_merge = kor_fs[['계정', '종목코드',\n",
    "                       'ttm']].merge(ticker_list[['종목코드', '시가총액', '기준일']],\n",
    "                                     on='종목코드')\n",
    "kor_fs_merge['시가총액'] = kor_fs_merge['시가총액'] / 100000000\n",
    "\n",
    "kor_fs_merge['value'] = kor_fs_merge['시가총액'] / kor_fs_merge['ttm']\n",
    "kor_fs_merge['value'] = kor_fs_merge['value'].round(4)\n",
    "kor_fs_merge['지표'] = np.where(\n",
    "    kor_fs_merge['계정'] == '매출액', 'PSR',\n",
    "    np.where(\n",
    "        kor_fs_merge['계정'] == '영업활동으로인한현금흐름', 'PCR',\n",
    "        np.where(kor_fs_merge['계정'] == '자본', 'PBR',\n",
    "                 np.where(kor_fs_merge['계정'] == '당기순이익', 'PER', None))))\n",
    "\n",
    "kor_fs_merge.rename(columns={'value': '값'}, inplace=True)\n",
    "kor_fs_merge = kor_fs_merge[['종목코드', '기준일', '지표', '값']]\n",
    "kor_fs_merge = kor_fs_merge.replace([np.inf, -np.inf, np.nan], None)\n",
    "\n",
    "kor_fs_merge.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a48ba55",
   "metadata": {},
   "source": [
    "1. TTM 기준으로 계산된 재무제표 테이블과 티커리스트 테이블을 합친다.\n",
    "2. 시가총액을 억원으로 나눈다.\n",
    "3. 시가총액을 재무데이터 값으로 나누어 가치지표를 계산한 후, 반올림을 한다.\n",
    "4. 각 계정에 맞게 계정명(PSR, PCR, PER, PBR)을 적는다.\n",
    "5. `rename()` 메서드를 통해 'value'라는 열 이름을 '값'으로 변경한다.\n",
    "6. 필요한 열만 선택한 후, `replace()` 메서드를 통해 inf와 nan를 None으로 변경한다.\n",
    "\n",
    "계산된 가치지표를 데이터베이스에 저장하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "971dfdfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    insert into kor_value (종목코드, 기준일, 지표, 값)\n",
    "    values (%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    값=new.값\n",
    "\"\"\"\n",
    "\n",
    "args_fs = kor_fs_merge.values.tolist()\n",
    "mycursor.executemany(query, args_fs)\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3a383e6",
   "metadata": {},
   "source": [
    "가치지표를 kor_value 테이블에 upsert 방식으로 저장한다. 마지막으로 배당수익률의 경우 티커리스트를 통해 한번에 계산할 수 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6ba65f81",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>종목코드</th>\n",
       "      <th>기준일</th>\n",
       "      <th>지표</th>\n",
       "      <th>값</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000020</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>DY</td>\n",
       "      <td>0.0170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000050</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>DY</td>\n",
       "      <td>0.0097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000060</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>DY</td>\n",
       "      <td>0.0172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000070</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>DY</td>\n",
       "      <td>0.0414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>000080</td>\n",
       "      <td>2022-08-03</td>\n",
       "      <td>DY</td>\n",
       "      <td>0.0256</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     종목코드         기준일  지표       값\n",
       "0  000020  2022-08-03  DY  0.0170\n",
       "2  000050  2022-08-03  DY  0.0097\n",
       "3  000060  2022-08-03  DY  0.0172\n",
       "4  000070  2022-08-03  DY  0.0414\n",
       "5  000080  2022-08-03  DY  0.0256"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticker_list['값'] = ticker_list['주당배당금'] / ticker_list['종가']\n",
    "ticker_list['값'] = ticker_list['값'].round(4)\n",
    "ticker_list['지표'] = 'DY'\n",
    "dy_list = ticker_list[['종목코드', '기준일', '지표', '값']]\n",
    "dy_list = dy_list.replace([np.inf, -np.inf, np.nan], None)\n",
    "dy_list = dy_list[dy_list['값'] != 0]\n",
    "\n",
    "dy_list.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "335716d4",
   "metadata": {},
   "source": [
    "1. 주당배당금을 종가로 나누어 배당수익률을 계산한 후, 반올림을 한다.\n",
    "2. '지표'열에 'DY'라는 글자를 입력한다.\n",
    "3. 원하는 열만 선택한다.\n",
    "4. inf와 nan은 None으로 변경한다.\n",
    "5. 주당배당금이 0원인 종목은 값이 0으로 계산되므로, 이를 제외한 종목만 선택한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "2a8f997a",
   "metadata": {},
   "outputs": [],
   "source": [
    "args_dy = dy_list.values.tolist()\n",
    "mycursor.executemany(query, args_dy)\n",
    "con.commit()\n",
    "\n",
    "engine.dispose()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc30781a",
   "metadata": {},
   "source": [
    "배당수익률 역시 kor_value 테이블에 upsert 방식으로 저장한 후, DB와의 연결을 종료한다.\n",
    "\n",
    "```{figure} image/data_korea/sql_value.png\n",
    "---\n",
    "name: sql_value\n",
    "---\n",
    "가치지표 테이블\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "position": {
    "height": "468.844px",
    "left": "1483px",
    "right": "20px",
    "top": "120px",
    "width": "350px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}