Financial Statement Analysis of S&P 500 stocks-Database Engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preparation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql mysql+pymysql://root:@fe512_mysql/fe512db\n",
    "%sql USE fe512db; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See the list of databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Database</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FE512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fe512db</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>information_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mysql</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>performance_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sys</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('FE512',),\n",
       " ('fe512db',),\n",
       " ('information_schema',),\n",
       " ('mysql',),\n",
       " ('performance_schema',),\n",
       " ('sys',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SHOW DATABASES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Choose the database we are going to use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>DATABASE()</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fe512db</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('fe512db',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DATABASE();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clear up the database by dropping unnecessary tables and views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS MSQR;\n",
    "DROP VIEW IF EXISTS HPE_earlier;\n",
    "DROP VIEW IF EXISTS HPE_later;\n",
    "DROP VIEW IF EXISTS DHI_earlier;\n",
    "DROP VIEW IF EXISTS DHI_later;\n",
    "DROP TABLE IF EXISTS ROE_SUM1;\n",
    "DROP TABLE IF EXISTS prices;\n",
    "DROP TABLE IF EXISTS financial_statements;\n",
    "DROP TABLE IF EXISTS securities;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See the list of existed tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Tables_in_fe512db</th>\n",
       "        <th>Table_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13mar</td>\n",
       "        <td>VIEW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16mar</td>\n",
       "        <td>VIEW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013report</td>\n",
       "        <td>BASE TABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016report</td>\n",
       "        <td>BASE TABLE</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('13mar', 'VIEW'),\n",
       " ('16mar', 'VIEW'),\n",
       " ('2013report', 'BASE TABLE'),\n",
       " ('2016report', 'BASE TABLE')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SHOW FULL TABLES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create the table of securities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "CREATE TABLE `fe512db`.`securities` (\n",
    "  `Ticker_symbol` VARCHAR(45) NOT NULL,\n",
    "  `Company_name` VARCHAR(45) NULL,\n",
    "  `SEC_filings` VARCHAR(45) NULL,\n",
    "  `GICS_Sector` VARCHAR(45) NULL,\n",
    "  `GICS_Sub_Industry` VARCHAR(45) NULL,\n",
    "  `Address` VARCHAR(45) NULL,\n",
    "  `State` VARCHAR(45) NULL,\n",
    "  `Date_first_added` Date NULL,\n",
    "  `CIK` INT,\n",
    "  PRIMARY KEY (`Ticker_symbol`));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the creation of table securities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Tables_in_fe512db</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13mar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16mar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013report</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016report</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>securities</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('13mar',), ('16mar',), ('2013report',), ('2016report',), ('securities',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SHOW tables;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up the configuration of mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SET sql_mode = '';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the data into the table of securities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "505 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "LOAD DATA INFILE '/home/data/securities.csv' \n",
    "INTO TABLE securities \n",
    "FIELDS TERMINATED BY ',' \n",
    "ENCLOSED BY '\"'\n",
    "IGNORE 1 LINES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the creation of securities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ticker_symbol</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>NO</td>\n",
       "        <td>PRI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Company_name</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SEC_filings</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GICS_Sector</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GICS_Sub_Industry</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Address</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>State</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Date_first_added</td>\n",
       "        <td>date</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CIK</td>\n",
       "        <td>int(11)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ticker_symbol', 'varchar(45)', 'NO', 'PRI', None, ''),\n",
       " ('Company_name', 'varchar(45)', 'YES', '', None, ''),\n",
       " ('SEC_filings', 'varchar(45)', 'YES', '', None, ''),\n",
       " ('GICS_Sector', 'varchar(45)', 'YES', '', None, ''),\n",
       " ('GICS_Sub_Industry', 'varchar(45)', 'YES', '', None, ''),\n",
       " ('Address', 'varchar(45)', 'YES', '', None, ''),\n",
       " ('State', 'varchar(45)', 'YES', '', None, ''),\n",
       " ('Date_first_added', 'date', 'YES', '', None, ''),\n",
       " ('CIK', 'int(11)', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DESCRIBE securities;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show five rows of securities for example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_symbol</th>\n",
       "        <th>Company_name</th>\n",
       "        <th>SEC_filings</th>\n",
       "        <th>GICS_Sector</th>\n",
       "        <th>GICS_Sub_Industry</th>\n",
       "        <th>Address</th>\n",
       "        <th>State</th>\n",
       "        <th>Date_first_added</th>\n",
       "        <th>CIK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A</td>\n",
       "        <td>Agilent Technologies Inc</td>\n",
       "        <td>reports</td>\n",
       "        <td>Health Care</td>\n",
       "        <td>Health Care Equipment</td>\n",
       "        <td>Santa Clara</td>\n",
       "        <td> California</td>\n",
       "        <td>0000-00-00</td>\n",
       "        <td>1090872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>American Airlines Group</td>\n",
       "        <td>reports</td>\n",
       "        <td>Industrials</td>\n",
       "        <td>Airlines</td>\n",
       "        <td>Fort Worth</td>\n",
       "        <td> Texas</td>\n",
       "        <td>2015-03-23</td>\n",
       "        <td>6201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAP</td>\n",
       "        <td>Advance Auto Parts</td>\n",
       "        <td>reports</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td>Automotive Retail</td>\n",
       "        <td>Roanoke</td>\n",
       "        <td> Virginia</td>\n",
       "        <td>2015-07-09</td>\n",
       "        <td>1158449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAPL</td>\n",
       "        <td>Apple Inc.</td>\n",
       "        <td>reports</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td>Computer Hardware</td>\n",
       "        <td>Cupertino</td>\n",
       "        <td> California</td>\n",
       "        <td>1982-11-30</td>\n",
       "        <td>320193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ABBV</td>\n",
       "        <td>AbbVie</td>\n",
       "        <td>reports</td>\n",
       "        <td>Health Care</td>\n",
       "        <td>Pharmaceuticals</td>\n",
       "        <td>North Chicago</td>\n",
       "        <td> Illinois</td>\n",
       "        <td>2012-12-31</td>\n",
       "        <td>1551152</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('A', 'Agilent Technologies Inc', 'reports', 'Health Care', 'Health Care Equipment', 'Santa Clara', ' California', '0000-00-00', 1090872),\n",
       " ('AAL', 'American Airlines Group', 'reports', 'Industrials', 'Airlines', 'Fort Worth', ' Texas', datetime.date(2015, 3, 23), 6201),\n",
       " ('AAP', 'Advance Auto Parts', 'reports', 'Consumer Discretionary', 'Automotive Retail', 'Roanoke', ' Virginia', datetime.date(2015, 7, 9), 1158449),\n",
       " ('AAPL', 'Apple Inc.', 'reports', 'Information Technology', 'Computer Hardware', 'Cupertino', ' California', datetime.date(1982, 11, 30), 320193),\n",
       " ('ABBV', 'AbbVie', 'reports', 'Health Care', 'Pharmaceuticals', 'North Chicago', ' Illinois', datetime.date(2012, 12, 31), 1551152)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM securities LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See how many rows in the table of securities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>505</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(505,)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM securities;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create the table prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE `fe512db`.`prices` (\n",
    "  `symbol` VARCHAR(45) NOT NULL,\n",
    "  `date` DATE NOT NULL,\n",
    "  `open` FLOAT NULL,\n",
    "  `close` FLOAT NULL,\n",
    "  `low` FLOAT NULL,\n",
    "  `high` FLOAT NULL,\n",
    "  `volume` INT NULL,\n",
    "  PRIMARY KEY (`date`, `symbol`),\n",
    "  CONSTRAINT `symbol`\n",
    "    FOREIGN KEY (`symbol`)\n",
    "    REFERENCES `fe512db`.`securities` (`Ticker_symbol`));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the creation of prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>symbol</td>\n",
       "        <td>varchar(45)</td>\n",
       "        <td>NO</td>\n",
       "        <td>PRI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>date</td>\n",
       "        <td>date</td>\n",
       "        <td>NO</td>\n",
       "        <td>PRI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>open</td>\n",
       "        <td>float</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>close</td>\n",
       "        <td>float</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>low</td>\n",
       "        <td>float</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>high</td>\n",
       "        <td>float</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>volume</td>\n",
       "        <td>int(11)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('symbol', 'varchar(45)', 'NO', 'PRI', None, ''),\n",
       " ('date', 'date', 'NO', 'PRI', None, ''),\n",
       " ('open', 'float', 'YES', '', None, ''),\n",
       " ('close', 'float', 'YES', '', None, ''),\n",
       " ('low', 'float', 'YES', '', None, ''),\n",
       " ('high', 'float', 'YES', '', None, ''),\n",
       " ('volume', 'int(11)', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DESCRIBE prices;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the data into table prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "851264 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "LOAD DATA INFILE '/home/data/prices.csv' \n",
    "INTO TABLE prices fields \n",
    "TERMINATED BY ',' \n",
    "IGNORE 1 LINES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See a sample of data records in prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>symbol</th>\n",
       "        <th>date</th>\n",
       "        <th>open</th>\n",
       "        <th>close</th>\n",
       "        <th>low</th>\n",
       "        <th>high</th>\n",
       "        <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>22.4535</td>\n",
       "        <td>22.3891</td>\n",
       "        <td>22.2675</td>\n",
       "        <td>22.6252</td>\n",
       "        <td>3815500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>4.84</td>\n",
       "        <td>4.77</td>\n",
       "        <td>4.66</td>\n",
       "        <td>4.94</td>\n",
       "        <td>9837300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAP</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>40.7</td>\n",
       "        <td>40.38</td>\n",
       "        <td>40.36</td>\n",
       "        <td>41.04</td>\n",
       "        <td>1701700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAPL</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>30.49</td>\n",
       "        <td>30.5729</td>\n",
       "        <td>30.34</td>\n",
       "        <td>30.6429</td>\n",
       "        <td>123432400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ABC</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>26.29</td>\n",
       "        <td>26.63</td>\n",
       "        <td>26.14</td>\n",
       "        <td>26.69</td>\n",
       "        <td>2455900</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('A', datetime.date(2010, 1, 4), 22.4535, 22.3891, 22.2675, 22.6252, 3815500),\n",
       " ('AAL', datetime.date(2010, 1, 4), 4.84, 4.77, 4.66, 4.94, 9837300),\n",
       " ('AAP', datetime.date(2010, 1, 4), 40.7, 40.38, 40.36, 41.04, 1701700),\n",
       " ('AAPL', datetime.date(2010, 1, 4), 30.49, 30.5729, 30.34, 30.6429, 123432400),\n",
       " ('ABC', datetime.date(2010, 1, 4), 26.29, 26.63, 26.14, 26.69, 2455900)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM prices LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the total number of records and period in prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(symbol)</th>\n",
       "        <th>MIN(date)</th>\n",
       "        <th>MAX(date)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>851264</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>2016-12-30</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(851264, datetime.date(2010, 1, 4), datetime.date(2016, 12, 30))]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(symbol),MIN(date), MAX(date) FROM prices;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create the table of financial_statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE `fe512db`.`financial_statements` (\n",
    "  `Ticker_Symbol` VARCHAR(45) NOT NULL,\n",
    "  `Period_Ending` DATE NOT NULL,\n",
    "  `Accounts_Payable` DOUBLE NULL,\n",
    "  `Accounts_Receivable` DOUBLE NULL,\n",
    "  `Add'l_income/expense_ items` DOUBLE NULL,\n",
    "  `After_Tax_ROE` DOUBLE NULL,\n",
    "  `Capital_Expenditures` DOUBLE NULL,\n",
    "  `Capital_Surplus` DOUBLE NULL,\n",
    "  `Cash_Ratio` DOUBLE NULL,\n",
    "  `Cash_and_Cash_Equivalents` DOUBLE NULL,\n",
    "  `Changes_in_Inventories` DOUBLE NULL,\n",
    "  `Common_Stocks` DOUBLE NULL,\n",
    "  `Cost_of_Revenue` DOUBLE NULL,\n",
    "  `Current_Ratio` DOUBLE NULL,\n",
    "  `Deferred_Asset_Charges` DOUBLE NULL,\n",
    "  `Deferred_Liability_Charges` DOUBLE NULL,\n",
    "  `Depreciation` DOUBLE NULL,\n",
    "  `Earnings_Before_Interest_and_Tax` DOUBLE NULL,\n",
    "  `Earnings_Before_Tax` DOUBLE NULL,\n",
    "  `Effect_of_Exchange_Rate` DOUBLE NULL,\n",
    "  `Equity_Earnings/Loss_Unconsolidated_Subsidiary` DOUBLE NULL,\n",
    "  `Fixed_Assets` DOUBLE NULL,\n",
    "  `Goodwill` DOUBLE NULL,\n",
    "  `Gross_Margin` DOUBLE NULL,\n",
    "  `Gross_Profit` DOUBLE NULL,\n",
    "  `Income_Tax` DOUBLE NULL,\n",
    "  `Intangible_Assets` DOUBLE NULL,\n",
    "  `Interest_Expense` DOUBLE NULL,\n",
    "  `Inventory` DOUBLE NULL,\n",
    "  `Investments` DOUBLE NULL,\n",
    "  `Liabilities` DOUBLE NULL,\n",
    "  `Long-Term_Debt` DOUBLE NULL,\n",
    "  `Long-Term_Investments` DOUBLE NULL,\n",
    "  `Minority_Interest` DOUBLE NULL,\n",
    "  `Misc._Stocks` DOUBLE NULL,\n",
    "  `Net_Borrowings` DOUBLE NULL,\n",
    "  `Net_Cash_Flow` DOUBLE NULL,\n",
    "  `Net_Cash_Flow-Operating` DOUBLE NULL,\n",
    "  `Net_Cash_Flows-Financing` DOUBLE NULL,\n",
    "  `Net_Cash_Flows-Investing` DOUBLE NULL,\n",
    "  `Net_Income` DOUBLE NULL,\n",
    "  `Net_Income_Adjustments` DOUBLE NULL,\n",
    "  `Net_Income_Applicable_to_Common_Shareholders` DOUBLE NULL,\n",
    "  `Net_Income-Cont._Operations` DOUBLE NULL,\n",
    "  `Net_Receivables` DOUBLE NULL,\n",
    "  `Non-Recurring_Itemsl` DOUBLE NULL,\n",
    "  `Operating_Income` DOUBLE NULL,\n",
    "  `Operating_Margin` DOUBLE NULL,\n",
    "  `Other_Assets` DOUBLE NULL,\n",
    "  `Other_Current_Assets` DOUBLE NULL,\n",
    "  `Other_Current_Liabilities` DOUBLE NULL,\n",
    "  `Other_Equity` DOUBLE NULL,\n",
    "  `Other_Financing_Activities` DOUBLE NULL,\n",
    "  `Other_Investing_Activities` DOUBLE NULL,\n",
    "  `Other_Liabilities` DOUBLE NULL,\n",
    "  `Other_Operating_Activities` DOUBLE NULL,\n",
    "  `Other_Operating_Items` DOUBLE NULL,\n",
    "  `Pre-Tax_Margin` DOUBLE NULL,\n",
    "  `Pre-Tax_ROE` DOUBLE NULL,\n",
    "  `Profit_Margin` DOUBLE NULL,\n",
    "  `Quick_Ratio` DOUBLE NULL,\n",
    "  `Research_and_Development` DOUBLE NULL,\n",
    "  `Retained_Earnings` DOUBLE NULL,\n",
    "  `Sale_and_Purchase_of_Stock` DOUBLE NULL,\n",
    "  `Sales_General_and_Admin` DOUBLE NULL,\n",
    "  `Short-Term_Debt_/_Current_Portion_of_Long-Term_Debt` DOUBLE NULL,\n",
    "  `Short-Term_Investments` DOUBLE NULL,\n",
    "  `Total_Assets` DOUBLE NULL,\n",
    "  `Total_Current_Assets` DOUBLE NULL,\n",
    "  `Total_Current_Liabilities` DOUBLE NULL,\n",
    "  `Total_Equity` DOUBLE NULL,\n",
    "  `Total_Liabilities` DOUBLE NULL,\n",
    "  `Total_Liabilities_&_Equity` DOUBLE NULL,\n",
    "  `Total_Revenue` DOUBLE NULL,\n",
    "  `Treasury_Stock` DOUBLE NULL,\n",
    "  `For_Year` DOUBLE NULL,\n",
    "  `Earnings_Per_Share` DOUBLE NULL,\n",
    "  `Estimated_Shares_Outstanding` DOUBLE NULL,\n",
    "  PRIMARY KEY (Ticker_Symbol, Period_Ending),\n",
    "  CONSTRAINT `Ticker_Symbol`\n",
    "  FOREIGN KEY (`Ticker_Symbol`)\n",
    "  REFERENCES `fe512db`.`securities` (`Ticker_symbol`)\n",
    "  ON DELETE NO ACTION\n",
    "  ON UPDATE NO ACTION);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "Load data into the table of financial_statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1776 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "LOAD DATA INFILE '/home/data/financial_statements.csv' \n",
    "INTO TABLE financial_statements fields \n",
    "TERMINATED BY ',' \n",
    "IGNORE 1 LINES;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>Accounts_Payable</th>\n",
       "        <th>Accounts_Receivable</th>\n",
       "        <th>Add&#x27;l_income/expense_ items</th>\n",
       "        <th>After_Tax_ROE</th>\n",
       "        <th>Capital_Expenditures</th>\n",
       "        <th>Capital_Surplus</th>\n",
       "        <th>Cash_Ratio</th>\n",
       "        <th>Cash_and_Cash_Equivalents</th>\n",
       "        <th>Changes_in_Inventories</th>\n",
       "        <th>Common_Stocks</th>\n",
       "        <th>Cost_of_Revenue</th>\n",
       "        <th>Current_Ratio</th>\n",
       "        <th>Deferred_Asset_Charges</th>\n",
       "        <th>Deferred_Liability_Charges</th>\n",
       "        <th>Depreciation</th>\n",
       "        <th>Earnings_Before_Interest_and_Tax</th>\n",
       "        <th>Earnings_Before_Tax</th>\n",
       "        <th>Effect_of_Exchange_Rate</th>\n",
       "        <th>Equity_Earnings/Loss_Unconsolidated_Subsidiary</th>\n",
       "        <th>Fixed_Assets</th>\n",
       "        <th>Goodwill</th>\n",
       "        <th>Gross_Margin</th>\n",
       "        <th>Gross_Profit</th>\n",
       "        <th>Income_Tax</th>\n",
       "        <th>Intangible_Assets</th>\n",
       "        <th>Interest_Expense</th>\n",
       "        <th>Inventory</th>\n",
       "        <th>Investments</th>\n",
       "        <th>Liabilities</th>\n",
       "        <th>Long-Term_Debt</th>\n",
       "        <th>Long-Term_Investments</th>\n",
       "        <th>Minority_Interest</th>\n",
       "        <th>Misc._Stocks</th>\n",
       "        <th>Net_Borrowings</th>\n",
       "        <th>Net_Cash_Flow</th>\n",
       "        <th>Net_Cash_Flow-Operating</th>\n",
       "        <th>Net_Cash_Flows-Financing</th>\n",
       "        <th>Net_Cash_Flows-Investing</th>\n",
       "        <th>Net_Income</th>\n",
       "        <th>Net_Income_Adjustments</th>\n",
       "        <th>Net_Income_Applicable_to_Common_Shareholders</th>\n",
       "        <th>Net_Income-Cont._Operations</th>\n",
       "        <th>Net_Receivables</th>\n",
       "        <th>Non-Recurring_Itemsl</th>\n",
       "        <th>Operating_Income</th>\n",
       "        <th>Operating_Margin</th>\n",
       "        <th>Other_Assets</th>\n",
       "        <th>Other_Current_Assets</th>\n",
       "        <th>Other_Current_Liabilities</th>\n",
       "        <th>Other_Equity</th>\n",
       "        <th>Other_Financing_Activities</th>\n",
       "        <th>Other_Investing_Activities</th>\n",
       "        <th>Other_Liabilities</th>\n",
       "        <th>Other_Operating_Activities</th>\n",
       "        <th>Other_Operating_Items</th>\n",
       "        <th>Pre-Tax_Margin</th>\n",
       "        <th>Pre-Tax_ROE</th>\n",
       "        <th>Profit_Margin</th>\n",
       "        <th>Quick_Ratio</th>\n",
       "        <th>Research_and_Development</th>\n",
       "        <th>Retained_Earnings</th>\n",
       "        <th>Sale_and_Purchase_of_Stock</th>\n",
       "        <th>Sales_General_and_Admin</th>\n",
       "        <th>Short-Term_Debt_/_Current_Portion_of_Long-Term_Debt</th>\n",
       "        <th>Short-Term_Investments</th>\n",
       "        <th>Total_Assets</th>\n",
       "        <th>Total_Current_Assets</th>\n",
       "        <th>Total_Current_Liabilities</th>\n",
       "        <th>Total_Equity</th>\n",
       "        <th>Total_Liabilities</th>\n",
       "        <th>Total_Liabilities_&amp;_Equity</th>\n",
       "        <th>Total_Revenue</th>\n",
       "        <th>Treasury_Stock</th>\n",
       "        <th>For_Year</th>\n",
       "        <th>Earnings_Per_Share</th>\n",
       "        <th>Estimated_Shares_Outstanding</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>2012-12-31</td>\n",
       "        <td>3068000000.0</td>\n",
       "        <td>-222000000.0</td>\n",
       "        <td>-1961000000.0</td>\n",
       "        <td>23.0</td>\n",
       "        <td>-1888000000.0</td>\n",
       "        <td>4695000000.0</td>\n",
       "        <td>53.0</td>\n",
       "        <td>1330000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>127000000.0</td>\n",
       "        <td>10499000000.0</td>\n",
       "        <td>78.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>223000000.0</td>\n",
       "        <td>1001000000.0</td>\n",
       "        <td>-1813000000.0</td>\n",
       "        <td>-2445000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>13402000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>58.0</td>\n",
       "        <td>14356000000.0</td>\n",
       "        <td>-569000000.0</td>\n",
       "        <td>869000000.0</td>\n",
       "        <td>632000000.0</td>\n",
       "        <td>580000000.0</td>\n",
       "        <td>306000000.0</td>\n",
       "        <td>473000000.0</td>\n",
       "        <td>7116000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-1020000000.0</td>\n",
       "        <td>197000000.0</td>\n",
       "        <td>1285000000.0</td>\n",
       "        <td>483000000.0</td>\n",
       "        <td>-1571000000.0</td>\n",
       "        <td>-1876000000.0</td>\n",
       "        <td>2050000000.0</td>\n",
       "        <td>-1876000000.0</td>\n",
       "        <td>-4084000000.0</td>\n",
       "        <td>1124000000.0</td>\n",
       "        <td>386000000.0</td>\n",
       "        <td>148000000.0</td>\n",
       "        <td>1.0</td>\n",
       "        <td>2167000000.0</td>\n",
       "        <td>626000000.0</td>\n",
       "        <td>4524000000.0</td>\n",
       "        <td>-2980000000.0</td>\n",
       "        <td>1509000000.0</td>\n",
       "        <td>11000000.0</td>\n",
       "        <td>15147000000.0</td>\n",
       "        <td>-141000000.0</td>\n",
       "        <td>845000000.0</td>\n",
       "        <td>10.0</td>\n",
       "        <td>31.0</td>\n",
       "        <td>8.0</td>\n",
       "        <td>72.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-9462000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>12977000000.0</td>\n",
       "        <td>1419000000.0</td>\n",
       "        <td>3412000000.0</td>\n",
       "        <td>23510000000.0</td>\n",
       "        <td>7072000000.0</td>\n",
       "        <td>9011000000.0</td>\n",
       "        <td>-7987000000.0</td>\n",
       "        <td>24891000000.0</td>\n",
       "        <td>16904000000.0</td>\n",
       "        <td>24855000000.0</td>\n",
       "        <td>-367000000.0</td>\n",
       "        <td>2012.0</td>\n",
       "        <td>-5.6</td>\n",
       "        <td>335000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>2013-12-31</td>\n",
       "        <td>4975000000.0</td>\n",
       "        <td>-93000000.0</td>\n",
       "        <td>-2723000000.0</td>\n",
       "        <td>67.0</td>\n",
       "        <td>-3114000000.0</td>\n",
       "        <td>10592000000.0</td>\n",
       "        <td>75.0</td>\n",
       "        <td>2175000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>5000000.0</td>\n",
       "        <td>11019000000.0</td>\n",
       "        <td>104.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>935000000.0</td>\n",
       "        <td>1020000000.0</td>\n",
       "        <td>-1324000000.0</td>\n",
       "        <td>-2180000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>19259000000.0</td>\n",
       "        <td>4086000000.0</td>\n",
       "        <td>59.0</td>\n",
       "        <td>15724000000.0</td>\n",
       "        <td>-346000000.0</td>\n",
       "        <td>2311000000.0</td>\n",
       "        <td>856000000.0</td>\n",
       "        <td>1012000000.0</td>\n",
       "        <td>-1181000000.0</td>\n",
       "        <td>-235000000.0</td>\n",
       "        <td>15353000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2208000000.0</td>\n",
       "        <td>660000000.0</td>\n",
       "        <td>675000000.0</td>\n",
       "        <td>3799000000.0</td>\n",
       "        <td>-3814000000.0</td>\n",
       "        <td>-1834000000.0</td>\n",
       "        <td>1873000000.0</td>\n",
       "        <td>-1834000000.0</td>\n",
       "        <td>-4489000000.0</td>\n",
       "        <td>1560000000.0</td>\n",
       "        <td>559000000.0</td>\n",
       "        <td>1399000000.0</td>\n",
       "        <td>5.0</td>\n",
       "        <td>2299000000.0</td>\n",
       "        <td>1465000000.0</td>\n",
       "        <td>7385000000.0</td>\n",
       "        <td>-2032000000.0</td>\n",
       "        <td>1711000000.0</td>\n",
       "        <td>481000000.0</td>\n",
       "        <td>14915000000.0</td>\n",
       "        <td>-56000000.0</td>\n",
       "        <td>853000000.0</td>\n",
       "        <td>8.0</td>\n",
       "        <td>80.0</td>\n",
       "        <td>7.0</td>\n",
       "        <td>96.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-11296000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>12913000000.0</td>\n",
       "        <td>1446000000.0</td>\n",
       "        <td>8111000000.0</td>\n",
       "        <td>42278000000.0</td>\n",
       "        <td>14323000000.0</td>\n",
       "        <td>13806000000.0</td>\n",
       "        <td>-2731000000.0</td>\n",
       "        <td>45009000000.0</td>\n",
       "        <td>42278000000.0</td>\n",
       "        <td>26743000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2013.0</td>\n",
       "        <td>-11.25</td>\n",
       "        <td>163022222.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>2014-12-31</td>\n",
       "        <td>4668000000.0</td>\n",
       "        <td>-160000000.0</td>\n",
       "        <td>-150000000.0</td>\n",
       "        <td>143.0</td>\n",
       "        <td>-5311000000.0</td>\n",
       "        <td>15135000000.0</td>\n",
       "        <td>60.0</td>\n",
       "        <td>1768000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>7000000.0</td>\n",
       "        <td>15620000000.0</td>\n",
       "        <td>88.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>829000000.0</td>\n",
       "        <td>1342000000.0</td>\n",
       "        <td>4099000000.0</td>\n",
       "        <td>3212000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>23084000000.0</td>\n",
       "        <td>4091000000.0</td>\n",
       "        <td>63.0</td>\n",
       "        <td>27030000000.0</td>\n",
       "        <td>330000000.0</td>\n",
       "        <td>2240000000.0</td>\n",
       "        <td>887000000.0</td>\n",
       "        <td>1004000000.0</td>\n",
       "        <td>1799000000.0</td>\n",
       "        <td>-1026000000.0</td>\n",
       "        <td>16043000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>170000000.0</td>\n",
       "        <td>-146000000.0</td>\n",
       "        <td>3080000000.0</td>\n",
       "        <td>-315000000.0</td>\n",
       "        <td>-2911000000.0</td>\n",
       "        <td>2882000000.0</td>\n",
       "        <td>542000000.0</td>\n",
       "        <td>2882000000.0</td>\n",
       "        <td>2882000000.0</td>\n",
       "        <td>1771000000.0</td>\n",
       "        <td>800000000.0</td>\n",
       "        <td>4249000000.0</td>\n",
       "        <td>10.0</td>\n",
       "        <td>2060000000.0</td>\n",
       "        <td>898000000.0</td>\n",
       "        <td>7059000000.0</td>\n",
       "        <td>-4559000000.0</td>\n",
       "        <td>817000000.0</td>\n",
       "        <td>601000000.0</td>\n",
       "        <td>10928000000.0</td>\n",
       "        <td>-500000000.0</td>\n",
       "        <td>1295000000.0</td>\n",
       "        <td>8.0</td>\n",
       "        <td>159.0</td>\n",
       "        <td>7.0</td>\n",
       "        <td>80.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-8562000000.0</td>\n",
       "        <td>-1052000000.0</td>\n",
       "        <td>20686000000.0</td>\n",
       "        <td>1677000000.0</td>\n",
       "        <td>6309000000.0</td>\n",
       "        <td>43225000000.0</td>\n",
       "        <td>11750000000.0</td>\n",
       "        <td>13404000000.0</td>\n",
       "        <td>2021000000.0</td>\n",
       "        <td>41204000000.0</td>\n",
       "        <td>43225000000.0</td>\n",
       "        <td>42650000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2014.0</td>\n",
       "        <td>4.02</td>\n",
       "        <td>716915422.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>5102000000.0</td>\n",
       "        <td>352000000.0</td>\n",
       "        <td>-708000000.0</td>\n",
       "        <td>135.0</td>\n",
       "        <td>-6151000000.0</td>\n",
       "        <td>11591000000.0</td>\n",
       "        <td>51.0</td>\n",
       "        <td>1085000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>6000000.0</td>\n",
       "        <td>11096000000.0</td>\n",
       "        <td>73.0</td>\n",
       "        <td>2477000000.0</td>\n",
       "        <td>667000000.0</td>\n",
       "        <td>1487000000.0</td>\n",
       "        <td>5496000000.0</td>\n",
       "        <td>4616000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>27510000000.0</td>\n",
       "        <td>4091000000.0</td>\n",
       "        <td>73.0</td>\n",
       "        <td>29894000000.0</td>\n",
       "        <td>-2994000000.0</td>\n",
       "        <td>2249000000.0</td>\n",
       "        <td>880000000.0</td>\n",
       "        <td>863000000.0</td>\n",
       "        <td>443000000.0</td>\n",
       "        <td>-633000000.0</td>\n",
       "        <td>18330000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2856000000.0</td>\n",
       "        <td>-604000000.0</td>\n",
       "        <td>6249000000.0</td>\n",
       "        <td>-1259000000.0</td>\n",
       "        <td>-5594000000.0</td>\n",
       "        <td>7610000000.0</td>\n",
       "        <td>-2662000000.0</td>\n",
       "        <td>7610000000.0</td>\n",
       "        <td>7610000000.0</td>\n",
       "        <td>1425000000.0</td>\n",
       "        <td>1051000000.0</td>\n",
       "        <td>6204000000.0</td>\n",
       "        <td>15.0</td>\n",
       "        <td>2103000000.0</td>\n",
       "        <td>748000000.0</td>\n",
       "        <td>6272000000.0</td>\n",
       "        <td>-4732000000.0</td>\n",
       "        <td>96000000.0</td>\n",
       "        <td>114000000.0</td>\n",
       "        <td>10178000000.0</td>\n",
       "        <td>95000000.0</td>\n",
       "        <td>1364000000.0</td>\n",
       "        <td>11.0</td>\n",
       "        <td>82.0</td>\n",
       "        <td>19.0</td>\n",
       "        <td>67.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-1230000000.0</td>\n",
       "        <td>-3846000000.0</td>\n",
       "        <td>21275000000.0</td>\n",
       "        <td>2231000000.0</td>\n",
       "        <td>5864000000.0</td>\n",
       "        <td>48415000000.0</td>\n",
       "        <td>9985000000.0</td>\n",
       "        <td>13605000000.0</td>\n",
       "        <td>5635000000.0</td>\n",
       "        <td>42780000000.0</td>\n",
       "        <td>48415000000.0</td>\n",
       "        <td>40990000000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>11.39</td>\n",
       "        <td>668129938.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAP</td>\n",
       "        <td>2012-12-29</td>\n",
       "        <td>2409453000.0</td>\n",
       "        <td>-89482000.0</td>\n",
       "        <td>600000.0</td>\n",
       "        <td>32.0</td>\n",
       "        <td>-271182000.0</td>\n",
       "        <td>520215000.0</td>\n",
       "        <td>23.0</td>\n",
       "        <td>598111000.0</td>\n",
       "        <td>-260298000.0</td>\n",
       "        <td>7000.0</td>\n",
       "        <td>3106967000.0</td>\n",
       "        <td>124.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>189544000.0</td>\n",
       "        <td>657915000.0</td>\n",
       "        <td>624074000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1292547000.0</td>\n",
       "        <td>76389000.0</td>\n",
       "        <td>50.0</td>\n",
       "        <td>3098036000.0</td>\n",
       "        <td>236404000.0</td>\n",
       "        <td>28845000.0</td>\n",
       "        <td>33841000.0</td>\n",
       "        <td>2308609000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>426323000.0</td>\n",
       "        <td>604461000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>177445000.0</td>\n",
       "        <td>540210000.0</td>\n",
       "        <td>685281000.0</td>\n",
       "        <td>127907000.0</td>\n",
       "        <td>-272978000.0</td>\n",
       "        <td>387670000.0</td>\n",
       "        <td>23311000.0</td>\n",
       "        <td>387670000.0</td>\n",
       "        <td>387670000.0</td>\n",
       "        <td>229866000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>657315000.0</td>\n",
       "        <td>11.0</td>\n",
       "        <td>31833000.0</td>\n",
       "        <td>47614000.0</td>\n",
       "        <td>149558000.0</td>\n",
       "        <td>2667000.0</td>\n",
       "        <td>-33499000.0</td>\n",
       "        <td>-1796000.0</td>\n",
       "        <td>239021000.0</td>\n",
       "        <td>8213000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>10.0</td>\n",
       "        <td>52.0</td>\n",
       "        <td>6.0</td>\n",
       "        <td>34.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>714900000.0</td>\n",
       "        <td>-18600000.0</td>\n",
       "        <td>2440721000.0</td>\n",
       "        <td>627000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4613814000.0</td>\n",
       "        <td>3184200000.0</td>\n",
       "        <td>2559638000.0</td>\n",
       "        <td>1210694000.0</td>\n",
       "        <td>3403120000.0</td>\n",
       "        <td>4613814000.0</td>\n",
       "        <td>6205003000.0</td>\n",
       "        <td>-27095000.0</td>\n",
       "        <td>2012.0</td>\n",
       "        <td>5.29</td>\n",
       "        <td>73283553.88</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AAL', datetime.date(2012, 12, 31), 3068000000.0, -222000000.0, -1961000000.0, 23.0, -1888000000.0, 4695000000.0, 53.0, 1330000000.0, 0.0, 127000000.0, 10499000000.0, 78.0, 0.0, 223000000.0, 1001000000.0, -1813000000.0, -2445000000.0, 0.0, 0.0, 13402000000.0, 0.0, 58.0, 14356000000.0, -569000000.0, 869000000.0, 632000000.0, 580000000.0, 306000000.0, 473000000.0, 7116000000.0, 0.0, 0.0, 0.0, -1020000000.0, 197000000.0, 1285000000.0, 483000000.0, -1571000000.0, -1876000000.0, 2050000000.0, -1876000000.0, -4084000000.0, 1124000000.0, 386000000.0, 148000000.0, 1.0, 2167000000.0, 626000000.0, 4524000000.0, -2980000000.0, 1509000000.0, 11000000.0, 15147000000.0, -141000000.0, 845000000.0, 10.0, 31.0, 8.0, 72.0, 0.0, -9462000000.0, 0.0, 12977000000.0, 1419000000.0, 3412000000.0, 23510000000.0, 7072000000.0, 9011000000.0, -7987000000.0, 24891000000.0, 16904000000.0, 24855000000.0, -367000000.0, 2012.0, -5.6, 335000000.0),\n",
       " ('AAL', datetime.date(2013, 12, 31), 4975000000.0, -93000000.0, -2723000000.0, 67.0, -3114000000.0, 10592000000.0, 75.0, 2175000000.0, 0.0, 5000000.0, 11019000000.0, 104.0, 0.0, 935000000.0, 1020000000.0, -1324000000.0, -2180000000.0, 0.0, 0.0, 19259000000.0, 4086000000.0, 59.0, 15724000000.0, -346000000.0, 2311000000.0, 856000000.0, 1012000000.0, -1181000000.0, -235000000.0, 15353000000.0, 0.0, 0.0, 0.0, 2208000000.0, 660000000.0, 675000000.0, 3799000000.0, -3814000000.0, -1834000000.0, 1873000000.0, -1834000000.0, -4489000000.0, 1560000000.0, 559000000.0, 1399000000.0, 5.0, 2299000000.0, 1465000000.0, 7385000000.0, -2032000000.0, 1711000000.0, 481000000.0, 14915000000.0, -56000000.0, 853000000.0, 8.0, 80.0, 7.0, 96.0, 0.0, -11296000000.0, 0.0, 12913000000.0, 1446000000.0, 8111000000.0, 42278000000.0, 14323000000.0, 13806000000.0, -2731000000.0, 45009000000.0, 42278000000.0, 26743000000.0, 0.0, 2013.0, -11.25, 163022222.2),\n",
       " ('AAL', datetime.date(2014, 12, 31), 4668000000.0, -160000000.0, -150000000.0, 143.0, -5311000000.0, 15135000000.0, 60.0, 1768000000.0, 0.0, 7000000.0, 15620000000.0, 88.0, 0.0, 829000000.0, 1342000000.0, 4099000000.0, 3212000000.0, 0.0, 0.0, 23084000000.0, 4091000000.0, 63.0, 27030000000.0, 330000000.0, 2240000000.0, 887000000.0, 1004000000.0, 1799000000.0, -1026000000.0, 16043000000.0, 0.0, 0.0, 0.0, 170000000.0, -146000000.0, 3080000000.0, -315000000.0, -2911000000.0, 2882000000.0, 542000000.0, 2882000000.0, 2882000000.0, 1771000000.0, 800000000.0, 4249000000.0, 10.0, 2060000000.0, 898000000.0, 7059000000.0, -4559000000.0, 817000000.0, 601000000.0, 10928000000.0, -500000000.0, 1295000000.0, 8.0, 159.0, 7.0, 80.0, 0.0, -8562000000.0, -1052000000.0, 20686000000.0, 1677000000.0, 6309000000.0, 43225000000.0, 11750000000.0, 13404000000.0, 2021000000.0, 41204000000.0, 43225000000.0, 42650000000.0, 0.0, 2014.0, 4.02, 716915422.9),\n",
       " ('AAL', datetime.date(2015, 12, 31), 5102000000.0, 352000000.0, -708000000.0, 135.0, -6151000000.0, 11591000000.0, 51.0, 1085000000.0, 0.0, 6000000.0, 11096000000.0, 73.0, 2477000000.0, 667000000.0, 1487000000.0, 5496000000.0, 4616000000.0, 0.0, 0.0, 27510000000.0, 4091000000.0, 73.0, 29894000000.0, -2994000000.0, 2249000000.0, 880000000.0, 863000000.0, 443000000.0, -633000000.0, 18330000000.0, 0.0, 0.0, 0.0, 2856000000.0, -604000000.0, 6249000000.0, -1259000000.0, -5594000000.0, 7610000000.0, -2662000000.0, 7610000000.0, 7610000000.0, 1425000000.0, 1051000000.0, 6204000000.0, 15.0, 2103000000.0, 748000000.0, 6272000000.0, -4732000000.0, 96000000.0, 114000000.0, 10178000000.0, 95000000.0, 1364000000.0, 11.0, 82.0, 19.0, 67.0, 0.0, -1230000000.0, -3846000000.0, 21275000000.0, 2231000000.0, 5864000000.0, 48415000000.0, 9985000000.0, 13605000000.0, 5635000000.0, 42780000000.0, 48415000000.0, 40990000000.0, 0.0, 2015.0, 11.39, 668129938.5),\n",
       " ('AAP', datetime.date(2012, 12, 29), 2409453000.0, -89482000.0, 600000.0, 32.0, -271182000.0, 520215000.0, 23.0, 598111000.0, -260298000.0, 7000.0, 3106967000.0, 124.0, 0.0, 0.0, 189544000.0, 657915000.0, 624074000.0, 0.0, 0.0, 1292547000.0, 76389000.0, 50.0, 3098036000.0, 236404000.0, 28845000.0, 33841000.0, 2308609000.0, 0.0, 426323000.0, 604461000.0, 0.0, 0.0, 0.0, 177445000.0, 540210000.0, 685281000.0, 127907000.0, -272978000.0, 387670000.0, 23311000.0, 387670000.0, 387670000.0, 229866000.0, 0.0, 657315000.0, 11.0, 31833000.0, 47614000.0, 149558000.0, 2667000.0, -33499000.0, -1796000.0, 239021000.0, 8213000.0, 0.0, 10.0, 52.0, 6.0, 34.0, 0.0, 714900000.0, -18600000.0, 2440721000.0, 627000.0, 0.0, 4613814000.0, 3184200000.0, 2559638000.0, 1210694000.0, 3403120000.0, 4613814000.0, 6205003000.0, -27095000.0, 2012.0, 5.29, 73283553.88)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM financial_statements LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the number of records and period in financial statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(Ticker_Symbol)</th>\n",
       "        <th>MIN(period_ending)</th>\n",
       "        <th>MAX(period_ending)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1776</td>\n",
       "        <td>2012-03-03</td>\n",
       "        <td>2016-12-31</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1776, datetime.date(2012, 3, 3), datetime.date(2016, 12, 31))]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(Ticker_Symbol),MIN(period_ending), MAX(period_ending) FROM financial_statements;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Propose Questions and Answears\n",
    "<br>\n",
    "<br>\n",
    "This section is mainly about two topics. The first part is value investing, and the second part is Dupont Analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part I   Value Investing<br>\n",
    "We start our research  with checking the database to see whether it meet our anticipation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q1.1 What are the top 10 companies which had the largest net assets at the end of 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>Company_name</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>Net_assests</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INTC</td>\n",
       "        <td>Intel Corp.</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>65899000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FB</td>\n",
       "        <td>Facebook</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>59194000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GM</td>\n",
       "        <td>General Motors</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>44000000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHTR</td>\n",
       "        <td>Charter Communications</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>40000000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UNH</td>\n",
       "        <td>United Health Group Inc.</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>38464000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CVS</td>\n",
       "        <td>CVS Health</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>36830000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KMI</td>\n",
       "        <td>Kinder Morgan</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>34431000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AMGN</td>\n",
       "        <td>Amgen Inc</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>29875000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F</td>\n",
       "        <td>Ford Motor</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>29000000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UTX</td>\n",
       "        <td>United Technologies</td>\n",
       "        <td>2016-12-31</td>\n",
       "        <td>27579000000.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('INTC', 'Intel Corp.', datetime.date(2016, 12, 31), 65899000000.0),\n",
       " ('FB', 'Facebook', datetime.date(2016, 12, 31), 59194000000.0),\n",
       " ('GM', 'General Motors', datetime.date(2016, 12, 31), 44000000000.0),\n",
       " ('CHTR', 'Charter Communications', datetime.date(2016, 12, 31), 40000000000.0),\n",
       " ('UNH', 'United Health Group Inc.', datetime.date(2016, 12, 31), 38464000000.0),\n",
       " ('CVS', 'CVS Health', datetime.date(2016, 12, 31), 36830000000.0),\n",
       " ('KMI', 'Kinder Morgan', datetime.date(2016, 12, 31), 34431000000.0),\n",
       " ('AMGN', 'Amgen Inc', datetime.date(2016, 12, 31), 29875000000.0),\n",
       " ('F', 'Ford Motor', datetime.date(2016, 12, 31), 29000000000.0),\n",
       " ('UTX', 'United Technologies', datetime.date(2016, 12, 31), 27579000000.0)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT fs.Ticker_Symbol,s.Company_name,fs.Period_Ending,(fs.Total_Assets-fs.Total_Liabilities) AS Net_assests \n",
    "FROM financial_statements AS fs\n",
    "INNER JOIN securities AS s\n",
    "ON fs.Ticker_Symbol=s.Ticker_Symbol\n",
    "WHERE Period_Ending LIKE \"2016-12%\"\n",
    "ORDER BY Net_assests DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.1 Interpretation<br>\n",
    "The net assets represent how much a company is worth on its book value. The top 10 companies with the largest net assets should be the big names and also have good operating performance. The output meets our anticipation. At the end of 2016, the top 10 largest companies include Intel, Facebook, and CVS, which are all very familiar to us. The company which had the largest net assets is Intel. Its net assets at the end of 2016 is $65,899,000,000.00 ."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q1.2 What are the top 10 companies that had the highest market values at the end of 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>symbol</th>\n",
       "        <th>company_name</th>\n",
       "        <th>date</th>\n",
       "        <th>market_value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAPL</td>\n",
       "        <td>Apple Inc.</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>633708781565.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MSFT</td>\n",
       "        <td>Microsoft Corp.</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>492371561222.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WMT</td>\n",
       "        <td>Wal-Mart Stores</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>221757493512.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DE</td>\n",
       "        <td>Deere &amp; Co.</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>172552370803.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HD</td>\n",
       "        <td>Home Depot</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>171177911316.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DIS</td>\n",
       "        <td>The Walt Disney Company</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>169918408193.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CSCO</td>\n",
       "        <td>Cisco Systems</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>152362710166.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>QCOM</td>\n",
       "        <td>QUALCOMM Inc.</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>96866141277.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NKE</td>\n",
       "        <td>Nike</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>86480003112.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SBUX</td>\n",
       "        <td>Starbucks Corp.</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>81905081298.35</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AAPL', 'Apple Inc.', datetime.date(2016, 12, 30), 633708781565.15),\n",
       " ('MSFT', 'Microsoft Corp.', datetime.date(2016, 12, 30), 492371561222.67),\n",
       " ('WMT', 'Wal-Mart Stores', datetime.date(2016, 12, 30), 221757493512.01),\n",
       " ('DE', 'Deere & Co.', datetime.date(2016, 12, 30), 172552370803.56),\n",
       " ('HD', 'Home Depot', datetime.date(2016, 12, 30), 171177911316.24),\n",
       " ('DIS', 'The Walt Disney Company', datetime.date(2016, 12, 30), 169918408193.89),\n",
       " ('CSCO', 'Cisco Systems', datetime.date(2016, 12, 30), 152362710166.44),\n",
       " ('QCOM', 'QUALCOMM Inc.', datetime.date(2016, 12, 30), 96866141277.67),\n",
       " ('NKE', 'Nike', datetime.date(2016, 12, 30), 86480003112.06),\n",
       " ('SBUX', 'Starbucks Corp.', datetime.date(2016, 12, 30), 81905081298.35)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT p.symbol,s.company_name,p.date,ROUND(p.close*fs.Estimated_Shares_Outstanding,2) AS market_value\n",
    "FROM securities AS s \n",
    "RIGHT JOIN financial_statements as fs ON s.ticker_symbol=fs.ticker_Symbol\n",
    "RIGHT JOIN prices as p ON s.Ticker_symbol=p.symbol\n",
    "WHERE p.date = \"2016-12-30\" AND fs.Period_Ending LIKE \"2016%\"\n",
    "ORDER BY market_value DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.2 Interpretation<br>\n",
    "Market value is used to refer to the market capitalization of a publicly-traded company. Market value can be obtained by multiplying the number of its outstanding shares by the current share price. The output of Q1.2 denotes that the top 10 companies which had the highest market value at the end of 2016 are Apple Inc., Microsoft Corp., Wal-Mart Stores, Deere & Co., Home Depot, The Walt Disney Company, Cisco Systems, QUALCOMM Inc., Nike, and Starbucks Corp. This means they are valued the highest on the open market.\n",
    "<br><br>Here a problem comes out. The outputs of Q1.1 and Q1.2 have different companies. This means that there is a difference between how much the investors value a company and how much a company's book value is. Our objective is to find out the companies that are undervalued. Therefore, we keep exploring."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Q1.3 Which companies had the largest price-to-book ratio at the end of their fiscal year in 2016?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP VIEW IF EXISTS MSQR;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE VIEW MSQR AS(\n",
    "SELECT\n",
    "s.ticker_symbol,s.company_name,s.GICS_sector,s.State,fs.Period_Ending,\n",
    "ROUND(p.close*fs.Estimated_Shares_Outstanding,2) AS market_value,\n",
    "(fs.Total_Assets-fs.Total_Liabilities) AS Net_assests,\n",
    "ROUND((p.close*fs.Estimated_Shares_Outstanding)/(fs.Total_Assets-fs.Total_Liabilities),2) AS PB_ratio\n",
    "FROM securities AS s \n",
    "RIGHT JOIN financial_statements as fs ON s.ticker_symbol=fs.ticker_Symbol\n",
    "RIGHT JOIN prices as p ON s.Ticker_symbol=p.symbol\n",
    "WHERE fs.Period_Ending LIKE \"2016%\" \n",
    "AND (p.date=fs.Period_Ending) \n",
    "AND fs.Estimated_Shares_Outstanding !=0\n",
    "AND (fs.Total_assets-fs.total_liabilities)>0\n",
    "ORDER BY PB_ratio ASC\n",
    "LIMIT 50);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "46 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ticker_symbol</th>\n",
       "        <th>company_name</th>\n",
       "        <th>GICS_sector</th>\n",
       "        <th>State</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>market_value</th>\n",
       "        <th>Net_assests</th>\n",
       "        <th>PB_ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NWSA</td>\n",
       "        <td>News Corp. Class A</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> New York</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>6772166894.66</td>\n",
       "        <td>11584000000.0</td>\n",
       "        <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WDC</td>\n",
       "        <td>Western Digital</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-07-01</td>\n",
       "        <td>11134396332.27</td>\n",
       "        <td>11145000000.0</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HPE</td>\n",
       "        <td>Hewlett Packard Enterprise</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-10-31</td>\n",
       "        <td>38601993394.4</td>\n",
       "        <td>31448000000.0</td>\n",
       "        <td>1.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WRK</td>\n",
       "        <td>WestRock Company</td>\n",
       "        <td>Materials</td>\n",
       "        <td> Virginia</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>12116892191.54</td>\n",
       "        <td>9728800000.0</td>\n",
       "        <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MU</td>\n",
       "        <td>Micron Technology</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Idaho</td>\n",
       "        <td>2016-09-01</td>\n",
       "        <td>17009777150.17</td>\n",
       "        <td>12080000000.0</td>\n",
       "        <td>1.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JEC</td>\n",
       "        <td>Jacobs Engineering Group</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>6220083779.62</td>\n",
       "        <td>4265276000.0</td>\n",
       "        <td>1.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HP</td>\n",
       "        <td>Helmerich &amp; Payne</td>\n",
       "        <td>Energy</td>\n",
       "        <td> Oklahoma[5]</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>7082452911.26</td>\n",
       "        <td>4560925000.0</td>\n",
       "        <td>1.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DHI</td>\n",
       "        <td>D. R. Horton</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> Texas</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>11199272249.95</td>\n",
       "        <td>6792500000.0</td>\n",
       "        <td>1.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NTAP</td>\n",
       "        <td>NetApp</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-04-29</td>\n",
       "        <td>6940461359.51</td>\n",
       "        <td>2881000000.0</td>\n",
       "        <td>2.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TEL</td>\n",
       "        <td>TE Connectivity Ltd.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Switzerland</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>23559091890.02</td>\n",
       "        <td>8485000000.0</td>\n",
       "        <td>2.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COO</td>\n",
       "        <td>The Cooper Companies</td>\n",
       "        <td>Health Care</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-10-31</td>\n",
       "        <td>8534574662.86</td>\n",
       "        <td>2699867000.0</td>\n",
       "        <td>3.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PH</td>\n",
       "        <td>Parker-Hannifin</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Ohio</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>14627359806.28</td>\n",
       "        <td>4575255000.0</td>\n",
       "        <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KMX</td>\n",
       "        <td>Carmax Inc</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> Virginia</td>\n",
       "        <td>2016-02-29</td>\n",
       "        <td>9394064572.5</td>\n",
       "        <td>2904786000.0</td>\n",
       "        <td>3.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FDX</td>\n",
       "        <td>FedEx Corporation</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Tennessee</td>\n",
       "        <td>2016-05-31</td>\n",
       "        <td>45560759065.9</td>\n",
       "        <td>13784000000.0</td>\n",
       "        <td>3.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HRS</td>\n",
       "        <td>Harris Corporation</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Florida</td>\n",
       "        <td>2016-07-01</td>\n",
       "        <td>10252551266.68</td>\n",
       "        <td>3057000000.0</td>\n",
       "        <td>3.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SYMC</td>\n",
       "        <td>Symantec Corp.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-04-01</td>\n",
       "        <td>12346113105.37</td>\n",
       "        <td>3676000000.0</td>\n",
       "        <td>3.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>VIAB</td>\n",
       "        <td>Viacom Inc.</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> New York</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>15093057247.2</td>\n",
       "        <td>4277000000.0</td>\n",
       "        <td>3.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GPN</td>\n",
       "        <td>Global Payments Inc</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Georgia</td>\n",
       "        <td>2016-05-31</td>\n",
       "        <td>10295479123.54</td>\n",
       "        <td>2746476000.0</td>\n",
       "        <td>3.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CAH</td>\n",
       "        <td>Cardinal Health Inc.</td>\n",
       "        <td>Health Care</td>\n",
       "        <td> Ohio</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>25532172718.95</td>\n",
       "        <td>6554000000.0</td>\n",
       "        <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MCK</td>\n",
       "        <td>McKesson Corp.</td>\n",
       "        <td>Health Care</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-03-31</td>\n",
       "        <td>36157892056.45</td>\n",
       "        <td>8924000000.0</td>\n",
       "        <td>4.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SWKS</td>\n",
       "        <td>Skyworks Solutions</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Massachusetts</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>14378468192.6</td>\n",
       "        <td>3541400000.0</td>\n",
       "        <td>4.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DG</td>\n",
       "        <td>Dollar General</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> Tennessee</td>\n",
       "        <td>2016-01-29</td>\n",
       "        <td>22083561098.98</td>\n",
       "        <td>5377876000.0</td>\n",
       "        <td>4.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>APD</td>\n",
       "        <td>Air Products &amp; Chemicals Inc</td>\n",
       "        <td>Materials</td>\n",
       "        <td> Pennsylvania</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>30058284952.16</td>\n",
       "        <td>7079600000.0</td>\n",
       "        <td>4.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STX</td>\n",
       "        <td>Seagate Technology</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Ireland</td>\n",
       "        <td>2016-07-01</td>\n",
       "        <td>7180048397.39</td>\n",
       "        <td>1593000000.0</td>\n",
       "        <td>4.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MCHP</td>\n",
       "        <td>Microchip Technology</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Arizona</td>\n",
       "        <td>2016-03-31</td>\n",
       "        <td>9825888458.69</td>\n",
       "        <td>2150919000.0</td>\n",
       "        <td>4.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EMR</td>\n",
       "        <td>Emerson Electric Company</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Missouri</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>35851635260.71</td>\n",
       "        <td>7568000000.0</td>\n",
       "        <td>4.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BDX</td>\n",
       "        <td>Becton Dickinson</td>\n",
       "        <td>Health Care</td>\n",
       "        <td> New Jersey</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>38217097134.81</td>\n",
       "        <td>7633000000.0</td>\n",
       "        <td>5.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>COL</td>\n",
       "        <td>Rockwell Collins</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Iowa</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>11003497728.35</td>\n",
       "        <td>2078000000.0</td>\n",
       "        <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>VAR</td>\n",
       "        <td>Varian Medical Systems</td>\n",
       "        <td>Health Care</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>9488369314.46</td>\n",
       "        <td>1740500000.0</td>\n",
       "        <td>5.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MSFT</td>\n",
       "        <td>Microsoft Corp.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Washington</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>405449825131.5</td>\n",
       "        <td>72000000000.0</td>\n",
       "        <td>5.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CTAS</td>\n",
       "        <td>Cintas Corporation</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Ohio</td>\n",
       "        <td>2016-05-31</td>\n",
       "        <td>10435825099.51</td>\n",
       "        <td>1842659000.0</td>\n",
       "        <td>5.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EA</td>\n",
       "        <td>Electronic Arts</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-03-31</td>\n",
       "        <td>20488783027.97</td>\n",
       "        <td>3396000000.0</td>\n",
       "        <td>6.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FFIV</td>\n",
       "        <td>F5 Networks</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Washington</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>8397820806.27</td>\n",
       "        <td>1185262000.0</td>\n",
       "        <td>7.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AYI</td>\n",
       "        <td>Acuity Brands Inc</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Georgia</td>\n",
       "        <td>2016-08-31</td>\n",
       "        <td>11994736818.73</td>\n",
       "        <td>1659800000.0</td>\n",
       "        <td>7.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NKE</td>\n",
       "        <td>Nike</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> Oregon</td>\n",
       "        <td>2016-05-31</td>\n",
       "        <td>93948961349.37</td>\n",
       "        <td>12258000000.0</td>\n",
       "        <td>7.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROK</td>\n",
       "        <td>Rockwell Automation Inc.</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Wisconsin</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>15941338447.89</td>\n",
       "        <td>1990100000.0</td>\n",
       "        <td>8.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ABC</td>\n",
       "        <td>AmerisourceBergen Corp</td>\n",
       "        <td>Health Care</td>\n",
       "        <td> Pennsylvania</td>\n",
       "        <td>2016-09-30</td>\n",
       "        <td>17139391218.92</td>\n",
       "        <td>2129404000.0</td>\n",
       "        <td>8.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOW</td>\n",
       "        <td>Lowe&#x27;s Cos.</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> North Carolina</td>\n",
       "        <td>2016-01-29</td>\n",
       "        <td>66830171913.4</td>\n",
       "        <td>7654000000.0</td>\n",
       "        <td>8.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RHT</td>\n",
       "        <td>Red Hat Inc.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> North Carolina</td>\n",
       "        <td>2016-02-29</td>\n",
       "        <td>11952754535.81</td>\n",
       "        <td>1334432000.0</td>\n",
       "        <td>8.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EL</td>\n",
       "        <td>Estee Lauder Cos.</td>\n",
       "        <td>Consumer Staples</td>\n",
       "        <td> New York</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>33704614037.3</td>\n",
       "        <td>3587300000.0</td>\n",
       "        <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PAYX</td>\n",
       "        <td>Paychex Inc.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> New York</td>\n",
       "        <td>2016-05-31</td>\n",
       "        <td>19539855679.05</td>\n",
       "        <td>1911700000.0</td>\n",
       "        <td>10.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MON</td>\n",
       "        <td>Monsanto Co.</td>\n",
       "        <td>Materials</td>\n",
       "        <td> Missouri</td>\n",
       "        <td>2016-08-31</td>\n",
       "        <td>47113907289.0</td>\n",
       "        <td>4534000000.0</td>\n",
       "        <td>10.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KLAC</td>\n",
       "        <td>KLA-Tencor Corp.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>11415688383.4</td>\n",
       "        <td>689114000.0</td>\n",
       "        <td>16.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DE</td>\n",
       "        <td>Deere &amp; Co.</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> Illinois</td>\n",
       "        <td>2016-10-31</td>\n",
       "        <td>147868543606.02</td>\n",
       "        <td>6520000000.0</td>\n",
       "        <td>22.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CLX</td>\n",
       "        <td>The Clorox Company</td>\n",
       "        <td>Consumer Staples</td>\n",
       "        <td> California</td>\n",
       "        <td>2016-06-30</td>\n",
       "        <td>17899544836.04</td>\n",
       "        <td>297000000.0</td>\n",
       "        <td>60.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CSRA</td>\n",
       "        <td>CSRA Inc.</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> Virginia</td>\n",
       "        <td>2016-04-01</td>\n",
       "        <td>5197561087.35</td>\n",
       "        <td>64546000.0</td>\n",
       "        <td>80.52</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NWSA', 'News Corp. Class A', 'Consumer Discretionary', ' New York', datetime.date(2016, 6, 30), 6772166894.66, 11584000000.0, 0.58),\n",
       " ('WDC', 'Western Digital', 'Information Technology', ' California', datetime.date(2016, 7, 1), 11134396332.27, 11145000000.0, 1.0),\n",
       " ('HPE', 'Hewlett Packard Enterprise', 'Information Technology', ' California', datetime.date(2016, 10, 31), 38601993394.4, 31448000000.0, 1.23),\n",
       " ('WRK', 'WestRock Company', 'Materials', ' Virginia', datetime.date(2016, 9, 30), 12116892191.54, 9728800000.0, 1.25),\n",
       " ('MU', 'Micron Technology', 'Information Technology', ' Idaho', datetime.date(2016, 9, 1), 17009777150.17, 12080000000.0, 1.41),\n",
       " ('JEC', 'Jacobs Engineering Group', 'Industrials', ' California', datetime.date(2016, 9, 30), 6220083779.62, 4265276000.0, 1.46),\n",
       " ('HP', 'Helmerich & Payne', 'Energy', ' Oklahoma[5]', datetime.date(2016, 9, 30), 7082452911.26, 4560925000.0, 1.55),\n",
       " ('DHI', 'D. R. Horton', 'Consumer Discretionary', ' Texas', datetime.date(2016, 9, 30), 11199272249.95, 6792500000.0, 1.65),\n",
       " ('NTAP', 'NetApp', 'Information Technology', ' California', datetime.date(2016, 4, 29), 6940461359.51, 2881000000.0, 2.41),\n",
       " ('TEL', 'TE Connectivity Ltd.', 'Information Technology', ' Switzerland', datetime.date(2016, 9, 30), 23559091890.02, 8485000000.0, 2.78),\n",
       " ('COO', 'The Cooper Companies', 'Health Care', ' California', datetime.date(2016, 10, 31), 8534574662.86, 2699867000.0, 3.16),\n",
       " ('PH', 'Parker-Hannifin', 'Industrials', ' Ohio', datetime.date(2016, 6, 30), 14627359806.28, 4575255000.0, 3.2),\n",
       " ('KMX', 'Carmax Inc', 'Consumer Discretionary', ' Virginia', datetime.date(2016, 2, 29), 9394064572.5, 2904786000.0, 3.23),\n",
       " ('FDX', 'FedEx Corporation', 'Industrials', ' Tennessee', datetime.date(2016, 5, 31), 45560759065.9, 13784000000.0, 3.31),\n",
       " ('HRS', 'Harris Corporation', 'Information Technology', ' Florida', datetime.date(2016, 7, 1), 10252551266.68, 3057000000.0, 3.35),\n",
       " ('SYMC', 'Symantec Corp.', 'Information Technology', ' California', datetime.date(2016, 4, 1), 12346113105.37, 3676000000.0, 3.36),\n",
       " ('VIAB', 'Viacom Inc.', 'Consumer Discretionary', ' New York', datetime.date(2016, 9, 30), 15093057247.2, 4277000000.0, 3.53),\n",
       " ('GPN', 'Global Payments Inc', 'Information Technology', ' Georgia', datetime.date(2016, 5, 31), 10295479123.54, 2746476000.0, 3.75),\n",
       " ('CAH', 'Cardinal Health Inc.', 'Health Care', ' Ohio', datetime.date(2016, 6, 30), 25532172718.95, 6554000000.0, 3.9),\n",
       " ('MCK', 'McKesson Corp.', 'Health Care', ' California', datetime.date(2016, 3, 31), 36157892056.45, 8924000000.0, 4.05),\n",
       " ('SWKS', 'Skyworks Solutions', 'Information Technology', ' Massachusetts', datetime.date(2016, 9, 30), 14378468192.6, 3541400000.0, 4.06),\n",
       " ('DG', 'Dollar General', 'Consumer Discretionary', ' Tennessee', datetime.date(2016, 1, 29), 22083561098.98, 5377876000.0, 4.11),\n",
       " ('APD', 'Air Products & Chemicals Inc', 'Materials', ' Pennsylvania', datetime.date(2016, 9, 30), 30058284952.16, 7079600000.0, 4.25),\n",
       " ('STX', 'Seagate Technology', 'Information Technology', ' Ireland', datetime.date(2016, 7, 1), 7180048397.39, 1593000000.0, 4.51),\n",
       " ('MCHP', 'Microchip Technology', 'Information Technology', ' Arizona', datetime.date(2016, 3, 31), 9825888458.69, 2150919000.0, 4.57),\n",
       " ('EMR', 'Emerson Electric Company', 'Industrials', ' Missouri', datetime.date(2016, 9, 30), 35851635260.71, 7568000000.0, 4.74),\n",
       " ('BDX', 'Becton Dickinson', 'Health Care', ' New Jersey', datetime.date(2016, 9, 30), 38217097134.81, 7633000000.0, 5.01),\n",
       " ('COL', 'Rockwell Collins', 'Industrials', ' Iowa', datetime.date(2016, 9, 30), 11003497728.35, 2078000000.0, 5.3),\n",
       " ('VAR', 'Varian Medical Systems', 'Health Care', ' California', datetime.date(2016, 9, 30), 9488369314.46, 1740500000.0, 5.45),\n",
       " ('MSFT', 'Microsoft Corp.', 'Information Technology', ' Washington', datetime.date(2016, 6, 30), 405449825131.5, 72000000000.0, 5.63),\n",
       " ('CTAS', 'Cintas Corporation', 'Industrials', ' Ohio', datetime.date(2016, 5, 31), 10435825099.51, 1842659000.0, 5.66),\n",
       " ('EA', 'Electronic Arts', 'Information Technology', ' California', datetime.date(2016, 3, 31), 20488783027.97, 3396000000.0, 6.03),\n",
       " ('FFIV', 'F5 Networks', 'Information Technology', ' Washington', datetime.date(2016, 9, 30), 8397820806.27, 1185262000.0, 7.09),\n",
       " ('AYI', 'Acuity Brands Inc', 'Industrials', ' Georgia', datetime.date(2016, 8, 31), 11994736818.73, 1659800000.0, 7.23),\n",
       " ('NKE', 'Nike', 'Consumer Discretionary', ' Oregon', datetime.date(2016, 5, 31), 93948961349.37, 12258000000.0, 7.66),\n",
       " ('ROK', 'Rockwell Automation Inc.', 'Industrials', ' Wisconsin', datetime.date(2016, 9, 30), 15941338447.89, 1990100000.0, 8.01),\n",
       " ('ABC', 'AmerisourceBergen Corp', 'Health Care', ' Pennsylvania', datetime.date(2016, 9, 30), 17139391218.92, 2129404000.0, 8.05),\n",
       " ('LOW', \"Lowe's Cos.\", 'Consumer Discretionary', ' North Carolina', datetime.date(2016, 1, 29), 66830171913.4, 7654000000.0, 8.73),\n",
       " ('RHT', 'Red Hat Inc.', 'Information Technology', ' North Carolina', datetime.date(2016, 2, 29), 11952754535.81, 1334432000.0, 8.96),\n",
       " ('EL', 'Estee Lauder Cos.', 'Consumer Staples', ' New York', datetime.date(2016, 6, 30), 33704614037.3, 3587300000.0, 9.4),\n",
       " ('PAYX', 'Paychex Inc.', 'Information Technology', ' New York', datetime.date(2016, 5, 31), 19539855679.05, 1911700000.0, 10.22),\n",
       " ('MON', 'Monsanto Co.', 'Materials', ' Missouri', datetime.date(2016, 8, 31), 47113907289.0, 4534000000.0, 10.39),\n",
       " ('KLAC', 'KLA-Tencor Corp.', 'Information Technology', ' California', datetime.date(2016, 6, 30), 11415688383.4, 689114000.0, 16.57),\n",
       " ('DE', 'Deere & Co.', 'Industrials', ' Illinois', datetime.date(2016, 10, 31), 147868543606.02, 6520000000.0, 22.68),\n",
       " ('CLX', 'The Clorox Company', 'Consumer Staples', ' California', datetime.date(2016, 6, 30), 17899544836.04, 297000000.0, 60.27),\n",
       " ('CSRA', 'CSRA Inc.', 'Information Technology', ' Virginia', datetime.date(2016, 4, 1), 5197561087.35, 64546000.0, 80.52)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM MSQR;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.3 Interpretation<br>\n",
    "We used the price-to-book ratio to compare a firm's market to book value by dividing price per share by book value per share. If a market value is equal to its book value, then the P/B ratio should be one. A lower P/B ratio could mean the stock is undervalued. The output shows the companies that are most undervalued. The period ending means the date that a company issued its latest annual financial statements. In the where clause, we set the constraint that the estimated outstanding shares are not zero because we found that the companies which showed on the outputs of top companies when we first wrote the query without that constraint were all zero. This may be caused by missing values of the raw data or because the company just came to the market so that it's hard to estimate the outstanding shares. Another constraint we should pay attention to is that total assets minus total liabilities greater than zero. This constraint is necessary because the lowest PB_ratio would be a negative value if we don't set this constraint, which is not what we expected."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q1.4 Which companies had the lowest price-earnings ratio while also had the good price-book ratio at the end of 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ticker_symbol</th>\n",
       "        <th>company_name</th>\n",
       "        <th>GICS_sector</th>\n",
       "        <th>State</th>\n",
       "        <th>Market_value_per_share</th>\n",
       "        <th>Earnings_Per_Share</th>\n",
       "        <th>PE_ratio</th>\n",
       "        <th>PB_ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HPE</td>\n",
       "        <td>Hewlett Packard Enterprise</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>22.47</td>\n",
       "        <td>1.84</td>\n",
       "        <td>12.21</td>\n",
       "        <td>1.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DHI</td>\n",
       "        <td>D. R. Horton</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> Texas</td>\n",
       "        <td>30.2</td>\n",
       "        <td>2.39</td>\n",
       "        <td>12.64</td>\n",
       "        <td>1.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JEC</td>\n",
       "        <td>Jacobs Engineering Group</td>\n",
       "        <td>Industrials</td>\n",
       "        <td> California</td>\n",
       "        <td>51.72</td>\n",
       "        <td>1.75</td>\n",
       "        <td>29.55</td>\n",
       "        <td>1.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NWSA</td>\n",
       "        <td>News Corp. Class A</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td> New York</td>\n",
       "        <td>11.35</td>\n",
       "        <td>0.3</td>\n",
       "        <td>37.83</td>\n",
       "        <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WDC</td>\n",
       "        <td>Western Digital</td>\n",
       "        <td>Information Technology</td>\n",
       "        <td> California</td>\n",
       "        <td>46.47</td>\n",
       "        <td>1.01</td>\n",
       "        <td>46.01</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HPE', 'Hewlett Packard Enterprise', 'Information Technology', ' California', 22.47, 1.84, 12.21, 1.23),\n",
       " ('DHI', 'D. R. Horton', 'Consumer Discretionary', ' Texas', 30.2, 2.39, 12.64, 1.65),\n",
       " ('JEC', 'Jacobs Engineering Group', 'Industrials', ' California', 51.72, 1.75, 29.55, 1.46),\n",
       " ('NWSA', 'News Corp. Class A', 'Consumer Discretionary', ' New York', 11.35, 0.3, 37.83, 0.58),\n",
       " ('WDC', 'Western Digital', 'Information Technology', ' California', 46.47, 1.01, 46.01, 1.0)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "MSQR.ticker_symbol,MSQR.company_name,MSQR.GICS_sector,MSQR.State,\n",
    "ROUND(MSQR.market_value/fs.Estimated_Shares_Outstanding,2) AS Market_value_per_share,fs.Earnings_Per_Share,\n",
    "ROUND((MSQR.market_value/fs.Estimated_Shares_Outstanding)/fs.Earnings_Per_Share,2) AS PE_ratio,\n",
    "MSQR.PB_ratio\n",
    "FROM MSQR\n",
    "LEFT JOIN financial_statements AS fs ON MSQR.ticker_symbol=fs.ticker_symbol\n",
    "WHERE fs.Period_Ending LIKE \"2016%\"\n",
    "AND fs.Estimated_Shares_Outstanding !=0\n",
    "AND fs.Earnings_Per_Share >0\n",
    "AND MSQR.PB_ratio<=2\n",
    "ORDER BY PE_ratio;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "1.4 Interpretation<br>\n",
    "The price-earnings ratio (P/E ratio) is the ratio for valuing a company that measures its current share price relative to its per-share earnings. In essence, the price-earnings ratio indicates the dollar amount an investor can expect to invest in a company in order to receive one dollar of that company’s earnings. Typically, if the companies pay dividends at the same level, then for the investors, a lower P/E ratio is better. Therefore, we calculated the P/E ratio to find the potentially undervalued stocks. \n",
    "<br>The outputs just have five companies because we set the constraint \"PB ratio no more than 2\" in the where clause. This constraint came from the methodology of Benjamin Graham, the father of value investing, which is called the margin of safety. The margin of safety is the principle of buying a security at a significant discount to its intrinsic value, which is thought to not only provide high-return opportunities, but also to minimize the downside risk of an investment. Usually, the P/B ratio varies in different sectors, but a P/B ratio no more than two is a safe number that allows us to buy an equity at a big enough discount to leave some room for error in the estimation of value.\n",
    "<br>Similar to Q1.3, we set another two constraints \"outstanding estimated shares!=0\" and \"earnings per share>0\" because with these two constraints the lowest P/E ratio will be negative, which will cause us to make the opposite conclusion.\n",
    "<br>There is no exact rule for what is a good P/E ratio, but in practice, the average P/E ratio in the whole market is usually between 20 to 25, for a healthy market. Therefore, we also applied the methodology of the margin of safety to our selection standard. From the output, we can see only two companies, Hewlett Packard Enterprise and D. R. Horton have P/E ratio no more than 20. Therefore, the final decision about which security we should buy is to buy HPE and DHI."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "Q1.5 Assume we had invested the two securities we screened, HPE and DHI, at the earliest. What is the ratio of return on our capital if we hold these two stocks at the end of 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS HPE_earlier;\n",
    "DROP VIEW IF EXISTS HPE_later;\n",
    "DROP VIEW IF EXISTS DHI_earlier;\n",
    "DROP VIEW IF EXISTS DHI_later;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE VIEW HPE_earlier AS\n",
    "(SELECT symbol,date AS earlier_date,close AS earlier_close FROM prices\n",
    "WHERE (symbol,date) IN\n",
    "(SELECT symbol,MIN(date) FROM prices WHERE symbol= 'HPE'));\n",
    "\n",
    "CREATE VIEW HPE_later AS\n",
    "(SELECT symbol,date AS later_date,close AS later_close FROM prices\n",
    "WHERE (symbol,date) IN\n",
    "(SELECT symbol,MAX(date) FROM prices WHERE symbol= 'HPE'));\n",
    "\n",
    "CREATE VIEW DHI_earlier AS\n",
    "(SELECT symbol,date AS earlier_date,close AS earlier_close FROM prices\n",
    "WHERE (symbol,date) IN\n",
    "(SELECT symbol,MIN(date) FROM prices WHERE symbol= 'DHI'));\n",
    "\n",
    "CREATE VIEW DHI_later AS\n",
    "(SELECT symbol,date AS later_date,close AS later_close FROM prices\n",
    "WHERE (symbol,date) IN\n",
    "(SELECT symbol,MAX(date) FROM prices WHERE symbol= 'DHI'));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>symbol</th>\n",
       "        <th>earlier_date</th>\n",
       "        <th>earlier_close</th>\n",
       "        <th>later_date</th>\n",
       "        <th>later_close</th>\n",
       "        <th>dif_close</th>\n",
       "        <th>dif_date</th>\n",
       "        <th>annual_rate_of_increase</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HPE</td>\n",
       "        <td>2015-10-19</td>\n",
       "        <td>16.55</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>23.14</td>\n",
       "        <td>6.59</td>\n",
       "        <td>438</td>\n",
       "        <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DHI</td>\n",
       "        <td>2010-01-04</td>\n",
       "        <td>11.16</td>\n",
       "        <td>2016-12-30</td>\n",
       "        <td>27.33</td>\n",
       "        <td>16.17</td>\n",
       "        <td>2552</td>\n",
       "        <td>0.21</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HPE', datetime.date(2015, 10, 19), 16.55, datetime.date(2016, 12, 30), 23.14, 6.59, 438, 0.33),\n",
       " ('DHI', datetime.date(2010, 1, 4), 11.16, datetime.date(2016, 12, 30), 27.33, 16.17, 2552, 0.21)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT symbol,earlier_date,earlier_close,later_date,later_close, \n",
    "ROUND((later_close-earlier_close),2) AS dif_close, DATEDIFF(later_date,earlier_date) AS dif_date,\n",
    "ROUND(((later_close-earlier_close)/earlier_close)/DATEDIFF(later_date,earlier_date)*365,2) AS annual_rate_of_increase FROM\n",
    "    (\n",
    "        (SELECT HPE_earlier.symbol,HPE_earlier.earlier_date,HPE_earlier.earlier_close,\n",
    "         HPE_later.later_date,HPE_later.later_close FROM HPE_earlier\n",
    "        INNER JOIN HPE_later\n",
    "        ON HPE_earlier.symbol=HPE_later.symbol)\n",
    "        UNION\n",
    "        (SELECT DHI_earlier.symbol,DHI_earlier.earlier_date,DHI_earlier.earlier_close,\n",
    "         DHI_later.later_date,DHI_later.later_close FROM DHI_earlier\n",
    "        INNER JOIN DHI_later\n",
    "        ON DHI_earlier.symbol=DHI_later.symbol)\n",
    "    ) AS HPE_JEC\n",
    "ORDER BY annual_rate_of_increase DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "1.5 Interpretation<br><br>\n",
    "The return on capital is equal to the profit we gained divided by our invested capital. In order to simplify the calculation, we assume the stocks have no dividends. Then, the profit we gained during our holding period is mainly from the fluctuation of stock price, the latest close price minus the price when we invested it.<br>\n",
    "<br>In the output, the earlier date is the earliest date that we could invest in a stock, and the later date is the latest date we hold that stock, the end of 2016. The earlier date of HPE is not same as DHI, but just Oct 19th, 2015 because the HPE company had become a listed company until 2015. The earlier_close means the original price when we bought a stock, and the later_close is the close price at the end of 2016. The dif_close means the difference between the latest close price and the original price. Since we hold different stocks for different periods, is not comparable for the return of each stock, so we use the ratio of return instead of the absolute return value. The dif_date means how long we hold a stock. Then, the ratio of return of each stock could be calculated by dividing the price appreciation by the holding period. The result is the daily ratio of return. After that, we multiply this rate by 365 and get the annual rate of return.<br>\n",
    "<br>From the output, we can see that the annual rate of return for HPE is 33%, and the rate of 21% for DHI. These two numbers are both very high comparing to the rate of return on the whole market, which proved that we made a good choice."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part II Analyzing S&P 500 in the view of sectors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Q2.1 How many sectors covered in S&P 500? "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "Check if missing records existed in sector column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n",
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(Ticker_symbol)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0,)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(Ticker_symbol) FROM securities WHERE GICS_Sector =\"\";\n",
    "%sql SELECT COUNT(Ticker_symbol) FROM securities WHERE GICS_Sector IS NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "distinctly count sectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>GICS_Sector</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Industrials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consumer Discretionary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Information Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consumer Staples</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Utilities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Financials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Real Estate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Materials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Telecommunications Services</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Health Care',),\n",
       " ('Industrials',),\n",
       " ('Consumer Discretionary',),\n",
       " ('Information Technology',),\n",
       " ('Consumer Staples',),\n",
       " ('Utilities',),\n",
       " ('Financials',),\n",
       " ('Real Estate',),\n",
       " ('Materials',),\n",
       " ('Energy',),\n",
       " ('Telecommunications Services',)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT GICS_Sector FROM securities;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "2.1 Interpretation:\n",
    "<br><br>\n",
    "From the output above, 11 sectors covered in S&P 500.<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Q2.2 Background \n",
    "\n",
    "The Dupont analysis also called the Dupont model is a financial ratio based on the return on equity ratio that is used to analyze a company’s ability to increase its return on equity. The Dupont analysis looks at three main components of the ROE ratio: \n",
    "Profit Margin \n",
    "Total Asset Turnover \n",
    "Financial Leverage <br>\n",
    "ROE= Profit Margin * Total Assets Turnover * Financial Leverage \n",
    "ROE= Net Income/Total Equity= (Net Income/ Net Sales)* (Net Sales/ Average total Assets) * (Total Assets/ Total Equity)<br>\n",
    "Profit Margin measures the amount of net income earned with each dollar of sales generated by comparing the net income and net sales of a company. That is, this ration can tell us how effectively a company can convert sales into net income. <br>Total Assets Turnover shows how efficiently a company can use its assets to generate sales.<br>\n",
    "Financial Leverage shows the level of debt financing is used to acquire assets and maintain operations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q2.2  How are we going to use the DuPont Analysis?\n",
    "<br>\n",
    "<br>\n",
    "Many companies in different industries are always trying to exploring the right strategy they can exploit.\n",
    "What if they were not sure their strategies are appropriate. Questions come like \" Do we run our company efficiently?\" and \n",
    "\"Do we need to lower our company's financial leverage to reduce financial risk?\" For these business owners, it is a good way to examine successful corporate strategy examples, which can help them identify methods to target within their own organizations.\n",
    "S&P 500 covers 11 sectors and each company listed in S&P 500 can be considered as the successful benchmark in a certain sector.\n",
    "<br>\n",
    "<br>\n",
    "In this part, we are going to derive some information from all the financial statements filed by most of S&P 500 companies and their general description to offer some guidelines to other companies from the view of DuPont Analysis.\n",
    "<br>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Choose the year when most companies' reports had been collected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Year</th>\n",
       "        <th>COUNT(Ticker_Symbol)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016</td>\n",
       "        <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2015</td>\n",
       "        <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2014</td>\n",
       "        <td>436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013</td>\n",
       "        <td>439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2012</td>\n",
       "        <td>236</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2016, 220), (2015, 445), (2014, 436), (2013, 439), (2012, 236)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql (SELECT YEAR(Period_Ending) AS Year, COUNT(Ticker_Symbol) FROM financial_statements WHERE YEAR(Period_Ending) = 2016)\n",
    "      UNION\n",
    "     (SELECT YEAR(Period_Ending) AS Year, COUNT(Ticker_Symbol) FROM financial_statements WHERE YEAR(Period_Ending) = 2015)\n",
    "      UNION\n",
    "     (SELECT YEAR(Period_Ending) AS Year, COUNT(Ticker_Symbol) FROM financial_statements WHERE YEAR(Period_Ending) = 2014)\n",
    "      UNION\n",
    "     (SELECT YEAR(Period_Ending) AS Year, COUNT(Ticker_Symbol) FROM financial_statements WHERE YEAR(Period_Ending) = 2013)\n",
    "      UNION \n",
    "     (SELECT YEAR(Period_Ending) AS Year, COUNT(Ticker_Symbol) FROM financial_statements WHERE YEAR(Period_Ending) = 2012)\n",
    "     \n",
    "     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The query above shows how many reports had been collected in different years in our database. From this output, we decided to\n",
    "perfom the DuPont analysis based on the year 2015 since we could analyze the most companies-445.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use table\"financial_statements\" to calculate financial ratios which will be used in DuPont Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TEMPORARY TABLE IF EXISTS ROE_SUM;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1776 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql  \n",
    "CREATE TEMPORARY TABLE ROE_SUM AS\n",
    "(SELECT  Ticker_Symbol,Period_Ending,(Profit_Margin/100) AS Profit_Margin, \n",
    "ROUND(ABS((Net_Income/(Profit_Margin/100))/Total_Assets),2) AS Total_Assets_Turnover, \n",
    "ROUND(ABS(Total_Assets/Total_Equity ),2) AS Financial_Leverage, \n",
    "ABS(round((Profit_Margin*((Net_Income/(Profit_Margin/100))/Total_Assets)* (Total_Assets/Total_Equity)),0))/100 AS roe,\n",
    "(After_Tax_ROE/100) AS After_Tax_ROE\n",
    "FROM financial_statements \n",
    "ORDER BY After_Tax_ROE DESC);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>Profit_Margin</th>\n",
       "        <th>Total_Assets_Turnover</th>\n",
       "        <th>Financial_Leverage</th>\n",
       "        <th>roe</th>\n",
       "        <th>After_Tax_ROE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB</td>\n",
       "        <td>2015-01-31</td>\n",
       "        <td>0.09</td>\n",
       "        <td>1.55</td>\n",
       "        <td>415.33</td>\n",
       "        <td>57.89</td>\n",
       "        <td>57.89</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LB', datetime.date(2015, 1, 31), 0.09, 1.55, 415.33, 57.89, 57.89)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM ROE_SUM LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Through queries above, we calculated each companies ROE and the three components including Profit Margin, Total Assets Turnover, and Financial Leverage. We created a temporary table\" ROE_SUM\" to summarize our calculated results."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine temporary table\"ROE_SUM\" with base table\"securities\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS ROE_SUM1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1776 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "CREATE TABLE ROE_SUM1 AS\n",
    "(SELECT ROE_SUM.Ticker_Symbol, securities.Company_name AS name, securities.GICS_Sector AS sector,\n",
    "ROE_SUM.Period_Ending, ROE_SUM.Profit_Margin, ROE_SUM.Total_Assets_Turnover,\n",
    "ROE_SUM.Financial_Leverage,ROE_SUM.After_Tax_ROE      \n",
    "FROM ROE_SUM \n",
    "INNER JOIN securities\n",
    "ON securities.Ticker_symbol= ROE_SUM.Ticker_Symbol \n",
    "ORDER BY After_Tax_ROE DESC);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>name</th>\n",
       "        <th>sector</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>Profit_Margin</th>\n",
       "        <th>Total_Assets_Turnover</th>\n",
       "        <th>Financial_Leverage</th>\n",
       "        <th>After_Tax_ROE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB</td>\n",
       "        <td>L Brands Inc.</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td>2015-01-31</td>\n",
       "        <td>0.09</td>\n",
       "        <td>1.55</td>\n",
       "        <td>415.33</td>\n",
       "        <td>57.89</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LB', 'L Brands Inc.', 'Consumer Discretionary', datetime.date(2015, 1, 31), 0.09, 1.55, 415.33, 57.89)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM ROE_SUM1 LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Financial ratios vary a lot depending on sectors. Different companies in different sectors couldn't use the same standard. \n",
    "Because the operational risk companies in different sectors face and financial risk they undertake are significantly different.\n",
    "Hence, we used queries above to combine their financial.\n",
    "<br>\n",
    "ratios with their sector information and created the table\"ROE_SUM1\" based on this combination."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the financial ratios by sectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sector</th>\n",
       "        <th>Year</th>\n",
       "        <th>AVG_PM</th>\n",
       "        <th>AVG_TAT</th>\n",
       "        <th>AVG_FL</th>\n",
       "        <th>AVG_ROE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consumer Staples</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.09</td>\n",
       "        <td>1.37</td>\n",
       "        <td>5.72</td>\n",
       "        <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Energy</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.42</td>\n",
       "        <td>0.54</td>\n",
       "        <td>4.17</td>\n",
       "        <td>0.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Industrials</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.1</td>\n",
       "        <td>0.99</td>\n",
       "        <td>4.82</td>\n",
       "        <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Telecommunications Services</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.15</td>\n",
       "        <td>0.36</td>\n",
       "        <td>5.76</td>\n",
       "        <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Materials</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.12</td>\n",
       "        <td>0.74</td>\n",
       "        <td>4.06</td>\n",
       "        <td>0.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.09</td>\n",
       "        <td>1.17</td>\n",
       "        <td>4.31</td>\n",
       "        <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Health Care</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.12</td>\n",
       "        <td>0.85</td>\n",
       "        <td>4.27</td>\n",
       "        <td>0.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Information Technology</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.17</td>\n",
       "        <td>0.6</td>\n",
       "        <td>2.73</td>\n",
       "        <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Financials</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.16</td>\n",
       "        <td>0.21</td>\n",
       "        <td>8.18</td>\n",
       "        <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Real Estate</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.28</td>\n",
       "        <td>0.21</td>\n",
       "        <td>3.21</td>\n",
       "        <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Utilities</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.11</td>\n",
       "        <td>0.26</td>\n",
       "        <td>3.78</td>\n",
       "        <td>0.1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Consumer Staples', 2015, 0.09, 1.37, 5.72, 0.6),\n",
       " ('Energy', 2015, 0.42, 0.54, 4.17, 0.41),\n",
       " ('Industrials', 2015, 0.1, 0.99, 4.82, 0.37),\n",
       " ('Telecommunications Services', 2015, 0.15, 0.36, 5.76, 0.33),\n",
       " ('Materials', 2015, 0.12, 0.74, 4.06, 0.31),\n",
       " ('Consumer Discretionary', 2015, 0.09, 1.17, 4.31, 0.28),\n",
       " ('Health Care', 2015, 0.12, 0.85, 4.27, 0.26),\n",
       " ('Information Technology', 2015, 0.17, 0.6, 2.73, 0.23),\n",
       " ('Financials', 2015, 0.16, 0.21, 8.18, 0.16),\n",
       " ('Real Estate', 2015, 0.28, 0.21, 3.21, 0.12),\n",
       " ('Utilities', 2015, 0.11, 0.26, 3.78, 0.1)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT ROE_SUM1.sector,  YEAR(ROE_SUM1.Period_Ending) AS Year, \n",
    "             ROUND(AVG(ROE_SUM1.Profit_Margin),2) AS AVG_PM, \n",
    "             ROUND(AVG(ROE_SUM1.Total_Assets_Turnover),2) AS AVG_TAT,\n",
    "             ROUND(AVG(ROE_SUM1.Financial_Leverage),2) AS AVG_FL, \n",
    "             ROUND(AVG(ROE_SUM1.After_Tax_ROE),2) AS AVG_ROE\n",
    "      FROM   ROE_SUM1 WHERE YEAR(ROE_SUM1.Period_Ending)=2015 \n",
    "      AND    ROE_SUM1.After_Tax_ROE < 5 \n",
    "      GROUP BY ROE_SUM1.sector\n",
    "      ORDER BY AVG_ROE DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q2.2 Interpretation:\n",
    "<br>\n",
    "<br>\n",
    "In queries above, we calculated the average profit margin, average total assets turnover, average financial leverage and average return on equity by sectors in 2015. We should note one thing that we excluded companies whose ROE exceeded 500. The reason for excluding these companies is that their fairly high ROE came for a special reason and other companies are unlikely to refer to their financial ratios. We will continue to discuss these outliers in later queries.\n",
    "<br><br>\n",
    "So far, the query results can be presented to companies in different sectors as one of their references to set their goals or strategies. For example, a company in Health Care expects to achieve ROE about 0.26 which means that every dollar of common stockholders’ equity generates 0.22 dollar of net income, then it's advisable for this company to adopt the financial leverage no more than 4.3 and be more effective at using company's asset to create added values, say profit margin should be 12% and total assets turnover be 0.85."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q2.2 Extension: Further check the companies with exceptional performances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sector</th>\n",
       "        <th>Year</th>\n",
       "        <th>AVG_PM</th>\n",
       "        <th>AVG_TAT</th>\n",
       "        <th>AVG_FL</th>\n",
       "        <th>AVG_ROE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.06</td>\n",
       "        <td>0.89</td>\n",
       "        <td>635.02</td>\n",
       "        <td>31.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Energy</td>\n",
       "        <td>2015</td>\n",
       "        <td>2.42</td>\n",
       "        <td>0.54</td>\n",
       "        <td>7.73</td>\n",
       "        <td>8.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Industrials</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.07</td>\n",
       "        <td>0.97</td>\n",
       "        <td>88.4</td>\n",
       "        <td>6.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Financials</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.22</td>\n",
       "        <td>0.64</td>\n",
       "        <td>42.18</td>\n",
       "        <td>5.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consumer Staples</td>\n",
       "        <td>2015</td>\n",
       "        <td>0.05</td>\n",
       "        <td>1.37</td>\n",
       "        <td>85.3</td>\n",
       "        <td>5.82</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Consumer Discretionary', 2015, 0.06, 0.89, 635.02, 31.89),\n",
       " ('Energy', 2015, 2.42, 0.54, 7.73, 8.02),\n",
       " ('Industrials', 2015, 0.07, 0.97, 88.4, 6.01),\n",
       " ('Financials', 2015, 0.22, 0.64, 42.18, 5.96),\n",
       " ('Consumer Staples', 2015, 0.05, 1.37, 85.3, 5.82)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT ROE_SUM1.sector,  YEAR(ROE_SUM1.Period_Ending) AS Year, \n",
    "             ROUND(AVG(ROE_SUM1.Profit_Margin),2) AS AVG_PM, \n",
    "             ROUND(AVG(ROE_SUM1.Total_Assets_Turnover),2) AS AVG_TAT,\n",
    "             ROUND(AVG(ROE_SUM1.Financial_Leverage),2) AS AVG_FL, \n",
    "             ROUND(AVG(ROE_SUM1.After_Tax_ROE),2) AS AVG_ROE\n",
    "      FROM   ROE_SUM1 WHERE YEAR(ROE_SUM1.Period_Ending)=2015 \n",
    "      AND    ROE_SUM1.After_Tax_ROE >= 5 \n",
    "      GROUP BY ROE_SUM1.sector\n",
    "      ORDER BY AVG_ROE DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>name</th>\n",
       "        <th>sector</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>Profit_Margin</th>\n",
       "        <th>Total_Assets_Turnover</th>\n",
       "        <th>Financial_Leverage</th>\n",
       "        <th>After_Tax_ROE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LB</td>\n",
       "        <td>L Brands Inc.</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td>2015-01-31</td>\n",
       "        <td>0.09</td>\n",
       "        <td>1.55</td>\n",
       "        <td>415.33</td>\n",
       "        <td>57.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>APA</td>\n",
       "        <td>Apache Corporation</td>\n",
       "        <td>Energy</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>3.69</td>\n",
       "        <td>0.34</td>\n",
       "        <td>7.34</td>\n",
       "        <td>9.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHK</td>\n",
       "        <td>Chesapeake Energy</td>\n",
       "        <td>Energy</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>1.15</td>\n",
       "        <td>0.74</td>\n",
       "        <td>8.12</td>\n",
       "        <td>6.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ALLE</td>\n",
       "        <td>Allegion</td>\n",
       "        <td>Industrials</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>0.07</td>\n",
       "        <td>0.97</td>\n",
       "        <td>88.4</td>\n",
       "        <td>6.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SPGI</td>\n",
       "        <td>S&amp;P Global, Inc.</td>\n",
       "        <td>Financials</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>0.22</td>\n",
       "        <td>0.64</td>\n",
       "        <td>42.18</td>\n",
       "        <td>5.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHTR</td>\n",
       "        <td>Charter Communications</td>\n",
       "        <td>Consumer Discretionary</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>0.03</td>\n",
       "        <td>0.23</td>\n",
       "        <td>854.7</td>\n",
       "        <td>5.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KMB</td>\n",
       "        <td>Kimberly-Clark</td>\n",
       "        <td>Consumer Staples</td>\n",
       "        <td>2015-12-31</td>\n",
       "        <td>0.05</td>\n",
       "        <td>1.37</td>\n",
       "        <td>85.3</td>\n",
       "        <td>5.82</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LB', 'L Brands Inc.', 'Consumer Discretionary', datetime.date(2015, 1, 31), 0.09, 1.55, 415.33, 57.89),\n",
       " ('APA', 'Apache Corporation', 'Energy', datetime.date(2015, 12, 31), 3.69, 0.34, 7.34, 9.17),\n",
       " ('CHK', 'Chesapeake Energy', 'Energy', datetime.date(2015, 12, 31), 1.15, 0.74, 8.12, 6.87),\n",
       " ('ALLE', 'Allegion', 'Industrials', datetime.date(2015, 12, 31), 0.07, 0.97, 88.4, 6.01),\n",
       " ('SPGI', 'S&P Global, Inc.', 'Financials', datetime.date(2015, 12, 31), 0.22, 0.64, 42.18, 5.96),\n",
       " ('CHTR', 'Charter Communications', 'Consumer Discretionary', datetime.date(2015, 12, 31), 0.03, 0.23, 854.7, 5.89),\n",
       " ('KMB', 'Kimberly-Clark', 'Consumer Staples', datetime.date(2015, 12, 31), 0.05, 1.37, 85.3, 5.82)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM  ROE_SUM1 \n",
    "WHERE YEAR(ROE_SUM1.Period_Ending)=2015 \n",
    "AND ROE_SUM1.After_Tax_ROE >= 5\n",
    "ORDER BY After_Tax_ROE DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q2.2 Extension interpretation\n",
    "<br>\n",
    "<br>\n",
    "We further checked the outliers via queries screening out all the companies with ROE more than 5. As the output shown, there were 7 seven companies with fairly high ROE because of high financial leverage used or abnormal profit margin created in 2015. If we involved these seven companies to calculate the average standard and offer advice, our advice would be misleading. These companies' exceptional ratios of profit margin and financial leverage represented high operational risk and financial risk. On\n",
    "the other hand, these exceptional ratios also mean exceptional returns. So, these companies might be the first choice for speculators. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q2.3 Check if there is a coincidence between the financial performance and market trends"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we found the company with the best financial performance from 2013 to 2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "220 rows affected.\n",
      "439 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>Period_Ending</th>\n",
       "        <th>Accounts_Payable</th>\n",
       "        <th>Accounts_Receivable</th>\n",
       "        <th>Add&#x27;l_income/expense_ items</th>\n",
       "        <th>After_Tax_ROE</th>\n",
       "        <th>Capital_Expenditures</th>\n",
       "        <th>Capital_Surplus</th>\n",
       "        <th>Cash_Ratio</th>\n",
       "        <th>Cash_and_Cash_Equivalents</th>\n",
       "        <th>Changes_in_Inventories</th>\n",
       "        <th>Common_Stocks</th>\n",
       "        <th>Cost_of_Revenue</th>\n",
       "        <th>Current_Ratio</th>\n",
       "        <th>Deferred_Asset_Charges</th>\n",
       "        <th>Deferred_Liability_Charges</th>\n",
       "        <th>Depreciation</th>\n",
       "        <th>Earnings_Before_Interest_and_Tax</th>\n",
       "        <th>Earnings_Before_Tax</th>\n",
       "        <th>Effect_of_Exchange_Rate</th>\n",
       "        <th>Equity_Earnings/Loss_Unconsolidated_Subsidiary</th>\n",
       "        <th>Fixed_Assets</th>\n",
       "        <th>Goodwill</th>\n",
       "        <th>Gross_Margin</th>\n",
       "        <th>Gross_Profit</th>\n",
       "        <th>Income_Tax</th>\n",
       "        <th>Intangible_Assets</th>\n",
       "        <th>Interest_Expense</th>\n",
       "        <th>Inventory</th>\n",
       "        <th>Investments</th>\n",
       "        <th>Liabilities</th>\n",
       "        <th>Long-Term_Debt</th>\n",
       "        <th>Long-Term_Investments</th>\n",
       "        <th>Minority_Interest</th>\n",
       "        <th>Misc._Stocks</th>\n",
       "        <th>Net_Borrowings</th>\n",
       "        <th>Net_Cash_Flow</th>\n",
       "        <th>Net_Cash_Flow-Operating</th>\n",
       "        <th>Net_Cash_Flows-Financing</th>\n",
       "        <th>Net_Cash_Flows-Investing</th>\n",
       "        <th>Net_Income</th>\n",
       "        <th>Net_Income_Adjustments</th>\n",
       "        <th>Net_Income_Applicable_to_Common_Shareholders</th>\n",
       "        <th>Net_Income-Cont._Operations</th>\n",
       "        <th>Net_Receivables</th>\n",
       "        <th>Non-Recurring_Itemsl</th>\n",
       "        <th>Operating_Income</th>\n",
       "        <th>Operating_Margin</th>\n",
       "        <th>Other_Assets</th>\n",
       "        <th>Other_Current_Assets</th>\n",
       "        <th>Other_Current_Liabilities</th>\n",
       "        <th>Other_Equity</th>\n",
       "        <th>Other_Financing_Activities</th>\n",
       "        <th>Other_Investing_Activities</th>\n",
       "        <th>Other_Liabilities</th>\n",
       "        <th>Other_Operating_Activities</th>\n",
       "        <th>Other_Operating_Items</th>\n",
       "        <th>Pre-Tax_Margin</th>\n",
       "        <th>Pre-Tax_ROE</th>\n",
       "        <th>Profit_Margin</th>\n",
       "        <th>Quick_Ratio</th>\n",
       "        <th>Research_and_Development</th>\n",
       "        <th>Retained_Earnings</th>\n",
       "        <th>Sale_and_Purchase_of_Stock</th>\n",
       "        <th>Sales_General_and_Admin</th>\n",
       "        <th>Short-Term_Debt_/_Current_Portion_of_Long-Term_Debt</th>\n",
       "        <th>Short-Term_Investments</th>\n",
       "        <th>Total_Assets</th>\n",
       "        <th>Total_Current_Assets</th>\n",
       "        <th>Total_Current_Liabilities</th>\n",
       "        <th>Total_Equity</th>\n",
       "        <th>Total_Liabilities</th>\n",
       "        <th>Total_Liabilities_&amp;_Equity</th>\n",
       "        <th>Total_Revenue</th>\n",
       "        <th>Treasury_Stock</th>\n",
       "        <th>For_Year</th>\n",
       "        <th>Earnings_Per_Share</th>\n",
       "        <th>Estimated_Shares_Outstanding</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAP</td>\n",
       "        <td>2016-01-02</td>\n",
       "        <td>3757085000.0</td>\n",
       "        <td>-21476000.0</td>\n",
       "        <td>-7484000.0</td>\n",
       "        <td>19.0</td>\n",
       "        <td>-234747000.0</td>\n",
       "        <td>603332000.0</td>\n",
       "        <td>2.0</td>\n",
       "        <td>90782000.0</td>\n",
       "        <td>-244096000.0</td>\n",
       "        <td>7000.0</td>\n",
       "        <td>5314246000.0</td>\n",
       "        <td>130.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>433925000.0</td>\n",
       "        <td>269476000.0</td>\n",
       "        <td>818296000.0</td>\n",
       "        <td>752888000.0</td>\n",
       "        <td>-4213000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1434577000.0</td>\n",
       "        <td>989484000.0</td>\n",
       "        <td>45.0</td>\n",
       "        <td>4422772000.0</td>\n",
       "        <td>279490000.0</td>\n",
       "        <td>687125000.0</td>\n",
       "        <td>65408000.0</td>\n",
       "        <td>4174768000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>174667000.0</td>\n",
       "        <td>1213161000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-426322000.0</td>\n",
       "        <td>-13889000.0</td>\n",
       "        <td>689642000.0</td>\n",
       "        <td>-445952000.0</td>\n",
       "        <td>-253366000.0</td>\n",
       "        <td>473398000.0</td>\n",
       "        <td>30250000.0</td>\n",
       "        <td>473398000.0</td>\n",
       "        <td>473398000.0</td>\n",
       "        <td>597788000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>825780000.0</td>\n",
       "        <td>8.0</td>\n",
       "        <td>82633000.0</td>\n",
       "        <td>77408000.0</td>\n",
       "        <td>39794000.0</td>\n",
       "        <td>-44059000.0</td>\n",
       "        <td>-13492000.0</td>\n",
       "        <td>-18619000.0</td>\n",
       "        <td>229354000.0</td>\n",
       "        <td>7423000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>8.0</td>\n",
       "        <td>31.0</td>\n",
       "        <td>5.0</td>\n",
       "        <td>20.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2021077000.0</td>\n",
       "        <td>-1491000.0</td>\n",
       "        <td>3596992000.0</td>\n",
       "        <td>598000.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>8134565000.0</td>\n",
       "        <td>4940746000.0</td>\n",
       "        <td>3797477000.0</td>\n",
       "        <td>2460648000.0</td>\n",
       "        <td>5673917000.0</td>\n",
       "        <td>8134565000.0</td>\n",
       "        <td>9737018000.0</td>\n",
       "        <td>-119709000.0</td>\n",
       "        <td>2015.0</td>\n",
       "        <td>6.45</td>\n",
       "        <td>73395038.76</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AAP', datetime.date(2016, 1, 2), 3757085000.0, -21476000.0, -7484000.0, 19.0, -234747000.0, 603332000.0, 2.0, 90782000.0, -244096000.0, 7000.0, 5314246000.0, 130.0, 0.0, 433925000.0, 269476000.0, 818296000.0, 752888000.0, -4213000.0, 0.0, 1434577000.0, 989484000.0, 45.0, 4422772000.0, 279490000.0, 687125000.0, 65408000.0, 4174768000.0, 0.0, 174667000.0, 1213161000.0, 0.0, 0.0, 0.0, -426322000.0, -13889000.0, 689642000.0, -445952000.0, -253366000.0, 473398000.0, 30250000.0, 473398000.0, 473398000.0, 597788000.0, 0.0, 825780000.0, 8.0, 82633000.0, 77408000.0, 39794000.0, -44059000.0, -13492000.0, -18619000.0, 229354000.0, 7423000.0, 0.0, 8.0, 31.0, 5.0, 20.0, 0.0, 2021077000.0, -1491000.0, 3596992000.0, 598000.0, 0.0, 8134565000.0, 4940746000.0, 3797477000.0, 2460648000.0, 5673917000.0, 8134565000.0, 9737018000.0, -119709000.0, 2015.0, 6.45, 73395038.76)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql DROP TABLE IF EXISTS 2016report;\n",
    "      DROP TABLE IF EXISTS 2013report;\n",
    "      CREATE TABLE 2016report AS(SELECT * FROM financial_statements WHERE YEAR(Period_Ending)=2016);\n",
    "      CREATE TABLE 2013report AS(SELECT * FROM financial_statements WHERE YEAR(Period_Ending)=2013);\n",
    "      SELECT * FROM 2016report LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Ticker_Symbol</th>\n",
       "        <th>NCFchange_billion</th>\n",
       "        <th>percentI</th>\n",
       "        <th>NIncome_billion</th>\n",
       "        <th>percentII</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FB</td>\n",
       "        <td>3.057</td>\n",
       "        <td>3.26</td>\n",
       "        <td>8.697</td>\n",
       "        <td>5.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAPL</td>\n",
       "        <td>-4.149</td>\n",
       "        <td>-1.18</td>\n",
       "        <td>8.65</td>\n",
       "        <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GM</td>\n",
       "        <td>-3.771</td>\n",
       "        <td>-2.36</td>\n",
       "        <td>5.657</td>\n",
       "        <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EBAY</td>\n",
       "        <td>2.307</td>\n",
       "        <td>-0.99</td>\n",
       "        <td>4.41</td>\n",
       "        <td>1.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHTR</td>\n",
       "        <td>1.516</td>\n",
       "        <td>108.29</td>\n",
       "        <td>3.691</td>\n",
       "        <td>-21.84</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('FB', 3.057, 3.26, 8.697, 5.83),\n",
       " ('AAPL', -4.149, -1.18, 8.65, 0.23),\n",
       " ('GM', -3.771, -2.36, 5.657, 1.5),\n",
       " ('EBAY', 2.307, -0.99, 4.41, 1.54),\n",
       " ('CHTR', 1.516, 108.29, 3.691, -21.84)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT 2016report.Ticker_Symbol, ((2016report.Net_Cash_Flow-2013report.Net_Cash_Flow)/1000000000) AS NCFchange_billion,\n",
    "     ROUND((2016report.Net_Cash_Flow-2013report.Net_Cash_Flow)/2013report.Net_Cash_Flow,2) AS percentI,\n",
    "    ((2016report.Net_Income_Applicable_to_Common_Shareholders-2013report.Net_Income_Applicable_to_Common_Shareholders)/1000000000)\n",
    "    AS NIncome_billion,\n",
    "    ROUND(((2016report.Net_Income_Applicable_to_Common_Shareholders-2013report.Net_Income_Applicable_to_Common_Shareholders)\n",
    "    /2013report.Net_Income_Applicable_to_Common_Shareholders),2) AS percentII\n",
    "    FROM 2016report INNER JOIN 2013report ON 2016report.Ticker_Symbol=2013report.Ticker_Symbol\n",
    "    ORDER BY NIncome_billion DESC LIMIT 5;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The query above indicated that FB(Facebook) had the best financial performance.\n",
    "<br>\n",
    "In the next step, we looked at the stock market and spotted out which company's close price experienced the fastes growing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>symbol</th>\n",
       "        <th>SUM_volume16</th>\n",
       "        <th>AVG_close16</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A</td>\n",
       "        <td>524927900</td>\n",
       "        <td>43.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAL</td>\n",
       "        <td>2309784700</td>\n",
       "        <td>38.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAP</td>\n",
       "        <td>260668100</td>\n",
       "        <td>155.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AAPL</td>\n",
       "        <td>9680671300</td>\n",
       "        <td>104.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ABBV</td>\n",
       "        <td>2019860600</td>\n",
       "        <td>60.67</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('A', Decimal('524927900'), 43.37),\n",
       " ('AAL', Decimal('2309784700'), 38.18),\n",
       " ('AAP', Decimal('260668100'), 155.49),\n",
       " ('AAPL', Decimal('9680671300'), 104.6),\n",
       " ('ABBV', Decimal('2019860600'), 60.67)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql  DROP VIEW IF EXISTS 16mar;\n",
    "       DROP VIEW IF EXISTS 13mar;\n",
    "    \n",
    "       CREATE VIEW 13mar AS (SELECT symbol, SUM(volume) AS SUM_volume13, ROUND(AVG(close),2) AS AVG_close13 FROM prices WHERE YEAR(date)=2013 \n",
    "       GROUP BY symbol);\n",
    "        \n",
    "       CREATE VIEW 16mar AS (SELECT symbol, SUM(volume) AS SUM_volume16, ROUND(AVG(close),2) AS AVG_close16 FROM prices WHERE YEAR(date)=2016 \n",
    "       GROUP BY symbol);\n",
    "    \n",
    "       SELECT * FROM 16mar LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@fe512_mysql/fe512db\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>symbol</th>\n",
       "        <th>SUM_volume16</th>\n",
       "        <th>SUM_volume13</th>\n",
       "        <th>volume_dif</th>\n",
       "        <th>AVG_close16</th>\n",
       "        <th>AVG_close13</th>\n",
       "        <th>AvgClose_dif</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AVGO</td>\n",
       "        <td>807593700</td>\n",
       "        <td>650915300</td>\n",
       "        <td>0.2407</td>\n",
       "        <td>157.19</td>\n",
       "        <td>38.54</td>\n",
       "        <td>3.07862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NVDA</td>\n",
       "        <td>2788055600</td>\n",
       "        <td>2228684400</td>\n",
       "        <td>0.2510</td>\n",
       "        <td>53.76</td>\n",
       "        <td>14.19</td>\n",
       "        <td>2.788584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EA</td>\n",
       "        <td>906326300</td>\n",
       "        <td>1201599300</td>\n",
       "        <td>-0.2457</td>\n",
       "        <td>73.75</td>\n",
       "        <td>21.82</td>\n",
       "        <td>2.379927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FB</td>\n",
       "        <td>6414357000</td>\n",
       "        <td>15143182600</td>\n",
       "        <td>-0.5764</td>\n",
       "        <td>117.04</td>\n",
       "        <td>35.48</td>\n",
       "        <td>2.29876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SWKS</td>\n",
       "        <td>795435200</td>\n",
       "        <td>839161400</td>\n",
       "        <td>-0.0521</td>\n",
       "        <td>70.63</td>\n",
       "        <td>23.68</td>\n",
       "        <td>1.982686</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AVGO', Decimal('807593700'), Decimal('650915300'), Decimal('0.2407'), 157.19, 38.54, 3.07862),\n",
       " ('NVDA', Decimal('2788055600'), Decimal('2228684400'), Decimal('0.2510'), 53.76, 14.19, 2.788584),\n",
       " ('EA', Decimal('906326300'), Decimal('1201599300'), Decimal('-0.2457'), 73.75, 21.82, 2.379927),\n",
       " ('FB', Decimal('6414357000'), Decimal('15143182600'), Decimal('-0.5764'), 117.04, 35.48, 2.29876),\n",
       " ('SWKS', Decimal('795435200'), Decimal('839161400'), Decimal('-0.0521'), 70.63, 23.68, 1.982686)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT 16mar.symbol, 16mar.SUM_volume16,13mar.SUM_volume13,\n",
    "      (16mar.SUM_volume16-13mar.SUM_volume13)/13mar.SUM_volume13 AS volume_dif,\n",
    "      16mar.AVG_close16,13mar.AVG_close13,\n",
    "      (16mar.AVG_close16-13mar.AVG_close13)/13mar.AVG_close13 AS AvgClose_dif\n",
    "      FROM 16mar, 13mar \n",
    "      WHERE 16mar.symbol = 13mar.symbol\n",
    "      ORDER BY AvgClose_dif DESC LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Q2.3 interpretation\n",
    "<br><br>\n",
    "This results showed us that Facebook's average close price increased by 229% and its volume decreased by 57%.\n",
    "Even though in the growth of close price term, Facebook didn't reach the first place in the stock market from 2013 to 2016,\n",
    "it still remains at the top five with its outstanding financial performance. There is no coincidence. Markets can greatly\n",
    "reflect the companies performance."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "# Conclusion\n",
    "<br>\n",
    "1.We should invest the two most undervalued companies, Hewlett Packard Enterprise and D. R. Horton.<br><br>\n",
    "2.We would get at most 33% and 21% if we invested HPE and DHI as earlier as possible since 2010.<br><br>\n",
    "3.Companies can improve their strategies by referring to the industrial standard generated by DuPont analysis and finally raise their returns on equity.<br><br>\n",
    "4.Facebook had the best financial performance from 2013 to 2016.\n",
    "<br>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Future Work\n",
    "<br>\n",
    "In this project, we had a lot of limitations when we tried to use some models and formulas to analyze the stocks. This is mainly caused by the lack of data in our table.<br>\n",
    "<br> On one hand, some models and formulas needed some features that do not exist in our database. For example, when we try to build the capital asset pricing model (CAPM) to evaluate the securities' returns and risks more technically and accurately, we could not achieve this because our database did not have the risk-free rate and other key factors.<br>\n",
    "<br>On the other hand, the existed features in our database did not have complete data. For instance, when I using the DuPont Analysis on Q2, we chose to perform it on 2015 but not on 2016 just because the companies in 2016 are far less than the companies in 2015.<br>\n",
    "<br>In the future, we will directly get the raw data from original website, not from Kaggle. This method can allow us get more abundant data that we want, and then we will be able to perform further analysis.<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# References\n",
    "<br>\n",
    "Entity–relationship model, (https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model)\n",
    "<br>\n",
    "Benjamin Graham's Timeless Investment Principles,(https://www.investopedia.com/articles/basics/07/grahamprinciples.asp)\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}