filter_stock.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy import *\n",
    "from numpy import *\n",
    "from matplotlib import *\n",
    "from matplotlib.pyplot import *\n",
    "from matplotlib.font_manager import FontProperties\n",
    "from matplotlib.ticker import MultipleLocator, FormatStrFormatter\n",
    "from matplotlib.font_manager import FontProperties\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import gridspec\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from datetime import datetime, timedelta\n",
    "import matplotlib.dates as mdates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Magic formula\n",
    "1) Collect all the equities based on ascending order of ROCE\n",
    "\n",
    "2) Ascending order by P/E \n",
    "\n",
    "3) Make rank according to location of ROCE and P/E "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>S.No.</th>\n",
       "      <th>Name</th>\n",
       "      <th>CMP Rs.</th>\n",
       "      <th>P/E</th>\n",
       "      <th>Mar Cap Rs.Cr.</th>\n",
       "      <th>Div Yld %</th>\n",
       "      <th>NP Qtr Rs.Cr.</th>\n",
       "      <th>Qtr Profit Var %</th>\n",
       "      <th>Sales Qtr Rs.Cr.</th>\n",
       "      <th>Qtr Sales Var %</th>\n",
       "      <th>ROCE %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Kilpest India</td>\n",
       "      <td>451.50</td>\n",
       "      <td>8.12</td>\n",
       "      <td>338.99</td>\n",
       "      <td>0.22</td>\n",
       "      <td>5.14</td>\n",
       "      <td>-90.19</td>\n",
       "      <td>15.22</td>\n",
       "      <td>-86.44</td>\n",
       "      <td>179.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ksolves India</td>\n",
       "      <td>339.10</td>\n",
       "      <td>28.47</td>\n",
       "      <td>402.04</td>\n",
       "      <td>4.79</td>\n",
       "      <td>3.90</td>\n",
       "      <td>39.78</td>\n",
       "      <td>11.97</td>\n",
       "      <td>70.27</td>\n",
       "      <td>161.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Nupur Recyclers</td>\n",
       "      <td>273.05</td>\n",
       "      <td>56.12</td>\n",
       "      <td>567.94</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.73</td>\n",
       "      <td>NaN</td>\n",
       "      <td>157.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>EKI Energy</td>\n",
       "      <td>11241.85</td>\n",
       "      <td>415.02</td>\n",
       "      <td>7727.65</td>\n",
       "      <td>0.01</td>\n",
       "      <td>161.21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>687.82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>149.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Nestle India</td>\n",
       "      <td>18790.75</td>\n",
       "      <td>80.83</td>\n",
       "      <td>181172.36</td>\n",
       "      <td>1.06</td>\n",
       "      <td>617.37</td>\n",
       "      <td>5.16</td>\n",
       "      <td>3882.57</td>\n",
       "      <td>9.62</td>\n",
       "      <td>139.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   S.No.             Name   CMP Rs.     P/E  Mar Cap Rs.Cr.  Div Yld %  \\\n",
       "0      1    Kilpest India    451.50    8.12          338.99       0.22   \n",
       "1      2    Ksolves India    339.10   28.47          402.04       4.79   \n",
       "2      3  Nupur Recyclers    273.05   56.12          567.94       0.00   \n",
       "3      4       EKI Energy  11241.85  415.02         7727.65       0.01   \n",
       "4      5     Nestle India  18790.75   80.83       181172.36       1.06   \n",
       "\n",
       "   NP Qtr Rs.Cr.  Qtr Profit Var %  Sales Qtr Rs.Cr.  Qtr Sales Var %  ROCE %  \n",
       "0           5.14            -90.19             15.22           -86.44  179.46  \n",
       "1           3.90             39.78             11.97            70.27  161.06  \n",
       "2           6.64               NaN             38.73              NaN  157.58  \n",
       "3         161.21               NaN            687.82              NaN  149.11  \n",
       "4         617.37              5.16           3882.57             9.62  139.29  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#https://in.finance.yahoo.com  #Import the Sample worksheet\n",
    "\n",
    "#df_clean = df['Date'].str.contains(\"null\") | df['Close'].str.contains(\"null\")\n",
    "#df1 = df[~df_clean]\n",
    "#df = pd.read_csv('/home/kishor/Dropbox/stock/sample_data/AMC.AX.csv') #, usecols=[0,4]\n",
    "#clean data file ->  'null' in few rows!\n",
    "#df.dropna(subset=['Close'], inplace=True)\n",
    "\n",
    "# read excel\n",
    "df = pd.read_excel('/Users/kishor/Dropbox/stock/personal/filter_magic_formula/roce_asc.xlsx')\n",
    "df = df.replace(',','', regex=True)\n",
    "l1 = len(df) # length of the data\n",
    "\n",
    "#----------------------------------------\n",
    "#df.to_csv('/Users/kishor/bajaj_test.csv')\n",
    "#pd.set_option('max_rows', l1)\n",
    "#pd.options.display.max_rows\n",
    "#---------------------------------\n",
    "#df.to_csv('/home/kishor/Dropbox/stock/indices/indian_bse.csv')\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>S.No.</th>\n",
       "      <th>Name</th>\n",
       "      <th>CMP Rs.</th>\n",
       "      <th>P/E</th>\n",
       "      <th>Mar Cap Rs.Cr.</th>\n",
       "      <th>Div Yld %</th>\n",
       "      <th>NP Qtr Rs.Cr.</th>\n",
       "      <th>Qtr Profit Var %</th>\n",
       "      <th>Sales Qtr Rs.Cr.</th>\n",
       "      <th>Qtr Sales Var %</th>\n",
       "      <th>ROCE %</th>\n",
       "      <th>Row_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>666</th>\n",
       "      <td>667</td>\n",
       "      <td>FSN E-Commerce</td>\n",
       "      <td>1660.65</td>\n",
       "      <td>1269.79</td>\n",
       "      <td>78536.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.17</td>\n",
       "      <td>-95.74</td>\n",
       "      <td>885.26</td>\n",
       "      <td>46.62</td>\n",
       "      <td>14.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1159</th>\n",
       "      <td>1160</td>\n",
       "      <td>National Standar</td>\n",
       "      <td>10701.00</td>\n",
       "      <td>1019.14</td>\n",
       "      <td>21402.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.86</td>\n",
       "      <td>66.28</td>\n",
       "      <td>0.03</td>\n",
       "      <td>-92.31</td>\n",
       "      <td>5.83</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1083</th>\n",
       "      <td>1084</td>\n",
       "      <td>Waaree Renewab.</td>\n",
       "      <td>371.45</td>\n",
       "      <td>1004.12</td>\n",
       "      <td>773.17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4.30</td>\n",
       "      <td>854.39</td>\n",
       "      <td>11.30</td>\n",
       "      <td>1102.13</td>\n",
       "      <td>7.25</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>860</th>\n",
       "      <td>861</td>\n",
       "      <td>Adani Green</td>\n",
       "      <td>1929.00</td>\n",
       "      <td>751.32</td>\n",
       "      <td>301698.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>460.72</td>\n",
       "      <td>1295.00</td>\n",
       "      <td>111.60</td>\n",
       "      <td>10.96</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>890</td>\n",
       "      <td>Mangalam Global</td>\n",
       "      <td>139.35</td>\n",
       "      <td>699.16</td>\n",
       "      <td>349.58</td>\n",
       "      <td>0.72</td>\n",
       "      <td>-1.19</td>\n",
       "      <td>-178.81</td>\n",
       "      <td>153.13</td>\n",
       "      <td>-45.90</td>\n",
       "      <td>10.44</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      S.No.              Name   CMP Rs.      P/E  Mar Cap Rs.Cr.  Div Yld %  \\\n",
       "666     667    FSN E-Commerce   1660.65  1269.79        78536.66       0.00   \n",
       "1159   1160  National Standar  10701.00  1019.14        21402.00       0.00   \n",
       "1083   1084   Waaree Renewab.    371.45  1004.12          773.17       0.00   \n",
       "860     861       Adani Green   1929.00   751.32       301698.35       0.00   \n",
       "889     890   Mangalam Global    139.35   699.16          349.58       0.72   \n",
       "\n",
       "      NP Qtr Rs.Cr.  Qtr Profit Var %  Sales Qtr Rs.Cr.  Qtr Sales Var %  \\\n",
       "666            1.17            -95.74            885.26            46.62   \n",
       "1159           2.86             66.28              0.03           -92.31   \n",
       "1083           4.30            854.39             11.30          1102.13   \n",
       "860          100.00            460.72           1295.00           111.60   \n",
       "889           -1.19           -178.81            153.13           -45.90   \n",
       "\n",
       "      ROCE %  Row_number  \n",
       "666    14.00           0  \n",
       "1159    5.83           1  \n",
       "1083    7.25           2  \n",
       "860    10.96           3  \n",
       "889    10.44           4  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = df.sort_values('P/E', axis=0, ascending=False, inplace=False)\n",
    "df1['Row_number'] = np.arange(len(df1))\n",
    "#df1=df.sort_values('S.No.', axis=0, ascending=True, inplace=False)\n",
    "df1.to_csv('/Users/kishor/Dropbox/stock/personal/filter_magic_formula/PE.csv')\n",
    "#print(df1.head(7))\n",
    "df2 = df1\n",
    "df1.head(5)\n",
    "\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Rank           Name   CMP Rs.     P/E  Mar Cap Rs.Cr.  Div Yld %  \\\n",
      "3     12     EKI Energy  11241.85  415.02         7727.65       0.01   \n",
      "23    73  Clean Science   2307.30  123.40        24507.90       0.00   \n",
      "66   109   HLE Glascoat   6290.05  138.31         8587.87       0.06   \n",
      "4    112   Nestle India  18790.75   80.83       181172.36       1.06   \n",
      "21   114    Sigma Solve    660.00   89.55          271.33       0.08   \n",
      "\n",
      "    NP Qtr Rs.Cr.  Qtr Profit Var %  Sales Qtr Rs.Cr.  Qtr Sales Var %  \\\n",
      "3          161.21               NaN            687.82              NaN   \n",
      "23          53.52             -1.38            153.16             9.20   \n",
      "66          13.10             15.52            110.01             0.95   \n",
      "4          617.37              5.16           3882.57             9.62   \n",
      "21            NaN               NaN               NaN              NaN   \n",
      "\n",
      "    ROCE %  Row_number  \n",
      "3   149.11           8  \n",
      "23   60.52          49  \n",
      "66   40.94          42  \n",
      "4   139.29         107  \n",
      "21   61.29          92  \n"
     ]
    }
   ],
   "source": [
    "df2[\"S.No.\"] = df1[\"S.No.\"] + df1[\"Row_number\"]\n",
    "df2 = df2.sort_values('S.No.', axis=0, ascending=True, inplace=False)\n",
    "df2 = df2.rename({'S.No.': 'Rank'}, axis=1) \n",
    "df2.to_csv('/Users/kishor/Dropbox/stock/personal/filter_magic_formula/rank.csv')\n",
    "print(df2.head(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change strings to nuemrical values\n",
    "df['Price'] = pd.to_numeric(df['Price'],errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter Promotor Holding more than 30% and dividend yielding\n",
    "#df1 = pd.DataFrame(data = df)\n",
    "df = df[df['Price'] < 10.00]\n",
    "#l2 = len(df1 )\n",
    "df.to_csv('/home/kishor/Downloads/stock/filter_asx100.csv')\n",
    "#df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter Promotor Holding more than 30% and dividend yielding\n",
    "df1 = df[(df['Prom_sha_per'] > 30) & (df['Div_Rs'] > 0)]\n",
    "l2 = len(df1 )\n",
    "#df1.to_csv('/Users/kishor/Dropbox/stock/indices/promotor_hold30.csv')\n",
    "df1.head(l2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter Promotor Holding between 20-30 % also dividend providing\n",
    "df2 = df[(df['Prom_sha_per'] > 20) & (df['Prom_sha_per'] < 30) & (df['Div_Rs'] > 0)]\n",
    "l2 = len(df2 )\n",
    "df2.to_csv('/Users/kishor/Dropbox/stock/indices/pro_20_30_div.csv')\n",
    "df2.head()\n",
    "print(len(df2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter Promotor Holding more than 20% - NO dividend providing, MP greater than zero\n",
    "df3 = df[(df['Prom_sha_per'] > 20) & (df['Div_Rs'] <= 0) & (df['MP_June_21'] > 100)]\n",
    "l2 = len(df3 )\n",
    "#df1.to_csv('/Users/kishor/Dropbox/stock/indices/promotor_hold30.csv')\n",
    "df3.to_csv('/Users/kishor/Dropbox/stock/indices/pr_20_div_0_mp_gt_100.csv')\n",
    "df3.head(l2)\n",
    "print(len(df3))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare Base value and current price\n",
    "df3 = df2[ (abs(df1['MP_June_21']-df1['BV'])/df1['MP_June_21'])*100 < 20]\n",
    "#df3.to_csv('/Users/kishor/Dropbox/stock/indices/base_current_profit.csv')\n",
    "df3.head(l2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['StockName'].str.contains('Amar*')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df.sort_values('LastSale', axis=0, ascending=True, inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.to_csv('/home/kishor/Dropbox/stock/indices/us/nasdaq_des.csv')"
   ]
  },
  {
   "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.10"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}