main_download_news_text.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download news raw text from Eikon for a list of storyID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This script loads the already downloaded Eikon news storyID, connect to Eikon API and request the news raw text for each of them"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import pandas as pd\n",
    "import time\n",
    "import numpy as np\n",
    "import dateutil\n",
    "from tqdm import tqdm\n",
    "import glob\n",
    "import os\n",
    "from IPython.display import HTML\n",
    "from bs4 import BeautifulSoup\n",
    "import pickle\n",
    "import string\n",
    "import random\n",
    "import html5lib\n",
    "import matplotlib\n",
    "from matplotlib.ticker import PercentFormatter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Paths setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If working locally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = \"G:\\\\My Drive\\\\PhD\\\\Research\\\\Projects\\\\Portfolio Management and Sentiment views\\\\Data\\\\News\\\\Eikon\\\\\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If working at the Eikon's workstation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = \"C:\\\\Users\\\\cityu_local\\\\Downloads\\\\\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Eikon API setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('Eikon_Api_Key.txt', 'r') as f:\n",
    "    cookie = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import eikon as ek\n",
    "ek.set_app_key(cookie)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Defining the tickers list to use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "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>company</th>\n",
       "      <th>exchange</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>ISIN</th>\n",
       "      <th>RIC</th>\n",
       "      <th>selected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>779000000000.0</td>\n",
       "      <td>US5949181045</td>\n",
       "      <td>MSFT.O</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Apple Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>744000000000.0</td>\n",
       "      <td>US0378331005</td>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AMZN</td>\n",
       "      <td>Amazoncom Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>714000000000.0</td>\n",
       "      <td>US0231351067</td>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>Alphabet Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>712000000000.0</td>\n",
       "      <td>US02079K3059</td>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FB</td>\n",
       "      <td>Facebook Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>383000000000.0</td>\n",
       "      <td>US30303M1027</td>\n",
       "      <td>FB.O</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>JNJ</td>\n",
       "      <td>Johnson &amp; Johnson</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>344000000000.0</td>\n",
       "      <td>US4781601046</td>\n",
       "      <td>JNJ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>JPM</td>\n",
       "      <td>J P Morgan Chase &amp; Co</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>321000000000.0</td>\n",
       "      <td>US46625H1005</td>\n",
       "      <td>JPM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>V</td>\n",
       "      <td>Visa Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>293000000000.0</td>\n",
       "      <td>US92826C8394</td>\n",
       "      <td>V</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>XOM</td>\n",
       "      <td>Exxon Mobil Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>291000000000.0</td>\n",
       "      <td>US30231G1022</td>\n",
       "      <td>XOM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WMT</td>\n",
       "      <td>Walmart Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>254000000000.0</td>\n",
       "      <td>US9311421039</td>\n",
       "      <td>WMT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PFE</td>\n",
       "      <td>Pfizer Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>242000000000.0</td>\n",
       "      <td>US7170811035</td>\n",
       "      <td>PFE</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BAC</td>\n",
       "      <td>Bank of America Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>237000000000.0</td>\n",
       "      <td>US0605051046</td>\n",
       "      <td>BAC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>UNH</td>\n",
       "      <td>UnitedHealth Group Incorporated</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>233000000000.0</td>\n",
       "      <td>US91324P1021</td>\n",
       "      <td>UNH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>PG</td>\n",
       "      <td>Procter &amp; Gamble Company (The)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>227000000000.0</td>\n",
       "      <td>US7427181091</td>\n",
       "      <td>PG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>KO</td>\n",
       "      <td>Coca-Cola Company (The)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>202000000000.0</td>\n",
       "      <td>US1912161007</td>\n",
       "      <td>KO</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>HD</td>\n",
       "      <td>Home Depot Inc (The)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>185000000000.0</td>\n",
       "      <td>US4370761029</td>\n",
       "      <td>HD</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>DIS</td>\n",
       "      <td>Walt Disney Company (The)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>159000000000.0</td>\n",
       "      <td>US2546871060</td>\n",
       "      <td>DIS</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UNP</td>\n",
       "      <td>Union Pacific Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>99230893092.0</td>\n",
       "      <td>US9078181081</td>\n",
       "      <td>UNP</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HON</td>\n",
       "      <td>Honeywell International Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>96787269000.0</td>\n",
       "      <td>US4385161066</td>\n",
       "      <td>HON</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>LIN</td>\n",
       "      <td>Linde plc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>85982612516.0</td>\n",
       "      <td>IE00BZ12WP82</td>\n",
       "      <td>LIN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NEE</td>\n",
       "      <td>NextEra Energy Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>83597404902.0</td>\n",
       "      <td>US65339F1012</td>\n",
       "      <td>NEE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>SBUX</td>\n",
       "      <td>Starbucks Corporation</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>77103290000.0</td>\n",
       "      <td>US8552441094</td>\n",
       "      <td>SBUX.O</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>LMT</td>\n",
       "      <td>Lockheed Martin Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>75455290770.0</td>\n",
       "      <td>US5398301094</td>\n",
       "      <td>LMT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AMT</td>\n",
       "      <td>American Tower Corporation (REIT)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>71156409066.0</td>\n",
       "      <td>US03027X1000</td>\n",
       "      <td>AMT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>COP</td>\n",
       "      <td>ConocoPhillips</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>69074513280.0</td>\n",
       "      <td>US20825C1045</td>\n",
       "      <td>COP</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>DUK</td>\n",
       "      <td>Duke Energy Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>62833027962.0</td>\n",
       "      <td>US26441C2044</td>\n",
       "      <td>DUK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>GS</td>\n",
       "      <td>Goldman Sachs Group Inc (The)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>62644097553.0</td>\n",
       "      <td>US38141G1040</td>\n",
       "      <td>GS</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>D</td>\n",
       "      <td>Dominion Energy Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>49117339863.0</td>\n",
       "      <td>US25746U1097</td>\n",
       "      <td>D</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>OXY</td>\n",
       "      <td>Occidental Petroleum Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>45777222621.0</td>\n",
       "      <td>US6745991058</td>\n",
       "      <td>OXY</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>CCI</td>\n",
       "      <td>Crown Castle International Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>45633548620.0</td>\n",
       "      <td>US22822V1017</td>\n",
       "      <td>CCI</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>ECL</td>\n",
       "      <td>Ecolab Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>42091692407.0</td>\n",
       "      <td>US2788651006</td>\n",
       "      <td>ECL</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>PLD</td>\n",
       "      <td>Prologis Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>38004726100.0</td>\n",
       "      <td>US74340W1036</td>\n",
       "      <td>PLD</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NEM</td>\n",
       "      <td>Newmont Mining Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>18158387173.0</td>\n",
       "      <td>US6516391066</td>\n",
       "      <td>NEM</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker                                 company exchange      market_cap  \\\n",
       "0    MSFT                   Microsoft Corporation   NASDAQ  779000000000.0   \n",
       "1    AAPL                               Apple Inc   NASDAQ  744000000000.0   \n",
       "2    AMZN                           Amazoncom Inc   NASDAQ  714000000000.0   \n",
       "3   GOOGL                            Alphabet Inc   NASDAQ  712000000000.0   \n",
       "4      FB                            Facebook Inc   NASDAQ  383000000000.0   \n",
       "5     JNJ                       Johnson & Johnson     NYSE  344000000000.0   \n",
       "6     JPM                   J P Morgan Chase & Co     NYSE  321000000000.0   \n",
       "7       V                                Visa Inc     NYSE  293000000000.0   \n",
       "8     XOM                 Exxon Mobil Corporation     NYSE  291000000000.0   \n",
       "9     WMT                             Walmart Inc     NYSE  254000000000.0   \n",
       "10    PFE                              Pfizer Inc     NYSE  242000000000.0   \n",
       "11    BAC             Bank of America Corporation     NYSE  237000000000.0   \n",
       "12    UNH         UnitedHealth Group Incorporated     NYSE  233000000000.0   \n",
       "13     PG          Procter & Gamble Company (The)     NYSE  227000000000.0   \n",
       "14     KO                 Coca-Cola Company (The)     NYSE  202000000000.0   \n",
       "15     HD                    Home Depot Inc (The)     NYSE  185000000000.0   \n",
       "16    DIS               Walt Disney Company (The)     NYSE  159000000000.0   \n",
       "17    UNP               Union Pacific Corporation     NYSE   99230893092.0   \n",
       "18    HON             Honeywell International Inc     NYSE   96787269000.0   \n",
       "19    LIN                               Linde plc     NYSE   85982612516.0   \n",
       "20    NEE                      NextEra Energy Inc     NYSE   83597404902.0   \n",
       "21   SBUX                   Starbucks Corporation   NASDAQ   77103290000.0   \n",
       "22    LMT             Lockheed Martin Corporation     NYSE   75455290770.0   \n",
       "23    AMT       American Tower Corporation (REIT)     NYSE   71156409066.0   \n",
       "24    COP                          ConocoPhillips     NYSE   69074513280.0   \n",
       "25    DUK                 Duke Energy Corporation     NYSE   62833027962.0   \n",
       "26     GS           Goldman Sachs Group Inc (The)     NYSE   62644097553.0   \n",
       "27      D                     Dominion Energy Inc     NYSE   49117339863.0   \n",
       "28    OXY        Occidental Petroleum Corporation     NYSE   45777222621.0   \n",
       "29    CCI  Crown Castle International Corporation     NYSE   45633548620.0   \n",
       "30    ECL                              Ecolab Inc     NYSE   42091692407.0   \n",
       "31    PLD                            Prologis Inc     NYSE   38004726100.0   \n",
       "32    NEM              Newmont Mining Corporation     NYSE   18158387173.0   \n",
       "\n",
       "            ISIN      RIC selected  \n",
       "0   US5949181045   MSFT.O        0  \n",
       "1   US0378331005   AAPL.O        1  \n",
       "2   US0231351067   AMZN.O        1  \n",
       "3   US02079K3059  GOOGL.O        1  \n",
       "4   US30303M1027     FB.O        0  \n",
       "5   US4781601046      JNJ        1  \n",
       "6   US46625H1005      JPM        1  \n",
       "7   US92826C8394        V        0  \n",
       "8   US30231G1022      XOM        1  \n",
       "9   US9311421039      WMT        1  \n",
       "10  US7170811035      PFE        0  \n",
       "11  US0605051046      BAC        0  \n",
       "12  US91324P1021      UNH        0  \n",
       "13  US7427181091       PG        0  \n",
       "14  US1912161007       KO        0  \n",
       "15  US4370761029       HD        0  \n",
       "16  US2546871060      DIS        0  \n",
       "17  US9078181081      UNP        1  \n",
       "18  US4385161066      HON        0  \n",
       "19  IE00BZ12WP82      LIN        1  \n",
       "20  US65339F1012      NEE        1  \n",
       "21  US8552441094   SBUX.O        0  \n",
       "22  US5398301094      LMT        0  \n",
       "23  US03027X1000      AMT        1  \n",
       "24  US20825C1045      COP        0  \n",
       "25  US26441C2044      DUK        0  \n",
       "26  US38141G1040       GS        0  \n",
       "27  US25746U1097        D        0  \n",
       "28  US6745991058      OXY        0  \n",
       "29  US22822V1017      CCI        0  \n",
       "30  US2788651006      ECL        0  \n",
       "31  US74340W1036      PLD        0  \n",
       "32  US6516391066      NEM        0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickers = pd.read_csv(\"myTickerList-Eikon.csv\",\n",
    "                         encoding = 'utf-8',\n",
    "                     index_col = 0).astype(str)\n",
    "tickers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>company</th>\n",
       "      <th>exchange</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>ISIN</th>\n",
       "      <th>RIC</th>\n",
       "      <th>selected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Apple Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>744000000000.0</td>\n",
       "      <td>US0378331005</td>\n",
       "      <td>AAPL.O</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AMZN</td>\n",
       "      <td>Amazoncom Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>714000000000.0</td>\n",
       "      <td>US0231351067</td>\n",
       "      <td>AMZN.O</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>Alphabet Inc</td>\n",
       "      <td>NASDAQ</td>\n",
       "      <td>712000000000.0</td>\n",
       "      <td>US02079K3059</td>\n",
       "      <td>GOOGL.O</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>JNJ</td>\n",
       "      <td>Johnson &amp; Johnson</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>344000000000.0</td>\n",
       "      <td>US4781601046</td>\n",
       "      <td>JNJ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>JPM</td>\n",
       "      <td>J P Morgan Chase &amp; Co</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>321000000000.0</td>\n",
       "      <td>US46625H1005</td>\n",
       "      <td>JPM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>XOM</td>\n",
       "      <td>Exxon Mobil Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>291000000000.0</td>\n",
       "      <td>US30231G1022</td>\n",
       "      <td>XOM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WMT</td>\n",
       "      <td>Walmart Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>254000000000.0</td>\n",
       "      <td>US9311421039</td>\n",
       "      <td>WMT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UNP</td>\n",
       "      <td>Union Pacific Corporation</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>99230893092.0</td>\n",
       "      <td>US9078181081</td>\n",
       "      <td>UNP</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>LIN</td>\n",
       "      <td>Linde plc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>85982612516.0</td>\n",
       "      <td>IE00BZ12WP82</td>\n",
       "      <td>LIN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NEE</td>\n",
       "      <td>NextEra Energy Inc</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>83597404902.0</td>\n",
       "      <td>US65339F1012</td>\n",
       "      <td>NEE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AMT</td>\n",
       "      <td>American Tower Corporation (REIT)</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>71156409066.0</td>\n",
       "      <td>US03027X1000</td>\n",
       "      <td>AMT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker                            company exchange      market_cap  \\\n",
       "1    AAPL                          Apple Inc   NASDAQ  744000000000.0   \n",
       "2    AMZN                      Amazoncom Inc   NASDAQ  714000000000.0   \n",
       "3   GOOGL                       Alphabet Inc   NASDAQ  712000000000.0   \n",
       "5     JNJ                  Johnson & Johnson     NYSE  344000000000.0   \n",
       "6     JPM              J P Morgan Chase & Co     NYSE  321000000000.0   \n",
       "8     XOM            Exxon Mobil Corporation     NYSE  291000000000.0   \n",
       "9     WMT                        Walmart Inc     NYSE  254000000000.0   \n",
       "17    UNP          Union Pacific Corporation     NYSE   99230893092.0   \n",
       "19    LIN                          Linde plc     NYSE   85982612516.0   \n",
       "20    NEE                 NextEra Energy Inc     NYSE   83597404902.0   \n",
       "23    AMT  American Tower Corporation (REIT)     NYSE   71156409066.0   \n",
       "\n",
       "            ISIN      RIC selected  \n",
       "1   US0378331005   AAPL.O        1  \n",
       "2   US0231351067   AMZN.O        1  \n",
       "3   US02079K3059  GOOGL.O        1  \n",
       "5   US4781601046      JNJ        1  \n",
       "6   US46625H1005      JPM        1  \n",
       "8   US30231G1022      XOM        1  \n",
       "9   US9311421039      WMT        1  \n",
       "17  US9078181081      UNP        1  \n",
       "19  IE00BZ12WP82      LIN        1  \n",
       "20  US65339F1012      NEE        1  \n",
       "23  US03027X1000      AMT        1  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickers = tickers[tickers['selected'] == '1']\n",
    "tickers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11, 7)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickers.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading the storyID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "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>index</th>\n",
       "      <th>versionCreated</th>\n",
       "      <th>text</th>\n",
       "      <th>storyId</th>\n",
       "      <th>sourceCode</th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-12 23:22:40.536</td>\n",
       "      <td>2019-06-12 23:22:40.536000+00:00</td>\n",
       "      <td>Quisitive Technology Solutions Acquires 100% S...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nNRA8y1imv:1</td>\n",
       "      <td>NS:DATMTR</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-12 23:13:47.773</td>\n",
       "      <td>2019-06-12 23:13:47.773000+00:00</td>\n",
       "      <td>Reuters Insider - Cramer: Here's how to spot t...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nRTV1KKFwz:1</td>\n",
       "      <td>NS:CNBC</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-12 22:35:50.594</td>\n",
       "      <td>2019-06-12 22:35:50.594000+00:00</td>\n",
       "      <td>Microsoft Corporation - Microsoft announces qu...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nNDL1XwdmZ:1</td>\n",
       "      <td>NS:PUBT</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-12 22:21:42.000</td>\n",
       "      <td>2019-06-12 22:21:42+00:00</td>\n",
       "      <td>BRIEF-Microsoft Announces Quarterly Dividend</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nFWN23J0UQ:2</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-12 22:21:14.000</td>\n",
       "      <td>2019-06-12 22:21:16.580000+00:00</td>\n",
       "      <td>MICROSOFT CORP &lt;MSFT.O&gt; SETS QUARTERLY DIVIDEN...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nFWN23J0UQ:2</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164870</th>\n",
       "      <td>2020-06-01 06:00:10.000</td>\n",
       "      <td>2020-06-01 06:00:10+00:00</td>\n",
       "      <td>REG - GS ActiveBeta US  - Net Asset Value(s)</td>\n",
       "      <td>urn:newsml:reuters.com:20200601:nRSA4576Oa:1</td>\n",
       "      <td>NS:LSE</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-01 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164871</th>\n",
       "      <td>2020-06-01 06:00:05.000</td>\n",
       "      <td>2020-06-01 06:00:05+00:00</td>\n",
       "      <td>REG - Goldman Sachs ETF  - Net Asset Value(s)</td>\n",
       "      <td>urn:newsml:reuters.com:20200601:nRSA4469Oa:1</td>\n",
       "      <td>NS:LSE</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-01 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164872</th>\n",
       "      <td>2020-06-09 23:20:42.895</td>\n",
       "      <td>2020-06-10 07:17:27.709000+00:00</td>\n",
       "      <td>Dow Jones Selected Stocks - June 10</td>\n",
       "      <td>urn:newsml:newsroom:20200609:nNRAc0ncwa:0</td>\n",
       "      <td>NS:AAP</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-09 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164873</th>\n",
       "      <td>2020-06-10 14:38:20.000</td>\n",
       "      <td>2020-06-10 14:52:16+00:00</td>\n",
       "      <td>BREAKINGVIEWS-SoftBank could end up making lem...</td>\n",
       "      <td>urn:newsml:reuters.com:20200610:nL8N2DN53P:1</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-10 00:00:00.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164874</th>\n",
       "      <td>2020-06-10 14:33:10.000</td>\n",
       "      <td>2020-06-10 14:33:10+00:00</td>\n",
       "      <td>Goldman Sachs forms fund to address racial inj...</td>\n",
       "      <td>urn:newsml:reuters.com:20200610:nL4N2DN338:1</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-10 00:00:00.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>164875 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          index                    versionCreated  \\\n",
       "0       2019-06-12 23:22:40.536  2019-06-12 23:22:40.536000+00:00   \n",
       "1       2019-06-12 23:13:47.773  2019-06-12 23:13:47.773000+00:00   \n",
       "2       2019-06-12 22:35:50.594  2019-06-12 22:35:50.594000+00:00   \n",
       "3       2019-06-12 22:21:42.000         2019-06-12 22:21:42+00:00   \n",
       "4       2019-06-12 22:21:14.000  2019-06-12 22:21:16.580000+00:00   \n",
       "...                         ...                               ...   \n",
       "164870  2020-06-01 06:00:10.000         2020-06-01 06:00:10+00:00   \n",
       "164871  2020-06-01 06:00:05.000         2020-06-01 06:00:05+00:00   \n",
       "164872  2020-06-09 23:20:42.895  2020-06-10 07:17:27.709000+00:00   \n",
       "164873  2020-06-10 14:38:20.000         2020-06-10 14:52:16+00:00   \n",
       "164874  2020-06-10 14:33:10.000         2020-06-10 14:33:10+00:00   \n",
       "\n",
       "                                                     text  \\\n",
       "0       Quisitive Technology Solutions Acquires 100% S...   \n",
       "1       Reuters Insider - Cramer: Here's how to spot t...   \n",
       "2       Microsoft Corporation - Microsoft announces qu...   \n",
       "3            BRIEF-Microsoft Announces Quarterly Dividend   \n",
       "4       MICROSOFT CORP <MSFT.O> SETS QUARTERLY DIVIDEN...   \n",
       "...                                                   ...   \n",
       "164870       REG - GS ActiveBeta US  - Net Asset Value(s)   \n",
       "164871      REG - Goldman Sachs ETF  - Net Asset Value(s)   \n",
       "164872                Dow Jones Selected Stocks - June 10   \n",
       "164873  BREAKINGVIEWS-SoftBank could end up making lem...   \n",
       "164874  Goldman Sachs forms fund to address racial inj...   \n",
       "\n",
       "                                             storyId sourceCode ticker  \\\n",
       "0       urn:newsml:reuters.com:20190612:nNRA8y1imv:1  NS:DATMTR   MSFT   \n",
       "1       urn:newsml:reuters.com:20190612:nRTV1KKFwz:1    NS:CNBC   MSFT   \n",
       "2       urn:newsml:reuters.com:20190612:nNDL1XwdmZ:1    NS:PUBT   MSFT   \n",
       "3       urn:newsml:reuters.com:20190612:nFWN23J0UQ:2    NS:RTRS   MSFT   \n",
       "4       urn:newsml:reuters.com:20190612:nFWN23J0UQ:2    NS:RTRS   MSFT   \n",
       "...                                              ...        ...    ...   \n",
       "164870  urn:newsml:reuters.com:20200601:nRSA4576Oa:1     NS:LSE     GS   \n",
       "164871  urn:newsml:reuters.com:20200601:nRSA4469Oa:1     NS:LSE     GS   \n",
       "164872     urn:newsml:newsroom:20200609:nNRAc0ncwa:0     NS:AAP     GS   \n",
       "164873  urn:newsml:reuters.com:20200610:nL8N2DN53P:1    NS:RTRS     GS   \n",
       "164874  urn:newsml:reuters.com:20200610:nL4N2DN338:1    NS:RTRS     GS   \n",
       "\n",
       "                           date  \n",
       "0       2019-06-12 00:00:00.000  \n",
       "1       2019-06-12 00:00:00.000  \n",
       "2       2019-06-12 00:00:00.000  \n",
       "3       2019-06-12 00:00:00.000  \n",
       "4       2019-06-12 00:00:00.000  \n",
       "...                         ...  \n",
       "164870  2020-06-01 00:00:00.000  \n",
       "164871  2020-06-01 00:00:00.000  \n",
       "164872  2020-06-09 00:00:00.000  \n",
       "164873  2020-06-10 00:00:00.000  \n",
       "164874  2020-06-10 00:00:00.000  \n",
       "\n",
       "[164875 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId = pd.read_csv(data_path + \"storyId\\\\storyId.csv\", encoding = 'utf-8')\n",
    "storyId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>index</th>\n",
       "      <th>versionCreated</th>\n",
       "      <th>text</th>\n",
       "      <th>storyId</th>\n",
       "      <th>sourceCode</th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-12 23:22:40.536</td>\n",
       "      <td>2019-06-12 23:22:40.536000+00:00</td>\n",
       "      <td>Quisitive Technology Solutions Acquires 100% S...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nNRA8y1imv:1</td>\n",
       "      <td>NS:DATMTR</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-12 23:13:47.773</td>\n",
       "      <td>2019-06-12 23:13:47.773000+00:00</td>\n",
       "      <td>Reuters Insider - Cramer: Here's how to spot t...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nRTV1KKFwz:1</td>\n",
       "      <td>NS:CNBC</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-12 22:35:50.594</td>\n",
       "      <td>2019-06-12 22:35:50.594000+00:00</td>\n",
       "      <td>Microsoft Corporation - Microsoft announces qu...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nNDL1XwdmZ:1</td>\n",
       "      <td>NS:PUBT</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-12 22:21:42.000</td>\n",
       "      <td>2019-06-12 22:21:42+00:00</td>\n",
       "      <td>BRIEF-Microsoft Announces Quarterly Dividend</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nFWN23J0UQ:2</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-12 22:21:14.000</td>\n",
       "      <td>2019-06-12 22:21:16.580000+00:00</td>\n",
       "      <td>MICROSOFT CORP &lt;MSFT.O&gt; SETS QUARTERLY DIVIDEN...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nFWN23J0UQ:2</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164870</th>\n",
       "      <td>2020-06-01 06:00:10.000</td>\n",
       "      <td>2020-06-01 06:00:10+00:00</td>\n",
       "      <td>REG - GS ActiveBeta US  - Net Asset Value(s)</td>\n",
       "      <td>urn:newsml:reuters.com:20200601:nRSA4576Oa:1</td>\n",
       "      <td>NS:LSE</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164871</th>\n",
       "      <td>2020-06-01 06:00:05.000</td>\n",
       "      <td>2020-06-01 06:00:05+00:00</td>\n",
       "      <td>REG - Goldman Sachs ETF  - Net Asset Value(s)</td>\n",
       "      <td>urn:newsml:reuters.com:20200601:nRSA4469Oa:1</td>\n",
       "      <td>NS:LSE</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164872</th>\n",
       "      <td>2020-06-09 23:20:42.895</td>\n",
       "      <td>2020-06-10 07:17:27.709000+00:00</td>\n",
       "      <td>Dow Jones Selected Stocks - June 10</td>\n",
       "      <td>urn:newsml:newsroom:20200609:nNRAc0ncwa:0</td>\n",
       "      <td>NS:AAP</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164873</th>\n",
       "      <td>2020-06-10 14:38:20.000</td>\n",
       "      <td>2020-06-10 14:52:16+00:00</td>\n",
       "      <td>BREAKINGVIEWS-SoftBank could end up making lem...</td>\n",
       "      <td>urn:newsml:reuters.com:20200610:nL8N2DN53P:1</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164874</th>\n",
       "      <td>2020-06-10 14:33:10.000</td>\n",
       "      <td>2020-06-10 14:33:10+00:00</td>\n",
       "      <td>Goldman Sachs forms fund to address racial inj...</td>\n",
       "      <td>urn:newsml:reuters.com:20200610:nL4N2DN338:1</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>GS</td>\n",
       "      <td>2020-06-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>164875 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         index                   versionCreated  \\\n",
       "0      2019-06-12 23:22:40.536 2019-06-12 23:22:40.536000+00:00   \n",
       "1      2019-06-12 23:13:47.773 2019-06-12 23:13:47.773000+00:00   \n",
       "2      2019-06-12 22:35:50.594 2019-06-12 22:35:50.594000+00:00   \n",
       "3      2019-06-12 22:21:42.000        2019-06-12 22:21:42+00:00   \n",
       "4      2019-06-12 22:21:14.000 2019-06-12 22:21:16.580000+00:00   \n",
       "...                        ...                              ...   \n",
       "164870 2020-06-01 06:00:10.000        2020-06-01 06:00:10+00:00   \n",
       "164871 2020-06-01 06:00:05.000        2020-06-01 06:00:05+00:00   \n",
       "164872 2020-06-09 23:20:42.895 2020-06-10 07:17:27.709000+00:00   \n",
       "164873 2020-06-10 14:38:20.000        2020-06-10 14:52:16+00:00   \n",
       "164874 2020-06-10 14:33:10.000        2020-06-10 14:33:10+00:00   \n",
       "\n",
       "                                                     text  \\\n",
       "0       Quisitive Technology Solutions Acquires 100% S...   \n",
       "1       Reuters Insider - Cramer: Here's how to spot t...   \n",
       "2       Microsoft Corporation - Microsoft announces qu...   \n",
       "3            BRIEF-Microsoft Announces Quarterly Dividend   \n",
       "4       MICROSOFT CORP <MSFT.O> SETS QUARTERLY DIVIDEN...   \n",
       "...                                                   ...   \n",
       "164870       REG - GS ActiveBeta US  - Net Asset Value(s)   \n",
       "164871      REG - Goldman Sachs ETF  - Net Asset Value(s)   \n",
       "164872                Dow Jones Selected Stocks - June 10   \n",
       "164873  BREAKINGVIEWS-SoftBank could end up making lem...   \n",
       "164874  Goldman Sachs forms fund to address racial inj...   \n",
       "\n",
       "                                             storyId sourceCode ticker  \\\n",
       "0       urn:newsml:reuters.com:20190612:nNRA8y1imv:1  NS:DATMTR   MSFT   \n",
       "1       urn:newsml:reuters.com:20190612:nRTV1KKFwz:1    NS:CNBC   MSFT   \n",
       "2       urn:newsml:reuters.com:20190612:nNDL1XwdmZ:1    NS:PUBT   MSFT   \n",
       "3       urn:newsml:reuters.com:20190612:nFWN23J0UQ:2    NS:RTRS   MSFT   \n",
       "4       urn:newsml:reuters.com:20190612:nFWN23J0UQ:2    NS:RTRS   MSFT   \n",
       "...                                              ...        ...    ...   \n",
       "164870  urn:newsml:reuters.com:20200601:nRSA4576Oa:1     NS:LSE     GS   \n",
       "164871  urn:newsml:reuters.com:20200601:nRSA4469Oa:1     NS:LSE     GS   \n",
       "164872     urn:newsml:newsroom:20200609:nNRAc0ncwa:0     NS:AAP     GS   \n",
       "164873  urn:newsml:reuters.com:20200610:nL8N2DN53P:1    NS:RTRS     GS   \n",
       "164874  urn:newsml:reuters.com:20200610:nL4N2DN338:1    NS:RTRS     GS   \n",
       "\n",
       "             date  \n",
       "0      2019-06-12  \n",
       "1      2019-06-12  \n",
       "2      2019-06-12  \n",
       "3      2019-06-12  \n",
       "4      2019-06-12  \n",
       "...           ...  \n",
       "164870 2020-06-01  \n",
       "164871 2020-06-01  \n",
       "164872 2020-06-09  \n",
       "164873 2020-06-10  \n",
       "164874 2020-06-10  \n",
       "\n",
       "[164875 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId['index'] = pd.to_datetime(pd.to_datetime(storyId['index']))\n",
    "storyId['versionCreated'] = pd.to_datetime(pd.to_datetime(storyId['versionCreated']))\n",
    "storyId['date'] = pd.to_datetime(pd.to_datetime(storyId['date']).dt.date)\n",
    "storyId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 164875 entries, 0 to 164874\n",
      "Data columns (total 7 columns):\n",
      " #   Column          Non-Null Count   Dtype              \n",
      "---  ------          --------------   -----              \n",
      " 0   index           164875 non-null  datetime64[ns]     \n",
      " 1   versionCreated  164875 non-null  datetime64[ns, UTC]\n",
      " 2   text            164875 non-null  object             \n",
      " 3   storyId         164875 non-null  object             \n",
      " 4   sourceCode      164875 non-null  object             \n",
      " 5   ticker          164875 non-null  object             \n",
      " 6   date            164875 non-null  datetime64[ns]     \n",
      "dtypes: datetime64[ns, UTC](1), datetime64[ns](2), object(4)\n",
      "memory usage: 8.8+ MB\n"
     ]
    }
   ],
   "source": [
    "storyId.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Keeping only the storyId for tickers in the tickers list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    88623\n",
       "True     76252\n",
       "Name: ticker, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId['ticker'].isin(list(tickers['ticker'])).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "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>index</th>\n",
       "      <th>versionCreated</th>\n",
       "      <th>text</th>\n",
       "      <th>storyId</th>\n",
       "      <th>sourceCode</th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-12 23:00:11.983</td>\n",
       "      <td>2019-06-12 23:00:11.983000+00:00</td>\n",
       "      <td>Amazon Boosts Cashierless Initiatives With 2nd...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nNRA8y19v4:1</td>\n",
       "      <td>NS:ZACKSC</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-12 21:57:27.059</td>\n",
       "      <td>2019-06-12 21:57:27.059000+00:00</td>\n",
       "      <td>Reuters Insider - Facebook is sinking back int...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nRTV1vZVFx:1</td>\n",
       "      <td>NS:CNBC</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-12 21:32:15.868</td>\n",
       "      <td>2019-06-12 21:32:15.868000+00:00</td>\n",
       "      <td>Reuters Insider - The question is how will tar...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nRTV1cdVkS:1</td>\n",
       "      <td>NS:CNBC</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-12 21:27:02.000</td>\n",
       "      <td>2019-06-12 21:27:02+00:00</td>\n",
       "      <td>Republican senator criticizes potential dual U...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nL2N23J1K6:3</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-12 20:27:24.000</td>\n",
       "      <td>2019-06-12 20:27:24+00:00</td>\n",
       "      <td>Pompeo to push in India for more U.S. access t...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nL2N23J1FG:5</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76247</th>\n",
       "      <td>2020-02-18 04:21:35.000</td>\n",
       "      <td>2020-02-18 20:20:37+00:00</td>\n",
       "      <td>UPDATE 9-Oil near flat; virus impact offsets L...</td>\n",
       "      <td>urn:newsml:reuters.com:20200218:nL4N2AI139:16</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-02-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76248</th>\n",
       "      <td>2020-04-30 07:20:46.999</td>\n",
       "      <td>2020-04-30 07:20:49.299000+00:00</td>\n",
       "      <td>Dow Jones Selected Stocks 1710 - April 30</td>\n",
       "      <td>urn:newsml:newsroom:20200430:nNRAbo9mnf:0</td>\n",
       "      <td>NS:AAP</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76249</th>\n",
       "      <td>2020-04-30 07:03:12.228</td>\n",
       "      <td>2020-04-30 07:03:12.228000+00:00</td>\n",
       "      <td>Apple grew by 78% in Q1 2020 in India, major c...</td>\n",
       "      <td>urn:newsml:reuters.com:20200430:nNRAbo9gve:1</td>\n",
       "      <td>NS:HINDUT</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76250</th>\n",
       "      <td>2020-04-30 07:00:00.000</td>\n",
       "      <td>2020-04-30 07:00:00+00:00</td>\n",
       "      <td>EXCLUSIVE-Finance, tech firms on hiring spree ...</td>\n",
       "      <td>urn:newsml:reuters.com:20200430:nL4N2CH2DP:1</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76251</th>\n",
       "      <td>2020-04-30 06:04:28.873</td>\n",
       "      <td>2020-04-30 06:04:28.873000+00:00</td>\n",
       "      <td>Contact tracing Letters to the Editor</td>\n",
       "      <td>urn:newsml:reuters.com:20200430:nNRAbo8o1l:1</td>\n",
       "      <td>NS:TELGPH</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>76252 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        index                   versionCreated  \\\n",
       "0     2019-06-12 23:00:11.983 2019-06-12 23:00:11.983000+00:00   \n",
       "1     2019-06-12 21:57:27.059 2019-06-12 21:57:27.059000+00:00   \n",
       "2     2019-06-12 21:32:15.868 2019-06-12 21:32:15.868000+00:00   \n",
       "3     2019-06-12 21:27:02.000        2019-06-12 21:27:02+00:00   \n",
       "4     2019-06-12 20:27:24.000        2019-06-12 20:27:24+00:00   \n",
       "...                       ...                              ...   \n",
       "76247 2020-02-18 04:21:35.000        2020-02-18 20:20:37+00:00   \n",
       "76248 2020-04-30 07:20:46.999 2020-04-30 07:20:49.299000+00:00   \n",
       "76249 2020-04-30 07:03:12.228 2020-04-30 07:03:12.228000+00:00   \n",
       "76250 2020-04-30 07:00:00.000        2020-04-30 07:00:00+00:00   \n",
       "76251 2020-04-30 06:04:28.873 2020-04-30 06:04:28.873000+00:00   \n",
       "\n",
       "                                                    text  \\\n",
       "0      Amazon Boosts Cashierless Initiatives With 2nd...   \n",
       "1      Reuters Insider - Facebook is sinking back int...   \n",
       "2      Reuters Insider - The question is how will tar...   \n",
       "3      Republican senator criticizes potential dual U...   \n",
       "4      Pompeo to push in India for more U.S. access t...   \n",
       "...                                                  ...   \n",
       "76247  UPDATE 9-Oil near flat; virus impact offsets L...   \n",
       "76248          Dow Jones Selected Stocks 1710 - April 30   \n",
       "76249  Apple grew by 78% in Q1 2020 in India, major c...   \n",
       "76250  EXCLUSIVE-Finance, tech firms on hiring spree ...   \n",
       "76251              Contact tracing Letters to the Editor   \n",
       "\n",
       "                                             storyId sourceCode ticker  \\\n",
       "0       urn:newsml:reuters.com:20190612:nNRA8y19v4:1  NS:ZACKSC   AMZN   \n",
       "1       urn:newsml:reuters.com:20190612:nRTV1vZVFx:1    NS:CNBC   AMZN   \n",
       "2       urn:newsml:reuters.com:20190612:nRTV1cdVkS:1    NS:CNBC   AMZN   \n",
       "3       urn:newsml:reuters.com:20190612:nL2N23J1K6:3    NS:RTRS   AMZN   \n",
       "4       urn:newsml:reuters.com:20190612:nL2N23J1FG:5    NS:RTRS   AMZN   \n",
       "...                                              ...        ...    ...   \n",
       "76247  urn:newsml:reuters.com:20200218:nL4N2AI139:16    NS:RTRS   AAPL   \n",
       "76248      urn:newsml:newsroom:20200430:nNRAbo9mnf:0     NS:AAP   AAPL   \n",
       "76249   urn:newsml:reuters.com:20200430:nNRAbo9gve:1  NS:HINDUT   AAPL   \n",
       "76250   urn:newsml:reuters.com:20200430:nL4N2CH2DP:1    NS:RTRS   AAPL   \n",
       "76251   urn:newsml:reuters.com:20200430:nNRAbo8o1l:1  NS:TELGPH   AAPL   \n",
       "\n",
       "            date  \n",
       "0     2019-06-12  \n",
       "1     2019-06-12  \n",
       "2     2019-06-12  \n",
       "3     2019-06-12  \n",
       "4     2019-06-12  \n",
       "...          ...  \n",
       "76247 2020-02-18  \n",
       "76248 2020-04-30  \n",
       "76249 2020-04-30  \n",
       "76250 2020-04-30  \n",
       "76251 2020-04-30  \n",
       "\n",
       "[76252 rows x 7 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId = storyId[storyId['ticker'].isin(list(tickers['ticker']))]\n",
    "storyId.reset_index(drop=True, inplace=True)\n",
    "storyId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 76252 entries, 0 to 76251\n",
      "Data columns (total 7 columns):\n",
      " #   Column          Non-Null Count  Dtype              \n",
      "---  ------          --------------  -----              \n",
      " 0   index           76252 non-null  datetime64[ns]     \n",
      " 1   versionCreated  76252 non-null  datetime64[ns, UTC]\n",
      " 2   text            76252 non-null  object             \n",
      " 3   storyId         76252 non-null  object             \n",
      " 4   sourceCode      76252 non-null  object             \n",
      " 5   ticker          76252 non-null  object             \n",
      " 6   date            76252 non-null  datetime64[ns]     \n",
      "dtypes: datetime64[ns, UTC](1), datetime64[ns](2), object(4)\n",
      "memory usage: 4.1+ MB\n"
     ]
    }
   ],
   "source": [
    "storyId.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>12289</td>\n",
       "      <td>33.668493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMT</th>\n",
       "      <td>625</td>\n",
       "      <td>1.712329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMZN</th>\n",
       "      <td>15754</td>\n",
       "      <td>43.161644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOGL</th>\n",
       "      <td>8821</td>\n",
       "      <td>24.167123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JNJ</th>\n",
       "      <td>4233</td>\n",
       "      <td>11.597260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPM</th>\n",
       "      <td>20737</td>\n",
       "      <td>56.813699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LIN</th>\n",
       "      <td>1199</td>\n",
       "      <td>3.284932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NEE</th>\n",
       "      <td>1087</td>\n",
       "      <td>2.978082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNP</th>\n",
       "      <td>985</td>\n",
       "      <td>2.698630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMT</th>\n",
       "      <td>6020</td>\n",
       "      <td>16.493151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>XOM</th>\n",
       "      <td>4502</td>\n",
       "      <td>12.334247</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            0          1\n",
       "ticker                  \n",
       "AAPL    12289  33.668493\n",
       "AMT       625   1.712329\n",
       "AMZN    15754  43.161644\n",
       "GOOGL    8821  24.167123\n",
       "JNJ      4233  11.597260\n",
       "JPM     20737  56.813699\n",
       "LIN      1199   3.284932\n",
       "NEE      1087   2.978082\n",
       "UNP       985   2.698630\n",
       "WMT      6020  16.493151\n",
       "XOM      4502  12.334247"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([storyId.groupby(['ticker']).size(), storyId.groupby(['ticker']).size()/365], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting the dates that do not overlap for the 5 tickers and 28 tickers samples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the initial 5 tickers we collected the storyId starting from 2019-6-9, however for the remaining 28 we started from 2019-6-12. The end date for the initial 5 tickers was 2019-6-12, for the remaining 28 is 2020-6-16.<br>The overlaping dates therefore are from 2019-6-12 to 2020-6-12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_date = datetime.datetime(2019, 6, 12)\n",
    "end_date = datetime.datetime(2020, 6, 12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "85"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId[(storyId['date'] < start_date)].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "386"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId[(storyId['date'] > end_date)].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>index</th>\n",
       "      <th>versionCreated</th>\n",
       "      <th>text</th>\n",
       "      <th>storyId</th>\n",
       "      <th>sourceCode</th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-12 23:00:11.983</td>\n",
       "      <td>2019-06-12 23:00:11.983000+00:00</td>\n",
       "      <td>Amazon Boosts Cashierless Initiatives With 2nd...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nNRA8y19v4:1</td>\n",
       "      <td>NS:ZACKSC</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-12 21:57:27.059</td>\n",
       "      <td>2019-06-12 21:57:27.059000+00:00</td>\n",
       "      <td>Reuters Insider - Facebook is sinking back int...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nRTV1vZVFx:1</td>\n",
       "      <td>NS:CNBC</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-12 21:32:15.868</td>\n",
       "      <td>2019-06-12 21:32:15.868000+00:00</td>\n",
       "      <td>Reuters Insider - The question is how will tar...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nRTV1cdVkS:1</td>\n",
       "      <td>NS:CNBC</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-12 21:27:02.000</td>\n",
       "      <td>2019-06-12 21:27:02+00:00</td>\n",
       "      <td>Republican senator criticizes potential dual U...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nL2N23J1K6:3</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-12 20:27:24.000</td>\n",
       "      <td>2019-06-12 20:27:24+00:00</td>\n",
       "      <td>Pompeo to push in India for more U.S. access t...</td>\n",
       "      <td>urn:newsml:reuters.com:20190612:nL2N23J1FG:5</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76247</th>\n",
       "      <td>2020-02-18 04:21:35.000</td>\n",
       "      <td>2020-02-18 20:20:37+00:00</td>\n",
       "      <td>UPDATE 9-Oil near flat; virus impact offsets L...</td>\n",
       "      <td>urn:newsml:reuters.com:20200218:nL4N2AI139:16</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-02-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76248</th>\n",
       "      <td>2020-04-30 07:20:46.999</td>\n",
       "      <td>2020-04-30 07:20:49.299000+00:00</td>\n",
       "      <td>Dow Jones Selected Stocks 1710 - April 30</td>\n",
       "      <td>urn:newsml:newsroom:20200430:nNRAbo9mnf:0</td>\n",
       "      <td>NS:AAP</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76249</th>\n",
       "      <td>2020-04-30 07:03:12.228</td>\n",
       "      <td>2020-04-30 07:03:12.228000+00:00</td>\n",
       "      <td>Apple grew by 78% in Q1 2020 in India, major c...</td>\n",
       "      <td>urn:newsml:reuters.com:20200430:nNRAbo9gve:1</td>\n",
       "      <td>NS:HINDUT</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76250</th>\n",
       "      <td>2020-04-30 07:00:00.000</td>\n",
       "      <td>2020-04-30 07:00:00+00:00</td>\n",
       "      <td>EXCLUSIVE-Finance, tech firms on hiring spree ...</td>\n",
       "      <td>urn:newsml:reuters.com:20200430:nL4N2CH2DP:1</td>\n",
       "      <td>NS:RTRS</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76251</th>\n",
       "      <td>2020-04-30 06:04:28.873</td>\n",
       "      <td>2020-04-30 06:04:28.873000+00:00</td>\n",
       "      <td>Contact tracing Letters to the Editor</td>\n",
       "      <td>urn:newsml:reuters.com:20200430:nNRAbo8o1l:1</td>\n",
       "      <td>NS:TELGPH</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-04-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75781 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        index                   versionCreated  \\\n",
       "0     2019-06-12 23:00:11.983 2019-06-12 23:00:11.983000+00:00   \n",
       "1     2019-06-12 21:57:27.059 2019-06-12 21:57:27.059000+00:00   \n",
       "2     2019-06-12 21:32:15.868 2019-06-12 21:32:15.868000+00:00   \n",
       "3     2019-06-12 21:27:02.000        2019-06-12 21:27:02+00:00   \n",
       "4     2019-06-12 20:27:24.000        2019-06-12 20:27:24+00:00   \n",
       "...                       ...                              ...   \n",
       "76247 2020-02-18 04:21:35.000        2020-02-18 20:20:37+00:00   \n",
       "76248 2020-04-30 07:20:46.999 2020-04-30 07:20:49.299000+00:00   \n",
       "76249 2020-04-30 07:03:12.228 2020-04-30 07:03:12.228000+00:00   \n",
       "76250 2020-04-30 07:00:00.000        2020-04-30 07:00:00+00:00   \n",
       "76251 2020-04-30 06:04:28.873 2020-04-30 06:04:28.873000+00:00   \n",
       "\n",
       "                                                    text  \\\n",
       "0      Amazon Boosts Cashierless Initiatives With 2nd...   \n",
       "1      Reuters Insider - Facebook is sinking back int...   \n",
       "2      Reuters Insider - The question is how will tar...   \n",
       "3      Republican senator criticizes potential dual U...   \n",
       "4      Pompeo to push in India for more U.S. access t...   \n",
       "...                                                  ...   \n",
       "76247  UPDATE 9-Oil near flat; virus impact offsets L...   \n",
       "76248          Dow Jones Selected Stocks 1710 - April 30   \n",
       "76249  Apple grew by 78% in Q1 2020 in India, major c...   \n",
       "76250  EXCLUSIVE-Finance, tech firms on hiring spree ...   \n",
       "76251              Contact tracing Letters to the Editor   \n",
       "\n",
       "                                             storyId sourceCode ticker  \\\n",
       "0       urn:newsml:reuters.com:20190612:nNRA8y19v4:1  NS:ZACKSC   AMZN   \n",
       "1       urn:newsml:reuters.com:20190612:nRTV1vZVFx:1    NS:CNBC   AMZN   \n",
       "2       urn:newsml:reuters.com:20190612:nRTV1cdVkS:1    NS:CNBC   AMZN   \n",
       "3       urn:newsml:reuters.com:20190612:nL2N23J1K6:3    NS:RTRS   AMZN   \n",
       "4       urn:newsml:reuters.com:20190612:nL2N23J1FG:5    NS:RTRS   AMZN   \n",
       "...                                              ...        ...    ...   \n",
       "76247  urn:newsml:reuters.com:20200218:nL4N2AI139:16    NS:RTRS   AAPL   \n",
       "76248      urn:newsml:newsroom:20200430:nNRAbo9mnf:0     NS:AAP   AAPL   \n",
       "76249   urn:newsml:reuters.com:20200430:nNRAbo9gve:1  NS:HINDUT   AAPL   \n",
       "76250   urn:newsml:reuters.com:20200430:nL4N2CH2DP:1    NS:RTRS   AAPL   \n",
       "76251   urn:newsml:reuters.com:20200430:nNRAbo8o1l:1  NS:TELGPH   AAPL   \n",
       "\n",
       "            date  \n",
       "0     2019-06-12  \n",
       "1     2019-06-12  \n",
       "2     2019-06-12  \n",
       "3     2019-06-12  \n",
       "4     2019-06-12  \n",
       "...          ...  \n",
       "76247 2020-02-18  \n",
       "76248 2020-04-30  \n",
       "76249 2020-04-30  \n",
       "76250 2020-04-30  \n",
       "76251 2020-04-30  \n",
       "\n",
       "[75781 rows x 7 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId = storyId[(storyId['date'] <= end_date)]\n",
    "storyId = storyId[(storyId['date'] >= start_date)]\n",
    "storyId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                   75781\n",
       "unique                    367\n",
       "top       2019-12-03 00:00:00\n",
       "freq                      794\n",
       "first     2019-06-12 00:00:00\n",
       "last      2020-06-12 00:00:00\n",
       "Name: date, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storyId['date'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a list of dates to iter over"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-06-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-06-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-06-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>2020-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>2020-06-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <td>2020-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>2020-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>2020-06-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>367 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date\n",
       "0   2019-06-12\n",
       "1   2019-06-13\n",
       "2   2019-06-14\n",
       "3   2019-06-15\n",
       "4   2019-06-16\n",
       "..         ...\n",
       "362 2020-06-08\n",
       "363 2020-06-09\n",
       "364 2020-06-10\n",
       "365 2020-06-11\n",
       "366 2020-06-12\n",
       "\n",
       "[367 rows x 1 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_list = pd.DataFrame((pd.to_datetime(storyId['date'].dt.date.unique())))\n",
    "date_list.columns = ['date']\n",
    "date_list.sort_values(by=['date'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2020 is leap year, plus 1 date (2020-6-12) which is the first of the next solar solar year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>2020-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>2020-02-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>2020-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>2020-02-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>2020-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>2020-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>2020-06-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <td>2020-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>2020-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>2020-06-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>131 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date\n",
       "236 2020-02-03\n",
       "237 2020-02-04\n",
       "238 2020-02-05\n",
       "239 2020-02-06\n",
       "240 2020-02-07\n",
       "..         ...\n",
       "362 2020-06-08\n",
       "363 2020-06-09\n",
       "364 2020-06-10\n",
       "365 2020-06-11\n",
       "366 2020-06-12\n",
       "\n",
       "[131 rows x 1 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_list = date_list[date_list['date'] > datetime.datetime(2020,2,2)]\n",
    "date_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2020-03-13 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first</th>\n",
       "      <td>2020-02-03 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last</th>\n",
       "      <td>2020-06-12 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       date\n",
       "count                   131\n",
       "unique                  131\n",
       "top     2020-03-13 00:00:00\n",
       "freq                      1\n",
       "first   2020-02-03 00:00:00\n",
       "last    2020-06-12 00:00:00"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_list.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating date groups in storyId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_group = storyId.groupby('date')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Collecting the news raw text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "j = 0\n",
    "#iterate on the dates\n",
    "for date in tqdm(date_list.itertuples(), desc = 'dates list'):\n",
    "    #initialize the  data frame for the current iteration date\n",
    "    news_text = pd.DataFrame(columns = ['storyId', 'story'])    \n",
    "    #assign the current iteration date to a variable\n",
    "    date = date.date.strftime('%Y-%m-%d')\n",
    "    #assign the data frame that contains the current iteration date to a variable\n",
    "    storyId_in_date = date_group.get_group(date)['storyId']\n",
    "    print('########################## start collecting {} news for the date {}'.format(storyId_in_date.shape[0], date))     \n",
    "    #iterate on the storyId for the current iteration date\n",
    "    \n",
    "    #check if API requests limit number has been hit\n",
    "    if (j + storyId_in_date.shape[0]) > 10000:\n",
    "        break\n",
    "        print('hit limit of {} API requests'.format(j))\n",
    "        \n",
    "    for i, storyId in tqdm(enumerate(storyId_in_date), desc = 'storyID Loop'):\n",
    "        #print('{} {}'.format(i, storyId))\n",
    "        j += 1\n",
    "        if 'html' in locals():\n",
    "            del html\n",
    "        try:\n",
    "            html = ek.get_news_story(storyId)\n",
    "        except:\n",
    "            print('!!!!!!!!!!!!!!!!!!!!! story {} story-date {} story Id: {} NOT FOUND'.format(j, i, storyId))\n",
    "            #append the current iteration story to news_text, write np.nan if the story is not found\n",
    "            news_text = pd.concat([news_text, pd.DataFrame([{'storyId': storyId,'story': np.nan}])]) \n",
    "            continue\n",
    "        #get the text part parsing the html   \n",
    "        story = BeautifulSoup(html, 'html5lib').get_text() \n",
    "        print('story {} story-date {} story Id: {} story: {}'.format(j, i, storyId, story[0:50]))\n",
    "        #append the current iteration story to news_text\n",
    "        news_text = pd.concat([news_text, pd.DataFrame([{'storyId': storyId,'story': story}])])   \n",
    "        \n",
    "    #write on a file the stories collected for the current iteration date\n",
    "    #pickle.dump(news_text, open(data_path + '\\\\story\\\\{}.pkl'.format(date), 'wb'))\n",
    "    news_text.to_csv(data_path + 'story\\\\{}.csv'.format(date), header=False, index=False,encoding = 'utf-8')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}