GDS_2_tickers.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import time\n",
    "import numpy as np\n",
    "import refinitiv.dataplatform as rdp\n",
    "import eikon as ek"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('emissions.csv')\n",
    "df_new = df.sample(n=125)\n",
    "df_new = df_new.reset_index(drop=True)\n",
    "df_new\n",
    "\n",
    "df_new.to_csv('company_list.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>Company</th>\n",
       "      <th>E1-2018</th>\n",
       "      <th>E1-2019</th>\n",
       "      <th>E1-2020</th>\n",
       "      <th>E1-2021</th>\n",
       "      <th>E2L-2018</th>\n",
       "      <th>E2L-2019</th>\n",
       "      <th>E2L-2020</th>\n",
       "      <th>E2L-2021</th>\n",
       "      <th>E2M-2018</th>\n",
       "      <th>E2M-2019</th>\n",
       "      <th>E2M-2020</th>\n",
       "      <th>E2M-2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AECOM</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>23149</td>\n",
       "      <td>19689</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>70870</td>\n",
       "      <td>53796</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Stanley Black &amp; Decker, Inc.</td>\n",
       "      <td>109159</td>\n",
       "      <td>118711</td>\n",
       "      <td>99558</td>\n",
       "      <td>110288</td>\n",
       "      <td>361575</td>\n",
       "      <td>326427</td>\n",
       "      <td>322430</td>\n",
       "      <td>342012</td>\n",
       "      <td>240818</td>\n",
       "      <td>148621</td>\n",
       "      <td>147279</td>\n",
       "      <td>138940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Imperial Brands</td>\n",
       "      <td>108241</td>\n",
       "      <td>99577</td>\n",
       "      <td>95987</td>\n",
       "      <td>91129</td>\n",
       "      <td>150348</td>\n",
       "      <td>147039</td>\n",
       "      <td>143390</td>\n",
       "      <td>132550</td>\n",
       "      <td>155648</td>\n",
       "      <td>131737</td>\n",
       "      <td>136401</td>\n",
       "      <td>85476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trane Technologies</td>\n",
       "      <td>343565</td>\n",
       "      <td>304585</td>\n",
       "      <td>273621</td>\n",
       "      <td>257632</td>\n",
       "      <td>192939</td>\n",
       "      <td>154027</td>\n",
       "      <td>139193</td>\n",
       "      <td>137264</td>\n",
       "      <td>191786</td>\n",
       "      <td>141753</td>\n",
       "      <td>75149</td>\n",
       "      <td>61086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DS Smith Plc</td>\n",
       "      <td>1750321</td>\n",
       "      <td>2400758</td>\n",
       "      <td>2047265</td>\n",
       "      <td>2023278</td>\n",
       "      <td>623218</td>\n",
       "      <td>867705</td>\n",
       "      <td>894631</td>\n",
       "      <td>886062</td>\n",
       "      <td>527020</td>\n",
       "      <td>803370</td>\n",
       "      <td>763727</td>\n",
       "      <td>759257</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>United Utilities</td>\n",
       "      <td>119354</td>\n",
       "      <td>127172</td>\n",
       "      <td>132575</td>\n",
       "      <td>131735</td>\n",
       "      <td>187171</td>\n",
       "      <td>164521</td>\n",
       "      <td>149030</td>\n",
       "      <td>134492</td>\n",
       "      <td>18503</td>\n",
       "      <td>11789</td>\n",
       "      <td>8507</td>\n",
       "      <td>4201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>PUMA SE</td>\n",
       "      <td>6918</td>\n",
       "      <td>6326</td>\n",
       "      <td>4179</td>\n",
       "      <td>4046</td>\n",
       "      <td>43366</td>\n",
       "      <td>40986</td>\n",
       "      <td>29839</td>\n",
       "      <td>32545</td>\n",
       "      <td>22128</td>\n",
       "      <td>11533</td>\n",
       "      <td>1078</td>\n",
       "      <td>1458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Ardagh Group SA</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>2422025</td>\n",
       "      <td>2480614</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1075758</td>\n",
       "      <td>933743</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Tech Mahindra</td>\n",
       "      <td>2561</td>\n",
       "      <td>1804</td>\n",
       "      <td>7930</td>\n",
       "      <td>8996</td>\n",
       "      <td>108418</td>\n",
       "      <td>105737</td>\n",
       "      <td>66419</td>\n",
       "      <td>57852</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Unilever plc</td>\n",
       "      <td>752804</td>\n",
       "      <td>659028</td>\n",
       "      <td>606771</td>\n",
       "      <td>565988</td>\n",
       "      <td>1430659</td>\n",
       "      <td>1321648</td>\n",
       "      <td>1264328</td>\n",
       "      <td>1244505</td>\n",
       "      <td>826076</td>\n",
       "      <td>443897</td>\n",
       "      <td>216740</td>\n",
       "      <td>144752</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>125 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Company  E1-2018  E1-2019  E1-2020  E1-2021  \\\n",
       "0                           AECOM        -        -    23149    19689   \n",
       "1    Stanley Black & Decker, Inc.   109159   118711    99558   110288   \n",
       "2                 Imperial Brands   108241    99577    95987    91129   \n",
       "3              Trane Technologies   343565   304585   273621   257632   \n",
       "4                    DS Smith Plc  1750321  2400758  2047265  2023278   \n",
       "..                            ...      ...      ...      ...      ...   \n",
       "120              United Utilities   119354   127172   132575   131735   \n",
       "121                       PUMA SE     6918     6326     4179     4046   \n",
       "122               Ardagh Group SA        -        -  2422025  2480614   \n",
       "123                 Tech Mahindra     2561     1804     7930     8996   \n",
       "124                  Unilever plc   752804   659028   606771   565988   \n",
       "\n",
       "    E2L-2018 E2L-2019 E2L-2020 E2L-2021 E2M-2018 E2M-2019 E2M-2020 E2M-2021  \n",
       "0          -        -    70870    53796        -        -        -        -  \n",
       "1     361575   326427   322430   342012   240818   148621   147279   138940  \n",
       "2     150348   147039   143390   132550   155648   131737   136401    85476  \n",
       "3     192939   154027   139193   137264   191786   141753    75149    61086  \n",
       "4     623218   867705   894631   886062   527020   803370   763727   759257  \n",
       "..       ...      ...      ...      ...      ...      ...      ...      ...  \n",
       "120   187171   164521   149030   134492    18503    11789     8507     4201  \n",
       "121    43366    40986    29839    32545    22128    11533     1078     1458  \n",
       "122        -        -  1075758   933743        -        -        -        -  \n",
       "123   108418   105737    66419    57852        -        -        -        -  \n",
       "124  1430659  1321648  1264328  1244505   826076   443897   216740   144752  \n",
       "\n",
       "[125 rows x 13 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     SNC.LS  SNC.LS    Sonaecom SGPS SA           SNC\n",
      "1      ACM.N   ACM.N               AECOM           ACM\n",
      "None\n",
      "  Instrument     RIC          Company Common Name Ticker Symbol\n",
      "0      IMB.L   IMB.L          Imperial Brands PLC           IMB\n",
      "1     67SR.L  67SR.L  Imperial Brands Finance PLC          67SR\n",
      "  Instrument   RIC     Company Common Name Ticker Symbol\n",
      "0       TT.N  TT.N  Trane Technologies PLC            TT\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     SMDS.L  SMDS.L        DS Smith PLC          SMDS\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     FLS.CO  FLS.CO   FLSmidth & Co A/S           FLS\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     MNDI.L  MNDI.L           Mondi PLC          MNDI\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     LEVI.N  LEVI.N   Levi Strauss & Co          LEVI\n",
      "None\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      FGP.L  FGP.L      FirstGroup PLC           FGP\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    CAPP.PA  CAPP.PA        Capgemini SE           CAP\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    HSIC.OQ  HSIC.OQ    Henry Schein Inc          HSIC\n",
      "  Instrument     RIC    Company Common Name Ticker Symbol\n",
      "0     DELL.N  DELL.N  Dell Technologies Inc          DELL\n",
      "  Instrument    RIC    Company Common Name Ticker Symbol\n",
      "0      ETN.N  ETN.N  Eaton Corporation PLC           ETN\n",
      "None\n",
      "  Instrument        RIC Company Common Name Ticker Symbol\n",
      "0  PNDORA.CO  PNDORA.CO         Pandora A/S        PNDORA\n",
      "None\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0       BT.L    BT.L        BT Group PLC          BT.A\n",
      "1     PBGJ.J  PBGJ.J       PBT Group Ltd           PBG\n",
      "None\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     ITRK.L  ITRK.L  Intertek Group PLC          ITRK\n",
      "        Instrument              RIC                 Company Common Name  \\\n",
      "0          CBQP.PA          CBQP.PA                           Unibel SA   \n",
      "1         ALUCI.PA         ALUCI.PA                          Lucibel SA   \n",
      "2  BE0945246784.BR  BE0945246784.BR                         Fimmobel SA   \n",
      "3  BE6265912038.BR  BE6265912038.BR                         Infrabel SA   \n",
      "4  BE0032095854.BR  BE0032095854.BR  Beurrerie du Pays D'Herve Aubel SA   \n",
      "5          UNLP.WA          UNLP.WA                     United Label SA   \n",
      "\n",
      "  Ticker Symbol  \n",
      "0          UNBL  \n",
      "1         ALUCI  \n",
      "2                \n",
      "3                \n",
      "4                \n",
      "5           UNL  \n",
      "None\n",
      "  Instrument       RIC Company Common Name Ticker Symbol\n",
      "0   OUT1V.HE  OUT1V.HE       Outokumpu Oyj         OUT1V\n",
      "None\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     CTVA.N  CTVA.N         Corteva Inc          CTVA\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    RWEG.DE  RWEG.DE              RWE AG           RWE\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    DOCU.OQ  DOCU.OQ        DocuSign Inc          DOCU\n",
      "None\n",
      "  Instrument    RIC                  Company Common Name Ticker Symbol\n",
      "0      PEG.N  PEG.N  Public Service Enterprise Group Inc           PEG\n",
      "None\n",
      "None\n",
      "None\n",
      "       Instrument           RIC  \\\n",
      "0         COLR.BR       COLR.BR   \n",
      "1         GBLB.BR       GBLB.BR   \n",
      "2           MMU.N         MMU.N   \n",
      "3         TESB.BR       TESB.BR   \n",
      "4          RUS.TO        RUS.TO   \n",
      "..            ...           ...   \n",
      "512  MIRAEF5D1.BO  MIRAEF5D1.BO   \n",
      "513      ESSN.ASE      ESSN.ASE   \n",
      "514  MIRAEF6D1.BO  MIRAEF6D1.BO   \n",
      "515     448830.KQ     448830.KQ   \n",
      "516       BEMA.NS       BEMA.NS   \n",
      "\n",
      "                                   Company Common Name Ticker Symbol  \n",
      "0                     Etablissementen Franz Colruyt NV          COLR  \n",
      "1                             Groep Brussel Lambert NV          GBLB  \n",
      "2            Western Asset Managed Municipals Fund Inc           MMU  \n",
      "3                                 Tessenderlo Group NV          TESB  \n",
      "4                                    Russel Metals Inc           RUS  \n",
      "..                                                 ...           ...  \n",
      "512  Mirae Asset Fixed Maturity Plan Series V Plan ...        543770  \n",
      "513                              Essentially Group PLC          ESSN  \n",
      "514     Mirae Asset FMP Srs V Plan 2  Regular Plan Gth        543820  \n",
      "515  Mirae Asset Vision Special Purpose Acquisition...        448830  \n",
      "516                               BEML Land Assets Ltd          BLAL  \n",
      "\n",
      "[517 rows x 4 columns]\n",
      "None\n",
      "None\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    RAND.AS  RAND.AS         Randstad NV          RAND\n",
      "None\n",
      "None\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     BALF.L  BALF.L  Balfour Beatty PLC           BBY\n",
      "  Instrument      RIC      Company Common Name Ticker Symbol\n",
      "0    LULU.OQ  LULU.OQ  Lululemon Athletica Inc          LULU\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      CNA.L  CNA.L        Centrica PLC           CNA\n",
      "None\n",
      "  Instrument        RIC Company Common Name Ticker Symbol\n",
      "0  DEMANT.CO  DEMANT.CO          Demant A/S        DEMANT\n",
      "   Instrument        RIC                                Company Common Name  \\\n",
      "0      MSLH.L     MSLH.L                                      Marshalls PLC   \n",
      "1      MARS.L     MARS.L                                      Marston's PLC   \n",
      "2       MMC.N      MMC.N                     Marsh & McLennan Companies Inc   \n",
      "3    MRSHL.IS   MRSHL.IS                  Marshall Boya ve Vernik Sanayi AS   \n",
      "4      MMH.NZ     MMH.NZ                      Marsden Maritime Holdings Ltd   \n",
      "5      6419.T     6419.T                           Mars Group Holdings Corp   \n",
      "6     SIMR.BO    SIMR.BO                              Simmonds Marshall Ltd   \n",
      "7     MNBP.PK    MNBP.PK                                   Mars Bancorp Inc   \n",
      "8     MARS.BO    MARS.BO                                        Marsons Ltd   \n",
      "9     BLMC.PK    BLMC.PK                            Biloxi Marsh Lands Corp   \n",
      "10   ALTPC.PA   ALTPC.PA   Societe Marseillaise du Tunnel Prado Carenage SA   \n",
      "11      BPM.L      BPM.L                          B.P. Marsh & Partners PLC   \n",
      "12    SUMP.SJ    SUMP.SJ                          Sumarstvo Prenj dd Konjic   \n",
      "13    NEIM.BJ    NEIM.BJ                   Neimarstvo ad Trebinje u stecaju   \n",
      "14    SBLM.SJ    SBLM.SJ                  Soko Bravarsko Limarsko dd Mostar   \n",
      "15    MMAT.CA    MMAT.CA       Marsa Marsa Alam for Tourism Development SAE   \n",
      "16   INST.MOT   INST.MOT                 Institut za Sumarstvo ad Podgorica   \n",
      "17   POIS.MOT   POIS.MOT                Poljoprivreda i Sumarstvo ad Berane   \n",
      "18     MMPJ.J     MMPJ.J                             Marshall Monteagle PLC   \n",
      "19    JMSB.OQ    JMSB.OQ                          John Marshall Bancorp Inc   \n",
      "20    MAAL.CA    MAAL.CA  Marseilia Egyptian Gulf Real Estate Investment...   \n",
      "21  300380.SZ  300380.SZ  Shanghai Amarsoft Information & Technology Co Ltd   \n",
      "22     MMH.GH     MMH.GH             Meridian-Marshalls Holding Company Ltd   \n",
      "23    RDMR.PK    RDMR.PK                                  Road Marshall Inc   \n",
      "24  300894.SZ  300894.SZ                      Marssenger Kitchenware Co Ltd   \n",
      "25    MARH.NS    MARH.NS                              Marshall Machines Ltd   \n",
      "26   6708.TWO   6708.TWO                            Mars Semiconductor Corp   \n",
      "27    MARX.OQ    MARX.OQ                              Mars Acquisition Corp   \n",
      "\n",
      "   Ticker Symbol  \n",
      "0           MSLH  \n",
      "1           MARS  \n",
      "2            MMC  \n",
      "3        MRSHL.E  \n",
      "4            MMH  \n",
      "5           6419  \n",
      "6         507998  \n",
      "7           MNBP  \n",
      "8         517467  \n",
      "9           BLMC  \n",
      "10         ALTPC  \n",
      "11           BPM  \n",
      "12         SUMPR  \n",
      "13      NEIM-R-A  \n",
      "14         SBLMR  \n",
      "15          MMAT  \n",
      "16          INST  \n",
      "17          POIS  \n",
      "18           MMP  \n",
      "19          JMSB  \n",
      "20          MAAL  \n",
      "21        300380  \n",
      "22           MMH  \n",
      "23          RDMR  \n",
      "24        300894  \n",
      "25      MARSHALL  \n",
      "26          6708  \n",
      "27          MARX  \n",
      "  Instrument        RIC Company Common Name  Ticker Symbol\n",
      "0  066570.KS  066570.KS  LG Electronics Inc          66570\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     ABBV.N  ABBV.N          Abbvie Inc          ABBV\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      TPR.N  TPR.N        Tapestry Inc           TPR\n",
      "None\n",
      "None\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      DGE.L  DGE.L          Diageo PLC           DGE\n",
      "None\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    LYFT.OQ  LYFT.OQ            Lyft Inc          LYFT\n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    PUBP.PA  PUBP.PA  Publicis Groupe SA           PUB\n",
      "  Instrument       RIC Company Common Name Ticker Symbol\n",
      "0   HOLMb.ST  HOLMb.ST           Holmen AB        HOLM B\n",
      "  Instrument    RIC            Company Common Name Ticker Symbol\n",
      "0      AEO.N  AEO.N  American Eagle Outfitters Inc           AEO\n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     SCMN.S  SCMN.S         Swisscom AG          SCMN\n",
      "  Instrument     RIC      Company Common Name Ticker Symbol\n",
      "0     VIE.PA  VIE.PA  Veolia Environnement SA           VIE\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    ATEA.OL  ATEA.OL            Atea ASA          ATEA\n",
      "  Instrument       RIC Company Common Name Ticker Symbol\n",
      "0   ABEV3.SA  ABEV3.SA            Ambev SA         ABEV3\n",
      "  Instrument      RIC              Company Common Name  Ticker Symbol\n",
      "0    2232.HK  2232.HK  Crystal International Group Ltd           2232\n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument       RIC Company Common Name Ticker Symbol\n",
      "0   SSABa.ST  SSABa.ST             SSAB AB        SSAB A\n",
      "1    MSBN.PK   MSBN.PK       Messaben Corp          MSBN\n",
      "None\n",
      "  Instrument      RIC        Company Common Name Ticker Symbol\n",
      "0    FLTRF.L  FLTRF.L  Flutter Entertainment PLC          FLTR\n",
      "  Instrument     RIC     Company Common Name Ticker Symbol\n",
      "0      KGF.L   KGF.L          Kingfisher PLC           KGF\n",
      "1      KFR.V   KFR.V  Kingfisher Metals Corp           KFR\n",
      "2     KFM.AX  KFM.AX   Kingfisher Mining Ltd           KFM\n",
      "  Instrument    RIC                           Company Common Name  \\\n",
      "0      CRL.N  CRL.N  Charles River Laboratories International Inc   \n",
      "\n",
      "  Ticker Symbol  \n",
      "0           CRL  \n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    EXHO.PA  EXHO.PA           Sodexo SA            SW\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     ADNT.N  ADNT.N          Adient PLC          ADNT\n",
      "None\n",
      "  Instrument     RIC   Company Common Name Ticker Symbol\n",
      "0     SCHP.S  SCHP.S  Schindler Holding AG          SCHP\n",
      "  Instrument   RIC Company Common Name Ticker Symbol\n",
      "0       NG.L  NG.L   National Grid PLC           NG.\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      VST.N  VST.N         Vistra Corp           VST\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    QCOM.OQ  QCOM.OQ        Qualcomm Inc          QCOM\n",
      "  Instrument       RIC                      Company Common Name Ticker Symbol\n",
      "0     TSCO.L    TSCO.L                                Tesco PLC          TSCO\n",
      "1    EVER.PS   EVER.PS  Ever Gotesco Resources and Holdings Inc          EVER\n",
      "2     6268.T    6268.T                            Nabtesco Corp          6268\n",
      "3    MITI.PK   MITI.PK                              Mitesco Inc          MITI\n",
      "4     ATS.HN    ATS.HN          Atesco Industrial Cartering JSC           ATS\n",
      "5   VTSCn.DE  VTSCn.DE            Vitesco Technologies Group AG          VTSC\n",
      "6    MTCb.CR   MTCb.CR         Montesco Fondo Agroindustrial CA         MTC.B\n",
      "None\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    KRNG.DE  KRNG.DE           Krones AG           KRN\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument     RIC      Company Common Name Ticker Symbol\n",
      "0     CRDA.L  CRDA.L  Croda International PLC          CRDA\n",
      "None\n",
      "None\n",
      "   Instrument       RIC                                Company Common Name  \\\n",
      "0     TNNP.NS   TNNP.NS                   Tamilnadu Newsprint & Papers Ltd   \n",
      "1     ZIMP.ZI   ZIMP.ZI                     Zimbabwe Newspapers (1980) Ltd   \n",
      "2     SRNP.NS   SRNP.NS                           Shree Rama Newsprint Ltd   \n",
      "3     VWSP.NS   VWSP.NS                                      Vikas WSP Ltd   \n",
      "4        FP.V      FP.V                                  FP Newspapers Inc   \n",
      "5     CRLN.BO   CRLN.BO                               Coral Newsprints Ltd   \n",
      "6     ACNI.PK   ACNI.PK                  American Community Newspapers Inc   \n",
      "7      WSP.TO    WSP.TO                                     WSP Global Inc   \n",
      "8    FLSP.EUB  FLSP.EUB                                      Flowsparks NV   \n",
      "9     SLTN.PK   SLTN.PK                                       Growspot Inc   \n",
      "10     PTW.WA    PTW.WA  Polskie Towarzystwo Wspierania Przedsiebiorczo...   \n",
      "\n",
      "   Ticker Symbol  \n",
      "0           TNPL  \n",
      "1           ZIMP  \n",
      "2       RAMANEWS  \n",
      "3       VIKASWSP  \n",
      "4             FP  \n",
      "5         530755  \n",
      "6           ACNI  \n",
      "7            WSP  \n",
      "8           FLSP  \n",
      "9           SLTN  \n",
      "10           PTW  \n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      ECL.N  ECL.N          Ecolab Inc           ECL\n",
      "None\n",
      "  Instrument        RIC                                Company Common Name  \\\n",
      "0  002555.SZ  002555.SZ  37 Interactive Entertainment Network Technolog...   \n",
      "\n",
      "   Ticker Symbol  \n",
      "0           2555  \n",
      "None\n",
      "None\n",
      "  Instrument       RIC Company Common Name Ticker Symbol\n",
      "0   HUSQb.ST  HUSQb.ST        Husqvarna AB        HUSQ B\n",
      "None\n",
      "    Instrument         RIC                         Company Common Name  \\\n",
      "0        ABT.N       ABT.N                         Abbott Laboratories   \n",
      "1       ABB.NS      ABB.NS                               ABB India Ltd   \n",
      "2     SPRB.EUA    SPRB.EUA                       Speed Rabbit Pizza SA   \n",
      "3    RABBIT.BK   RABBIT.BK                         Rabbit Holdings PCL   \n",
      "4     ABBT.PSX    ABBT.PSX            Abbott Laboratories Pakistan Ltd   \n",
      "5     ALBS.PSX    ALBS.PSX                    Al-Abbas Sugar Mills Ltd   \n",
      "6     SHAB.PSX    SHAB.PSX                Shabbir Tiles & Ceramics Ltd   \n",
      "7      ABOT.NS     ABOT.NS                            Abbott India Ltd   \n",
      "8       ABBN.S      ABBN.S                                     Abb Ltd   \n",
      "9     BUDS.NFF    BUDS.NFF                                     Abbh AS   \n",
      "10   KABEI.RTS   KABEI.RTS                            Kabbalkenergo AO   \n",
      "11  ABBEYBD.LG  ABBEYBD.LG                     Abbey Mortgage Bank PLC   \n",
      "12     ABBY.PK     ABBY.PK                                    Abby Inc   \n",
      "13     BVAL.HT     BVAL.HT                       Banca Valsabbina SCpA   \n",
      "14      ABBV.N      ABBV.N                                  Abbvie Inc   \n",
      "15     2030.HK     2030.HK                         Cabbeen Fashion Ltd   \n",
      "16     FILA.MI     FILA.MI  FILA Fabbrica Italiana Lapis ed Affini SpA   \n",
      "17      RABI.V      RABI.V                                Adrabbit Ltd   \n",
      "18     KTAG.DE     KTAG.DE                            Knaus Tabbert AG   \n",
      "19      JABp.V      JABp.V                          Jabbo Capital Corp   \n",
      "20     2256.HK     2256.HK                          Abbisko Cayman Ltd   \n",
      "\n",
      "   Ticker Symbol  \n",
      "0            ABT  \n",
      "1            ABB  \n",
      "2          MLSRP  \n",
      "3         RABBIT  \n",
      "4           ABOT  \n",
      "5           AABS  \n",
      "6           STCL  \n",
      "7     ABBOTINDIA  \n",
      "8           ABBN  \n",
      "9           BUDS  \n",
      "10          kabe  \n",
      "11      ABBEYBDS  \n",
      "12          ABBY  \n",
      "13  IT0000220449  \n",
      "14          ABBV  \n",
      "15          2030  \n",
      "16          FILA  \n",
      "17          RABI  \n",
      "18           KTA  \n",
      "19         JAB.P  \n",
      "20          2256  \n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    AFRY.ST  AFRY.ST             Afry AB          AFRY\n",
      "  Instrument    RIC Company Common Name Ticker Symbol\n",
      "0      GPS.N  GPS.N             Gap Inc           GPS\n",
      "None\n",
      "None\n",
      "None\n",
      "  Instrument   RIC         Company Common Name Ticker Symbol\n",
      "0       UU.L  UU.L  United Utilities Group PLC           UU.\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    PUMG.DE  PUMG.DE             Puma SE           PUM\n",
      "None\n",
      "  Instrument      RIC Company Common Name Ticker Symbol\n",
      "0    TEML.NS  TEML.NS   Tech Mahindra Ltd         TECHM\n",
      "  Instrument     RIC Company Common Name Ticker Symbol\n",
      "0     ULVR.L  ULVR.L        Unilever PLC          ULVR\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>RIC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SNC.LS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IMB.L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TT.N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SMDS.L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>UU.L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>PUMG.DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>TEML.NS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>ULVR.L</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>125 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         RIC\n",
       "0     SNC.LS\n",
       "1        NaN\n",
       "2      IMB.L\n",
       "3       TT.N\n",
       "4     SMDS.L\n",
       "..       ...\n",
       "120     UU.L\n",
       "121  PUMG.DE\n",
       "122      NaN\n",
       "123  TEML.NS\n",
       "124   ULVR.L\n",
       "\n",
       "[125 rows x 1 columns]"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ek.set_app_key('58f10218ec2745df8b72a10d7f212b9428071259')\n",
    "companies = df_new['Company'].tolist()\n",
    "for i, co in enumerate(companies):\n",
    "    companies[i] = co.replace('Corporation', 'Corp')\n",
    "    companies[i] = co.replace('Incorporated', 'Inc')\n",
    "    companies[i] = co.replace('Limited', 'Ltd')\n",
    "    companies[i] = co.replace('Company', 'Co')\n",
    "    companies[i] = co.replace('Inc.', 'Inc')\n",
    "    companies[i] = co.replace('Corp.', 'Corp')\n",
    "    companies[i] = co.replace('Ltd.', 'Ltd')\n",
    "    companies[i] = co.replace(',', '')\n",
    "    companies[i] = co.replace('.', '')\n",
    "\n",
    "df_tickers = pd.DataFrame(0, index = np.arange(len(companies)), columns=['RIC'])\n",
    "\n",
    "\n",
    "for i, co in enumerate(companies):\n",
    "    company_name = co\n",
    "    screener_exp = 'SCREEN(U(IN(Equity(active,public,primary))),Contains(TR.CommonName,\"{}\"))'.format(company_name)\n",
    "    try:\n",
    "        company_ticker, err = ek.get_data(instruments=[screener_exp], fields = ['TR.RIC','TR.CommonName','TR.TickerSymbol'])\n",
    "    except:\n",
    "        pass\n",
    "    print(company_ticker)\n",
    "    if company_ticker is not None:\n",
    "        df_tickers['RIC'][i] = company_ticker['RIC'][0]\n",
    "    else:\n",
    "        df_tickers['RIC'][i] = np.nan\n",
    "\n",
    "df_tickers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tickers['RIC'][0] = 'ACM.N'\n",
    "df_tickers['RIC'][7] = '6758.T'\n",
    "df_tickers['RIC'][12] = '2308.TW'\n",
    "df_tickers['RIC'][14] = '4544.T'\n",
    "df_tickers['RIC'][16] = 'ADSK.O'\n",
    "df_tickers['RIC'][44] = 'SSE.L'\n",
    "df_tickers['RIC'][45] = 'MCD'\n",
    "df_tickers['RIC'][46] = 'THO'\n",
    "df_tickers['RIC'][48] = 'KLBN4.SA'\n",
    "df_tickers['RIC'][49] = '8802.T'\n",
    "df_tickers['RIC'][55] = 'MARX.O'\n",
    "df_tickers['RIC'][60] = 'MBG.MI'\n",
    "df_tickers['RIC'][64] = 'JKS'\n",
    "df_tickers['RIC'][79] = 'KNIN.S'\n",
    "df_tickers['RIC'][80] = '600567.SS'\n",
    "df_tickers['RIC'][82] = 'VOLCARb.ST'\n",
    "df_tickers['RIC'][88] = 'NESN.S'\n",
    "df_tickers['RIC'][96] = 'CAH'\n",
    "df_tickers['RIC'][97] = '2587.T'\n",
    "df_tickers['RIC'][98] = 'ETSY.O'\n",
    "df_tickers['RIC'][99] = 'OREP.PA'\n",
    "df_tickers['RIC'][101] = 'DPWGn.DE'\n",
    "df_tickers['RIC'][102] = 'IPG'\n",
    "df_tickers['RIC'][103] = 'WSP.TO'\n",
    "df_tickers['RIC'][104] = 'AXIA.KL'\n",
    "df_tickers['RIC'][105] = 'BDEV.F'\n",
    "df_tickers['RIC'][106] = 'FGVH.KL'\n",
    "df_tickers['RIC'][110] = 'ARCLK.IS'\n",
    "df_tickers['RIC'][111] = 'PEP.O'\n",
    "df_tickers['RIC'][113] = 'ADI.O'\n",
    "df_tickers['RIC'][114] = 'ABBN.S'\n",
    "df_tickers['RIC'][116] = 'GPS'\n",
    "df_tickers['RIC'][119] = 'HMb.ST'\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fin = df_tickers.merge(df_new, left_index=True, right_index=True)\n",
    "df_fin.dropna(subset=['RIC'], inplace=True)\n",
    "df_fin = df_fin.reset_index(drop=True)\n",
    "# df_fin.to_csv(r'C:\\Users\\Evan Mc Garry\\Desktop\\FDS\\FDS\\GDS\\final_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = df_fin.copy()\n",
    "df_list.drop(['E1-2018', 'E1-2019', 'E1-2020', 'E1-2021', 'E2L-2018', 'E2L-2019', 'E2L-2020', 'E2L-2021', 'E2M-2018', 'E2M-2019', 'E2M-2020', 'E2M-2021'], axis=1, inplace=True)\n",
    "# df_list.to_csv(r'C:\\Users\\Evan Mc Garry\\Desktop\\FDS\\FDS\\GDS\\ticker_list.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}