Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv(\"dataset.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE1</th>\n",
       "      <th>SYMBOL</th>\n",
       "      <th>PREV_CLOSE</th>\n",
       "      <th>OPEN_PRICE</th>\n",
       "      <th>HIGH_PRICE</th>\n",
       "      <th>LOW_PRICE</th>\n",
       "      <th>LAST_PRICE</th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>AVG_PRICE</th>\n",
       "      <th>TTL_TRD_QNTY</th>\n",
       "      <th>TURNOVER_LACS</th>\n",
       "      <th>NO_OF_TRADES</th>\n",
       "      <th>DELIV_QTY</th>\n",
       "      <th>DELIV_PER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11-02-2022</td>\n",
       "      <td>NETFLTGILT</td>\n",
       "      <td>22.40</td>\n",
       "      <td>22.02</td>\n",
       "      <td>22.48</td>\n",
       "      <td>22.02</td>\n",
       "      <td>22.25</td>\n",
       "      <td>22.27</td>\n",
       "      <td>22.25</td>\n",
       "      <td>16697</td>\n",
       "      <td>3.72</td>\n",
       "      <td>83</td>\n",
       "      <td>12968.0</td>\n",
       "      <td>77.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11-02-2022</td>\n",
       "      <td>NETFMID150</td>\n",
       "      <td>116.02</td>\n",
       "      <td>119.50</td>\n",
       "      <td>119.50</td>\n",
       "      <td>113.61</td>\n",
       "      <td>114.10</td>\n",
       "      <td>113.95</td>\n",
       "      <td>114.68</td>\n",
       "      <td>224822</td>\n",
       "      <td>257.82</td>\n",
       "      <td>1780</td>\n",
       "      <td>200777.0</td>\n",
       "      <td>89.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11-02-2022</td>\n",
       "      <td>NETFNIF100</td>\n",
       "      <td>183.49</td>\n",
       "      <td>178.50</td>\n",
       "      <td>184.98</td>\n",
       "      <td>178.50</td>\n",
       "      <td>182.05</td>\n",
       "      <td>182.22</td>\n",
       "      <td>182.07</td>\n",
       "      <td>2354</td>\n",
       "      <td>4.29</td>\n",
       "      <td>135</td>\n",
       "      <td>2140.0</td>\n",
       "      <td>90.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11-02-2022</td>\n",
       "      <td>NETFDIVOPP</td>\n",
       "      <td>45.63</td>\n",
       "      <td>46.25</td>\n",
       "      <td>46.25</td>\n",
       "      <td>45.07</td>\n",
       "      <td>45.33</td>\n",
       "      <td>45.09</td>\n",
       "      <td>45.40</td>\n",
       "      <td>1239</td>\n",
       "      <td>0.56</td>\n",
       "      <td>86</td>\n",
       "      <td>1122.0</td>\n",
       "      <td>90.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-02-2022</td>\n",
       "      <td>NETFGILT5Y</td>\n",
       "      <td>49.66</td>\n",
       "      <td>49.66</td>\n",
       "      <td>49.82</td>\n",
       "      <td>49.47</td>\n",
       "      <td>49.82</td>\n",
       "      <td>49.79</td>\n",
       "      <td>49.72</td>\n",
       "      <td>82821</td>\n",
       "      <td>41.18</td>\n",
       "      <td>322</td>\n",
       "      <td>74512.0</td>\n",
       "      <td>89.97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE1      SYMBOL   PREV_CLOSE   OPEN_PRICE   HIGH_PRICE   LOW_PRICE  \\\n",
       "0  11-02-2022  NETFLTGILT        22.40        22.02        22.48       22.02   \n",
       "1  11-02-2022  NETFMID150       116.02       119.50       119.50      113.61   \n",
       "2  11-02-2022  NETFNIF100       183.49       178.50       184.98      178.50   \n",
       "3  11-02-2022  NETFDIVOPP        45.63        46.25        46.25       45.07   \n",
       "4  11-02-2022  NETFGILT5Y        49.66        49.66        49.82       49.47   \n",
       "\n",
       "    LAST_PRICE   CLOSE_PRICE   AVG_PRICE   TTL_TRD_QNTY   TURNOVER_LACS  \\\n",
       "0        22.25         22.27       22.25          16697            3.72   \n",
       "1       114.10        113.95      114.68         224822          257.82   \n",
       "2       182.05        182.22      182.07           2354            4.29   \n",
       "3        45.33         45.09       45.40           1239            0.56   \n",
       "4        49.82         49.79       49.72          82821           41.18   \n",
       "\n",
       "    NO_OF_TRADES   DELIV_QTY   DELIV_PER  \n",
       "0             83     12968.0       77.67  \n",
       "1           1780    200777.0       89.30  \n",
       "2            135      2140.0       90.91  \n",
       "3             86      1122.0       90.56  \n",
       "4            322     74512.0       89.97  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " DATE1            0\n",
       "SYMBOL            0\n",
       " PREV_CLOSE       0\n",
       " OPEN_PRICE       0\n",
       " HIGH_PRICE       0\n",
       " LOW_PRICE        0\n",
       " LAST_PRICE       0\n",
       " CLOSE_PRICE      0\n",
       " AVG_PRICE        0\n",
       " TTL_TRD_QNTY     0\n",
       " TURNOVER_LACS    0\n",
       " NO_OF_TRADES     0\n",
       " DELIV_QTY        3\n",
       " DELIV_PER        3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop([\" OPEN_PRICE\",\" HIGH_PRICE\",\" LOW_PRICE\",\" LAST_PRICE\",\" AVG_PRICE\",\" TTL_TRD_QNTY\",\" TURNOVER_LACS\",\" NO_OF_TRADES\",\" DELIV_QTY\",\" DELIV_PER\"], axis=1)\n",
    "df[\"change\"]=(((df[\" CLOSE_PRICE\"]-df[\" PREV_CLOSE\"])/df[\" PREV_CLOSE\"])*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[' DATE1'] = pd.to_datetime(df[' DATE1'], dayfirst=True)\n",
    "df['month'] = pd.DatetimeIndex(df[' DATE1']).month\n",
    "df['year'] = pd.DatetimeIndex(df[' DATE1']).year\n",
    "df=df.sort_values(by=[\"SYMBOL\",\"month\",\"year\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pip install pandasql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYMBOL</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20MICRONS</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>0.000304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20MICRONS</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>-0.534678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20MICRONS</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>-0.751252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20MICRONS</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>1.027580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20MICRONS</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>-0.536147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41650</th>\n",
       "      <td>ZYDUSWELL</td>\n",
       "      <td>10</td>\n",
       "      <td>2021</td>\n",
       "      <td>-0.693857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41651</th>\n",
       "      <td>ZYDUSWELL</td>\n",
       "      <td>11</td>\n",
       "      <td>2021</td>\n",
       "      <td>-0.266966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41652</th>\n",
       "      <td>ZYDUSWELL</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "      <td>-0.160813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41653</th>\n",
       "      <td>ZYDUSWELL</td>\n",
       "      <td>1</td>\n",
       "      <td>2022</td>\n",
       "      <td>-0.398216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41654</th>\n",
       "      <td>ZYDUSWELL</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>-1.635702</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41655 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          SYMBOL  month  year    change\n",
       "0      20MICRONS      1  2020  0.000304\n",
       "1      20MICRONS      2  2020 -0.534678\n",
       "2      20MICRONS      3  2020 -0.751252\n",
       "3      20MICRONS      4  2020  1.027580\n",
       "4      20MICRONS      5  2020 -0.536147\n",
       "...          ...    ...   ...       ...\n",
       "41650  ZYDUSWELL     10  2021 -0.693857\n",
       "41651  ZYDUSWELL     11  2021 -0.266966\n",
       "41652  ZYDUSWELL     12  2021 -0.160813\n",
       "41653  ZYDUSWELL      1  2022 -0.398216\n",
       "41654  ZYDUSWELL      2  2022 -1.635702\n",
       "\n",
       "[41655 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandasql import sqldf\n",
    "q=\"select SYMBOL,month,year,avg(change) as change from df group by symbol,year,month order by SYMBOL\"\n",
    "newdf=sqldf(q, globals())\n",
    "newdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYMBOL</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11947</th>\n",
       "      <td>GAYAHWS</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>-9.090909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27993</th>\n",
       "      <td>PARSVNATH</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>-6.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27104</th>\n",
       "      <td>OILCOUNTUB</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>-5.263158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1072</th>\n",
       "      <td>AGROPHOS</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>-5.157383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4821</th>\n",
       "      <td>BCP</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>-4.761905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32857</th>\n",
       "      <td>SELMC</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>4.984945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38845</th>\n",
       "      <td>UGARSUGAR</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>5.077231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40824</th>\n",
       "      <td>WEIZMANIND</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>5.387560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24026</th>\n",
       "      <td>MHLXMIRU</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>8.218743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3775</th>\n",
       "      <td>AWL</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>13.815473</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41655 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           SYMBOL  month  year     change\n",
       "11947     GAYAHWS      1  2020  -9.090909\n",
       "27993   PARSVNATH      1  2020  -6.666667\n",
       "27104  OILCOUNTUB      1  2020  -5.263158\n",
       "1072     AGROPHOS      1  2020  -5.157383\n",
       "4821          BCP      1  2020  -4.761905\n",
       "...           ...    ...   ...        ...\n",
       "32857       SELMC      2  2022   4.984945\n",
       "38845   UGARSUGAR      2  2022   5.077231\n",
       "40824  WEIZMANIND      2  2022   5.387560\n",
       "24026    MHLXMIRU      2  2022   8.218743\n",
       "3775          AWL      2  2022  13.815473\n",
       "\n",
       "[41655 rows x 4 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdf=newdf.sort_values(by=[\"year\",\"month\",\"change\"])\n",
    "newdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYMBOL</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>max(change)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>INTEGRA</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>16.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UVSL</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KSERASERA</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>13.157895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PRADIP</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>11.397547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SEPOWER</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>9.393939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CASTEXTECH</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>10.207071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ROSSARI</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>10.147587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CCCL</td>\n",
       "      <td>8</td>\n",
       "      <td>2020</td>\n",
       "      <td>14.093915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ROUTE</td>\n",
       "      <td>9</td>\n",
       "      <td>2020</td>\n",
       "      <td>16.056921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CASTEXTECH</td>\n",
       "      <td>10</td>\n",
       "      <td>2020</td>\n",
       "      <td>8.232680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>GLAND</td>\n",
       "      <td>11</td>\n",
       "      <td>2020</td>\n",
       "      <td>6.218003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BECTORFOOD</td>\n",
       "      <td>12</td>\n",
       "      <td>2020</td>\n",
       "      <td>18.463369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>KARMAENG</td>\n",
       "      <td>1</td>\n",
       "      <td>2021</td>\n",
       "      <td>7.457785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RAILTEL</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>29.095745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NAZARA</td>\n",
       "      <td>3</td>\n",
       "      <td>2021</td>\n",
       "      <td>18.350047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>BLUECHIP</td>\n",
       "      <td>4</td>\n",
       "      <td>2021</td>\n",
       "      <td>18.988095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>VIJIFIN</td>\n",
       "      <td>5</td>\n",
       "      <td>2021</td>\n",
       "      <td>8.118022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>VINEETLAB</td>\n",
       "      <td>6</td>\n",
       "      <td>2021</td>\n",
       "      <td>19.958438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>TATVA</td>\n",
       "      <td>7</td>\n",
       "      <td>2021</td>\n",
       "      <td>55.790726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>KHAITANLTD</td>\n",
       "      <td>8</td>\n",
       "      <td>2021</td>\n",
       "      <td>4.817891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ABMINTLLTD</td>\n",
       "      <td>9</td>\n",
       "      <td>2021</td>\n",
       "      <td>8.736550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>GAYAHWS</td>\n",
       "      <td>10</td>\n",
       "      <td>2021</td>\n",
       "      <td>6.350414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>GOCOLORS</td>\n",
       "      <td>11</td>\n",
       "      <td>2021</td>\n",
       "      <td>81.695652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>SUPRIYA</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "      <td>16.514317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HIRECT</td>\n",
       "      <td>1</td>\n",
       "      <td>2022</td>\n",
       "      <td>18.708995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AWL</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>13.815473</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        SYMBOL  month  year  max(change)\n",
       "0      INTEGRA      1  2020    16.666667\n",
       "1         UVSL      2  2020    10.000000\n",
       "2    KSERASERA      3  2020    13.157895\n",
       "3       PRADIP      4  2020    11.397547\n",
       "4      SEPOWER      5  2020     9.393939\n",
       "5   CASTEXTECH      6  2020    10.207071\n",
       "6      ROSSARI      7  2020    10.147587\n",
       "7         CCCL      8  2020    14.093915\n",
       "8        ROUTE      9  2020    16.056921\n",
       "9   CASTEXTECH     10  2020     8.232680\n",
       "10       GLAND     11  2020     6.218003\n",
       "11  BECTORFOOD     12  2020    18.463369\n",
       "12    KARMAENG      1  2021     7.457785\n",
       "13     RAILTEL      2  2021    29.095745\n",
       "14      NAZARA      3  2021    18.350047\n",
       "15    BLUECHIP      4  2021    18.988095\n",
       "16     VIJIFIN      5  2021     8.118022\n",
       "17   VINEETLAB      6  2021    19.958438\n",
       "18       TATVA      7  2021    55.790726\n",
       "19  KHAITANLTD      8  2021     4.817891\n",
       "20  ABMINTLLTD      9  2021     8.736550\n",
       "21     GAYAHWS     10  2021     6.350414\n",
       "22    GOCOLORS     11  2021    81.695652\n",
       "23     SUPRIYA     12  2021    16.514317\n",
       "24      HIRECT      1  2022    18.708995\n",
       "25         AWL      2  2022    13.815473"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Top performing Stock\n",
    "q1=\"select symbol,month,year,max(change) from newdf group by year,month\"\n",
    "sqldf(q1, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYMBOL</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>min(change)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GAYAHWS</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>-9.090909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BIRLATYRE</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>-8.343363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EBANK</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>-41.240420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IRCON</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>-4.302631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SURYALAXMI</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>-7.196245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>INDOTHAI</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>-4.882846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>XPROINDIA</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>-8.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>EICHERMOT</td>\n",
       "      <td>8</td>\n",
       "      <td>2020</td>\n",
       "      <td>-4.195377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>VIKASMCORP</td>\n",
       "      <td>9</td>\n",
       "      <td>2020</td>\n",
       "      <td>-4.776977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SHRENIK</td>\n",
       "      <td>10</td>\n",
       "      <td>2020</td>\n",
       "      <td>-5.088548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>INTEGRA</td>\n",
       "      <td>11</td>\n",
       "      <td>2020</td>\n",
       "      <td>-4.241325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MAJESCO</td>\n",
       "      <td>12</td>\n",
       "      <td>2020</td>\n",
       "      <td>-3.231288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>IRFC</td>\n",
       "      <td>1</td>\n",
       "      <td>2021</td>\n",
       "      <td>-4.615385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>UNIVASTU</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>-6.501548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>KALYANKJIL</td>\n",
       "      <td>3</td>\n",
       "      <td>2021</td>\n",
       "      <td>-7.743078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>SUVIDHAA</td>\n",
       "      <td>4</td>\n",
       "      <td>2021</td>\n",
       "      <td>-6.437946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>VIVIMEDLAB</td>\n",
       "      <td>5</td>\n",
       "      <td>2021</td>\n",
       "      <td>-4.946518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>BASML</td>\n",
       "      <td>6</td>\n",
       "      <td>2021</td>\n",
       "      <td>-8.743211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ORCHPHARMA</td>\n",
       "      <td>7</td>\n",
       "      <td>2021</td>\n",
       "      <td>-4.997313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>SAGCEM</td>\n",
       "      <td>8</td>\n",
       "      <td>2021</td>\n",
       "      <td>-3.512445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>GANGAFORGE</td>\n",
       "      <td>9</td>\n",
       "      <td>2021</td>\n",
       "      <td>-4.022442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>ICICI500</td>\n",
       "      <td>10</td>\n",
       "      <td>2021</td>\n",
       "      <td>-4.445929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>SIGACHI</td>\n",
       "      <td>11</td>\n",
       "      <td>2021</td>\n",
       "      <td>-11.437171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>CINEVISTA</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "      <td>-4.976303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AGSTRA</td>\n",
       "      <td>1</td>\n",
       "      <td>2022</td>\n",
       "      <td>-7.971429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AAKASH</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>-12.456280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        SYMBOL  month  year  min(change)\n",
       "0      GAYAHWS      1  2020    -9.090909\n",
       "1    BIRLATYRE      2  2020    -8.343363\n",
       "2        EBANK      3  2020   -41.240420\n",
       "3        IRCON      4  2020    -4.302631\n",
       "4   SURYALAXMI      5  2020    -7.196245\n",
       "5     INDOTHAI      6  2020    -4.882846\n",
       "6    XPROINDIA      7  2020    -8.500000\n",
       "7    EICHERMOT      8  2020    -4.195377\n",
       "8   VIKASMCORP      9  2020    -4.776977\n",
       "9      SHRENIK     10  2020    -5.088548\n",
       "10     INTEGRA     11  2020    -4.241325\n",
       "11     MAJESCO     12  2020    -3.231288\n",
       "12        IRFC      1  2021    -4.615385\n",
       "13    UNIVASTU      2  2021    -6.501548\n",
       "14  KALYANKJIL      3  2021    -7.743078\n",
       "15    SUVIDHAA      4  2021    -6.437946\n",
       "16  VIVIMEDLAB      5  2021    -4.946518\n",
       "17       BASML      6  2021    -8.743211\n",
       "18  ORCHPHARMA      7  2021    -4.997313\n",
       "19      SAGCEM      8  2021    -3.512445\n",
       "20  GANGAFORGE      9  2021    -4.022442\n",
       "21    ICICI500     10  2021    -4.445929\n",
       "22     SIGACHI     11  2021   -11.437171\n",
       "23   CINEVISTA     12  2021    -4.976303\n",
       "24      AGSTRA      1  2022    -7.971429\n",
       "25      AAKASH      2  2022   -12.456280"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Top Non Performing Stock\n",
    "q2=\"select symbol,month,year,min(change) from newdf group by year,month\"\n",
    "sqldf(q2, globals())"
   ]
  },
  {
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}