extract_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "import json\n",
    "from tqdm import tqdm\n",
    "import concurrent.futures\n",
    "from urllib.parse import urlparse\n",
    "import bisect\n",
    "import time\n",
    "from requests import Session\n",
    "from requests_cache import CacheMixin, SQLiteCache\n",
    "from requests_ratelimiter import LimiterMixin, MemoryQueueBucket\n",
    "from pyrate_limiter import Duration, RequestRate, Limiter\n",
    "\n",
    "class CachedLimiterSession(CacheMixin, LimiterMixin, Session):\n",
    "    pass\n",
    "\n",
    "HOUR = 60 * 60\n",
    "DAY = HOUR * 24\n",
    "YF_HISTORY_LIMIT = 730 * DAY      # limit by the yfinance package\n",
    "\n",
    "news_data_file_path = 'TickerTick-stock-news-dataset.2023-11-23.json'\n",
    "\n",
    "sp500_data = pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')[0]\n",
    "sp500_tickers = set(sp500_data['Symbol'].tolist())\n",
    "\n",
    "sp100_data = pd.read_html('https://en.wikipedia.org/wiki/S%26P_100')[2]\n",
    "sp100_tickers = set(sp100_data['Symbol'].tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://stackoverflow.com/questions/5543651/computing-standard-deviation-in-a-stream\n",
    "class OnlineStats:\n",
    "    def __init__(self, iterable=None, ddof=1):\n",
    "        self.ddof, self.n, self.mean, self.M2 = ddof, 0, 0.0, 0.0\n",
    "        if iterable is not None:\n",
    "            for datum in iterable:\n",
    "                self.include(datum)\n",
    "\n",
    "    def include(self, datum):\n",
    "        self.n += 1\n",
    "        self.delta = datum - self.mean\n",
    "        self.mean += self.delta / self.n\n",
    "        self.M2 += self.delta * (datum - self.mean)\n",
    "\n",
    "    def variance(self):\n",
    "        return self.M2 / (self.n - self.ddof)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "9867339it [02:35, 63630.18it/s]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "32479"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from collections import defaultdict\n",
    "\n",
    "hostname_desc_stats_map = defaultdict(OnlineStats)\n",
    "\n",
    "with open('TickerTick-stock-news-dataset.2023-11-23.json', \"r\") as f:\n",
    "    pbar = tqdm()\n",
    "    while (line := f.readline()):\n",
    "        story = json.loads(line)\n",
    "        if \"url\" in story:\n",
    "            url = story[\"url\"]\n",
    "            hostname = urlparse(url).hostname\n",
    "            hostname_desc_stats_map[hostname].include(len(story[\"description\"]) if \"description\" in story else 0)\n",
    "        pbar.update(1)\n",
    "    pbar.close()\n",
    "\n",
    "len(hostname_desc_stats_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>domain</th>\n",
       "      <th>n</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>www.tickerreport.com</td>\n",
       "      <td>782351</td>\n",
       "      <td>355.701036</td>\n",
       "      <td>20.414411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>finance.yahoo.com</td>\n",
       "      <td>762210</td>\n",
       "      <td>234.421086</td>\n",
       "      <td>342.909783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>seekingalpha.com</td>\n",
       "      <td>446532</td>\n",
       "      <td>0.030849</td>\n",
       "      <td>2.914709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>www.etfdailynews.com</td>\n",
       "      <td>263621</td>\n",
       "      <td>354.313712</td>\n",
       "      <td>20.617613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>www.reddit.com</td>\n",
       "      <td>229657</td>\n",
       "      <td>791.079379</td>\n",
       "      <td>1187.396293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>news.cgtn.com</td>\n",
       "      <td>1023</td>\n",
       "      <td>5203.140762</td>\n",
       "      <td>1989.244992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>www.gq.com</td>\n",
       "      <td>1019</td>\n",
       "      <td>95.922473</td>\n",
       "      <td>52.830705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>www.datadoghq.com</td>\n",
       "      <td>1018</td>\n",
       "      <td>178.916503</td>\n",
       "      <td>270.155249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>www.anandtech.com</td>\n",
       "      <td>1008</td>\n",
       "      <td>1510.179563</td>\n",
       "      <td>1832.775524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>aithority.com</td>\n",
       "      <td>1007</td>\n",
       "      <td>4663.858987</td>\n",
       "      <td>1758.432450</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>865 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   domain       n         mean          std\n",
       "30   www.tickerreport.com  782351   355.701036    20.414411\n",
       "137     finance.yahoo.com  762210   234.421086   342.909783\n",
       "35       seekingalpha.com  446532     0.030849     2.914709\n",
       "29   www.etfdailynews.com  263621   354.313712    20.617613\n",
       "28         www.reddit.com  229657   791.079379  1187.396293\n",
       "..                    ...     ...          ...          ...\n",
       "652         news.cgtn.com    1023  5203.140762  1989.244992\n",
       "176            www.gq.com    1019    95.922473    52.830705\n",
       "618     www.datadoghq.com    1018   178.916503   270.155249\n",
       "381     www.anandtech.com    1008  1510.179563  1832.775524\n",
       "445         aithority.com    1007  4663.858987  1758.432450\n",
       "\n",
       "[865 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "domain_desc_stats = []\n",
    "\n",
    "for hostname, stats in hostname_desc_stats_map.items():\n",
    "    if stats.n > 1000:\n",
    "        domain_desc_stats.append({\"domain\": hostname, \"n\": stats.n, \"mean\": stats.mean, \"std\": stats.variance() ** 0.5})\n",
    "\n",
    "desc_stats_df = pd.DataFrame(domain_desc_stats)\n",
    "desc_stats_df = desc_stats_df.sort_values(by=\"n\", ascending=False)\n",
    "desc_stats_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "9867339it [02:26, 67162.58it/s] \n"
     ]
    }
   ],
   "source": [
    "# Filter the original dataset to only include Yahoo Finance stories\n",
    "\n",
    "yf_news_stories = []\n",
    "with open(news_data_file_path, \"r\") as f:\n",
    "   pbar = tqdm()\n",
    "   while (line := f.readline()):\n",
    "      story = json.loads(line)\n",
    "      if \"url\" in story and urlparse(story[\"url\"]).hostname == \"finance.yahoo.com\":\n",
    "         yf_news_stories.append(story)\n",
    "      pbar.update(1)\n",
    "   pbar.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data query range: (2022-05-03 20:00:00-04:00, 2023-11-29 01:45:18-05:00)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 0/503 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING: cookie & crumb does not work well with requests_cache. Am experimenting with 'expire_after=DO_NOT_CACHE', but you need to help stress-test.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 19%|█▉        | 95/503 [00:05<00:54,  7.47it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Skipping SOLV\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 40%|████      | 202/503 [00:10<00:33,  9.02it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Skipping BRK.B\n",
      "Skipping CPAY\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 64%|██████▍   | 323/503 [00:14<00:05, 31.68it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Skipping BF.B\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 66%|██████▌   | 331/503 [00:15<00:12, 13.27it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Skipping GEV\n",
      "Skipping DAY\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 503/503 [00:20<00:00, 24.21it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fetch errors: [Exception(\"SOLV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception(\"SOLV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception(\"SOLV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception(\"SOLV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception('BRK.B: No timezone found, symbol may be delisted'), Exception('BRK.B: No timezone found, symbol may be delisted'), Exception('BRK.B: No timezone found, symbol may be delisted'), Exception('BRK.B: No timezone found, symbol may be delisted'), Exception('CPAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('CPAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('CPAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('CPAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('BF.B: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('BF.B: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('BF.B: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('BF.B: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception(\"GEV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception(\"GEV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception(\"GEV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception(\"GEV: Data doesn't exist for startDate = 1651622400, endDate = 1701240318\"), Exception('DAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('DAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('DAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)'), Exception('DAY: No price data found, symbol may be delisted (1h 1651622400 -> 1701240318)')]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(497, 503)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session = CachedLimiterSession(\n",
    "    limiter=Limiter(RequestRate(2, Duration.SECOND*5)),  # max 2 requests per 5 seconds\n",
    "    bucket_class=MemoryQueueBucket,\n",
    "    backend=SQLiteCache(\"yfinance.cache\"),\n",
    ")\n",
    "# session = requests_cache.CachedSession('yfinance.cache')\n",
    "session.headers['User-agent'] = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36'\n",
    "\n",
    "query_startime = int(time.time()) // DAY * DAY  - (YF_HISTORY_LIMIT - DAY)\n",
    "\n",
    "for story in yf_news_stories:\n",
    "    if \"unix_timestamp\" in story and type(story[\"unix_timestamp\"]) == int:\n",
    "        query_endtime = story[\"unix_timestamp\"] + 5 * DAY\n",
    "        break\n",
    "else:\n",
    "    raise Exception(\"Data appears to be empty or missing unix_timestamp field.\")\n",
    "\n",
    "print(f\"Data query range: ({pd.Timestamp(query_startime, unit='s', tz='US/Eastern')}, {pd.Timestamp(query_endtime, unit='s', tz='US/Eastern')})\")\n",
    "\n",
    "ticker_history = {}\n",
    "ticker_info = {}\n",
    "fetch_errors = []\n",
    "\n",
    "for ticker in tqdm(sp500_tickers):\n",
    "    for retry in range(4):\n",
    "      try:\n",
    "          yfTicker = yf.Ticker(ticker, session=session)\n",
    "          ticker_info[ticker] = yfTicker.info\n",
    "          hist_df = yfTicker.history(start=query_startime, end=query_endtime, interval='1h', raise_errors=True)\n",
    "          hist_df['ts_int'] = hist_df.index.values.astype('int64') // 1e9\n",
    "          ticker_history[ticker] = hist_df\n",
    "          break\n",
    "      except Exception as e:\n",
    "          fetch_errors.append(e)\n",
    "    else:\n",
    "      print(f\"Skipping {ticker}\")\n",
    "\n",
    "print(f\"Fetch errors: {fetch_errors}\")\n",
    "len(ticker_history), len(ticker_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\"datetime\", \"title\", \"description\", \"ticker\", \"company\", \"sector\", \"industry\", \"change_pct\"]\n",
    "column_idx = {col: i for i, col in enumerate(columns)}\n",
    "\n",
    "def process_valid_story(story, tickers):\n",
    "    rows = []\n",
    "\n",
    "    timestamp_int = story[\"unix_timestamp\"]\n",
    "    timestamp = pd.Timestamp(timestamp_int, unit='s', tz='US/Eastern')\n",
    "\n",
    "    row = [None] * len(columns)\n",
    "    row[column_idx[\"datetime\"]] = timestamp\n",
    "    row[column_idx[\"title\"]] = story[\"title\"]\n",
    "    row[column_idx[\"description\"]] = story[\"description\"] if \"description\" in story else \"\"\n",
    "\n",
    "    for ticker in tickers:\n",
    "        hist_df = ticker_history[ticker]\n",
    "        info = ticker_info[ticker]\n",
    "\n",
    "        ts_ints = hist_df[\"ts_int\"].values\n",
    "        init_ts_int = timestamp_int - 2 * HOUR\n",
    "        final_ts_int = timestamp_int + 6 * HOUR\n",
    "\n",
    "        init_idx = bisect.bisect_right(ts_ints, init_ts_int) - 1                 # bisect_right - 1: rightmost value less than or equal to x\n",
    "        final_idx = bisect.bisect_left(ts_ints, final_ts_int, lo=init_idx+1)     # bisect_left: leftmost item greater than or equal to x\n",
    "        \n",
    "        if init_idx == 0 and ts_ints[init_idx] > init_ts_int:\n",
    "            continue\n",
    "\n",
    "        init_price = hist_df.iloc[init_idx][\"Close\"]\n",
    "        final_price = hist_df.iloc[final_idx][\"Close\"]\n",
    "        change_pct = (final_price - init_price) / init_price * 100\n",
    "        \n",
    "        row_copy = row.copy()\n",
    "        row_copy[column_idx[\"ticker\"]] = ticker\n",
    "        row_copy[column_idx[\"company\"]] = info[\"longName\"]\n",
    "        row_copy[column_idx[\"sector\"]] = info[\"sector\"]\n",
    "        row_copy[column_idx[\"industry\"]] = info[\"industry\"]\n",
    "        row_copy[column_idx[\"change_pct\"]] = change_pct\n",
    "        rows.append(row_copy)\n",
    "\n",
    "    return rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "180775"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = []\n",
    "parse_errors = []\n",
    "future_story_map = {}\n",
    "now = time.time()\n",
    "\n",
    "with concurrent.futures.ThreadPoolExecutor() as executor:\n",
    "    for story in yf_news_stories:\n",
    "        tickers = [T for t in story.get(\"tickers_direct\", []) + story.get(\"tickers_indirect\", []) if (T := t.upper()) in ticker_history]\n",
    "        if len(tickers) == 0:\n",
    "            continue\n",
    "\n",
    "        if \"unix_timestamp\" not in story and type(story[\"unix_timestamp\"]) != int:\n",
    "            continue\n",
    "\n",
    "        if now - story[\"unix_timestamp\"] >= (YF_HISTORY_LIMIT - DAY):\n",
    "            break\n",
    "\n",
    "        if \"description\" in story and len(story[\"description\"]) > 0:\n",
    "            future_story_map[executor.submit(process_valid_story, story, tickers)] = story\n",
    "    \n",
    "len(future_story_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 180775/180775 [00:00<00:00, 317759.22it/s]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 parse errors: []\n"
     ]
    },
    {
     "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>datetime</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>ticker</th>\n",
       "      <th>company</th>\n",
       "      <th>sector</th>\n",
       "      <th>industry</th>\n",
       "      <th>change_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wed 04 May 2022, 12:00AM</td>\n",
       "      <td>BlackRock targets ‘industrial renaissance’ wit...</td>\n",
       "      <td>The two iShares ETFs are the manager’s first r...</td>\n",
       "      <td>BLK</td>\n",
       "      <td>BlackRock, Inc.</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Asset Management</td>\n",
       "      <td>-13.482159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Wed 04 May 2022, 12:00AM</td>\n",
       "      <td>Big 5 Sporting Goods (BGFV) Q1 2022 Earnings C...</td>\n",
       "      <td>Image source: The Motley Fool. Big 5 Sporting ...</td>\n",
       "      <td>NDAQ</td>\n",
       "      <td>Nasdaq, Inc.</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Financial Data &amp; Stock Exchanges</td>\n",
       "      <td>-6.146185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wed 04 May 2022, 12:00AM</td>\n",
       "      <td>Advanced Micro Devices (AMD) Q1 2022 Earnings ...</td>\n",
       "      <td>It's now my pleasure to turn the call over to ...</td>\n",
       "      <td>AMD</td>\n",
       "      <td>Advanced Micro Devices, Inc.</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Semiconductors</td>\n",
       "      <td>-23.202964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wed 04 May 2022, 12:05AM</td>\n",
       "      <td>Elon Musk Has a Fascinating Idea to Make Money...</td>\n",
       "      <td>Elon Musk hears criticism and never hesitates ...</td>\n",
       "      <td>TSLA</td>\n",
       "      <td>Tesla, Inc.</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>Auto Manufacturers</td>\n",
       "      <td>20.651853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wed 04 May 2022, 12:30AM</td>\n",
       "      <td>Airbnb, Inc. (ABNB) Q1 2022 Earnings Call Tran...</td>\n",
       "      <td>Good afternoon, and thank you for joining Airb...</td>\n",
       "      <td>ABNB</td>\n",
       "      <td>Airbnb, Inc.</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>Travel Services</td>\n",
       "      <td>18.279398</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227502</th>\n",
       "      <td>Thu 23 Nov 2023, 04:47PM</td>\n",
       "      <td>Corrections &amp; Amplifications - The success of ...</td>\n",
       "      <td>The success of blood thinners being developed ...</td>\n",
       "      <td>BMY</td>\n",
       "      <td>Bristol-Myers Squibb Company</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>Drug Manufacturers - General</td>\n",
       "      <td>0.323559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227503</th>\n",
       "      <td>Thu 23 Nov 2023, 05:40PM</td>\n",
       "      <td>Crews Extinguish Fire After CSX Train Derailme...</td>\n",
       "      <td>Hundreds of emergency workers in Kentucky put ...</td>\n",
       "      <td>CSX</td>\n",
       "      <td>CSX Corporation</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>Railroads</td>\n",
       "      <td>-0.199597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227504</th>\n",
       "      <td>Thu 23 Nov 2023, 05:43PM</td>\n",
       "      <td>UPDATE 1-German union Verdi calls for strikes ...</td>\n",
       "      <td>German trade union Verdi has called on members...</td>\n",
       "      <td>AMZN</td>\n",
       "      <td>Amazon.com, Inc.</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>Internet Retail</td>\n",
       "      <td>-0.825589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227505</th>\n",
       "      <td>Thu 23 Nov 2023, 07:00PM</td>\n",
       "      <td>10 Can’t Miss Black Friday Electronics Deals a...</td>\n",
       "      <td>The biggest shopping day of the season is upon...</td>\n",
       "      <td>COST</td>\n",
       "      <td>Costco Wholesale Corporation</td>\n",
       "      <td>Consumer Defensive</td>\n",
       "      <td>Discount Stores</td>\n",
       "      <td>0.592448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227506</th>\n",
       "      <td>Fri 24 Nov 2023, 12:00AM</td>\n",
       "      <td>OpenAI turmoil exposes threat to Microsoft’s i...</td>\n",
       "      <td>Microsoft chief executive Satya Nadella’s deci...</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>Technology</td>\n",
       "      <td>Software - Infrastructure</td>\n",
       "      <td>-0.500163</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>227507 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        datetime  \\\n",
       "0       Wed 04 May 2022, 12:00AM   \n",
       "1       Wed 04 May 2022, 12:00AM   \n",
       "2       Wed 04 May 2022, 12:00AM   \n",
       "3       Wed 04 May 2022, 12:05AM   \n",
       "4       Wed 04 May 2022, 12:30AM   \n",
       "...                          ...   \n",
       "227502  Thu 23 Nov 2023, 04:47PM   \n",
       "227503  Thu 23 Nov 2023, 05:40PM   \n",
       "227504  Thu 23 Nov 2023, 05:43PM   \n",
       "227505  Thu 23 Nov 2023, 07:00PM   \n",
       "227506  Fri 24 Nov 2023, 12:00AM   \n",
       "\n",
       "                                                    title  \\\n",
       "0       BlackRock targets ‘industrial renaissance’ wit...   \n",
       "1       Big 5 Sporting Goods (BGFV) Q1 2022 Earnings C...   \n",
       "2       Advanced Micro Devices (AMD) Q1 2022 Earnings ...   \n",
       "3       Elon Musk Has a Fascinating Idea to Make Money...   \n",
       "4       Airbnb, Inc. (ABNB) Q1 2022 Earnings Call Tran...   \n",
       "...                                                   ...   \n",
       "227502  Corrections & Amplifications - The success of ...   \n",
       "227503  Crews Extinguish Fire After CSX Train Derailme...   \n",
       "227504  UPDATE 1-German union Verdi calls for strikes ...   \n",
       "227505  10 Can’t Miss Black Friday Electronics Deals a...   \n",
       "227506  OpenAI turmoil exposes threat to Microsoft’s i...   \n",
       "\n",
       "                                              description ticker  \\\n",
       "0       The two iShares ETFs are the manager’s first r...    BLK   \n",
       "1       Image source: The Motley Fool. Big 5 Sporting ...   NDAQ   \n",
       "2       It's now my pleasure to turn the call over to ...    AMD   \n",
       "3       Elon Musk hears criticism and never hesitates ...   TSLA   \n",
       "4       Good afternoon, and thank you for joining Airb...   ABNB   \n",
       "...                                                   ...    ...   \n",
       "227502  The success of blood thinners being developed ...    BMY   \n",
       "227503  Hundreds of emergency workers in Kentucky put ...    CSX   \n",
       "227504  German trade union Verdi has called on members...   AMZN   \n",
       "227505  The biggest shopping day of the season is upon...   COST   \n",
       "227506  Microsoft chief executive Satya Nadella’s deci...   MSFT   \n",
       "\n",
       "                             company              sector  \\\n",
       "0                    BlackRock, Inc.  Financial Services   \n",
       "1                       Nasdaq, Inc.  Financial Services   \n",
       "2       Advanced Micro Devices, Inc.          Technology   \n",
       "3                        Tesla, Inc.   Consumer Cyclical   \n",
       "4                       Airbnb, Inc.   Consumer Cyclical   \n",
       "...                              ...                 ...   \n",
       "227502  Bristol-Myers Squibb Company          Healthcare   \n",
       "227503               CSX Corporation         Industrials   \n",
       "227504              Amazon.com, Inc.   Consumer Cyclical   \n",
       "227505  Costco Wholesale Corporation  Consumer Defensive   \n",
       "227506         Microsoft Corporation          Technology   \n",
       "\n",
       "                                industry  change_pct  \n",
       "0                       Asset Management  -13.482159  \n",
       "1       Financial Data & Stock Exchanges   -6.146185  \n",
       "2                         Semiconductors  -23.202964  \n",
       "3                     Auto Manufacturers   20.651853  \n",
       "4                        Travel Services   18.279398  \n",
       "...                                  ...         ...  \n",
       "227502      Drug Manufacturers - General    0.323559  \n",
       "227503                         Railroads   -0.199597  \n",
       "227504                   Internet Retail   -0.825589  \n",
       "227505                   Discount Stores    0.592448  \n",
       "227506         Software - Infrastructure   -0.500163  \n",
       "\n",
       "[227507 rows x 8 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for future in tqdm(concurrent.futures.as_completed(future_story_map.keys()), total=len(future_story_map)):\n",
    "    try:\n",
    "        rows = future.result()\n",
    "        dataset.extend(rows)\n",
    "            \n",
    "    except Exception as e:\n",
    "        story = future_story_map[future]\n",
    "        if \"id\" in story:\n",
    "            parse_errors.append((story[\"id\"], e))\n",
    "\n",
    "print(f\"{len(parse_errors)} parse errors: {parse_errors}\")\n",
    "\n",
    "df = pd.DataFrame(dataset, columns=columns)\n",
    "df.sort_values(by=\"datetime\", inplace=True, ascending=True)\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "df['datetime'] = df['datetime'].dt.strftime('%a %d %b %Y, %I:%M%p')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"dataset.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}