Value Based Strategy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Quantitative Value Strategy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests\n",
    "import math\n",
    "from scipy import stats\n",
    "import xlsxwriter\n",
    "import yfinance as yf\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "sp500 = pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')[0]\n",
    "\n",
    "sp500['Symbol'] = sp500['Symbol'].str.replace('.', '-')\n",
    "sp500 = sp500['Symbol'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks_combined = ' '.join(sp500)\n",
    "\n",
    "stocks_data = yf.Tickers(stocks_combined)\n",
    "stocks_data = stocks_data.tickers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Ticker</th>\n",
       "      <th>Price</th>\n",
       "      <th>Price-to-Earnings Ratio</th>\n",
       "      <th>Number of Shares to Buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MMM</td>\n",
       "      <td>125.24</td>\n",
       "      <td>48.731518</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AOS</td>\n",
       "      <td>82.4</td>\n",
       "      <td>21.29199</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABT</td>\n",
       "      <td>111.31</td>\n",
       "      <td>35.113564</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABBV</td>\n",
       "      <td>189.29</td>\n",
       "      <td>63.30769</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACN</td>\n",
       "      <td>323.15</td>\n",
       "      <td>29.59249</td>\n",
       "      <td>N/A</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>498</th>\n",
       "      <td>XYL</td>\n",
       "      <td>130.15</td>\n",
       "      <td>40.16975</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>YUM</td>\n",
       "      <td>135.18</td>\n",
       "      <td>23.968084</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>ZBRA</td>\n",
       "      <td>322.3</td>\n",
       "      <td>72.42696</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>ZBH</td>\n",
       "      <td>110.36</td>\n",
       "      <td>23.939262</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>ZTS</td>\n",
       "      <td>180.59</td>\n",
       "      <td>34.862934</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>503 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Ticker   Price Price-to-Earnings Ratio Number of Shares to Buy\n",
       "0      MMM  125.24               48.731518                     N/A\n",
       "1      AOS    82.4                21.29199                     N/A\n",
       "2      ABT  111.31               35.113564                     N/A\n",
       "3     ABBV  189.29                63.30769                     N/A\n",
       "4      ACN  323.15                29.59249                     N/A\n",
       "..     ...     ...                     ...                     ...\n",
       "498    XYL  130.15                40.16975                     N/A\n",
       "499    YUM  135.18               23.968084                     N/A\n",
       "500   ZBRA   322.3                72.42696                     N/A\n",
       "501    ZBH  110.36               23.939262                     N/A\n",
       "502    ZTS  180.59               34.862934                     N/A\n",
       "\n",
       "[503 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_columns = ['Ticker', 'Price', 'Price-to-Earnings Ratio', 'Number of Shares to Buy']\n",
    "final_dataframe = pd.DataFrame(columns=my_columns)\n",
    "\n",
    "for stock_ticker in stocks_data.values():\n",
    "\n",
    "    response = stock_ticker.info\n",
    "\n",
    "    if 'currentPrice' in response and 'trailingPE' in response:\n",
    "        new_row = pd.Series(\n",
    "        [response['symbol'], response['currentPrice'], response['trailingPE'], 'N/A'],\n",
    "        index=my_columns\n",
    "        )\n",
    "    else:\n",
    "        new_row = pd.Series(\n",
    "        [response['symbol'], 0, 0, 'N/A'],\n",
    "        index=my_columns\n",
    "        )\n",
    "    \n",
    "\n",
    "    final_dataframe = pd.concat([final_dataframe, new_row.to_frame().T], ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Ticker</th>\n",
       "      <th>Price</th>\n",
       "      <th>Price-to-Earnings Ratio</th>\n",
       "      <th>Number of Shares to Buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CRWD</td>\n",
       "      <td>217.89</td>\n",
       "      <td>403.49997</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GILD</td>\n",
       "      <td>76.77</td>\n",
       "      <td>213.24998</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LUV</td>\n",
       "      <td>25.05</td>\n",
       "      <td>192.6923</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DAY</td>\n",
       "      <td>53.23</td>\n",
       "      <td>177.43332</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AMD</td>\n",
       "      <td>132.5</td>\n",
       "      <td>159.63855</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>INCY</td>\n",
       "      <td>64.47</td>\n",
       "      <td>157.24391</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CSGP</td>\n",
       "      <td>75.27</td>\n",
       "      <td>144.75</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NOW</td>\n",
       "      <td>790.64</td>\n",
       "      <td>142.97287</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ARE</td>\n",
       "      <td>113.36</td>\n",
       "      <td>141.7</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IRM</td>\n",
       "      <td>106.69</td>\n",
       "      <td>136.78206</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>LLY</td>\n",
       "      <td>804.46</td>\n",
       "      <td>119.00296</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>TYL</td>\n",
       "      <td>575.81</td>\n",
       "      <td>118.969</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ENPH</td>\n",
       "      <td>105.19</td>\n",
       "      <td>113.10753</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>WELL</td>\n",
       "      <td>112.76</td>\n",
       "      <td>104.4074</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>FIS</td>\n",
       "      <td>74.36</td>\n",
       "      <td>103.27777</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DD</td>\n",
       "      <td>79.12</td>\n",
       "      <td>101.435905</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UDR</td>\n",
       "      <td>40.59</td>\n",
       "      <td>99.0</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>DIS</td>\n",
       "      <td>89.57</td>\n",
       "      <td>97.358696</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MPWR</td>\n",
       "      <td>795.76</td>\n",
       "      <td>94.28436</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>UBER</td>\n",
       "      <td>58.99</td>\n",
       "      <td>93.634926</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>INTC</td>\n",
       "      <td>21.48</td>\n",
       "      <td>89.5</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AXON</td>\n",
       "      <td>292.36</td>\n",
       "      <td>85.485374</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>FICO</td>\n",
       "      <td>1600.38</td>\n",
       "      <td>84.053566</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>LYV</td>\n",
       "      <td>91.21</td>\n",
       "      <td>82.91818</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>EQIX</td>\n",
       "      <td>806.65</td>\n",
       "      <td>80.665</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>ISRG</td>\n",
       "      <td>449.73</td>\n",
       "      <td>77.53966</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>RVTY</td>\n",
       "      <td>123.27</td>\n",
       "      <td>77.04375</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>GLW</td>\n",
       "      <td>38.94</td>\n",
       "      <td>76.35294</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NWS</td>\n",
       "      <td>27.14</td>\n",
       "      <td>73.35135</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ZBRA</td>\n",
       "      <td>322.3</td>\n",
       "      <td>72.42696</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NWSA</td>\n",
       "      <td>26.3</td>\n",
       "      <td>71.08108</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>PTC</td>\n",
       "      <td>172.05</td>\n",
       "      <td>70.51229</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>RTX</td>\n",
       "      <td>116.44</td>\n",
       "      <td>67.69768</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>CDNS</td>\n",
       "      <td>249.63</td>\n",
       "      <td>64.503876</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>TECH</td>\n",
       "      <td>80.17</td>\n",
       "      <td>64.136</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>CLX</td>\n",
       "      <td>144.09</td>\n",
       "      <td>64.04</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>ABBV</td>\n",
       "      <td>189.29</td>\n",
       "      <td>63.30769</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>NVDA</td>\n",
       "      <td>107.27</td>\n",
       "      <td>62.73099</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>AVGO</td>\n",
       "      <td>143.82</td>\n",
       "      <td>61.991383</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>EFX</td>\n",
       "      <td>284.63</td>\n",
       "      <td>60.94861</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>BSX</td>\n",
       "      <td>74.5</td>\n",
       "      <td>60.569103</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>STX</td>\n",
       "      <td>94.57</td>\n",
       "      <td>59.854427</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>PODD</td>\n",
       "      <td>196.45</td>\n",
       "      <td>59.171688</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>TSLA</td>\n",
       "      <td>207.67</td>\n",
       "      <td>58.498592</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>INTU</td>\n",
       "      <td>623.92</td>\n",
       "      <td>57.82391</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>AJG</td>\n",
       "      <td>287.14</td>\n",
       "      <td>56.191784</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>SNPS</td>\n",
       "      <td>505.78</td>\n",
       "      <td>55.276505</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>O</td>\n",
       "      <td>59.32</td>\n",
       "      <td>54.925922</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>COO</td>\n",
       "      <td>93.27</td>\n",
       "      <td>54.864704</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>BXP</td>\n",
       "      <td>66.93</td>\n",
       "      <td>54.860653</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Ticker    Price Price-to-Earnings Ratio Number of Shares to Buy\n",
       "0    CRWD   217.89               403.49997                     N/A\n",
       "1    GILD    76.77               213.24998                     N/A\n",
       "2     LUV    25.05                192.6923                     N/A\n",
       "3     DAY    53.23               177.43332                     N/A\n",
       "4     AMD    132.5               159.63855                     N/A\n",
       "5    INCY    64.47               157.24391                     N/A\n",
       "6    CSGP    75.27                  144.75                     N/A\n",
       "7     NOW   790.64               142.97287                     N/A\n",
       "8     ARE   113.36                   141.7                     N/A\n",
       "9     IRM   106.69               136.78206                     N/A\n",
       "10    LLY   804.46               119.00296                     N/A\n",
       "11    TYL   575.81                 118.969                     N/A\n",
       "12   ENPH   105.19               113.10753                     N/A\n",
       "13   WELL   112.76                104.4074                     N/A\n",
       "14    FIS    74.36               103.27777                     N/A\n",
       "15     DD    79.12              101.435905                     N/A\n",
       "16    UDR    40.59                    99.0                     N/A\n",
       "17    DIS    89.57               97.358696                     N/A\n",
       "18   MPWR   795.76                94.28436                     N/A\n",
       "19   UBER    58.99               93.634926                     N/A\n",
       "20   INTC    21.48                    89.5                     N/A\n",
       "21   AXON   292.36               85.485374                     N/A\n",
       "22   FICO  1600.38               84.053566                     N/A\n",
       "23    LYV    91.21                82.91818                     N/A\n",
       "24   EQIX   806.65                  80.665                     N/A\n",
       "25   ISRG   449.73                77.53966                     N/A\n",
       "26   RVTY   123.27                77.04375                     N/A\n",
       "27    GLW    38.94                76.35294                     N/A\n",
       "28    NWS    27.14                73.35135                     N/A\n",
       "29   ZBRA    322.3                72.42696                     N/A\n",
       "30   NWSA     26.3                71.08108                     N/A\n",
       "31    PTC   172.05                70.51229                     N/A\n",
       "32    RTX   116.44                67.69768                     N/A\n",
       "33   CDNS   249.63               64.503876                     N/A\n",
       "34   TECH    80.17                  64.136                     N/A\n",
       "35    CLX   144.09                   64.04                     N/A\n",
       "36   ABBV   189.29                63.30769                     N/A\n",
       "37   NVDA   107.27                62.73099                     N/A\n",
       "38   AVGO   143.82               61.991383                     N/A\n",
       "39    EFX   284.63                60.94861                     N/A\n",
       "40    BSX     74.5               60.569103                     N/A\n",
       "41    STX    94.57               59.854427                     N/A\n",
       "42   PODD   196.45               59.171688                     N/A\n",
       "43   TSLA   207.67               58.498592                     N/A\n",
       "44   INTU   623.92                57.82391                     N/A\n",
       "45    AJG   287.14               56.191784                     N/A\n",
       "46   SNPS   505.78               55.276505                     N/A\n",
       "47      O    59.32               54.925922                     N/A\n",
       "48    COO    93.27               54.864704                     N/A\n",
       "49    BXP    66.93               54.860653                     N/A"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_dataframe.sort_values('Price-to-Earnings Ratio', ascending=False, inplace=True)\n",
    "final_dataframe = final_dataframe[final_dataframe['Price-to-Earnings Ratio'] > 0]\n",
    "final_dataframe = final_dataframe[:50]\n",
    "final_dataframe.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getAmountToInvest():\n",
    "    portfolio_size = input('Enter the Amount')\n",
    "    val = 0.0\n",
    "    while(True):\n",
    "        try:\n",
    "            val = float(portfolio_size)\n",
    "            break\n",
    "        except:\n",
    "            portfolio_size = input('Enter the Amount Again')\n",
    "    \n",
    "    return (val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Ticker</th>\n",
       "      <th>Price</th>\n",
       "      <th>Price-to-Earnings Ratio</th>\n",
       "      <th>Number of Shares to Buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PRGO</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2800.0</td>\n",
       "      <td>714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMD</td>\n",
       "      <td>151.58</td>\n",
       "      <td>216.54286</td>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GILD</td>\n",
       "      <td>72.56</td>\n",
       "      <td>201.55554</td>\n",
       "      <td>275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VNO</td>\n",
       "      <td>28.71</td>\n",
       "      <td>191.4</td>\n",
       "      <td>696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAP</td>\n",
       "      <td>59.62</td>\n",
       "      <td>165.6111</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IRM</td>\n",
       "      <td>98.28</td>\n",
       "      <td>148.90909</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MRK</td>\n",
       "      <td>125.77</td>\n",
       "      <td>138.20879</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LLY</td>\n",
       "      <td>857.47</td>\n",
       "      <td>126.65731</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>WELL</td>\n",
       "      <td>107.41</td>\n",
       "      <td>124.89535</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TYL</td>\n",
       "      <td>524.33</td>\n",
       "      <td>119.16591</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ARE</td>\n",
       "      <td>125.21</td>\n",
       "      <td>118.12265</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>FIS</td>\n",
       "      <td>75.25</td>\n",
       "      <td>104.513885</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DIS</td>\n",
       "      <td>95.74</td>\n",
       "      <td>104.065216</td>\n",
       "      <td>208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>LYV</td>\n",
       "      <td>95.1</td>\n",
       "      <td>87.2477</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>DD</td>\n",
       "      <td>79.64</td>\n",
       "      <td>86.565216</td>\n",
       "      <td>251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IP</td>\n",
       "      <td>45.72</td>\n",
       "      <td>86.26416</td>\n",
       "      <td>437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NOW</td>\n",
       "      <td>751.2</td>\n",
       "      <td>80.0</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>EQIX</td>\n",
       "      <td>786.82</td>\n",
       "      <td>78.83968</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ISRG</td>\n",
       "      <td>455.01</td>\n",
       "      <td>78.18041</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NWS</td>\n",
       "      <td>28.19</td>\n",
       "      <td>76.189186</td>\n",
       "      <td>709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NWSA</td>\n",
       "      <td>27.37</td>\n",
       "      <td>73.97298</td>\n",
       "      <td>730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CDNS</td>\n",
       "      <td>279.95</td>\n",
       "      <td>72.90365</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>DXCM</td>\n",
       "      <td>111.66</td>\n",
       "      <td>72.5065</td>\n",
       "      <td>179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>CLX</td>\n",
       "      <td>135.14</td>\n",
       "      <td>69.30256</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NVDA</td>\n",
       "      <td>117.93</td>\n",
       "      <td>68.96491</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AVGO</td>\n",
       "      <td>157.35</td>\n",
       "      <td>67.532196</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ABMD</td>\n",
       "      <td>381.02</td>\n",
       "      <td>65.35506</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>BSX</td>\n",
       "      <td>77.56</td>\n",
       "      <td>65.17647</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>LDOS</td>\n",
       "      <td>149.94</td>\n",
       "      <td>64.07693</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ZBRA</td>\n",
       "      <td>321.45</td>\n",
       "      <td>63.653465</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>ANSS</td>\n",
       "      <td>314.54</td>\n",
       "      <td>63.28773</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>GLW</td>\n",
       "      <td>44.31</td>\n",
       "      <td>61.541668</td>\n",
       "      <td>451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>SNPS</td>\n",
       "      <td>563.12</td>\n",
       "      <td>61.342045</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>FTI</td>\n",
       "      <td>28.15</td>\n",
       "      <td>59.893616</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>INTU</td>\n",
       "      <td>636.56</td>\n",
       "      <td>58.831795</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>CMG</td>\n",
       "      <td>53.54</td>\n",
       "      <td>56.957447</td>\n",
       "      <td>373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>EL</td>\n",
       "      <td>99.18</td>\n",
       "      <td>55.7191</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>EFX</td>\n",
       "      <td>260.3</td>\n",
       "      <td>55.501064</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>CTVA</td>\n",
       "      <td>54.78</td>\n",
       "      <td>55.333332</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>ROL</td>\n",
       "      <td>49.44</td>\n",
       "      <td>54.933334</td>\n",
       "      <td>404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>AJG</td>\n",
       "      <td>270.27</td>\n",
       "      <td>54.932922</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>BXP</td>\n",
       "      <td>66.76</td>\n",
       "      <td>54.721313</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>JNPR</td>\n",
       "      <td>37.29</td>\n",
       "      <td>54.04348</td>\n",
       "      <td>536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>ADI</td>\n",
       "      <td>231.22</td>\n",
       "      <td>54.02336</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>SPGI</td>\n",
       "      <td>479.84</td>\n",
       "      <td>53.73348</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>COO</td>\n",
       "      <td>90.75</td>\n",
       "      <td>53.38235</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>O</td>\n",
       "      <td>57.58</td>\n",
       "      <td>53.314816</td>\n",
       "      <td>347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>ADSK</td>\n",
       "      <td>242.45</td>\n",
       "      <td>52.59219</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>COST</td>\n",
       "      <td>838.13</td>\n",
       "      <td>52.09012</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>AMZN</td>\n",
       "      <td>183.13</td>\n",
       "      <td>51.29692</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Ticker   Price Price-to-Earnings Ratio Number of Shares to Buy\n",
       "0    PRGO    28.0                  2800.0                     714\n",
       "1     AMD  151.58               216.54286                     131\n",
       "2    GILD   72.56               201.55554                     275\n",
       "3     VNO   28.71                   191.4                     696\n",
       "4     AAP   59.62                165.6111                     335\n",
       "5     IRM   98.28               148.90909                     203\n",
       "6     MRK  125.77               138.20879                     159\n",
       "7     LLY  857.47               126.65731                      23\n",
       "8    WELL  107.41               124.89535                     186\n",
       "9     TYL  524.33               119.16591                      38\n",
       "10    ARE  125.21               118.12265                     159\n",
       "11    FIS   75.25              104.513885                     265\n",
       "12    DIS   95.74              104.065216                     208\n",
       "13    LYV    95.1                 87.2477                     210\n",
       "14     DD   79.64               86.565216                     251\n",
       "15     IP   45.72                86.26416                     437\n",
       "16    NOW   751.2                    80.0                      26\n",
       "17   EQIX  786.82                78.83968                      25\n",
       "18   ISRG  455.01                78.18041                      43\n",
       "19    NWS   28.19               76.189186                     709\n",
       "20   NWSA   27.37                73.97298                     730\n",
       "21   CDNS  279.95                72.90365                      71\n",
       "22   DXCM  111.66                 72.5065                     179\n",
       "23    CLX  135.14                69.30256                     147\n",
       "24   NVDA  117.93                68.96491                     169\n",
       "25   AVGO  157.35               67.532196                     127\n",
       "26   ABMD  381.02                65.35506                      52\n",
       "27    BSX   77.56                65.17647                     257\n",
       "28   LDOS  149.94                64.07693                     133\n",
       "29   ZBRA  321.45               63.653465                      62\n",
       "30   ANSS  314.54                63.28773                      63\n",
       "31    GLW   44.31               61.541668                     451\n",
       "32   SNPS  563.12               61.342045                      35\n",
       "33    FTI   28.15               59.893616                     710\n",
       "34   INTU  636.56               58.831795                      31\n",
       "35    CMG   53.54               56.957447                     373\n",
       "36     EL   99.18                 55.7191                     201\n",
       "37    EFX   260.3               55.501064                      76\n",
       "38   CTVA   54.78               55.333332                     365\n",
       "39    ROL   49.44               54.933334                     404\n",
       "40    AJG  270.27               54.932922                      74\n",
       "41    BXP   66.76               54.721313                     299\n",
       "42   JNPR   37.29                54.04348                     536\n",
       "43    ADI  231.22                54.02336                      86\n",
       "44   SPGI  479.84                53.73348                      41\n",
       "45    COO   90.75                53.38235                     220\n",
       "46      O   57.58               53.314816                     347\n",
       "47   ADSK  242.45                52.59219                      82\n",
       "48   COST  838.13                52.09012                      23\n",
       "49   AMZN  183.13                51.29692                     109"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = getAmountToInvest() / len(final_dataframe.index)\n",
    "for i in range(0 , len(final_dataframe.index)):\n",
    "    final_dataframe.loc[i ,'Number of Shares to Buy'] = math.floor(x / final_dataframe.loc[i, 'Price'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now use the global `portfolio_size` variable to calculate the number of shares that our strategy should purchase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building a Better Value Strategy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_columns = ['Ticker', \n",
    "              'Price', \n",
    "              'Price-to-Earnings Ratio',\n",
    "              'PE Percentile',\n",
    "              'Price-to-Book Ratio',\n",
    "              'PB Percentile',\n",
    "              'Price-to-Sales Ratio',\n",
    "              'PS Percentile',\n",
    "              'EV/EBITDA',\n",
    "              'EE Percentile',\n",
    "              'EV/GP',\n",
    "              'EG Percentile',\n",
    "              'Number of Shares to Buy'\n",
    "            ] "
   ]
  },
  {
   "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>Ticker</th>\n",
       "      <th>Price</th>\n",
       "      <th>Price-to-Earnings Ratio</th>\n",
       "      <th>PE Percentile</th>\n",
       "      <th>Price-to-Book Ratio</th>\n",
       "      <th>PB Percentile</th>\n",
       "      <th>Price-to-Sales Ratio</th>\n",
       "      <th>PS Percentile</th>\n",
       "      <th>EV/EBITDA</th>\n",
       "      <th>EE Percentile</th>\n",
       "      <th>EV/GP</th>\n",
       "      <th>EG Percentile</th>\n",
       "      <th>Number of Shares to Buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MMM</td>\n",
       "      <td>125.24</td>\n",
       "      <td>48.731518</td>\n",
       "      <td>0</td>\n",
       "      <td>17.570147</td>\n",
       "      <td>0</td>\n",
       "      <td>2.109557</td>\n",
       "      <td>0</td>\n",
       "      <td>9.824</td>\n",
       "      <td>0</td>\n",
       "      <td>4.950085</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AOS</td>\n",
       "      <td>82.4</td>\n",
       "      <td>21.29199</td>\n",
       "      <td>0</td>\n",
       "      <td>6.286717</td>\n",
       "      <td>0</td>\n",
       "      <td>3.058932</td>\n",
       "      <td>0</td>\n",
       "      <td>14.416</td>\n",
       "      <td>0</td>\n",
       "      <td>7.930902</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABT</td>\n",
       "      <td>111.31</td>\n",
       "      <td>35.113564</td>\n",
       "      <td>0</td>\n",
       "      <td>4.925657</td>\n",
       "      <td>0</td>\n",
       "      <td>4.755513</td>\n",
       "      <td>0</td>\n",
       "      <td>19.065</td>\n",
       "      <td>0</td>\n",
       "      <td>8.920604</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABBV</td>\n",
       "      <td>189.29</td>\n",
       "      <td>63.30769</td>\n",
       "      <td>0</td>\n",
       "      <td>41.739803</td>\n",
       "      <td>0</td>\n",
       "      <td>6.077482</td>\n",
       "      <td>0</td>\n",
       "      <td>14.88</td>\n",
       "      <td>0</td>\n",
       "      <td>10.21586</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACN</td>\n",
       "      <td>323.15</td>\n",
       "      <td>29.59249</td>\n",
       "      <td>0</td>\n",
       "      <td>7.298536</td>\n",
       "      <td>0</td>\n",
       "      <td>3.139408</td>\n",
       "      <td>0</td>\n",
       "      <td>17.939</td>\n",
       "      <td>0</td>\n",
       "      <td>9.632895</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>XYL</td>\n",
       "      <td>130.15</td>\n",
       "      <td>40.16975</td>\n",
       "      <td>0</td>\n",
       "      <td>3.057102</td>\n",
       "      <td>0</td>\n",
       "      <td>3.765188</td>\n",
       "      <td>0</td>\n",
       "      <td>20.289</td>\n",
       "      <td>0</td>\n",
       "      <td>10.646601</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>YUM</td>\n",
       "      <td>135.18</td>\n",
       "      <td>23.968084</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.416277</td>\n",
       "      <td>0</td>\n",
       "      <td>19.193</td>\n",
       "      <td>0</td>\n",
       "      <td>14.149908</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>ZBRA</td>\n",
       "      <td>322.3</td>\n",
       "      <td>72.42696</td>\n",
       "      <td>0</td>\n",
       "      <td>5.056559</td>\n",
       "      <td>0</td>\n",
       "      <td>3.815552</td>\n",
       "      <td>0</td>\n",
       "      <td>28.392</td>\n",
       "      <td>0</td>\n",
       "      <td>9.160476</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>ZBH</td>\n",
       "      <td>110.36</td>\n",
       "      <td>23.939262</td>\n",
       "      <td>0</td>\n",
       "      <td>1.801943</td>\n",
       "      <td>0</td>\n",
       "      <td>3.046554</td>\n",
       "      <td>0</td>\n",
       "      <td>11.451</td>\n",
       "      <td>0</td>\n",
       "      <td>5.287035</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>ZTS</td>\n",
       "      <td>180.59</td>\n",
       "      <td>34.862934</td>\n",
       "      <td>0</td>\n",
       "      <td>16.314934</td>\n",
       "      <td>0</td>\n",
       "      <td>9.434659</td>\n",
       "      <td>0</td>\n",
       "      <td>24.452</td>\n",
       "      <td>0</td>\n",
       "      <td>14.249613</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>503 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Ticker   Price Price-to-Earnings Ratio PE Percentile Price-to-Book Ratio  \\\n",
       "0      MMM  125.24               48.731518             0           17.570147   \n",
       "1      AOS    82.4                21.29199             0            6.286717   \n",
       "2      ABT  111.31               35.113564             0            4.925657   \n",
       "3     ABBV  189.29                63.30769             0           41.739803   \n",
       "4      ACN  323.15                29.59249             0            7.298536   \n",
       "..     ...     ...                     ...           ...                 ...   \n",
       "498    XYL  130.15                40.16975             0            3.057102   \n",
       "499    YUM  135.18               23.968084             0                   0   \n",
       "500   ZBRA   322.3                72.42696             0            5.056559   \n",
       "501    ZBH  110.36               23.939262             0            1.801943   \n",
       "502    ZTS  180.59               34.862934             0           16.314934   \n",
       "\n",
       "    PB Percentile Price-to-Sales Ratio PS Percentile EV/EBITDA EE Percentile  \\\n",
       "0               0             2.109557             0     9.824             0   \n",
       "1               0             3.058932             0    14.416             0   \n",
       "2               0             4.755513             0    19.065             0   \n",
       "3               0             6.077482             0     14.88             0   \n",
       "4               0             3.139408             0    17.939             0   \n",
       "..            ...                  ...           ...       ...           ...   \n",
       "498             0             3.765188             0    20.289             0   \n",
       "499             0             5.416277             0    19.193             0   \n",
       "500             0             3.815552             0    28.392             0   \n",
       "501             0             3.046554             0    11.451             0   \n",
       "502             0             9.434659             0    24.452             0   \n",
       "\n",
       "         EV/GP EG Percentile Number of Shares to Buy  \n",
       "0     4.950085             0                       0  \n",
       "1     7.930902             0                       0  \n",
       "2     8.920604             0                       0  \n",
       "3     10.21586             0                       0  \n",
       "4     9.632895             0                       0  \n",
       "..         ...           ...                     ...  \n",
       "498  10.646601             0                       0  \n",
       "499  14.149908             0                       0  \n",
       "500   9.160476             0                       0  \n",
       "501   5.287035             0                       0  \n",
       "502  14.249613             0                       0  \n",
       "\n",
       "[503 rows x 13 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_dataframe = pd.DataFrame(columns=my_columns)\n",
    "\n",
    "for stock_ticker in stocks_data.values():\n",
    "\n",
    "    response = stock_ticker.info\n",
    "\n",
    "    row = []\n",
    "    row.append(response['symbol'])\n",
    "\n",
    "    if 'currentPrice' in response:\n",
    "        row.append(response['currentPrice'])\n",
    "    else:\n",
    "        row.append(0)\n",
    "\n",
    "    if 'trailingPE' in response:\n",
    "        row.append(response['trailingPE'] / 100)\n",
    "    else:\n",
    "        row.append(0)\n",
    "\n",
    "    row.append(0)\n",
    "\n",
    "    if 'priceToBook' in response:\n",
    "        row.append(response['priceToBook'] / 100)\n",
    "    else:\n",
    "        row.append(0)\n",
    "\n",
    "    row.append(0)\n",
    "\n",
    "    if 'priceToSalesTrailing12Months' in response:\n",
    "        row.append(response['priceToSalesTrailing12Months'] / 100)\n",
    "    else:\n",
    "        row.append(0)\n",
    "\n",
    "    row.append(0)\n",
    "\n",
    "    if 'enterpriseToEbitda' in response:\n",
    "        row.append(response['enterpriseToEbitda'] / 100)\n",
    "    else:\n",
    "        row.append(0)\n",
    "    \n",
    "    row.append(0)\n",
    "\n",
    "    if 'enterpriseValue' in response and 'grossMargins' in response:\n",
    "        row.append((response['enterpriseValue'] / (response['grossMargins'] * response['totalRevenue'])) / 100)\n",
    "    else:\n",
    "        row.append(0)\n",
    "\n",
    "    row.append(0)\n",
    "\n",
    "    row.append(0)\n",
    "    \n",
    "    new_row = pd.Series(\n",
    "        row,\n",
    "        index=my_columns\n",
    "    )\n",
    "    \n",
    "\n",
    "    final_dataframe = pd.concat([final_dataframe, new_row.to_frame().T], ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import percentileofscore as score\n",
    "\n",
    "metrics = {\n",
    "    'Price-to-Earnings Ratio': 'PE Percentile',\n",
    "    'Price-to-Book Ratio': 'PB Percentile',\n",
    "    'Price-to-Sales Ratio': 'PS Percentile',\n",
    "    'EV/EBITDA': 'EE Percentile',\n",
    "    'EV/GP': 'EG Percentile'\n",
    "}\n",
    "\n",
    "for row in final_dataframe.index:\n",
    "    for metric in metrics.keys():\n",
    "        final_dataframe.loc[row, metrics[metric]] = score(final_dataframe[metric], int(final_dataframe.loc[row, metric])) / 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "from statistics import mean\n",
    "\n",
    "for row in final_dataframe.index:\n",
    "    scores = []\n",
    "    for metric in metrics.values():\n",
    "        scores.append(final_dataframe.loc[row, metric])\n",
    "    final_dataframe.loc[row, 'Score'] = mean(scores)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_dataframe.sort_values('Score', ascending=True, inplace=True)\n",
    "final_dataframe = final_dataframe[final_dataframe['Score'] > 0]\n",
    "final_dataframe = final_dataframe[:50]\n",
    "final_dataframe.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = getAmountToInvest() / len(final_dataframe.index)\n",
    "for i in range(0 , len(final_dataframe.index)):\n",
    "    final_dataframe.loc[i ,'Number of Shares to Buy'] = math.floor(x / final_dataframe.loc[i, 'Price'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter('Value Strategy.xlsx', engine='xlsxwriter')\n",
    "final_dataframe.to_excel(writer, sheet_name='Value Strategy.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "background_color = '#0a0a23'\n",
    "font_color = '#ffffff'\n",
    "\n",
    "string_template = writer.book.add_format(\n",
    "        {\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "dollar_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'$0.00',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "integer_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'0',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )\n",
    "\n",
    "percent_template = writer.book.add_format(\n",
    "        {\n",
    "            'num_format':'0.0%',\n",
    "            'font_color': font_color,\n",
    "            'bg_color': background_color,\n",
    "            'border': 1\n",
    "        }\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_format = {\n",
    "    'A': ['Ticker', string_template],\n",
    "    'B': ['Price', dollar_template],\n",
    "    'C': ['Price-to-Earning Ratio', percent_template],\n",
    "    'D': ['PE Percentile', percent_template],\n",
    "    'E': ['Price-to-Book Ratio', percent_template],\n",
    "    'F': ['PB Percentile', percent_template],\n",
    "    'G': ['Price-to-Sale Ratio', percent_template],\n",
    "    'H': ['PS Percentile', percent_template],\n",
    "    'I': ['EV/EBITDA', percent_template],\n",
    "    'J': ['EE Ratio', percent_template],\n",
    "    'K': ['EV/GP Ratio', percent_template],\n",
    "    'L': ['EG Percentile', percent_template],\n",
    "    'M': ['Number of Shares to Buy', integer_template],\n",
    "    'N': ['Score', integer_template]\n",
    "}\n",
    "\n",
    "for column in columns_format.keys():\n",
    "    writer.sheets[\"Value Strategy.xlsx\"].set_column(f'{column}:{column}', 25, columns_format[column][1])\n",
    "    writer.sheets[\"Value Strategy.xlsx\"].write(f'{column}1', columns_format[column][0], columns_format[column][1])\n",
    "\n",
    "writer.close()"
   ]
  }
 ],
 "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}