nb/SEC-13F-parse.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parsing SEC 13F forms\n",
    "\n",
    "We examine 13F filings which are quarterly reports filed per SEC regulations\n",
    "by institutional investment managers containing all equity assets under management\n",
    "of at least \\$ 100 million in value. **Form 13F is required to be filed\n",
    "within 45 days of the end of a calendar quarter**\n",
    "(*which should be considered as significant information latency*).\n",
    "\n",
    "Form 13F *only reports long* positions.\n",
    "Short positions are not required to be disclosed and are not reported.\n",
    "Section 13(f) securities generally include equity securities\n",
    "that trade on an exchange (including Nasdaq), certain equity options and warrants,\n",
    "shares of closed-end investment companies, and certain convertible debt securities.\n",
    "The shares of open-end investment companies\n",
    "(i.e. mutual funds) are not Section 13(f) securities.\n",
    "See [Official List of Section 13(f) Securities](http://www.sec.gov/divisions/investment/13flists.htm) and our caveats section below.\n",
    "\n",
    "Form 13F surprisingly excludes total portfolio value and percentage allocation\n",
    "of each stock listed. We remedy that, and also parse the report for easy reading.\n",
    "Our notebook then develops into a module **yi_secform** which will do\n",
    "all the work via one function.\n",
    "\n",
    "As specific example, we follow Druckenmiller and Paulson as asset managers\n",
    "who have significant positions in GLD, a gold ETF.\n",
    "We show the Druckenmiller's sudden accumulation,\n",
    "and Paulson's dramatic liquidation.\n",
    "\n",
    "*Top holdings are easily analyzed by a single Python module:* **yi_secform**\n",
    "Caveats are disclosed in the first section.\n",
    "\n",
    "Shortcut to this notebook: https://git.io/13F"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Dependencies:*\n",
    "\n",
    "- Repository: https://github.com/rsvp/fecon235 -- Module: yi_secform\n",
    "- Python: pandas, numpy, lxml, bs4, html5lib\n",
    "     \n",
    "*CHANGE LOG*\n",
    "\n",
    "    2016-02-22  Fix issue #2 by v4 and p6 updates.\n",
    "                   Replace .sort(columns=...) with .sort_values(by=...)\n",
    "                   since pandas 0.17.1 gives us future deprecation warning.\n",
    "                   Paulson liquidates 37.6% of his GLD inventory.\n",
    "    2015-11-16  Update in Appendix for Druckenmiller and Paulson.\n",
    "    2015-08-28  First version."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from fecon235.fecon235 import *\n",
    "\n",
    "#  pandas will give best results\n",
    "#  if it can call the Python package: lxml,\n",
    "#  and as a fallback: bs4 and html5lib.\n",
    "#  They parse (non-strict) XML and HTML pages.\n",
    "#  Be sure those three packages are pre-installed.\n",
    "\n",
    "from fecon235.lib import yi_secform\n",
    "#  We are going to derive this module in this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " ::  Python 2.7.11\n",
      " ::  IPython 4.0.0\n",
      " ::  jupyter 1.0.0\n",
      " ::  notebook 4.0.6\n",
      " ::  matplotlib 1.4.3\n",
      " ::  numpy 1.10.1\n",
      " ::  pandas 0.17.1\n",
      " ::  pandas_datareader 0.2.0\n",
      " ::  Repository: fecon235 v4.16.0221 develop\n",
      " ::  Timestamp: 2016-02-23, 19:11:16 UTC\n",
      " ::  $pwd: /media/yaya/virt15h/virt/dbx/Dropbox/ipy/fecon235/nb\n"
     ]
    }
   ],
   "source": [
    "#  PREAMBLE-p6.15.1223 :: Settings and system details\n",
    "from __future__ import absolute_import, print_function\n",
    "system.specs()\n",
    "pwd = system.getpwd()   # present working directory as variable.\n",
    "print(\" ::  $pwd:\", pwd)\n",
    "#  If a module is modified, automatically reload it:\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "#       Use 0 to disable this feature.\n",
    "\n",
    "#  Notebook DISPLAY options:\n",
    "#      Represent pandas DataFrames as text; not HTML representation:\n",
    "import pandas as pd\n",
    "pd.set_option( 'display.notebook_repr_html', False )\n",
    "#  Beware, for MATH display, use %%latex, NOT the following:\n",
    "#                   from IPython.display import Math\n",
    "#                   from IPython.display import Latex\n",
    "from IPython.display import HTML # useful for snippets\n",
    "#  e.g. HTML('<iframe src=http://en.mobile.wikipedia.org/?useformat=mobile width=700 height=350></iframe>')\n",
    "from IPython.display import Image \n",
    "#  e.g. Image(filename='holt-winters-equations.png', embed=True) # url= also works\n",
    "from IPython.display import YouTubeVideo\n",
    "#  e.g. YouTubeVideo('1j_HxD4iLn8', start='43', width=600, height=400)\n",
    "from IPython.core import page\n",
    "get_ipython().set_hook('show_in_pager', page.as_hook(page.display_page), 0)\n",
    "#  Or equivalently in config file: \"InteractiveShell.display_page = True\", \n",
    "#  which will display results in secondary notebook pager frame in a cell.\n",
    "\n",
    "#  Generate PLOTS inside notebook, \"inline\" generates static png:\n",
    "%matplotlib inline   \n",
    "#          \"notebook\" argument allows interactive zoom and resize."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Caveats regarding 13F\n",
    "\n",
    "- 13F filings disclose hedge fund long positions in US equity markets, American Depositary Receipts (ADRs), both put and call options, as well as convertible notes. **They do not disclose short sales, cash positions, or any other asset class.**\n",
    "\n",
    "- Yet performance results for the [clones](http://blog.alphaclone.com/alphaclone/2011/01/clone-vs-fund-2010.html) are somewhat in line with the actual performance of the fund. E.g. Paulson & Co (John Paulson)\n",
    "\n",
    "- Clones track managers that normally run net long. Tracking global macro funds (Bridgewater, Tudor) or credit funds (Fortress, Cerberus) is misguided because the vast majority of their positions are in asset classes that they don't have to disclose (futures, commodities, bonds, currencies, etc).  And while quant funds (RenTec, AQR) often disclose stocks, following them is a folly because you have absolutely no idea why their algorithms bought in the first place (statistical arbitrage may entail positions in foreign countries).  \n",
    "\n",
    "- 13F does not reveal international holdings (except for ADR's).\n",
    "\n",
    "- Follow long-term oriented funds to reduce the effect of the delay in 13F disclosures.\n",
    "\n",
    "- Money managers allocate the most capital to their best ideas. Pay attention to \"new positions\" in their disclosures as these are their most recent ideas. \n",
    "\n",
    "- Always remember that the **13F is not their whole portfolio and that it's a past snapshot.**\n",
    "\n",
    "- Monitor all SEC filings, not just 13F's:  13G filings, 13D filings, as well as various Form 3 and Form 4's are filed on a more timely basis and provide a more current look at what managers are buying or selling.  They are required to file when they've purchased 5% or more of a company. \n",
    "\n",
    "- Caveats source: http://www.marketfolly.com/2012/10/hedge-fund-13f-filing-pros-and-cons.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis of the 13F format\n",
    "\n",
    "Source: http://www.sec.gov/answers/form13f.htm\n",
    "\n",
    "\n",
    "### Obtaining 13F\n",
    "\n",
    "You can search for and retrieve Form 13F filings using the [SEC's EDGAR database](http://www.sec.gov/edgar/searchedgar/companysearch.html). To find the filings of a particular money manager, enter the money manager's name in the Company Name field. To see all recently filed 13Fs, use the [\"Latest Filings\"](http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent) search function and enter \"13F\" in the Form Type box.\n",
    "\n",
    "\n",
    "### Example: Druckenmiller, 14 August 2015 filing\n",
    "\n",
    "Stanley Druckenmiller closed his Duquesne Capital Management hedge fund in 2010 but he still discloses the holdings of his family office (search: [“Duquesne Family Office”](http://www.sec.gov/cgi-bin/browse-edgar?company=Duquesne+Family+Office&owner=exclude&action=getcompany) picking the Information Table in html).\n",
    "\n",
    "He made headlines because of his new acquisition in gold (the GLD ETF) which now is his largest position (about 22% of his entire portfolio)!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#            https cannot be read by lxml, surprisingly.\n",
    "druck150814='http://www.sec.gov/Archives/edgar/data/1536411/000153641115000006/xslForm13F_X01/form13f_20150630.xml'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## HOWTO parse 13F reports: deriving our module\n",
    "\n",
    "If bugs appear because of format changes at the SEC\n",
    "this section will useful for interactive debugging.\n",
    "\n",
    "Take note that: **lxml** *cannot read https, so use http\n",
    "when specifying URL.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#     START HERE with a particular URL:\n",
    "url = druck150814"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe src=http://www.sec.gov/Archives/edgar/data/1536411/000153641115000006/xslForm13F_X01/form13f_20150630.xml width=1400 height=350></iframe>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Let's display the web page as in the browser to understand the semantics:\n",
    "HTML(\"<iframe src=\" + url + \" width=1400 height=350></iframe>\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  Use pandas to read in the xml page...\n",
    "#  See http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_html.html\n",
    "\n",
    "#  It searches for <table> elements and only for <tr> and <th> rows and <td> elements \n",
    "#  within each <tr> or <th> element in the table.\n",
    "\n",
    "page = pd.read_html( url )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  Nasty output in full:\n",
    "\n",
    "#uncomment:  page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  page is a list of length 4:\n",
    "len( page )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  But only the last element of page interests us:\n",
    "df = page[-1]\n",
    "#  which turns out to be a dataframe!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#  Let's rename columns for our sanity:\n",
    "df.columns = [ 'stock', 'class', 'cusip', 'usd', 'size', 'sh_prin', 'putcall', 'discret', 'manager', 'vote1', 'vote2', 'vote3'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                            stock           class      cusip     usd     size  \\\n",
       "0                 Akamai Tech Inc             COM  00971T101   24807   355300   \n",
       "1                       Alcoa Inc             COM  013817101   40698  3650000   \n",
       "2          Alibaba Group Hldg Ltd       SPONS ADS  01609W102   18025   219100   \n",
       "3                Am Int'l Grp Inc             COM  026874784   15474   250300   \n",
       "4                       BB&T Corp             COM  054937107   19575   485600   \n",
       "5                   Citigroup Inc             COM  172967424   64907  1175000   \n",
       "6       Cognizant Tech Sol's Corp            CL A  192446102   14466   236800   \n",
       "7             Ctrip.com Int'l Ltd      AM DEP SHS  22943F100   14626   201400   \n",
       "8                 Dow Chemical Co             COM  260543103   59526  1163300   \n",
       "9                   EOG Res's Inc             COM  26875P101   27219   310900   \n",
       "10            Eastman Chemical Co             COM  277432100   15219   186000   \n",
       "11                       EBay Inc             COM  278642103   19698   327000   \n",
       "12                   Facebook Inc            CL A  30303M102  160612  1872700   \n",
       "13           Freeport-McMoRan Inc            CL B  35671D857   66045  3547000   \n",
       "14                  HDFC Bank Ltd  ADR REPS 3 SHS  40415F101   60748  1003600   \n",
       "15                 Halliburton Co             COM  406216101   66629  1547000   \n",
       "16                   Illumina Inc             COM  452327109   45091   206500   \n",
       "17           JP Morgan Chase & Co             COM  46625H100   52670   777300   \n",
       "18                     JD.Com Inc   SPON ADR CL A  47215P106   20221   593000   \n",
       "19                 Microsoft Corp             COM  594918104   61726  1398100   \n",
       "20            Newmont Mining Corp             COM  651639106   29898  1279900   \n",
       "21               Noble Energy Inc             COM  655044105   13453   315200   \n",
       "22  PNC Fin'l Services Group, Inc             COM  693475105   19570   204600   \n",
       "23         Pioneer Nat'l Res's Co             COM  723787107   15922   114800   \n",
       "24                SPDR Gold Trust        GOLD SHS  78463V907  323626  2880000   \n",
       "25             SunTrust Banks Inc             COM  867914103   19664   457100   \n",
       "26               Wells Fargo & Co             COM  949746101   94449  1679400   \n",
       "27                    Workday Inc            CL A  98138H101   25261   330679   \n",
       "28        LyondellBasell Ind's NV       SHS - A -  N53745100   74219   716950   \n",
       "\n",
       "   sh_prin putcall discret manager    vote1 vote2 vote3  \n",
       "0       SH     NaN    DFND     NaN   355300     0     0  \n",
       "1       SH     NaN    DFND     NaN  3650000     0     0  \n",
       "2       SH     NaN    DFND     NaN   219100     0     0  \n",
       "3       SH     NaN    DFND     NaN   250300     0     0  \n",
       "4       SH     NaN    DFND     NaN   485600     0     0  \n",
       "5       SH     NaN    DFND     NaN  1175000     0     0  \n",
       "6       SH     NaN    DFND     NaN   236800     0     0  \n",
       "7       SH     NaN    DFND     NaN   201400     0     0  \n",
       "8       SH     NaN    DFND     NaN  1163300     0     0  \n",
       "9       SH     NaN    DFND     NaN   310900     0     0  \n",
       "10      SH     NaN    DFND     NaN   186000     0     0  \n",
       "11      SH     NaN    DFND     NaN   327000     0     0  \n",
       "12      SH     NaN    DFND     NaN  1872700     0     0  \n",
       "13      SH     NaN    DFND     NaN  3547000     0     0  \n",
       "14      SH     NaN    DFND     NaN  1003600     0     0  \n",
       "15      SH     NaN    DFND     NaN  1547000     0     0  \n",
       "16      SH     NaN    DFND     NaN   206500     0     0  \n",
       "17      SH     NaN    DFND     NaN   777300     0     0  \n",
       "18      SH     NaN    DFND     NaN   593000     0     0  \n",
       "19      SH     NaN    DFND     NaN  1398100     0     0  \n",
       "20      SH     NaN    DFND     NaN  1279900     0     0  \n",
       "21      SH     NaN    DFND     NaN   315200     0     0  \n",
       "22      SH     NaN    DFND     NaN   204600     0     0  \n",
       "23      SH     NaN    DFND     NaN   114800     0     0  \n",
       "24      SH     NaN    DFND     NaN  2880000     0     0  \n",
       "25      SH     NaN    DFND     NaN   457100     0     0  \n",
       "26      SH     NaN    DFND     NaN  1679400     0     0  \n",
       "27      SH     NaN    DFND     NaN   330679     0     0  \n",
       "28      SH     NaN    DFND     NaN   716950     0     0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  But first three rows are SEC labels, not data, \n",
    "#  so delete them:\n",
    "df = df[3:]\n",
    "\n",
    "#  Start a new index from 0 instead of 3:\n",
    "df.reset_index( drop=True )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  Delete irrevelant columns:\n",
    "dflite = df.drop( df.columns[[1, 4, 5, 7, 8, 9, 10, 11]], axis=1 )\n",
    "#         inplac=True only after pandas 0.13\n",
    "#uncomment: dflite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  usd needs float type since usd was read as string:\n",
    "dflite[['usd']] = dflite[['usd']].astype( float )\n",
    "#                  Gotcha: int as type will fail for NaN\n",
    "\n",
    "#  Type change allows proper sort:\n",
    "dfusd = dflite.sort_values( by=['usd'], ascending=[False] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1484044.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "usdsum = sum( dfusd.usd )\n",
    "#  Portfolio total in USD:\n",
    "usdsum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  New column for percentage of total portfolio:\n",
    "dfusd['pcent'] = np.round(( dfusd.usd / usdsum ) * 100, 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                      stock      cusip     usd putcall  pcent\n",
       "27          SPDR Gold Trust  78463V907  323626     NaN  21.81\n",
       "15             Facebook Inc  30303M102  160612     NaN  10.82\n",
       "29         Wells Fargo & Co  949746101   94449     NaN   6.36\n",
       "31  LyondellBasell Ind's NV  N53745100   74219     NaN   5.00\n",
       "18           Halliburton Co  406216101   66629     NaN   4.49\n",
       "16     Freeport-McMoRan Inc  35671D857   66045     NaN   4.45\n",
       "8             Citigroup Inc  172967424   64907     NaN   4.37\n",
       "22           Microsoft Corp  594918104   61726     NaN   4.16\n",
       "17            HDFC Bank Ltd  40415F101   60748     NaN   4.09\n",
       "11          Dow Chemical Co  260543103   59526     NaN   4.01\n",
       "20     JP Morgan Chase & Co  46625H100   52670     NaN   3.55\n",
       "19             Illumina Inc  452327109   45091     NaN   3.04\n",
       "4                 Alcoa Inc  013817101   40698     NaN   2.74\n",
       "23      Newmont Mining Corp  651639106   29898     NaN   2.01\n",
       "12            EOG Res's Inc  26875P101   27219     NaN   1.83\n",
       "30              Workday Inc  98138H101   25261     NaN   1.70\n",
       "3           Akamai Tech Inc  00971T101   24807     NaN   1.67\n",
       "21               JD.Com Inc  47215P106   20221     NaN   1.36\n",
       "14                 EBay Inc  278642103   19698     NaN   1.33\n",
       "28       SunTrust Banks Inc  867914103   19664     NaN   1.33"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Top 20 Hits!\n",
    "dfusd.head( 20 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Summary module: yi_secform\n",
    "\n",
    "**We sum up our work above in a module for easy execution using one function.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[1;31mSignature: \u001b[0m\u001b[0myi_secform\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpcent13f\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0murl\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'http://www.sec.gov/Archives/edgar/data/1536411/000153641115000006/xslForm13F_X01/form13f_20150630.xml'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtop\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m7654321\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
       "\u001b[1;31mSource:\u001b[0m\n",
       "def pcent13f( url=druck150814, top=7654321 ):\n",
       "     '''Prune, then sort SEC 13F by percentage allocation, showing top N.\n",
       "     >>> pcent13f( top= 7 )\n",
       "                           stock      cusip     usd putcall  pcent\n",
       "     27          SPDR Gold Trust  78463V907  323626     NaN  21.81\n",
       "     15             Facebook Inc  30303M102  160612     NaN  10.82\n",
       "     29         Wells Fargo & Co  949746101   94449     NaN   6.36\n",
       "     31  LyondellBasell Ind's NV  N53745100   74219     NaN   5.00\n",
       "     18           Halliburton Co  406216101   66629     NaN   4.49\n",
       "     16     Freeport-McMoRan Inc  35671D857   66045     NaN   4.45\n",
       "     8             Citigroup Inc  172967424   64907     NaN   4.37\n",
       "     '''\n",
       "     df = parse13f( url )\n",
       "     #    Drop irrevelant COLUMNS:\n",
       "     df.drop( df.columns[[1, 4, 5, 7, 8, 9, 10, 11]], axis=1, inplace=True )\n",
       "     #                    inplace=True available after pandas 0.13\n",
       "     #\n",
       "     #  Convert usd to float type since it was read as string:\n",
       "     df[['usd']] = df[['usd']].astype( float )\n",
       "     #                 Gotcha: int as type will fail for NaN !\n",
       "     #  Also we need float anyways for Python2 division later.\n",
       "     #\n",
       "     #  Sort holdings by dollar value:\n",
       "     dfusd = df.sort_values( by=['usd'], ascending=[False] )\n",
       "     #         .sort(columns=...) to be deprecated per pandas 0.17.1\n",
       "     #  Sum total portfolio in USD:\n",
       "     usdsum = sum( dfusd.usd )\n",
       "     #\n",
       "     #  NEW COLUMN 'pcent' for percentage of total portfolio:\n",
       "     dfusd['pcent'] = np.round(( dfusd.usd / usdsum ) * 100, 2)\n",
       "     #\n",
       "     #          Selects top N positions from the portfolio:\n",
       "     return dfusd.head( top )\n",
       "\u001b[1;31mFile:      \u001b[0m~/Dropbox/ipy/fecon235/lib/yi_secform.py\n",
       "\u001b[1;31mType:      \u001b[0mfunction"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "yi_secform.pcent13f??"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                      stock      cusip     usd putcall  pcent\n",
       "27          SPDR Gold Trust  78463V907  323626     NaN  21.81\n",
       "15             Facebook Inc  30303M102  160612     NaN  10.82\n",
       "29         Wells Fargo & Co  949746101   94449     NaN   6.36\n",
       "31  LyondellBasell Ind's NV  N53745100   74219     NaN   5.00\n",
       "18           Halliburton Co  406216101   66629     NaN   4.49\n",
       "16     Freeport-McMoRan Inc  35671D857   66045     NaN   4.45\n",
       "8             Citigroup Inc  172967424   64907     NaN   4.37\n",
       "22           Microsoft Corp  594918104   61726     NaN   4.16\n",
       "17            HDFC Bank Ltd  40415F101   60748     NaN   4.09\n",
       "11          Dow Chemical Co  260543103   59526     NaN   4.01\n",
       "20     JP Morgan Chase & Co  46625H100   52670     NaN   3.55\n",
       "19             Illumina Inc  452327109   45091     NaN   3.04\n",
       "4                 Alcoa Inc  013817101   40698     NaN   2.74\n",
       "23      Newmont Mining Corp  651639106   29898     NaN   2.01\n",
       "12            EOG Res's Inc  26875P101   27219     NaN   1.83\n",
       "30              Workday Inc  98138H101   25261     NaN   1.70\n",
       "3           Akamai Tech Inc  00971T101   24807     NaN   1.67\n",
       "21               JD.Com Inc  47215P106   20221     NaN   1.36\n",
       "14                 EBay Inc  278642103   19698     NaN   1.33\n",
       "28       SunTrust Banks Inc  867914103   19664     NaN   1.33"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yi_secform.pcent13f( druck150814, 20 )\n",
    "\n",
    "#  Simply enter the Information Table html URL for a 13F filing, \n",
    "#  and bang... [verifying our output in the previous cell]:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quick look at John Paulson\n",
    "\n",
    "For comparison, since the press has been stating Paulson has been selling the GLD ETF due to redemptions. 13F search page: http://www.sec.gov/cgi-bin/browse-edgar?company=Paulson+%26+Co.&owner=exclude&action=getcompany"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#  13F for Paulson & Co. filed 2015-08-14:\n",
    "paulson150814 = 'http://www.sec.gov/Archives/edgar/data/1035674/000114036115032242/xslForm13F_X01/form13fInfoTable.xml'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                           stock      cusip      usd putcall  pcent\n",
       "4                   ALLERGAN PLC  G0177J108  2177083     NaN  10.04\n",
       "59  VALEANT PHARMACEUTICALS INTL  91911K102  1999350     NaN   9.22\n",
       "49                     SHIRE PLC  82481R106  1713396     NaN   7.90\n",
       "36                     MYLAN N V  N59465109  1487020     NaN   6.86\n",
       "56         TIME WARNER CABLE INC  88732J207  1447275     NaN   6.67\n",
       "19                       DIRECTV  25490A309  1057806     NaN   4.88\n",
       "50               SPDR GOLD TRUST  78463V107  1037720     NaN   4.79\n",
       "52  STARWOOD HOTELS&RESORTS WRLD  85590A401   973031     NaN   4.49\n",
       "58               T-MOBILE US INC  872590104   924467     NaN   4.26\n",
       "5        AMERICAN INTL GROUP INC  026874784   902770     NaN   4.16\n",
       "21        EXTENDED STAY AMER INC  30224P200   894608     NaN   4.13\n",
       "32       MALLINCKRODT PUB LTD CO  G5785G107   776268     NaN   3.58\n",
       "23                   GRIFOLS S A  398438408   551918     NaN   2.55\n",
       "25              HCA HOLDINGS INC  40412C101   427464     NaN   1.97\n",
       "61         WHITING PETE CORP NEW  966387102   417258     NaN   1.92\n",
       "13        COBALT INTL ENERGY INC  19075F106   405403     NaN   1.87\n",
       "48            REALOGY HLDGS CORP  75605Y106   329016     NaN   1.52\n",
       "27  HOUGHTON MIFFLIN HARCOURT CO  44157R109   308176     NaN   1.42\n",
       "12          CABLEVISION SYS CORP  12686C109   296856     NaN   1.37\n",
       "16        COMPUTER SCIENCES CORP  205363104   278642     NaN   1.28"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yi_secform.pcent13f( paulson150814, 20 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gold ETF comment, August 2015\n",
    "\n",
    "Paulson is usually the largest stakeholder in GLD also known as \"SPDR GOLD TRUST\". The latest 13F shows he holds \\$1.037 billion worth which is equivalent to about 886,183 troy ounces (*27.6 metric tons*). It actually only represents half of his very top equity holding: Allergan at 10% allocation.\n",
    "\n",
    "Druckenmiller's 13F disclosing his Family Office (not a public operation like Paulson & Co) shows a \\$0.324 billion position in GLD, equivalent to about 276,367 troy ounces (*8.6 metric tons*). It appears to be a new bold trade since it is his largest holding at 22% allocation. \n",
    "\n",
    "[Spot gold on 2015-06-30, end of second calendar quarter, was fixed at \\$1171 in London.]\n",
    "\n",
    "*We do not truly know their respective net positions because they **could be hedged in the gold futures market.** Futures positions are excluded from 13F filings, as well as cash position (which would fund any further accumulation).*\n",
    "\n",
    "Remember that we viewing past snapshots of positions dated 30 June 2015. Such positions could have been entirely liquidated during July and August when the gold market declined severely. On the other hand, gold rallied considerably days after the market received Druckenmiller's vote of bold confidence via his 13F filed on August 14th. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Appendix: Getting quotes\n",
    "\n",
    "GLD, the ETF for gold, is designed to track spot gold prices,\n",
    "less their management fees.\n",
    "Within fecon235, one can easily retrieve stock or ETF quotes,\n",
    "for example, by this syntax: get('s4gld'),\n",
    "noting the string 's4' concatenated with the symbol\n",
    "in lower case.\n",
    "\n",
    "Notice that the SEC 13F requires unique CUSIP identifiers,\n",
    "rather than ticker symbols for their 13F forms.\n",
    "\n",
    "For gold, we prefer to use equivalent measures used in the spot market.\n",
    "For example, quotes here are given in USD per troy ounce,\n",
    "and the London PM fix (rather than nearby futures) is\n",
    "accepted as a benchmark.\n",
    "GLD valuation can be converted into such spot terms.\n",
    "Within fecon235: get(d4xau)\n",
    "will retrieve the appropriate dataframe for you.\n",
    "See https://git.io/gold for more details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Appendix: November 2015 update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "druck151113 = 'http://www.sec.gov/Archives/edgar/data/1536411/000153641115000008/xslForm13F_X01/form13f_20150930.xml'\n",
    "paulson151116 = 'http://www.sec.gov/Archives/edgar/data/1035674/000114036115041689/xslForm13F_X01/form13fInfoTable.xml'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                        stock      cusip     usd putcall  pcent\n",
       "9                Facebook Inc  30303M102  367988     NaN  31.29\n",
       "17            SPDR Gold Trust  78463V907  307757    Call  26.17\n",
       "13             Microsoft Corp  594918104   61477     NaN   5.23\n",
       "10              HDFC Bank Ltd  40415F101   61310     NaN   5.21\n",
       "19           Wells Fargo & Co  949746101   61050     NaN   5.19\n",
       "3              Amazon.com Inc  023135106   49807     NaN   4.23\n",
       "5         Ctrip.com Int'l Ltd  22943F100   30364     NaN   2.58\n",
       "20                Workday Inc  98138H101   30187     NaN   2.57\n",
       "12                 JD.Com Inc  47215P106   28103     NaN   2.39\n",
       "11               Illumina Inc  452327109   27393     NaN   2.33\n",
       "18          Under Armour, Inc  904311107   25143     NaN   2.14\n",
       "8                 Expedia Inc  30212P303   20370     NaN   1.73\n",
       "4               Carnival Corp  143658300   19303     NaN   1.64\n",
       "16        Priceline Group Inc  741503403   19295     NaN   1.64\n",
       "21  Royal Carib'n Cruises Ltd  V7780T103   19021     NaN   1.62\n",
       "14        PayPal Holdings Inc  70450Y103   17544     NaN   1.49\n",
       "15     Pioneer Nat'l Res's Co  723787107   10060     NaN   0.86\n",
       "7           EOG Resources Inc  26875P101   10046     NaN   0.85\n",
       "6             Dow Chemical Co  260543103    9998     NaN   0.85"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Druckenmiller 13F for 2015-11-13:\n",
    "yi_secform.pcent13f( druck151113, 20 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                           stock      cusip      usd putcall  pcent\n",
       "5                   ALLERGAN PLC  G0177J108  1952139     NaN  10.13\n",
       "63  VALEANT PHARMACEUTICALS INTL  91911K102  1585798     NaN   8.23\n",
       "61         TIME WARNER CABLE INC  88732J207  1457023     NaN   7.56\n",
       "54                     SHIRE PLC  82481R106  1396631     NaN   7.25\n",
       "56  STARWOOD HOTELS&RESORTS WRLD  85590A401  1036756     NaN   5.38\n",
       "59  TEVA PHARMACEUTICAL INDS LTD  881624209  1016280     NaN   5.28\n",
       "55               SPDR GOLD TRUST  78463V107   986836     NaN   5.12\n",
       "62               T-MOBILE US INC  872590104   937430     NaN   4.87\n",
       "38                     MYLAN N V  N59465109   882220     NaN   4.58\n",
       "7        AMERICAN INTL GROUP INC  026874784   829754     NaN   4.31\n",
       "24        EXTENDED STAY AMER INC  30224P200   799761     NaN   4.15\n",
       "26                   GRIFOLS S A  398438408   538966     NaN   2.80\n",
       "45                PERRIGO CO PLC  G97822103   462138     NaN   2.40\n",
       "34       MALLINCKRODT PUB LTD CO  G5785G107   421633     NaN   2.19\n",
       "27              HCA HOLDINGS INC  40412C101   364513     NaN   1.89\n",
       "16        COBALT INTL ENERGY INC  19075F106   295598     NaN   1.53\n",
       "15                 CIT GROUP INC  125581801   276998     NaN   1.44\n",
       "52            REALOGY HLDGS CORP  75605Y106   265002     NaN   1.38\n",
       "11          CABLEVISION SYS CORP  12686C109   253266     NaN   1.31\n",
       "48      PRECISION CASTPARTS CORP  740189105   229710     NaN   1.19"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Paulson 13F for 2015-11-16:\n",
    "yi_secform.pcent13f( paulson151116, 20 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Spot gold on 2015-09-30, end of third calendar quarter, was fixed \n",
    "at $1114 in London, -4.9% from previous quarter.]\n",
    "\n",
    "2015-11-16: GLD is no longer Druckenmiller's top holding (replaced by Facebook): \n",
    "change from last quarter \"323626 NaN 21.81\" to \"307757 Call 26.17\" -- \n",
    "however, its allocation has been increased, though net position is \n",
    "unchanged (given price decrease in the spot market). \n",
    "Curiously it appears the underlying instrument has *shifted to calls*. \n",
    "The expiration date of the calls are not known, but it is \n",
    "indicative of a shift to short-term trading perspective. \n",
    "Druckenmiller's gold downside is now limited to the premiums paid.\n",
    "\n",
    "As for Paulson: change from last quarter: \"1037720 NaN 4.79\" to \"986836 NaN 5.12\" -- \n",
    "indicates *no change in position* since the GLD valuation \n",
    "mirrors the decrease in the spot gold market."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Appendix: February 2016 update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "druck160216='http://www.sec.gov/Archives/edgar/data/1536411/000153641116000010/xslForm13F_X01/form13f_20151231.xml'\n",
    "paulson160216='http://www.sec.gov/Archives/edgar/data/1035674/000114036116053318/xslForm13F_X01/form13fInfoTable.xml'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                      stock      cusip     usd putcall  pcent\n",
       "13          SPDR Gold Trust  78463V907  292205    Call  29.90\n",
       "7             Facebook Inc.  30303M102  264842     NaN  27.10\n",
       "4           Amazon.com Inc.  023135106  128419     NaN  13.14\n",
       "9            Microsoft Corp  594918104   64634     NaN   6.61\n",
       "12              Raytheon Co  755111507   52029     NaN   5.32\n",
       "6      Ctrip.com Int'l Ltd.  22943F100   35781     NaN   3.66\n",
       "8            HDFC Bank Ltd.  40415F101   30252     NaN   3.10\n",
       "10    Northrop Grumman Corp  666807102   25603     NaN   2.62\n",
       "3             Alphabet Inc.  02079K305   25519     NaN   2.61\n",
       "11        Pure Storage Inc.  74624M102   24912     NaN   2.55\n",
       "14           Syncrony Fin'l  87165B103   14402     NaN   1.47\n",
       "5   Chipotle Mex Grill Inc.  169656105    9693     NaN   0.99\n",
       "15  Tempur Sealy Int'l Inc.  88023U101    8843     NaN   0.90"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Druckenmiller 13F for 2016-02-16:\n",
    "yi_secform.pcent13f( druck160216, 20 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                           stock      cusip      usd putcall  pcent\n",
       "6                   ALLERGAN PLC  G0177J108  1728938     NaN  10.33\n",
       "65                     SHIRE PLC  82481R106  1403184     NaN   8.39\n",
       "75  VALEANT PHARMACEUTICALS INTL  91911K102  1348479     NaN   8.06\n",
       "71  TEVA PHARMACEUTICAL INDS LTD  881624209  1339666     NaN   8.01\n",
       "47                     MYLAN N V  N59465109  1232475     NaN   7.37\n",
       "73         TIME WARNER CABLE INC  88732J207  1089246     NaN   6.51\n",
       "67  STARWOOD HOTELS&RESORTS WRLD  85590A401   803882     NaN   4.81\n",
       "74               T-MOBILE US INC  872590104   773747     NaN   4.63\n",
       "8        AMERICAN INTL GROUP INC  026874784   719050     NaN   4.30\n",
       "29        EXTENDED STAY AMER INC  30224P200   678319     NaN   4.05\n",
       "42       MALLINCKRODT PUB LTD CO  G5785G107   643586     NaN   3.85\n",
       "66               SPDR GOLD TRUST  78463V107   585933     NaN   3.50\n",
       "32                   GRIFOLS S A  398438408   401700     NaN   2.40\n",
       "38             LAM RESEARCH CORP  512807108   397100     NaN   2.37\n",
       "54                PERRIGO CO PLC  G97822103   385191     NaN   2.30\n",
       "57                POST HLDGS INC  737446104   253266     NaN   1.51\n",
       "18        COBALT INTL ENERGY INC  19075F106   214656     NaN   1.28\n",
       "40                  LIVANOVA PLC  G5509L101   211451     NaN   1.26\n",
       "45            MGIC INVT CORP WIS  552848AE3   207805     NaN   1.24\n",
       "25                 ENDO INTL PLC  G30401106   202161     NaN   1.21"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Paulson 13F for 2016-02-16:\n",
    "yi_secform.pcent13f( paulson160216, 20 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Spot gold on 2015-12-31, end of fourth calendar quarter, was fixed at $1060 in London, -4.85% from previous quarter.]\n",
    "\n",
    "2016-02-21: GLD is back to Druckenmiller's top holding: change from last quarter \"307757 Call 26.17\" to \"292205 Call 29.90\". If the net position was unchanged (given price decrease in the spot market), we can attribute a loss of \\$ 626,000 due to decay in the call option valuation (cf. theta).\n",
    "\n",
    "#### 2015-Q4: Drama for Paulson\n",
    "\n",
    "As for Paulson: change from last quarter: \"986836 NaN 5.12\" to \"585933 NaN 3.50\" -- indicates HUGE change! Market movement alone would give 938974 valuation, so the\n",
    "**realized displacement on GLD is \\$ 353,041,000, i.e. -353 million USD.** \n",
    "\n",
    "Paulson is usually the largest stakeholder in GLD also known as \"SPDR GOLD TRUST\".\n",
    "The 13F parsed in August 2015 showed \\$ 1.037 billion worth which was equivalent to\n",
    "about 886,183 troy ounces (27.6 metric tons).\n",
    "The 13F currently shows \\$ 0.586 billion worth which is equivalent to\n",
    "about 552,767 troy ounces -- a reduction of -37.6% in gold inventory.\n",
    "\n",
    "Thus we can estimate that *Paulson liquidated the equivalent of\n",
    "about 333,416 troy ounces at a weighted average price of \\$ 1059*\n",
    "(note: recorded low during 2016-Q4 for the London PM Gold fix was \\$ 1049).\n",
    "From that weighted average and gold's price history,\n",
    "we can discern that the liquidation took place largely in late December 2015."
   ]
  }
 ],
 "metadata": {
  "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}