Ingester_baby_steps.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Ingester\n",
    "    Port:7497\n",
    "    Client id: 35"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 Basics Concept"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 OOPS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "314.0\n"
     ]
    }
   ],
   "source": [
    "def cal_area(rad):\n",
    "    return 3.14*rad*rad\n",
    "\n",
    "print(cal_area(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Employee: Ram is created\n",
      "Salary for Employee: Ram is calculated\n",
      "Name: Ram Id: god Exp: 6 Dept: r&d Salary: 8\n"
     ]
    }
   ],
   "source": [
    "class Employee:\n",
    "    def __init__(self, name=\"Ram\",emp_id=\"god\",exp=6,dept=\"r&d\"): #magic fucntion executed automatically\n",
    "        self.name=name\n",
    "        self.emp_id=emp_id\n",
    "        self.exp=exp\n",
    "        self.dept=dept\n",
    "        print(\"Employee:\",self.name,\"is created\")\n",
    "    \n",
    "    def salary(self):\n",
    "        if self.exp>5 and self.dept==\"r&d\":\n",
    "            self.salary=8\n",
    "        else:\n",
    "            self.salary=2\n",
    "        print(\"Salary for Employee:\",self.name,\"is calculated\")\n",
    "        \n",
    "    def disc(self):\n",
    "        print(\"Name:\",self.name,\n",
    "              \"Id:\",self.emp_id,\n",
    "              \"Exp:\",self.exp,\n",
    "              \"Dept:\",self.dept,\n",
    "              \"Salary:\", self.salary)\n",
    "emp1=Employee()\n",
    "emp1.salary()\n",
    "emp1.disc()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2. Inheritance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Employee: Ram is created\n",
      "Salary for Employee: Ram is calculated\n",
      "Name: Ram Id: god Exp: 6 Dept: r&d Salary: 30\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'great'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class Sub_emp(Employee):\n",
    "    def __init__(self, name=\"Ram\",emp_id=\"god\",exp=6,dept=\"r&d\", sub_id=\"great\"):\n",
    "        super(Sub_emp,self).__init__(name, emp_id, exp, dept)# All params passed to init of super for declaring\n",
    "        self.sub_id=sub_id\n",
    "    def salary(self):\n",
    "        self.salary=self.exp*5\n",
    "        print(\"Salary for Employee:\",self.name,\"is calculated\")\n",
    "\n",
    "emp2=Sub_emp()\n",
    "emp2.salary()\n",
    "emp2.disc()\n",
    "emp2.sub_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 Threads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import threading\n",
    "import numpy as np\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "42Hello\n",
      "\n",
      "Hello\n",
      "81\n",
      "Hello\n",
      "155\n",
      "Hello\n",
      "167\n",
      "Hello\n",
      "534\n"
     ]
    }
   ],
   "source": [
    "def rand_num_gen():\n",
    "    for i in range(5):\n",
    "        print(np.random.randint(1, 1000))\n",
    "        time.sleep(1)\n",
    "        \n",
    "thr1=threading.Thread(target=rand_num_gen)\n",
    "thr1.start()\n",
    "\n",
    "def greetings():\n",
    "    for i in range(5):\n",
    "        print(\"Hello\")\n",
    "        time.sleep(1)\n",
    "\n",
    "greetings()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.4 Daemon\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import threading\n",
    "import numpy as np\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0Hello\n",
      "\n",
      "ReqId: 0 Historical Data Ended from 20210330  04:31:44 to 20210419  04:31:44\n",
      "1Hello\n",
      "\n",
      "Hello2\n",
      "\n",
      "Hello\n",
      "Hello\n"
     ]
    }
   ],
   "source": [
    "def rand_num_gen():\n",
    "    for i in range(3):\n",
    "        print(i)\n",
    "        time.sleep(1)\n",
    "        \n",
    "thr1=threading.Thread(target=rand_num_gen)\n",
    "thr1.daemon=True\n",
    "thr1.start()\n",
    "\n",
    "\n",
    "def greetings():\n",
    "    for i in range(5):\n",
    "        print(\"Hello\")\n",
    "        time.sleep(1)\n",
    "\n",
    "greetings()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.5 Event Based Threading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import threading\n",
    "import numpy as np\n",
    "import time "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "Hello\n",
      "1\n",
      "Hello\n",
      "2\n"
     ]
    }
   ],
   "source": [
    "def rand_num_gen():\n",
    "    for i in range(5):\n",
    "        if event.is_set():\n",
    "            break\n",
    "        else:\n",
    "            print(i)\n",
    "            time.sleep(1)\n",
    "\n",
    "event=threading.Event()\n",
    "\n",
    "thr1=threading.Thread(target=rand_num_gen)\n",
    "thr1.start()\n",
    "\n",
    "def greetings():\n",
    "    for i in range(2):\n",
    "        print(\"Hello\")\n",
    "        time.sleep(1)\n",
    "\n",
    "greetings()\n",
    "event.set() ## AFter greeting event is set\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. TWS API\n",
    "## 2.1 Eclient & Ewrapper -- Conectivity Section of API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "    \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1) \n",
    "app.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### =================> Now exit by restarting kernel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Contract Info -- Financial Instruments (Contracts) Section of API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "######################## Contract Class of IBKR ##########################\n",
    "class Contract(Object):\n",
    "    def __init__(self):\n",
    "        self.conId = 0\n",
    "        self.symbol = \"\"\n",
    "        self.secType = \"\"\n",
    "        self.lastTradeDateOrContractMonth = \"\"\n",
    "        self.strike = 0.  # float !!\n",
    "        self.right = \"\"\n",
    "        self.multiplier = \"\"\n",
    "        self.exchange = \"\"\n",
    "        self.primaryExchange = \"\" # pick an actual (ie non-aggregate) exchange that the contract trades on.  DO NOT SET TO SMART.\n",
    "        self.currency = \"\"\n",
    "        self.localSymbol = \"\"\n",
    "        self.tradingClass = \"\"\n",
    "        self.includeExpired = False\n",
    "        self.secIdType = \"\"\t  # CUSIP;SEDOL;ISIN;RIC\n",
    "        self.secId = \"\"\n",
    "\n",
    "        #combos\n",
    "        self.comboLegsDescrip = \"\"  # type: str; received in open order 14 and up for all combos\n",
    "        self.comboLegs = None     # type: list<ComboLeg>\n",
    "        self.deltaNeutralContract = None\n",
    "        \n",
    "        \n",
    "######################## reqcontract comes from Eclient ##########################\n",
    "self.reqContractDetails(10004, ContractSamples.NewsFeedForQuery())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "unhandled exception in EReader thread\n",
      "Traceback (most recent call last):\n",
      "  File \"/home/boo/Software/miniconda3/envs/quant/lib/python3.8/site-packages/ibapi-9.76.1-py3.8.egg/ibapi/reader.py\", line 34, in run\n",
      "    data = self.conn.recvMsg()\n",
      "  File \"/home/boo/Software/miniconda3/envs/quant/lib/python3.8/site-packages/ibapi-9.76.1-py3.8.egg/ibapi/connection.py\", line 99, in recvMsg\n",
      "    buf = self._recvAllMsg()\n",
      "  File \"/home/boo/Software/miniconda3/envs/quant/lib/python3.8/site-packages/ibapi-9.76.1-py3.8.egg/ibapi/connection.py\", line 119, in _recvAllMsg\n",
      "    buf = self.socket.recv(4096)\n",
      "ConnectionResetError: [Errno 104] Connection reset by peer\n"
     ]
    }
   ],
   "source": [
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "    \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "\n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "contract=contract_maker(\"TATAMOTOR\")\n",
    "app.reqContractDetails( 108,contract) ## 108 is arbirary refer to above cell for funntion\n",
    "\n",
    "app.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### =================> Now exit by restarting kernel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Solving Persistant Connection using multithreading and restarting kernel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "    \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "\n",
    "\n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "def web_socket():\n",
    "    app.run()\n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.daemon=True\n",
    "thr1.start()\n",
    "\n",
    "contract=contract_maker(\"TATAMOTOR\")\n",
    "time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "app.reqContractDetails( 108,contract) ## 108 is arbirary refer to above cell for funntion\n",
    "        \n",
    "    \n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "\n",
    "time.sleep(5)## Waiting for app.reqContract to finish before restarting kernel.\n",
    "kernel_restarter()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4 Solving Persistant Connection using Event and restarting kernel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "    \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "\n",
    "\n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "def web_socket():\n",
    "    app.run()\n",
    "    if event.is_set():\n",
    "        app.close()\n",
    "    \n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "event=threading.Event()\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.start()\n",
    "\n",
    "contract=contract_maker(\"TATAMOTOR\")\n",
    "time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "app.reqContractDetails( 108,contract) ## 108 is arbirary refer to above cell for funntion\n",
    "event.set()\n",
    "time.sleep(5)## Waiting for app.reqContract to finish before restarting kernel.\n",
    "    \n",
    "    \n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "\n",
    "kernel_restarter()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Retriving Data\n",
    "## 3.1. Getting Historical Data -- Historical Market Data Section of API"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "void reqHistoricalData\t(\tint \ttickerId,\n",
    "Contract \tcontract,\n",
    "string \tendDateTime,\n",
    "string \tdurationStr,\n",
    "string \tbarSizeSetting,\n",
    "string \twhatToShow,\n",
    "int \tuseRTH,\n",
    "int \tformatDate,\n",
    "bool \tkeepUpToDate,\n",
    "List< TagValue > \tchartOptions \n",
    ")\t\t\n",
    "\n",
    "\n",
    "Parameters\n",
    "\n",
    "tickerId\tthe request's unique identifier.\n",
    "contract\tthe contract for which we want to retrieve the data.\n",
    "endDateTime\trequest's ending time with format yyyyMMdd HH:mm:ss {TMZ}\n",
    "durationStr\tthe amount of time for which the data needs to be retrieved:\n",
    "    \" S (seconds) - \" D (days)\n",
    "    \" W (weeks) - \" M (months)\n",
    "    \" Y (years)\n",
    "barSizeSetting\tthe size of the bar:\n",
    "    1 sec\n",
    "    5 secs\n",
    "    15 secs\n",
    "    30 secs\n",
    "    1 min\n",
    "    2 mins\n",
    "    3 mins\n",
    "    5 mins\n",
    "    15 mins\n",
    "    30 mins\n",
    "    1 hour\n",
    "    1 day\n",
    "whatToShow\tthe kind of information being retrieved:\n",
    "    TRADES\n",
    "    MIDPOINT\n",
    "    BID\n",
    "    ASK\n",
    "    BID_ASK\n",
    "    HISTORICAL_VOLATILITY\n",
    "    OPTION_IMPLIED_VOLATILITY\n",
    "    FEE_RATE\n",
    "    REBATE_RATE\n",
    "useRTH\tset to 0 to obtain the data which was also generated outside of the Regular Trading Hours, set to 1 to obtain only the RTH data\n",
    "formatDate\tset to 1 to obtain the bars' time as yyyyMMdd HH:mm:ss, set to 2 to obtain it like system time format in seconds\n",
    "keepUpToDate\tset to True to received continuous updates on most recent bar data. If True, and endDateTime cannot be specified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "    \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "        \n",
    "    def headTimestamp(self, reqId, headTimestamp):\n",
    "        print(\"ReqId:\", reqId,\"Earliest data available:\",headTimestamp)\n",
    "\n",
    "    def historicalData(self, reqId:int, bar):\n",
    "        print(bar)\n",
    "        self.bar=bar\n",
    "        \n",
    "    def historicalDataEnd(self, reqId: int, start: str, end: str):\n",
    "        super().historicalDataEnd(reqId, start, end)\n",
    "        print(\"ReqId:\", reqId,\"Historical Data Ended\",\"from\", start, \"to\", end)\n",
    "        \n",
    "    def historicalDataUpdate(self, reqId: int, bar):\n",
    "        print(\"HistoricalDataUpdate. ReqId:\", reqId, \"BarData.\", bar)\n",
    "\n",
    "    \n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "import datetime\n",
    "today_date=(datetime.datetime.today().strftime(\"%Y%m%d %H:%M:%S\"))\n",
    "def history_retriver(req_id,contract,durationStr=\"2 D\",barSizeSetting=\"1 day\", endDateTime=today_date):\n",
    "    app.reqHistoricalData(  reqId=req_id,\n",
    "                            contract=contract,\n",
    "                            endDateTime=endDateTime,\n",
    "                            durationStr=durationStr,\n",
    "                            barSizeSetting=barSizeSetting,\n",
    "                            whatToShow=\"TRADES\",\n",
    "                            useRTH=1,\n",
    "                            formatDate=1,\n",
    "                            keepUpToDate=False,\n",
    "                            chartOptions=[]\n",
    "                         )\n",
    "\n",
    "def web_socket():\n",
    "    app.run()\n",
    "    if event.is_set():\n",
    "        app.close()\n",
    "    \n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "event=threading.Event()\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.start()\n",
    "\n",
    "contract=contract_maker(\"TATAMOTOR\")\n",
    "time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "\n",
    "# app.reqContractDetails( 108,contract) ## 108 is arbirary refer to above cell for funntion\n",
    "app.reqHeadTimeStamp(118, contract, \"TRADES\", 0, 1) # How old can we go for data \n",
    "\n",
    "\n",
    "########## Historical Data ###########\n",
    "history_retriver(128, contract,\"2 D\", \"1 day\")\n",
    "\n",
    "time.sleep(5)## Waiting for app.reqContract to finish before restarting kernel.                                                                                \n",
    "event.set()\n",
    "\n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "\n",
    "kernel_restarter()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 Getting Historical Data for multiple Stock"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "    \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "        \n",
    "    def headTimestamp(self, reqId, headTimestamp):\n",
    "        print(\"ReqId:\", reqId,\"Earliest data available:\",headTimestamp)\n",
    "\n",
    "    def historicalData(self, reqId:int, bar):\n",
    "        print(bar)\n",
    "        self.bar=bar\n",
    "        \n",
    "    def historicalDataEnd(self, reqId: int, start: str, end: str):\n",
    "        super().historicalDataEnd(reqId, start, end)\n",
    "        print(\"ReqId:\", reqId,\"Historical Data Ended\",\"from\", start, \"to\", end)\n",
    "        \n",
    "    def historicalDataUpdate(self, reqId: int, bar):\n",
    "        print(\"HistoricalDataUpdate. ReqId:\", reqId, \"BarData.\", bar)\n",
    "\n",
    "    \n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "import datetime\n",
    "today_date=(datetime.datetime.today().strftime(\"%Y%m%d %H:%M:%S\"))\n",
    "def history_retriver(req_id,contract,durationStr=\"2 D\",barSizeSetting=\"1 D\", endDateTime=today_date):\n",
    "    app.reqHistoricalData(  reqId=req_id,\n",
    "                            contract=contract,\n",
    "                            endDateTime=endDateTime,\n",
    "                            durationStr=durationStr,\n",
    "                            barSizeSetting=barSizeSetting,\n",
    "                            whatToShow=\"TRADES\",\n",
    "                            useRTH=1,\n",
    "                            formatDate=1,\n",
    "                            keepUpToDate=False,\n",
    "                            chartOptions=[]\n",
    "                         )\n",
    "\n",
    "def web_socket():\n",
    "    app.run()\n",
    "    if event.is_set():\n",
    "        app.close()\n",
    "    \n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "event=threading.Event()\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.start()\n",
    "\n",
    "\n",
    "\n",
    "########## Historical Data ############\n",
    "tickers=['TATASTEEL', 'TATAPOWER', 'TATAMOTOR']\n",
    "for i in range(len(tickers)):\n",
    "    contract=contract_maker(tickers[i])\n",
    "    time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "    history_retriver(i, contract,\"2 D\", \"1 day\")\n",
    "    time.sleep(1)## Waiting for app.reqContract to finish before restarting kernel.                                                                                \n",
    "\n",
    "event.set()\n",
    "\n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "\n",
    "kernel_restarter()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 Storing Data in Pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "        self.data={}\n",
    "        \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "        \n",
    "    def headTimestamp(self, reqId, headTimestamp):\n",
    "        print(\"ReqId:\", reqId,\"Earliest data available:\",headTimestamp)\n",
    "\n",
    "    def historicalData(self, reqId, bar):\n",
    "        if reqId not in self.data:\n",
    "            self.data[reqId]=[{\"date\":bar.date,\n",
    "                               \"open\":bar.open,\n",
    "                               \"high\":bar.high,\n",
    "                               \"low\":bar.low,\n",
    "                               \"close\":bar.close,\n",
    "                               \"volume\":bar.volume}]\n",
    "        else:\n",
    "            self.data[reqId].append({\"date\":bar.date,\n",
    "                                       \"open\":bar.open,\n",
    "                                       \"high\":bar.high,\n",
    "                                       \"low\":bar.low,\n",
    "                                       \"close\":bar.close,\n",
    "                                       \"volume\":bar.volume})\n",
    "        \n",
    "    def historicalDataEnd(self, reqId: int, start: str, end: str):\n",
    "        super().historicalDataEnd(reqId, start, end)\n",
    "        print(\"ReqId:\", reqId,\"Historical Data Ended\",\"from\", start, \"to\", end)\n",
    "        \n",
    "    def historicalDataUpdate(self, reqId: int, bar):\n",
    "        print(\"HistoricalDataUpdate. ReqId:\", reqId, \"BarData.\", bar)\n",
    "\n",
    "    \n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "def history_retriver(req_id,contract,from_datetime,duration=\"2 D\",bar_size=\"1 day\", ):\n",
    "    app.reqHistoricalData(  reqId=req_id,\n",
    "                            contract=contract,\n",
    "                            endDateTime=from_datetime,\n",
    "                            durationStr=duration,\n",
    "                            barSizeSetting=bar_size,\n",
    "                            whatToShow=\"TRADES\",\n",
    "                            useRTH=1,\n",
    "                            formatDate=1,\n",
    "                            keepUpToDate=False,\n",
    "                            chartOptions=[]\n",
    "                         )\n",
    "\n",
    "def multi_historical_retriver(ticker_list,app_obj, from_datetime, duration,bar_size):\n",
    "    df_dict={}\n",
    "    for ticker in ticker_list:\n",
    "        ix=ticker_list.index(ticker)\n",
    "\n",
    "        contract=contract_maker(ticker)\n",
    "        time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "        history_retriver(ix, contract,from_datetime, duration, bar_size, )\n",
    "        time.sleep(1)## Waiting for app.reqContract to finish before restarting kernel.\n",
    "        df_dict[ticker]=pd.DataFrame(app_obj.data[ix])\n",
    "        time.sleep(1)\n",
    "    \n",
    "    return df_dict\n",
    "\n",
    "\n",
    "\n",
    "##########  Starting App as Thread #############\n",
    "def web_socket():\n",
    "    app.run()\n",
    "    if event.is_set():\n",
    "        app.close()\n",
    "    \n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "event=threading.Event()\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.start()\n",
    "##################################################\n",
    "\n",
    "\n",
    "##########  From Datetime #############\n",
    "import datetime\n",
    "today_date=(datetime.datetime.today().strftime(\"%Y%m%d %H:%M:%S\"))\n",
    "########################################\n",
    "\n",
    "\n",
    "########## Historical Data ############\n",
    "tickers=['TATASTEEL', 'TATAPOWER', 'TATAMOTOR']\n",
    "\n",
    "df=multi_historical_retriver(ticker_list=tickers,\n",
    "                             app_obj=app, \n",
    "                             from_datetime=today_date,\n",
    "                             duration=\"2 D\",\n",
    "                             bar_size=\"1 day\",\n",
    "                             )\n",
    "print(df)\n",
    "event.set()\n",
    "########################################\n",
    "\n",
    "\n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "kernel_restarter()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.4 Storing Data in CSV file\n",
    "-- /data/Ticker_Name/retrivalDate_bar_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "        self.data={}\n",
    "        \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "        \n",
    "    def headTimestamp(self, reqId, headTimestamp):\n",
    "        print(\"ReqId:\", reqId,\"Earliest data available:\",headTimestamp)\n",
    "\n",
    "    def historicalData(self, reqId, bar):\n",
    "        if reqId not in self.data:\n",
    "            self.data[reqId]=[{\"date\":bar.date,\n",
    "                               \"open\":bar.open,\n",
    "                               \"high\":bar.high,\n",
    "                               \"low\":bar.low,\n",
    "                               \"close\":bar.close,\n",
    "                               \"volume\":bar.volume}]\n",
    "        else:\n",
    "            self.data[reqId].append({\"date\":bar.date,\n",
    "                                       \"open\":bar.open,\n",
    "                                       \"high\":bar.high,\n",
    "                                       \"low\":bar.low,\n",
    "                                       \"close\":bar.close,\n",
    "                                       \"volume\":bar.volume})\n",
    "        \n",
    "    def historicalDataEnd(self, reqId: int, start: str, end: str):\n",
    "        super().historicalDataEnd(reqId, start, end)\n",
    "        print(\"ReqId:\", reqId,\"Historical Data Ended\",\"from\", start, \"to\", end)\n",
    "        \n",
    "    def historicalDataUpdate(self, reqId: int, bar):\n",
    "        print(\"HistoricalDataUpdate. ReqId:\", reqId, \"BarData.\", bar)\n",
    "\n",
    "    \n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "def history_retriver(req_id,contract,from_date,duration=\"2 D\",bar_size=\"1 day\", ):\n",
    "    app.reqHistoricalData(  reqId=req_id,\n",
    "                            contract=contract,\n",
    "                            endDateTime=from_date,\n",
    "                            durationStr=duration,\n",
    "                            barSizeSetting=bar_size,\n",
    "                            whatToShow=\"TRADES\",\n",
    "                            useRTH=1,\n",
    "                            formatDate=1,\n",
    "                            keepUpToDate=False,\n",
    "                            chartOptions=[]\n",
    "                         )\n",
    "def data_saver(df_dict, from_date, duration, bar_size):\n",
    "    for ticker in df_dict:\n",
    "        \n",
    "        ###### Directory ##########\n",
    "        dir_name= \"./Data/\"\n",
    "        import os\n",
    "        os.makedirs(dir_name, exist_ok=True)\n",
    "        ############################\n",
    "        \n",
    "        ##### File Name ###########\n",
    "        date_str=from_date.split(\" \")[0]\n",
    "        file_name=ticker+\"_\"+date_str+\"_\"+duration+\"_\"+bar_size+\".csv\"\n",
    "        file_name=file_name.replace(\" \", \"\")\n",
    "        file_name=dir_name+file_name\n",
    "        ############################\n",
    "        \n",
    "        df_dict[ticker].to_csv(file_name, index=False)\n",
    "        print(\"Data saved to:\",file_name)\n",
    "\n",
    "        \n",
    "def multi_historical_retriver(ticker_list,app_obj, from_date, duration,bar_size):\n",
    "    df_dict={}\n",
    "    for ticker in ticker_list:\n",
    "        ix=ticker_list.index(ticker)\n",
    "\n",
    "        contract=contract_maker(ticker)\n",
    "        time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "        history_retriver(ix, contract,from_date, duration, bar_size, )\n",
    "        time.sleep(1)## Waiting for app.reqContract to finish before restarting kernel.\n",
    "        df_dict[ticker]=pd.DataFrame(app_obj.data[ix])\n",
    "        time.sleep(1)\n",
    "    \n",
    "    data_saver(df_dict, from_date, duration, bar_size)\n",
    "    \n",
    "\n",
    "##########  Starting App as Thread #############\n",
    "def web_socket():\n",
    "    app.run()\n",
    "    event.wait()\n",
    "    if event.is_set():\n",
    "        app.close()\n",
    "    \n",
    "\n",
    "app=TradingApp()\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "event=threading.Event()\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.start()\n",
    "##################################################\n",
    "\n",
    "\n",
    "##########  From Datetime #############\n",
    "import datetime\n",
    "today_date=(datetime.datetime.today().strftime(\"%Y%m%d %H:%M:%S\"))\n",
    "########################################\n",
    "\n",
    "\n",
    "########## Historical Data ############\n",
    "tickers=['TATASTEEL']\n",
    "multi_historical_retriver(ticker_list=tickers,\n",
    "                         app_obj=app, \n",
    "                         from_date=today_date,\n",
    "                         duration=\"20 D\",\n",
    "                         bar_size=\"1 min\",\n",
    "                         )\n",
    "\n",
    "event.set()\n",
    "########################################\n",
    "\n",
    "\n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "kernel_restarter()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.5 Ingesting CSV File to SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ReqId: -1 Error: 2104 Message: Market data farm connection is OK:usfarm.nj\n",
      "ReqId: -1 Error: 2104 Message: Market data farm connection is OK:hfarm\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:euhmds\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:hkhmds\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:fundfarm\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:ushmds\n",
      "ReqId: -1 Error: 2158 Message: Sec-def data farm connection is OK:secdefhk\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<script>Jupyter.notebook.kernel.restart()</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self, ticker_list):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "        self.data={}\n",
    "        self.ticker_list=ticker_list\n",
    "        \n",
    "        self.current_ticker=\"\" \n",
    "        self.ix=-1\n",
    "        self.df=pd.DataFrame()\n",
    "        \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "        \n",
    "    def headTimestamp(self, reqId, headTimestamp):\n",
    "        print(\"ReqId:\", reqId,\"Earliest data available:\",headTimestamp)\n",
    "\n",
    "    def historicalData(self, reqId, bar):\n",
    "        print(threading.active_count() )\n",
    "        if reqId not in self.data:\n",
    "            self.data[reqId]=[{\"date\":bar.date,\n",
    "                               \"open\":bar.open,\n",
    "                               \"high\":bar.high,\n",
    "                               \"low\":bar.low,\n",
    "                               \"close\":bar.close,\n",
    "                               \"volume\":bar.volume}]\n",
    "        else:\n",
    "            self.data[reqId].append({\"date\":bar.date,\n",
    "                                       \"open\":bar.open,\n",
    "                                       \"high\":bar.high,\n",
    "                                       \"low\":bar.low,\n",
    "                                       \"close\":bar.close,\n",
    "                                       \"volume\":bar.volume})\n",
    "            \n",
    "    def historicalDataEnd(self, reqId: int, start: str, end: str):\n",
    "        super().historicalDataEnd(reqId, start, end)\n",
    "        print(\"ReqId:\", reqId,\"Historical Data Ended\",\"from\", start, \"to\", end)\n",
    "\n",
    "        self.df=pd.DataFrame(self.data[reqId])\n",
    "        self.df.to_csv(self.file_name, index=False)\n",
    "        \n",
    "    def historicalDataUpdate(self, reqId: int, bar):\n",
    "        print(\"HistoricalDataUpdate. ReqId:\", reqId, \"BarData.\", bar)\n",
    "\n",
    "    \n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "def history_retriver(req_id,contract,from_date,duration=\"2 D\",bar_size=\"1 day\"):\n",
    "    app.reqHistoricalData(  reqId=req_id,\n",
    "                            contract=contract,\n",
    "                            endDateTime=from_date,\n",
    "                            durationStr=duration,\n",
    "                            barSizeSetting=bar_size,\n",
    "                            whatToShow=\"TRADES\",\n",
    "                            useRTH=1,\n",
    "                            formatDate=1,\n",
    "                            keepUpToDate=False,\n",
    "                            chartOptions=[]\n",
    "                         )\n",
    "    time.sleep(1)\n",
    "        \n",
    "        \n",
    "\n",
    "        \n",
    "def multi_historical_retriver(ticker_list,app_obj, from_date, duration,bar_size):\n",
    "    df_dict={}\n",
    "    ###### Directory ##########\n",
    "    dir_name= \"./Data/\"\n",
    "    import os\n",
    "    os.makedirs(dir_name, exist_ok=True)\n",
    "    ############################\n",
    "\n",
    "    for ticker in ticker_list:\n",
    "        \n",
    "        \n",
    "        ##### File Name ###########\n",
    "        date_str=from_date.split(\" \")[0]\n",
    "        file_name=ticker+\"_\"+date_str+\"_\"+duration+\"_\"+bar_size+\".csv\"\n",
    "        file_name=file_name.replace(\" \", \"\")\n",
    "        file_name=dir_name+file_name\n",
    "        ############################\n",
    "        \n",
    "        \n",
    "        app_obj.current_ticker=ticker\n",
    "        app_obj.file_name=file_name\n",
    "        app_obj.ix=ticker_list.index(ticker)\n",
    "        \n",
    "        ix=ticker_list.index(ticker)\n",
    "\n",
    "        contract=contract_maker(ticker)\n",
    "        time.sleep(2)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "        history_retriver(ix, contract,from_date, duration, bar_size)\n",
    "        \n",
    "def web_socket():\n",
    "    app.run()\n",
    "\n",
    "tickers=['TATAMOTOR']\n",
    "\n",
    "app=TradingApp(tickers)\n",
    "app.connect(\"127.0.0.1\",7497 , clientId=1)\n",
    "\n",
    "thr1=threading.Thread(target=web_socket)\n",
    "thr1.daemon=True\n",
    "thr1.start()\n",
    "\n",
    "time.sleep(1)\n",
    "\n",
    "##########  From Datetime #############\n",
    "import datetime\n",
    "today_date=(datetime.datetime.today().strftime(\"%Y%m%d %H:%M:%S\"))\n",
    "########################################\n",
    "\n",
    "\n",
    "########## Historical Data ############\n",
    "tickers=['TATAMOTOR','TATAPOWER']\n",
    "multi_historical_retriver(ticker_list=tickers,\n",
    "                         app_obj=app, \n",
    "                         from_date=today_date,\n",
    "                         duration=\"20 D\",\n",
    "                         bar_size=\"1 min\",\n",
    "                         )\n",
    "\n",
    "########################################\n",
    "\n",
    "\n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "kernel_restarter()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5.1 Ingesting CSV File to SQL  ----via simple lib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tatamotor_1day :\n",
      "Table exist, updating record.\n",
      "Done\n",
      "tatasteel_1min :\n",
      "Table exist, updating record.\n",
      "Done\n",
      "tatasteel_1min :\n",
      "Table exist, updating record.\n",
      "Done\n",
      "tatapower_1day :\n",
      "Table exist, updating record.\n",
      "Done\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def csv_to_pandas(csv_file_name, bar_size):\n",
    "    df=pd.read_csv(csv_file_name)\n",
    "    \n",
    "    long_bars=[\"1day\",\"1week\", \"1month\"]\n",
    "    if bar_size in long_bars:\n",
    "        df[\"date\"]=pd.to_datetime(df[\"date\"],format='%Y%m%d')\n",
    "    else:\n",
    "        df[\"date\"]=pd.to_datetime(df[\"date\"],format='%Y%m%d  %H:%M:%S')\n",
    "    \n",
    "    df.set_index(\"date\", inplace=True)\n",
    "    return df\n",
    "\n",
    "def csv_name_to_data(file_name):\n",
    "    ####### To Extarct Info from CSV file\n",
    "    csv_file_name=file_name.split(\"/\")[2]\n",
    "    ticker=csv_file_name.split(\"_\")[0]\n",
    "    bar_size=csv_file_name.split(\"_\")[3].split(\".\")[0]\n",
    "    tabel_name=ticker+\"_\"+bar_size\n",
    "    tabel_name=tabel_name.lower()\n",
    "    return  tabel_name, bar_size\n",
    "\n",
    "\n",
    "def sql_ingester(data_dir, overwrite=False):\n",
    "    \n",
    "    import sqlalchemy\n",
    "    from sqlalchemy import Table, Column, Integer, String, MetaData, DateTime, Float\n",
    "    engine = sqlalchemy.create_engine('postgresql://krh:krh@123@localhost:5432/krh')\n",
    "    \n",
    "    \n",
    "    ######### Csv Part #####\n",
    "    import glob\n",
    "    csv_list=glob.glob(data_dir+\"/*\")\n",
    "    ########################    \n",
    "    \n",
    "    ##### Sql Part #######\n",
    "    for file_name in csv_list:\n",
    "\n",
    "        #### Extracting Info #########\n",
    "        table_name, bar_size=csv_name_to_data(file_name)\n",
    "        df=csv_to_pandas(file_name, bar_size)\n",
    "        ##################################\n",
    "\n",
    "        \n",
    "        meta = MetaData()\n",
    "        tick_table = Table(\n",
    "        table_name, meta, \n",
    "        Column('date', DateTime, primary_key = True), \n",
    "        Column('open', Float),\n",
    "        Column('high', Float),\n",
    "        Column('low', Float),\n",
    "        Column('close', Float),\n",
    "        Column('volume', Float),\n",
    "      )\n",
    "        if table_name in engine.table_names():\n",
    "            print(table_name,\":\")\n",
    "            if overwrite:\n",
    "                print(\"Table exist, deleting this and creating new table.\")\n",
    "                tick_table.drop(engine)\n",
    "                tick_table.create(engine)\n",
    "                df.to_sql(table_name, engine, if_exists='append', chunksize=5000, method='multi')\n",
    "                print(\"Done\")\n",
    "            else:\n",
    "                print(\"Table exist, updating record.\")\n",
    "                old_df=pd.read_sql_table(table_name,engine, index_col=\"date\")\n",
    "                df=df[~df.index.isin(old_df.index)]\n",
    "                df.to_sql(table_name, engine, if_exists='append', chunksize=5000, method='multi')\n",
    "                print(\"Done\")\n",
    "        else:\n",
    "            print(\"Table does not exist, creating one.\")\n",
    "            tick_table.create(engine)\n",
    "            df.to_sql(table_name, engine, if_exists='append', chunksize=5000, method='multi')\n",
    "            print(\"Done\")\n",
    "            \n",
    "     \n",
    "    return df\n",
    "\n",
    "data_dir=\"./Data\"\n",
    "df=sql_ingester(data_dir, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.6  Final Code For CSV Retrival and SQL Upload"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ReqId: -1 Error: 2104 Message: Market data farm connection is OK:hfarm\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:euhmds\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:hkhmds\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:fundfarm\n",
      "ReqId: -1 Error: 2106 Message: HMDS data farm connection is OK:ushmds\n",
      "ReqId: -1 Error: 2158 Message: Sec-def data farm connection is OK:secdefhk\n",
      "ReqId: 0 Historical Data Ended from 20210418  01:22:19 to 20210420  01:22:19\n",
      "Data saved to: ./Data/TATASTEEL_20210420_2D_1day.csv\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<script>Jupyter.notebook.kernel.restart()</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from ibapi.client import EClient     ## For Connection\n",
    "from ibapi.wrapper import EWrapper   ## For Translating all low level operations in high level python\n",
    "from ibapi.contract import Contract  ## For contract as IBKR Trades around various exchanges\n",
    "\n",
    "import threading\n",
    "import time\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "class TradingApp(EClient, EWrapper):\n",
    "    def __init__(self):\n",
    "        EClient.__init__(self, self) #First self belongs to slef, other belongs to wrapper class\n",
    "        self.data={}\n",
    "        self.event=\"\"\n",
    "        \n",
    "    def error(self, reqId, errorCode, errorString): # Copied from wrapper.py\n",
    "        print(\"ReqId:\",reqId, \"Error:\",errorCode,\"Message:\", errorString )\n",
    "        \n",
    "    def contractDetails(self, reqId, contractDetails):\n",
    "        print(\"ReqId:\", reqId,\n",
    "             \"Contract Details:\", contractDetails)\n",
    "        \n",
    "    def headTimestamp(self, reqId, headTimestamp):\n",
    "        print(\"ReqId:\", reqId,\"Earliest data available:\",headTimestamp)\n",
    "\n",
    "    def historicalData(self, reqId, bar):\n",
    "        if reqId not in self.data:\n",
    "            self.data[reqId]=[{\"date\":bar.date,\n",
    "                               \"open\":bar.open,\n",
    "                               \"high\":bar.high,\n",
    "                               \"low\":bar.low,\n",
    "                               \"close\":bar.close,\n",
    "                               \"volume\":bar.volume}]\n",
    "        else:\n",
    "            self.data[reqId].append({\"date\":bar.date,\n",
    "                                       \"open\":bar.open,\n",
    "                                       \"high\":bar.high,\n",
    "                                       \"low\":bar.low,\n",
    "                                       \"close\":bar.close,\n",
    "                                       \"volume\":bar.volume})\n",
    "        \n",
    "    def historicalDataEnd(self, reqId: int, start: str, end: str):\n",
    "        super().historicalDataEnd(reqId, start, end)\n",
    "        print(\"ReqId:\", reqId,\"Historical Data Ended\",\"from\", start, \"to\", end)\n",
    "        self.event.set()\n",
    "        \n",
    "    def historicalDataUpdate(self, reqId: int, bar):\n",
    "        print(\"HistoricalDataUpdate. ReqId:\", reqId, \"BarData.\", bar)\n",
    "\n",
    "    \n",
    "def contract_maker(symbol,sec_type=\"STK\", exchange=\"NSE\", currency=\"INR\"):\n",
    "    contract=Contract()\n",
    "    contract.symbol = symbol\n",
    "    contract.secType = sec_type\n",
    "    contract.exchange =exchange\n",
    "    contract.currency = currency\n",
    "    return contract\n",
    "\n",
    "def history_retriver(req_id,contract,app_obj,from_date,duration=\"2 D\",bar_size=\"1 day\", ):\n",
    "    app_obj.reqHistoricalData(  reqId=req_id,\n",
    "                            contract=contract,\n",
    "                            endDateTime=from_date,\n",
    "                            durationStr=duration,\n",
    "                            barSizeSetting=bar_size,\n",
    "                            whatToShow=\"TRADES\",\n",
    "                            useRTH=1,\n",
    "                            formatDate=1,\n",
    "                            keepUpToDate=False,\n",
    "                            chartOptions=[]\n",
    "                         )\n",
    "def data_saver(df_dict, from_date, duration, bar_size):\n",
    "    for ticker in df_dict:\n",
    "        \n",
    "        ###### Directory ##########\n",
    "        dir_name= \"./Data/\"\n",
    "        import os\n",
    "        os.makedirs(dir_name, exist_ok=True)\n",
    "        ############################\n",
    "        \n",
    "        ##### File Name ###########\n",
    "        date_str=from_date.split(\" \")[0]\n",
    "        file_name=ticker+\"_\"+date_str+\"_\"+duration+\"_\"+bar_size+\".csv\"\n",
    "        file_name=file_name.replace(\" \", \"\")\n",
    "        file_name=dir_name+file_name\n",
    "        ############################\n",
    "        \n",
    "        df_dict[ticker].to_csv(file_name, index=False)\n",
    "        print(\"Data saved to:\",file_name)\n",
    "\n",
    "        \n",
    "def multi_historical_retriver(ticker_list,app_obj, from_date, duration,bar_size, event):\n",
    "    df_dict={}\n",
    "    for ticker in ticker_list:\n",
    "        event.clear()\n",
    " \n",
    "        ix=ticker_list.index(ticker)\n",
    "\n",
    "        contract=contract_maker(ticker)\n",
    "        time.sleep(1)  ## Wait for Client to make conecction and then pass this to to thread for reply\n",
    "        history_retriver(ix, contract,app_obj,from_date, duration, bar_size)\n",
    "        time.sleep(1)## Waiting for app.reqContract to finish before restarting kernel.\n",
    " \n",
    "        event.wait()\n",
    " \n",
    "        df_dict[ticker]=pd.DataFrame(app_obj.data[ix])\n",
    "        time.sleep(1)\n",
    "        \n",
    "    data_saver(df_dict, from_date, duration, bar_size)\n",
    "    \n",
    "\n",
    "##########  Starting App as Thread #############\n",
    "def websocket_con():\n",
    "    app.run()\n",
    "    \n",
    "\n",
    "event = threading.Event() \n",
    "\n",
    "app = TradingApp()\n",
    "app.event=event\n",
    "app.connect(host='127.0.0.1', port=7497, clientId=23) #port 4002 for ib gateway paper trading/7497 for TWS paper trading\n",
    "con_thread = threading.Thread(target=websocket_con, daemon=True)\n",
    "con_thread.start()\n",
    "time.sleep(1) # some latency added to ensure that the connection is established\n",
    " \n",
    "##################################################\n",
    "\n",
    "\n",
    "##########  From Datetime #############\n",
    "import datetime\n",
    "today_date=(datetime.datetime.today().strftime(\"%Y%m%d %H:%M:%S\"))\n",
    "########################################\n",
    "\n",
    "\n",
    "########## Historical Data ############\n",
    "tickers=['TATASTEEL']\n",
    "multi_historical_retriver(ticker_list=tickers,\n",
    "                         app_obj=app, \n",
    "                         from_date=today_date,\n",
    "                         duration=\"2 D\",\n",
    "                         bar_size=\"1 day\",\n",
    "                         event=event\n",
    "                         )\n",
    "\n",
    "\n",
    "########################################\n",
    "\n",
    "\n",
    "def kernel_restarter():\n",
    "    from IPython.display import display_html\n",
    "    display_html(\"<script>Jupyter.notebook.kernel.restart()</script>\",raw=True)\n",
    "kernel_restarter()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6.1 Fincal code fro CSV TO SQL "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def csv_to_pandas(csv_file_name, bar_size):\n",
    "    df=pd.read_csv(csv_file_name)\n",
    "    \n",
    "    long_bars=[\"1day\",\"1week\", \"1month\"]\n",
    "    if bar_size in long_bars:\n",
    "        df[\"date\"]=pd.to_datetime(df[\"date\"],format='%Y%m%d')\n",
    "    else:\n",
    "        df[\"date\"]=pd.to_datetime(df[\"date\"],format='%Y%m%d  %H:%M:%S')\n",
    "    \n",
    "    df.set_index(\"date\", inplace=True)\n",
    "    return df\n",
    "\n",
    "def csv_name_to_data(file_name):\n",
    "    ####### To Extarct Info from CSV file\n",
    "    csv_file_name=file_name.split(\"/\")[2]\n",
    "    ticker=csv_file_name.split(\"_\")[0]\n",
    "    bar_size=csv_file_name.split(\"_\")[3].split(\".\")[0]\n",
    "    tabel_name=ticker+\"_\"+bar_size\n",
    "    tabel_name=tabel_name.lower()\n",
    "    return  tabel_name, bar_size\n",
    "\n",
    "\n",
    "def sql_ingester(data_dir, engine, overwrite=False):\n",
    "    \n",
    "    from sqlalchemy import Table, Column, Integer, String, MetaData, DateTime, Float\n",
    "    \n",
    "    \n",
    "    ######### Csv Part #####\n",
    "    import glob\n",
    "    csv_list=glob.glob(data_dir+\"/*\")\n",
    "    ########################    \n",
    "    \n",
    "    ##### Sql Part #######\n",
    "    for file_name in csv_list:\n",
    "\n",
    "        #### Extracting Info #########\n",
    "        table_name, bar_size=csv_name_to_data(file_name)\n",
    "        df=csv_to_pandas(file_name, bar_size)\n",
    "        ##################################\n",
    "\n",
    "        \n",
    "        meta = MetaData()\n",
    "        tick_table = Table(\n",
    "        table_name, meta, \n",
    "        Column('date', DateTime, primary_key = True), \n",
    "        Column('open', Float),\n",
    "        Column('high', Float),\n",
    "        Column('low', Float),\n",
    "        Column('close', Float),\n",
    "        Column('volume', Float),\n",
    "      )\n",
    "        if table_name in engine.table_names():\n",
    "            print(table_name,\":\")\n",
    "            if overwrite:\n",
    "                print(\"Table exist, deleting this and creating new table.\")\n",
    "                tick_table.drop(engine)\n",
    "                tick_table.create(engine)\n",
    "                df.to_sql(table_name, engine, if_exists='append', chunksize=5000, method='multi')\n",
    "                print(\"Done\")\n",
    "            else:\n",
    "                print(\"Table exist, updating record.\")\n",
    "                old_df=pd.read_sql_table(table_name,engine, index_col=\"date\")\n",
    "                df=df[~df.index.isin(old_df.index)]\n",
    "                df.to_sql(table_name, engine, if_exists='append', chunksize=5000, method='multi')\n",
    "                print(\"Done\")\n",
    "        else:\n",
    "            print(\"Table does not exist, creating one.\")\n",
    "            tick_table.create(engine)\n",
    "            df.to_sql(table_name, engine, if_exists='append', chunksize=5000, method='multi')\n",
    "            print(\"Done\")\n",
    "            \n",
    "     \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tatamotor_1day :\n",
      "Table exist, updating record.\n",
      "Done\n",
      "tatasteel_1min :\n",
      "Table exist, updating record.\n",
      "Done\n",
      "tatasteel_1min :\n",
      "Table exist, updating record.\n",
      "Done\n",
      "tatapower_1day :\n",
      "Table exist, updating record.\n",
      "Done\n"
     ]
    }
   ],
   "source": [
    "data_dir=\"./Data\"\n",
    "\n",
    "import sqlalchemy\n",
    "sql_obj = sqlalchemy.create_engine('postgresql://krh:krh@123@localhost:5432/krh')\n",
    "    \n",
    "df=sql_ingester(data_dir, sql_obj, False)"
   ]
  },
  {
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}