{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import database_tables as tables\n",
    "import pandas as pd\n",
    "import os\n",
    "import dautil as dl\n",
    "import ch7util\n",
    "import sqlite3\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from IPython.display import HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def populate_date_dim(session):\n",
    "    for d in pd.date_range(start='19000101', end='20250101'):\n",
    "        adate = tables.DateDim(date=d.date(), day_of_month=d.day,\n",
    "                               day_of_week=d.dayofweek, month=d.month,\n",
    "                               quarter=d.quarter, year=d.year)\n",
    "        session.add(adate)\n",
    "\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def populate_asset_dim(session):\n",
    "    asset = tables.AssetDim(symbol='AAPL', name='Apple Inc.',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Consumer Goods')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='INTC', name='Intel Corporation',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Technology')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='MSFT', name='Microsoft Corporation',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Technology')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='KO', name='The Coca-Cola Company',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Consumer Goods')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='DIS', name='The Walt Disney Company',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Services')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='MCD', name='McDonald\\'s Corp.',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Services')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='NKE', name='NIKE, Inc.',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Consumer Goods')\n",
    "    session.add(asset)\n",
    "\n",
    "    asset = tables.AssetDim(symbol='IBM',\n",
    "                            name='International Business Machines Corporation',\n",
    "                            category='Common Stock', country='USA',\n",
    "                            sector='Technology')\n",
    "    session.add(asset)\n",
    "\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def populate_source_dim(session):\n",
    "    session.add(tables.SourceDim(name='Yahoo Finance',\n",
    "                                 url='https://finance.yahoo.com'))\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def populate_prices(session):\n",
    "    symbols = dl.db.map_to_id(session, tables.AssetDim.symbol)\n",
    "    dates = dl.db.map_to_id(session, tables.DateDim.date)\n",
    "    source_id = session.query(tables.SourceDim).first().id\n",
    "    ohlc = dl.data.OHLC()\n",
    "    conn = sqlite3.connect(dbname)\n",
    "    c = conn.cursor()\n",
    "    insert = '''INSERT INTO stock_price (id, date_id,\n",
    "        asset_id, source_id, open_price, high_price, low_price,\n",
    "        close_price, adjusted_close, volume)  VALUES({id}, {date_id},\n",
    "        {asset_id}, {source_id}, {open_price}, {high_price},\n",
    "        {low_price}, {close_price}, {adj_close}, {volume})'''\n",
    "    logger = dl.log_api.conf_logger(__name__)\n",
    "\n",
    "    for symbol in ch7util.STOCKS:\n",
    "        df = ohlc.get(symbol)\n",
    "        i = 0\n",
    "\n",
    "        for index, row in df.iterrows():\n",
    "            date_id = dates[index.date()]\n",
    "            asset_id = symbols[symbol]\n",
    "            i += 1\n",
    "            stmt = insert.format(id=i, date_id=date_id,\n",
    "                                 asset_id=asset_id,\n",
    "                                 source_id=source_id,\n",
    "                                 open_price=dl.data.centify(row['Open']),\n",
    "                                 high_price=dl.data.centify(row['High']),\n",
    "                                 low_price=dl.data.centify(row['Low']),\n",
    "                                 close_price=dl.data.centify(row['Close']),\n",
    "                                 adj_close=dl.data.centify(row['Adj Close']),\n",
    "                                 volume=int(row['Volume']))\n",
    "            c.execute(stmt)\n",
    "\n",
    "            if i % 1000 == 0:\n",
    "                logger.info(\"Progress %s %s\", symbol, i)\n",
    "\n",
    "            conn.commit()\n",
    "\n",
    "        conn.commit()\n",
    "\n",
    "    c.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def populate(session):\n",
    "    if session.query(tables.SourceDim).count() == 0:\n",
    "        populate_source_dim(session)\n",
    "        populate_asset_dim(session)\n",
    "        populate_date_dim(session)\n",
    "        populate_prices(session)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def plot_volume(col, ax):\n",
    "    df = pd.read_sql(sql.format(col=col), conn)\n",
    "    sns.barplot(x=col, y='AVG(P.Volume/1000)', data=df,\n",
    "                hue='sector', ax=ax)\n",
    "\n",
    "    ax.legend(loc='best')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dbname = os.path.join(dl.data.get_data_dir(), 'stock_prices.db')\n",
    "session = dl.db.create_session(dbname, tables.Base)\n",
    "populate(session)\n",
    "sql = '''\n",
    "    SELECT\n",
    "        A.sector,\n",
    "        D.{col},\n",
    "        AVG(P.Volume/1000)\n",
    "    FROM stock_price P\n",
    "    INNER JOIN date_dim D  ON (P.Date_Id = D.Id)\n",
    "    INNER JOIN asset_dim A ON (P.asset_id = a.Id)\n",
    "    GROUP BY\n",
    "        A.sector,\n",
    "        D.{col}\n",
    "      '''\n",
    "\n",
    "conn = sqlite3.connect(dbname)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "context = dl.nb.Context('populate_database')\n",
    "dl.nb.RcWidget(context)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sp = dl.plotting.Subplotter(2, 2, context)\n",
    "plot_volume('day_of_week', sp.ax)\n",
    "sp.ax.set_xticklabels(['Mon', 'Tue', 'Wed', 'Thu', 'Fri'])\n",
    "\n",
    "plot_volume('month', sp.next_ax())\n",
    "sp.ax.set_xticklabels(dl.ts.short_months())\n",
    "\n",
    "plot_volume('day_of_month', sp.next_ax())\n",
    "plot_volume('quarter', sp.next_ax())\n",
    "HTML(sp.exit())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
