{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yahoo Finance quotes\n",
    "\n",
    "We'd like to get the latest oil price. We can use Yahoo Finance. There are two ways: get CSV data from `download.finance.yahoo.com` or use `query.yahooapis.com` to do the same thing but parse it into JSON.\n",
    "\n",
    "## Quick and dirty CSV\n",
    "\n",
    "[Here is an explanation](http://mdbitz.com/2010/02/02/understanding-yahoo-finance-stock-quotes-and-sl1d1t1c1ohgv/) of the `f` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "symbol = 'CLF17.NYM'\n",
    "url = \"http://download.finance.yahoo.com/d/quotes.csv\"\n",
    "params = {'s': symbol, 'f': 'l1'}\n",
    "\n",
    "r = requests.get(url, params=params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "48.02\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    print(float(r.text))\n",
    "except ValueError:\n",
    "    print(r.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# If we get more data, eg f: sl1d1t1 then we can\n",
    "# import csv\n",
    "# data = list(csv.reader([r.text]))[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Benchmarks that seem to work with this service:\n",
    "\n",
    "* CL — West Texas Intermediate or WTI, light sweet crude\n",
    "* BB — Brent crude penultimate financial futures\n",
    "* BZ — Brent look-alike crude oil futures\n",
    "* MB — Gulf Coast Sour Crude\n",
    "* RE — Russian Export Blend Crude Oil (REBCO) futures\n",
    "\n",
    "Gas spot prices that work:\n",
    "\n",
    "* NG — Henry Hub physical futures\n",
    "* HH — Henry Hub last day financial futures\n",
    "\n",
    "Symbols that don't work:\n",
    "\n",
    "* DC — Dubai crude calendar futures\n",
    "* WCC — Canadian Heavy (differential, cf CL)\n",
    "* WCE — Western Canadian select crude oil futures (differential, cf CL) — but this seems to be the same price as WCC, which can't be right\n",
    "* LN — European options"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parse into JSON\n",
    "\n",
    "This does more or less the same thing, but we wrap it in another service to get at the JSON. We might do this when things start to get more complicated, like..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\"BZX16.NYM\",48.54,\"9/7/2016\",\"6:03pm\"\\n'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "symbol = 'BZX16.NYM'\n",
    "url = \"http://download.finance.yahoo.com/d/quotes.csv\"\n",
    "params = {'s': symbol, 'f': 'sl1d1t1'}\n",
    "\n",
    "r = requests.get(url, params=params)\n",
    "r.text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll use the url that `requests` formed, above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://download.finance.yahoo.com/d/quotes.csv?f=sl1d1t1&s=BZX16.NYM'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = \"select * from csv\"\n",
    "query += \" where url='{}' and\".format(r.url)\n",
    "query += \" columns='symbol,price,data,time'\"  # corresponds to f parameter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "url = \"https://query.yahooapis.com/v1/public/yql\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "env = \"http://datatables.org/alltableswithkeys.env\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "params = {\n",
    "    'q': query,\n",
    "    'format': 'json',\n",
    "    'env': env,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "r = requests.get(url, params=params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "j = r.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'query': {'count': 1,\n",
       "  'created': '2016-09-07T22:30:39Z',\n",
       "  'lang': 'en-US',\n",
       "  'results': {'row': {'data': '9/7/2016',\n",
       "    'price': '48.54',\n",
       "    'symbol': 'BZX16.NYM',\n",
       "    'time': '6:03pm'}}}}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "price = float(j['query']['results']['row']['price'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "48.54"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Forming symbols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's the symbol? Crude futures symbols are funky, e.g.\n",
    "\n",
    "* CL — West Texas Intermediate or WTI, light sweet crude\n",
    "* BZ — Brent look-alike crude oil futures\n",
    "* MB — Gulf Coast Sour Crude\n",
    "* BB — Brent crude penultimate financial futures\n",
    "* NG — Henry Hub physical futures\n",
    "* HH — Henry Hub last day financial futures\n",
    "\n",
    "The ticker symbols we're passing look like XXMYY.NYM, with components as follows:\n",
    "\n",
    "* `XX` — commodity symbol, as above: CL, BB, etc.\n",
    "* `M` — a month code, symbolizing January to December: `[F,G,H,J,K,M,N,Q,U,V,X,Z]`\n",
    "* `YY` — a two-digit year, like 13.\n",
    "* `.NYM` — the Nymex symbol."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "def get_symbol(benchmark):\n",
    "    # We compute a time 45 days in the future for a price\n",
    "    future = time.gmtime(time.time() + 45*24*60*60)\n",
    "    month = future.tm_mon\n",
    "    month_codes = ['F', 'G', 'H', 'J', 'K', 'M', 'N', 'Q', 'U', 'V', 'X', 'Z']\n",
    "    month = month_codes[month - 1]\n",
    "    year = str(future.tm_year)[-2:]\n",
    "\n",
    "    symbol = benchmark + month + year + \".NYM\"\n",
    "    return symbol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'WCCV16.NYM'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_symbol('WCC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}