data_global_revision.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2c227c32",
   "metadata": {},
   "source": [
    "# 전 세계 주식 데이터 수집하기\n",
    "\n",
    "퀀트 투자의 장점은 데이터만 있다면 동일한 투자 전략을 전 세계 모든 국가에 적용할 수 있다는 점이다. 이번 장에서는 전 세계 종목의 티커 수집 및 주가, 재무제표, 가치지표를 다운로드 하는 방법에 대해 알아보겠다.\n",
    "\n",
    "## 유료 데이터 벤더 이용하기\n",
    "\n",
    "미국 시장의 데이터만 필요할 경우 유료 데이터 벤더를 이용하는 것도 좋은 방법이다. 미국에는 금융 데이터를 API로 제공하는 수많은 업체가 있으며, tiingo의 경우는 월 $10만 지불하면 미국과 중국의 4만여개 종목에 대한 데이터를 API 형태로 받을 수 있다. 이는 상장폐지된 종목을 커버할 뿐만 아니라, API를 이용하므로 크롤링과는 비교할 수 없는 속도로 데이터를 받을 수 있다는 장점이 있다. 이 외에도 Alpha Vantage, Quandl, Polygon 등 수많은 데이터 벤더가 존재한다.\n",
    "\n",
    "```{figure} image/data_global/tiingo.png\n",
    "---\n",
    "scale: 70%\n",
    "name: tiingo\n",
    "---\n",
    "tiingo의 유/무료 서비스 비교\n",
    "```\n",
    "\n",
    "tiingo는 무료 계정도 하루 1,000회까지 API 요청을 할 수 있으며, 파이썬에서 사용할 수 있는 패키지도 있으므로 이를 사용해 데이터를 수집해보도록 하겠다. \n",
    "\n",
    "### 가입 및 API token 받기\n",
    "\n",
    "먼저 https://api.tiingo.com/ 사이트에 접속하여 우측 상단의 [Sign-up]을 클릭해 회원가입을 한다. 그 후 로그인을 한 후 우측 상단에서 본인의 ID를 클릭한 후 [Account]를 선택, 좌측 메뉴의 [API] 부분에서 [Token]을 클릭하면 본인의 API token을 확인할 수 있다.\n",
    "\n",
    "```{figure} image/data_global/token.png\n",
    "---\n",
    "name: token\n",
    "---\n",
    "API token 확인\n",
    "```\n",
    "\n",
    "발급받은 토큰을 PC에 저장할 경우, keyring 패키지를 이용하면 안전하게 저장할 수 있다. 패키지를 이용해 암호나 키 값을 저장하는 법은 다음과 같다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bd278e0",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "import keyring\n",
    "\n",
    "keyring.set_password('System', 'User Name', 'Password')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1b8371e",
   "metadata": {},
   "source": [
    "[System]에는 시스템 종류, [User Name]에는 본인의 이름, [Password]에는 발급받은 API Key를 입력한다. 한번 입력된 값은 계속 저장되어 있다. 저장한 키를 불러오는 법은 다음과 같다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "138655e3",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "api_key = keyring.get_password('System', 'User Name')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5984f437",
   "metadata": {},
   "source": [
    "이제 'Sysyem'에는 'tiingo', 'User Name'에는 본인의 이름, 'Password'에는 위에서 발급받은 API Token을 입력해 토큰을 저장하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89cf3139",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "import keyring\n",
    "\n",
    "keyring.set_password('tiingo', 'User Name', 'Your API Token')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "347971ef",
   "metadata": {},
   "source": [
    "### 데이터 다운로드\n",
    "\n",
    "tiingo 패키지를 이용해 데이터를 받아보도록 하겠다. 데이터를 받기위해 API 접속환경을 셋팅한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "262a6ba2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tiingo import TiingoClient\n",
    "import pandas as pd\n",
    "import keyring\n",
    "\n",
    "api_key = keyring.get_password('tiingo', 'Henry')\n",
    "config = {}\n",
    "config['session'] = True\n",
    "config['api_key'] = api_key\n",
    "client = TiingoClient(config)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20e6d5fb",
   "metadata": {},
   "source": [
    "API token을 불러온 후, 접속환경에 해당하는 config에 이를 입력한다.\n",
    "\n",
    "먼저 tiingo에서 제공하는 종목은 어떠한 것이 있는지 티커 정보들을 확인해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecd20353",
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers = client.list_stock_tickers()\n",
    "tickers_df = pd.DataFrame.from_records(tickers)\n",
    "\n",
    "tickers_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4cfe316",
   "metadata": {},
   "source": [
    "`list_stock_tickers()` 메서드를 통해 티커 정보를 받아올 수 있다. ticker(티커), exchange(거래소), assetType(주식 종류), priceCurrency(거래 통화), startDate(시작일), endDate(마감일) 정보가 표시된다. 거래소와 통화 별 종목이 몇개가 있는지 확인해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b619209",
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers_df.groupby(['exchange', 'priceCurrency'])['ticker'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68f83651",
   "metadata": {},
   "source": [
    "이 중 마이너 거래소나 장외 거래소의 경우 정보를 받아도 우리나라의 증권사를 통해서는 실제로 거래를 할 수 없을수도 있다. 따라서 실제 거래가 가능한 거래소 데이터만 필터링한 후 해당 종목들을 받는 것이 효율적이다.\n",
    "\n",
    "각 종목의 상세 정보를 확인해보도록 하며, 예로써 애플(AAPL)을 이용한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83bb54c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "ticker_metadata = client.get_ticker_metadata(\"AAPL\")\n",
    "print(ticker_metadata)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "488fa4b6",
   "metadata": {},
   "source": [
    "`get_ticker_metadata()` 메서드 내에 티커를 입력하면 티커, 종목명, 사업내역 등 대략적인 정보를 받아올 수 있다.\n",
    "\n",
    "이제 주가를 받아보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9254f8cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "historical_prices = client.get_dataframe(\"AAPL\",\n",
    "                                         startDate='2017-08-01',\n",
    "                                         frequency='daily')\n",
    "\n",
    "historical_prices.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3afe1a69",
   "metadata": {},
   "source": [
    "`get_dataframe()` 메서드 내에 티커를 입력하면 close(종가), high(고가), low(저가), open(시가), volumne(거래량) 및 수정주가와 divCash(현금 배당), splitFactor(주식분할 조정계수)까지 데이터를 받을 수 있다.\n",
    "\n",
    "이번에는 일별 가치지표를 받아보도록 하자. (무료 계정의 경우 다우존스 30 지수에 포함되는 종목 정보만 제공한다.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cf9efa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "fundamentals_daily = client.get_fundamentals_daily('AAPL')\n",
    "fundamentals_daily_df = pd.DataFrame.from_records(fundamentals_daily)\n",
    "\n",
    "fundamentals_daily_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00c81f36",
   "metadata": {},
   "source": [
    "`get_fundamentals_daily()` 메서드 내에 티커를 입력하면 일간 시가총액, 기업가치, PER, PBR, PEG 정보가 JSON 형태로 받아지며, `from_records()` 메서드를 통해 데이터프레임 형태로 변경해준다.\n",
    "\n",
    "마지막으로 재무제표를 받아보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0499a3a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "fundamentals_stmnts = client.get_fundamentals_statements(\n",
    "    'AAPL', startDate='2019-01-01', asReported=True, fmt='csv')\n",
    "\n",
    "df_fs = pd.DataFrame([x.split(',') for x in fundamentals_stmnts.split('\\n')])\n",
    "df_fs.columns = df_fs.iloc[0]\n",
    "df_fs = df_fs[1:]\n",
    "df_fs.set_index('date', drop=True, inplace=True)\n",
    "df_fs = df_fs[df_fs.index != '']\n",
    "\n",
    "df_fs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b37f4c3",
   "metadata": {},
   "source": [
    "1. `get_fundamentals_statements()` 메서드 내에 티커를 입력하면 재무제표의 세부항목을 받을 수 있다. 또한 `fmt`은 포맷 형태를 의미하며, JSON으로 받을 경우 형태가 지나치게 복잡하므로 CSV로 받는 것이 좋다.\n",
    "2. 텍스트 형태로 데이터가 들어오므로, 클렌징을 통해 데이터프레임 형태로 변경한다.\n",
    "3. 첫번째 행을 열 이름으로 지정한 후, 해당 행은 삭제한다.\n",
    "4. 'date' 열을 인덱스로 지정한다.\n",
    "5. 'date'가 비어있는 부분이 있으므로 이를 제거한다.\n",
    "\n",
    "결과를 확인해보면 연간 재무제표와 분기 재무제표의 상세 정보가 다운로드 될 뿐만 아니라 발표 날짜 또한 제공된다. 이처럼 유료 벤더를 이용하면 티커 및 주가, 재무제표, 가치지표를 매우 쉽고 빠르게 받을 수 있다. \n",
    "\n",
    "tiingo의 API 사용법 및 파이썬 패키지 사용법은 아래 페이지에 나와있다.\n",
    "\n",
    "- tiingo API: https://api.tiingo.com/documentation/general/overview\n",
    "- 파이썬 패키지: https://github.com/hydrosquall/tiingo-python\n",
    "\n",
    "## 티커 수집하기\n",
    "\n",
    "이번에는 크롤링을 통해 데이터를 수집하는 방법에 대해 알아보겠다. 우리나라는 한국거래소를 통해 티커를 손쉽게 수집할 수 있지만 해외의 경우는 그렇지 않다. 먼저 우리나라와 달리 국가 별로 거래소가 여러개인 경우도 있으며, 홈페이지에 상장 종목 리스트를 제공하지 않는 경우도 많기 때문이다. \n",
    "\n",
    "다행히 투자자들이 많이 참조하는 사이트인 인베스팅닷컴(https://www.investing.com/)에서는 전 세계 주식 및 각종 금융 데이터를 제공하고 있다. 이 중 스크리닝 기능을 활용하면 각 국가별 티커 리스트를 수집할 수 있다. 먼저 인베스팅닷컴에 접속한 후 [Markets → Stocks → Stock Screener]에 접속한다.\n",
    "\n",
    "```{figure} image/data_global/screen.png\n",
    "---\n",
    "name: screen\n",
    "---\n",
    "인베스팅닷컴의 주식 스크리너\n",
    "```\n",
    "\n",
    "페이지를 접속하면 미국 종목들이 나타나며, URL은 다음과 같다. \n",
    "\n",
    "```\n",
    "https://www.investing.com/stock-screener/?sp=country::5|sector::a|industry::a|equityType::a%3Ceq_market_cap;1\n",
    "```\n",
    "\n",
    "하단에 표로 나타는 정보 중 Symbol이 티커에 해당하며, 이를 통해 티커를 손쉽게 수집할 수 있다. 다음으로 국가를 Japan(일본)으로 선택하고, Equity Type은 ORD(보통주)를 선택하자.\n",
    "\n",
    "```{figure} image/data_global/japan.png\n",
    "---\n",
    "name: japan\n",
    "---\n",
    "일본 국가 선택\n",
    "```\n",
    "\n",
    "하단의 테이블이 일본 종목들로 바뀌며 URL 역시 다음과 같이 바뀐다.\n",
    "\n",
    "```\n",
    "https://www.investing.com/stock-screener/?sp=country::35|sector::a|industry::a|equityType::ORD%3Ceq_market_cap;1\n",
    "```\n",
    "\n",
    "즉 기존 URL 중 국가에 해당하는 'country' 부분이 5에서 35로, 주식 종류에 해당하는 'equityType' 부분이 a에서 ORD로 변경되었다. 해당 페이지는 동적으로 페이지가 바뀌므로 셀레니움을 통해 크롤링을 해야한다. 예제로 미국의 보통주 전종목 정보를 크롤링 해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a845cf4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "from selenium.webdriver.common.by import By\n",
    "from bs4 import BeautifulSoup\n",
    "import math\n",
    "import pandas as pd\n",
    "\n",
    "driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()))\n",
    "url = 'https://www.investing.com/stock-screener/?sp=country::5|sector::a|industry::a|equityType::ORD|exchange::a%3Ceq_market_cap;1'\n",
    "driver.get(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83b62a11",
   "metadata": {},
   "source": [
    "1. 먼저 크롬 드라이버를 설정한다.\n",
    "2. 미국 보통주에 해당하는 URL을 입력한다.\n",
    "3. 해당 페이지를 연다.\n",
    "\n",
    "```{figure} image/data_global/selenium_open.png\n",
    "---\n",
    "name: selenium_open\n",
    "---\n",
    "셀레니움을 이용한 스크리너 접속\n",
    "```\n",
    "\n",
    "다음으로 HTML 정보를 가져오도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "61273db2",
   "metadata": {},
   "outputs": [],
   "source": [
    "html = BeautifulSoup(driver.page_source, 'lxml')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e6b0120",
   "metadata": {},
   "source": [
    "HTML 정보에 해당하는 `driver.page_source`를 BeautifulSoup 객체로 만들어준다. 이제 우리가 찾고자 하는 데이터를 하나씩 살펴보도록 한다. 먼저 각 국가별 코드를 살펴보도록 하자. 개발자도구 화면에서 'newBtnDropdown noHover' 클래스 하단의 'li 태그의 'data-value' 속성을 살펴보면 국가별 코드와 국가명이 적혀있다.\n",
    "\n",
    "```{figure} image/data_global/country_ticker.png\n",
    "---\n",
    "name: country_ticker\n",
    "---\n",
    "국가별 코드\n",
    "```\n",
    "\n",
    "이번에는 위젯에서 선택되어 있는 국가명을 확인해보도록 하자. 'newBtnDropdown noHover' 클래스 하단의 'input' 태그의 'value' 속성의 속성명에는 국가명이 적혀 있다. 이를 코드를 통해 찾아보도록 하자.\n",
    "\n",
    "```{figure} image/data_global/country_name.png\n",
    "---\n",
    "name: country_name\n",
    "---\n",
    "국가명 확인\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2752c0b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'United States'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "html.find(class_='js-search-input inputDropDown')['value']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c962a1fd",
   "metadata": {},
   "source": [
    "이번에는 종목들의 정보가 있는 테이블을 확인해보도록 하자. 클래스 명이 'genTbl openTbl resultsStockScreenerTbl elpTbl' 인 테이블 중 'tbody' 부분에 해당 데이터가 위치하고 있다. 이 정보를 이용해 해당 테이블 데이터를 선택하자.\n",
    "\n",
    "```{figure} image/data_global/screen_table.png\n",
    "---\n",
    "name: screen_table\n",
    "---\n",
    "종목 테이블\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d0f8b272",
   "metadata": {},
   "outputs": [],
   "source": [
    "html_table = html.select('table.genTbl.openTbl.resultsStockScreenerTbl.elpTbl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9354c07",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "print(html_table[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab875d45",
   "metadata": {},
   "source": [
    "```{figure} image/data_global/selenium_1.png\n",
    "---\n",
    "name: selenium_1\n",
    "---\n",
    "```\n",
    "\n",
    "`select` 함수를 이용해 table 태그 중 해당 클래스명을 찾은 후 출력하면, 종목 정보들이 담긴 테이블의 HTML 정보가 출력된다. 이제 이를 데이터프레임 형태로 변환해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f02eecfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_table = pd.read_html(html_table[0].prettify())\n",
    "df_table_result = df_table[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a7c01ce",
   "metadata": {},
   "source": [
    "`prettify()` 메서드를 이용해 BeautifulSoup 에서 파싱한 파서 트리를 유니코드 형태로 다시 돌려준 후, `read_html()` 함수를 통해 테이블을 읽어온다. Variable Explorer 창에서 df_table_result 변수를 확인해보자.\n",
    "\n",
    "```{figure} image/data_global/selenium_2.png\n",
    "---\n",
    "name: selenium_2\n",
    "---\n",
    "크롤링 결과 확인\n",
    "```\n",
    "\n",
    "결과를 살펴보면 웹페이지에 있는 내역 외에도 Exchange(거래소), Sector, Industy 등 추가적인 정보를 확인할 수 있다. 이 중 필요한 열만 선택하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a687db91",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_table_select = df_table[0][['Name', 'Symbol', 'Exchange',  'Sector', 'Market Cap']]\n",
    "df_table_select.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1df70bdf",
   "metadata": {},
   "source": [
    "마지막으로 종목 정보가 몇페이지까지 있는지 확인해야 한다. 웹페이지의 'Screener Results' 글자 뒤에는 해당 국가에 총 몇 종목이 있는지 출력된다. 한 페이지에는 총 50 종목이 출력되므로 해당 숫자를 50으로 나눈 후 올림을 하면 총 페이지 수를 계산할 수 있다. 해당 정보는 'js-total-results' 클래스에 위치하고 있으며, 이를 이용해 페이지 수를 계산해보도록 하자.\n",
    "\n",
    "```{figure} image/data_global/country_page.png\n",
    "---\n",
    "name: country_page\n",
    "---\n",
    "종목 수 확인\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4afa6076",
   "metadata": {},
   "outputs": [],
   "source": [
    "end_num = driver.find_element(By.CLASS_NAME, value = 'js-total-results').text\n",
    "print(math.ceil(int(end_num) / 50))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35dd018b",
   "metadata": {},
   "source": [
    "마지막으로 드라이브롤 종료해준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93874d57",
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eda133f0",
   "metadata": {},
   "source": [
    "### 전 종목 티커 크롤링\n",
    "\n",
    "위 과정을 통해 국가별 전 종목의 티커 및 관련 정보를 수집하는 방법과, 페이지 수를 계산할 수 있었다. 이제 for문을 이용해 미국의 전 종목 티커를 크롤링해보도록 하겠다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e47a420",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from bs4 import BeautifulSoup\n",
    "from datetime import datetime\n",
    "import math\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import time\n",
    "\n",
    "driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()))\n",
    "nationcode = '5'\n",
    "url = f'''https://investing.com/stock-screener/?sp=country::\n",
    "{nationcode}|sector::a|industry::a|equityType::ORD%3Ceq_market_cap;1'''\n",
    "driver.get(url)\n",
    "\n",
    "WebDriverWait(driver, 10).until(EC.visibility_of_element_located(\n",
    "    (By.XPATH, '//*[@id=\"resultsTable\"]/tbody')))\n",
    "\n",
    "end_num = driver.find_element(By.CLASS_NAME, value='js-total-results').text\n",
    "end_num = math.ceil(int(end_num) / 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e02fd43d",
   "metadata": {},
   "source": [
    "1. 크롬 드라이브를 불러온다.\n",
    "2. 국가 코드는 미국에 해당하는 '5'를 입력한다.\n",
    "3. 먼저 첫페이지에 해당하는 URL을 생성한다.\n",
    "4. 셀레니움으로 해당 페이지를 연다\n",
    "5. 'Screener Results'에 해당하는 부분은 종목이 들어있는 테이블이 로딩된 이후 나타난다. 따라서 `WebDriverWait()` 함수를 통해 해당 테이블이 로딩될 때까지 기다리며, 테이블의 XPATH는 '//*[@id=\"resultsTable\"]/tbody' 이다.\n",
    "6. 종목수에 해당하는 부분을 크롤링한 후, 이를 통해 페이지 수를 계산한다.\n",
    "\n",
    "이제 for문을 통해 모든 페이지의 데이터를 크롤링해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "557a8b77",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "all_data_df = []\n",
    "\n",
    "for i in tqdm(range(1, end_num + 1)):\n",
    "\n",
    "    url = f'''https://investing.com/stock-screener/?sp=country::\n",
    "        {nationcode}|sector::a|industry::a|equityType::ORD%3Ceq_market_cap;{i}'''\n",
    "    driver.get(url)\n",
    "\n",
    "    try:\n",
    "        WebDriverWait(driver, 10).until(EC.visibility_of_element_located(\n",
    "            (By.XPATH, '//*[@id=\"resultsTable\"]/tbody')))\n",
    "    except:\n",
    "        time.sleep(1)\n",
    "        driver.refresh()\n",
    "        WebDriverWait(driver, 10).until(EC.visibility_of_element_located(\n",
    "            (By.XPATH, '//*[@id=\"resultsTable\"]/tbody')))\n",
    "\n",
    "    html = BeautifulSoup(driver.page_source, 'lxml')\n",
    "\n",
    "    html_table = html.select(\n",
    "        'table.genTbl.openTbl.resultsStockScreenerTbl.elpTbl')\n",
    "    df_table = pd.read_html(html_table[0].prettify())\n",
    "    df_table_select = df_table[0][['Name', 'Symbol',\n",
    "                                   'Exchange',  'Sector', 'Market Cap']]\n",
    "\n",
    "    all_data_df.append(df_table_select)\n",
    "\n",
    "    time.sleep(2)\n",
    "\n",
    "all_data_df_bind = pd.concat(all_data_df, axis=0)\n",
    "\n",
    "data_country = html.find(class_='js-search-input inputDropDown')['value']\n",
    "all_data_df_bind['country'] = data_country\n",
    "all_data_df_bind['date'] = datetime.today().strftime('%Y-%m-%d')\n",
    "all_data_df_bind = all_data_df_bind[~all_data_df_bind['Name'].isnull()]\n",
    "all_data_df_bind = all_data_df_bind[all_data_df_bind['Exchange'].isin(\n",
    "    ['NASDAQ', 'NYSE', 'NYSE Amex'])]\n",
    "all_data_df_bind = all_data_df_bind.drop_duplicates(['Symbol'])\n",
    "all_data_df_bind.reset_index(inplace=True, drop=True)\n",
    "all_data_df_bind = all_data_df_bind.replace({np.nan: None})\n",
    "\n",
    "driver.quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39679da2",
   "metadata": {},
   "source": [
    "1. 빈 리스트(all_data_df)를 생성한다.\n",
    "2. for문을 통해 전체 페이지에서 종목명과 티커 등의 정보를 크롤링한다.\n",
    "3. f-string을 통해 각 페이지에 해당하는 URL을 생성한 후 페이지를 연다.\n",
    "4. `WebDriverWait()` 함수를 통해 테이블이 로딩될때 까지 기다린다. 또한 간혹 페이지 오류가 발생할 때가 있으므로, try except문을 이용해 오류 발생 시 1초간 기다린 후 `refresh()`를 통해 새로고침을 하여 다시 테이블이 로딩되길 기다린다.\n",
    "5. HTML 정보를 불러온 후, 테이블에 해당하는 부분을 선택한다.\n",
    "6. 원하는 열만 선택한다.\n",
    "7. `append()` 메서드를 통해 해당 테이블을 리스트에 추가한다.\n",
    "8. 2초가 일시정지를 한다.\n",
    "9. for문이 끝나면 `concat()` 함수를 통해 리스트 내 모든 데이터프레임을 행으로 묶어준다.\n",
    "10. 국가명에 해당하는 부분을 추출한 뒤, 'country' 열에 입력한다.\n",
    "11. 'date' 열에 오늘 날짜를 입력한다.\n",
    "12. 일부 종목의 경우 종목명이 빈칸으로 들어오므로 이를 제거한다.\n",
    "13. 'Exchange' 열에서 거래가 가능한 거래소만 선택한다.\n",
    "14. 일부 종목의 경우 중복된 결과가 들어오기도 하므로 `drop_duplicates()` 메서드를 통해 Symbol이 겹치는 경우 한개만 남겨준다.\n",
    "14. `reset_index()` 메서드를 통해 인덱스를 초기화한다.\n",
    "15. nan을 None으로 변경한다.\n",
    "16. 드라이브롤 종료한다.\n",
    "\n",
    "마지막으로 위 데이터프레임을 SQL에 저장해주도록 한다. 먼저 SQL에서 다음의 쿼리를 통해 테이블(global_ticker)을 만든다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a718d185",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table global_ticker\n",
    "(\n",
    "    Name varchar(50) not null,\n",
    "    Symbol varchar(30),\n",
    "    Exchange varchar(30),\n",
    "    Sector varchar(40),\n",
    "    `Market Cap` varchar(10),\n",
    "    country varchar(20),    \n",
    "    date date,\n",
    "    primary key(Symbol, country, date)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1c8108b",
   "metadata": {},
   "source": [
    "위에서 구한 티커 데이터를 해당 테이블에 저장한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa4f61b9",
   "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 = \"\"\"\n",
    "    insert into global_ticker (Name, Symbol, Exchange, Sector, `Market Cap`, country, date)\n",
    "    values (%s,%s,%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    name=new.name,Exchange=new.Exchange,Sector=new.Sector,\n",
    "    `Market Cap`=new.`Market Cap`; \n",
    "\"\"\"\n",
    "\n",
    "args = all_data_df_bind.values.tolist()\n",
    "\n",
    "mycursor.executemany(query, args)\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0bca42a",
   "metadata": {
    "id": "f0bca42a"
   },
   "source": [
    "```{figure} image/data_global/ticker_sql.png\n",
    "---\n",
    "name: ticker_sql\n",
    "---\n",
    "글로벌 티커 테이블\n",
    "```\n",
    "\n",
    "```{note}\n",
    "- nationcode 부분만 변경하면 모든 국가의 티커 리스트 역시 동일한 방법으로 다운로드 받을 수 있다.\n",
    "```\n",
    "\n",
    "## 주가 다운로드\n",
    "\n",
    "야후 파이낸스에서는 전 세계 주가(한국 포함)를 제공하고 있다.\n",
    "\n",
    "```\n",
    "https://finance.yahoo.com/\n",
    "```\n",
    "\n",
    "사이트에서 종목 티커를 검색한 후 [Historical Data] 탭을 선택하면 확인 및 다운로드가 가능하다. 또한 야후 API를 통해 해당 데이터를 손쉽게 다운로드 받을 수 있는 패키지들이 있으므로, 이를 이용해 먼저 애플(AAPL)의 주가를 다운로드 받아보도록 하자.\n",
    "\n",
    "```{figure} image/data_global/yahoo_price.png\n",
    "---\n",
    "name: yahoo_price\n",
    "---\n",
    "야후에서 제공하는 주가 데이터\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "56bd2cfa",
   "metadata": {
    "id": "56bd2cfa",
    "outputId": "90772661-20f3-4ea2-d030-be67d1793d0d"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[*********************100%%**********************]  1 of 1 completed\n"
     ]
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</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>1980-12-12</th>\n",
       "      <td>0.128348</td>\n",
       "      <td>0.128906</td>\n",
       "      <td>0.128348</td>\n",
       "      <td>0.128348</td>\n",
       "      <td>0.099192</td>\n",
       "      <td>469033600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-15</th>\n",
       "      <td>0.122210</td>\n",
       "      <td>0.122210</td>\n",
       "      <td>0.121652</td>\n",
       "      <td>0.121652</td>\n",
       "      <td>0.094017</td>\n",
       "      <td>175884800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-16</th>\n",
       "      <td>0.113281</td>\n",
       "      <td>0.113281</td>\n",
       "      <td>0.112723</td>\n",
       "      <td>0.112723</td>\n",
       "      <td>0.087117</td>\n",
       "      <td>105728000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-17</th>\n",
       "      <td>0.115513</td>\n",
       "      <td>0.116071</td>\n",
       "      <td>0.115513</td>\n",
       "      <td>0.115513</td>\n",
       "      <td>0.089273</td>\n",
       "      <td>86441600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-18</th>\n",
       "      <td>0.118862</td>\n",
       "      <td>0.119420</td>\n",
       "      <td>0.118862</td>\n",
       "      <td>0.118862</td>\n",
       "      <td>0.091861</td>\n",
       "      <td>73449600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Open      High       Low     Close  Adj Close     Volume\n",
       "Date                                                                    \n",
       "1980-12-12  0.128348  0.128906  0.128348  0.128348   0.099192  469033600\n",
       "1980-12-15  0.122210  0.122210  0.121652  0.121652   0.094017  175884800\n",
       "1980-12-16  0.113281  0.113281  0.112723  0.112723   0.087117  105728000\n",
       "1980-12-17  0.115513  0.116071  0.115513  0.115513   0.089273   86441600\n",
       "1980-12-18  0.118862  0.119420  0.118862  0.118862   0.091861   73449600"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import yfinance as yf\n",
    "\n",
    "price =  yf.download('AAPL')\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78ec6bb2",
   "metadata": {
    "id": "78ec6bb2"
   },
   "source": [
    "yfinance 패키지의 `download()` 함수 내에 티커를 입력하면 주가 정보를 매우 손쉽게 받을 수 있다. [1 of 1 completed] 부분에 해당하는 진행과정을 출력하고 싶지 않을 시, `progress=False` 인자를 추가하면 된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f5c7cece",
   "metadata": {
    "id": "f5c7cece"
   },
   "outputs": [],
   "source": [
    "price = yf.download('AAPL', progress = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3588d23b",
   "metadata": {
    "id": "3588d23b"
   },
   "source": [
    "`start` 인자를 추가하면 데이터 다운로드 기간을 변경할 수도 있다. 2000년 1월 1일부터 데이터를 받는법은 다음가 같다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b93c1325",
   "metadata": {
    "id": "b93c1325",
    "outputId": "51e2898e-b61b-4437-d8fb-7162635cc182"
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</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>2000-01-03</th>\n",
       "      <td>0.936384</td>\n",
       "      <td>1.004464</td>\n",
       "      <td>0.907924</td>\n",
       "      <td>0.999442</td>\n",
       "      <td>0.846127</td>\n",
       "      <td>535796800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-04</th>\n",
       "      <td>0.966518</td>\n",
       "      <td>0.987723</td>\n",
       "      <td>0.903460</td>\n",
       "      <td>0.915179</td>\n",
       "      <td>0.774790</td>\n",
       "      <td>512377600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-05</th>\n",
       "      <td>0.926339</td>\n",
       "      <td>0.987165</td>\n",
       "      <td>0.919643</td>\n",
       "      <td>0.928571</td>\n",
       "      <td>0.786128</td>\n",
       "      <td>778321600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-06</th>\n",
       "      <td>0.947545</td>\n",
       "      <td>0.955357</td>\n",
       "      <td>0.848214</td>\n",
       "      <td>0.848214</td>\n",
       "      <td>0.718098</td>\n",
       "      <td>767972800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-07</th>\n",
       "      <td>0.861607</td>\n",
       "      <td>0.901786</td>\n",
       "      <td>0.852679</td>\n",
       "      <td>0.888393</td>\n",
       "      <td>0.752113</td>\n",
       "      <td>460734400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Open      High       Low     Close  Adj Close     Volume\n",
       "Date                                                                    \n",
       "2000-01-03  0.936384  1.004464  0.907924  0.999442   0.846127  535796800\n",
       "2000-01-04  0.966518  0.987723  0.903460  0.915179   0.774790  512377600\n",
       "2000-01-05  0.926339  0.987165  0.919643  0.928571   0.786128  778321600\n",
       "2000-01-06  0.947545  0.955357  0.848214  0.848214   0.718098  767972800\n",
       "2000-01-07  0.861607  0.901786  0.852679  0.888393   0.752113  460734400"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price = yf.download('AAPL', start = '2000-01-01', progress = False)\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "361ed8b0",
   "metadata": {
    "id": "361ed8b0"
   },
   "source": [
    "미국이 아닌 국가의 경우 단순히 티커만 입력할 경우 데이터를 받을 수 없다. 예를 들어 일본의 '도쿄 일렉트론'은 일본 내에서 티커가 '8035'이며, 야후 파이낸스에서 이를 검색해보자.\n",
    "\n",
    "```{figure} image/data_global/same_ticker.png\n",
    "---\n",
    "name: same_ticker\n",
    "---\n",
    "중복 티커\n",
    "```\n",
    "\n",
    "우리가 원하는 도쿄 일렉트론 뿐만 아니라 홍콩에 상장된 'Janco Holdings Limited'라는 주식 역시 티커가 8035 이다. 이처럼 각기 다른 국가에서 중복된 티커가 사용되는 경우가 종종 발생되므로, 야후 파이낸스 혹은 여러 벤더의 경우 '티커.국가코드' 형태를 통해 이들을 구분한다. 야후 파이낸스에서 일본의 국가코드는 'T' 이다. 이를 이용해 도쿄 일렉트론의 주가를 받는 법은 다음과 같다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "317e6213",
   "metadata": {
    "id": "317e6213",
    "outputId": "c55184e8-2e41-446a-ecfb-c12f76ae9d01"
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</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>2000-01-04</th>\n",
       "      <td>4600.000000</td>\n",
       "      <td>4666.666504</td>\n",
       "      <td>4296.666504</td>\n",
       "      <td>4336.666504</td>\n",
       "      <td>2920.333984</td>\n",
       "      <td>693000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-05</th>\n",
       "      <td>3836.666748</td>\n",
       "      <td>4046.666748</td>\n",
       "      <td>3673.333252</td>\n",
       "      <td>3983.333252</td>\n",
       "      <td>2682.397949</td>\n",
       "      <td>2016000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-06</th>\n",
       "      <td>4000.000000</td>\n",
       "      <td>4200.000000</td>\n",
       "      <td>3666.666748</td>\n",
       "      <td>3673.333252</td>\n",
       "      <td>2473.642822</td>\n",
       "      <td>2064000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-07</th>\n",
       "      <td>3600.000000</td>\n",
       "      <td>3843.333252</td>\n",
       "      <td>3510.000000</td>\n",
       "      <td>3640.000000</td>\n",
       "      <td>2451.196289</td>\n",
       "      <td>3609000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-10</th>\n",
       "      <td>3640.000000</td>\n",
       "      <td>3640.000000</td>\n",
       "      <td>3640.000000</td>\n",
       "      <td>3640.000000</td>\n",
       "      <td>2451.196289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2000-01-04  4600.000000  4666.666504  4296.666504  4336.666504  2920.333984   \n",
       "2000-01-05  3836.666748  4046.666748  3673.333252  3983.333252  2682.397949   \n",
       "2000-01-06  4000.000000  4200.000000  3666.666748  3673.333252  2473.642822   \n",
       "2000-01-07  3600.000000  3843.333252  3510.000000  3640.000000  2451.196289   \n",
       "2000-01-10  3640.000000  3640.000000  3640.000000  3640.000000  2451.196289   \n",
       "\n",
       "             Volume  \n",
       "Date                 \n",
       "2000-01-04   693000  \n",
       "2000-01-05  2016000  \n",
       "2000-01-06  2064000  \n",
       "2000-01-07  3609000  \n",
       "2000-01-10        0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price = yf.download(\"8035.T\", progress = False)\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25b53ebf",
   "metadata": {
    "id": "25b53ebf"
   },
   "source": [
    "```{note}\n",
    "- 미국의 경우는 국가코드가 필요없이 단순히 티커만 입력하면 된다.\n",
    "- 국내 주가 역시 야후 파이낸스를 통해 다운로드 받을 수 있다. 그러나 일부 중소형주의 경우 데이터가 존재하지 않는 문제가 있어 국내 사이트를 이용해 수집하는 것을 권장한다.\n",
    "```\n",
    "\n",
    "### 전 종목 주가 다운로드\n",
    "\n",
    "미국 데이터 역시 국내 전종목 주가를 다운로드 받고 DB에 저장했던것과 동일하게 for문을 이용하면 된다. 먼저 SQL에서 주가 데이터에 해당하는 테이블(global_price)를 만든다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11da8213",
   "metadata": {
    "deletable": false,
    "editable": false,
    "id": "11da8213",
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table global_price\n",
    "(\n",
    "    Date date,\n",
    "    High double,\n",
    "    Low double,\n",
    "    Open double,\n",
    "    Close double,\n",
    "    Volume double,\n",
    "    `Adj Close` double,\n",
    "    ticker varchar(20),\n",
    "    primary key(Date, ticker)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2769d64",
   "metadata": {
    "id": "d2769d64"
   },
   "source": [
    "파이썬에서 아래 코드를 실행하면 for문을 통해 전종목 주가가 DB에 저장된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fcdd4db",
   "metadata": {
    "deletable": false,
    "editable": false,
    "id": "9fcdd4db",
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "import time\n",
    "from tqdm import tqdm\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",
    "\n",
    "mycursor = con.cursor()\n",
    "\n",
    "# 티커리스트 불러오기\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from global_ticker\n",
    "where date = (select max(date) from global_ticker)\n",
    "and country = 'United States';\n",
    "\"\"\", con=engine)\n",
    "\n",
    "# DB 저장 쿼리\n",
    "query = \"\"\"\n",
    "    insert into global_price (Date, High, Low, Open, Close, Volume, `Adj Close`, ticker)\n",
    "    values (%s, %s,%s,%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    High = new.High, Low = new.Low, Open = new.Open, Close = new.Close,\n",
    "    Volume = new.Volume, `Adj Close` = new.`Adj Close`;\n",
    "\"\"\"\n",
    "\n",
    "# 오류 발생시 저장할 리스트 생성\n",
    "error_list = []\n",
    "\n",
    "# 전종목 주가 다운로드 및 저장\n",
    "for i in tqdm(range(0, len(ticker_list))):\n",
    "\n",
    "    # 티커 선택\n",
    "    ticker = ticker_list['Symbol'][i]\n",
    "\n",
    "    # 오류 발생 시 이를 무시하고 다음 루프로 진행\n",
    "    try:\n",
    "\n",
    "        # 주가 다운로드\n",
    "        price = yf.download(ticker, progress=False)\n",
    "\n",
    "        # 데이터 클렌징\n",
    "        price = price.reset_index()\n",
    "        price['ticker'] = 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": "909ed01e",
   "metadata": {
    "id": "909ed01e"
   },
   "source": [
    "1. DB에 연결한다.\n",
    "2. 기준일이 최대, 즉 최근일 기준 보통주에 해당하며, 미국 종목의 리스트(ticker_list)만 불러온다.\n",
    "3. DB에 저장할 쿼리(query)를 입력한다.\n",
    "4. 페이지 오류, 통신 오류 등 오류가 발생한 티커명을 저장할 리스트(error_list)를 만든다.\n",
    "5. for문을 통해 전종목 주가를 다운로드 받으며, 진행상황을 알기위해 `tqdm()` 함수를 이용한다.\n",
    "6. `download()` 함수를 통해 야후 파이낸스에서 주가를 받은 후 클렌징 처리한다. 그 후 주가 데이터를 DB에 저장한다.\n",
    "7. `try except`문을 통해 오류가 발생시 'error_list'에 티커를 저장한다.\n",
    "8. 무한 크롤링을 방지하기 위해 한 번의 루프가 끝날 때마다 타임슬립을 적용한다.\n",
    "9. 모든 작업이 끝나면 DB와의 연결을 종료한다.\n",
    "\n",
    "```{figure} image/data_global/sql_price.png\n",
    "---\n",
    "name: sql_price\n",
    "---\n",
    "글로벌 주가 테이블\n",
    "```\n",
    "\n",
    "작업이 종료된 후 'error_list'에는 오류가 발생해 다운로드 받지 못한 종목들이 입력되어 있다. 이는 페이지 오류나 통신 오류 때문일 수도 있으며, 인베스팅닷컴에는 존재하지만 야후 파이낸스에는 존재하지 않는 종목일 수도 있다.\n",
    "\n",
    "```{note}\n",
    "미국이 아닌 타 국가의 경우 티커의 중복 방지를 위해 ticker 열에 국가코드도 함께 입력한 후 DB에 저장하는 것을 추천한다.\n",
    "```\n",
    "\n",
    "## 재무제표 다운로드\n",
    "\n",
    "재무제표 역시 야후 파이낸스에서 구할 수 있으며, [Financials] 탭을 클릭하면 연간 및 분기 기준 재무제표를 제공하고 있다. 해당 데이터를 다운로드 받을 수 있는 여러 패키지가 존재하며, 본 책에서는 그 중에서 yahooquery 패키지를 사용하도록 하겠다. 해당 패키지의 자세한 설명은 아래 사이트에서 확인할 수 있다. 예시로 애플(AAPL)의 종목정보 및 재무제표를 받아보도록 하겠다.\n",
    "\n",
    "```\n",
    "https://yahooquery.dpguthrie.com/\n",
    "```\n",
    "\n",
    "```{figure} image/data_global/yahoo_fs.png\n",
    "---\n",
    "name: yahoo_fs\n",
    "---\n",
    "야후에서 제공하는 재무제표 데이터\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "618d41ec",
   "metadata": {
    "id": "618d41ec",
    "outputId": "9ecfddc6-89b6-40cf-c570-89309b6ed981"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AAPL': {'address1': 'One Apple Park Way',\n",
       "  'city': 'Cupertino',\n",
       "  'state': 'CA',\n",
       "  'zip': '95014',\n",
       "  'country': 'United States',\n",
       "  'phone': '408 996 1010',\n",
       "  'website': 'https://www.apple.com',\n",
       "  'industry': 'Consumer Electronics',\n",
       "  'industryKey': 'consumer-electronics',\n",
       "  'industryDisp': 'Consumer Electronics',\n",
       "  'sector': 'Technology',\n",
       "  'sectorKey': 'technology',\n",
       "  'sectorDisp': 'Technology',\n",
       "  'longBusinessSummary': 'Apple Inc. designs, manufactures, and markets smartphones, personal computers, tablets, wearables, and accessories worldwide. The company offers iPhone, a line of smartphones; Mac, a line of personal computers; iPad, a line of multi-purpose tablets; and wearables, home, and accessories comprising AirPods, Apple TV, Apple Watch, Beats products, and HomePod. It also provides AppleCare support and cloud services; and operates various platforms, including the App Store that allow customers to discover and download applications and digital content, such as books, music, video, games, and podcasts. In addition, the company offers various services, such as Apple Arcade, a game subscription service; Apple Fitness+, a personalized fitness service; Apple Music, which offers users a curated listening experience with on-demand radio stations; Apple News+, a subscription news and magazine service; Apple TV+, which offers exclusive original content; Apple Card, a co-branded credit card; and Apple Pay, a cashless payment service, as well as licenses its intellectual property. The company serves consumers, and small and mid-sized businesses; and the education, enterprise, and government markets. It distributes third-party applications for its products through the App Store. The company also sells its products through its retail and online stores, and direct sales force; and third-party cellular network carriers, wholesalers, retailers, and resellers. Apple Inc. was founded in 1976 and is headquartered in Cupertino, California.',\n",
       "  'fullTimeEmployees': 161000,\n",
       "  'companyOfficers': [{'maxAge': 1,\n",
       "    'name': 'Mr. Timothy D. Cook',\n",
       "    'age': 62,\n",
       "    'title': 'CEO & Director',\n",
       "    'yearBorn': 1961,\n",
       "    'fiscalYear': 2023,\n",
       "    'totalPay': 16239562,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Mr. Luca  Maestri',\n",
       "    'age': 60,\n",
       "    'title': 'CFO & Senior VP',\n",
       "    'yearBorn': 1963,\n",
       "    'fiscalYear': 2023,\n",
       "    'totalPay': 4612242,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Mr. Jeffrey E. Williams',\n",
       "    'age': 59,\n",
       "    'title': 'Chief Operating Officer',\n",
       "    'yearBorn': 1964,\n",
       "    'fiscalYear': 2023,\n",
       "    'totalPay': 4637585,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Ms. Katherine L. Adams',\n",
       "    'age': 59,\n",
       "    'title': 'Senior VP, General Counsel & Secretary',\n",
       "    'yearBorn': 1964,\n",
       "    'fiscalYear': 2023,\n",
       "    'totalPay': 4618064,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': \"Ms. Deirdre  O'Brien\",\n",
       "    'age': 56,\n",
       "    'title': 'Senior Vice President of Retail',\n",
       "    'yearBorn': 1967,\n",
       "    'fiscalYear': 2023,\n",
       "    'totalPay': 4613369,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Mr. Chris  Kondo',\n",
       "    'title': 'Senior Director of Corporate Accounting',\n",
       "    'fiscalYear': 2023,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Mr. James  Wilson',\n",
       "    'title': 'Chief Technology Officer',\n",
       "    'fiscalYear': 2023,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Suhasini  Chandramouli',\n",
       "    'title': 'Director of Investor Relations',\n",
       "    'fiscalYear': 2023,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Mr. Greg  Joswiak',\n",
       "    'title': 'Senior Vice President of Worldwide Marketing',\n",
       "    'fiscalYear': 2023,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0},\n",
       "   {'maxAge': 1,\n",
       "    'name': 'Mr. Adrian  Perica',\n",
       "    'age': 49,\n",
       "    'title': 'Head of Corporate Development',\n",
       "    'yearBorn': 1974,\n",
       "    'fiscalYear': 2023,\n",
       "    'exercisedValue': 0,\n",
       "    'unexercisedValue': 0}],\n",
       "  'auditRisk': 4,\n",
       "  'boardRisk': 1,\n",
       "  'compensationRisk': 6,\n",
       "  'shareHolderRightsRisk': 1,\n",
       "  'overallRisk': 1,\n",
       "  'governanceEpochDate': '2024-02-01 09:00:00',\n",
       "  'compensationAsOfEpochDate': '2023-12-31 09:00:00',\n",
       "  'maxAge': 86400}}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from yahooquery import Ticker\n",
    "import numpy as np\n",
    "\n",
    "data = Ticker('AAPL')\n",
    "data.asset_profile"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6acb8fd5",
   "metadata": {
    "id": "6acb8fd5"
   },
   "source": [
    "yahooquery 패키지의 `Ticker()` 함수내에 티커를 입력하면, 해당 종목에 대한 각종 정보를 받을 수 있다. 먼저 `asset_profile` 부분에는 기업에 대한 정보가 나타난다. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "3f19a60a",
   "metadata": {
    "id": "3f19a60a",
    "outputId": "39249d17-5a02-4f13-cb16-f15d0bc34ea1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AAPL': {'maxAge': 1,\n",
       "  'priceHint': 2,\n",
       "  'previousClose': 188.08,\n",
       "  'open': 188.65,\n",
       "  'dayLow': 188.0,\n",
       "  'dayHigh': 189.99,\n",
       "  'regularMarketPreviousClose': 188.08,\n",
       "  'regularMarketOpen': 188.65,\n",
       "  'regularMarketDayLow': 188.0,\n",
       "  'regularMarketDayHigh': 189.99,\n",
       "  'dividendRate': 0.96,\n",
       "  'dividendYield': 0.0050999997,\n",
       "  'exDividendDate': '2024-02-09 09:00:00',\n",
       "  'payoutRatio': 0.14770001,\n",
       "  'fiveYearAvgDividendYield': 0.78,\n",
       "  'beta': 1.312,\n",
       "  'trailingPE': 29.324535,\n",
       "  'forwardPE': 26.3757,\n",
       "  'volume': 45155216,\n",
       "  'regularMarketVolume': 45155216,\n",
       "  'averageVolume': 53311511,\n",
       "  'averageVolume10days': 57853670,\n",
       "  'averageDailyVolume10Day': 57853670,\n",
       "  'bid': 0.0,\n",
       "  'ask': 0.0,\n",
       "  'bidSize': 3000,\n",
       "  'askSize': 1100,\n",
       "  'marketCap': 2916202840064,\n",
       "  'fiftyTwoWeekLow': 143.9,\n",
       "  'fiftyTwoWeekHigh': 199.62,\n",
       "  'priceToSalesTrailing12Months': 7.5606885,\n",
       "  'fiftyDayAverage': 190.4794,\n",
       "  'twoHundredDayAverage': 183.0677,\n",
       "  'trailingAnnualDividendRate': 0.95,\n",
       "  'trailingAnnualDividendYield': 0.005051042,\n",
       "  'currency': 'USD',\n",
       "  'fromCurrency': None,\n",
       "  'toCurrency': None,\n",
       "  'lastMarket': None,\n",
       "  'coinMarketCapLink': None,\n",
       "  'algorithm': None,\n",
       "  'tradeable': False}}"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.summary_detail"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17e6fb25",
   "metadata": {
    "id": "17e6fb25"
   },
   "source": [
    "`summary_detail` 부분에는 가격, 배당, 밸류에이션 등 주요 지표가 나타난다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "47f4bc4f",
   "metadata": {
    "id": "47f4bc4f",
    "outputId": "9c267b28-bac1-4901-c942-dedba90b90b1"
   },
   "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>adjclose</th>\n",
       "      <th>dividends</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</th>\n",
       "      <th>date</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 rowspan=\"5\" valign=\"top\">AAPL</th>\n",
       "      <th>2024-01-02</th>\n",
       "      <td>187.149994</td>\n",
       "      <td>188.440002</td>\n",
       "      <td>183.889999</td>\n",
       "      <td>185.639999</td>\n",
       "      <td>82488700</td>\n",
       "      <td>185.403412</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-03</th>\n",
       "      <td>184.220001</td>\n",
       "      <td>185.880005</td>\n",
       "      <td>183.429993</td>\n",
       "      <td>184.250000</td>\n",
       "      <td>58414500</td>\n",
       "      <td>184.015198</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-04</th>\n",
       "      <td>182.149994</td>\n",
       "      <td>183.089996</td>\n",
       "      <td>180.880005</td>\n",
       "      <td>181.910004</td>\n",
       "      <td>71983600</td>\n",
       "      <td>181.678177</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-05</th>\n",
       "      <td>181.990005</td>\n",
       "      <td>182.759995</td>\n",
       "      <td>180.169998</td>\n",
       "      <td>181.179993</td>\n",
       "      <td>62303300</td>\n",
       "      <td>180.949097</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-08</th>\n",
       "      <td>182.089996</td>\n",
       "      <td>185.600006</td>\n",
       "      <td>181.500000</td>\n",
       "      <td>185.559998</td>\n",
       "      <td>59144500</td>\n",
       "      <td>185.323517</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         open        high         low       close    volume  \\\n",
       "symbol date                                                                   \n",
       "AAPL   2024-01-02  187.149994  188.440002  183.889999  185.639999  82488700   \n",
       "       2024-01-03  184.220001  185.880005  183.429993  184.250000  58414500   \n",
       "       2024-01-04  182.149994  183.089996  180.880005  181.910004  71983600   \n",
       "       2024-01-05  181.990005  182.759995  180.169998  181.179993  62303300   \n",
       "       2024-01-08  182.089996  185.600006  181.500000  185.559998  59144500   \n",
       "\n",
       "                     adjclose  dividends  \n",
       "symbol date                               \n",
       "AAPL   2024-01-02  185.403412        0.0  \n",
       "       2024-01-03  184.015198        0.0  \n",
       "       2024-01-04  181.678177        0.0  \n",
       "       2024-01-05  180.949097        0.0  \n",
       "       2024-01-08  185.323517        0.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.history().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc8bdc73",
   "metadata": {
    "id": "dc8bdc73"
   },
   "source": [
    "`history()` 부분을 통해 주가를 받을 수도 있다.\n",
    "\n",
    "재무제표 데이터의 경우 `data.balance_sheet()`, `data.cash_flow()`, `data.income_statement()` 을 통해 재무상태표, 현금흐름표, 손익계산서를 각각 받을 수도 있지만, `data.all_financial_data()`을 통해 세 개의 테이블을 한 번에 받을 수도 있다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ea4aa439",
   "metadata": {
    "id": "ea4aa439",
    "outputId": "6c6519e6-d162-4089-e441-5c6c0739b97b"
   },
   "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>asOfDate</th>\n",
       "      <th>periodType</th>\n",
       "      <th>currencyCode</th>\n",
       "      <th>AccountsPayable</th>\n",
       "      <th>AccountsReceivable</th>\n",
       "      <th>AccumulatedDepreciation</th>\n",
       "      <th>AvailableForSaleSecurities</th>\n",
       "      <th>BasicAverageShares</th>\n",
       "      <th>BasicEPS</th>\n",
       "      <th>BeginningCashPosition</th>\n",
       "      <th>...</th>\n",
       "      <th>TotalEquityGrossMinorityInterest</th>\n",
       "      <th>TotalExpenses</th>\n",
       "      <th>TotalLiabilitiesNetMinorityInterest</th>\n",
       "      <th>TotalNonCurrentAssets</th>\n",
       "      <th>TotalNonCurrentLiabilitiesNetMinorityInterest</th>\n",
       "      <th>TotalOperatingIncomeAsReported</th>\n",
       "      <th>TotalRevenue</th>\n",
       "      <th>TradeandOtherPayablesNonCurrent</th>\n",
       "      <th>TreasurySharesNumber</th>\n",
       "      <th>WorkingCapital</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</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></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></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>12M</td>\n",
       "      <td>USD</td>\n",
       "      <td>4.229600e+10</td>\n",
       "      <td>1.612000e+10</td>\n",
       "      <td>-6.676000e+10</td>\n",
       "      <td>1.008870e+11</td>\n",
       "      <td>1.735212e+10</td>\n",
       "      <td>3.31</td>\n",
       "      <td>5.022400e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>6.533900e+10</td>\n",
       "      <td>2.082270e+11</td>\n",
       "      <td>2.585490e+11</td>\n",
       "      <td>1.801750e+11</td>\n",
       "      <td>1.531570e+11</td>\n",
       "      <td>6.628800e+10</td>\n",
       "      <td>2.745150e+11</td>\n",
       "      <td>2.817000e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.832100e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>12M</td>\n",
       "      <td>USD</td>\n",
       "      <td>5.476300e+10</td>\n",
       "      <td>2.627800e+10</td>\n",
       "      <td>-7.028300e+10</td>\n",
       "      <td>1.278770e+11</td>\n",
       "      <td>1.670127e+10</td>\n",
       "      <td>5.67</td>\n",
       "      <td>3.978900e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>6.309000e+10</td>\n",
       "      <td>2.568680e+11</td>\n",
       "      <td>2.879120e+11</td>\n",
       "      <td>2.161660e+11</td>\n",
       "      <td>1.624310e+11</td>\n",
       "      <td>1.089490e+11</td>\n",
       "      <td>3.658170e+11</td>\n",
       "      <td>2.468900e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.355000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>2022-09-30</td>\n",
       "      <td>12M</td>\n",
       "      <td>USD</td>\n",
       "      <td>6.411500e+10</td>\n",
       "      <td>2.818400e+10</td>\n",
       "      <td>-7.234000e+10</td>\n",
       "      <td>1.208050e+11</td>\n",
       "      <td>1.621596e+10</td>\n",
       "      <td>6.15</td>\n",
       "      <td>3.592900e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>5.067200e+10</td>\n",
       "      <td>2.748910e+11</td>\n",
       "      <td>3.020830e+11</td>\n",
       "      <td>2.173500e+11</td>\n",
       "      <td>1.481010e+11</td>\n",
       "      <td>1.194370e+11</td>\n",
       "      <td>3.943280e+11</td>\n",
       "      <td>1.665700e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.857700e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>12M</td>\n",
       "      <td>USD</td>\n",
       "      <td>6.261100e+10</td>\n",
       "      <td>2.950800e+10</td>\n",
       "      <td>-7.088400e+10</td>\n",
       "      <td>1.005440e+11</td>\n",
       "      <td>1.574423e+10</td>\n",
       "      <td>6.16</td>\n",
       "      <td>2.497700e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>6.214600e+10</td>\n",
       "      <td>2.689840e+11</td>\n",
       "      <td>2.904370e+11</td>\n",
       "      <td>2.090170e+11</td>\n",
       "      <td>1.451290e+11</td>\n",
       "      <td>1.143010e+11</td>\n",
       "      <td>3.832850e+11</td>\n",
       "      <td>1.545700e+10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.742000e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 158 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         asOfDate periodType currencyCode  AccountsPayable  \\\n",
       "symbol                                                       \n",
       "AAPL   2020-09-30        12M          USD     4.229600e+10   \n",
       "AAPL   2021-09-30        12M          USD     5.476300e+10   \n",
       "AAPL   2022-09-30        12M          USD     6.411500e+10   \n",
       "AAPL   2023-09-30        12M          USD     6.261100e+10   \n",
       "\n",
       "        AccountsReceivable  AccumulatedDepreciation  \\\n",
       "symbol                                                \n",
       "AAPL          1.612000e+10            -6.676000e+10   \n",
       "AAPL          2.627800e+10            -7.028300e+10   \n",
       "AAPL          2.818400e+10            -7.234000e+10   \n",
       "AAPL          2.950800e+10            -7.088400e+10   \n",
       "\n",
       "        AvailableForSaleSecurities  BasicAverageShares  BasicEPS  \\\n",
       "symbol                                                             \n",
       "AAPL                  1.008870e+11        1.735212e+10      3.31   \n",
       "AAPL                  1.278770e+11        1.670127e+10      5.67   \n",
       "AAPL                  1.208050e+11        1.621596e+10      6.15   \n",
       "AAPL                  1.005440e+11        1.574423e+10      6.16   \n",
       "\n",
       "        BeginningCashPosition  ...  TotalEquityGrossMinorityInterest  \\\n",
       "symbol                         ...                                     \n",
       "AAPL             5.022400e+10  ...                      6.533900e+10   \n",
       "AAPL             3.978900e+10  ...                      6.309000e+10   \n",
       "AAPL             3.592900e+10  ...                      5.067200e+10   \n",
       "AAPL             2.497700e+10  ...                      6.214600e+10   \n",
       "\n",
       "        TotalExpenses  TotalLiabilitiesNetMinorityInterest  \\\n",
       "symbol                                                       \n",
       "AAPL     2.082270e+11                         2.585490e+11   \n",
       "AAPL     2.568680e+11                         2.879120e+11   \n",
       "AAPL     2.748910e+11                         3.020830e+11   \n",
       "AAPL     2.689840e+11                         2.904370e+11   \n",
       "\n",
       "        TotalNonCurrentAssets  TotalNonCurrentLiabilitiesNetMinorityInterest  \\\n",
       "symbol                                                                         \n",
       "AAPL             1.801750e+11                                   1.531570e+11   \n",
       "AAPL             2.161660e+11                                   1.624310e+11   \n",
       "AAPL             2.173500e+11                                   1.481010e+11   \n",
       "AAPL             2.090170e+11                                   1.451290e+11   \n",
       "\n",
       "        TotalOperatingIncomeAsReported  TotalRevenue  \\\n",
       "symbol                                                 \n",
       "AAPL                      6.628800e+10  2.745150e+11   \n",
       "AAPL                      1.089490e+11  3.658170e+11   \n",
       "AAPL                      1.194370e+11  3.943280e+11   \n",
       "AAPL                      1.143010e+11  3.832850e+11   \n",
       "\n",
       "        TradeandOtherPayablesNonCurrent  TreasurySharesNumber  WorkingCapital  \n",
       "symbol                                                                         \n",
       "AAPL                       2.817000e+10                   NaN    3.832100e+10  \n",
       "AAPL                       2.468900e+10                   NaN    9.355000e+09  \n",
       "AAPL                       1.665700e+10                   NaN   -1.857700e+10  \n",
       "AAPL                       1.545700e+10                   0.0   -1.742000e+09  \n",
       "\n",
       "[4 rows x 158 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y = data.all_financial_data(frequency = 'a')\n",
    "data_y"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c7e5318",
   "metadata": {
    "id": "8c7e5318"
   },
   "source": [
    "`all_financial_data()`를 입력하면 세 개의 재무제표를 한 번에 받을 수 있으며, 인자에 `frequency = 'a'`를 입력하면 연간(annual) 재무제표를 받는다. 이는 가로로 긴 형태이므로 클렌징처리를 해주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e084aa15",
   "metadata": {
    "id": "e084aa15",
    "outputId": "e8b3d536-7cca-4296-9e2c-700ee7995f56"
   },
   "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>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>account</th>\n",
       "      <th>value</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>42296000000.0</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>54763000000.0</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2022-09-30</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>64115000000.0</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>62611000000.0</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-09-30</td>\n",
       "      <td>AccountsReceivable</td>\n",
       "      <td>16120000000.0</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ticker       date             account          value freq\n",
       "0   AAPL 2020-09-30     AccountsPayable  42296000000.0    y\n",
       "1   AAPL 2021-09-30     AccountsPayable  54763000000.0    y\n",
       "2   AAPL 2022-09-30     AccountsPayable  64115000000.0    y\n",
       "3   AAPL 2023-09-30     AccountsPayable  62611000000.0    y\n",
       "4   AAPL 2020-09-30  AccountsReceivable  16120000000.0    y"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y.reset_index(inplace = True)\n",
    "data_y = data_y.loc[:, ~data_y.columns.isin(['periodType', 'currencyCode'])]\n",
    "data_y = data_y.melt(id_vars = ['symbol', 'asOfDate'])\n",
    "data_y = data_y.replace([np.nan], None)\n",
    "data_y['freq'] = 'y'\n",
    "data_y.columns = ['ticker', 'date', 'account', 'value', 'freq']\n",
    "\n",
    "data_y.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8d22380",
   "metadata": {
    "id": "f8d22380"
   },
   "source": [
    "1. 인덱스를 초기화한다.\n",
    "2. 불필요한 열을 제외한다.\n",
    "3. `melt()` 함수를 통해 세로로 긴 형태로 만든다.\n",
    "4. nan을 None으로 변경한다.\n",
    "5. freq 열에 연간에 해당하는 'y'를 입력한다.\n",
    "6. 열 이름을 변경한다.\n",
    "\n",
    "이처럼 해외 종목의 재무제표 데이터도 매우 쉽게 다운로드 받을 수 있다. 분기별 재무제표를 받는법도 위와 같으며, 인자만 `frequency = 'q'`로 변경하면 된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "c1827203",
   "metadata": {
    "id": "c1827203",
    "outputId": "0349443f-9285-4b47-8f8b-eefaf920c176"
   },
   "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>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>account</th>\n",
       "      <th>value</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>57918000000.0</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2023-03-31</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>42945000000.0</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>46699000000.0</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>62611000000.0</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>AccountsPayable</td>\n",
       "      <td>58146000000.0</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ticker       date          account          value freq\n",
       "0   AAPL 2022-12-31  AccountsPayable  57918000000.0    q\n",
       "1   AAPL 2023-03-31  AccountsPayable  42945000000.0    q\n",
       "2   AAPL 2023-06-30  AccountsPayable  46699000000.0    q\n",
       "3   AAPL 2023-09-30  AccountsPayable  62611000000.0    q\n",
       "4   AAPL 2023-12-31  AccountsPayable  58146000000.0    q"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_q = data.all_financial_data(frequency = 'q')\n",
    "data_q.reset_index(inplace = True)\n",
    "data_q = data_q.loc[:, ~data_q.columns.isin(['periodType', 'currencyCode'])]\n",
    "data_q = data_q.melt(id_vars = ['symbol', 'asOfDate'])\n",
    "data_q = data_q.replace([np.nan], None)\n",
    "data_q['freq'] = 'q'\n",
    "data_q.columns = ['ticker', 'date', 'account', 'value', 'freq']\n",
    "\n",
    "data_q.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0910f1e4",
   "metadata": {
    "id": "0910f1e4"
   },
   "source": [
    "### 전 종목 재무제표 다운로드\n",
    "\n",
    "for문을 이용하여 전 종목 재무제표를 다운로드 받도록 하겠다. 먼저 SQL에서 재무제표 데이터에 해당하는 테이블(global_fs)를 만든다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40d72f57",
   "metadata": {
    "deletable": false,
    "editable": false,
    "id": "40d72f57",
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table global_fs\n",
    "(\n",
    "    ticker varchar(20),        \n",
    "    date date,\n",
    "    account varchar(100),\n",
    "    value double,\n",
    "    freq varchar(1),\n",
    "    \n",
    "    primary key(ticker, date, account, freq)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad4def91",
   "metadata": {
    "id": "ad4def91"
   },
   "source": [
    "이제 파이썬에서 아래 코드를 실행하면 for문을 통해 전 종목 재무제표가 DB에 저장된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b1608c2",
   "metadata": {
    "deletable": false,
    "editable": false,
    "id": "1b1608c2",
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "from sqlalchemy import create_engine\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "from yahooquery import Ticker\n",
    "import time\n",
    "from tqdm import tqdm\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",
    "\n",
    "mycursor = con.cursor()\n",
    "\n",
    "# 티커리스트 불러오기\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from global_ticker\n",
    "where date = (select max(date) from global_ticker)\n",
    "and country = 'United States';\n",
    "\"\"\", con=engine)\n",
    "\n",
    "# DB 저장 쿼리\n",
    "query_fs = \"\"\"\n",
    "    insert into global_fs (ticker, date, account, value, freq)\n",
    "    values (%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    value = new.value;\n",
    "\"\"\"\n",
    "\n",
    "# 오류 발생시 저장할 리스트 생성\n",
    "error_list = []\n",
    "\n",
    "# 전종목 재무제표 다운로드 및 저장\n",
    "for i in tqdm(range(0, len(ticker_list))):\n",
    "\n",
    "    # 티커 선택\n",
    "    ticker = ticker_list['Symbol'][i]\n",
    "\n",
    "    # 오류 발생 시 이를 무시하고 다음 루프로 진행\n",
    "    try:\n",
    "\n",
    "        #  정보 다운로드\n",
    "        data = Ticker(ticker)\n",
    "        \n",
    "        # 연간 재무제표\n",
    "        data_y = data.all_financial_data(frequency = 'a')\n",
    "        data_y.reset_index(inplace = True)\n",
    "        data_y = data_y.loc[:, ~data_y.columns.isin(['periodType', 'currencyCode'])]\n",
    "        data_y = data_y.melt(id_vars = ['symbol', 'asOfDate'])\n",
    "        data_y = data_y.replace([np.nan], None)\n",
    "        data_y['freq'] = 'y'\n",
    "        data_y.columns = ['ticker', 'date', 'account', 'value', 'freq']\n",
    "        \n",
    "        \n",
    "        # 분기 재무제표\n",
    "        data_q = data.all_financial_data(frequency = 'q')\n",
    "        data_q.reset_index(inplace = True)\n",
    "        data_q = data_q.loc[:, ~data_q.columns.isin(['periodType', 'currencyCode'])]\n",
    "        data_q = data_q.melt(id_vars = ['symbol', 'asOfDate'])\n",
    "        data_q = data_q.replace([np.nan], None)\n",
    "        data_q['freq'] = 'q'\n",
    "        data_q.columns = ['ticker', 'date', 'account', 'value', 'freq']\n",
    "        \n",
    "        # 데이터 합치기\n",
    "        data_fs = pd.concat([data_y, data_q], axis=0)\n",
    "\n",
    "        # 재무제표 데이터를 DB에 저장\n",
    "        args = data_fs.values.tolist()\n",
    "        mycursor.executemany(query_fs, 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": "1bd53666",
   "metadata": {
    "id": "1bd53666"
   },
   "source": [
    "1. DB에 연결한다.\n",
    "2. 기준일이 최대, 즉 최근일 기준 보통주에 해당하며, 미국 종목의 리스트(ticker_list)만 불러온다.\n",
    "3. DB에 저장할 쿼리(query)를 입력한다.\n",
    "4. 페이지 오류, 통신 오류 등 오류가 발생한 티커명을 저장할 리스트(error_list)를 만든다.\n",
    "5. for문을 통해 전종목 재무제표를 다운로드 받으며, 진행상황을 알기위해 tqdm() 함수를 이용한다.\n",
    "6. `Ticker()` 함수를 이용해 종목 데이터를 받은 후 연간 및 분기 재무제표를 구한다. \n",
    "7. 이후, 두 테이블을 concat() 함수를 통해 행으로 묶어준다.\n",
    "8. 재무제표 데이터를 DB에 저장한다.\n",
    "9. 무한 크롤링을 방지하기 위해 한 번의 루프가 끝날 때마다 타임슬립을 적용한다.\n",
    "10. 모든 작업이 끝나면 DB와의 연결을 종료한다.\n",
    "\n",
    "```{figure} image/data_global/sql_fs.png\n",
    "---\n",
    "name: sql_fs\n",
    "---\n",
    "글로벌 재무제표 테이블\n",
    "```\n",
    "\n",
    "```{note}\n",
    "미국 종목들의 가치지표는 국내 재무제표 데이터를 이용해 가치지표를 계산했던 것과 동일한 방법으로 계산할 수 있으므로, 이는 생략하도록 한다.\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.11.5"
  },
  "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()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}