DSE_web_scrapping.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "DSE web scrapping.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyOURXldu7Mv5LUDTeX2Ec7E",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ra-rownok/dse_web_scraping/blob/main/DSE_web_scrapping.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Rray7_YVyNsd"
      },
      "outputs": [],
      "source": [
        "!pip install requests\n",
        "!pip install html5lib\n",
        "!pip install bs4"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pprint import pprint\n",
        "import requests\n",
        "from bs4 import BeautifulSoup\n",
        "import re\n",
        "import itertools\n",
        "\n",
        "companies = ['CITYBANK','ACI'] \n",
        "\n",
        "eps_data = []\n",
        "month_data = []\n",
        "year_data = []  \n",
        "company_data = []\n",
        "\n",
        "for company in companies:\n",
        "  all_data=[]  # a list to store data\n",
        "  \n",
        "  URL = f\"https://www.dsebd.org/old_news.php?inst={company}&criteria=3&archive=news\"\n",
        "  r = requests.get(URL)\n",
        "    \n",
        "  soup = BeautifulSoup(r.content, 'html5lib')\n",
        "  content = str(soup)\n",
        "    \n",
        "  table = soup.find('table',attrs={'class':'table-news'})\n",
        "\n",
        "  mystring = 'EPS was Tk. (.*?) for (.*?) (.*?) as against Tk. (.*?) for (.*?) (\\d*)'\n",
        "  eps_value_list = re.findall(mystring,content)\n",
        "\n",
        "\n",
        "  for eps in eps_value_list:\n",
        "      eps_value = eps[0::3]\n",
        "      month_duration = eps[1::3]\n",
        "      year = eps[2::3]\n",
        "      all_data.append([eps_value,month_duration,year])\n",
        "\n",
        "  for data in all_data:\n",
        "      company_data.append((company,company))\n",
        "      eps_data.append(data[0])\n",
        "      month_data.append(data[1])\n",
        "      year_data.append(data[2])\n",
        "\n",
        "\n",
        "eps_res = list(itertools.chain(*eps_data))\n",
        "month_res = list(itertools.chain(*month_data))\n",
        "year_res = list(itertools.chain(*year_data))\n",
        "company_res = list(itertools.chain(*company_data))\n",
        "\n",
        "with open(\"output.txt\", \"w\") as f:\n",
        "  for com, eps, month, year in zip(company_res,eps_res, month_res, year_res):\n",
        "      f.write(com + \"\\t\" + eps + \"\\t\" + month + \"\\t\" + year + \"\\n\")\n",
        "      print(\"Company:\",com,\"EPS:\",eps,\",Date:\",month,year)"
      ],
      "metadata": {
        "id": "WBC9vs9xyoV2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Install postgresql server\n",
        "!sudo apt-get -y -qq update\n",
        "!sudo apt-get -y -qq install postgresql\n",
        "!sudo service postgresql start"
      ],
      "metadata": {
        "id": "8iILuIZg14aA",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "cbfec230-a027-4214-fba0-c0a5543c2ba5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * Starting PostgreSQL 10 database server\n",
            "   ...done.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Setup a password `postgres` for username `postgres`\n",
        "!sudo -u postgres psql -U postgres -c \"ALTER USER postgres PASSWORD 'postgres';\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "STsLv5r82-S4",
        "outputId": "11a58312-f98f-4bbc-a593-b03a9909fdf8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "ALTER ROLE\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Setup a database with name `dsescrapping` to be used\n",
        "!sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS dsescrapping;'\n",
        "!sudo -u postgres psql -U postgres -c 'CREATE DATABASE dsescrapping;'"
      ],
      "metadata": {
        "id": "GqunhWWd3TcT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# set connection\n",
        "%env DATABASE_URL=postgresql://postgres:postgres@localhost:5432/dsescrapping"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wc81Ij743k4J",
        "outputId": "6b02abdb-d16e-45df-9b02-d9559328fb4d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "env: DATABASE_URL=postgresql://postgres:postgres@localhost:5432/dsescrapping\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#To load the sql extention to start using %%sql\n",
        "%reload_ext sql"
      ],
      "metadata": {
        "id": "IhMAahfv4uSC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#You can start executing postgres sql commands\n",
        "%%sql\n",
        "select * from information_schema.columns;"
      ],
      "metadata": {
        "id": "TCeO3A7745Fo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "sql='Insert into dse_table(company,eps,date) values'\n",
        "for com, eps, month, year in zip(company_res, eps_res, month_res, year_res):\n",
        "  sql+=f\"('{com}','{eps.replace('(','').replace(')','')}','{month.replace(',','')},{year}'),\"\n",
        "\n",
        "sql = sql[:-1]"
      ],
      "metadata": {
        "id": "SCkrpDyTiBnm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%sql create table dse_table(company varchar(255),eps varchar(255),date varchar(255))"
      ],
      "metadata": {
        "id": "zt0Xyew8azVO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a schema and insert values\n",
        "%%sql\n",
        "set schema 'dsewebscraping';\n"
      ],
      "metadata": {
        "id": "ebrmqqArKGw-",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c3ec6134-f2af-484e-b781-3ce5f031cb33"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://postgres:***@localhost:5432/dsescrapping\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 156
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%sql $sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "j0f1XRwFbg_6",
        "outputId": "3e345b36-87ec-497c-eada-80dc4cec94a1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://postgres:***@localhost:5432/dsescrapping\n",
            "44 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 169
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "Select * from dse_table;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "7nHl-M5sJdzf",
        "outputId": "7050db41-1120-46af-e658-89fd2b7b6e22"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://postgres:***@localhost:5432/dsescrapping\n",
            "44 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('CITYBANK', '1.21', 'April-June,2022'),\n",
              " ('CITYBANK', '1.19', 'April-June,2021'),\n",
              " ('CITYBANK', '2.04', 'January-June,2022'),\n",
              " ('CITYBANK', '2.16', 'January-June,2021'),\n",
              " ('CITYBANK', '0.83', 'January-March,2022'),\n",
              " ('CITYBANK', '0.97', 'January-March,2021'),\n",
              " ('CITYBANK', '0.90', 'July-September,2021'),\n",
              " ('CITYBANK', '1.89', 'July-September,2020'),\n",
              " ('CITYBANK', '2.97', 'January-September,2021'),\n",
              " ('CITYBANK', '2.89', 'January-September,2020'),\n",
              " ('CITYBANK', '1.13', 'April-June,2021'),\n",
              " ('CITYBANK', '0.29', 'April-June,2020'),\n",
              " ('CITYBANK', '2.06', 'January-June,2021'),\n",
              " ('CITYBANK', '1.00', 'January-June,2020'),\n",
              " ('CITYBANK', '0.98', 'January-March,2021'),\n",
              " ('CITYBANK', '0.75', 'January-March,2020'),\n",
              " ('CITYBANK', '1.99', 'July-September,2020'),\n",
              " ('CITYBANK', '0.76', 'July-September,2019'),\n",
              " ('CITYBANK', '3.04', 'January-September,2020'),\n",
              " ('CITYBANK', '2.58', 'January-September,2019'),\n",
              " ('CITYBANK', '0.30', 'April-June,2020'),\n",
              " ('CITYBANK', '1.06', 'April-June,2019'),\n",
              " ('CITYBANK', '1.05', 'January-June,2020'),\n",
              " ('CITYBANK', '1.82', 'January-June,2019'),\n",
              " ('ACI', '0.44', 'January-March,2022'),\n",
              " ('ACI', '0.30', 'January-March,2021'),\n",
              " ('ACI', '5.95', 'July,2021-March 2022'),\n",
              " ('ACI', '3.07', 'July,2020'),\n",
              " ('ACI', '2.10', 'October-December,2021'),\n",
              " ('ACI', '1.44', 'October-December,2020'),\n",
              " ('ACI', '5.51', 'July-December,2021'),\n",
              " ('ACI', '2.77', 'July-December,2020'),\n",
              " ('ACI', '3.93', 'July-September,2021'),\n",
              " ('ACI', '1.54', 'July-September,2020'),\n",
              " ('ACI', '0.35', 'January-March,2021'),\n",
              " ('ACI', '4.81', 'January-March,2020'),\n",
              " ('ACI', '3.54', 'July,2020-March 2021'),\n",
              " ('ACI', '15.90', 'July,2019'),\n",
              " ('ACI', '1.65', 'October-December,2020'),\n",
              " ('ACI', '6.35', 'October-December,2019'),\n",
              " ('ACI', '3.19', 'July-December,2020'),\n",
              " ('ACI', '11.09', 'July-December,2019'),\n",
              " ('ACI', '1.69', 'July-September,2020'),\n",
              " ('ACI', '5.21', 'July-September,2019')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>company</th>\n",
              "            <th>eps</th>\n",
              "            <th>date</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.21</td>\n",
              "            <td>April-June,2022</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.19</td>\n",
              "            <td>April-June,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>2.04</td>\n",
              "            <td>January-June,2022</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>2.16</td>\n",
              "            <td>January-June,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.83</td>\n",
              "            <td>January-March,2022</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.97</td>\n",
              "            <td>January-March,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.90</td>\n",
              "            <td>July-September,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.89</td>\n",
              "            <td>July-September,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>2.97</td>\n",
              "            <td>January-September,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>2.89</td>\n",
              "            <td>January-September,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.13</td>\n",
              "            <td>April-June,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.29</td>\n",
              "            <td>April-June,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>2.06</td>\n",
              "            <td>January-June,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.00</td>\n",
              "            <td>January-June,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.98</td>\n",
              "            <td>January-March,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.75</td>\n",
              "            <td>January-March,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.99</td>\n",
              "            <td>July-September,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.76</td>\n",
              "            <td>July-September,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>3.04</td>\n",
              "            <td>January-September,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>2.58</td>\n",
              "            <td>January-September,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>0.30</td>\n",
              "            <td>April-June,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.06</td>\n",
              "            <td>April-June,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.05</td>\n",
              "            <td>January-June,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>CITYBANK</td>\n",
              "            <td>1.82</td>\n",
              "            <td>January-June,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>0.44</td>\n",
              "            <td>January-March,2022</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>0.30</td>\n",
              "            <td>January-March,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>5.95</td>\n",
              "            <td>July,2021-March 2022</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>3.07</td>\n",
              "            <td>July,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>2.10</td>\n",
              "            <td>October-December,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>1.44</td>\n",
              "            <td>October-December,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>5.51</td>\n",
              "            <td>July-December,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>2.77</td>\n",
              "            <td>July-December,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>3.93</td>\n",
              "            <td>July-September,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>1.54</td>\n",
              "            <td>July-September,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>0.35</td>\n",
              "            <td>January-March,2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>4.81</td>\n",
              "            <td>January-March,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>3.54</td>\n",
              "            <td>July,2020-March 2021</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>15.90</td>\n",
              "            <td>July,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>1.65</td>\n",
              "            <td>October-December,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>6.35</td>\n",
              "            <td>October-December,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>3.19</td>\n",
              "            <td>July-December,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>11.09</td>\n",
              "            <td>July-December,2019</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>1.69</td>\n",
              "            <td>July-September,2020</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>ACI</td>\n",
              "            <td>5.21</td>\n",
              "            <td>July-September,2019</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 170
        }
      ]
    }
  ]
}