00_Downloading_Data_from_Yahoo_Finance.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "# Downloading Data from Yahoo Finance\n",
    "\n",
    "Before we can do anything interesting, we need to get data. Downloading it ourselves can be a pain so let's automated it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Adj_Close': '65.415729',\n",
      " 'Close': '65.900002',\n",
      " 'Date': '2016-02-16',\n",
      " 'High': '66.800003',\n",
      " 'Low': '65.50',\n",
      " 'Open': '66.610001',\n",
      " 'Symbol': 'WMT',\n",
      " 'Volume': '11360500'}\n"
     ]
    }
   ],
   "source": [
    "from pprint import pprint\n",
    "import numpy as np\n",
    "# Let's import the yahoo finance API\n",
    "import yahoo_finance\n",
    "\n",
    "def download_data(ticker, start_date, end_date):\n",
    "    # Do a lookup on a ticker\n",
    "    share = yahoo_finance.Share(ticker)\n",
    "    # And query the historical data\n",
    "    return share.get_historical(start_date, end_date)\n",
    "\n",
    "# And a little test with Walmart\n",
    "pprint(download_data('WMT', '2016-02-10', '2016-02-16')[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Multiple Stocks\n",
    "\n",
    "The previous code will only allow us to query on a single stock, so let's wrap it so we can iterate over multiple stocks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def download_from_yf(tickers, start_date, end_date):\n",
    "    data = {}\n",
    "    for ticker in tickers:\n",
    "        data[ticker] = download_data(ticker, start_date, end_date)\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fetch stock data for WMT, XOM from 2016-02-23 to 2016-03-23\n"
     ]
    }
   ],
   "source": [
    "# We need to tell the downloader what stocks we care about!\n",
    "tickers = ['WMT', 'XOM'] # Walmart and ExxonMobil from example in class\n",
    "\n",
    "# And also a timerange, i.e. the last month\n",
    "import datetime\n",
    "import dateutil.relativedelta\n",
    "\n",
    "end_date = datetime.date.today()\n",
    "\n",
    "# And now we just have to subtract a month from end_date\n",
    "start_date = end_date - dateutil.relativedelta.relativedelta(months=1)\n",
    "print \"Fetch stock data for %s from\"%\", \".join(tickers), start_date, \"to\", end_date\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But the Yahoo Finance API wants the dates as strings, so we need to convert from a datetime.date object to a string!\n",
    "* The string is formatted as YYYY-MM-DD (which also happens to be the default format Python uses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "date_format = '%Y-%m-%d' # YYYY-MM-DD in Python\n",
    "s_date = start_date.strftime(date_format)\n",
    "\n",
    "# We could have alternatively cast the dates into a\n",
    "# string since %Y-%m-%d is the the default date format\n",
    "# s_date = str(start_date)\n",
    "\n",
    "e_date = end_date.strftime(date_format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'WMT': [{'Adj_Close': '67.870003',\n",
      "          'Close': '67.870003',\n",
      "          'Date': '2016-03-22',\n",
      "          'High': '68.68',\n",
      "          'Low': '67.790001',\n",
      "          'Open': '68.00',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '7497600'},\n",
      "         {'Adj_Close': '67.970001',\n",
      "          'Close': '67.970001',\n",
      "          'Date': '2016-03-21',\n",
      "          'High': '68.379997',\n",
      "          'Low': '66.970001',\n",
      "          'Open': '67.029999',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '8978500'},\n",
      "         {'Adj_Close': '66.949997',\n",
      "          'Close': '66.949997',\n",
      "          'Date': '2016-03-18',\n",
      "          'High': '68.029999',\n",
      "          'Low': '66.75',\n",
      "          'Open': '67.480003',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '22837600'},\n",
      "         {'Adj_Close': '67.449997',\n",
      "          'Close': '67.449997',\n",
      "          'Date': '2016-03-17',\n",
      "          'High': '68.919998',\n",
      "          'Low': '67.260002',\n",
      "          'Open': '67.82',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '11823000'},\n",
      "         {'Adj_Close': '67.989998',\n",
      "          'Close': '67.989998',\n",
      "          'Date': '2016-03-16',\n",
      "          'High': '68.339996',\n",
      "          'Low': '67.25',\n",
      "          'Open': '67.940002',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '8205100'},\n",
      "         {'Adj_Close': '68.089996',\n",
      "          'Close': '68.089996',\n",
      "          'Date': '2016-03-15',\n",
      "          'High': '68.349998',\n",
      "          'Low': '67.00',\n",
      "          'Open': '67.00',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '7552300'},\n",
      "         {'Adj_Close': '67.360001',\n",
      "          'Close': '67.360001',\n",
      "          'Date': '2016-03-14',\n",
      "          'High': '67.669998',\n",
      "          'Low': '66.959999',\n",
      "          'Open': '67.150002',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '6656200'},\n",
      "         {'Adj_Close': '67.169998',\n",
      "          'Close': '67.169998',\n",
      "          'Date': '2016-03-11',\n",
      "          'High': '67.940002',\n",
      "          'Low': '66.800003',\n",
      "          'Open': '67.769997',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '8250800'},\n",
      "         {'Adj_Close': '67.410004',\n",
      "          'Close': '67.410004',\n",
      "          'Date': '2016-03-10',\n",
      "          'High': '68.040001',\n",
      "          'Low': '66.400002',\n",
      "          'Open': '67.550003',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '9635600'},\n",
      "         {'Adj_Close': '67.529999',\n",
      "          'Close': '67.529999',\n",
      "          'Date': '2016-03-09',\n",
      "          'High': '67.989998',\n",
      "          'Low': '67.120003',\n",
      "          'Open': '67.790001',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '7806000'},\n",
      "         {'Adj_Close': '67.540002',\n",
      "          'Close': '68.040001',\n",
      "          'Date': '2016-03-08',\n",
      "          'High': '68.470001',\n",
      "          'Low': '67.489998',\n",
      "          'Open': '67.730003',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '10067900'},\n",
      "         {'Adj_Close': '67.391103',\n",
      "          'Close': '67.889999',\n",
      "          'Date': '2016-03-07',\n",
      "          'High': '68.480003',\n",
      "          'Low': '66.269997',\n",
      "          'Open': '66.620003',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '12825400'},\n",
      "         {'Adj_Close': '66.289259',\n",
      "          'Close': '66.779999',\n",
      "          'Date': '2016-03-04',\n",
      "          'High': '67.279999',\n",
      "          'Low': '66.089996',\n",
      "          'Open': '66.139999',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '10010500'},\n",
      "         {'Adj_Close': '65.653963',\n",
      "          'Close': '66.139999',\n",
      "          'Date': '2016-03-03',\n",
      "          'High': '66.199997',\n",
      "          'Low': '64.879997',\n",
      "          'Open': '65.949997',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '11846800'},\n",
      "         {'Adj_Close': '65.723448',\n",
      "          'Close': '66.209999',\n",
      "          'Date': '2016-03-02',\n",
      "          'High': '66.669998',\n",
      "          'Low': '66.00',\n",
      "          'Open': '66.209999',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '9165600'},\n",
      "         {'Adj_Close': '65.971611',\n",
      "          'Close': '66.459999',\n",
      "          'Date': '2016-03-01',\n",
      "          'High': '66.889999',\n",
      "          'Low': '66.00',\n",
      "          'Open': '66.650002',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '10429500'},\n",
      "         {'Adj_Close': '65.85249',\n",
      "          'Close': '66.339996',\n",
      "          'Date': '2016-02-29',\n",
      "          'High': '66.949997',\n",
      "          'Low': '66.220001',\n",
      "          'Open': '66.629997',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '9345200'},\n",
      "         {'Adj_Close': '66.021247',\n",
      "          'Close': '66.510002',\n",
      "          'Date': '2016-02-26',\n",
      "          'High': '68.199997',\n",
      "          'Low': '66.300003',\n",
      "          'Open': '68.080002',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '10984300'},\n",
      "         {'Adj_Close': '67.540002',\n",
      "          'Close': '68.040001',\n",
      "          'Date': '2016-02-25',\n",
      "          'High': '68.129997',\n",
      "          'Low': '67.330002',\n",
      "          'Open': '67.449997',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '9581300'},\n",
      "         {'Adj_Close': '66.626765',\n",
      "          'Close': '67.120003',\n",
      "          'Date': '2016-02-24',\n",
      "          'High': '67.330002',\n",
      "          'Low': '65.720001',\n",
      "          'Open': '66.190002',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '10291200'},\n",
      "         {'Adj_Close': '65.991469',\n",
      "          'Close': '66.480003',\n",
      "          'Date': '2016-02-23',\n",
      "          'High': '66.599998',\n",
      "          'Low': '65.279999',\n",
      "          'Open': '65.839996',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': '11432000'}],\n",
      " 'XOM': [{'Adj_Close': '84.120003',\n",
      "          'Close': '84.120003',\n",
      "          'Date': '2016-03-22',\n",
      "          'High': '84.860001',\n",
      "          'Low': '83.010002',\n",
      "          'Open': '83.080002',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '12899300'},\n",
      "         {'Adj_Close': '83.620003',\n",
      "          'Close': '83.620003',\n",
      "          'Date': '2016-03-21',\n",
      "          'High': '84.139999',\n",
      "          'Low': '83.50',\n",
      "          'Open': '83.839996',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '8339200'},\n",
      "         {'Adj_Close': '84.199997',\n",
      "          'Close': '84.199997',\n",
      "          'Date': '2016-03-18',\n",
      "          'High': '84.599998',\n",
      "          'Low': '83.25',\n",
      "          'Open': '84.589996',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '19400400'},\n",
      "         {'Adj_Close': '84.099998',\n",
      "          'Close': '84.099998',\n",
      "          'Date': '2016-03-17',\n",
      "          'High': '84.68',\n",
      "          'Low': '82.720001',\n",
      "          'Open': '83.68',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '13101300'},\n",
      "         {'Adj_Close': '82.870003',\n",
      "          'Close': '82.870003',\n",
      "          'Date': '2016-03-16',\n",
      "          'High': '83.160004',\n",
      "          'Low': '81.940002',\n",
      "          'Open': '83.019997',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '11411000'},\n",
      "         {'Adj_Close': '82.82',\n",
      "          'Close': '82.82',\n",
      "          'Date': '2016-03-15',\n",
      "          'High': '82.849998',\n",
      "          'Low': '81.519997',\n",
      "          'Open': '81.599998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '9181600'},\n",
      "         {'Adj_Close': '82.410004',\n",
      "          'Close': '82.410004',\n",
      "          'Date': '2016-03-14',\n",
      "          'High': '82.639999',\n",
      "          'Low': '81.019997',\n",
      "          'Open': '81.480003',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '13260100'},\n",
      "         {'Adj_Close': '82.190002',\n",
      "          'Close': '82.190002',\n",
      "          'Date': '2016-03-11',\n",
      "          'High': '83.529999',\n",
      "          'Low': '82.010002',\n",
      "          'Open': '83.220001',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '14753100'},\n",
      "         {'Adj_Close': '82.18',\n",
      "          'Close': '82.18',\n",
      "          'Date': '2016-03-10',\n",
      "          'High': '82.660004',\n",
      "          'Low': '80.93',\n",
      "          'Open': '82.419998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '15877900'},\n",
      "         {'Adj_Close': '82.400002',\n",
      "          'Close': '82.400002',\n",
      "          'Date': '2016-03-09',\n",
      "          'High': '83.849998',\n",
      "          'Low': '82.239998',\n",
      "          'Open': '83.220001',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '14128000'},\n",
      "         {'Adj_Close': '82.629997',\n",
      "          'Close': '82.629997',\n",
      "          'Date': '2016-03-08',\n",
      "          'High': '84.209999',\n",
      "          'Low': '82.480003',\n",
      "          'Open': '84.00',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '13697900'},\n",
      "         {'Adj_Close': '84.459999',\n",
      "          'Close': '84.459999',\n",
      "          'Date': '2016-03-07',\n",
      "          'High': '84.459999',\n",
      "          'Low': '82.209999',\n",
      "          'Open': '82.309998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '18164900'},\n",
      "         {'Adj_Close': '82.290001',\n",
      "          'Close': '82.290001',\n",
      "          'Date': '2016-03-04',\n",
      "          'High': '82.970001',\n",
      "          'Low': '81.760002',\n",
      "          'Open': '82.660004',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '18364400'},\n",
      "         {'Adj_Close': '82.400002',\n",
      "          'Close': '82.400002',\n",
      "          'Date': '2016-03-03',\n",
      "          'High': '82.720001',\n",
      "          'Low': '81.540001',\n",
      "          'Open': '82.660004',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '13174800'},\n",
      "         {'Adj_Close': '82.699997',\n",
      "          'Close': '82.699997',\n",
      "          'Date': '2016-03-02',\n",
      "          'High': '82.720001',\n",
      "          'Low': '80.309998',\n",
      "          'Open': '80.949997',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '14067400'},\n",
      "         {'Adj_Close': '81.279999',\n",
      "          'Close': '81.279999',\n",
      "          'Date': '2016-03-01',\n",
      "          'High': '81.769997',\n",
      "          'Low': '80.32',\n",
      "          'Open': '80.559998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '15688800'},\n",
      "         {'Adj_Close': '80.150002',\n",
      "          'Close': '80.150002',\n",
      "          'Date': '2016-02-29',\n",
      "          'High': '82.400002',\n",
      "          'Low': '80.050003',\n",
      "          'Open': '81.879997',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '17736400'},\n",
      "         {'Adj_Close': '81.75',\n",
      "          'Close': '81.75',\n",
      "          'Date': '2016-02-26',\n",
      "          'High': '83.080002',\n",
      "          'Low': '81.690002',\n",
      "          'Open': '82.889999',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '14276800'},\n",
      "         {'Adj_Close': '82.010002',\n",
      "          'Close': '82.010002',\n",
      "          'Date': '2016-02-25',\n",
      "          'High': '82.07',\n",
      "          'Low': '80.019997',\n",
      "          'Open': '81.559998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '13620200'},\n",
      "         {'Adj_Close': '81.519997',\n",
      "          'Close': '81.519997',\n",
      "          'Date': '2016-02-24',\n",
      "          'High': '81.769997',\n",
      "          'Low': '79.669998',\n",
      "          'Open': '79.910004',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '13691100'},\n",
      "         {'Adj_Close': '81.230003',\n",
      "          'Close': '81.230003',\n",
      "          'Date': '2016-02-23',\n",
      "          'High': '82.370003',\n",
      "          'Low': '80.879997',\n",
      "          'Open': '81.989998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': '10891400'}]}\n"
     ]
    }
   ],
   "source": [
    "data = download_from_yf(tickers, s_date, e_date)\n",
    "from pprint import pprint\n",
    "pprint(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# What if we want weekly data?\n",
    "\n",
    "Unfortunately, YF doesn't seem to provide weekly and monthly data through their API. \n",
    "\n",
    "We need to generate this data ourselves\n",
    "\n",
    "* Open Price is the opening price we observe on the Monday\n",
    "* Similarly, closing price is determined by the closing price on Friday\n",
    " * Actually this isn't completely true due to fixed intervals, holidays, etc... but we will leave those details for the implementation\n",
    "* Adjusted Close works the same as close\n",
    "* Low is the lowest 'low' in a week\n",
    "* High is the highest 'high' in the week\n",
    "* Volume is average volume over the week\n",
    " * $Volume = \\frac{1}{N} \\sum_{i=1}^{N} Volume_i$\n",
    " * Yahoo Finance actually truncates the number since they track volume to the hundreds place"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def convert_to_weekly(data):\n",
    "    def __convert_to_weekly(data):\n",
    "        # Sort the data by time from oldest date to most recent\n",
    "        data = sorted(data, key=lambda row: datetime.datetime.strptime(row['Date'], date_format))\n",
    "        weeks = {}\n",
    "        \n",
    "        # High level approach: Markets typically open on a Monday,\n",
    "        # so we see if the Monday of a given week is in the weeks dict\n",
    "        #   if it is: we aren't the first day the markets were open on a given week\n",
    "        #     - Update adj. close and close\n",
    "        #     - Append volume to list\n",
    "        #     - And check low and high\n",
    "        #   if it isn't: we are the first day\n",
    "        #     - Do the above stuff and also set the open price\n",
    "        \n",
    "        for row in data:\n",
    "            d = datetime.datetime.strptime(row['Date'], date_format)\n",
    "            # Expected start of the week\n",
    "            d_start = (d-datetime.timedelta(d.weekday())).strftime(date_format)\n",
    "            if d_start not in weeks:\n",
    "                # First day in a given week\n",
    "                weeks[d_start] = row\n",
    "                weeks[d_start]['Volume'] = [row['Volume']]\n",
    "            else:\n",
    "                # Append the daily volume to the weekly count\n",
    "                weeks[d_start]['Volume'].append(row['Volume'])\n",
    "                # Update close and adj. close since we are the latest date\n",
    "                weeks[d_start]['Close'] = row['Close']\n",
    "                weeks[d_start]['Adj_Close'] = row['Adj_Close']\n",
    "\n",
    "                # Check if we are higher than the highest observed value\n",
    "                if float(weeks[d_start]['High']) < float(row['High']):\n",
    "                    weeks[d_start]['High'] = row['High']\n",
    "\n",
    "                # Check if we are lower than the lowest observed value\n",
    "                if float(weeks[d_start]['Low']) > float(row['Low']):\n",
    "                    weeks[d_start]['Low'] = row['Low']\n",
    "\n",
    "        rows = []\n",
    "        for k,v in weeks.items():\n",
    "            # Sum up the volumes and divide by the count (compute average volume)\n",
    "            v['Volume'] = np.average(map(int, v['Volume']))\n",
    "            rows.append(v)\n",
    "        return rows\n",
    "        \n",
    "    for ticker in data.keys():\n",
    "        data[ticker] = __convert_to_weekly(data[ticker])\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'WMT': [{'Adj_Close': '66.021247',\n",
      "          'Close': '66.510002',\n",
      "          'Date': '2016-02-23',\n",
      "          'High': '68.199997',\n",
      "          'Low': '65.279999',\n",
      "          'Open': '65.839996',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': 10572200.0},\n",
      "         {'Adj_Close': '66.289259',\n",
      "          'Close': '66.779999',\n",
      "          'Date': '2016-02-29',\n",
      "          'High': '67.279999',\n",
      "          'Low': '64.879997',\n",
      "          'Open': '66.629997',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': 10159520.0},\n",
      "         {'Adj_Close': '67.870003',\n",
      "          'Close': '67.870003',\n",
      "          'Date': '2016-03-21',\n",
      "          'High': '68.68',\n",
      "          'Low': '66.970001',\n",
      "          'Open': '67.029999',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': 8238050.0},\n",
      "         {'Adj_Close': '66.949997',\n",
      "          'Close': '66.949997',\n",
      "          'Date': '2016-03-14',\n",
      "          'High': '68.919998',\n",
      "          'Low': '66.75',\n",
      "          'Open': '67.150002',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': 11414840.0},\n",
      "         {'Adj_Close': '67.169998',\n",
      "          'Close': '67.169998',\n",
      "          'Date': '2016-03-07',\n",
      "          'High': '68.480003',\n",
      "          'Low': '66.269997',\n",
      "          'Open': '66.620003',\n",
      "          'Symbol': 'WMT',\n",
      "          'Volume': 9717140.0}],\n",
      " 'XOM': [{'Adj_Close': '81.75',\n",
      "          'Close': '81.75',\n",
      "          'Date': '2016-02-23',\n",
      "          'High': '83.080002',\n",
      "          'Low': '79.669998',\n",
      "          'Open': '81.989998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': 13119875.0},\n",
      "         {'Adj_Close': '82.290001',\n",
      "          'Close': '82.290001',\n",
      "          'Date': '2016-02-29',\n",
      "          'High': '82.970001',\n",
      "          'Low': '80.050003',\n",
      "          'Open': '81.879997',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': 15806360.0},\n",
      "         {'Adj_Close': '84.120003',\n",
      "          'Close': '84.120003',\n",
      "          'Date': '2016-03-21',\n",
      "          'High': '84.860001',\n",
      "          'Low': '83.010002',\n",
      "          'Open': '83.839996',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': 10619250.0},\n",
      "         {'Adj_Close': '84.199997',\n",
      "          'Close': '84.199997',\n",
      "          'Date': '2016-03-14',\n",
      "          'High': '84.68',\n",
      "          'Low': '81.019997',\n",
      "          'Open': '81.480003',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': 13270880.0},\n",
      "         {'Adj_Close': '82.190002',\n",
      "          'Close': '82.190002',\n",
      "          'Date': '2016-03-07',\n",
      "          'High': '84.459999',\n",
      "          'Low': '80.93',\n",
      "          'Open': '82.309998',\n",
      "          'Symbol': 'XOM',\n",
      "          'Volume': 15324360.0}]}\n"
     ]
    }
   ],
   "source": [
    "weekly_data = convert_to_weekly(data)\n",
    "pprint(weekly_data)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting monthly data\n",
    "\n",
    "It's easier than weekly since you don't have to deal with weekdays! I am not going to go into the details..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aside on Numerical Analysis & Processing\n",
    "\n",
    "Python defaults to representing numbers as floating point numbers (floats). This is fine for many applications, but finance is not one of them. Many finance companies choose to work with integers (i.e. MasterCard) and convert 123.45 into 12345.\n",
    "\n",
    "Floats are great because they can store data in a massive range: $1.175494 × 10^{-38} \\leq x \\leq 3.402823 × 10^{38}$, but they cannot represent every rational number or even every integer in that range. The easy way to think about it is there are a finite number of representations, which is much smaller than the range. To represent a number, we want to minimize the error between the actual number and it's representation. \n",
    "\n",
    "If we start doing lots of math with floating point number, we will start increasing the amount of error!\n",
    "\n",
    "\n",
    "Look at some of the decimals above. Unfortunately, the Yahoo Finance API uses floats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "73.9100020000000057507349993102252483367919921875\n",
      "73.91\n"
     ]
    }
   ],
   "source": [
    "# Python's decimal class let's us represent decimals \n",
    "# with a fixed number of digits after the decimal place\n",
    "import decimal\n",
    "# This is no good as 73.9... is a float and we will represent it to 26 places of accuracy\n",
    "dec = decimal.Decimal(73.910002)\n",
    "print dec\n",
    "\n",
    "# If we round it first, we get nicer results :)\n",
    "dec = decimal.Decimal(str(round(73.910002, 2)))\n",
    "print dec"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting the data into Excel\n",
    "\n",
    "We might want to leverage excel in our analysis. Fortunately Python has a module for working with excel!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Let's import it and get started\n",
    "import xlsxwriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def write_xlsx(data, output_file):\n",
    "    header = ['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj_Close']\n",
    "    # Create a new workbook\n",
    "    workbook = xlsxwriter.Workbook(output_file)\n",
    "    # Pick the format that you want Excel to use for dates\n",
    "    # mm/dd/yy is the default, so let's use that\n",
    "    excel_date_format = workbook.add_format({'num_format': 'mm/dd/yy'})\n",
    "    \n",
    "    def __write_val(sheet, field, row, row_pos, col_pos):\n",
    "        # A little helper func for dealing with different data types\n",
    "        if field == 'Date':\n",
    "            # Write the date to cell (col_pos, row_pos) i.e. (A1)\n",
    "            sheet.write_datetime(\n",
    "                row_pos,\n",
    "                col_pos,\n",
    "                datetime.datetime.strptime(row['Date'], date_format),\n",
    "                excel_date_format\n",
    "            )\n",
    "        elif field == 'Volume':\n",
    "            # Volume is an int, so we will write it as such\n",
    "            sheet.write_number(row_pos, col_pos, int(row[field]))\n",
    "        else:\n",
    "            sheet.write_number(\n",
    "                row_pos,\n",
    "                col_pos,\n",
    "                # From the aside\n",
    "                decimal.Decimal(str(round(float(row[field]), 2)))\n",
    "            )\n",
    "    # Add the worksheets in sorted order\n",
    "    for ticker in sorted(data.keys()):\n",
    "        # Just name the worksheet after the stock\n",
    "        worksheet = workbook.add_worksheet(ticker)\n",
    "        row_pos = 0\n",
    "        col_pos = 0\n",
    "        # Write the header to the sheet\n",
    "        for field in header:\n",
    "            worksheet.write(row_pos, col_pos, field)\n",
    "            col_pos += 1\n",
    "        row_pos += 1\n",
    "        \n",
    "        # Sort the dates in descending order\n",
    "        for row in sorted(data[ticker],\n",
    "            key=lambda r: datetime.datetime.strptime(r['Date'], date_format)):\n",
    "                col_pos = 0\n",
    "                for field in header:\n",
    "                    __write_val(worksheet, field, row, row_pos, col_pos)\n",
    "                    col_pos += 1\n",
    "                row_pos += 1\n",
    "\n",
    "    # And finally close the workbook\n",
    "    workbook.close()\n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# And let's try it\n",
    "out_file = 'ticker_prices.xlsx'\n",
    "write_xlsx(weekly_data, out_file)\n",
    "# Should now have ticker_prices.xlsx in your working directory\n",
    "import os\n",
    "# Just a little check to see if the file is there\n",
    "assert(out_file in os.listdir('.'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusion\n",
    "\n",
    "Open ticker_prices.xlsx up in excel and see the results. Feel free to tweak this notebook and experiment with it!\n",
    "\n",
    "If we wanted to build a portfolio testing (backtesting) framework, we don't want to have to keep querying for data.\n",
    "* Solution: Build a transparent wrapper around our queries to fetch them from a local cache if they exist, otherwise reach out to Yahoo Finance\n",
    " * A bit of overhead when populating the database, but much faster going forward"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}