채권BBB-+5년+값.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['BBB-', '3.81', '4.47', '5.14', '5.60', '6.35', '7.14', '8.33', '8.66']\n",
      "8.66\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "today = datetime.today()\n",
    "yesterday = today - timedelta(1)\n",
    "yesterday = yesterday.strftime(\"%Y%m%d\")\n",
    "\n",
    "url = 'http://www.kisrating.com/ratings/statics_interest.asp?gubun=1&sdate='+yesterday\n",
    "html = requests.get(url).text\n",
    "soup = BeautifulSoup(html,'lxml')\n",
    "tr_list = soup.find_all('tr','')\n",
    "tr = tr_list[12].get_text()\n",
    "txt = tr.split()\n",
    "print (txt)\n",
    "print (txt[8])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### mysql create table 'bond5years'\n",
    "### bond5years - years, bbb_\n",
    "\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "from datetime import datetime, timedelta\n",
    "import mysql.connector\n",
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "def get_bond(yesterday, s_date):\n",
    "    n = 0\n",
    "    while int(yesterday) >= int(s_date):\n",
    "        s_date = date + timedelta(n)\n",
    "        s_date = s_date.strftime(\"%Y%m%d\")\n",
    "    \n",
    "        url = 'http://www.kisrating.com/ratings/statics_interest.asp?gubun=1&sdate='+s_date\n",
    "        html = requests.get(url).text\n",
    "        soup = BeautifulSoup(html,'lxml')\n",
    "        tr_list = soup.find_all('tr','')\n",
    "        tr = tr_list[12].get_text() #BBB- 찾기\n",
    "        txt = tr.split()\n",
    "        bbb_value = txt[8] # 5년 금리 가져오기\n",
    "        print (s_date,bbb_value)\n",
    "        sql = \"INSERT INTO bond5years (year, bbb_) values(%s, %s)\"\n",
    "        # 한번만 실행함으로 comment처리해 놓음.\n",
    "        # con.execute(sql, s_date, bbb_value, if_exists='replace')\n",
    "        n += 1\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    pwd = 'rlaehgus1'\n",
    "    engine = create_engine('mysql+mysqlconnector://root:'+pwd+'@localhost/findb', echo=False)\n",
    "    con = engine.connect()\n",
    "    \n",
    "    today = datetime.today()\n",
    "    yesterday = today - timedelta(1)\n",
    "    yesterday = yesterday.strftime(\"%Y%m%d\")\n",
    "    date = datetime(2002,1,1) # 한신평 채권금리 첫 제공 일자\n",
    "    s_date = date.strftime(\"%Y%m%d\")\n",
    "    \n",
    "    get_bond(yesterday, s_date)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x2bb676b7f60>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### bond5years - years, bbb_\n",
    "### bond5yeas table에 매일 금리를 append함.\n",
    "\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "from datetime import datetime, timedelta\n",
    "import mysql.connector\n",
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "pwd = 'rlaehgus1'\n",
    "engine = create_engine('mysql+mysqlconnector://root:'+pwd+'@localhost/findb', echo=False)\n",
    "con = engine.connect()\n",
    "    \n",
    "today = datetime.today()\n",
    "yesterday = today - timedelta(1)\n",
    "yesterday = yesterday.strftime(\"%Y%m%d\")\n",
    "\n",
    "url = 'http://www.kisrating.com/ratings/statics_interest.asp?gubun=1&sdate='+yesterday\n",
    "html = requests.get(url).text\n",
    "soup = BeautifulSoup(html,'lxml')\n",
    "tr_list = soup.find_all('tr','')\n",
    "tr = tr_list[12].get_text() #BBB- 찾기\n",
    "txt = tr.split()\n",
    "bbb_value = txt[8] # 5년 금리 가져오기\n",
    "\n",
    "sql = \"INSERT INTO bond5years (year, bbb_) values(%s, %s)\"\n",
    "con.execute(sql, yesterday, bbb_value, if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}