edgar_scraping-wip.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scraping 10-Ks and 10-Qs for Alpha (Data Cleaning)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## THESIS:\n",
    "Major text changes in 10-K and 10-Q filings over time indicate significant decreases in future returns. We find alpha in shorting the companies with the largest text changes in their filings and buying the companies with the smallest text changes in their filings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introduction\n",
    "Publicly listed companies in the U.S. are required by law to file \"10-K\" and \"10-Q\" reports with the [Securities and Exchange Commission](https://www.sec.gov/) (SEC). These reports provide both qualitative and quantitative descriptions of the company's performance, from revenue numbers to qualitative risk factors.\n",
    "\n",
    "When companies file 10-Ks and 10-Qs, they are required to disclose certain pieces of information. For example, companies are required to report information about [\"significant pending lawsuits or other legal proceedings\"](https://www.sec.gov/fast-answers/answersreada10khtm.html). As such, 10-Ks and 10-Qs often hold valuable insights into a company's performance.\n",
    "\n",
    "These insights, however, can be difficult to access. The average 10-K was [42,000 words long](https://www.wsj.com/articles/the-109-894-word-annual-report-1433203762) in 2013; put in perspective, that's roughly one-fifth of the length of Moby-Dick. Beyond the sheer length, dense language and lots of boilerplate can further obfuscate true meaning for many investors.\n",
    "\n",
    "The good news? We might not need to read companies' 10-Ks and 10-Qs from cover-to-cover in order derive value from the information they contain. Specifically, Lauren Cohen, Christopher Malloy and Quoc Nguyen argue in their [recent paper](https://papers.ssrn.com/sol3/papers.cfm?abstract_id=1658471) that we can simply analyze textual changes in 10-Ks and 10-Qs to predict companies' future stock returns.\n",
    "\n",
    "In this investigation, we attempt to replicate their results on Quantopian.\n",
    "\n",
    "(For an overview of this paper from Lauren Cohen himself, see [the Lazy Prices interview](https://www.youtube.com/watch?v=g96gROyc3wE) from QuantCon 2018.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hypothesis\n",
    "Companies make major textual changes to their 10-Ks and 10-Qs when major things happen to their business. Thus, we expect that textual changes to 10-Ks and 10-Qs are a signal of future stock price movement.\n",
    "\n",
    "Since the vast majority (86%) of textual changes have negative sentiment, we generally expect that major textual changes signal a decrease in stock price (Cohen et al. 2018).\n",
    "\n",
    "Thus, we expect to find alpha by shorting companies with large textual changes in their 10-Ks and 10-Qs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Methodology\n",
    "1. Scrape every publicly traded company's 10-Ks and 10-Qs from the [SEC EDGAR database](https://www.sec.gov/edgar/searchedgar/companysearch.html). Remove extraneous content from the 10-Ks and 10-Qs (numerical tables, HTML tags, XBRL tags, etc).\n",
    "2. For each company, compute [cosine similarity](http://scikit-learn.org/stable/modules/metrics.html#cosine-similarity) and [Jaccard similarity](http://scikit-learn.org/stable/modules/model_evaluation.html#jaccard-similarity-score) scores over the sequence of its 10-Ks and 10-Qs. Each 10-K is compared to the previous year's 10-K; each 10-Q is compared to the 10-Q from the same quarter of the previous year.\n",
    "3. Compile these scores into one dataset.\n",
    "4. Upload the data to Quantopian using [Self-Serve Data](https://www.quantopian.com/posts/upload-your-custom-datasets-and-signals-with-self-serve-data), then use [Alphalens](http://quantopian.github.io/alphalens/) to analyze the performance of 10-K and 10-Q text changes as an alpha factor.\n",
    "\n",
    "This notebook covers steps 1-3. For step 4, see the [Alphalens study](https://www.quantopian.com/posts/analyzing-alpha-in-10-ks-and-10-qs) notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1+1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Running This Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is intended to be run locally (on your own computer), *not* within the Quantopian Research environment. We run it locally in order to generate the .csv file for upload into the Self-Serve Data feature.\n",
    "\n",
    "In order to run this notebook, you will need to have Python 3 and the following packages installed:  \n",
    "\n",
    "- **jupyter notebook**\n",
    "- **pandas** (version 0.23.0)\n",
    "- **numpy**\n",
    "- **requests**\n",
    "- **scikit-learn**\n",
    "- **BeautifulSoup**\n",
    "- **lxml**\n",
    "- **tqdm**\n",
    "\n",
    "All of these packages can be installed using conda or pip. For detailed installation instructions, see the installation documentation for each package ([jupyter](http://jupyter.org/install), [pandas](https://pandas.pydata.org/pandas-docs/stable/install.html), [numpy](https://scipy.org/install.html), [Requests](http://docs.python-requests.org/en/master/user/install/#install), [scikit-learn](http://scikit-learn.org/stable/install.html), [BeautifulSoup](https://www.crummy.com/software/BeautifulSoup/bs4/doc/#installing-beautiful-soup), [lxml](https://lxml.de/installation.html), [tqdm](https://pypi.org/project/tqdm/#installation)).\n",
    "\n",
    "To run this notebook:\n",
    "\n",
    "1. Clone it into your own Quantopian account and open it in your research environment.\n",
    "2. Download it as a .ipynb file (Notebook > Download as > Notebook (.ipynb))\n",
    "3. Move the .ipynb notebook file to the desired directory on your local machine.\n",
    "4. Open a command line window.\n",
    "5. Use `cd` in the command line to navigate to the directory containing the notebook file.\n",
    "6. Run `jupyter notebook` in the command line to start a jupyter notebook session.\n",
    "7. A window should open in your default web browser displaying the contents of your current directory. Click the name of the .ipynb notebook file to open it.\n",
    "8. Run the cells just as you would in the Quantopian Research environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing built-in libraries (no need to install these)\n",
    "import os\n",
    "import pickle\n",
    "import re\n",
    "from time import gmtime, strftime\n",
    "from datetime import datetime, timedelta\n",
    "import unicodedata\n",
    "\n",
    "# Importing libraries you need to install\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "import requests\n",
    "import bs4 as bs\n",
    "from lxml import html\n",
    "from tqdm import tqdm_notebook as tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Data Scraping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to know what we want to scrape, so we'll begin by compiling a complete* list of U.S. stock tickers.\n",
    "\n",
    "*for our purposes, \"complete\" = everything traded on NASDAQ, NYSE, or AMEX."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get lists of tickers from NASDAQ, NYSE, AMEX\n",
    "nasdaq_tickers = pd.read_csv('https://www.nasdaq.com/screening/companies-by-name.aspx?letter=0&exchange=nasdaq&render=download')\n",
    "nyse_tickers = pd.read_csv('https://www.nasdaq.com/screening/companies-by-name.aspx?letter=0&exchange=nyse&render=download')\n",
    "amex_tickers = pd.read_csv('https://www.nasdaq.com/screening/companies-by-name.aspx?letter=0&exchange=amex&render=download')\n",
    "\n",
    "# Drop irrelevant cols\n",
    "nasdaq_tickers.drop(labels='Unnamed: 8', axis='columns', inplace=True)\n",
    "nyse_tickers.drop(labels='Unnamed: 8', axis='columns', inplace=True)\n",
    "amex_tickers.drop(labels='Unnamed: 8', axis='columns', inplace=True)\n",
    "\n",
    "# Create full list of tickers/names across all 3 exchanges\n",
    "tickers = list(set(list(nasdaq_tickers['Symbol']) + list(nyse_tickers['Symbol']) + list(amex_tickers['Symbol'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6865"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(tickers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unfortunately, the SEC indexes company filings by its own internal identifier, the \"Central Index Key\" (CIK). We'll need to translate tickers into CIKs in order to search for company filings on EDGAR.\n",
    "\n",
    "(The code below is an edited version of [this gist](https://gist.github.com/dougvk/8499335).)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def MapTickerToCik(tickers):\n",
    "    url = 'http://www.sec.gov/cgi-bin/browse-edgar?CIK={}&Find=Search&owner=exclude&action=getcompany'\n",
    "    cik_re = re.compile(r'.*CIK=(\\d{10}).*')\n",
    "\n",
    "    cik_dict = {}\n",
    "    for ticker in tqdm(tickers): # Use tqdm lib for progress bar\n",
    "        results = cik_re.findall(requests.get(url.format(ticker)).text)\n",
    "        if len(results):\n",
    "            cik_dict[str(ticker).lower()] = str(results[0])\n",
    "    \n",
    "    return cik_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6be26a54fb75479297b7a3234dd4efef",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=6865), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "#cik_dict = MapTickerToCik(tickers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('cik_dict.pickle', 'wb') as handle:\n",
    "    pickle.dump(cik_dict, handle, protocol=pickle.HIGHEST_PROTOCOL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cik_dict = {}\n",
    "with open('cik_dict.pickle', 'rb') as handle:\n",
    "    cik_dict = pickle.load(handle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5093"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cik_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean up the ticker-CIK mapping as a DataFrame\n",
    "ticker_cik_df = pd.DataFrame.from_dict(data=cik_dict, orient='index')\n",
    "ticker_cik_df.reset_index(inplace=True)\n",
    "ticker_cik_df.columns = ['ticker', 'cik']\n",
    "ticker_cik_df['cik'] = [str(cik) for cik in ticker_cik_df['cik']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>cik</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tri</td>\n",
       "      <td>0001075124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cstr</td>\n",
       "      <td>0001676479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chfc</td>\n",
       "      <td>0000019612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>laws</td>\n",
       "      <td>0000703604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ktn</td>\n",
       "      <td>0000829281</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ticker         cik\n",
       "0    tri  0001075124\n",
       "1   cstr  0001676479\n",
       "2   chfc  0000019612\n",
       "3   laws  0000703604\n",
       "4    ktn  0000829281"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticker_cik_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our ultimate goal is to link each ticker to a unique CIK.\n",
    "\n",
    "However, some CIKs might be linked to multiple tickers. For example, different [share classes](https://www.investopedia.com/terms/s/share_class.asp) within the same company would all be linked to the same CIK. Let's get rid of these duplicate mappings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of ticker-cik pairings: 5093\n",
      "Number of unique tickers: 5093\n",
      "Number of unique CIKs: 4878\n"
     ]
    }
   ],
   "source": [
    "# Check for duplicated tickers/CIKs\n",
    "print(\"Number of ticker-cik pairings:\", len(ticker_cik_df))\n",
    "print(\"Number of unique tickers:\", len(set(ticker_cik_df['ticker'])))\n",
    "print(\"Number of unique CIKs:\", len(set(ticker_cik_df['cik'])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like about 200 (4.5%) CIKs are linked to multiple tickers. To eliminate the duplicate mappings, we'll simply keep the ticker that comes first in the alphabet. In most cases, this means we'll keep the class A shares of the stock. \n",
    "\n",
    "It's certainly possible to eliminate duplicates using other methods; for the sake of simplicity, we'll stick with alphabetizing for now. As long as we apply it uniformly across all stocks, it shouldn't introduce any bias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep first ticker alphabetically for duplicated CIKs\n",
    "ticker_cik_df = ticker_cik_df.sort_values(by='ticker')\n",
    "ticker_cik_df.drop_duplicates(subset='cik', keep='first', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of ticker-cik pairings: 4878\n",
      "Number of unique tickers: 4878\n",
      "Number of unique CIKs: 4878\n"
     ]
    }
   ],
   "source": [
    "# Check that we've eliminated duplicate tickers/CIKs\n",
    "print(\"Number of ticker-cik pairings:\", len(ticker_cik_df))\n",
    "print(\"Number of unique tickers:\", len(set(ticker_cik_df['ticker'])))\n",
    "print(\"Number of unique CIKs:\", len(set(ticker_cik_df['cik'])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point, we have a list of the CIKs for which we want to obtain 10-Ks and 10-Qs. We can now begin scraping from EDGAR.\n",
    "\n",
    "As with many web scraping projects, we'll need to keep some technical considerations in mind:\n",
    "\n",
    "- We're scraping a lot of data, so it's unlikely that we'll be able to do it all in one session without something breaking (most likely scenario: the WiFI disconnects briefly or your laptop goes to sleep). As such, we should make sure that our scraper can easily pick up where it left off without having to re-scrape anything.\n",
    "- We also probably want to log warnings/errors and save that log, just in case we need to reference it later.\n",
    "- The SEC limits users to [10 requests per second](https://www.sec.gov/developer), so we need to make sure we're not making requests too quickly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def WriteLogFile(log_file_name, text):\n",
    "    \n",
    "    '''\n",
    "    Helper function.\n",
    "    Writes a log file with all notes and\n",
    "    error messages from a scraping \"session\".\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    log_file_name : str\n",
    "        Name of the log file (should be a .txt file).\n",
    "    text : str\n",
    "        Text to write to the log file.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    with open(log_file_name, \"a\") as log_file:\n",
    "        log_file.write(text)\n",
    "\n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function below scrapes all 10-Ks and 10-K405s one particular CIK. Our web scraper primarily depends on the [`requests`](http://docs.python-requests.org/en/master/) and [`BeautifulSoup`](https://www.crummy.com/software/BeautifulSoup/bs4/doc/) libraries.\n",
    "\n",
    "Note that the scraper creates a different directory for each CIK, and puts all the filings for that CIK within that directory. After scraping, your file structure should look like this:\n",
    "\n",
    "\n",
    "```\n",
    "- 10Ks\n",
    "    - CIK1\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "    - CIK2\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "    - CIK3\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "    ...\n",
    "- 10Qs\n",
    "    - CIK1\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "    - CIK2\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "    - CIK3\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "    ...\n",
    "```\n",
    "\n",
    "The scraper will create the directory for each CIK. However, we need to create different directories to hold our 10-K and 10-Q files. The exact pathname depends on your local setup, so you'll need to fill in the correct `pathname_10k` and `pathname_10q` for your machine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "cwd = os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "pathname_10k = cwd + '/Edgar_10Ks'\n",
    "pathname_10q = cwd + '/Edgar_10Qs'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "def Scrape10K(browse_url_base, filing_url_base, doc_url_base, cik, log_file_name):\n",
    "    \n",
    "    '''\n",
    "    Scrapes all 10-Ks and 10-K405s for a particular \n",
    "    CIK from EDGAR.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    browse_url_base : str\n",
    "        Base URL for browsing EDGAR.\n",
    "    filing_url_base : str\n",
    "        Base URL for filings listings on EDGAR.\n",
    "    doc_url_base : str\n",
    "        Base URL for one filing's document tables\n",
    "        page on EDGAR.\n",
    "    cik : str\n",
    "        Central Index Key.\n",
    "    log_file_name : str\n",
    "        Name of the log file (should be a .txt file).\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Check if we've already scraped this CIK\n",
    "    try:\n",
    "        os.mkdir(cik)\n",
    "    except OSError:\n",
    "        print(\"Already scraped CIK\", cik)\n",
    "        return\n",
    "    \n",
    "    # If we haven't, go into the directory for that CIK\n",
    "    os.chdir(cik)\n",
    "    \n",
    "    print('Scraping CIK', cik)\n",
    "    \n",
    "    # Request list of 10-K filings\n",
    "    res = requests.get(browse_url_base % cik)\n",
    "    \n",
    "    # If the request failed, log the failure and exit\n",
    "    if res.status_code != 200:\n",
    "        os.chdir('..')\n",
    "        os.rmdir(cik) # remove empty dir\n",
    "        text = \"Request failed with error code \" + str(res.status_code) + \\\n",
    "               \"\\nFailed URL: \" + (browse_url_base % cik) + '\\n'\n",
    "        WriteLogFile(log_file_name, text)\n",
    "        return\n",
    "\n",
    "    # If the request doesn't fail, continue...\n",
    "    \n",
    "    # Parse the response HTML using BeautifulSoup\n",
    "    soup = bs.BeautifulSoup(res.text, \"lxml\")\n",
    "\n",
    "    # Extract all tables from the response\n",
    "    html_tables = soup.find_all('table')\n",
    "    \n",
    "    # Check that the table we're looking for exists\n",
    "    # If it doesn't, exit\n",
    "    if len(html_tables)<3:\n",
    "        os.chdir('..')\n",
    "        return\n",
    "    \n",
    "    # Parse the Filings table\n",
    "    filings_table = pd.read_html(str(html_tables[2]), header=0)[0]\n",
    "    filings_table['Filings'] = [str(x) for x in filings_table['Filings']]\n",
    "\n",
    "    # Get only 10-K and 10-K405 document filings\n",
    "    filings_table = filings_table[(filings_table['Filings'] == '10-K') | (filings_table['Filings'] == '10-K405')]\n",
    "\n",
    "    # If filings table doesn't have any\n",
    "    # 10-Ks or 10-K405s, exit\n",
    "    if len(filings_table)==0:\n",
    "        os.chdir('..')\n",
    "        return\n",
    "    \n",
    "    # Get accession number for each 10-K and 10-K405 filing\n",
    "    filings_table['Acc_No'] = [x.replace('\\xa0',' ')\n",
    "                               .split('Acc-no: ')[1]\n",
    "                               .split(' ')[0] for x in filings_table['Description']]\n",
    "\n",
    "    # Iterate through each filing and \n",
    "    # scrape the corresponding document...\n",
    "    for index, row in filings_table.iterrows():\n",
    "        \n",
    "        # Get the accession number for the filing\n",
    "        acc_no = str(row['Acc_No'])\n",
    "        \n",
    "        # Navigate to the page for the filing\n",
    "        docs_page = requests.get(filing_url_base % (cik, acc_no))\n",
    "        \n",
    "        # If request fails, log the failure\n",
    "        # and skip to the next filing\n",
    "        if docs_page.status_code != 200:\n",
    "            os.chdir('..')\n",
    "            text = \"Request failed with error code \" + str(docs_page.status_code) + \\\n",
    "                   \"\\nFailed URL: \" + (filing_url_base % (cik, acc_no)) + '\\n'\n",
    "            WriteLogFile(log_file_name, text)\n",
    "            os.chdir(cik)\n",
    "            continue\n",
    "\n",
    "        # If request succeeds, keep going...\n",
    "        \n",
    "        # Parse the table of documents for the filing\n",
    "        docs_page_soup = bs.BeautifulSoup(docs_page.text, 'lxml')\n",
    "        docs_html_tables = docs_page_soup.find_all('table')\n",
    "        if len(docs_html_tables)==0:\n",
    "            continue\n",
    "        docs_table = pd.read_html(str(docs_html_tables[0]), header=0)[0]\n",
    "        docs_table['Type'] = [str(x) for x in docs_table['Type']]\n",
    "        \n",
    "        # Get the 10-K and 10-K405 entries for the filing\n",
    "        docs_table = docs_table[(docs_table['Type'] == '10-K') | (docs_table['Type'] == '10-K405')]\n",
    "        \n",
    "        # If there aren't any 10-K or 10-K405 entries,\n",
    "        # skip to the next filing\n",
    "        if len(docs_table)==0:\n",
    "            continue\n",
    "        # If there are 10-K or 10-K405 entries,\n",
    "        # grab the first document\n",
    "        elif len(docs_table)>0:\n",
    "            docs_table = docs_table.iloc[0]\n",
    "        \n",
    "        docname = docs_table['Document']\n",
    "        \n",
    "        # If that first entry is unavailable,\n",
    "        # log the failure and exit\n",
    "        if str(docname) == 'nan':\n",
    "            os.chdir('..')\n",
    "            text = 'File with CIK: %s and Acc_No: %s is unavailable' % (cik, acc_no) + '\\n'\n",
    "            WriteLogFile(log_file_name, text)\n",
    "            os.chdir(cik)\n",
    "            continue       \n",
    "        \n",
    "        # If it is available, continue...\n",
    "        \n",
    "        # Request the file\n",
    "        file = requests.get(doc_url_base % (cik, acc_no.replace('-', ''), docname))\n",
    "        \n",
    "        # If the request fails, log the failure and exit\n",
    "        if file.status_code != 200:\n",
    "            os.chdir('..')\n",
    "            text = \"Request failed with error code \" + str(file.status_code) + \\\n",
    "                   \"\\nFailed URL: \" + (doc_url_base % (cik, acc_no.replace('-', ''), docname)) + '\\n'\n",
    "            WriteLogFile(log_file_name, text)\n",
    "            os.chdir(cik)\n",
    "            continue\n",
    "        \n",
    "        # If it succeeds, keep going...\n",
    "        \n",
    "        # Save the file in appropriate format\n",
    "        if '.txt' in docname:\n",
    "            # Save text as TXT\n",
    "            date = str(row['Filing Date'])\n",
    "            filename = cik + '_' + date + '.txt'\n",
    "            html_file = open(filename, 'a')\n",
    "            html_file.write(file.text)\n",
    "            html_file.close()\n",
    "        else:\n",
    "            # Save text as HTML\n",
    "            date = str(row['Filing Date'])\n",
    "            filename = cik + '_' + date + '.html'\n",
    "            html_file = open(filename, 'a')\n",
    "            html_file.write(file.text)\n",
    "            html_file.close()\n",
    "        \n",
    "    # Move back to the main 10-K directory\n",
    "    os.chdir('..')\n",
    "        \n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "def Scrape10Q(browse_url_base, filing_url_base, doc_url_base, cik, log_file_name):\n",
    "    \n",
    "    '''\n",
    "    Scrapes all 10-Qs for a particular CIK from EDGAR.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    browse_url_base : str\n",
    "        Base URL for browsing EDGAR.\n",
    "    filing_url_base : str\n",
    "        Base URL for filings listings on EDGAR.\n",
    "    doc_url_base : str\n",
    "        Base URL for one filing's document tables\n",
    "        page on EDGAR.\n",
    "    cik : str\n",
    "        Central Index Key.\n",
    "    log_file_name : str\n",
    "        Name of the log file (should be a .txt file).\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Check if we've already scraped this CIK\n",
    "    try:\n",
    "        os.mkdir(cik)\n",
    "    except OSError:\n",
    "        print(\"Already scraped CIK\", cik)\n",
    "        return\n",
    "    \n",
    "    # If we haven't, go into the directory for that CIK\n",
    "    os.chdir(cik)\n",
    "    \n",
    "    print('Scraping CIK', cik)\n",
    "    \n",
    "    # Request list of 10-Q filings\n",
    "    res = requests.get(browse_url_base % cik)\n",
    "    \n",
    "    # If the request failed, log the failure and exit\n",
    "    if res.status_code != 200:\n",
    "        os.chdir('..')\n",
    "        os.rmdir(cik) # remove empty dir\n",
    "        text = \"Request failed with error code \" + str(res.status_code) + \\\n",
    "               \"\\nFailed URL: \" + (browse_url_base % cik) + '\\n'\n",
    "        WriteLogFile(log_file_name, text)\n",
    "        return\n",
    "    \n",
    "    # If the request doesn't fail, continue...\n",
    "\n",
    "    # Parse the response HTML using BeautifulSoup\n",
    "    soup = bs.BeautifulSoup(res.text, \"lxml\")\n",
    "\n",
    "    # Extract all tables from the response\n",
    "    html_tables = soup.find_all('table')\n",
    "    \n",
    "    # Check that the table we're looking for exists\n",
    "    # If it doesn't, exit\n",
    "    if len(html_tables)<3:\n",
    "        print(\"table too short\")\n",
    "        os.chdir('..')\n",
    "        return\n",
    "    \n",
    "    # Parse the Filings table\n",
    "    filings_table = pd.read_html(str(html_tables[2]), header=0)[0]\n",
    "    filings_table['Filings'] = [str(x) for x in filings_table['Filings']]\n",
    "\n",
    "    # Get only 10-Q document filings\n",
    "    filings_table = filings_table[filings_table['Filings'] == '10-Q']\n",
    "\n",
    "    # If filings table doesn't have any\n",
    "    # 10-Ks or 10-K405s, exit\n",
    "    if len(filings_table)==0:\n",
    "        os.chdir('..')\n",
    "        return\n",
    "    \n",
    "    # Get accession number for each 10-K and 10-K405 filing\n",
    "    filings_table['Acc_No'] = [x.replace('\\xa0',' ')\n",
    "                               .split('Acc-no: ')[1]\n",
    "                               .split(' ')[0] for x in filings_table['Description']]\n",
    "\n",
    "    # Iterate through each filing and \n",
    "    # scrape the corresponding document...\n",
    "    for index, row in filings_table.iterrows():\n",
    "        \n",
    "        # Get the accession number for the filing\n",
    "        acc_no = str(row['Acc_No'])\n",
    "        \n",
    "        # Navigate to the page for the filing\n",
    "        docs_page = requests.get(filing_url_base % (cik, acc_no))\n",
    "        \n",
    "        # If request fails, log the failure\n",
    "        # and skip to the next filing    \n",
    "        if docs_page.status_code != 200:\n",
    "            os.chdir('..')\n",
    "            text = \"Request failed with error code \" + str(docs_page.status_code) + \\\n",
    "                   \"\\nFailed URL: \" + (filing_url_base % (cik, acc_no)) + '\\n'\n",
    "            WriteLogFile(log_file_name, text)\n",
    "            os.chdir(cik)\n",
    "            continue\n",
    "            \n",
    "        # If request succeeds, keep going...\n",
    "        \n",
    "        # Parse the table of documents for the filing\n",
    "        docs_page_soup = bs.BeautifulSoup(docs_page.text, 'lxml')\n",
    "        docs_html_tables = docs_page_soup.find_all('table')\n",
    "        if len(docs_html_tables)==0:\n",
    "            continue\n",
    "        docs_table = pd.read_html(str(docs_html_tables[0]), header=0)[0]\n",
    "        docs_table['Type'] = [str(x) for x in docs_table['Type']]\n",
    "        \n",
    "        # Get the 10-K and 10-K405 entries for the filing\n",
    "        docs_table = docs_table[docs_table['Type'] == '10-Q']\n",
    "        \n",
    "        # If there aren't any 10-K or 10-K405 entries,\n",
    "        # skip to the next filing\n",
    "        if len(docs_table)==0:\n",
    "            continue\n",
    "        # If there are 10-K or 10-K405 entries,\n",
    "        # grab the first document\n",
    "        elif len(docs_table)>0:\n",
    "            docs_table = docs_table.iloc[0]\n",
    "        \n",
    "        docname = docs_table['Document']\n",
    "        \n",
    "        # If that first entry is unavailable,\n",
    "        # log the failure and exit\n",
    "        if str(docname) == 'nan':\n",
    "            os.chdir('..')\n",
    "            text = 'File with CIK: %s and Acc_No: %s is unavailable' % (cik, acc_no) + '\\n'\n",
    "            WriteLogFile(log_file_name, text)\n",
    "            os.chdir(cik)\n",
    "            continue       \n",
    "        \n",
    "        # If it is available, continue...\n",
    "        \n",
    "        # Request the file\n",
    "        file = requests.get(doc_url_base % (cik, acc_no.replace('-', ''), docname))\n",
    "        \n",
    "        # If the request fails, log the failure and exit\n",
    "        if file.status_code != 200:\n",
    "            os.chdir('..')\n",
    "            text = \"Request failed with error code \" + str(file.status_code) + \\\n",
    "                   \"\\nFailed URL: \" + (doc_url_base % (cik, acc_no.replace('-', ''), docname)) + '\\n'\n",
    "            WriteLogFile(log_file_name, text)\n",
    "            os.chdir(cik)\n",
    "            continue\n",
    "            \n",
    "        # If it succeeds, keep going...\n",
    "        \n",
    "        # Save the file in appropriate format\n",
    "        if '.txt' in docname:\n",
    "            # Save text as TXT\n",
    "            date = str(row['Filing Date'])\n",
    "            filename = cik + '_' + date + '.txt'\n",
    "            html_file = open(filename, 'a')\n",
    "            html_file.write(file.text)\n",
    "            html_file.close()\n",
    "        else:\n",
    "            # Save text as HTML\n",
    "            date = str(row['Filing Date'])\n",
    "            filename = cik + '_' + date + '.html'\n",
    "            html_file = open(filename, 'a')\n",
    "            html_file.write(file.text)\n",
    "            html_file.close()\n",
    "        \n",
    "    # Move back to the main 10-Q directory\n",
    "    os.chdir('..')\n",
    "        \n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've defined our scraper functions, let's scrape.\n",
    "\n",
    "(A note from the future: we're scraping a lot of data, which takes *time* and *space*. For reference, these functions ultimately scraped 170 GB of 10-Qs and 125 GB of 10-Ks; the scraping took roughly 20 hours total.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "ticker_map_df = ticker_cik_df[ticker_cik_df['ticker']=='aapl']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>cik</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>aapl</td>\n",
       "      <td>0000320193</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ticker         cik\n",
       "435   aapl  0000320193"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticker_map_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ea3606a2b6204ad3b39b29fccaaa6dfb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=1), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Scraping CIK 0000320193\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Run the function to scrape 10-Ks\n",
    "\n",
    "# Define parameters\n",
    "browse_url_base_10k = 'https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=%s&type=10-K'\n",
    "filing_url_base_10k = 'http://www.sec.gov/Archives/edgar/data/%s/%s-index.html'\n",
    "doc_url_base_10k = 'http://www.sec.gov/Archives/edgar/data/%s/%s/%s'\n",
    "\n",
    "# Set correct directory\n",
    "os.chdir(pathname_10k)\n",
    "\n",
    "# Initialize log file\n",
    "# (log file name = the time we initiate scraping session)\n",
    "time = strftime(\"%Y-%m-%d %Hh%Mm%Ss\", gmtime())\n",
    "log_file_name = 'log '+time+'.txt'\n",
    "with open(log_file_name, 'a') as log_file:\n",
    "    log_file.close()\n",
    "\n",
    "# Iterate over CIKs and scrape 10-Ks\n",
    "for cik in tqdm(ticker_map_df['cik']):\n",
    "    Scrape10K(browse_url_base=browse_url_base_10k, \n",
    "          filing_url_base=filing_url_base_10k, \n",
    "          doc_url_base=doc_url_base_10k, \n",
    "          cik=cik,\n",
    "          log_file_name=log_file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5933a4246ef84a1bb894bdd7d5f4afb9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=1), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Scraping CIK 0000320193\n"
     ]
    }
   ],
   "source": [
    "# Run the function to scrape 10-Qs\n",
    "\n",
    "# Define parameters\n",
    "browse_url_base_10q = 'https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=%s&type=10-Q&count=1000'\n",
    "filing_url_base_10q = 'http://www.sec.gov/Archives/edgar/data/%s/%s-index.html'\n",
    "doc_url_base_10q = 'http://www.sec.gov/Archives/edgar/data/%s/%s/%s'\n",
    "\n",
    "# Set correct directory (fill this out yourself!)\n",
    "os.chdir(pathname_10q)\n",
    "\n",
    "# Initialize log file\n",
    "# (log file name = the time we initiate scraping session)\n",
    "time = strftime(\"%Y-%m-%d %Hh%Mm%Ss\", gmtime())\n",
    "log_file_name = 'log '+time+'.txt'\n",
    "log_file = open(log_file_name, 'a')\n",
    "log_file.close()\n",
    "\n",
    "# Iterate over CIKs and scrape 10-Ks\n",
    "for cik in tqdm(ticker_map_df['cik']):\n",
    "    Scrape10Q(browse_url_base=browse_url_base_10q, \n",
    "          filing_url_base=filing_url_base_10q, \n",
    "          doc_url_base=doc_url_base_10q, \n",
    "          cik=cik,\n",
    "          log_file_name=log_file_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have 10-Ks and 10-Qs in HTML or plaintext (.txt) format for each CIK. Before computing our similarity scores, however, we need to clean the files up a bit.\n",
    "\n",
    "As outlined in the paper, we will:\n",
    "\n",
    "> ... remove all tables (if their numeric character content is \n",
    ">     greater than 15%), HTML tags, XBRL tables, exhibits, \n",
    ">     ASCII-encoded PDFs, graphics, XLS, and other binary files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def RemoveNumericalTables(soup):\n",
    "    \n",
    "    '''\n",
    "    Removes tables with >15% numerical characters.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    soup : BeautifulSoup object\n",
    "        Parsed result from BeautifulSoup.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    soup : BeautifulSoup object\n",
    "        Parsed result from BeautifulSoup\n",
    "        with numerical tables removed.\n",
    "        \n",
    "    '''\n",
    "    \n",
    "    # Determines percentage of numerical characters\n",
    "    # in a table\n",
    "    def GetDigitPercentage(tablestring):\n",
    "        if len(tablestring)>0.0:\n",
    "            numbers = sum([char.isdigit() for char in tablestring])\n",
    "            length = len(tablestring)\n",
    "            return numbers/length\n",
    "        else:\n",
    "            return 1\n",
    "    \n",
    "    # Evaluates numerical character % for each table\n",
    "    # and removes the table if the percentage is > 15%\n",
    "    [x.extract() for x in soup.find_all('table') if GetDigitPercentage(x.get_text())>0.15]\n",
    "    \n",
    "    return soup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def RemoveTags(soup):\n",
    "    \n",
    "    '''\n",
    "    Drops HTML tags, newlines and unicode text from\n",
    "    filing text.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    soup : BeautifulSoup object\n",
    "        Parsed result from BeautifulSoup.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    text : str\n",
    "        Filing text.\n",
    "        \n",
    "    '''\n",
    "    \n",
    "    # Remove HTML tags with get_text\n",
    "    text = soup.get_text()\n",
    "    \n",
    "    # Remove newline characters\n",
    "    text = text.replace('\\n', ' ')\n",
    "    \n",
    "    # Replace unicode characters with their\n",
    "    # \"normal\" representations\n",
    "    text = unicodedata.normalize('NFKD', text)\n",
    "    \n",
    "    return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def ConvertHTML(cik):\n",
    "    \n",
    "    '''\n",
    "    Removes numerical tables, HTML tags,\n",
    "    newlines, unicode text, and XBRL tables.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    cik : str\n",
    "        Central Index Key used to scrape files.\n",
    "    \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Look for files scraped for that CIK\n",
    "    try: \n",
    "        os.chdir(cik)\n",
    "    # ...if we didn't scrape any files for that CIK, exit\n",
    "    except FileNotFoundError:\n",
    "        print(\"Could not find directory for CIK\", cik)\n",
    "        return\n",
    "        \n",
    "    print(\"Parsing CIK %s...\" % cik)\n",
    "    parsed = False # flag to tell if we've parsed anything\n",
    "    \n",
    "    # Try to make a new directory within the CIK directory\n",
    "    # to store the text representations of the filings\n",
    "    try:\n",
    "        os.mkdir('rawtext')\n",
    "    # If it already exists, continue\n",
    "    # We can't exit at this point because we might be\n",
    "    # partially through parsing text files, so we need to continue\n",
    "    except OSError:\n",
    "        pass\n",
    "    \n",
    "    # Get list of scraped files\n",
    "    # excluding hidden files and directories\n",
    "    file_list = [fname for fname in os.listdir() if not (fname.startswith('.') | os.path.isdir(fname))]\n",
    "    \n",
    "    # Iterate over scraped files and clean\n",
    "    for filename in file_list:\n",
    "            \n",
    "        # Check if file has already been cleaned\n",
    "        new_filename = filename.replace('.html', '.txt')\n",
    "        text_file_list = os.listdir('rawtext')\n",
    "        if new_filename in text_file_list:\n",
    "            continue\n",
    "        \n",
    "        # If it hasn't been cleaned already, keep going...\n",
    "        \n",
    "        # Clean file\n",
    "        with open(filename, 'r') as file:\n",
    "            parsed = True\n",
    "            soup = bs.BeautifulSoup(file.read(), \"lxml\")\n",
    "            soup = RemoveNumericalTables(soup)\n",
    "            text = RemoveTags(soup)\n",
    "            with open('rawtext/'+new_filename, 'w') as newfile:\n",
    "                newfile.write(text)\n",
    "    \n",
    "    # If all files in the CIK directory have been parsed\n",
    "    # then log that\n",
    "    if parsed==False:\n",
    "        print(\"Already parsed CIK\", cik)\n",
    "    \n",
    "    os.chdir('..')\n",
    "    return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now apply this function to each of our 10-K and 10-Q files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# For 10-Ks...\n",
    "\n",
    "os.chdir(pathname_10k)\n",
    "\n",
    "# Iterate over CIKs and clean HTML filings\n",
    "for cik in tqdm(ticker_cik_df['cik']):\n",
    "    ConvertHTML(cik)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# For 10-Qs...\n",
    "\n",
    "os.chdir(pathname_10q)\n",
    "\n",
    "# Iterate over CIKs and clean HTML filings\n",
    "for cik in tqdm(ticker_cik_df['cik']):\n",
    "    ConvertHTML(cik)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After running the two cells above, we have cleaned plaintext 10-K and 10-Q filings for each CIK. At this point, our file structure looks like this:\n",
    "\n",
    "\n",
    "```\n",
    "- 10Ks\n",
    "    - CIK1\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "        - rawtext\n",
    "    - CIK2\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "        - rawtext\n",
    "    - CIK3\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "        - rawtext\n",
    "    ...\n",
    "- 10Qs\n",
    "    - CIK1\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "        - rawtext\n",
    "    - CIK2\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "        - rawtext\n",
    "    - CIK3\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "        - rawtext\n",
    "    ...\n",
    "```\n",
    "\n",
    "We can now begin computing our alpha factor (similarity scores)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Computing Similarity Scores"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll use [cosine similarity](http://scikit-learn.org/stable/modules/metrics.html#cosine-similarity) and [Jaccard similarity](http://scikit-learn.org/stable/modules/model_evaluation.html#jaccard-similarity-score) to compare documents.\n",
    "\n",
    "(The original paper also uses two other, simpler similarity measures, but cosine and Jaccard appeared to result in the best alpha factor performance -- and are much less computationally intensive to compute.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def ComputeCosineSimilarity(words_A, words_B):\n",
    "    \n",
    "    '''\n",
    "    Compute cosine similarity between document A and\n",
    "    document B.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    words_A : set\n",
    "        Words in document A.\n",
    "    words_B : set\n",
    "        Words in document B\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    cosine_score : float\n",
    "        Cosine similarity between document\n",
    "        A and document B.\n",
    "        \n",
    "    '''\n",
    "    \n",
    "    # Compile complete set of words in A or B\n",
    "    words = list(words_A.union(words_B))\n",
    "    \n",
    "    # Determine which words are in A\n",
    "    vector_A = [1 if x in words_A else 0 for x in words]\n",
    "    \n",
    "    # Determine which words are in B\n",
    "    vector_B = [1 if x in words_B else 0 for x in words]\n",
    "    \n",
    "    # Compute cosine score using scikit-learn\n",
    "    array_A = np.array(vector_A).reshape(1, -1)\n",
    "    array_B = np.array(vector_B).reshape(1, -1)\n",
    "    cosine_score = cosine_similarity(array_A, array_B)[0,0]\n",
    "    \n",
    "    return cosine_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def ComputeJaccardSimilarity(words_A, words_B):\n",
    "    \n",
    "    '''\n",
    "    Compute Jaccard similarity between document A and\n",
    "    document B.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    words_A : set\n",
    "        Words in document A.\n",
    "    words_B : set\n",
    "        Words in document B\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    jaccard_score : float\n",
    "        Jaccard similarity between document\n",
    "        A and document B.\n",
    "        \n",
    "    '''\n",
    "    \n",
    "    # Count number of words in both A and B\n",
    "    words_intersect = len(words_A.intersection(words_B))\n",
    "    \n",
    "    # Count number of words in A or B\n",
    "    words_union = len(words_A.union(words_B))\n",
    "    \n",
    "    # Compute Jaccard similarity score\n",
    "    jaccard_score = words_intersect / words_union\n",
    "    \n",
    "    return jaccard_score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before continuing, let's double-check that these functions are working properly.\n",
    "\n",
    "The paper gives the following sample sentences to compare:\n",
    "\n",
    "> $D_A$: We expect demand to increase.\n",
    "> $D_B$: We expect worldwide demand to increase.\n",
    "> $D_C$: We expect weakness in sales.\n",
    "\n",
    "As noted in the paper, the cosine similarity between $D_A$ and $D_B$ should be $0.91$, and the cosine similarity between $D_A$ and $D_C$ should be $0.40$.\n",
    "\n",
    "Meanwhile, the Jaccard similarity between $D_A$ and $D_B$ should be $0.83$, and the Jaccard similarity between $D_A$ and $D_C$ should be $0.25$.\n",
    "\n",
    "Let's double-check that our functions return the correct results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cosine similarity between A and B: 0.9128709291752769\n",
      "Cosine similarity between A and C: 0.39999999999999997\n",
      "Jaccard similarity between A and B: 0.8333333333333334\n",
      "Jaccard similarity between A and C: 0.25\n"
     ]
    }
   ],
   "source": [
    "d_a = set(['we', 'expect', 'demand', 'to', 'increase'])\n",
    "d_b = set(['we', 'expect', 'worldwide', 'demand', 'to', 'increase'])\n",
    "d_c = set(['we', 'expect', 'weakness', 'in', 'sales'])\n",
    "\n",
    "print(\"Cosine similarity between A and B:\", ComputeCosineSimilarity(d_a, d_b))\n",
    "print(\"Cosine similarity between A and C:\", ComputeCosineSimilarity(d_a, d_c))\n",
    "print(\"Jaccard similarity between A and B:\", ComputeJaccardSimilarity(d_a, d_b))\n",
    "print(\"Jaccard similarity between A and C:\", ComputeJaccardSimilarity(d_a, d_c))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Everything looks good! Now, let's begin applying these similarity computations to the scraped 10-Ks and 10-Qs.\n",
    "\n",
    "We'll start with 10-Qs. This is slightly difficult, because we want to compare each 10-Q to the 10-Q from the *same quarter of the previous year*.\n",
    "\n",
    "Keep in mind that 10-Qs are filed three times per year, and 10-Ks are filed once per year. Thus, we should simply be able to order the 10-Qs by filing date, then compare each 10-Q to the third-to-last file. For example, if our sorted list of 10-Q files was: `[10Q-1, 10Q-2, 10Q-3, 10Q-4, 10Q-5, 10Q-6...]` then we would iterate over the list and compare `10Q-4` to `10Q-1`, `10Q-5` to `10Q-2`, and so on.\n",
    "\n",
    "Unfortunately, filings aren't so clean in the real world. Sometimes, companies don't file 10-Qs for a quarter (or more) for a variety of reasons. We don't want to compare 10-Qs from different quarters; quarter-on-quarter differences will create misleading noise in our ultimate factor values. [why don't companies file 10Ks? why are 10Qs different per quarter?]\n",
    "\n",
    "As such, we'll take each 10-Q and look for 10-Qs that are dated between 345 and 385 days earlier. If one exists, we'll compute the similarity; if no such file exists, we'll report the scores as `NaN`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def ComputeSimilarityScores10Q(cik):\n",
    "    \n",
    "    '''\n",
    "    Computes cosine and Jaccard similarity scores\n",
    "    over 10-Qs for a particular CIK.\n",
    "    \n",
    "    Compares each 10-Q to the 10-Q from the same\n",
    "    quarter of the previous year.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    cik: str\n",
    "        Central Index Key used to scrape and name\n",
    "        files.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Define how stringent we want to be about \n",
    "    # \"previous year\"\n",
    "    year_short = timedelta(345)\n",
    "    year_long = timedelta(385)\n",
    "    \n",
    "    # Open directory that holds plain 10-Q textfiles\n",
    "    # for the CIK\n",
    "    os.chdir(cik+'/rawtext')\n",
    "    print(\"Parsing CIK %s...\" % cik)\n",
    "    \n",
    "    # Get list of files to compare\n",
    "    file_list = [fname for fname in os.listdir() if not \n",
    "                 (fname.startswith('.') | os.path.isdir(fname))]\n",
    "    file_list.sort()\n",
    "    \n",
    "    # Check if scores have already been calculated\n",
    "    try:\n",
    "        os.mkdir('../metrics')\n",
    "    # ... if they have already been calculated, exit\n",
    "    except OSError:\n",
    "        print(\"Already parsed CIK %s...\" % cik)\n",
    "        os.chdir('../..')\n",
    "        return\n",
    "    \n",
    "    # Check if enough files exist to compare\n",
    "    # ... if there aren't enough files, exit\n",
    "    if len(file_list) < 4:\n",
    "        print(\"No files to compare for CIK\", cik)\n",
    "        os.chdir('../..')\n",
    "        return\n",
    "    \n",
    "    # Initialize dataframe to hold similarity scores\n",
    "    dates = [x[-14:-4] for x in file_list]\n",
    "    cosine_score = [0]*len(dates)\n",
    "    jaccard_score = [0]*len(dates)\n",
    "    data = pd.DataFrame(columns={'cosine_score': cosine_score, \n",
    "                                 'jaccard_score': jaccard_score},\n",
    "                       index=dates)\n",
    "    \n",
    "    # Iterate over each quarter...\n",
    "    for j in range(3):\n",
    "        \n",
    "        # Get text and date of earliest filing from that quarter\n",
    "        file_name_A = file_list[j]\n",
    "        with open(file_name_A, 'r') as file:\n",
    "            file_text_A = file.read()\n",
    "        date_A = datetime.strptime(file_name_A[-14:-4], '%Y-%m-%d')\n",
    "        \n",
    "        # Iterate over the rest of the filings from that quarter...\n",
    "        for i in range(j+3, len(file_list), 3):\n",
    "\n",
    "            # Get name and date of the later file\n",
    "            file_name_B = file_list[i]\n",
    "            date_B = datetime.strptime(file_name_B[-14:-4], '%Y-%m-%d')\n",
    "            \n",
    "            # If B was not filed within ~1 year after A...\n",
    "            if (date_B > (date_A + year_long)) or (date_B < (date_A + year_short)):\n",
    "                \n",
    "                print(date_B.strftime('%Y-%m-%d'), \"is not within a year of\", date_A.strftime('%Y-%m-%d'))\n",
    "                \n",
    "                # Record values as NaN\n",
    "                data.at[date_B.strftime('%Y-%m-%d'), 'cosine_score'] = 'NaN'\n",
    "                data.at[date_B.strftime('%Y-%m-%d'), 'jaccard_score'] = 'NaN'\n",
    "                \n",
    "                # Pretend as if we found new date_A in the next year\n",
    "                date_A = date_A.replace(year=date_B.year)\n",
    "                \n",
    "                # Move to next filing\n",
    "                continue\n",
    "                \n",
    "            # If B was filed within ~1 year of A...\n",
    "            \n",
    "            # Get file text\n",
    "            with open(file_name_B, 'r') as file:\n",
    "                file_text_B = file.read()\n",
    "\n",
    "            # Get sets of words in A, B\n",
    "            words_A = set(re.findall(r\"[\\w']+\", file_text_A))\n",
    "            words_B = set(re.findall(r\"[\\w']+\", file_text_B))\n",
    "\n",
    "            # Calculate similarity score\n",
    "            cosine_score = ComputeCosineSimilarity(words_A, words_B)\n",
    "            jaccard_score = ComputeJaccardSimilarity(words_A, words_B)\n",
    "\n",
    "            # Store value (indexing by the date of document B)\n",
    "            data.at[date_B.strftime('%Y-%m-%d'), 'cosine_score'] = cosine_score\n",
    "            data.at[date_B.strftime('%Y-%m-%d'), 'jaccard_score'] = jaccard_score\n",
    "\n",
    "            # Reset value for next loop\n",
    "            # Don't re-read files, for efficiency\n",
    "            file_text_A = file_text_B\n",
    "            date_A = date_B\n",
    "\n",
    "    # Save scores\n",
    "    os.chdir('../metrics')\n",
    "    data.to_csv(cik+'_sim_scores.csv', index=False)\n",
    "    os.chdir('../..')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fortunately, 10-Ks are easier. Though there can still be time-jumps in 10-K filings, we don't mind as much if we're comparing a 10-K from 2006 to a 10-K from 2002. This is because we don't have to worry about non-substantive quarter-on-quarter differences as we would with 10-Qs. In fact, it might actually be better if our data reflects textual changes in 10-Ks across \"absent\" years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def ComputeSimilarityScores10K(cik):\n",
    "    \n",
    "    '''\n",
    "    Computes cosine and Jaccard similarity scores\n",
    "    over 10-Ks for a particular CIK.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    cik: str\n",
    "        Central Index Key used to scrape and name\n",
    "        files.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Open the directory that holds plaintext\n",
    "    # filings for the CIK\n",
    "    os.chdir(cik+'/rawtext')\n",
    "    print(\"Parsing CIK %s...\" % cik)\n",
    "    \n",
    "    # Get list of files to over which to compute scores\n",
    "    # excluding hidden files and directories\n",
    "    file_list = [fname for fname in os.listdir() if not \n",
    "                 (fname.startswith('.') | os.path.isdir(fname))]\n",
    "    file_list.sort()\n",
    "    \n",
    "    # Check if scores have already been calculated...\n",
    "    try:\n",
    "        os.mkdir('../metrics')\n",
    "    # ... if they have been, exit\n",
    "    except OSError:\n",
    "        print(\"Already parsed CIK %s...\" % cik)\n",
    "        os.chdir('../..')\n",
    "        return\n",
    "    \n",
    "    # Check if enough files exist to compute sim scores...\n",
    "    # If not, exit\n",
    "    if len(file_list) < 2:\n",
    "        print(\"No files to compare for CIK\", cik)\n",
    "        os.chdir('../..')\n",
    "        return\n",
    "    \n",
    "    # Initialize dataframe to store sim scores\n",
    "    dates = [x[-14:-4] for x in file_list]\n",
    "    cosine_score = [0]*len(dates)\n",
    "    jaccard_score = [0]*len(dates)\n",
    "    data = pd.DataFrame(columns={'cosine_score': cosine_score, \n",
    "                                 'jaccard_score': jaccard_score},\n",
    "                       index=dates)\n",
    "        \n",
    "    # Open first file\n",
    "    file_name_A = file_list[0]\n",
    "    with open(file_name_A, 'r') as file:\n",
    "        file_text_A = file.read()\n",
    "        \n",
    "    # Iterate over each 10-K file...\n",
    "    for i in range(1, len(file_list)):\n",
    "\n",
    "        file_name_B = file_list[i]\n",
    "\n",
    "        # Get file text B\n",
    "        with open(file_name_B, 'r') as file:\n",
    "            file_text_B = file.read()\n",
    "\n",
    "        # Get set of words in A, B\n",
    "        words_A = set(re.findall(r\"[\\w']+\", file_text_A))\n",
    "        words_B = set(re.findall(r\"[\\w']+\", file_text_B))\n",
    "\n",
    "        # Calculate similarity scores\n",
    "        cosine_score = ComputeCosineSimilarity(words_A, words_B)\n",
    "        jaccard_score = ComputeJaccardSimilarity(words_A, words_B)\n",
    "\n",
    "        # Store score values\n",
    "        date_B = file_name_B[-14:-4]\n",
    "        data.at[date_B, 'cosine_score'] = cosine_score\n",
    "        data.at[date_B, 'jaccard_score'] = jaccard_score\n",
    "\n",
    "        # Reset value for next loop\n",
    "        # (We don't open the file again, for efficiency)\n",
    "        file_text_A = file_text_B\n",
    "\n",
    "    # Save scores\n",
    "    os.chdir('../metrics')\n",
    "    data.to_csv(cik+'_sim_scores.csv', index=False)\n",
    "    os.chdir('../..')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we store factor values according to the date of the later document (document B). This is because we want our data to be point-in-time; we want to store the factor values according to the date that *we would have known about them in the past*.\n",
    "\n",
    "In this case, our values (similarity scores) depend on two things: the content of document A, and the content of document B. We would have become aware of the text of document A at `date_A`, and we would have become aware of the text of document B at `date_B`.\n",
    "\n",
    "Remember that A is stipulated to be the earlier document. Since A precedes B, we wouldn't know about the factor values at `date_A`; the content of B would not yet be available. However, we would know about the factor values at `date_B` -- at that point, both the content of A and the content of B would be available. As such, we'll store our values according to `date_B`.\n",
    "\n",
    "(The above applies to both 10-Qs and 10-Ks.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's go ahead and apply these functions to our stored 10-Qs and 10-Ks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Computing scores for 10-Qs...\n",
    "\n",
    "os.chdir(pathname_10q)\n",
    "\n",
    "for cik in tqdm(ticker_cik_df['cik']):\n",
    "    ComputeSimilarityScores10Q(cik)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Computing scores for 10-Ks...\n",
    "\n",
    "os.chdir(pathname_10k)\n",
    "\n",
    "for cik in tqdm(ticker_cik_df['cik']):\n",
    "    ComputeSimilarityScores10K(cik)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After computing the similarity scores, our file structure looks like this:\n",
    "\n",
    "\n",
    "```\n",
    "- 10Ks\n",
    "    - CIK1\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "        - rawtext\n",
    "        - metrics\n",
    "    - CIK2\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "        - rawtext\n",
    "        - metrics\n",
    "    - CIK3\n",
    "        - 10K #1\n",
    "        - 10K #2\n",
    "        ...\n",
    "        - rawtext\n",
    "        - metrics\n",
    "    ...\n",
    "- 10Qs\n",
    "    - CIK1\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "        - rawtext\n",
    "        - metrics\n",
    "    - CIK2\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "        - rawtext\n",
    "        - metrics\n",
    "    - CIK3\n",
    "        - 10Q #1\n",
    "        - 10Q #2\n",
    "        ...\n",
    "        - rawtext\n",
    "        - metrics\n",
    "    ...\n",
    "```\n",
    "\n",
    "The similarity scores for each CIK are stored in the `metrics` directory as a .csv file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Compiling the Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've scraped the data and computed the similarity scores, we're almost done. The final step is to format our data properly for upload to [Self-Serve Data](https://www.quantopian.com/posts/upload-your-custom-datasets-and-signals-with-self-serve-data).\n",
    "\n",
    "We'll begin by consolidating the .csv files in the 10-K and 10-Q directories into a single DataFrame for each CIK."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def GetData(cik, pathname_10k, pathname_10q, pathname_data):\n",
    "    \n",
    "    '''\n",
    "    Consolidate 10-K and 10-Q data into a single dataframe\n",
    "    for a CIK.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    cik : str\n",
    "        Central Index Key used to scrape and\n",
    "        store data.\n",
    "    pathname_10k : str\n",
    "        Path to directory holding 10-K files.\n",
    "    pathname_10q : str\n",
    "        Path to directory holding 10-Q files.\n",
    "    pathname_data : str\n",
    "        Path to directory holding newly\n",
    "        generated data files.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Flags to determine what data we have\n",
    "    data_10k = True\n",
    "    data_10q = True\n",
    "    \n",
    "    print(\"Gathering data for CIK %s...\" % cik)\n",
    "    file_name = ('%s_sim_scores_full.csv' % cik)\n",
    "    \n",
    "    # Check if data has already been gathered...\n",
    "    os.chdir(pathname_data)\n",
    "    file_list = [fname for fname in os.listdir() if not fname.startswith('.')]\n",
    "    \n",
    "    # ... if it has been, exit\n",
    "    if file_name in file_list:\n",
    "        print(\"Already gathered data for CIK\", cik)\n",
    "        return\n",
    "    \n",
    "    # Try to get 10-K data...\n",
    "    os.chdir(pathname_10k+'/%s/metrics' % cik)\n",
    "    try:\n",
    "        sim_scores_10k = pd.read_csv(cik+'_sim_scores.csv')\n",
    "    # ... if it doesn't exist, set 10-K flag to False\n",
    "    except FileNotFoundError:\n",
    "        print(\"No data to gather.\")\n",
    "        data_10k = False\n",
    "    \n",
    "    # Try to get 10-Q data...\n",
    "    os.chdir(pathname_10q+'/%s/metrics' % cik)\n",
    "    try:\n",
    "        sim_scores_10q = pd.read_csv(cik+'_sim_scores.csv')\n",
    "    # ... if it doesn't exist, set 10-Q flag to False\n",
    "    except FileNotFoundError:\n",
    "        print(\"No data to gather.\")\n",
    "        data_10q = False\n",
    "    \n",
    "    # Merge depending on available data...\n",
    "    # ... if there's no 10-K or 10-Q data, exit\n",
    "    if not (data_10k and data_10q):\n",
    "        return\n",
    "    \n",
    "    # ... if there's no 10-Q data (but there is 10-K data),\n",
    "    # only use the 10-K data\n",
    "    if not data_10q:\n",
    "        sim_scores = sim_scores_10k\n",
    "    # ... if the opposite is true, only use 10-Q data\n",
    "    elif not data_10k:\n",
    "        sim_scores = sim_scores_10q\n",
    "    # ... if there's both 10-K and 10-Q data, merge\n",
    "    elif (data_10q and data_10k):\n",
    "        sim_scores = pd.concat([sim_scores_10k, sim_scores_10q], \n",
    "                           axis='index')\n",
    "    \n",
    "    # Rename date column\n",
    "    sim_scores.rename(columns={'Unnamed: 0': 'date'}, inplace=True)\n",
    "\n",
    "    # Set CIK column\n",
    "    sim_scores['cik'] = cik\n",
    "    \n",
    "    # Save file in the data dir\n",
    "    os.chdir(pathname_data)\n",
    "    sim_scores.to_csv('%s_sim_scores_full.csv' % cik, index=False)\n",
    "    \n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pathname_data = '< YOUR DATA PATHNAME HERE >' # Fill this out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for cik in tqdm(ticker_cik_df['cik']):\n",
    "    GetData(cik, pathname_10k, pathname_10q, pathname_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we have a \"data\" directory that looks like this:\n",
    "\n",
    "\n",
    "```\n",
    "- data\n",
    "    - CIK1_sim_scores_full.csv\n",
    "    - CIK2_sim_scores_full.csv\n",
    "    ...\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, we need to consolidate each CIK's data into a single dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def MakeDataset(file_list, pathname_full_data):\n",
    "    \n",
    "    '''\n",
    "    Consolidates CIK datasets into a\n",
    "    single dataset.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    file_list : list\n",
    "        List of .csv files to merge.\n",
    "    pathname_full_data : str\n",
    "        Path to directory to store\n",
    "        full dataset.\n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    None.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    # Initialize dataframe to store results\n",
    "    data = pd.DataFrame(columns=['date', 'cosine_score', 'jaccard_score', 'cik'])\n",
    "    \n",
    "    # Iterate over files and merge all together\n",
    "    for file_name in tqdm(file_list):\n",
    "        new_data = pd.read_csv(file_name)\n",
    "        data = data.append(new_data, sort=True)\n",
    "    \n",
    "    # Store result\n",
    "    os.chdir(pathname_full_data)\n",
    "    data.to_csv('all_sim_scores.csv', index=False)\n",
    "    \n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pathname_full_data = '< YOUR FULL DATA PATHNAME HERE >'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "os.chdir(pathname_data)\n",
    "file_list = [fname for fname in os.listdir() if not fname.startswith('.')]\n",
    "\n",
    "MakeDataset(file_list, pathname_full_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The final step is to transform the data into a format appropriate for Self-Serve Data. This means that we want a dataset with one set of factor values per ticker per day. In other words, each day-ticker pair should have a `cosine_score` and a `jaccard_score` value.\n",
    "\n",
    "In this step, we'll need to:\n",
    "\n",
    "1. Map CIKs back to tickers. To do this, we'll simply merge our full dataset with `ticker_cik_df`.\n",
    "2. Forward-fill values for 60 calendar days (time limit per the original paper).\n",
    "3. Construct a dataset with one set of factor values per ticker per day (with `NaN`s for missing values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sim_scores_full = pd.read_csv('all_sim_scores.csv')\n",
    "\n",
    "# Cast CIKs as strings\n",
    "sim_scores_full['cik'] = [str(x) for x in sim_scores_full['cik']]\n",
    "\n",
    "# Merge to map tickers to CIKs\n",
    "sim_scores_ticker = sim_scores_full.merge(ticker_cik_df, how='left', on='cik')\n",
    "\n",
    "# Drop CIK column\n",
    "sim_scores_ticker.drop(labels=['cik'], axis='columns', inplace=True)\n",
    "\n",
    "# Drop NaN values\n",
    "sim_scores_ticker.dropna(axis='index', how='any', subset=['jaccard_score', 'cosine_score'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `sim_scores_ticker` data has one row for each filing, listing the set of factor values, ticker, and date. However, some day-ticker pairs have no associated factor values. We need to manipulate this data so that we have one row per ticker per day.\n",
    "\n",
    "To do this, we'll begin with an empty dataframe that contains one row per ticker per day. We'll then [join](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.join.html) this formatted empty dataframe (`empty_data`) with our actual data (`sim_scores_ticker`) in such a way that preserves all the rows of `empty_data`. We'll end up with a dataframe that contains all the data from `sim_scores_ticker`, with `NaN`s inserted for day-ticker pairs where we're missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def InitializeEmptyDataframe(start_date, end_date, tickers):\n",
    "    \n",
    "    '''\n",
    "    Initializes an empty DataFrame with all correct indices \n",
    "    (1 entry/ticker/day)\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    start_date : datetime.datetime\n",
    "        Start date of dataframe.\n",
    "    end_date : datetime.datetime\n",
    "        End date of dataframe.\n",
    "    tickers : list\n",
    "        List of tickers.\n",
    "    '''\n",
    "    \n",
    "    window_length_days = int((end_date - start_date).days)\n",
    "    date_list = [start_date+timedelta(days=x) for x in range(0, window_length_days)]\n",
    "    long_date_list = date_list * len(tickers)\n",
    "    long_date_list = [x.strftime('%Y-%m-%d') for x in long_date_list]\n",
    "    list.sort(long_date_list)\n",
    "    empty = pd.DataFrame(data={'date': long_date_list, \n",
    "                                     'ticker': tickers*len(date_list),\n",
    "                                'jaccard_score': [np.nan]*len(tickers)*len(date_list),\n",
    "                              'cosine_score': [np.nan]*len(tickers)*len(date_list)})\n",
    "    empty = empty.groupby(['date', 'ticker']).sum()\n",
    "    \n",
    "    empty['jaccard_score'] = np.nan\n",
    "    empty['cosine_score'] = np.nan\n",
    "    \n",
    "    return empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Initialize empty dataframe\n",
    "start_date = datetime(2015, 1, 1)\n",
    "end_date = datetime(2018, 1, 1)\n",
    "tickers = list(set(sim_scores_ticker['ticker']))\n",
    "\n",
    "empty_data = InitializeEmptyDataframe(start_date, end_date, tickers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Note that we set `start_date = datetime(2013, 1, 1)`. This is because Self-Serve Data has a maximum file size of 300 MB; too large of a date range will exceed that maximum. The dataset spanning 2013-2018 is 250 MB.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "&lt;style scoped=\"\"&gt;\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "&lt;/style&gt;\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>jaccard_score</th>\n",
       "      <th>cosine_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2015-01-01</th>\n",
       "      <th>a</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaba</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aac</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   jaccard_score  cosine_score\n",
       "date       ticker                             \n",
       "2015-01-01 a                 NaN           NaN\n",
       "           aa                NaN           NaN\n",
       "           aaba              NaN           NaN\n",
       "           aac               NaN           NaN\n",
       "           aal               NaN           NaN"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "empty_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Format sim_scores data for merging\n",
    "sim_scores_formatted = sim_scores_ticker.dropna(axis='index', how='any', subset=['jaccard_score', 'cosine_score'])\n",
    "\n",
    "sim_scores_formatted = sim_scores_formatted.groupby(['date', 'ticker']).agg('mean')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we use the `.agg('mean')` aggregator. This means that we'll take any rows that match each ticker-day pair and average the factor values.\n",
    "\n",
    "In most cases, there should only be one row per ticker-day pair; however, there are some cases where 10-Ks and 10-Qs are filed on the same day, thus creating the need for `.agg('mean')`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "&lt;style scoped=\"\"&gt;\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "&lt;/style&gt;\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>CIK</th>\n",
       "      <th>cosine_score</th>\n",
       "      <th>jaccard_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2000-07-05</th>\n",
       "      <th>gmz</th>\n",
       "      <td>886982</td>\n",
       "      <td>0.734078</td>\n",
       "      <td>0.573545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gs</th>\n",
       "      <td>886982</td>\n",
       "      <td>0.734078</td>\n",
       "      <td>0.573545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-07-14</th>\n",
       "      <th>sonc</th>\n",
       "      <td>868611</td>\n",
       "      <td>0.756802</td>\n",
       "      <td>0.608235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-07-17</th>\n",
       "      <th>adbe</th>\n",
       "      <td>796343</td>\n",
       "      <td>0.771085</td>\n",
       "      <td>0.627273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-07-28</th>\n",
       "      <th>aaba</th>\n",
       "      <td>1011006</td>\n",
       "      <td>0.744055</td>\n",
       "      <td>0.592149</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       CIK  cosine_score  jaccard_score\n",
       "date       ticker                                      \n",
       "2000-07-05 gmz      886982      0.734078       0.573545\n",
       "           gs       886982      0.734078       0.573545\n",
       "2000-07-14 sonc     868611      0.756802       0.608235\n",
       "2000-07-17 adbe     796343      0.771085       0.627273\n",
       "2000-07-28 aaba    1011006      0.744055       0.592149"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sim_scores_formatted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "formatted_data = empty_data.join(sim_scores_formatted,\n",
    "                                 how='left', \n",
    "                                 on=['date', 'ticker'], \n",
    "                                 lsuffix='_empty')\n",
    "\n",
    "formatted_data.drop(labels=['cosine_score_empty', 'jaccard_score_empty'], axis='columns', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "&lt;style scoped=\"\"&gt;\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "&lt;/style&gt;\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>CIK</th>\n",
       "      <th>cosine_score</th>\n",
       "      <th>jaccard_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2015-01-01</th>\n",
       "      <th>a</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aa</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaba</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aac</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   CIK  cosine_score  jaccard_score\n",
       "date       ticker                                  \n",
       "2015-01-01 a       NaN           NaN            NaN\n",
       "           aa      NaN           NaN            NaN\n",
       "           aaba    NaN           NaN            NaN\n",
       "           aac     NaN           NaN            NaN\n",
       "           aal     NaN           NaN            NaN"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "formatted_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our final step is to forward-fill the values by one quarter (approximately 90 calendar days). First, let's sort the data by ticker, then by calendar day:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "forward_filled_data = formatted_data.reset_index().sort_values(by=['ticker', 'date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "&lt;style scoped=\"\"&gt;\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "&lt;/style&gt;\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th>CIK</th>\n",
       "      <th>cosine_score</th>\n",
       "      <th>jaccard_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3970</th>\n",
       "      <td>2015-01-02</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7940</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11910</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15880</th>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             date ticker  CIK  cosine_score  jaccard_score\n",
       "0      2015-01-01      a  NaN           NaN            NaN\n",
       "3970   2015-01-02      a  NaN           NaN            NaN\n",
       "7940   2015-01-03      a  NaN           NaN            NaN\n",
       "11910  2015-01-04      a  NaN           NaN            NaN\n",
       "15880  2015-01-05      a  NaN           NaN            NaN"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "forward_filled_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "forward_filled_data.fillna(method='ffill', limit=90, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "&lt;style scoped=\"\"&gt;\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "&lt;/style&gt;\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th>CIK</th>\n",
       "      <th>cosine_score</th>\n",
       "      <th>jaccard_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3970</th>\n",
       "      <td>2015-01-02</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7940</th>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11910</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15880</th>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             date ticker  CIK  cosine_score  jaccard_score\n",
       "0      2015-01-01      a  NaN           NaN            NaN\n",
       "3970   2015-01-02      a  NaN           NaN            NaN\n",
       "7940   2015-01-03      a  NaN           NaN            NaN\n",
       "11910  2015-01-04      a  NaN           NaN            NaN\n",
       "15880  2015-01-05      a  NaN           NaN            NaN"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "forward_filled_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've have one row per ticker per day and we've forward-filled the values by 60 days, so we're ready to save our data as a .csv file and upload it to Self-Serve."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "forward_filled_data.to_csv('lazy_prices_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're done creating our dataset! For the next steps in our analysis, see the [Alphalens notebook](https://www.quantopian.com/posts/analyzing-alpha-in-10-ks-and-10-qs)."
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}