main_download_storyID_auto.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download storyID from Eikon for certain Tickers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:37.087066Z",
     "start_time": "2021-01-19T07:23:37.072558Z"
    }
   },
   "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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:37.975161Z",
     "start_time": "2021-01-19T07:23:37.963502Z"
    }
   },
   "outputs": [],
   "source": [
    "working_path=\"G:\\\\My Drive\\\\PhD\\\\Research\\\\Projects\\\\Portfolio Management and Sentiment views\\\\Data\\\\News\\\\Eikon\\\\test\\\\\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:38.279733Z",
     "start_time": "2021-01-19T07:23:38.265474Z"
    }
   },
   "outputs": [],
   "source": [
    "output_data_path = \"G:\\\\My Drive\\\\PhD\\\\Research\\\\Projects\\\\Portfolio Management and Sentiment views\\\\Data\\\\News\\\\Eikon\\\\test\"\n",
    "#data_path = \"C:\\\\Users\\\\cityu_local\\\\Downloads\\\\newsheadline_20190612_20200612_28_tickers\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Eikon API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:39.286781Z",
     "start_time": "2021-01-19T07:23:39.237231Z"
    }
   },
   "outputs": [],
   "source": [
    "with open(working_path+'Eikon_Api_Key.txt', 'r') as f:\n",
    "    cookie = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:02.409665Z",
     "start_time": "2021-01-19T07:22:47.459333Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import eikon as ek\n",
    "ek.set_app_key(cookie)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Self-definded Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following function transform Eikon RIC code into ISIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:41.208315Z",
     "start_time": "2021-01-19T07:23:41.196349Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_ISIN(ticker, verbose=False):\n",
    "    if verbose:\n",
    "        print(\"Getting ISIN code of {}\".format(ticker))\n",
    "    res = ek.get_symbology(ticker,\n",
    "                           from_symbol_type='RIC',\n",
    "                           to_symbol_type='ISIN')\n",
    "    if 'error' in res.columns:\n",
    "        ticker = \"{}.?\".format(ticker)\n",
    "        res = ek.get_symbology(ticker,\n",
    "                               from_symbol_type='RIC',\n",
    "                               to_symbol_type='ISIN')\n",
    "        if 'error' in res.columns:\n",
    "            return ''\n",
    "\n",
    "    ISIN_code = res.loc[ticker, 'ISIN']\n",
    "    if verbose:\n",
    "        print(\"ISIN Code of {}: {}\".format({ticker}, {ISIN_code}))\n",
    "    return ISIN_code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following function transform ISIN code into Eikon RIC code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:41.642629Z",
     "start_time": "2021-01-19T07:23:41.629277Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_RIC(ISIN_code, verbose = False):\n",
    "    if verbose:\n",
    "        print(\"Getting RIC code of {}\".format(ISIN_code))\n",
    "    res2 = ek.get_symbology(ISIN_code, \n",
    "                 from_symbol_type='ISIN', \n",
    "                 to_symbol_type='RIC')\n",
    "    if 'error' in res2.columns:\n",
    "        RIC_code = ''\n",
    "    else:\n",
    "        RIC_code = res2.loc[ISIN_code, 'RIC']\n",
    "    if verbose:\n",
    "        print(\"RIC Code of {}: {}\".format({ticker}, {RIC_code}))\n",
    "    return RIC_code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stock Tickers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the list of tickers from a file. Of course be sure you set the path correctly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:42.899019Z",
     "start_time": "2021-01-19T07:23:42.790719Z"
    }
   },
   "outputs": [],
   "source": [
    "tickers = pd.read_csv(working_path+\"myTickerList-Eikon.csv\",\n",
    "                         encoding = 'utf-8',\n",
    "                     index_col = 0).astype(str)\n",
    "ticker_list = list(tickers['ticker'])\n",
    "ticker_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively hardcode the list here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:43.899151Z",
     "start_time": "2021-01-19T07:23:43.879761Z"
    }
   },
   "outputs": [],
   "source": [
    "ticker_list=['AAPL','GS','PFE','SBUX','NEM']\n",
    "ticker_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:44.306132Z",
     "start_time": "2021-01-19T07:23:44.293131Z"
    }
   },
   "outputs": [],
   "source": [
    "len(ticker_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Crawling the storyID from Eikon with the API"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define how many months of news you want to collect starting from now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:45.905950Z",
     "start_time": "2021-01-19T07:23:45.893879Z"
    }
   },
   "outputs": [],
   "source": [
    "months_to_collect=12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:46.166714Z",
     "start_time": "2021-01-19T07:23:46.148758Z"
    }
   },
   "outputs": [],
   "source": [
    "now = datetime.datetime.now()\n",
    "start_date = (now - dateutil.relativedelta.relativedelta(months = months_to_collect)).strftime(\"%Y-%m-%d\")\n",
    "end_date = now.strftime(\"%Y-%m-%d\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As it can be seen from the code below, the first date is the oldest. The code starts to collect from the oldest date to the newest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:47.160339Z",
     "start_time": "2021-01-19T07:23:47.129763Z"
    }
   },
   "outputs": [],
   "source": [
    "date_range = pd.date_range(start = start_date,\n",
    "             end = end_date)\n",
    "date_range"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read from a path the already downloaded storyId in order to determine the remaning days to collect, avoiding to start over again every time the code is run. Of course be sure you set the path correctly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:48.509709Z",
     "start_time": "2021-01-19T07:23:48.459147Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "crawled_date = [os.path.split(item)[-1].replace('.csv', '') for item in glob.glob(os.path.join(working_path+\"storyID\\\\\", '*.csv'))]\n",
    "crawled_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-19T07:23:55.524966Z",
     "start_time": "2021-01-19T07:23:49.244294Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# parameters\n",
    "requests_limit = 100\n",
    "delta_hours = 24\n",
    "\n",
    "# dayly loop\n",
    "if not os.path.exists('newsheadline'):\n",
    "    os.makedirs('newsheadline')\n",
    "# crawled_date = [os.path.split(item)[-1].replace('.csv', '') for item in glob.glob(os.path.join('newsheadline', '*.csv'))]\n",
    "for date in tqdm(date_range, desc='Daily Loop'):\n",
    "    # GMT\n",
    "    date_to = (date + datetime.timedelta(days=1)).strftime(\"%Y-%m-%dT%H:%M:%S\")\n",
    "    date_from = date.strftime(\"%Y-%m-%dT%H:%M:%S\")\n",
    "    date_from_str = date.strftime(\"%Y-%m-%d\")\n",
    "    if date_from_str not in crawled_date:\n",
    "        print('Getting newsheadline for {}'.format(date_from_str))\n",
    "        news_headlines = pd.DataFrame()\n",
    "        \n",
    "        # RIC Code\n",
    "        for ticker in ticker_list:\n",
    "            RIC_code = tickers['RIC'][tickers['ticker'] == ticker].values[0]\n",
    "            print(\"####################################\\nStart collecting ticker: {}\".format(RIC_code))\n",
    "\n",
    "            # reset the time parameters\n",
    "            delta_hours_current = delta_hours\n",
    "            frequence = str(delta_hours_current)+'H'\n",
    "            periods = 24/delta_hours_current\n",
    "\n",
    "            # API calls manager. Break downs time range in multiple requests, if requests_limit is hit\n",
    "            while True:\n",
    "                \n",
    "                #reset requests_limit_hit to false. It isset to True every time a requests limit is hit\n",
    "                requests_limit_hit=False\n",
    "                \n",
    "                #create a new data frame to collect the headlines for the current ticker\n",
    "                news_headlines_ticker = pd.DataFrame()\n",
    "                \n",
    "                # generate the list of the data ranges of the requests\n",
    "                print(\"TRY delta_hours: {}, frequence: {}, periods {}: \".format(delta_hours_current, frequence, periods))\n",
    "                day_time = pd.date_range(start=date, periods=periods, freq=frequence)\n",
    "                print('list of data ranges to try:\\n', day_time)\n",
    "                \n",
    "                #loop over the list of data ranges, with frequency set by \"frequence\", for the current date \n",
    "                for day_time_i in day_time:\n",
    "\n",
    "                    # define the data ranges for the current request\n",
    "                    date_to_current_request = (day_time_i + datetime.timedelta(hours=delta_hours_current)).strftime(\"%Y-%m-%dT%H:%M:%S\")\n",
    "                    date_from_current_request = day_time_i.strftime(\"%Y-%m-%dT%H:%M:%S\")\n",
    "                    \n",
    "                    print(\"current start from: {}, current to: {}\".format(date_from_current_request, date_to_current_request))\n",
    "                    tmp = ek.get_news_headlines(\"R:{} IN ENGLISH\".format(RIC_code),\n",
    "                                                date_from=date_from_current_request,\n",
    "                                                date_to=date_to_current_request,\n",
    "                                                count=requests_limit)\n",
    "#                     tmp=pd.DataFrame(np.random.randint(0,100,size=(np.random.randint(80,105), 4)), columns=['A','B','C','ticker'])\n",
    "                    tmp['ticker'] = ticker\n",
    "                    print(\"received {} news for {}\".format(tmp.shape[0], ticker))\n",
    "            \n",
    "                    #check if the API has received more headlines than the requests limit\n",
    "                    if tmp.shape[0] >= requests_limit:\n",
    "                        requests_limit_hit=True\n",
    "                        print('!!!!!! REQUESTS LIMIT HIT !!!!!!')\n",
    "                        # increase data range frequence of requests\n",
    "                        delta_hours_current = delta_hours_current/2\n",
    "                        frequence = str(delta_hours_current)+'H'\n",
    "                        periods = 24/delta_hours_current\n",
    "                        break  # exit the for loop, it is needed to increase the frequence because the limit has been hit     \n",
    "                           \n",
    "                    #append the request to the news headlines so far collected for the current ticker\n",
    "                    #print(\"Add {} news for {} from {} to {}\".format(tmp.shape[0], ticker, date_from_current_request, date_to_current_request))\n",
    "                    news_headlines_ticker = pd.concat([news_headlines_ticker, tmp])\n",
    "                    time.sleep(0.2)\n",
    "                \n",
    "                #if requests limit has been hit skip to the next while loop iteration\n",
    "                if requests_limit_hit==True:\n",
    "                    continue\n",
    "                           \n",
    "                #land here when the for loop finishes (without hit the break), it means all the requests have not hit the limit\n",
    "                #all headlines collected for the current ticker without limits hit, the while loop can be break now\n",
    "                #print('For loop on the data ranges for current ticker: finished without hit the requests limit')\n",
    "                break\n",
    "\n",
    "            #land here when the while loop finishes. Add the ticker's headlines to the date's headlines\n",
    "            #print('While loop for current ticker: finished')\n",
    "            print(\"Add {} news for {} in {}\".format(news_headlines_ticker.shape[0], ticker, date_from))\n",
    "            news_headlines = pd.concat([news_headlines, news_headlines_ticker])\n",
    "            time.sleep(0.2)\n",
    "\n",
    "        # write the date newsheadlines\n",
    "        print('We have crawled {} newsheadline for {}'.format(news_headlines.shape[0], date_from_str))\n",
    "        news_headlines.to_csv(os.path.join(output_data_path + '\\\\storyId\\\\',\n",
    "                                  '{}.csv'.format(date_from_str)),encoding='utf-8')\n",
    "        time.sleep(0.2)\n",
    "    else:\n",
    "        print(\"We already crawled newsheadline for {}\".format(date_from_str))"
   ]
  },
  {
   "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}