data_us.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "784640c9",
   "metadata": {},
   "source": [
    "# 미국 주식 데이터 수집하기\n",
    "\n",
    "퀀트 투자의 장점은 데이터만 있다면 동일한 투자 전략을 전세계 모든 국가에 적용할 수 있다는 점이다. 이번 장에서는 미국 전 종목의 티커 수집 및 주가, 재무제표, 가치지표를 다운로드 해보도록 하겠다.\n",
    "\n",
    "## 티커 수집하기\n",
    "\n",
    "우리나라는 거래소가 '한국거래소' 한 곳 뿐이지만, 미국은 매우 많은 거래소가 존재한다. 따라서 동일한 종목이 여러 거래소에서 거래되는 일도 발생한다. 그 중에서도 가장 규모가 큰 거래소는 뉴욕거래소(NYSE), 나스닥(NASDAQ), 아멕스(AMEX)이며, 해당 거래소에 상장된 종목들은 나스닥 홈페이지에서 제공된다.\n",
    "\n",
    "```\n",
    "https://www.nasdaq.com/market-activity/stocks/screener\n",
    "```\n",
    "\n",
    "위 사이트에 접속하여 개발자 도구 화면을 연 후 하단의 [Download CSV] 버튼을 누르면, 해당 데이터를 받아오는 웹주소가 표시된다.\n",
    "\n",
    "```{figure} image/data_us/us_ticker.png\n",
    "---\n",
    "name: us_ticker\n",
    "---\n",
    "미국의 상장종목 리스트\n",
    "```\n",
    "\n",
    "```\n",
    "https://api.nasdaq.com/api/screener/stocks?tableonly=true&limit=25&offset=0&download=true\n",
    "```\n",
    "\n",
    "해당 주소에 접속하면, 각 종목에 대한 정보가 JSON 형태로 제공된다. 이를 크롤링 해보도록 하겠다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdfad27b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests as rq\n",
    "\n",
    "url = 'https://api.nasdaq.com/api/screener/stocks?tableonly=true&limit=25&offset=0&download=true'\n",
    "headers = {\n",
    "    \"User-Agent\":\n",
    "    \"Mozilla/5.0 (X11; CrOS x86_64 12871.102.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.141 Safari/537.36\"\n",
    "}\n",
    "\n",
    "data = rq.get(url, headers=headers)\n",
    "data_json = data.json()['data']\n",
    "us_ticker = pd.DataFrame(data_json['rows'])\n",
    "\n",
    "us_ticker.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5122eda5",
   "metadata": {},
   "source": [
    "1. 먼저 url과 헤더정보를 입력하며, 헤더의 User-Agent에는 웹브라우저 구별을 입력해준다. 해당 사이트는 크롤러와 같이 정체가 불분명한 웹브라우저를 통한 접속이 막혀 있어, 마치 모질라 혹은 크롬을 통해 접속한 것 처럼 데이터를 요청한다. 다양한 웹브라우저 리스트는 다음 사이트에서 확인할 수 있다.\n",
    "http://www.useragentstring.com/pages/useragentstring.php\n",
    "2. `get()` 함수를 통해 페이지의 데이터를 불러온다.\n",
    "3. `json()` 함수를 통해 json 형태로 불러온 후, data 부분을 선택한다.\n",
    "4. json 형태를 데이터프레임 형태로 변경한다.\n",
    "\n",
    "## 티커 클렌징\n",
    "\n",
    "먼저 위에서 받은 데이터에서 종목 수를 확인해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3804627c",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(us_ticker)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09a98834",
   "metadata": {},
   "source": [
    "종목수를 살펴보면 상당히 많은 종목이 존재한다. 그러나 미국의 경우 동일한 종목이 여러 거래소에 상장된 경우가 있으므로, 이를 먼저 제거해주도록 한다. symbol과 name열 중 중복되는 종목이 있으면 첫번쨰 데이터만을 남기도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea13f2fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_ticker = us_ticker.loc[~us_ticker.symbol.duplicated(keep='first'), :]\n",
    "us_ticker = us_ticker.loc[~us_ticker.name.duplicated(keep='first'), :]\n",
    "\n",
    "len(us_ticker)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "830e8ec3",
   "metadata": {},
   "source": [
    "다음으로 위에서 받은 데이터 중 모든 종목이 투자에 적합한 종목이 아니므로 클렌징 처리를 해주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3466870",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_ticker['marketCap'] = pd.to_numeric(us_ticker['marketCap'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "828afdf8",
   "metadata": {},
   "source": [
    " 먼저 시가총액에 해당하는 열을 숫자 형태로 바꿔준다. 이제 어떠한 종목이 일반적인 종목이 아닌지 탐색해보도록 하자."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94b27d7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "display(us_ticker.loc[us_ticker['marketCap'] == 0].head(),\n",
    "        us_ticker[np.isnan(us_ticker['marketCap'])].head(),\n",
    "        us_ticker.loc[us_ticker['name'].str.contains('%')].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ba2d972",
   "metadata": {},
   "source": [
    "첫째, 시가총액이 0인 종목, 시가총액이 NaN인 종목, 종목 이름에 '%'가 들어간 종목을 살펴보면 Acquisition 혹은 Warrant 등이거나 전환사채, 우선주 등 보통주가 아니다. 따라서 제외하는 것이 좋다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cf2a1d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_ticker.loc[us_ticker['name'].str.contains('ETF | Fund')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "222ac212",
   "metadata": {},
   "source": [
    "둘째, 종목명에 'ETF' 혹은 'Fund'가 들어간 종목은 펀드에 해당하므로 제외한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1644089c",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_ticker.loc[us_ticker['name'].str.contains(\n",
    "    'Depositary | each representing')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7594e58a",
   "metadata": {},
   "source": [
    "마지막으로 종목명에 'Depositary'나 'each representing'가 들어간 종목은 ADR 이므로 제외한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f5a2dfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "us_ticker = us_ticker.loc[~(us_ticker['marketCap'] == 0)]\n",
    "us_ticker = us_ticker.loc[~(np.isnan(us_ticker['marketCap']))]\n",
    "us_ticker = us_ticker.loc[~(us_ticker['name'].str.contains('%'))]\n",
    "us_ticker = us_ticker.loc[~us_ticker['name'].str.contains('ETF | Fund')]\n",
    "us_ticker = us_ticker.loc[~us_ticker['name'].str.contains('Depositary')]\n",
    "us_ticker = us_ticker.loc[~us_ticker['name'].str.contains('each representing')]\n",
    "\n",
    "len(us_ticker)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e81bedf3",
   "metadata": {},
   "source": [
    "이전에 비해 종목수가 현격하게 줄어들었다. 물론 해당 과정을 거쳐도 다중 클래스가 상장되어 있는 등 클렌징 처리를 할 종목이 많지만 그 수가 매우 적으므로, 효율성 측면에서 위의 과정 정도만으로도 충분하다. 이 중 필요한 열 선택 및 기준일(오늘 날짜)을 입력해준다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7adb90d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, timedelta\n",
    "\n",
    "date.today()\n",
    "\n",
    "us_ticker = us_ticker[[\n",
    "    'symbol', 'name', 'marketCap', 'country', 'ipoyear', 'industry', 'sector'\n",
    "]]\n",
    "us_ticker['date'] = date.today().strftime(\"%Y%m%d\")\n",
    "us_ticker['date'] = pd.to_datetime(us_ticker['date'])\n",
    "us_ticker = us_ticker.replace({np.nan: None})\n",
    "\n",
    "us_ticker.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a048d465",
   "metadata": {},
   "source": [
    "이제 위의 데이터를 DB에 저장한다. 먼저 SQL에서 다음의 쿼리를 통해 테이블(us_ticker)을 만든다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a491d97",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table us_ticker\n",
    "(\n",
    "    symbol varchar(20) not null,\n",
    "    name varchar(300),\n",
    "    marketCap float,\n",
    "    country varchar(20),\n",
    "    ipoyear varchar(4),\n",
    "    industry varchar(100),\n",
    "    sector varchar(30),\n",
    "    date date,\n",
    "    primary key(symbol, date)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcba4866",
   "metadata": {},
   "source": [
    "마지막으로 티커 데이터를 해당 테이블에 저장한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64c21044",
   "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 = \"\"\"\n",
    "    insert into us_ticker (symbol, name, marketCap, country, ipoyear, industry, sector, date)\n",
    "    values (%s,%s,%s,%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    name=new.name,marketCap=new.marketCap,country=new.country,\n",
    "    ipoyear=new.ipoyear,industry=new.industry,sector=new.sector;    \n",
    "\"\"\"\n",
    "\n",
    "args = us_ticker.values.tolist()\n",
    "\n",
    "mycursor.executemany(query, args)\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cceff5e6",
   "metadata": {},
   "source": [
    "## 주가 다운로드\n",
    "\n",
    "미국 뿐만 아니라 전 세계 주가(한국 포함)의 경우 야후 파이낸스에서 제공하고 있다.\n",
    "\n",
    "```\n",
    "https://finance.yahoo.com/\n",
    "```\n",
    "\n",
    "사이트에서 종목 티커를 검색한 후 [Historical Data] 탭을 선택하면 확인 및 다운로드가 가능하다. 또한 pandas_datareader 패키지의 `DataReader()` 함수를 사용하면 야후 API를 통해 주가 데이터를 매우 손쉽게 다운로드 받을 수도 있다. 예시로써 애플(AAPL)의 주가를 다운로드 받아보도록 하자.\n",
    "\n",
    "```{figure} image/data_us/yahoo_price.png\n",
    "---\n",
    "name: yahoo_price\n",
    "---\n",
    "야후에서 제공하는 주가 데이터\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e494a42",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas_datareader as web\n",
    "\n",
    "price = web.DataReader('AAPL', 'yahoo')\n",
    "\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c45e21be",
   "metadata": {},
   "source": [
    "`DataReader()` 함수 내에 티커와 출처에 해당하는 'yahoo'를 입력하면 주가 정보를 매우 손쉽게 받을 수 있다.\n",
    "\n",
    "```{note}\n",
    "국내 주가 역시 야후 파이낸스를 통해 다운로드 받을 수 있다. 그러나 일부 중소형주의 경우 데이터가 존재하지 않는 문제가 있어 국내 사이트를 이용해 수집하는 것을 권장한다.\n",
    "```\n",
    "\n",
    "### 전 종목 주가 다운로드\n",
    "\n",
    "미국 데이터 역시 국내 전종목 주가를 다운로드 받고 DB에 저장했던것과 동일하게 `for loop` 구문을 이용하면 된다. 먼저 SQL에서 미국 주가 데이터에 해당하는 테이블(us_price)를 만든다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9199c85",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table us_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": "a9cf5d8f",
   "metadata": {},
   "source": [
    "파이썬에서 아래 코드를 실행하면 for loop 구문을 통해 전종목 주가가 DB에 저장된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9e3bc16",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "import pandas_datareader as web\n",
    "from datetime import date\n",
    "import time\n",
    "from tqdm import tqdm\n",
    "\n",
    "# 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 us_ticker\n",
    "where date = (select max(date) from us_ticker);\n",
    "\"\"\",\n",
    "                          con=con)\n",
    "\n",
    "# DB 저장 쿼리\n",
    "query = \"\"\"\n",
    "    insert into us_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, Open = new.Open, Close = new.Close,\n",
    "    Volume = new.Volume, Adj_Close = new.Adj_Close;\n",
    "\"\"\"\n",
    "\n",
    "# 오류 발생시 저장할 데이터프레임 생성\n",
    "error_list = []\n",
    "price_empty = pd.DataFrame({\n",
    "    'Date': date.today().strftime(\"%Y-%m-%d\"),\n",
    "    'High': [None],\n",
    "    'Low': [None],\n",
    "    'Open': [None],\n",
    "    'Close': [None],\n",
    "    'Volumne': [None],\n",
    "    'Adj_Close': [None]\n",
    "})\n",
    "\n",
    "# 전종목 주가 다운로드 및 저장\n",
    "for i in tqdm(range(0, len(ticker_list))):\n",
    "\n",
    "    # 빈 데이터프레임 복사\n",
    "    price = price_empty.copy()\n",
    "\n",
    "    # 티커 선택\n",
    "    ticker = ticker_list['symbol'][i]\n",
    "\n",
    "    # 오류 발생 시 이를 무시하고 다음 루프로 진행\n",
    "    try:\n",
    "\n",
    "        # url 생성\n",
    "        price = web.DataReader(ticker, 'yahoo')\n",
    "\n",
    "        # 데이터 클렌징\n",
    "        price = price.reset_index()\n",
    "        price['ticker'] = ticker\n",
    "        price.rename(columns={'Adj Close': 'Adj_Close'}, inplace=True)\n",
    "\n",
    "    except:\n",
    "\n",
    "        # 오류 발생시 빈 데이터프레임을 불러온 후 다음 루프로 이동\n",
    "        price['ticker'] = ticker\n",
    "        error_list.append(ticker)\n",
    "\n",
    "    # 주가 데이터를 DB에 저장\n",
    "    args = price.values.tolist()\n",
    "\n",
    "    mycursor.executemany(query, args)\n",
    "    con.commit()\n",
    "\n",
    "    # 타임슬립 적용\n",
    "    time.sleep(2)\n",
    "\n",
    "# DB 연결 종료\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcfb3c7f",
   "metadata": {},
   "source": [
    "1. DB에 연결한다.\n",
    "2. 기준일이 최대, 즉 최근일 기준 보통주에 해당하는 종목 리스트(ticker_list)만 불러온다.\n",
    "3. DB에 저장할 쿼리(query)를 입력한다.\n",
    "4. 페이지 오류, 통신 오류 등 오류가 발생할 경우 저장할 빈 데이터프레임(price_empty)을 생성한다.\n",
    "5. `for loop` 구문을 통해 전종목 주가를 다운로드 받으며, 진행상황을 알기위해 `tqdm()` 함수를 이용한다.\n",
    "6. `DataReader()` 함수를 통해 야후 파이낸스에서 주가를 받은 후 클렌징 처리한다.\n",
    "7.  `try except` 문을 통해 오류가 발생시 위에서 생성한 빈 데이터프레임을 이용한다.\n",
    "8. 주가 데이터를 DB에 저장한다.\n",
    "9. 무한 크롤링을 방지하기 위해 한 번의 루프가 끝날 때마다 타임슬립을 적용한다.\n",
    "10. 모든 작업이 끝나면 DB와의 연결을 종료한다.\n",
    "\n",
    "```{figure} image/data_us/sql_price.png\n",
    "---\n",
    "name: sql_price\n",
    "---\n",
    "미국 주가 테이블\n",
    "```\n",
    "\n",
    "## 재무제표 크롤링\n",
    "\n",
    "재무제표 역시 야후 파이낸스에서 구할 수 있다. [Financials] 탭을 클릭하면 연간 및 분기 기준 재무제표를 제공하고 있다. 해당 데이터를 다운로드 받을 수 있는 여러 패키지가 존재하며, 본 책에서는 그 중에서도 `yahoo_fin` 패키지를 사용하도록 하겠다. 해당 패키지의 자세한 설명은 아래 사이트에서 확인할 수 있다.\n",
    "\n",
    "```\n",
    "http://theautomatic.net/yahoo_fin-documentation/\n",
    "```\n",
    "\n",
    "```{figure} image/data_us/yahoo_fs.png\n",
    "---\n",
    "name: yahoo_fs\n",
    "---\n",
    "야후에서 제공하는 재무제표 데이터\n",
    "```\n",
    "\n",
    "해당 패키지의 `get_financials()` 함수를 이용하면 손익계산서, 재무상태표, 현금흐름표를 한번에 다운로드 받을 수 있으며, 예시로 애플(AAPL) 종목의 연간 재무제표를 받아보도록 하겠다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c821d479",
   "metadata": {},
   "outputs": [],
   "source": [
    "import yahoo_fin.stock_info as si\n",
    "\n",
    "data_y = si.get_financials('AAPL', yearly=True, quarterly=False)\n",
    "data_y.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb1d1bfb",
   "metadata": {},
   "source": [
    "인자에 `yearly = True, quarterly = False`는 연간 재무제표를 의미하며, 딕셔너리 형태로 세 종류의 재무제표가 다운로드 되었다. 이를 하나의 데이터프레임으로 바꿔주도록 한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebade0a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data_fs_y = pd.concat([v for k, v in data_y.items()])\n",
    "data_fs_y = data_fs_y.stack().reset_index()\n",
    "data_fs_y.columns = ['account', 'date', 'value']\n",
    "data_fs_y['freq'] = 'y'\n",
    "\n",
    "data_fs_y.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1a0f718",
   "metadata": {},
   "source": [
    "1. 딕셔너리의 value에 해당하는 부분만을 선택한 후 `concat()` 함수를 통해 데이터프레임 형태로 합쳐준다.\n",
    "2. `stack()` 함수를 통해 데이터를 위에서 아래로 길게 재구조화 해주며, `reset_index()` 함수를 통해 인덱스를 초기화한다.\n",
    "3. 열 이름을 변경한다.\n",
    "4. freq 열에 연간에 해당하는 'y'를 입력한다.\n",
    "\n",
    "이처럼 패키지를 이용하여 미국 데이터도 매우 쉽게 다운로드 받을 수 있다. 분기별 재무제표를 받는법도 위와 같으며, 인자만 `yearly=False, quarterly=True`로 변경하면 된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52e6dd68",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_q = si.get_financials('AAPL', yearly=False, quarterly=True)\n",
    "data_fs_q = pd.concat([v for k, v in data_q.items()])\n",
    "data_fs_q = data_fs_q.stack().reset_index()\n",
    "data_fs_q.columns = ['account', 'date', 'value']\n",
    "data_fs_q['freq'] = 'q'\n",
    "\n",
    "data_fs_q.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "579f225d",
   "metadata": {},
   "source": [
    "### 전 종목 재무제표 다운로드\n",
    "\n",
    "`for loop` 구문을 이용하여 전 종목 재무제표를 다운로드 받도록 하겠다. 먼저 SQL에서 미국 재무제표 데이터에 해당하는 테이블(us_fs)를 만든다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e442bd2",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "use stock_db;\n",
    "\n",
    "create table us_fs\n",
    "(\n",
    "    account varchar(100),\n",
    "    date date,\n",
    "    value double,\n",
    "    freq varchar(1),\n",
    "    ticker varchar(20),    \n",
    "    primary key(account, date, ticker, freq)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0526fe5",
   "metadata": {},
   "source": [
    "이제 파이썬에서 아래 코드를 실행하면 for loop 구문을 통해 전 종목 재무제표가 DB에 저장된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a137833",
   "metadata": {
    "deletable": false,
    "editable": false,
    "run_control": {
     "frozen": true
    }
   },
   "outputs": [],
   "source": [
    "# 패키지 불러오기\n",
    "\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "import yahoo_fin.stock_info as si\n",
    "import numpy as np\n",
    "\n",
    "# DB 연결\n",
    "con = pymysql.connect(\n",
    "    user='root', passwd='1234', host='127.0.0.1', db='stock_db', charset='utf8'\n",
    ")\n",
    "\n",
    "mycursor = con.cursor()\n",
    "\n",
    "# 티커리스트 불러오기\n",
    "ticker_list = pd.read_sql(\"\"\"\n",
    "select * from us_ticker\n",
    "where date = (select max(date) from us_ticker);\n",
    "\"\"\", con = con)\n",
    "\n",
    "\n",
    "# DB 저장 쿼리\n",
    "query_fs = \"\"\"\n",
    "    insert into us_fs (account, date, value, freq, ticker)\n",
    "    values (%s,%s,%s,%s,%s) as new\n",
    "    on duplicate key update\n",
    "    value = new.value;\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "# 오류 발생시 저장할 데이터프레임 생성\n",
    "error_list = []\n",
    "fs_empty = pd.DataFrame({'account' : [None],                                                  \n",
    "                         'date' : [None],\n",
    "                         'value' : [None],\n",
    "                         'freq' : [None],     \n",
    "                         'ticker' : [None]                                               \n",
    "                         })\n",
    "\n",
    "\n",
    "# 전종목 주가 다운로드 및 저장\n",
    "\n",
    "for i in tqdm(range(0, len(ticker_list))): \n",
    "\n",
    "    # 빈 데이터프레임 복사\n",
    "    data_fs = fs_empty.copy()  \n",
    "    \n",
    "    # 티커 선택\n",
    "    ticker = ticker_list['symbol'][i]        \n",
    "    \n",
    "    # 오류 발생 시 이를 무시하고 다음 루프로 진행\n",
    "    try:\n",
    "        \n",
    "       # 재무제표 다운로드\n",
    "       data_y = si.get_financials(ticker, yearly=True, quarterly=False)\n",
    "       data_fs_y = pd.concat([v for k, v in data_y.items()])\n",
    "       data_fs_y = data_fs_y.stack().reset_index()\n",
    "       data_fs_y.columns = ['account', 'date', 'value']\n",
    "       data_fs_y['freq'] = 'y'\n",
    "\n",
    "       data_q = si.get_financials('AAPL', yearly=False, quarterly=True)\n",
    "       data_fs_q = pd.concat([v for k, v in data_q.items()])\n",
    "       data_fs_q = data_fs_q.stack().reset_index()\n",
    "       data_fs_q.columns = ['account', 'date', 'value']\n",
    "       data_fs_q['freq'] = 'q'\n",
    "       \n",
    "       data_fs = pd.concat([data_fs_y, data_fs_q], axis = 0)\n",
    "       data_fs['ticker'] = ticker             \n",
    "    \n",
    "    except:\n",
    "        \n",
    "        # 오류 발생시 빈 데이터프레임을 불러온 후 다음 루프로 이동\n",
    "        data_fs['ticker'] = ticker     \n",
    "        error_list.append(ticker)\n",
    "    \n",
    "    # 주가 데이터를 DB에 저장\n",
    "    args  = data_fs.values.tolist() \n",
    "    mycursor.executemany(query_fs, args)\n",
    "\n",
    "    con.commit()\n",
    "    \n",
    "    # 타임슬립 적용\n",
    "    time.sleep(2)    \n",
    "    \n",
    "# DB 연결 종료\n",
    "con.close()    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a8954fa",
   "metadata": {},
   "source": [
    "1. DB에 연결한다.\n",
    "2. 기준일이 최대, 즉 최근일 기준 보통주에 해당하는 종목 리스트(ticker_list)만 불러온다.\n",
    "3. DB에 저장할 쿼리(query)를 입력한다.\n",
    "4. 페이지 오류, 통신 오류 등 오류가 발생할 경우 저장할 빈 데이터프레임(fs_empty)을 생성한다.\n",
    "5. `for loop` 구문을 통해 전종목 재무제표를 다운로드 받으며, 진행상황을 알기위해 `tqdm()` 함수를 이용한다.\n",
    "6. `get_financials()` 함수를 이용해 연간 및 분기 재무제표를 받은 후, 두 테이블을 `concat()` 함수를 통해 행으로 묶어준다.\n",
    "7. 재무제표 데이터를 DB에 저장한다.\n",
    "8. 무한 크롤링을 방지하기 위해 한 번의 루프가 끝날 때마다 타임슬립을 적용한다.\n",
    "9. 모든 작업이 끝나면 DB와의 연결을 종료한다.\n",
    "\n",
    "```{figure} image/data_us/sql_fs.png\n",
    "---\n",
    "name: sql_fs\n",
    "---\n",
    "미국 재무제표 테이블\n",
    "```\n",
    "\n",
    "```{note}\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()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}