stock-market-scraper.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"Assets/yahoo_finance_logo.png\" alt=\"drawing\" width=\"200\" />\n",
    "\n",
    "<h1 style=\"text-align: center;\">stock-market-scraper</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>\n",
    "<br/>\n",
    "\n",
    "### `Remember this is for downloading only selected stocks only. `\n",
    "* **`For mass downloading, head here:`  [stock-market-scraper](https://github.com/Gunjan933/stock-market-scraper/blob/master/stock-market-scraper.py)**\n",
    "\n",
    "* **`Readme is here:`  [README.md](https://github.com/Gunjan933/stock-market-scraper/blob/master/README.md)**\n",
    "\n",
    "\n",
    "\n",
    "<br/>\n",
    "<br/>\n",
    "\n",
    "# Let's see the scraping idea\n",
    "<br/>\n",
    "\n",
    "**Yahoo has gone to a Reactjs front end which means if you analyze the request headers from the client to the backend you can get the actual JSON they use to populate the client side stores.**\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "<br/>\n",
    "\n",
    "### Hosts:\n",
    "* `query1.finance.yahoo.com` HTTP/1.0\n",
    "* `query2.finance.yahoo.com` HTTP/1.1 [difference between HTTP/1.0 & HTTP/1.1](https://stackoverflow.com/questions/246859/http-1-0-vs-1-1)  \n",
    "\n",
    "If you plan to use a proxy or persistent connections use `query2.finance.yahoo.com`. But for the purposes of this post the host used for the example URLs is not meant to imply anything about the path it's being used with.\n",
    "\n",
    "> We will use HTTP/1.1\n",
    "\n",
    "\n",
    "### Fundamental Data\n",
    "* `/v10/finance/quoteSummary/AAPL?modules=` (Full list of modules below)\n",
    "\n",
    "(substitute your symbol for: AAPL)\n",
    "\n",
    "#### Inputs for the `?modules=` query:\n",
    "\n",
    "* ```modules = [\n",
    "   'assetProfile',\n",
    "   'incomeStatementHistory',\n",
    "   'incomeStatementHistoryQuarterly',\n",
    "   'balanceSheetHistory',\n",
    "   'balanceSheetHistoryQuarterly',\n",
    "   'cashflowStatementHistory',\n",
    "   'cashflowStatementHistoryQuarterly',\n",
    "   'defaultKeyStatistics',\n",
    "   'financialData',\n",
    "   'calendarEvents',\n",
    "   'secFilings',\n",
    "   'recommendationTrend',\n",
    "   'upgradeDowngradeHistory',\n",
    "   'institutionOwnership',\n",
    "   'fundOwnership',\n",
    "   'majorDirectHolders',\n",
    "   'majorHoldersBreakdown',\n",
    "   'insiderTransactions',\n",
    "   'insiderHolders',\n",
    "   'netSharePurchaseActivity',\n",
    "   'earnings',\n",
    "   'earningsHistory',\n",
    "   'earningsTrend',\n",
    "   'industryTrend',\n",
    "   'indexTrend',\n",
    "   'sectorTrend' ]\n",
    "   ```\n",
    "#### Example URL:\n",
    "\n",
    "* `https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile%2CearningsHistory`\n",
    "\n",
    "Querying for: `assetProfile` and `earningsHistory`\n",
    "\n",
    "The `%2C` is the Hex representation of `,` and needs to be inserted between each module you request. [details about the hex encoding bit](https://stackoverflow.com/questions/6182356/what-is-2c-in-a-url) (if you care)  \n",
    "\n",
    "<br/>\n",
    "\n",
    "### Options contracts\n",
    "* `/v7/finance/options/AAPL` (current expiration)\n",
    "* `/v7/finance/options/AAPL?date=1579219200` (January 17, 2020 expiration)\n",
    "\n",
    "#### Example URL:\n",
    "\n",
    "* `https://query2.finance.yahoo.com/v7/finance/options/AAPL` (current expiration)\n",
    "* `https://query2.finance.yahoo.com/v7/finance/options/AAPL?date=1579219200` (January 17, 2020 expiration)\n",
    "\n",
    "Any valid future expiration represented as a UNIX timestamp can be used in the `?date=` query. If you query for the current expiration the JSON response will contain a list of all the valid expirations that can be used in the `?date=` query. ([here is a post explaining converting human readable dates to unix timestamp in Python](https://stackoverflow.com/questions/3682748/converting-unix-timestamp-string-to-readable-date))  \n",
    "\n",
    "<br/>\n",
    "\n",
    "### Price\n",
    "* `/v8/finance/chart/AAPL?symbol=AAPL&period1=0&period2=9999999999&interval=3mo`  \n",
    "\n",
    "#### Intervals:\n",
    "\n",
    "* `&interval=3mo` 3 months, going back until initial trading date.\n",
    "* `&interval=1d` 1 day, going back until initial trading date.\n",
    "* `&interval=5m` 5 minuets, going back 80(ish) days.\n",
    "* `&interval=1m` 1 minuet, going back 4-5 days.\n",
    "\n",
    "How far back you can go with each interval is a little confusing and seems inconsistent. My assumption is that internally yahoo is counting in trading days and my naive approach was not accounting for holidays. Although that's a guess and YMMV.\n",
    "\n",
    "`period1=`: unix timestamp representation of the date you wish to **start** at. Values below the initial trading date will be rounded up to the initial trading date.\n",
    "\n",
    "`period2=`: unix timestamp representation of the date you wish to **end** at. Values greater than the last trading date will be rounded down to the most recent timestamp available.\n",
    "\n",
    "**Note:** *If you query with a `period1=` (start date) that is too far in the past for the interval you've chosen, yahoo will return prices in the `3mo` interval regardless of what interval you requested.*\n",
    "\n",
    "#### Add pre & post market data\n",
    "\n",
    "`&includePrePost=true`\n",
    "\n",
    "#### Add dividends & splits\n",
    "\n",
    "`&events=div%2Csplit`\n",
    "\n",
    "#### Example URL:  \n",
    "\n",
    "* `https://query1.finance.yahoo.com/v8/finance/chart/AAPL?symbol=AAPL&period1=0&period2=9999999999&interval=1d&includePrePost=true&events=div%2Csplit`  \n",
    "\n",
    "The above request will return all price data for ticker AAPL on a 1 day interval including pre and post market data as well as dividends and splits.\n",
    "\n",
    "**Note:** *the values used in the price example url for `period1=` & `period2=` are to demonstrate the respective rounding behavior of each input.*\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>  \n",
    "\n",
    "`The above article is taken from `**[here](https://stackoverflow.com/a/47505102/8141330)**`.`\n",
    "\n",
    "<br/>  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dividents and Splits\n",
    "\n",
    "Yahoo adjusts **all historical** prices to reflect a stock **split**. For example, `ISRG` was trading around $1000 prior to `2017/10/06`. Then on `2017/10/06`, it underwent a 3-for-1 stock split. As you can see, Yahoo's historical prices divided all prices by 3 (both prior to and after `2017/10/06`):\n",
    "  \n",
    "    \n",
    "![](Assets/splits.png)\n",
    "\n",
    "For **dividends**, let's say stock `ABC` closed at 200 on December 18. Then on December 19, the stock increases in price by `$2` but it pays out a `$1` dividend. In Yahoo's historical prices for XYZ, you will see that it closed at 200 on Dec 18 and 201 on Dec 19. Yahoo factors in the dividend in the **\"Adj Close\"** column for all the previous days. So the Close for Dec 18 would be 200, but the Adj Close would be 199.\n",
    "\n",
    "For example, on 2017/09/15, SPY paid out a `$1.235` dividend. Yahoo's historical prices say that SPY's closing price on 2017/09/14 was 250.09, but the Adj Close is 248.85, which is `$1.24` lower. The **Adjusted Close** for the previous days was reduced by the dividend amount.\n",
    "  \n",
    "![](Assets/dividents.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>  \n",
    "\n",
    "`The above article is taken from `**[here](https://money.stackexchange.com/a/44146)**`.`\n",
    "\n",
    "<br/>  \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>  \n",
    "\n",
    "# Now let's get back to some Code to get historic prices of stocks\n",
    "<br/>  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import some modules:  \n",
    "* **urllib**: *To get url data*\n",
    "* **json**: *To handle json files*\n",
    "* **time**: *To put the program in sleep for some time*\n",
    "* **os**: *To walk through different directories*\n",
    "* **difflib**: *To get close matches of strings. Helps to find correct stock from the input user gives.*\n",
    "* **itertools**: *To repeat same variable to pass in multithreading funciton.*\n",
    "* **pandas**: *To handle matrix and csv file*\n",
    "* **datetime**: *To change unix timestamp to normal date and time. Yahoo query uses unix timestamp*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request, json , time, os, difflib, itertools\n",
    "import pandas as pd\n",
    "from multiprocessing.dummy import Pool\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's make a code snippet which can tell if we have working internet connection or not.\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    import httplib\n",
    "except:\n",
    "    import http.client as httplib\n",
    "\n",
    "def check_internet():\n",
    "    conn = httplib.HTTPConnection(\"www.google.com\", timeout=5)\n",
    "    try:\n",
    "        conn.request(\"HEAD\", \"/\")\n",
    "        conn.close()\n",
    "        # print(\"True\")\n",
    "        return True\n",
    "    except:\n",
    "        conn.close()\n",
    "        # print(\"False\")\n",
    "        return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<br/>\n",
    "\n",
    "Now see below, I have opened an arbitrary stock `Igarashi Motors`. In URL can you see the **ticker** for the stock? It is `IGARASHI.BO`\n",
    "\n",
    "<br/>\n",
    "\n",
    "![](Assets/chart.png)\n",
    "\n",
    "\n",
    "<br/>   \n",
    "<br/>\n",
    "<br/>\n",
    "\n",
    "How to get the **ticker**, I will show you later.  \n",
    "\n",
    "First let us make a **function** that can pull `json data` from yahoo about that stock like below. (I will discuss about the function `parameters` later)\n",
    "> We will be using query2\n",
    "\n",
    "<br/>\n",
    "<br/>\n",
    "<br/>\n",
    "\n",
    "![](Assets/query_json.png)\n",
    "\n",
    "<br/>\n",
    "<br/>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>\n",
    "\n",
    "### Now write down the function which will `get_stock_price` for given `query_url`.\n",
    "\n",
    "#### It will save the stock data as `json` and `csv` inside a folder named \"historic_data\"\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def get_historic_price(query_url,json_path,csv_path):\n",
    "    \n",
    "    while not check_internet():\n",
    "        print(\"Could not connect, trying again in 5 seconds...\")\n",
    "        time.sleep(5)\n",
    "    \n",
    "    stock_id=query_url.split(\"&period\")[0].split(\"symbol=\")[1]\n",
    "    \n",
    "    if os.path.exists(csv_path+stock_id+'.csv') and os.stat(csv_path+stock_id+'.csv').st_size != 0:\n",
    "        print(\"<<<  Historical data of \"+stock_id+\" already exists, Updating data...\")\n",
    "\n",
    "    try:\n",
    "        with urllib.request.urlopen(query_url) as url:\n",
    "            parsed = json.loads(url.read().decode())\n",
    "    except:\n",
    "        print(\"|||  Historical data of \"+stock_id+\" doesn't exist\")\n",
    "        return\n",
    "    \n",
    "    else:\n",
    "        if os.path.exists(json_path+stock_id+'.json'):\n",
    "            os.remove(json_path+stock_id+'.json')\n",
    "        with open(json_path+stock_id+'.json', 'w') as outfile:\n",
    "            json.dump(parsed, outfile, indent=4)\n",
    "\n",
    "        try:\n",
    "            Date=[]\n",
    "            for i in parsed['chart']['result'][0]['timestamp']:\n",
    "                Date.append(datetime.utcfromtimestamp(int(i)).strftime('%d-%m-%Y'))\n",
    "\n",
    "            Low=parsed['chart']['result'][0]['indicators']['quote'][0]['low']\n",
    "            Open=parsed['chart']['result'][0]['indicators']['quote'][0]['open']\n",
    "            Volume=parsed['chart']['result'][0]['indicators']['quote'][0]['volume']\n",
    "            High=parsed['chart']['result'][0]['indicators']['quote'][0]['high']\n",
    "            Close=parsed['chart']['result'][0]['indicators']['quote'][0]['close']\n",
    "            Adjusted_Close=parsed['chart']['result'][0]['indicators']['adjclose'][0]['adjclose']\n",
    "\n",
    "            df=pd.DataFrame(list(zip(Date,Low,Open,Volume,High,Close,Adjusted_Close)),columns =['Date','Low','Open','Volume','High','Close','Adjusted Close'])\n",
    "\n",
    "            if os.path.exists(csv_path+stock_id+'.csv'):\n",
    "                os.remove(csv_path+stock_id+'.csv')\n",
    "            df.to_csv(csv_path+stock_id+'.csv', sep=',', index=None)\n",
    "            print(\">>>  Historical data of \"+stock_id+\" saved\")\n",
    "            return\n",
    "        except:\n",
    "            print(\">>>  Historical data of \"+stock_id+\" exists but has no trading data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>\n",
    "\n",
    "#### First we have to set where the `json` and `csv` files will be saved which have been passed to the function `get_historic_price()`\n",
    "<br/>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_path = os.getcwd()+os.sep+\"..\"+os.sep+\"historic_data\"+os.sep+\"json\"+os.sep\n",
    "csv_path = os.getcwd()+os.sep+\"..\"+os.sep+\"historic_data\"+os.sep+\"csv\"+os.sep"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   \n",
    "#### Then we have to check if these directory exists, if not, then we will use `os.mkdir`\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.isdir(json_path):\n",
    "    os.makedirs(json_path)\n",
    "if not os.path.isdir(csv_path):\n",
    "    os.makedirs(csv_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<br/>\n",
    "<br/>\n",
    "\n",
    "## Getting tickers\n",
    "\n",
    "Now as promised I will be showing how to find **historical data**. See below, I have opened historical data of `Igarashi Motors`. Here you can see max time period from which we can pull data for the stock. It stores period as `unix timestamp` in the query.\n",
    "\n",
    "<br/>\n",
    "\n",
    "![](Assets/historic_data.png)\n",
    "\n",
    "\n",
    "<br/>   \n",
    "<br/>\n",
    "<br/>\n",
    "\n",
    "Now let's make the **query**. First set\n",
    "* `period1 = 0`\n",
    "* `period2 = 9999999999`\n",
    "* `interval = 1d`  \n",
    "\n",
    "See the image below, it's `period1` is greater than `0` and `period2` is lesser than `9999999999`. This produces maximum span period from which data can be pulled.\n",
    "\n",
    "<br/>\n",
    "<br/>\n",
    "<br/>\n",
    "\n",
    "![](Assets/find_query.png)\n",
    "\n",
    "<br/>\n",
    "<br/>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>  \n",
    "\n",
    "#### Then we need to open our csv file where `yahoo finance tickers` are saved. This is in the `Assets` folder\n",
    "<br/>\n",
    "\n",
    "How did I get this? Well here is the **[direct link](http://investexcel.net/wp-content/uploads/2015/01/Yahoo-Ticker-Symbols-September-2017.zip)** to download the **yahoo ticker list (last updated September 2017)**. It would be helpful for the author if you visit **[his website page](http://investexcel.net/all-yahoo-finance-stock-tickers/)**, as his income is through advertisements, and it takes lots of hours to create this type of ticker list.\n",
    "\n",
    "All right, moving on.\n",
    "\n",
    "<br/>\n",
    "\n",
    "## Important Note:\n",
    "\n",
    "As I will be working on `India`, I will be using a function which gives me the list of stocks which are from India only. If you are from any other country, just change the `country name`, and it will return a list of stocks that are only of `your country`. This shrinking will help us speed up the program. As the original list contains **`106328 stocks`**.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_name = \"india\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's now make the funciton to shrink the ticker list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total stocks: 106331\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>Yahoo Stock Tickers</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://investexcel.net</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ticker</td>\n",
       "      <td>Name</td>\n",
       "      <td>Exchange</td>\n",
       "      <td>Category Name</td>\n",
       "      <td>Country</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OEDV</td>\n",
       "      <td>Osage Exploration and Development, Inc.</td>\n",
       "      <td>PNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Samir Khan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Apple Inc.</td>\n",
       "      <td>NMS</td>\n",
       "      <td>Electronic Equipment</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>simulationconsultant@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BAC</td>\n",
       "      <td>Bank of America Corporation</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Money Center Banks</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AMZN</td>\n",
       "      <td>Amazon.com, Inc.</td>\n",
       "      <td>NMS</td>\n",
       "      <td>Catalog &amp; Mail Order Houses</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>This ticker symbol list was downloaded from</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>T</td>\n",
       "      <td>AT&amp;T Inc.</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Telecom Services - Domestic</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://investexcel.net/all-yahoo-finance-stock...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>GOOG</td>\n",
       "      <td>Alphabet Inc.</td>\n",
       "      <td>NMS</td>\n",
       "      <td>Internet Information Providers</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>and was updated on 2nd September 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MO</td>\n",
       "      <td>Altria Group, Inc.</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Cigarettes</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Yahoo Stock Tickers                               Unnamed: 1 Unnamed: 2  \\\n",
       "0  http://investexcel.net                                      NaN        NaN   \n",
       "1                     NaN                                      NaN        NaN   \n",
       "2                  Ticker                                     Name   Exchange   \n",
       "3                    OEDV  Osage Exploration and Development, Inc.        PNK   \n",
       "4                    AAPL                               Apple Inc.        NMS   \n",
       "5                     BAC              Bank of America Corporation        NYQ   \n",
       "6                    AMZN                         Amazon.com, Inc.        NMS   \n",
       "7                       T                                AT&T Inc.        NYQ   \n",
       "8                    GOOG                            Alphabet Inc.        NMS   \n",
       "9                      MO                       Altria Group, Inc.        NYQ   \n",
       "\n",
       "                       Unnamed: 3 Unnamed: 4  Unnamed: 5  Unnamed: 6  \\\n",
       "0                             NaN        NaN         NaN         NaN   \n",
       "1                             NaN        NaN         NaN         NaN   \n",
       "2                   Category Name    Country         NaN         NaN   \n",
       "3                             NaN        USA         NaN         NaN   \n",
       "4            Electronic Equipment        USA         NaN         NaN   \n",
       "5              Money Center Banks        USA         NaN         NaN   \n",
       "6     Catalog & Mail Order Houses        USA         NaN         NaN   \n",
       "7     Telecom Services - Domestic        USA         NaN         NaN   \n",
       "8  Internet Information Providers        USA         NaN         NaN   \n",
       "9                      Cigarettes        USA         NaN         NaN   \n",
       "\n",
       "                                          Unnamed: 7  \n",
       "0                                                NaN  \n",
       "1                                                NaN  \n",
       "2                                                NaN  \n",
       "3                                         Samir Khan  \n",
       "4                     simulationconsultant@gmail.com  \n",
       "5                                                NaN  \n",
       "6        This ticker symbol list was downloaded from  \n",
       "7  http://investexcel.net/all-yahoo-finance-stock...  \n",
       "8              and was updated on 2nd September 2017  \n",
       "9                                                NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticker_file_path = \"Assets\"+os.sep+\"Yahoo Ticker Symbols - September 2017.xlsx\"\n",
    "temp_df = pd.read_excel(ticker_file_path)\n",
    "print(\"Total stocks:\",len(temp_df))\n",
    "temp_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>\n",
    "\n",
    "#### See the above list is messy, it contains garbage informations. So refining it we get\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total stocks: 106328\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>2</th>\n",
       "      <th>Ticker</th>\n",
       "      <th>Name</th>\n",
       "      <th>Exchange</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OEDV</td>\n",
       "      <td>Osage Exploration and Development, Inc.</td>\n",
       "      <td>PNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Apple Inc.</td>\n",
       "      <td>NMS</td>\n",
       "      <td>Electronic Equipment</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BAC</td>\n",
       "      <td>Bank of America Corporation</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Money Center Banks</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMZN</td>\n",
       "      <td>Amazon.com, Inc.</td>\n",
       "      <td>NMS</td>\n",
       "      <td>Catalog &amp; Mail Order Houses</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>T</td>\n",
       "      <td>AT&amp;T Inc.</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Telecom Services - Domestic</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GOOG</td>\n",
       "      <td>Alphabet Inc.</td>\n",
       "      <td>NMS</td>\n",
       "      <td>Internet Information Providers</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MO</td>\n",
       "      <td>Altria Group, Inc.</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Cigarettes</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DAL</td>\n",
       "      <td>Delta Air Lines, Inc.</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Major Airlines</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AA</td>\n",
       "      <td>Alcoa Corporation</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Aluminum</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AXP</td>\n",
       "      <td>American Express Company</td>\n",
       "      <td>NYQ</td>\n",
       "      <td>Credit Services</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2 Ticker                                     Name Exchange  \\\n",
       "0   OEDV  Osage Exploration and Development, Inc.      PNK   \n",
       "1   AAPL                               Apple Inc.      NMS   \n",
       "2    BAC              Bank of America Corporation      NYQ   \n",
       "3   AMZN                         Amazon.com, Inc.      NMS   \n",
       "4      T                                AT&T Inc.      NYQ   \n",
       "5   GOOG                            Alphabet Inc.      NMS   \n",
       "6     MO                       Altria Group, Inc.      NYQ   \n",
       "7    DAL                    Delta Air Lines, Inc.      NYQ   \n",
       "8     AA                        Alcoa Corporation      NYQ   \n",
       "9    AXP                 American Express Company      NYQ   \n",
       "\n",
       "2                   Category Name Country  \n",
       "0                             NaN     USA  \n",
       "1            Electronic Equipment     USA  \n",
       "2              Money Center Banks     USA  \n",
       "3     Catalog & Mail Order Houses     USA  \n",
       "4     Telecom Services - Domestic     USA  \n",
       "5  Internet Information Providers     USA  \n",
       "6                      Cigarettes     USA  \n",
       "7                  Major Airlines     USA  \n",
       "8                        Aluminum     USA  \n",
       "9                 Credit Services     USA  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_df = temp_df.drop(temp_df.columns[[5, 6, 7]], axis=1)\n",
    "headers = temp_df.iloc[2]\n",
    "df  = pd.DataFrame(temp_df.values[3:], columns=headers)\n",
    "print(\"Total stocks:\",len(df))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>  \n",
    "\n",
    "#### Let's only take the country which is set to `country_name` previously\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total stocks: 8984\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>2</th>\n",
       "      <th>Ticker</th>\n",
       "      <th>Name</th>\n",
       "      <th>Exchange</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1230</th>\n",
       "      <td>BHARTIARTL.NS</td>\n",
       "      <td>Bharti Airtel Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Wireless Communications</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1247</th>\n",
       "      <td>ASHOKLEY.NS</td>\n",
       "      <td>Ashok Leyland Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Auto Manufacturers - Major</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1441</th>\n",
       "      <td>AUROPHARMA.NS</td>\n",
       "      <td>Aurobindo Pharma Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Drugs - Generic</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1457</th>\n",
       "      <td>AREXMIS.BO</td>\n",
       "      <td>Arex Industries Ltd.</td>\n",
       "      <td>BSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1586</th>\n",
       "      <td>SANWARIA.NS</td>\n",
       "      <td>Sanwaria Agro Oils Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Farm Products</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1907</th>\n",
       "      <td>ALMONDZ.NS</td>\n",
       "      <td>Almondz Global Securities Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Investment Brokerage - National</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1962</th>\n",
       "      <td>ADINATH.BO</td>\n",
       "      <td>Adinath Textiles Ltd</td>\n",
       "      <td>BSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2897</th>\n",
       "      <td>SBIN.NS</td>\n",
       "      <td>State Bank of India</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Money Center Banks</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3199</th>\n",
       "      <td>BPCL.NS</td>\n",
       "      <td>Bharat Petroleum Corporation Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>Oil &amp; Gas Refining &amp; Marketing</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3322</th>\n",
       "      <td>MBECL.NS</td>\n",
       "      <td>McNally Bharat Engineering Company Limited</td>\n",
       "      <td>NSI</td>\n",
       "      <td>General Contractors</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2            Ticker                                        Name Exchange  \\\n",
       "1230  BHARTIARTL.NS                       Bharti Airtel Limited      NSI   \n",
       "1247    ASHOKLEY.NS                       Ashok Leyland Limited      NSI   \n",
       "1441  AUROPHARMA.NS                    Aurobindo Pharma Limited      NSI   \n",
       "1457     AREXMIS.BO                        Arex Industries Ltd.      BSE   \n",
       "1586    SANWARIA.NS                  Sanwaria Agro Oils Limited      NSI   \n",
       "1907     ALMONDZ.NS           Almondz Global Securities Limited      NSI   \n",
       "1962     ADINATH.BO                        Adinath Textiles Ltd      BSE   \n",
       "2897        SBIN.NS                         State Bank of India      NSI   \n",
       "3199        BPCL.NS        Bharat Petroleum Corporation Limited      NSI   \n",
       "3322       MBECL.NS  McNally Bharat Engineering Company Limited      NSI   \n",
       "\n",
       "2                       Category Name Country  \n",
       "1230          Wireless Communications   India  \n",
       "1247       Auto Manufacturers - Major   India  \n",
       "1441                  Drugs - Generic   India  \n",
       "1457                              NaN   India  \n",
       "1586                    Farm Products   India  \n",
       "1907  Investment Brokerage - National   India  \n",
       "1962                              NaN   India  \n",
       "2897               Money Center Banks   India  \n",
       "3199   Oil & Gas Refining & Marketing   India  \n",
       "3322              General Contractors   India  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df = df[df[\"Country\"].str.lower().str.contains(country_name.lower()) == True]\n",
    "print(\"Total stocks:\",len(new_df))\n",
    "new_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>\n",
    "\n",
    "#### Saving the list of stcks with tickers with `country_name` in a different `csv` file which can be used later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.to_csv('Assets'+os.sep+country_name+'.csv', sep=',', index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " \n",
    "#### Now we got our `tickers` which we can find by company names\n",
    "\n",
    "<br/>\n",
    "\n",
    "### Important Note\n",
    "We can scrap historical data for every stock mentioned on yahoo finance. But remember the data size. There are about **`8984`** stocks for only `India`. And if you want for every stocks, there is **`106328`** stocks. Which will take huge resources and time.\n",
    "Check out my brute force downloader **[stock-market-scraper](stock-market-scraper.py)** for downloading all datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List down the company for which the stocks will be downloaded\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "desired_company_list = [ 'Igarashi Motors' ,\n",
    "                        'State Bank of India' ,\n",
    "                        'Tata Motors' ,\n",
    "                        'Tata Consultancy Services' ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the ticker list for the companies user entered in the `desired_company_list`\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "ticker_list=[]\n",
    "for company in desired_company_list:\n",
    "    try:\n",
    "        exact_company_name = (difflib.get_close_matches(company, new_df['Name'])[0])\n",
    "        ticker_for_the_company = new_df.loc[new_df['Name'] == exact_company_name, 'Ticker'].iloc[0]\n",
    "        ticker_list.append(ticker_for_the_company)\n",
    "    except:\n",
    "        print(\"Company name \"+company+\" not found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/>\n",
    "\n",
    "#### Now create the query urls for the stock `ticker`s. This will bring the query pages, where yahoo finance holds it's historical stock data. \n",
    "\n",
    "<br/>\n",
    "\n",
    "Example query is like this:  `https://query1.finance.yahoo.com/v8/finance/chart/`**ticker**`?symbol=`**ticker**`&period1=0&period2=9999999999&interval=1d&includePrePost=true&events=div%2Csplit`\n",
    "\n",
    "<br/>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_urls=[]\n",
    "for ticker in ticker_list:\n",
    "    query_urls.append(\"https://query1.finance.yahoo.com/v8/finance/chart/\"+ticker+\"?symbol=\"+ticker+\"&period1=0&period2=9999999999&interval=1d&includePrePost=true&events=div%2Csplit\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now get to the stock datas with multithreading.\n",
    "<br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<<<  Historical data of SBIN.NS already exists, Updating data...\n",
      "<<<  Historical data of IGARASHI.NS already exists, Updating data...\n",
      "<<<  Historical data of TATAMOTORS.NS already exists, Updating data...<<<  Historical data of TCS.NS already exists, Updating data...\n",
      "\n",
      ">>>  Historical data of TCS.NS saved\n",
      ">>>  Historical data of IGARASHI.NS saved\n",
      ">>>  Historical data of TATAMOTORS.NS saved\n",
      ">>>  Historical data of SBIN.NS saved\n",
      "All downloads completed !\n"
     ]
    }
   ],
   "source": [
    "with Pool(processes=len(query_urls)) as pool:\n",
    "    pool.starmap(get_historic_price, zip(query_urls, itertools.repeat(json_path), itertools.repeat(csv_path)))\n",
    "print(\"All downloads completed !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## So like this you can update data everyday by yourself\n",
    "<br/>\n",
    "\n",
    "### Source :\n",
    "* **[Stack Overflow](https://stackoverflow.com/)**\n",
    "* **[Yahoo Finance](https://in.finance.yahoo.com/)**\n",
    "* **[Stack Exchange](https://stackexchange.com/)**\n",
    "* **[Python](https://www.python.org/)**\n",
    "* **[Geeks for Geeks](https://www.geeksforgeeks.org/)**"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}