0_retrieve_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f9663c91-12c9-4d5c-a62c-f422ecc797ed",
   "metadata": {},
   "source": [
    "Retrieve time series of market cap to calculate number of stocks, exchange market cap and primary stocks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "13d2c680-f70d-4c78-b911-ee028466dc5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Libraries\n",
    "import eikon as ek\n",
    "import csv\n",
    "import pandas as pd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2e7b2a09-d555-4504-9c39-61dc3bf75034",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Credentials\n",
    "ek.set_app_key(\"YOUR_KEY\")\n",
    "\n",
    "# Circumvence the problem with 404 Bad request\n",
    "# https://community.developers.refinitiv.com/questions/45723/why-eikonerror-error-code-400-backend-error-400-ba.html\n",
    "ek.set_timeout(10000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a155bf15-8f9e-42a1-9739-c70215c0f7dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Screener: exclude BETX [One exchange has to be excluded otherwise eikon does not fetch the data] from SCREENER\n",
    "# The rics we want to have information for\n",
    "ric = \"SCREEN(U(IN(Equity(active or inactive,public,primary))/*UNV:Public*/), NOT_IN(TR.ExchangeMarketIdCode,\"\"BETX\"\"), CURN=USD)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9aa3706c-58c6-4bc0-a7b3-187650195bb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "field = [\"TR.CommonName\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "34067cef-76d5-4d8a-9e43-2602167b1ade",
   "metadata": {},
   "outputs": [],
   "source": [
    "df, err = ek.get_data(ric, field)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "edbb67ad-c2a2-428e-a34c-2fc005caefbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slice the data for rics and create a list for ek.get_data()\n",
    "rics = list(df[\"Instrument\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "140ff8d3-2903-4eaf-986a-0f71f19541b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73353"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Instrument\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "04bd65b7-ec1f-416d-baaa-0d77e6f7495b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# From Data Item\n",
    "field = [\"TR.CompanyMarketCap.Date; TR.CompanyMarketCap\"]\n",
    "start = \"20000131\"\n",
    "end   = \"20200131\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "998c1e23-8397-4877-8203-6f9957416086",
   "metadata": {},
   "outputs": [],
   "source": [
    "ls_RIC = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3cb01d6f-dd8a-48d1-b463-f991dda15f73",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 24/24 [00:00<00:00, 459.58it/s]\n"
     ]
    }
   ],
   "source": [
    "# Eikon has a limit of 5000 rics per retrieval. Choose 3000 to see a progress in the for loop for ek.get_data().\n",
    "interval = 3000\n",
    "i = 3000\n",
    "ric_set = range(round(df3[\"Instrument\"].count()/ interval))\n",
    "\n",
    "for rics in tqdm(ric_set):\n",
    "    ls_RIC[\"RIC_SET_{0}\".format(rics)] = list(df3['Instrument'][i - interval:i].astype('str'))\n",
    "    i += interval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f56e7618-5771-4470-806a-e2a75f429d3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ls_RIC_keys = list(ls_RIC.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6aa6de60-1c2e-4a59-87e2-a8add9848bf2",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c2b6404-0a02-4b88-9f6c-ebd10d45ee49",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 0/2 [00:00<?, ?it/s]"
     ]
    }
   ],
   "source": [
    "# The for-loop is splitted in three chunks because it crashes with 400 error bad request (An internal Eikon error)\n",
    "# https://community.developers.refinitiv.com/questions/45723/why-eikonerror-error-code-400-backend-error-400-ba.html\n",
    "\n",
    "for keys in tqdm(ls_RIC_keys[:8]):\n",
    "    data_dict[\"data_{0}\".format(keys)], err = ek.get_data(ls_RIC.get(keys), field, \n",
    "                                                          {\"SDate\":start, \n",
    "                                                           \"EDate\":end, \n",
    "                                                           \"Frq\":\"Y\", \n",
    "                                                           \"curn\":\"USD\", \n",
    "                                                           # in Millions\n",
    "                                                           \"scale\":\"6\"\n",
    "                                                          })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e0cbb7c-dad4-42a2-a252-bac1e3367305",
   "metadata": {},
   "outputs": [],
   "source": [
    "for keys in tqdm(ls_RIC_keys[8:16]):\n",
    "    data_dict[\"data_{0}\".format(keys)], err = ek.get_data(ls_RIC.get(keys), field, \n",
    "                                                          {\"SDate\":start, \n",
    "                                                           \"EDate\":end, \n",
    "                                                           \"Frq\":\"Y\", \n",
    "                                                           \"curn\":\"USD\", \n",
    "                                                           # in Millions\n",
    "                                                           \"scale\":\"6\"\n",
    "                                                          })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34fe68a0-1de2-413c-8c47-cc1315dbf40d",
   "metadata": {},
   "outputs": [],
   "source": [
    "for keys in tqdm(ls_RIC_keys[16:]):\n",
    "    data_dict[\"data_{0}\".format(keys)], err = ek.get_data(ls_RIC.get(keys), field, \n",
    "                                                          {\"SDate\":start, \n",
    "                                                           \"EDate\":end, \n",
    "                                                           \"Frq\":\"Y\", \n",
    "                                                           \"curn\":\"USD\", \n",
    "                                                           # in Millions\n",
    "                                                           \"scale\":\"6\"\n",
    "                                                          })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0c42099-9ef0-4fed-90a8-fd46cd081941",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the dictionary to get one data frame to write as csv later on.\n",
    "df = pd.concat(data_dict, axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc7a3115-1422-4356-b486-fdc9d483acf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "870709bc-0acf-4634-a245-ba71e05ff204",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "297330e9-aa4f-405b-9379-2383c8278a39",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns = [\"level_0\", \"level_1\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33ae80cd-0d86-4a16-ad1b-0cbd3907d464",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(r'market_cap_over_time.csv')"
   ]
  }
 ],
 "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.8.8"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}