ml-finance-python
python scripts for finance machine learning
git clone https://9o.is/git/ml-finance-python.git
003_quantitative_value_strategy.ipynb
(173165B)
1 {
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "metadata": {},
6 "source": [
7 "# Quantitative Value Strategy\n",
8 "\"Value investing\" means investing in the stocks that are cheapest relative to common measures of business value (like earnings or assets).\n",
9 "\n",
10 "For this project, we're going to build an investing strategy that selects the 50 stocks with the best value metrics. From there, we will calculate recommended trades for an equal-weight portfolio of these 50 stocks.\n",
11 "\n",
12 "## Library Imports\n",
13 "The first thing we need to do is import the open-source software libraries that we'll be using in this tutorial."
14 ]
15 },
16 {
17 "cell_type": "code",
18 "execution_count": 1,
19 "metadata": {},
20 "outputs": [],
21 "source": [
22 "import numpy as np #The Numpy numerical computing library\n",
23 "import pandas as pd #The Pandas data science library\n",
24 "import requests #The requests library for HTTP requests in Python\n",
25 "import xlsxwriter #The XlsxWriter libarary for \n",
26 "import math #The Python math module\n",
27 "from scipy import stats #The SciPy stats module"
28 ]
29 },
30 {
31 "cell_type": "markdown",
32 "metadata": {},
33 "source": [
34 "## Importing Our List of Stocks & API Token\n",
35 "As before, we'll need to import our list of stocks and our API token before proceeding. Make sure the .csv file is still in your working directory and import it with the following command:"
36 ]
37 },
38 {
39 "cell_type": "code",
40 "execution_count": 2,
41 "metadata": {},
42 "outputs": [],
43 "source": [
44 "stocks = pd.read_csv('sp_500_stocks.csv')\n",
45 "from secrets import IEX_CLOUD_API_TOKEN"
46 ]
47 },
48 {
49 "cell_type": "markdown",
50 "metadata": {},
51 "source": [
52 "## Making Our First API Call\n",
53 "It's now time to make the first version of our value screener!\n",
54 "\n",
55 "We'll start by building a simple value screener that ranks securities based on a single metric (the price-to-earnings ratio)."
56 ]
57 },
58 {
59 "cell_type": "code",
60 "execution_count": 3,
61 "metadata": {},
62 "outputs": [
63 {
64 "data": {
65 "text/plain": [
66 "{'symbol': 'AAPL',\n",
67 " 'companyName': 'Apple, Inc.',\n",
68 " 'primaryExchange': 'SDAANQ',\n",
69 " 'calculationPrice': 'iexlasttrade',\n",
70 " 'open': None,\n",
71 " 'openTime': None,\n",
72 " 'openSource': 'aiofflic',\n",
73 " 'close': None,\n",
74 " 'closeTime': None,\n",
75 " 'closeSource': 'oaliiffc',\n",
76 " 'high': 480.07,\n",
77 " 'highTime': 1652873090519,\n",
78 " 'highSource': 'upena5eey1 t d remicild',\n",
79 " 'low': 478.47,\n",
80 " 'lowTime': 1619803709637,\n",
81 " 'lowSource': 'eldp imuae 1 ycd5etenri',\n",
82 " 'latestPrice': 469.22,\n",
83 " 'latestSource': 'IEX Last Trade',\n",
84 " 'latestTime': 'August 13, 2020',\n",
85 " 'latestUpdate': 1603191198139,\n",
86 " 'latestVolume': 52742837,\n",
87 " 'iexRealtimePrice': 475.89,\n",
88 " 'iexRealtimeSize': 102,\n",
89 " 'iexLastUpdated': 1600007375509,\n",
90 " 'delayedPrice': None,\n",
91 " 'delayedPriceTime': None,\n",
92 " 'oddLotDelayedPrice': None,\n",
93 " 'oddLotDelayedPriceTime': None,\n",
94 " 'extendedPrice': None,\n",
95 " 'extendedChange': None,\n",
96 " 'extendedChangePercent': None,\n",
97 " 'extendedPriceTime': None,\n",
98 " 'previousClose': 469.6,\n",
99 " 'previousVolume': 43414053,\n",
100 " 'change': 7.8,\n",
101 " 'changePercent': 0.01767,\n",
102 " 'volume': 50996231,\n",
103 " 'iexMarketPercent': 0.007247011401180851,\n",
104 " 'iexVolume': 380031,\n",
105 " 'avgTotalVolume': 39506212,\n",
106 " 'iexBidPrice': 0,\n",
107 " 'iexBidSize': 0,\n",
108 " 'iexAskPrice': 0,\n",
109 " 'iexAskSize': 0,\n",
110 " 'iexOpen': None,\n",
111 " 'iexOpenTime': None,\n",
112 " 'iexClose': 469.68,\n",
113 " 'iexCloseTime': 1600343577441,\n",
114 " 'marketCap': 1997216376455,\n",
115 " 'peRatio': 34.91,\n",
116 " 'week52High': 471.42,\n",
117 " 'week52Low': 200.32,\n",
118 " 'ytdChange': 0.5234,\n",
119 " 'lastTradeTime': 1645587579289,\n",
120 " 'isUSMarketOpen': False}"
121 ]
122 },
123 "execution_count": 3,
124 "metadata": {},
125 "output_type": "execute_result"
126 }
127 ],
128 "source": [
129 "symbol = 'AAPL'\n",
130 "api_url = f'https://sandbox.iexapis.com/stable/stock/{symbol}/quote?token={IEX_CLOUD_API_TOKEN}'\n",
131 "data = requests.get(api_url).json()\n",
132 "data"
133 ]
134 },
135 {
136 "cell_type": "markdown",
137 "metadata": {},
138 "source": [
139 "## Parsing Our API Call\n",
140 "This API call has the metric we need - the price-to-earnings ratio.\n",
141 "\n",
142 "Here is an example of how to parse the metric from our API call:"
143 ]
144 },
145 {
146 "cell_type": "code",
147 "execution_count": 4,
148 "metadata": {},
149 "outputs": [
150 {
151 "data": {
152 "text/plain": [
153 "34.91"
154 ]
155 },
156 "execution_count": 4,
157 "metadata": {},
158 "output_type": "execute_result"
159 }
160 ],
161 "source": [
162 "pe_ratio = data['peRatio']\n",
163 "pe_ratio"
164 ]
165 },
166 {
167 "cell_type": "markdown",
168 "metadata": {},
169 "source": [
170 "## Executing A Batch API Call & Building Our DataFrame\n",
171 "\n",
172 "Just like in our first project, it's now time to execute several batch API calls and add the information we need to our DataFrame.\n",
173 "\n",
174 "We'll start by running the following code cell, which contains some code we already built last time that we can re-use for this project. More specifically, it contains a function called chunks that we can use to divide our list of securities into groups of 100."
175 ]
176 },
177 {
178 "cell_type": "code",
179 "execution_count": 5,
180 "metadata": {},
181 "outputs": [],
182 "source": [
183 "# Function sourced from \n",
184 "# https://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks\n",
185 "def chunks(lst, n):\n",
186 " \"\"\"Yield successive n-sized chunks from lst.\"\"\"\n",
187 " for i in range(0, len(lst), n):\n",
188 " yield lst[i:i + n] \n",
189 " \n",
190 "symbol_groups = list(chunks(stocks['Ticker'], 100))\n",
191 "symbol_strings = []\n",
192 "for i in range(0, len(symbol_groups)):\n",
193 " symbol_strings.append(','.join(symbol_groups[i]))\n",
194 "# print(symbol_strings[i])\n",
195 "\n",
196 "my_columns = ['Ticker', 'Price', 'Price-to-Earnings Ratio', 'Number of Shares to Buy']"
197 ]
198 },
199 {
200 "cell_type": "markdown",
201 "metadata": {},
202 "source": [
203 "Now we need to create a blank DataFrame and add our data to the data frame one-by-one."
204 ]
205 },
206 {
207 "cell_type": "code",
208 "execution_count": 6,
209 "metadata": {},
210 "outputs": [
211 {
212 "data": {
213 "text/html": [
214 "<div>\n",
215 "<style scoped>\n",
216 " .dataframe tbody tr th:only-of-type {\n",
217 " vertical-align: middle;\n",
218 " }\n",
219 "\n",
220 " .dataframe tbody tr th {\n",
221 " vertical-align: top;\n",
222 " }\n",
223 "\n",
224 " .dataframe thead th {\n",
225 " text-align: right;\n",
226 " }\n",
227 "</style>\n",
228 "<table border=\"1\" class=\"dataframe\">\n",
229 " <thead>\n",
230 " <tr style=\"text-align: right;\">\n",
231 " <th></th>\n",
232 " <th>Ticker</th>\n",
233 " <th>Price</th>\n",
234 " <th>Price-to-Earnings Ratio</th>\n",
235 " <th>Number of Shares to Buy</th>\n",
236 " </tr>\n",
237 " </thead>\n",
238 " <tbody>\n",
239 " <tr>\n",
240 " <th>0</th>\n",
241 " <td>A</td>\n",
242 " <td>99.160</td>\n",
243 " <td>46.39</td>\n",
244 " <td>N/A</td>\n",
245 " </tr>\n",
246 " <tr>\n",
247 " <th>1</th>\n",
248 " <td>AAL</td>\n",
249 " <td>13.430</td>\n",
250 " <td>-1.65</td>\n",
251 " <td>N/A</td>\n",
252 " </tr>\n",
253 " <tr>\n",
254 " <th>2</th>\n",
255 " <td>AAP</td>\n",
256 " <td>156.890</td>\n",
257 " <td>28.9</td>\n",
258 " <td>N/A</td>\n",
259 " </tr>\n",
260 " <tr>\n",
261 " <th>3</th>\n",
262 " <td>AAPL</td>\n",
263 " <td>475.040</td>\n",
264 " <td>34.8</td>\n",
265 " <td>N/A</td>\n",
266 " </tr>\n",
267 " <tr>\n",
268 " <th>4</th>\n",
269 " <td>ABBV</td>\n",
270 " <td>95.770</td>\n",
271 " <td>20.14</td>\n",
272 " <td>N/A</td>\n",
273 " </tr>\n",
274 " <tr>\n",
275 " <th>...</th>\n",
276 " <td>...</td>\n",
277 " <td>...</td>\n",
278 " <td>...</td>\n",
279 " <td>...</td>\n",
280 " </tr>\n",
281 " <tr>\n",
282 " <th>500</th>\n",
283 " <td>YUM</td>\n",
284 " <td>93.960</td>\n",
285 " <td>28.5</td>\n",
286 " <td>N/A</td>\n",
287 " </tr>\n",
288 " <tr>\n",
289 " <th>501</th>\n",
290 " <td>ZBH</td>\n",
291 " <td>143.010</td>\n",
292 " <td>723.05</td>\n",
293 " <td>N/A</td>\n",
294 " </tr>\n",
295 " <tr>\n",
296 " <th>502</th>\n",
297 " <td>ZBRA</td>\n",
298 " <td>299.348</td>\n",
299 " <td>31.74</td>\n",
300 " <td>N/A</td>\n",
301 " </tr>\n",
302 " <tr>\n",
303 " <th>503</th>\n",
304 " <td>ZION</td>\n",
305 " <td>34.700</td>\n",
306 " <td>13.44</td>\n",
307 " <td>N/A</td>\n",
308 " </tr>\n",
309 " <tr>\n",
310 " <th>504</th>\n",
311 " <td>ZTS</td>\n",
312 " <td>163.740</td>\n",
313 " <td>47.5</td>\n",
314 " <td>N/A</td>\n",
315 " </tr>\n",
316 " </tbody>\n",
317 "</table>\n",
318 "<p>505 rows × 4 columns</p>\n",
319 "</div>"
320 ],
321 "text/plain": [
322 " Ticker Price Price-to-Earnings Ratio Number of Shares to Buy\n",
323 "0 A 99.160 46.39 N/A\n",
324 "1 AAL 13.430 -1.65 N/A\n",
325 "2 AAP 156.890 28.9 N/A\n",
326 "3 AAPL 475.040 34.8 N/A\n",
327 "4 ABBV 95.770 20.14 N/A\n",
328 ".. ... ... ... ...\n",
329 "500 YUM 93.960 28.5 N/A\n",
330 "501 ZBH 143.010 723.05 N/A\n",
331 "502 ZBRA 299.348 31.74 N/A\n",
332 "503 ZION 34.700 13.44 N/A\n",
333 "504 ZTS 163.740 47.5 N/A\n",
334 "\n",
335 "[505 rows x 4 columns]"
336 ]
337 },
338 "execution_count": 6,
339 "metadata": {},
340 "output_type": "execute_result"
341 }
342 ],
343 "source": [
344 "final_dataframe = pd.DataFrame(columns = my_columns)\n",
345 "\n",
346 "for symbol_string in symbol_strings:\n",
347 "# print(symbol_strings)\n",
348 " batch_api_call_url = f'https://sandbox.iexapis.com/stable/stock/market/batch/?types=quote&symbols={symbol_string}&token={IEX_CLOUD_API_TOKEN}'\n",
349 " data = requests.get(batch_api_call_url).json()\n",
350 " for symbol in symbol_string.split(','):\n",
351 " final_dataframe = final_dataframe.append(\n",
352 " pd.Series([symbol, \n",
353 " data[symbol]['quote']['latestPrice'],\n",
354 " data[symbol]['quote']['peRatio'],\n",
355 " 'N/A'\n",
356 " ], \n",
357 " index = my_columns), \n",
358 " ignore_index = True)\n",
359 " \n",
360 " \n",
361 "final_dataframe"
362 ]
363 },
364 {
365 "cell_type": "markdown",
366 "metadata": {},
367 "source": [
368 "## Removing Glamour Stocks\n",
369 "\n",
370 "The opposite of a \"value stock\" is a \"glamour stock\". \n",
371 "\n",
372 "Since the goal of this strategy is to identify the 50 best value stocks from our universe, our next step is to remove glamour stocks from the DataFrame.\n",
373 "\n",
374 "We'll sort the DataFrame by the stocks' price-to-earnings ratio, and drop all stocks outside the top 50."
375 ]
376 },
377 {
378 "cell_type": "code",
379 "execution_count": 7,
380 "metadata": {},
381 "outputs": [
382 {
383 "data": {
384 "text/html": [
385 "<div>\n",
386 "<style scoped>\n",
387 " .dataframe tbody tr th:only-of-type {\n",
388 " vertical-align: middle;\n",
389 " }\n",
390 "\n",
391 " .dataframe tbody tr th {\n",
392 " vertical-align: top;\n",
393 " }\n",
394 "\n",
395 " .dataframe thead th {\n",
396 " text-align: right;\n",
397 " }\n",
398 "</style>\n",
399 "<table border=\"1\" class=\"dataframe\">\n",
400 " <thead>\n",
401 " <tr style=\"text-align: right;\">\n",
402 " <th></th>\n",
403 " <th>Ticker</th>\n",
404 " <th>Price</th>\n",
405 " <th>Price-to-Earnings Ratio</th>\n",
406 " <th>Number of Shares to Buy</th>\n",
407 " </tr>\n",
408 " </thead>\n",
409 " <tbody>\n",
410 " <tr>\n",
411 " <th>0</th>\n",
412 " <td>SBAC</td>\n",
413 " <td>317.680</td>\n",
414 " <td>-2261.88</td>\n",
415 " <td>N/A</td>\n",
416 " </tr>\n",
417 " <tr>\n",
418 " <th>1</th>\n",
419 " <td>CRM</td>\n",
420 " <td>202.060</td>\n",
421 " <td>-1165.07</td>\n",
422 " <td>N/A</td>\n",
423 " </tr>\n",
424 " <tr>\n",
425 " <th>2</th>\n",
426 " <td>BMY</td>\n",
427 " <td>65.210</td>\n",
428 " <td>-639.08</td>\n",
429 " <td>N/A</td>\n",
430 " </tr>\n",
431 " <tr>\n",
432 " <th>3</th>\n",
433 " <td>TEL</td>\n",
434 " <td>99.630</td>\n",
435 " <td>-328.9</td>\n",
436 " <td>N/A</td>\n",
437 " </tr>\n",
438 " <tr>\n",
439 " <th>4</th>\n",
440 " <td>GILD</td>\n",
441 " <td>70.140</td>\n",
442 " <td>-318.24</td>\n",
443 " <td>N/A</td>\n",
444 " </tr>\n",
445 " <tr>\n",
446 " <th>5</th>\n",
447 " <td>HPE</td>\n",
448 " <td>9.720</td>\n",
449 " <td>-299.36</td>\n",
450 " <td>N/A</td>\n",
451 " </tr>\n",
452 " <tr>\n",
453 " <th>6</th>\n",
454 " <td>DRI</td>\n",
455 " <td>88.750</td>\n",
456 " <td>-247.4</td>\n",
457 " <td>N/A</td>\n",
458 " </tr>\n",
459 " <tr>\n",
460 " <th>7</th>\n",
461 " <td>KHC</td>\n",
462 " <td>36.964</td>\n",
463 " <td>-237.94</td>\n",
464 " <td>N/A</td>\n",
465 " </tr>\n",
466 " <tr>\n",
467 " <th>8</th>\n",
468 " <td>DIS</td>\n",
469 " <td>136.510</td>\n",
470 " <td>-221.5</td>\n",
471 " <td>N/A</td>\n",
472 " </tr>\n",
473 " <tr>\n",
474 " <th>9</th>\n",
475 " <td>XRAY</td>\n",
476 " <td>45.210</td>\n",
477 " <td>-203.96</td>\n",
478 " <td>N/A</td>\n",
479 " </tr>\n",
480 " <tr>\n",
481 " <th>10</th>\n",
482 " <td>COF</td>\n",
483 " <td>70.580</td>\n",
484 " <td>-202.95</td>\n",
485 " <td>N/A</td>\n",
486 " </tr>\n",
487 " <tr>\n",
488 " <th>11</th>\n",
489 " <td>IR</td>\n",
490 " <td>35.690</td>\n",
491 " <td>-156.36</td>\n",
492 " <td>N/A</td>\n",
493 " </tr>\n",
494 " <tr>\n",
495 " <th>12</th>\n",
496 " <td>PRU</td>\n",
497 " <td>72.280</td>\n",
498 " <td>-130</td>\n",
499 " <td>N/A</td>\n",
500 " </tr>\n",
501 " <tr>\n",
502 " <th>13</th>\n",
503 " <td>INCY</td>\n",
504 " <td>96.230</td>\n",
505 " <td>-108.7</td>\n",
506 " <td>N/A</td>\n",
507 " </tr>\n",
508 " <tr>\n",
509 " <th>14</th>\n",
510 " <td>MO</td>\n",
511 " <td>44.040</td>\n",
512 " <td>-87.45</td>\n",
513 " <td>N/A</td>\n",
514 " </tr>\n",
515 " <tr>\n",
516 " <th>15</th>\n",
517 " <td>NI</td>\n",
518 " <td>24.520</td>\n",
519 " <td>-80.42</td>\n",
520 " <td>N/A</td>\n",
521 " </tr>\n",
522 " <tr>\n",
523 " <th>16</th>\n",
524 " <td>ECL</td>\n",
525 " <td>203.920</td>\n",
526 " <td>-68.76</td>\n",
527 " <td>N/A</td>\n",
528 " </tr>\n",
529 " <tr>\n",
530 " <th>17</th>\n",
531 " <td>TAP</td>\n",
532 " <td>37.930</td>\n",
533 " <td>-53.25</td>\n",
534 " <td>N/A</td>\n",
535 " </tr>\n",
536 " <tr>\n",
537 " <th>18</th>\n",
538 " <td>WDC</td>\n",
539 " <td>35.834</td>\n",
540 " <td>-43.43</td>\n",
541 " <td>N/A</td>\n",
542 " </tr>\n",
543 " <tr>\n",
544 " <th>19</th>\n",
545 " <td>BA</td>\n",
546 " <td>182.960</td>\n",
547 " <td>-35.3</td>\n",
548 " <td>N/A</td>\n",
549 " </tr>\n",
550 " <tr>\n",
551 " <th>20</th>\n",
552 " <td>FCX</td>\n",
553 " <td>14.250</td>\n",
554 " <td>-31.58</td>\n",
555 " <td>N/A</td>\n",
556 " </tr>\n",
557 " <tr>\n",
558 " <th>21</th>\n",
559 " <td>HFC</td>\n",
560 " <td>25.740</td>\n",
561 " <td>-25.75</td>\n",
562 " <td>N/A</td>\n",
563 " </tr>\n",
564 " <tr>\n",
565 " <th>22</th>\n",
566 " <td>TWTR</td>\n",
567 " <td>37.930</td>\n",
568 " <td>-24.94</td>\n",
569 " <td>N/A</td>\n",
570 " </tr>\n",
571 " <tr>\n",
572 " <th>23</th>\n",
573 " <td>PSX</td>\n",
574 " <td>64.700</td>\n",
575 " <td>-23.26</td>\n",
576 " <td>N/A</td>\n",
577 " </tr>\n",
578 " <tr>\n",
579 " <th>24</th>\n",
580 " <td>TPR</td>\n",
581 " <td>15.870</td>\n",
582 " <td>-20.07</td>\n",
583 " <td>N/A</td>\n",
584 " </tr>\n",
585 " <tr>\n",
586 " <th>25</th>\n",
587 " <td>CVX</td>\n",
588 " <td>91.500</td>\n",
589 " <td>-19.48</td>\n",
590 " <td>N/A</td>\n",
591 " </tr>\n",
592 " <tr>\n",
593 " <th>26</th>\n",
594 " <td>DD</td>\n",
595 " <td>58.100</td>\n",
596 " <td>-17.22</td>\n",
597 " <td>N/A</td>\n",
598 " </tr>\n",
599 " <tr>\n",
600 " <th>27</th>\n",
601 " <td>DOW</td>\n",
602 " <td>44.960</td>\n",
603 " <td>-16.75</td>\n",
604 " <td>N/A</td>\n",
605 " </tr>\n",
606 " <tr>\n",
607 " <th>28</th>\n",
608 " <td>LYV</td>\n",
609 " <td>52.300</td>\n",
610 " <td>-14.36</td>\n",
611 " <td>N/A</td>\n",
612 " </tr>\n",
613 " <tr>\n",
614 " <th>29</th>\n",
615 " <td>CNP</td>\n",
616 " <td>20.910</td>\n",
617 " <td>-13.61</td>\n",
618 " <td>N/A</td>\n",
619 " </tr>\n",
620 " <tr>\n",
621 " <th>30</th>\n",
622 " <td>F</td>\n",
623 " <td>7.200</td>\n",
624 " <td>-13.54</td>\n",
625 " <td>N/A</td>\n",
626 " </tr>\n",
627 " <tr>\n",
628 " <th>31</th>\n",
629 " <td>LB</td>\n",
630 " <td>26.590</td>\n",
631 " <td>-10.95</td>\n",
632 " <td>N/A</td>\n",
633 " </tr>\n",
634 " <tr>\n",
635 " <th>32</th>\n",
636 " <td>L</td>\n",
637 " <td>38.820</td>\n",
638 " <td>-9.34</td>\n",
639 " <td>N/A</td>\n",
640 " </tr>\n",
641 " <tr>\n",
642 " <th>33</th>\n",
643 " <td>NLSN</td>\n",
644 " <td>15.940</td>\n",
645 " <td>-9.11</td>\n",
646 " <td>N/A</td>\n",
647 " </tr>\n",
648 " <tr>\n",
649 " <th>34</th>\n",
650 " <td>WYNN</td>\n",
651 " <td>88.120</td>\n",
652 " <td>-8.39</td>\n",
653 " <td>N/A</td>\n",
654 " </tr>\n",
655 " <tr>\n",
656 " <th>35</th>\n",
657 " <td>EXPE</td>\n",
658 " <td>91.140</td>\n",
659 " <td>-8.08</td>\n",
660 " <td>N/A</td>\n",
661 " </tr>\n",
662 " <tr>\n",
663 " <th>36</th>\n",
664 " <td>MRO</td>\n",
665 " <td>6.140</td>\n",
666 " <td>-7.42</td>\n",
667 " <td>N/A</td>\n",
668 " </tr>\n",
669 " <tr>\n",
670 " <th>37</th>\n",
671 " <td>UAA</td>\n",
672 " <td>11.280</td>\n",
673 " <td>-7.41</td>\n",
674 " <td>N/A</td>\n",
675 " </tr>\n",
676 " <tr>\n",
677 " <th>38</th>\n",
678 " <td>NWL</td>\n",
679 " <td>17.333</td>\n",
680 " <td>-7.34</td>\n",
681 " <td>N/A</td>\n",
682 " </tr>\n",
683 " <tr>\n",
684 " <th>39</th>\n",
685 " <td>NWS</td>\n",
686 " <td>16.112</td>\n",
687 " <td>-7.25</td>\n",
688 " <td>N/A</td>\n",
689 " </tr>\n",
690 " <tr>\n",
691 " <th>40</th>\n",
692 " <td>NWSA</td>\n",
693 " <td>15.900</td>\n",
694 " <td>-7.16</td>\n",
695 " <td>N/A</td>\n",
696 " </tr>\n",
697 " <tr>\n",
698 " <th>41</th>\n",
699 " <td>GPS</td>\n",
700 " <td>15.280</td>\n",
701 " <td>-6.9</td>\n",
702 " <td>N/A</td>\n",
703 " </tr>\n",
704 " <tr>\n",
705 " <th>42</th>\n",
706 " <td>UA</td>\n",
707 " <td>9.900</td>\n",
708 " <td>-6.66</td>\n",
709 " <td>N/A</td>\n",
710 " </tr>\n",
711 " <tr>\n",
712 " <th>43</th>\n",
713 " <td>RCL</td>\n",
714 " <td>60.600</td>\n",
715 " <td>-6.59</td>\n",
716 " <td>N/A</td>\n",
717 " </tr>\n",
718 " <tr>\n",
719 " <th>44</th>\n",
720 " <td>MOS</td>\n",
721 " <td>18.140</td>\n",
722 " <td>-6.08</td>\n",
723 " <td>N/A</td>\n",
724 " </tr>\n",
725 " <tr>\n",
726 " <th>45</th>\n",
727 " <td>UAL</td>\n",
728 " <td>36.000</td>\n",
729 " <td>-6.06</td>\n",
730 " <td>N/A</td>\n",
731 " </tr>\n",
732 " <tr>\n",
733 " <th>46</th>\n",
734 " <td>AIG</td>\n",
735 " <td>30.890</td>\n",
736 " <td>-5.89</td>\n",
737 " <td>N/A</td>\n",
738 " </tr>\n",
739 " <tr>\n",
740 " <th>47</th>\n",
741 " <td>PVH</td>\n",
742 " <td>55.360</td>\n",
743 " <td>-5.08</td>\n",
744 " <td>N/A</td>\n",
745 " </tr>\n",
746 " <tr>\n",
747 " <th>48</th>\n",
748 " <td>HES</td>\n",
749 " <td>55.160</td>\n",
750 " <td>-5.03</td>\n",
751 " <td>N/A</td>\n",
752 " </tr>\n",
753 " <tr>\n",
754 " <th>49</th>\n",
755 " <td>DAL</td>\n",
756 " <td>29.600</td>\n",
757 " <td>-5</td>\n",
758 " <td>N/A</td>\n",
759 " </tr>\n",
760 " <tr>\n",
761 " <th>50</th>\n",
762 " <td>CAH</td>\n",
763 " <td>54.300</td>\n",
764 " <td>-4.27</td>\n",
765 " <td>N/A</td>\n",
766 " </tr>\n",
767 " </tbody>\n",
768 "</table>\n",
769 "</div>"
770 ],
771 "text/plain": [
772 " Ticker Price Price-to-Earnings Ratio Number of Shares to Buy\n",
773 "0 SBAC 317.680 -2261.88 N/A\n",
774 "1 CRM 202.060 -1165.07 N/A\n",
775 "2 BMY 65.210 -639.08 N/A\n",
776 "3 TEL 99.630 -328.9 N/A\n",
777 "4 GILD 70.140 -318.24 N/A\n",
778 "5 HPE 9.720 -299.36 N/A\n",
779 "6 DRI 88.750 -247.4 N/A\n",
780 "7 KHC 36.964 -237.94 N/A\n",
781 "8 DIS 136.510 -221.5 N/A\n",
782 "9 XRAY 45.210 -203.96 N/A\n",
783 "10 COF 70.580 -202.95 N/A\n",
784 "11 IR 35.690 -156.36 N/A\n",
785 "12 PRU 72.280 -130 N/A\n",
786 "13 INCY 96.230 -108.7 N/A\n",
787 "14 MO 44.040 -87.45 N/A\n",
788 "15 NI 24.520 -80.42 N/A\n",
789 "16 ECL 203.920 -68.76 N/A\n",
790 "17 TAP 37.930 -53.25 N/A\n",
791 "18 WDC 35.834 -43.43 N/A\n",
792 "19 BA 182.960 -35.3 N/A\n",
793 "20 FCX 14.250 -31.58 N/A\n",
794 "21 HFC 25.740 -25.75 N/A\n",
795 "22 TWTR 37.930 -24.94 N/A\n",
796 "23 PSX 64.700 -23.26 N/A\n",
797 "24 TPR 15.870 -20.07 N/A\n",
798 "25 CVX 91.500 -19.48 N/A\n",
799 "26 DD 58.100 -17.22 N/A\n",
800 "27 DOW 44.960 -16.75 N/A\n",
801 "28 LYV 52.300 -14.36 N/A\n",
802 "29 CNP 20.910 -13.61 N/A\n",
803 "30 F 7.200 -13.54 N/A\n",
804 "31 LB 26.590 -10.95 N/A\n",
805 "32 L 38.820 -9.34 N/A\n",
806 "33 NLSN 15.940 -9.11 N/A\n",
807 "34 WYNN 88.120 -8.39 N/A\n",
808 "35 EXPE 91.140 -8.08 N/A\n",
809 "36 MRO 6.140 -7.42 N/A\n",
810 "37 UAA 11.280 -7.41 N/A\n",
811 "38 NWL 17.333 -7.34 N/A\n",
812 "39 NWS 16.112 -7.25 N/A\n",
813 "40 NWSA 15.900 -7.16 N/A\n",
814 "41 GPS 15.280 -6.9 N/A\n",
815 "42 UA 9.900 -6.66 N/A\n",
816 "43 RCL 60.600 -6.59 N/A\n",
817 "44 MOS 18.140 -6.08 N/A\n",
818 "45 UAL 36.000 -6.06 N/A\n",
819 "46 AIG 30.890 -5.89 N/A\n",
820 "47 PVH 55.360 -5.08 N/A\n",
821 "48 HES 55.160 -5.03 N/A\n",
822 "49 DAL 29.600 -5 N/A\n",
823 "50 CAH 54.300 -4.27 N/A"
824 ]
825 },
826 "execution_count": 7,
827 "metadata": {},
828 "output_type": "execute_result"
829 }
830 ],
831 "source": [
832 "final_dataframe.sort_values('Price-to-Earnings Ratio', inplace = True)\n",
833 "final_dataframe = final_dataframe[final_dataframe['Price-to-Earnings Ratio'] > 0]\n",
834 "final_dataframe = final_dataframe[:50]\n",
835 "final_dataframe.reset_index(inplace = True)\n",
836 "final_dataframe.drop('index', axis=1, inplace = True)"
837 ]
838 },
839 {
840 "cell_type": "markdown",
841 "metadata": {},
842 "source": [
843 "## Calculating the Number of Shares to Buy\n",
844 "We now need to calculate the number of shares we need to buy. \n",
845 "\n",
846 "To do this, we will use the `portfolio_input` function that we created in our momentum project.\n",
847 "\n",
848 "I have included this function below."
849 ]
850 },
851 {
852 "cell_type": "code",
853 "execution_count": 8,
854 "metadata": {},
855 "outputs": [],
856 "source": [
857 "def portfolio_input():\n",
858 " global portfolio_size\n",
859 " portfolio_size = input(\"Enter the value of your portfolio:\")\n",
860 "\n",
861 " try:\n",
862 " val = float(portfolio_size)\n",
863 " except ValueError:\n",
864 " print(\"That's not a number! \\n Try again:\")\n",
865 " portfolio_size = input(\"Enter the value of your portfolio:\")"
866 ]
867 },
868 {
869 "cell_type": "markdown",
870 "metadata": {},
871 "source": [
872 "Use the `portfolio_input` function to accept a `portfolio_size` variable from the user of this script."
873 ]
874 },
875 {
876 "cell_type": "code",
877 "execution_count": 9,
878 "metadata": {},
879 "outputs": [
880 {
881 "name": "stdout",
882 "output_type": "stream",
883 "text": [
884 "Enter the value of your portfolio:1000000\n"
885 ]
886 }
887 ],
888 "source": [
889 "portfolio_input()"
890 ]
891 },
892 {
893 "cell_type": "markdown",
894 "metadata": {},
895 "source": [
896 "You can now use the global `portfolio_size` variable to calculate the number of shares that our strategy should purchase."
897 ]
898 },
899 {
900 "cell_type": "code",
901 "execution_count": 10,
902 "metadata": {},
903 "outputs": [
904 {
905 "name": "stderr",
906 "output_type": "stream",
907 "text": [
908 "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/core/indexing.py:494: SettingWithCopyWarning: \n",
909 "A value is trying to be set on a copy of a slice from a DataFrame.\n",
910 "Try using .loc[row_indexer,col_indexer] = value instead\n",
911 "\n",
912 "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
913 " self.obj[item] = s\n"
914 ]
915 },
916 {
917 "data": {
918 "text/html": [
919 "<div>\n",
920 "<style scoped>\n",
921 " .dataframe tbody tr th:only-of-type {\n",
922 " vertical-align: middle;\n",
923 " }\n",
924 "\n",
925 " .dataframe tbody tr th {\n",
926 " vertical-align: top;\n",
927 " }\n",
928 "\n",
929 " .dataframe thead th {\n",
930 " text-align: right;\n",
931 " }\n",
932 "</style>\n",
933 "<table border=\"1\" class=\"dataframe\">\n",
934 " <thead>\n",
935 " <tr style=\"text-align: right;\">\n",
936 " <th></th>\n",
937 " <th>Ticker</th>\n",
938 " <th>Price</th>\n",
939 " <th>Price-to-Earnings Ratio</th>\n",
940 " <th>Number of Shares to Buy</th>\n",
941 " </tr>\n",
942 " </thead>\n",
943 " <tbody>\n",
944 " <tr>\n",
945 " <th>0</th>\n",
946 " <td>SBAC</td>\n",
947 " <td>317.680</td>\n",
948 " <td>-2261.88</td>\n",
949 " <td>61</td>\n",
950 " </tr>\n",
951 " <tr>\n",
952 " <th>1</th>\n",
953 " <td>CRM</td>\n",
954 " <td>202.060</td>\n",
955 " <td>-1165.07</td>\n",
956 " <td>97</td>\n",
957 " </tr>\n",
958 " <tr>\n",
959 " <th>2</th>\n",
960 " <td>BMY</td>\n",
961 " <td>65.210</td>\n",
962 " <td>-639.08</td>\n",
963 " <td>300</td>\n",
964 " </tr>\n",
965 " <tr>\n",
966 " <th>3</th>\n",
967 " <td>TEL</td>\n",
968 " <td>99.630</td>\n",
969 " <td>-328.9</td>\n",
970 " <td>196</td>\n",
971 " </tr>\n",
972 " <tr>\n",
973 " <th>4</th>\n",
974 " <td>GILD</td>\n",
975 " <td>70.140</td>\n",
976 " <td>-318.24</td>\n",
977 " <td>279</td>\n",
978 " </tr>\n",
979 " <tr>\n",
980 " <th>5</th>\n",
981 " <td>HPE</td>\n",
982 " <td>9.720</td>\n",
983 " <td>-299.36</td>\n",
984 " <td>2017</td>\n",
985 " </tr>\n",
986 " <tr>\n",
987 " <th>6</th>\n",
988 " <td>DRI</td>\n",
989 " <td>88.750</td>\n",
990 " <td>-247.4</td>\n",
991 " <td>220</td>\n",
992 " </tr>\n",
993 " <tr>\n",
994 " <th>7</th>\n",
995 " <td>KHC</td>\n",
996 " <td>36.964</td>\n",
997 " <td>-237.94</td>\n",
998 " <td>530</td>\n",
999 " </tr>\n",
1000 " <tr>\n",
1001 " <th>8</th>\n",
1002 " <td>DIS</td>\n",
1003 " <td>136.510</td>\n",
1004 " <td>-221.5</td>\n",
1005 " <td>143</td>\n",
1006 " </tr>\n",
1007 " <tr>\n",
1008 " <th>9</th>\n",
1009 " <td>XRAY</td>\n",
1010 " <td>45.210</td>\n",
1011 " <td>-203.96</td>\n",
1012 " <td>433</td>\n",
1013 " </tr>\n",
1014 " <tr>\n",
1015 " <th>10</th>\n",
1016 " <td>COF</td>\n",
1017 " <td>70.580</td>\n",
1018 " <td>-202.95</td>\n",
1019 " <td>277</td>\n",
1020 " </tr>\n",
1021 " <tr>\n",
1022 " <th>11</th>\n",
1023 " <td>IR</td>\n",
1024 " <td>35.690</td>\n",
1025 " <td>-156.36</td>\n",
1026 " <td>549</td>\n",
1027 " </tr>\n",
1028 " <tr>\n",
1029 " <th>12</th>\n",
1030 " <td>PRU</td>\n",
1031 " <td>72.280</td>\n",
1032 " <td>-130</td>\n",
1033 " <td>271</td>\n",
1034 " </tr>\n",
1035 " <tr>\n",
1036 " <th>13</th>\n",
1037 " <td>INCY</td>\n",
1038 " <td>96.230</td>\n",
1039 " <td>-108.7</td>\n",
1040 " <td>203</td>\n",
1041 " </tr>\n",
1042 " <tr>\n",
1043 " <th>14</th>\n",
1044 " <td>MO</td>\n",
1045 " <td>44.040</td>\n",
1046 " <td>-87.45</td>\n",
1047 " <td>445</td>\n",
1048 " </tr>\n",
1049 " <tr>\n",
1050 " <th>15</th>\n",
1051 " <td>NI</td>\n",
1052 " <td>24.520</td>\n",
1053 " <td>-80.42</td>\n",
1054 " <td>799</td>\n",
1055 " </tr>\n",
1056 " <tr>\n",
1057 " <th>16</th>\n",
1058 " <td>ECL</td>\n",
1059 " <td>203.920</td>\n",
1060 " <td>-68.76</td>\n",
1061 " <td>96</td>\n",
1062 " </tr>\n",
1063 " <tr>\n",
1064 " <th>17</th>\n",
1065 " <td>TAP</td>\n",
1066 " <td>37.930</td>\n",
1067 " <td>-53.25</td>\n",
1068 " <td>516</td>\n",
1069 " </tr>\n",
1070 " <tr>\n",
1071 " <th>18</th>\n",
1072 " <td>WDC</td>\n",
1073 " <td>35.834</td>\n",
1074 " <td>-43.43</td>\n",
1075 " <td>547</td>\n",
1076 " </tr>\n",
1077 " <tr>\n",
1078 " <th>19</th>\n",
1079 " <td>BA</td>\n",
1080 " <td>182.960</td>\n",
1081 " <td>-35.3</td>\n",
1082 " <td>107</td>\n",
1083 " </tr>\n",
1084 " <tr>\n",
1085 " <th>20</th>\n",
1086 " <td>FCX</td>\n",
1087 " <td>14.250</td>\n",
1088 " <td>-31.58</td>\n",
1089 " <td>1375</td>\n",
1090 " </tr>\n",
1091 " <tr>\n",
1092 " <th>21</th>\n",
1093 " <td>HFC</td>\n",
1094 " <td>25.740</td>\n",
1095 " <td>-25.75</td>\n",
1096 " <td>761</td>\n",
1097 " </tr>\n",
1098 " <tr>\n",
1099 " <th>22</th>\n",
1100 " <td>TWTR</td>\n",
1101 " <td>37.930</td>\n",
1102 " <td>-24.94</td>\n",
1103 " <td>516</td>\n",
1104 " </tr>\n",
1105 " <tr>\n",
1106 " <th>23</th>\n",
1107 " <td>PSX</td>\n",
1108 " <td>64.700</td>\n",
1109 " <td>-23.26</td>\n",
1110 " <td>303</td>\n",
1111 " </tr>\n",
1112 " <tr>\n",
1113 " <th>24</th>\n",
1114 " <td>TPR</td>\n",
1115 " <td>15.870</td>\n",
1116 " <td>-20.07</td>\n",
1117 " <td>1235</td>\n",
1118 " </tr>\n",
1119 " <tr>\n",
1120 " <th>25</th>\n",
1121 " <td>CVX</td>\n",
1122 " <td>91.500</td>\n",
1123 " <td>-19.48</td>\n",
1124 " <td>214</td>\n",
1125 " </tr>\n",
1126 " <tr>\n",
1127 " <th>26</th>\n",
1128 " <td>DD</td>\n",
1129 " <td>58.100</td>\n",
1130 " <td>-17.22</td>\n",
1131 " <td>337</td>\n",
1132 " </tr>\n",
1133 " <tr>\n",
1134 " <th>27</th>\n",
1135 " <td>DOW</td>\n",
1136 " <td>44.960</td>\n",
1137 " <td>-16.75</td>\n",
1138 " <td>436</td>\n",
1139 " </tr>\n",
1140 " <tr>\n",
1141 " <th>28</th>\n",
1142 " <td>LYV</td>\n",
1143 " <td>52.300</td>\n",
1144 " <td>-14.36</td>\n",
1145 " <td>374</td>\n",
1146 " </tr>\n",
1147 " <tr>\n",
1148 " <th>29</th>\n",
1149 " <td>CNP</td>\n",
1150 " <td>20.910</td>\n",
1151 " <td>-13.61</td>\n",
1152 " <td>937</td>\n",
1153 " </tr>\n",
1154 " <tr>\n",
1155 " <th>30</th>\n",
1156 " <td>F</td>\n",
1157 " <td>7.200</td>\n",
1158 " <td>-13.54</td>\n",
1159 " <td>2723</td>\n",
1160 " </tr>\n",
1161 " <tr>\n",
1162 " <th>31</th>\n",
1163 " <td>LB</td>\n",
1164 " <td>26.590</td>\n",
1165 " <td>-10.95</td>\n",
1166 " <td>737</td>\n",
1167 " </tr>\n",
1168 " <tr>\n",
1169 " <th>32</th>\n",
1170 " <td>L</td>\n",
1171 " <td>38.820</td>\n",
1172 " <td>-9.34</td>\n",
1173 " <td>505</td>\n",
1174 " </tr>\n",
1175 " <tr>\n",
1176 " <th>33</th>\n",
1177 " <td>NLSN</td>\n",
1178 " <td>15.940</td>\n",
1179 " <td>-9.11</td>\n",
1180 " <td>1230</td>\n",
1181 " </tr>\n",
1182 " <tr>\n",
1183 " <th>34</th>\n",
1184 " <td>WYNN</td>\n",
1185 " <td>88.120</td>\n",
1186 " <td>-8.39</td>\n",
1187 " <td>222</td>\n",
1188 " </tr>\n",
1189 " <tr>\n",
1190 " <th>35</th>\n",
1191 " <td>EXPE</td>\n",
1192 " <td>91.140</td>\n",
1193 " <td>-8.08</td>\n",
1194 " <td>215</td>\n",
1195 " </tr>\n",
1196 " <tr>\n",
1197 " <th>36</th>\n",
1198 " <td>MRO</td>\n",
1199 " <td>6.140</td>\n",
1200 " <td>-7.42</td>\n",
1201 " <td>3193</td>\n",
1202 " </tr>\n",
1203 " <tr>\n",
1204 " <th>37</th>\n",
1205 " <td>UAA</td>\n",
1206 " <td>11.280</td>\n",
1207 " <td>-7.41</td>\n",
1208 " <td>1738</td>\n",
1209 " </tr>\n",
1210 " <tr>\n",
1211 " <th>38</th>\n",
1212 " <td>NWL</td>\n",
1213 " <td>17.333</td>\n",
1214 " <td>-7.34</td>\n",
1215 " <td>1131</td>\n",
1216 " </tr>\n",
1217 " <tr>\n",
1218 " <th>39</th>\n",
1219 " <td>NWS</td>\n",
1220 " <td>16.112</td>\n",
1221 " <td>-7.25</td>\n",
1222 " <td>1216</td>\n",
1223 " </tr>\n",
1224 " <tr>\n",
1225 " <th>40</th>\n",
1226 " <td>NWSA</td>\n",
1227 " <td>15.900</td>\n",
1228 " <td>-7.16</td>\n",
1229 " <td>1233</td>\n",
1230 " </tr>\n",
1231 " <tr>\n",
1232 " <th>41</th>\n",
1233 " <td>GPS</td>\n",
1234 " <td>15.280</td>\n",
1235 " <td>-6.9</td>\n",
1236 " <td>1283</td>\n",
1237 " </tr>\n",
1238 " <tr>\n",
1239 " <th>42</th>\n",
1240 " <td>UA</td>\n",
1241 " <td>9.900</td>\n",
1242 " <td>-6.66</td>\n",
1243 " <td>1980</td>\n",
1244 " </tr>\n",
1245 " <tr>\n",
1246 " <th>43</th>\n",
1247 " <td>RCL</td>\n",
1248 " <td>60.600</td>\n",
1249 " <td>-6.59</td>\n",
1250 " <td>323</td>\n",
1251 " </tr>\n",
1252 " <tr>\n",
1253 " <th>44</th>\n",
1254 " <td>MOS</td>\n",
1255 " <td>18.140</td>\n",
1256 " <td>-6.08</td>\n",
1257 " <td>1080</td>\n",
1258 " </tr>\n",
1259 " <tr>\n",
1260 " <th>45</th>\n",
1261 " <td>UAL</td>\n",
1262 " <td>36.000</td>\n",
1263 " <td>-6.06</td>\n",
1264 " <td>544</td>\n",
1265 " </tr>\n",
1266 " <tr>\n",
1267 " <th>46</th>\n",
1268 " <td>AIG</td>\n",
1269 " <td>30.890</td>\n",
1270 " <td>-5.89</td>\n",
1271 " <td>634</td>\n",
1272 " </tr>\n",
1273 " <tr>\n",
1274 " <th>47</th>\n",
1275 " <td>PVH</td>\n",
1276 " <td>55.360</td>\n",
1277 " <td>-5.08</td>\n",
1278 " <td>354</td>\n",
1279 " </tr>\n",
1280 " <tr>\n",
1281 " <th>48</th>\n",
1282 " <td>HES</td>\n",
1283 " <td>55.160</td>\n",
1284 " <td>-5.03</td>\n",
1285 " <td>355</td>\n",
1286 " </tr>\n",
1287 " <tr>\n",
1288 " <th>49</th>\n",
1289 " <td>DAL</td>\n",
1290 " <td>29.600</td>\n",
1291 " <td>-5</td>\n",
1292 " <td>662</td>\n",
1293 " </tr>\n",
1294 " <tr>\n",
1295 " <th>50</th>\n",
1296 " <td>CAH</td>\n",
1297 " <td>54.300</td>\n",
1298 " <td>-4.27</td>\n",
1299 " <td>361</td>\n",
1300 " </tr>\n",
1301 " </tbody>\n",
1302 "</table>\n",
1303 "</div>"
1304 ],
1305 "text/plain": [
1306 " Ticker Price Price-to-Earnings Ratio Number of Shares to Buy\n",
1307 "0 SBAC 317.680 -2261.88 61\n",
1308 "1 CRM 202.060 -1165.07 97\n",
1309 "2 BMY 65.210 -639.08 300\n",
1310 "3 TEL 99.630 -328.9 196\n",
1311 "4 GILD 70.140 -318.24 279\n",
1312 "5 HPE 9.720 -299.36 2017\n",
1313 "6 DRI 88.750 -247.4 220\n",
1314 "7 KHC 36.964 -237.94 530\n",
1315 "8 DIS 136.510 -221.5 143\n",
1316 "9 XRAY 45.210 -203.96 433\n",
1317 "10 COF 70.580 -202.95 277\n",
1318 "11 IR 35.690 -156.36 549\n",
1319 "12 PRU 72.280 -130 271\n",
1320 "13 INCY 96.230 -108.7 203\n",
1321 "14 MO 44.040 -87.45 445\n",
1322 "15 NI 24.520 -80.42 799\n",
1323 "16 ECL 203.920 -68.76 96\n",
1324 "17 TAP 37.930 -53.25 516\n",
1325 "18 WDC 35.834 -43.43 547\n",
1326 "19 BA 182.960 -35.3 107\n",
1327 "20 FCX 14.250 -31.58 1375\n",
1328 "21 HFC 25.740 -25.75 761\n",
1329 "22 TWTR 37.930 -24.94 516\n",
1330 "23 PSX 64.700 -23.26 303\n",
1331 "24 TPR 15.870 -20.07 1235\n",
1332 "25 CVX 91.500 -19.48 214\n",
1333 "26 DD 58.100 -17.22 337\n",
1334 "27 DOW 44.960 -16.75 436\n",
1335 "28 LYV 52.300 -14.36 374\n",
1336 "29 CNP 20.910 -13.61 937\n",
1337 "30 F 7.200 -13.54 2723\n",
1338 "31 LB 26.590 -10.95 737\n",
1339 "32 L 38.820 -9.34 505\n",
1340 "33 NLSN 15.940 -9.11 1230\n",
1341 "34 WYNN 88.120 -8.39 222\n",
1342 "35 EXPE 91.140 -8.08 215\n",
1343 "36 MRO 6.140 -7.42 3193\n",
1344 "37 UAA 11.280 -7.41 1738\n",
1345 "38 NWL 17.333 -7.34 1131\n",
1346 "39 NWS 16.112 -7.25 1216\n",
1347 "40 NWSA 15.900 -7.16 1233\n",
1348 "41 GPS 15.280 -6.9 1283\n",
1349 "42 UA 9.900 -6.66 1980\n",
1350 "43 RCL 60.600 -6.59 323\n",
1351 "44 MOS 18.140 -6.08 1080\n",
1352 "45 UAL 36.000 -6.06 544\n",
1353 "46 AIG 30.890 -5.89 634\n",
1354 "47 PVH 55.360 -5.08 354\n",
1355 "48 HES 55.160 -5.03 355\n",
1356 "49 DAL 29.600 -5 662\n",
1357 "50 CAH 54.300 -4.27 361"
1358 ]
1359 },
1360 "execution_count": 10,
1361 "metadata": {},
1362 "output_type": "execute_result"
1363 }
1364 ],
1365 "source": [
1366 "position_size = float(portfolio_size) / len(final_dataframe.index)\n",
1367 "for i in range(0, len(final_dataframe['Ticker'])):\n",
1368 " final_dataframe.loc[i, 'Number of Shares to Buy'] = math.floor(position_size / final_dataframe['Price'][i])\n",
1369 "final_dataframe"
1370 ]
1371 },
1372 {
1373 "cell_type": "markdown",
1374 "metadata": {},
1375 "source": [
1376 "## Building a Better (and More Realistic) Value Strategy\n",
1377 "Every valuation metric has certain flaws.\n",
1378 "\n",
1379 "For example, the price-to-earnings ratio doesn't work well with stocks with negative earnings.\n",
1380 "\n",
1381 "Similarly, stocks that buyback their own shares are difficult to value using the price-to-book ratio.\n",
1382 "\n",
1383 "Investors typically use a `composite` basket of valuation metrics to build robust quantitative value strategies. In this section, we will filter for stocks with the lowest percentiles on the following metrics:\n",
1384 "\n",
1385 "* Price-to-earnings ratio\n",
1386 "* Price-to-book ratio\n",
1387 "* Price-to-sales ratio\n",
1388 "* Enterprise Value divided by Earnings Before Interest, Taxes, Depreciation, and Amortization (EV/EBITDA)\n",
1389 "* Enterprise Value divided by Gross Profit (EV/GP)\n",
1390 "\n",
1391 "Some of these metrics aren't provided directly by the IEX Cloud API, and must be computed after pulling raw data. We'll start by calculating each data point from scratch."
1392 ]
1393 },
1394 {
1395 "cell_type": "code",
1396 "execution_count": 11,
1397 "metadata": {},
1398 "outputs": [],
1399 "source": [
1400 "symbol = 'AAPL'\n",
1401 "batch_api_call_url = f'https://sandbox.iexapis.com/stable/stock/market/batch/?types=advanced-stats,quote&symbols={symbol}&token={IEX_CLOUD_API_TOKEN}'\n",
1402 "data = requests.get(batch_api_call_url).json()\n",
1403 "\n",
1404 "# P/E Ratio\n",
1405 "pe_ratio = data[symbol]['quote']['peRatio']\n",
1406 "\n",
1407 "# P/B Ratio\n",
1408 "pb_ratio = data[symbol]['advanced-stats']['priceToBook']\n",
1409 "\n",
1410 "#P/S Ratio\n",
1411 "ps_ratio = data[symbol]['advanced-stats']['priceToSales']\n",
1412 "\n",
1413 "# EV/EBITDA\n",
1414 "enterprise_value = data[symbol]['advanced-stats']['enterpriseValue']\n",
1415 "ebitda = data[symbol]['advanced-stats']['EBITDA']\n",
1416 "ev_to_ebitda = enterprise_value/ebitda\n",
1417 "\n",
1418 "# EV/GP\n",
1419 "gross_profit = data[symbol]['advanced-stats']['grossProfit']\n",
1420 "ev_to_gross_profit = enterprise_value/gross_profit"
1421 ]
1422 },
1423 {
1424 "cell_type": "markdown",
1425 "metadata": {},
1426 "source": [
1427 "Let's move on to building our DataFrame. You'll notice that I use the abbreviation `rv` often. It stands for `robust value`, which is what we'll call this sophisticated strategy moving forward."
1428 ]
1429 },
1430 {
1431 "cell_type": "code",
1432 "execution_count": 12,
1433 "metadata": {},
1434 "outputs": [
1435 {
1436 "data": {
1437 "text/html": [
1438 "<div>\n",
1439 "<style scoped>\n",
1440 " .dataframe tbody tr th:only-of-type {\n",
1441 " vertical-align: middle;\n",
1442 " }\n",
1443 "\n",
1444 " .dataframe tbody tr th {\n",
1445 " vertical-align: top;\n",
1446 " }\n",
1447 "\n",
1448 " .dataframe thead th {\n",
1449 " text-align: right;\n",
1450 " }\n",
1451 "</style>\n",
1452 "<table border=\"1\" class=\"dataframe\">\n",
1453 " <thead>\n",
1454 " <tr style=\"text-align: right;\">\n",
1455 " <th></th>\n",
1456 " <th>Ticker</th>\n",
1457 " <th>Price</th>\n",
1458 " <th>Number of Shares to Buy</th>\n",
1459 " <th>Price-to-Earnings Ratio</th>\n",
1460 " <th>PE Percentile</th>\n",
1461 " <th>Price-to-Book Ratio</th>\n",
1462 " <th>PB Percentile</th>\n",
1463 " <th>EV/EBITDA</th>\n",
1464 " <th>EV/EBITDA Percentile</th>\n",
1465 " <th>EV/GP</th>\n",
1466 " <th>EV/GP Percentile</th>\n",
1467 " <th>RV Score</th>\n",
1468 " </tr>\n",
1469 " </thead>\n",
1470 " <tbody>\n",
1471 " <tr>\n",
1472 " <th>0</th>\n",
1473 " <td>A</td>\n",
1474 " <td>100.010</td>\n",
1475 " <td>N/A</td>\n",
1476 " <td>46.95</td>\n",
1477 " <td>N/A</td>\n",
1478 " <td>6.596140</td>\n",
1479 " <td>N/A</td>\n",
1480 " <td>26.372976</td>\n",
1481 " <td>N/A</td>\n",
1482 " <td>11.311629</td>\n",
1483 " <td>N/A</td>\n",
1484 " <td>N/A</td>\n",
1485 " </tr>\n",
1486 " <tr>\n",
1487 " <th>1</th>\n",
1488 " <td>AAL</td>\n",
1489 " <td>13.360</td>\n",
1490 " <td>N/A</td>\n",
1491 " <td>-1.71</td>\n",
1492 " <td>N/A</td>\n",
1493 " <td>-60.417952</td>\n",
1494 " <td>N/A</td>\n",
1495 " <td>5.952664</td>\n",
1496 " <td>N/A</td>\n",
1497 " <td>3.098483</td>\n",
1498 " <td>N/A</td>\n",
1499 " <td>N/A</td>\n",
1500 " </tr>\n",
1501 " <tr>\n",
1502 " <th>2</th>\n",
1503 " <td>AAP</td>\n",
1504 " <td>161.440</td>\n",
1505 " <td>N/A</td>\n",
1506 " <td>29</td>\n",
1507 " <td>N/A</td>\n",
1508 " <td>3.123759</td>\n",
1509 " <td>N/A</td>\n",
1510 " <td>15.086368</td>\n",
1511 " <td>N/A</td>\n",
1512 " <td>3.208667</td>\n",
1513 " <td>N/A</td>\n",
1514 " <td>N/A</td>\n",
1515 " </tr>\n",
1516 " <tr>\n",
1517 " <th>3</th>\n",
1518 " <td>AAPL</td>\n",
1519 " <td>466.070</td>\n",
1520 " <td>N/A</td>\n",
1521 " <td>34.69</td>\n",
1522 " <td>N/A</td>\n",
1523 " <td>22.373999</td>\n",
1524 " <td>N/A</td>\n",
1525 " <td>25.708773</td>\n",
1526 " <td>N/A</td>\n",
1527 " <td>19.428993</td>\n",
1528 " <td>N/A</td>\n",
1529 " <td>N/A</td>\n",
1530 " </tr>\n",
1531 " <tr>\n",
1532 " <th>4</th>\n",
1533 " <td>ABBV</td>\n",
1534 " <td>96.680</td>\n",
1535 " <td>N/A</td>\n",
1536 " <td>21</td>\n",
1537 " <td>N/A</td>\n",
1538 " <td>-21.463532</td>\n",
1539 " <td>N/A</td>\n",
1540 " <td>12.272585</td>\n",
1541 " <td>N/A</td>\n",
1542 " <td>7.672427</td>\n",
1543 " <td>N/A</td>\n",
1544 " <td>N/A</td>\n",
1545 " </tr>\n",
1546 " <tr>\n",
1547 " <th>...</th>\n",
1548 " <td>...</td>\n",
1549 " <td>...</td>\n",
1550 " <td>...</td>\n",
1551 " <td>...</td>\n",
1552 " <td>...</td>\n",
1553 " <td>...</td>\n",
1554 " <td>...</td>\n",
1555 " <td>...</td>\n",
1556 " <td>...</td>\n",
1557 " <td>...</td>\n",
1558 " <td>...</td>\n",
1559 " <td>...</td>\n",
1560 " </tr>\n",
1561 " <tr>\n",
1562 " <th>500</th>\n",
1563 " <td>YUM</td>\n",
1564 " <td>94.320</td>\n",
1565 " <td>N/A</td>\n",
1566 " <td>28</td>\n",
1567 " <td>N/A</td>\n",
1568 " <td>-3.659682</td>\n",
1569 " <td>N/A</td>\n",
1570 " <td>18.841249</td>\n",
1571 " <td>N/A</td>\n",
1572 " <td>13.891510</td>\n",
1573 " <td>N/A</td>\n",
1574 " <td>N/A</td>\n",
1575 " </tr>\n",
1576 " <tr>\n",
1577 " <th>501</th>\n",
1578 " <td>ZBH</td>\n",
1579 " <td>143.470</td>\n",
1580 " <td>N/A</td>\n",
1581 " <td>718.1</td>\n",
1582 " <td>N/A</td>\n",
1583 " <td>2.390128</td>\n",
1584 " <td>N/A</td>\n",
1585 " <td>17.170711</td>\n",
1586 " <td>N/A</td>\n",
1587 " <td>7.478460</td>\n",
1588 " <td>N/A</td>\n",
1589 " <td>N/A</td>\n",
1590 " </tr>\n",
1591 " <tr>\n",
1592 " <th>502</th>\n",
1593 " <td>ZBRA</td>\n",
1594 " <td>288.222</td>\n",
1595 " <td>N/A</td>\n",
1596 " <td>31.86</td>\n",
1597 " <td>N/A</td>\n",
1598 " <td>8.600669</td>\n",
1599 " <td>N/A</td>\n",
1600 " <td>19.480804</td>\n",
1601 " <td>N/A</td>\n",
1602 " <td>8.449885</td>\n",
1603 " <td>N/A</td>\n",
1604 " <td>N/A</td>\n",
1605 " </tr>\n",
1606 " <tr>\n",
1607 " <th>503</th>\n",
1608 " <td>ZION</td>\n",
1609 " <td>35.770</td>\n",
1610 " <td>N/A</td>\n",
1611 " <td>13.24</td>\n",
1612 " <td>N/A</td>\n",
1613 " <td>0.766237</td>\n",
1614 " <td>N/A</td>\n",
1615 " <td>NaN</td>\n",
1616 " <td>N/A</td>\n",
1617 " <td>NaN</td>\n",
1618 " <td>N/A</td>\n",
1619 " <td>N/A</td>\n",
1620 " </tr>\n",
1621 " <tr>\n",
1622 " <th>504</th>\n",
1623 " <td>ZTS</td>\n",
1624 " <td>160.920</td>\n",
1625 " <td>N/A</td>\n",
1626 " <td>47.2</td>\n",
1627 " <td>N/A</td>\n",
1628 " <td>29.222282</td>\n",
1629 " <td>N/A</td>\n",
1630 " <td>32.483029</td>\n",
1631 " <td>N/A</td>\n",
1632 " <td>19.667225</td>\n",
1633 " <td>N/A</td>\n",
1634 " <td>N/A</td>\n",
1635 " </tr>\n",
1636 " </tbody>\n",
1637 "</table>\n",
1638 "<p>505 rows × 12 columns</p>\n",
1639 "</div>"
1640 ],
1641 "text/plain": [
1642 " Ticker Price Number of Shares to Buy Price-to-Earnings Ratio \\\n",
1643 "0 A 100.010 N/A 46.95 \n",
1644 "1 AAL 13.360 N/A -1.71 \n",
1645 "2 AAP 161.440 N/A 29 \n",
1646 "3 AAPL 466.070 N/A 34.69 \n",
1647 "4 ABBV 96.680 N/A 21 \n",
1648 ".. ... ... ... ... \n",
1649 "500 YUM 94.320 N/A 28 \n",
1650 "501 ZBH 143.470 N/A 718.1 \n",
1651 "502 ZBRA 288.222 N/A 31.86 \n",
1652 "503 ZION 35.770 N/A 13.24 \n",
1653 "504 ZTS 160.920 N/A 47.2 \n",
1654 "\n",
1655 " PE Percentile Price-to-Book Ratio PB Percentile EV/EBITDA \\\n",
1656 "0 N/A 6.596140 N/A 26.372976 \n",
1657 "1 N/A -60.417952 N/A 5.952664 \n",
1658 "2 N/A 3.123759 N/A 15.086368 \n",
1659 "3 N/A 22.373999 N/A 25.708773 \n",
1660 "4 N/A -21.463532 N/A 12.272585 \n",
1661 ".. ... ... ... ... \n",
1662 "500 N/A -3.659682 N/A 18.841249 \n",
1663 "501 N/A 2.390128 N/A 17.170711 \n",
1664 "502 N/A 8.600669 N/A 19.480804 \n",
1665 "503 N/A 0.766237 N/A NaN \n",
1666 "504 N/A 29.222282 N/A 32.483029 \n",
1667 "\n",
1668 " EV/EBITDA Percentile EV/GP EV/GP Percentile RV Score \n",
1669 "0 N/A 11.311629 N/A N/A \n",
1670 "1 N/A 3.098483 N/A N/A \n",
1671 "2 N/A 3.208667 N/A N/A \n",
1672 "3 N/A 19.428993 N/A N/A \n",
1673 "4 N/A 7.672427 N/A N/A \n",
1674 ".. ... ... ... ... \n",
1675 "500 N/A 13.891510 N/A N/A \n",
1676 "501 N/A 7.478460 N/A N/A \n",
1677 "502 N/A 8.449885 N/A N/A \n",
1678 "503 N/A NaN N/A N/A \n",
1679 "504 N/A 19.667225 N/A N/A \n",
1680 "\n",
1681 "[505 rows x 12 columns]"
1682 ]
1683 },
1684 "execution_count": 12,
1685 "metadata": {},
1686 "output_type": "execute_result"
1687 }
1688 ],
1689 "source": [
1690 "rv_columns = [\n",
1691 " 'Ticker',\n",
1692 " 'Price',\n",
1693 " 'Number of Shares to Buy', \n",
1694 " 'Price-to-Earnings Ratio',\n",
1695 " 'PE Percentile',\n",
1696 " 'Price-to-Book Ratio',\n",
1697 " 'PB Percentile',\n",
1698 " 'Price-to-Sales Ratio',\n",
1699 " 'PS Percentile',\n",
1700 " 'EV/EBITDA',\n",
1701 " 'EV/EBITDA Percentile',\n",
1702 " 'EV/GP',\n",
1703 " 'EV/GP Percentile',\n",
1704 " 'RV Score'\n",
1705 "]\n",
1706 "\n",
1707 "rv_dataframe = pd.DataFrame(columns = rv_columns)\n",
1708 "\n",
1709 "for symbol_string in symbol_strings:\n",
1710 " batch_api_call_url = f'https://sandbox.iexapis.com/stable/stock/market/batch?symbols={symbol_string}&types=quote,advanced-stats&token={IEX_CLOUD_API_TOKEN}'\n",
1711 " data = requests.get(batch_api_call_url).json()\n",
1712 " for symbol in symbol_string.split(','):\n",
1713 " enterprise_value = data[symbol]['advanced-stats']['enterpriseValue']\n",
1714 " ebitda = data[symbol]['advanced-stats']['EBITDA']\n",
1715 " gross_profit = data[symbol]['advanced-stats']['grossProfit']\n",
1716 " \n",
1717 " try:\n",
1718 " ev_to_ebitda = enterprise_value/ebitda\n",
1719 " except TypeError:\n",
1720 " ev_to_ebitda = np.NaN\n",
1721 " \n",
1722 " try:\n",
1723 " ev_to_gross_profit = enterprise_value/gross_profit\n",
1724 " except TypeError:\n",
1725 " ev_to_gross_profit = np.NaN\n",
1726 " \n",
1727 " rv_dataframe = rv_dataframe.append(\n",
1728 " pd.Series([\n",
1729 " symbol,\n",
1730 " data[symbol]['quote']['latestPrice'],\n",
1731 " 'N/A',\n",
1732 " data[symbol]['quote']['peRatio'],\n",
1733 " 'N/A',\n",
1734 " data[symbol]['advanced-stats']['priceToBook'],\n",
1735 " 'N/A',\n",
1736 " data[symbol]['advanced-stats']['priceToSales'],\n",
1737 " 'N/A',\n",
1738 " ev_to_ebitda,\n",
1739 " 'N/A',\n",
1740 " ev_to_gross_profit,\n",
1741 " 'N/A',\n",
1742 " 'N/A'\n",
1743 " ],\n",
1744 " index = rv_columns),\n",
1745 " ignore_index = True\n",
1746 " )"
1747 ]
1748 },
1749 {
1750 "cell_type": "markdown",
1751 "metadata": {},
1752 "source": [
1753 "## Dealing With Missing Data in Our DataFrame\n",
1754 "\n",
1755 "Our DataFrame contains some missing data because all of the metrics we require are not available through the API we're using. \n",
1756 "\n",
1757 "You can use pandas' `isnull` method to identify missing data:"
1758 ]
1759 },
1760 {
1761 "cell_type": "code",
1762 "execution_count": 14,
1763 "metadata": {},
1764 "outputs": [
1765 {
1766 "data": {
1767 "text/html": [
1768 "<div>\n",
1769 "<style scoped>\n",
1770 " .dataframe tbody tr th:only-of-type {\n",
1771 " vertical-align: middle;\n",
1772 " }\n",
1773 "\n",
1774 " .dataframe tbody tr th {\n",
1775 " vertical-align: top;\n",
1776 " }\n",
1777 "\n",
1778 " .dataframe thead th {\n",
1779 " text-align: right;\n",
1780 " }\n",
1781 "</style>\n",
1782 "<table border=\"1\" class=\"dataframe\">\n",
1783 " <thead>\n",
1784 " <tr style=\"text-align: right;\">\n",
1785 " <th></th>\n",
1786 " <th>Ticker</th>\n",
1787 " <th>Price</th>\n",
1788 " <th>Number of Shares to Buy</th>\n",
1789 " <th>Price-to-Earnings Ratio</th>\n",
1790 " <th>PE Percentile</th>\n",
1791 " <th>Price-to-Book Ratio</th>\n",
1792 " <th>PB Percentile</th>\n",
1793 " <th>EV/EBITDA</th>\n",
1794 " <th>EV/EBITDA Percentile</th>\n",
1795 " <th>EV/GP</th>\n",
1796 " <th>EV/GP Percentile</th>\n",
1797 " <th>RV Score</th>\n",
1798 " </tr>\n",
1799 " </thead>\n",
1800 " <tbody>\n",
1801 " <tr>\n",
1802 " <th>17</th>\n",
1803 " <td>AFL</td>\n",
1804 " <td>39.060</td>\n",
1805 " <td>N/A</td>\n",
1806 " <td>9.7</td>\n",
1807 " <td>N/A</td>\n",
1808 " <td>0.950934</td>\n",
1809 " <td>N/A</td>\n",
1810 " <td>NaN</td>\n",
1811 " <td>N/A</td>\n",
1812 " <td>NaN</td>\n",
1813 " <td>N/A</td>\n",
1814 " <td>N/A</td>\n",
1815 " </tr>\n",
1816 " <tr>\n",
1817 " <th>18</th>\n",
1818 " <td>AIG</td>\n",
1819 " <td>31.950</td>\n",
1820 " <td>N/A</td>\n",
1821 " <td>-5.88</td>\n",
1822 " <td>N/A</td>\n",
1823 " <td>0.407454</td>\n",
1824 " <td>N/A</td>\n",
1825 " <td>NaN</td>\n",
1826 " <td>N/A</td>\n",
1827 " <td>NaN</td>\n",
1828 " <td>N/A</td>\n",
1829 " <td>N/A</td>\n",
1830 " </tr>\n",
1831 " <tr>\n",
1832 " <th>20</th>\n",
1833 " <td>AIZ</td>\n",
1834 " <td>127.680</td>\n",
1835 " <td>N/A</td>\n",
1836 " <td>20.22</td>\n",
1837 " <td>N/A</td>\n",
1838 " <td>1.360264</td>\n",
1839 " <td>N/A</td>\n",
1840 " <td>NaN</td>\n",
1841 " <td>N/A</td>\n",
1842 " <td>NaN</td>\n",
1843 " <td>N/A</td>\n",
1844 " <td>N/A</td>\n",
1845 " </tr>\n",
1846 " <tr>\n",
1847 " <th>26</th>\n",
1848 " <td>ALL</td>\n",
1849 " <td>97.880</td>\n",
1850 " <td>N/A</td>\n",
1851 " <td>7.1</td>\n",
1852 " <td>N/A</td>\n",
1853 " <td>1.173772</td>\n",
1854 " <td>N/A</td>\n",
1855 " <td>NaN</td>\n",
1856 " <td>N/A</td>\n",
1857 " <td>NaN</td>\n",
1858 " <td>N/A</td>\n",
1859 " <td>N/A</td>\n",
1860 " </tr>\n",
1861 " <tr>\n",
1862 " <th>39</th>\n",
1863 " <td>ANTM</td>\n",
1864 " <td>293.560</td>\n",
1865 " <td>N/A</td>\n",
1866 " <td>12.24</td>\n",
1867 " <td>N/A</td>\n",
1868 " <td>2.304037</td>\n",
1869 " <td>N/A</td>\n",
1870 " <td>NaN</td>\n",
1871 " <td>N/A</td>\n",
1872 " <td>NaN</td>\n",
1873 " <td>N/A</td>\n",
1874 " <td>N/A</td>\n",
1875 " </tr>\n",
1876 " <tr>\n",
1877 " <th>40</th>\n",
1878 " <td>AON</td>\n",
1879 " <td>196.350</td>\n",
1880 " <td>N/A</td>\n",
1881 " <td>26.09</td>\n",
1882 " <td>N/A</td>\n",
1883 " <td>13.486066</td>\n",
1884 " <td>N/A</td>\n",
1885 " <td>17.533688</td>\n",
1886 " <td>N/A</td>\n",
1887 " <td>NaN</td>\n",
1888 " <td>N/A</td>\n",
1889 " <td>N/A</td>\n",
1890 " </tr>\n",
1891 " <tr>\n",
1892 " <th>56</th>\n",
1893 " <td>BAC</td>\n",
1894 " <td>26.970</td>\n",
1895 " <td>N/A</td>\n",
1896 " <td>12.63</td>\n",
1897 " <td>N/A</td>\n",
1898 " <td>0.867662</td>\n",
1899 " <td>N/A</td>\n",
1900 " <td>NaN</td>\n",
1901 " <td>N/A</td>\n",
1902 " <td>NaN</td>\n",
1903 " <td>N/A</td>\n",
1904 " <td>N/A</td>\n",
1905 " </tr>\n",
1906 " <tr>\n",
1907 " <th>64</th>\n",
1908 " <td>BK</td>\n",
1909 " <td>38.160</td>\n",
1910 " <td>N/A</td>\n",
1911 " <td>8.27</td>\n",
1912 " <td>N/A</td>\n",
1913 " <td>0.804550</td>\n",
1914 " <td>N/A</td>\n",
1915 " <td>NaN</td>\n",
1916 " <td>N/A</td>\n",
1917 " <td>NaN</td>\n",
1918 " <td>N/A</td>\n",
1919 " <td>N/A</td>\n",
1920 " </tr>\n",
1921 " <tr>\n",
1922 " <th>65</th>\n",
1923 " <td>BKNG</td>\n",
1924 " <td>1866.170</td>\n",
1925 " <td>N/A</td>\n",
1926 " <td>30.78</td>\n",
1927 " <td>N/A</td>\n",
1928 " <td>12.571484</td>\n",
1929 " <td>N/A</td>\n",
1930 " <td>12.859665</td>\n",
1931 " <td>N/A</td>\n",
1932 " <td>NaN</td>\n",
1933 " <td>N/A</td>\n",
1934 " <td>N/A</td>\n",
1935 " </tr>\n",
1936 " <tr>\n",
1937 " <th>75</th>\n",
1938 " <td>C</td>\n",
1939 " <td>55.140</td>\n",
1940 " <td>N/A</td>\n",
1941 " <td>9.52</td>\n",
1942 " <td>N/A</td>\n",
1943 " <td>0.576968</td>\n",
1944 " <td>N/A</td>\n",
1945 " <td>NaN</td>\n",
1946 " <td>N/A</td>\n",
1947 " <td>NaN</td>\n",
1948 " <td>N/A</td>\n",
1949 " <td>N/A</td>\n",
1950 " </tr>\n",
1951 " <tr>\n",
1952 " <th>78</th>\n",
1953 " <td>CARR</td>\n",
1954 " <td>30.210</td>\n",
1955 " <td>N/A</td>\n",
1956 " <td>None</td>\n",
1957 " <td>N/A</td>\n",
1958 " <td>1.884398</td>\n",
1959 " <td>N/A</td>\n",
1960 " <td>9.144195</td>\n",
1961 " <td>N/A</td>\n",
1962 " <td>4.550543</td>\n",
1963 " <td>N/A</td>\n",
1964 " <td>N/A</td>\n",
1965 " </tr>\n",
1966 " <tr>\n",
1967 " <th>80</th>\n",
1968 " <td>CB</td>\n",
1969 " <td>132.400</td>\n",
1970 " <td>N/A</td>\n",
1971 " <td>27.62</td>\n",
1972 " <td>N/A</td>\n",
1973 " <td>1.066146</td>\n",
1974 " <td>N/A</td>\n",
1975 " <td>NaN</td>\n",
1976 " <td>N/A</td>\n",
1977 " <td>NaN</td>\n",
1978 " <td>N/A</td>\n",
1979 " <td>N/A</td>\n",
1980 " </tr>\n",
1981 " <tr>\n",
1982 " <th>90</th>\n",
1983 " <td>CFG</td>\n",
1984 " <td>26.840</td>\n",
1985 " <td>N/A</td>\n",
1986 " <td>10.71</td>\n",
1987 " <td>N/A</td>\n",
1988 " <td>0.501914</td>\n",
1989 " <td>N/A</td>\n",
1990 " <td>NaN</td>\n",
1991 " <td>N/A</td>\n",
1992 " <td>NaN</td>\n",
1993 " <td>N/A</td>\n",
1994 " <td>N/A</td>\n",
1995 " </tr>\n",
1996 " <tr>\n",
1997 " <th>94</th>\n",
1998 " <td>CI</td>\n",
1999 " <td>188.390</td>\n",
2000 " <td>N/A</td>\n",
2001 " <td>13.31</td>\n",
2002 " <td>N/A</td>\n",
2003 " <td>1.477180</td>\n",
2004 " <td>N/A</td>\n",
2005 " <td>NaN</td>\n",
2006 " <td>N/A</td>\n",
2007 " <td>NaN</td>\n",
2008 " <td>N/A</td>\n",
2009 " <td>N/A</td>\n",
2010 " </tr>\n",
2011 " <tr>\n",
2012 " <th>95</th>\n",
2013 " <td>CINF</td>\n",
2014 " <td>80.210</td>\n",
2015 " <td>N/A</td>\n",
2016 " <td>23.51</td>\n",
2017 " <td>N/A</td>\n",
2018 " <td>1.305219</td>\n",
2019 " <td>N/A</td>\n",
2020 " <td>NaN</td>\n",
2021 " <td>N/A</td>\n",
2022 " <td>NaN</td>\n",
2023 " <td>N/A</td>\n",
2024 " <td>N/A</td>\n",
2025 " </tr>\n",
2026 " <tr>\n",
2027 " <th>97</th>\n",
2028 " <td>CLX</td>\n",
2029 " <td>232.200</td>\n",
2030 " <td>N/A</td>\n",
2031 " <td>30.25</td>\n",
2032 " <td>N/A</td>\n",
2033 " <td>32.278899</td>\n",
2034 " <td>N/A</td>\n",
2035 " <td>NaN</td>\n",
2036 " <td>N/A</td>\n",
2037 " <td>9.790028</td>\n",
2038 " <td>N/A</td>\n",
2039 " <td>N/A</td>\n",
2040 " </tr>\n",
2041 " <tr>\n",
2042 " <th>98</th>\n",
2043 " <td>CMA</td>\n",
2044 " <td>43.270</td>\n",
2045 " <td>N/A</td>\n",
2046 " <td>10.2</td>\n",
2047 " <td>N/A</td>\n",
2048 " <td>0.801751</td>\n",
2049 " <td>N/A</td>\n",
2050 " <td>NaN</td>\n",
2051 " <td>N/A</td>\n",
2052 " <td>NaN</td>\n",
2053 " <td>N/A</td>\n",
2054 " <td>N/A</td>\n",
2055 " </tr>\n",
2056 " <tr>\n",
2057 " <th>104</th>\n",
2058 " <td>CNC</td>\n",
2059 " <td>65.780</td>\n",
2060 " <td>N/A</td>\n",
2061 " <td>22.88</td>\n",
2062 " <td>N/A</td>\n",
2063 " <td>2.982878</td>\n",
2064 " <td>N/A</td>\n",
2065 " <td>NaN</td>\n",
2066 " <td>N/A</td>\n",
2067 " <td>NaN</td>\n",
2068 " <td>N/A</td>\n",
2069 " <td>N/A</td>\n",
2070 " </tr>\n",
2071 " <tr>\n",
2072 " <th>106</th>\n",
2073 " <td>COF</td>\n",
2074 " <td>67.830</td>\n",
2075 " <td>N/A</td>\n",
2076 " <td>-195.92</td>\n",
2077 " <td>N/A</td>\n",
2078 " <td>0.534970</td>\n",
2079 " <td>N/A</td>\n",
2080 " <td>NaN</td>\n",
2081 " <td>N/A</td>\n",
2082 " <td>NaN</td>\n",
2083 " <td>N/A</td>\n",
2084 " <td>N/A</td>\n",
2085 " </tr>\n",
2086 " <tr>\n",
2087 " <th>129</th>\n",
2088 " <td>DFS</td>\n",
2089 " <td>55.920</td>\n",
2090 " <td>N/A</td>\n",
2091 " <td>17.68</td>\n",
2092 " <td>N/A</td>\n",
2093 " <td>1.456734</td>\n",
2094 " <td>N/A</td>\n",
2095 " <td>NaN</td>\n",
2096 " <td>N/A</td>\n",
2097 " <td>NaN</td>\n",
2098 " <td>N/A</td>\n",
2099 " <td>N/A</td>\n",
2100 " </tr>\n",
2101 " <tr>\n",
2102 " <th>185</th>\n",
2103 " <td>FITB</td>\n",
2104 " <td>21.043</td>\n",
2105 " <td>N/A</td>\n",
2106 " <td>10.87</td>\n",
2107 " <td>N/A</td>\n",
2108 " <td>0.717095</td>\n",
2109 " <td>N/A</td>\n",
2110 " <td>NaN</td>\n",
2111 " <td>N/A</td>\n",
2112 " <td>NaN</td>\n",
2113 " <td>N/A</td>\n",
2114 " <td>N/A</td>\n",
2115 " </tr>\n",
2116 " <tr>\n",
2117 " <th>192</th>\n",
2118 " <td>FRC</td>\n",
2119 " <td>123.410</td>\n",
2120 " <td>N/A</td>\n",
2121 " <td>22.93</td>\n",
2122 " <td>N/A</td>\n",
2123 " <td>2.163006</td>\n",
2124 " <td>N/A</td>\n",
2125 " <td>NaN</td>\n",
2126 " <td>N/A</td>\n",
2127 " <td>NaN</td>\n",
2128 " <td>N/A</td>\n",
2129 " <td>N/A</td>\n",
2130 " </tr>\n",
2131 " <tr>\n",
2132 " <th>201</th>\n",
2133 " <td>GL</td>\n",
2134 " <td>85.320</td>\n",
2135 " <td>N/A</td>\n",
2136 " <td>13.09</td>\n",
2137 " <td>N/A</td>\n",
2138 " <td>1.266177</td>\n",
2139 " <td>N/A</td>\n",
2140 " <td>NaN</td>\n",
2141 " <td>N/A</td>\n",
2142 " <td>NaN</td>\n",
2143 " <td>N/A</td>\n",
2144 " <td>N/A</td>\n",
2145 " </tr>\n",
2146 " <tr>\n",
2147 " <th>210</th>\n",
2148 " <td>GS</td>\n",
2149 " <td>211.320</td>\n",
2150 " <td>N/A</td>\n",
2151 " <td>16.21</td>\n",
2152 " <td>N/A</td>\n",
2153 " <td>0.813961</td>\n",
2154 " <td>N/A</td>\n",
2155 " <td>NaN</td>\n",
2156 " <td>N/A</td>\n",
2157 " <td>NaN</td>\n",
2158 " <td>N/A</td>\n",
2159 " <td>N/A</td>\n",
2160 " </tr>\n",
2161 " <tr>\n",
2162 " <th>214</th>\n",
2163 " <td>HBAN</td>\n",
2164 " <td>10.179</td>\n",
2165 " <td>N/A</td>\n",
2166 " <td>12.49</td>\n",
2167 " <td>N/A</td>\n",
2168 " <td>0.855431</td>\n",
2169 " <td>N/A</td>\n",
2170 " <td>NaN</td>\n",
2171 " <td>N/A</td>\n",
2172 " <td>NaN</td>\n",
2173 " <td>N/A</td>\n",
2174 " <td>N/A</td>\n",
2175 " </tr>\n",
2176 " <tr>\n",
2177 " <th>220</th>\n",
2178 " <td>HIG</td>\n",
2179 " <td>42.840</td>\n",
2180 " <td>N/A</td>\n",
2181 " <td>8.7</td>\n",
2182 " <td>N/A</td>\n",
2183 " <td>0.959808</td>\n",
2184 " <td>N/A</td>\n",
2185 " <td>NaN</td>\n",
2186 " <td>N/A</td>\n",
2187 " <td>NaN</td>\n",
2188 " <td>N/A</td>\n",
2189 " <td>N/A</td>\n",
2190 " </tr>\n",
2191 " <tr>\n",
2192 " <th>232</th>\n",
2193 " <td>HUM</td>\n",
2194 " <td>427.300</td>\n",
2195 " <td>N/A</td>\n",
2196 " <td>15.96</td>\n",
2197 " <td>N/A</td>\n",
2198 " <td>4.713176</td>\n",
2199 " <td>N/A</td>\n",
2200 " <td>NaN</td>\n",
2201 " <td>N/A</td>\n",
2202 " <td>NaN</td>\n",
2203 " <td>N/A</td>\n",
2204 " <td>N/A</td>\n",
2205 " </tr>\n",
2206 " <tr>\n",
2207 " <th>260</th>\n",
2208 " <td>JPM</td>\n",
2209 " <td>103.310</td>\n",
2210 " <td>N/A</td>\n",
2211 " <td>14.29</td>\n",
2212 " <td>N/A</td>\n",
2213 " <td>1.213231</td>\n",
2214 " <td>N/A</td>\n",
2215 " <td>NaN</td>\n",
2216 " <td>N/A</td>\n",
2217 " <td>NaN</td>\n",
2218 " <td>N/A</td>\n",
2219 " <td>N/A</td>\n",
2220 " </tr>\n",
2221 " <tr>\n",
2222 " <th>262</th>\n",
2223 " <td>KEY</td>\n",
2224 " <td>13.180</td>\n",
2225 " <td>N/A</td>\n",
2226 " <td>11.35</td>\n",
2227 " <td>N/A</td>\n",
2228 " <td>0.752496</td>\n",
2229 " <td>N/A</td>\n",
2230 " <td>NaN</td>\n",
2231 " <td>N/A</td>\n",
2232 " <td>NaN</td>\n",
2233 " <td>N/A</td>\n",
2234 " <td>N/A</td>\n",
2235 " </tr>\n",
2236 " <tr>\n",
2237 " <th>274</th>\n",
2238 " <td>L</td>\n",
2239 " <td>38.130</td>\n",
2240 " <td>N/A</td>\n",
2241 " <td>-9.39</td>\n",
2242 " <td>N/A</td>\n",
2243 " <td>0.573510</td>\n",
2244 " <td>N/A</td>\n",
2245 " <td>NaN</td>\n",
2246 " <td>N/A</td>\n",
2247 " <td>NaN</td>\n",
2248 " <td>N/A</td>\n",
2249 " <td>N/A</td>\n",
2250 " </tr>\n",
2251 " <tr>\n",
2252 " <th>285</th>\n",
2253 " <td>LNC</td>\n",
2254 " <td>39.000</td>\n",
2255 " <td>N/A</td>\n",
2256 " <td>34.14</td>\n",
2257 " <td>N/A</td>\n",
2258 " <td>0.382368</td>\n",
2259 " <td>N/A</td>\n",
2260 " <td>NaN</td>\n",
2261 " <td>N/A</td>\n",
2262 " <td>NaN</td>\n",
2263 " <td>N/A</td>\n",
2264 " <td>N/A</td>\n",
2265 " </tr>\n",
2266 " <tr>\n",
2267 " <th>294</th>\n",
2268 " <td>MA</td>\n",
2269 " <td>326.780</td>\n",
2270 " <td>N/A</td>\n",
2271 " <td>46.44</td>\n",
2272 " <td>N/A</td>\n",
2273 " <td>57.878484</td>\n",
2274 " <td>N/A</td>\n",
2275 " <td>32.240726</td>\n",
2276 " <td>N/A</td>\n",
2277 " <td>NaN</td>\n",
2278 " <td>N/A</td>\n",
2279 " <td>N/A</td>\n",
2280 " </tr>\n",
2281 " <tr>\n",
2282 " <th>304</th>\n",
2283 " <td>MET</td>\n",
2284 " <td>40.060</td>\n",
2285 " <td>N/A</td>\n",
2286 " <td>5.15</td>\n",
2287 " <td>N/A</td>\n",
2288 " <td>0.560684</td>\n",
2289 " <td>N/A</td>\n",
2290 " <td>NaN</td>\n",
2291 " <td>N/A</td>\n",
2292 " <td>NaN</td>\n",
2293 " <td>N/A</td>\n",
2294 " <td>N/A</td>\n",
2295 " </tr>\n",
2296 " <tr>\n",
2297 " <th>310</th>\n",
2298 " <td>MMC</td>\n",
2299 " <td>121.870</td>\n",
2300 " <td>N/A</td>\n",
2301 " <td>29.5</td>\n",
2302 " <td>N/A</td>\n",
2303 " <td>7.745583</td>\n",
2304 " <td>N/A</td>\n",
2305 " <td>19.929889</td>\n",
2306 " <td>N/A</td>\n",
2307 " <td>NaN</td>\n",
2308 " <td>N/A</td>\n",
2309 " <td>N/A</td>\n",
2310 " </tr>\n",
2311 " <tr>\n",
2312 " <th>318</th>\n",
2313 " <td>MS</td>\n",
2314 " <td>53.890</td>\n",
2315 " <td>N/A</td>\n",
2316 " <td>9.4</td>\n",
2317 " <td>N/A</td>\n",
2318 " <td>1.019241</td>\n",
2319 " <td>N/A</td>\n",
2320 " <td>NaN</td>\n",
2321 " <td>N/A</td>\n",
2322 " <td>NaN</td>\n",
2323 " <td>N/A</td>\n",
2324 " <td>N/A</td>\n",
2325 " </tr>\n",
2326 " <tr>\n",
2327 " <th>322</th>\n",
2328 " <td>MTB</td>\n",
2329 " <td>109.100</td>\n",
2330 " <td>N/A</td>\n",
2331 " <td>10.14</td>\n",
2332 " <td>N/A</td>\n",
2333 " <td>0.908166</td>\n",
2334 " <td>N/A</td>\n",
2335 " <td>NaN</td>\n",
2336 " <td>N/A</td>\n",
2337 " <td>NaN</td>\n",
2338 " <td>N/A</td>\n",
2339 " <td>N/A</td>\n",
2340 " </tr>\n",
2341 " <tr>\n",
2342 " <th>343</th>\n",
2343 " <td>NTRS</td>\n",
2344 " <td>84.016</td>\n",
2345 " <td>N/A</td>\n",
2346 " <td>13.11</td>\n",
2347 " <td>N/A</td>\n",
2348 " <td>1.585778</td>\n",
2349 " <td>N/A</td>\n",
2350 " <td>NaN</td>\n",
2351 " <td>N/A</td>\n",
2352 " <td>NaN</td>\n",
2353 " <td>N/A</td>\n",
2354 " <td>N/A</td>\n",
2355 " </tr>\n",
2356 " <tr>\n",
2357 " <th>356</th>\n",
2358 " <td>OTIS</td>\n",
2359 " <td>65.000</td>\n",
2360 " <td>N/A</td>\n",
2361 " <td>None</td>\n",
2362 " <td>N/A</td>\n",
2363 " <td>16.466082</td>\n",
2364 " <td>N/A</td>\n",
2365 " <td>12.978635</td>\n",
2366 " <td>N/A</td>\n",
2367 " <td>7.134814</td>\n",
2368 " <td>N/A</td>\n",
2369 " <td>N/A</td>\n",
2370 " </tr>\n",
2371 " <tr>\n",
2372 " <th>360</th>\n",
2373 " <td>PBCT</td>\n",
2374 " <td>11.413</td>\n",
2375 " <td>N/A</td>\n",
2376 " <td>9.88</td>\n",
2377 " <td>N/A</td>\n",
2378 " <td>0.611706</td>\n",
2379 " <td>N/A</td>\n",
2380 " <td>NaN</td>\n",
2381 " <td>N/A</td>\n",
2382 " <td>NaN</td>\n",
2383 " <td>N/A</td>\n",
2384 " <td>N/A</td>\n",
2385 " </tr>\n",
2386 " <tr>\n",
2387 " <th>366</th>\n",
2388 " <td>PFG</td>\n",
2389 " <td>46.400</td>\n",
2390 " <td>N/A</td>\n",
2391 " <td>10</td>\n",
2392 " <td>N/A</td>\n",
2393 " <td>0.875822</td>\n",
2394 " <td>N/A</td>\n",
2395 " <td>NaN</td>\n",
2396 " <td>N/A</td>\n",
2397 " <td>NaN</td>\n",
2398 " <td>N/A</td>\n",
2399 " <td>N/A</td>\n",
2400 " </tr>\n",
2401 " <tr>\n",
2402 " <th>368</th>\n",
2403 " <td>PGR</td>\n",
2404 " <td>91.930</td>\n",
2405 " <td>N/A</td>\n",
2406 " <td>11.96</td>\n",
2407 " <td>N/A</td>\n",
2408 " <td>3.853690</td>\n",
2409 " <td>N/A</td>\n",
2410 " <td>NaN</td>\n",
2411 " <td>N/A</td>\n",
2412 " <td>NaN</td>\n",
2413 " <td>N/A</td>\n",
2414 " <td>N/A</td>\n",
2415 " </tr>\n",
2416 " <tr>\n",
2417 " <th>375</th>\n",
2418 " <td>PNC</td>\n",
2419 " <td>114.560</td>\n",
2420 " <td>N/A</td>\n",
2421 " <td>7</td>\n",
2422 " <td>N/A</td>\n",
2423 " <td>0.968309</td>\n",
2424 " <td>N/A</td>\n",
2425 " <td>NaN</td>\n",
2426 " <td>N/A</td>\n",
2427 " <td>NaN</td>\n",
2428 " <td>N/A</td>\n",
2429 " <td>N/A</td>\n",
2430 " </tr>\n",
2431 " <tr>\n",
2432 " <th>381</th>\n",
2433 " <td>PRU</td>\n",
2434 " <td>69.800</td>\n",
2435 " <td>N/A</td>\n",
2436 " <td>-130</td>\n",
2437 " <td>N/A</td>\n",
2438 " <td>0.442811</td>\n",
2439 " <td>N/A</td>\n",
2440 " <td>NaN</td>\n",
2441 " <td>N/A</td>\n",
2442 " <td>NaN</td>\n",
2443 " <td>N/A</td>\n",
2444 " <td>N/A</td>\n",
2445 " </tr>\n",
2446 " <tr>\n",
2447 " <th>391</th>\n",
2448 " <td>RE</td>\n",
2449 " <td>228.250</td>\n",
2450 " <td>N/A</td>\n",
2451 " <td>17.75</td>\n",
2452 " <td>N/A</td>\n",
2453 " <td>1.014116</td>\n",
2454 " <td>N/A</td>\n",
2455 " <td>NaN</td>\n",
2456 " <td>N/A</td>\n",
2457 " <td>NaN</td>\n",
2458 " <td>N/A</td>\n",
2459 " <td>N/A</td>\n",
2460 " </tr>\n",
2461 " <tr>\n",
2462 " <th>394</th>\n",
2463 " <td>RF</td>\n",
2464 " <td>12.050</td>\n",
2465 " <td>N/A</td>\n",
2466 " <td>17.46</td>\n",
2467 " <td>N/A</td>\n",
2468 " <td>0.711818</td>\n",
2469 " <td>N/A</td>\n",
2470 " <td>NaN</td>\n",
2471 " <td>N/A</td>\n",
2472 " <td>NaN</td>\n",
2473 " <td>N/A</td>\n",
2474 " <td>N/A</td>\n",
2475 " </tr>\n",
2476 " <tr>\n",
2477 " <th>410</th>\n",
2478 " <td>SIVB</td>\n",
2479 " <td>257.970</td>\n",
2480 " <td>N/A</td>\n",
2481 " <td>14.96</td>\n",
2482 " <td>N/A</td>\n",
2483 " <td>2.029366</td>\n",
2484 " <td>N/A</td>\n",
2485 " <td>NaN</td>\n",
2486 " <td>N/A</td>\n",
2487 " <td>NaN</td>\n",
2488 " <td>N/A</td>\n",
2489 " <td>N/A</td>\n",
2490 " </tr>\n",
2491 " <tr>\n",
2492 " <th>421</th>\n",
2493 " <td>STT</td>\n",
2494 " <td>71.170</td>\n",
2495 " <td>N/A</td>\n",
2496 " <td>11.32</td>\n",
2497 " <td>N/A</td>\n",
2498 " <td>1.018320</td>\n",
2499 " <td>N/A</td>\n",
2500 " <td>NaN</td>\n",
2501 " <td>N/A</td>\n",
2502 " <td>NaN</td>\n",
2503 " <td>N/A</td>\n",
2504 " <td>N/A</td>\n",
2505 " </tr>\n",
2506 " <tr>\n",
2507 " <th>434</th>\n",
2508 " <td>TFC</td>\n",
2509 " <td>39.360</td>\n",
2510 " <td>N/A</td>\n",
2511 " <td>12.85</td>\n",
2512 " <td>N/A</td>\n",
2513 " <td>0.820157</td>\n",
2514 " <td>N/A</td>\n",
2515 " <td>NaN</td>\n",
2516 " <td>N/A</td>\n",
2517 " <td>NaN</td>\n",
2518 " <td>N/A</td>\n",
2519 " <td>N/A</td>\n",
2520 " </tr>\n",
2521 " <tr>\n",
2522 " <th>443</th>\n",
2523 " <td>TRV</td>\n",
2524 " <td>123.090</td>\n",
2525 " <td>N/A</td>\n",
2526 " <td>17.09</td>\n",
2527 " <td>N/A</td>\n",
2528 " <td>1.165263</td>\n",
2529 " <td>N/A</td>\n",
2530 " <td>NaN</td>\n",
2531 " <td>N/A</td>\n",
2532 " <td>NaN</td>\n",
2533 " <td>N/A</td>\n",
2534 " <td>N/A</td>\n",
2535 " </tr>\n",
2536 " <tr>\n",
2537 " <th>458</th>\n",
2538 " <td>UNH</td>\n",
2539 " <td>337.050</td>\n",
2540 " <td>N/A</td>\n",
2541 " <td>18.69</td>\n",
2542 " <td>N/A</td>\n",
2543 " <td>5.428035</td>\n",
2544 " <td>N/A</td>\n",
2545 " <td>NaN</td>\n",
2546 " <td>N/A</td>\n",
2547 " <td>NaN</td>\n",
2548 " <td>N/A</td>\n",
2549 " <td>N/A</td>\n",
2550 " </tr>\n",
2551 " <tr>\n",
2552 " <th>459</th>\n",
2553 " <td>UNM</td>\n",
2554 " <td>19.580</td>\n",
2555 " <td>N/A</td>\n",
2556 " <td>4.12</td>\n",
2557 " <td>N/A</td>\n",
2558 " <td>0.392314</td>\n",
2559 " <td>N/A</td>\n",
2560 " <td>NaN</td>\n",
2561 " <td>N/A</td>\n",
2562 " <td>NaN</td>\n",
2563 " <td>N/A</td>\n",
2564 " <td>N/A</td>\n",
2565 " </tr>\n",
2566 " <tr>\n",
2567 " <th>463</th>\n",
2568 " <td>USB</td>\n",
2569 " <td>38.150</td>\n",
2570 " <td>N/A</td>\n",
2571 " <td>12.17</td>\n",
2572 " <td>N/A</td>\n",
2573 " <td>1.099607</td>\n",
2574 " <td>N/A</td>\n",
2575 " <td>NaN</td>\n",
2576 " <td>N/A</td>\n",
2577 " <td>NaN</td>\n",
2578 " <td>N/A</td>\n",
2579 " <td>N/A</td>\n",
2580 " </tr>\n",
2581 " <tr>\n",
2582 " <th>482</th>\n",
2583 " <td>WFC</td>\n",
2584 " <td>25.670</td>\n",
2585 " <td>N/A</td>\n",
2586 " <td>29.3</td>\n",
2587 " <td>N/A</td>\n",
2588 " <td>0.566555</td>\n",
2589 " <td>N/A</td>\n",
2590 " <td>NaN</td>\n",
2591 " <td>N/A</td>\n",
2592 " <td>NaN</td>\n",
2593 " <td>N/A</td>\n",
2594 " <td>N/A</td>\n",
2595 " </tr>\n",
2596 " <tr>\n",
2597 " <th>488</th>\n",
2598 " <td>WRB</td>\n",
2599 " <td>63.440</td>\n",
2600 " <td>N/A</td>\n",
2601 " <td>34.45</td>\n",
2602 " <td>N/A</td>\n",
2603 " <td>1.877291</td>\n",
2604 " <td>N/A</td>\n",
2605 " <td>NaN</td>\n",
2606 " <td>N/A</td>\n",
2607 " <td>NaN</td>\n",
2608 " <td>N/A</td>\n",
2609 " <td>N/A</td>\n",
2610 " </tr>\n",
2611 " <tr>\n",
2612 " <th>503</th>\n",
2613 " <td>ZION</td>\n",
2614 " <td>35.770</td>\n",
2615 " <td>N/A</td>\n",
2616 " <td>13.24</td>\n",
2617 " <td>N/A</td>\n",
2618 " <td>0.766237</td>\n",
2619 " <td>N/A</td>\n",
2620 " <td>NaN</td>\n",
2621 " <td>N/A</td>\n",
2622 " <td>NaN</td>\n",
2623 " <td>N/A</td>\n",
2624 " <td>N/A</td>\n",
2625 " </tr>\n",
2626 " </tbody>\n",
2627 "</table>\n",
2628 "</div>"
2629 ],
2630 "text/plain": [
2631 " Ticker Price Number of Shares to Buy Price-to-Earnings Ratio \\\n",
2632 "17 AFL 39.060 N/A 9.7 \n",
2633 "18 AIG 31.950 N/A -5.88 \n",
2634 "20 AIZ 127.680 N/A 20.22 \n",
2635 "26 ALL 97.880 N/A 7.1 \n",
2636 "39 ANTM 293.560 N/A 12.24 \n",
2637 "40 AON 196.350 N/A 26.09 \n",
2638 "56 BAC 26.970 N/A 12.63 \n",
2639 "64 BK 38.160 N/A 8.27 \n",
2640 "65 BKNG 1866.170 N/A 30.78 \n",
2641 "75 C 55.140 N/A 9.52 \n",
2642 "78 CARR 30.210 N/A None \n",
2643 "80 CB 132.400 N/A 27.62 \n",
2644 "90 CFG 26.840 N/A 10.71 \n",
2645 "94 CI 188.390 N/A 13.31 \n",
2646 "95 CINF 80.210 N/A 23.51 \n",
2647 "97 CLX 232.200 N/A 30.25 \n",
2648 "98 CMA 43.270 N/A 10.2 \n",
2649 "104 CNC 65.780 N/A 22.88 \n",
2650 "106 COF 67.830 N/A -195.92 \n",
2651 "129 DFS 55.920 N/A 17.68 \n",
2652 "185 FITB 21.043 N/A 10.87 \n",
2653 "192 FRC 123.410 N/A 22.93 \n",
2654 "201 GL 85.320 N/A 13.09 \n",
2655 "210 GS 211.320 N/A 16.21 \n",
2656 "214 HBAN 10.179 N/A 12.49 \n",
2657 "220 HIG 42.840 N/A 8.7 \n",
2658 "232 HUM 427.300 N/A 15.96 \n",
2659 "260 JPM 103.310 N/A 14.29 \n",
2660 "262 KEY 13.180 N/A 11.35 \n",
2661 "274 L 38.130 N/A -9.39 \n",
2662 "285 LNC 39.000 N/A 34.14 \n",
2663 "294 MA 326.780 N/A 46.44 \n",
2664 "304 MET 40.060 N/A 5.15 \n",
2665 "310 MMC 121.870 N/A 29.5 \n",
2666 "318 MS 53.890 N/A 9.4 \n",
2667 "322 MTB 109.100 N/A 10.14 \n",
2668 "343 NTRS 84.016 N/A 13.11 \n",
2669 "356 OTIS 65.000 N/A None \n",
2670 "360 PBCT 11.413 N/A 9.88 \n",
2671 "366 PFG 46.400 N/A 10 \n",
2672 "368 PGR 91.930 N/A 11.96 \n",
2673 "375 PNC 114.560 N/A 7 \n",
2674 "381 PRU 69.800 N/A -130 \n",
2675 "391 RE 228.250 N/A 17.75 \n",
2676 "394 RF 12.050 N/A 17.46 \n",
2677 "410 SIVB 257.970 N/A 14.96 \n",
2678 "421 STT 71.170 N/A 11.32 \n",
2679 "434 TFC 39.360 N/A 12.85 \n",
2680 "443 TRV 123.090 N/A 17.09 \n",
2681 "458 UNH 337.050 N/A 18.69 \n",
2682 "459 UNM 19.580 N/A 4.12 \n",
2683 "463 USB 38.150 N/A 12.17 \n",
2684 "482 WFC 25.670 N/A 29.3 \n",
2685 "488 WRB 63.440 N/A 34.45 \n",
2686 "503 ZION 35.770 N/A 13.24 \n",
2687 "\n",
2688 " PE Percentile Price-to-Book Ratio PB Percentile EV/EBITDA \\\n",
2689 "17 N/A 0.950934 N/A NaN \n",
2690 "18 N/A 0.407454 N/A NaN \n",
2691 "20 N/A 1.360264 N/A NaN \n",
2692 "26 N/A 1.173772 N/A NaN \n",
2693 "39 N/A 2.304037 N/A NaN \n",
2694 "40 N/A 13.486066 N/A 17.533688 \n",
2695 "56 N/A 0.867662 N/A NaN \n",
2696 "64 N/A 0.804550 N/A NaN \n",
2697 "65 N/A 12.571484 N/A 12.859665 \n",
2698 "75 N/A 0.576968 N/A NaN \n",
2699 "78 N/A 1.884398 N/A 9.144195 \n",
2700 "80 N/A 1.066146 N/A NaN \n",
2701 "90 N/A 0.501914 N/A NaN \n",
2702 "94 N/A 1.477180 N/A NaN \n",
2703 "95 N/A 1.305219 N/A NaN \n",
2704 "97 N/A 32.278899 N/A NaN \n",
2705 "98 N/A 0.801751 N/A NaN \n",
2706 "104 N/A 2.982878 N/A NaN \n",
2707 "106 N/A 0.534970 N/A NaN \n",
2708 "129 N/A 1.456734 N/A NaN \n",
2709 "185 N/A 0.717095 N/A NaN \n",
2710 "192 N/A 2.163006 N/A NaN \n",
2711 "201 N/A 1.266177 N/A NaN \n",
2712 "210 N/A 0.813961 N/A NaN \n",
2713 "214 N/A 0.855431 N/A NaN \n",
2714 "220 N/A 0.959808 N/A NaN \n",
2715 "232 N/A 4.713176 N/A NaN \n",
2716 "260 N/A 1.213231 N/A NaN \n",
2717 "262 N/A 0.752496 N/A NaN \n",
2718 "274 N/A 0.573510 N/A NaN \n",
2719 "285 N/A 0.382368 N/A NaN \n",
2720 "294 N/A 57.878484 N/A 32.240726 \n",
2721 "304 N/A 0.560684 N/A NaN \n",
2722 "310 N/A 7.745583 N/A 19.929889 \n",
2723 "318 N/A 1.019241 N/A NaN \n",
2724 "322 N/A 0.908166 N/A NaN \n",
2725 "343 N/A 1.585778 N/A NaN \n",
2726 "356 N/A 16.466082 N/A 12.978635 \n",
2727 "360 N/A 0.611706 N/A NaN \n",
2728 "366 N/A 0.875822 N/A NaN \n",
2729 "368 N/A 3.853690 N/A NaN \n",
2730 "375 N/A 0.968309 N/A NaN \n",
2731 "381 N/A 0.442811 N/A NaN \n",
2732 "391 N/A 1.014116 N/A NaN \n",
2733 "394 N/A 0.711818 N/A NaN \n",
2734 "410 N/A 2.029366 N/A NaN \n",
2735 "421 N/A 1.018320 N/A NaN \n",
2736 "434 N/A 0.820157 N/A NaN \n",
2737 "443 N/A 1.165263 N/A NaN \n",
2738 "458 N/A 5.428035 N/A NaN \n",
2739 "459 N/A 0.392314 N/A NaN \n",
2740 "463 N/A 1.099607 N/A NaN \n",
2741 "482 N/A 0.566555 N/A NaN \n",
2742 "488 N/A 1.877291 N/A NaN \n",
2743 "503 N/A 0.766237 N/A NaN \n",
2744 "\n",
2745 " EV/EBITDA Percentile EV/GP EV/GP Percentile RV Score \n",
2746 "17 N/A NaN N/A N/A \n",
2747 "18 N/A NaN N/A N/A \n",
2748 "20 N/A NaN N/A N/A \n",
2749 "26 N/A NaN N/A N/A \n",
2750 "39 N/A NaN N/A N/A \n",
2751 "40 N/A NaN N/A N/A \n",
2752 "56 N/A NaN N/A N/A \n",
2753 "64 N/A NaN N/A N/A \n",
2754 "65 N/A NaN N/A N/A \n",
2755 "75 N/A NaN N/A N/A \n",
2756 "78 N/A 4.550543 N/A N/A \n",
2757 "80 N/A NaN N/A N/A \n",
2758 "90 N/A NaN N/A N/A \n",
2759 "94 N/A NaN N/A N/A \n",
2760 "95 N/A NaN N/A N/A \n",
2761 "97 N/A 9.790028 N/A N/A \n",
2762 "98 N/A NaN N/A N/A \n",
2763 "104 N/A NaN N/A N/A \n",
2764 "106 N/A NaN N/A N/A \n",
2765 "129 N/A NaN N/A N/A \n",
2766 "185 N/A NaN N/A N/A \n",
2767 "192 N/A NaN N/A N/A \n",
2768 "201 N/A NaN N/A N/A \n",
2769 "210 N/A NaN N/A N/A \n",
2770 "214 N/A NaN N/A N/A \n",
2771 "220 N/A NaN N/A N/A \n",
2772 "232 N/A NaN N/A N/A \n",
2773 "260 N/A NaN N/A N/A \n",
2774 "262 N/A NaN N/A N/A \n",
2775 "274 N/A NaN N/A N/A \n",
2776 "285 N/A NaN N/A N/A \n",
2777 "294 N/A NaN N/A N/A \n",
2778 "304 N/A NaN N/A N/A \n",
2779 "310 N/A NaN N/A N/A \n",
2780 "318 N/A NaN N/A N/A \n",
2781 "322 N/A NaN N/A N/A \n",
2782 "343 N/A NaN N/A N/A \n",
2783 "356 N/A 7.134814 N/A N/A \n",
2784 "360 N/A NaN N/A N/A \n",
2785 "366 N/A NaN N/A N/A \n",
2786 "368 N/A NaN N/A N/A \n",
2787 "375 N/A NaN N/A N/A \n",
2788 "381 N/A NaN N/A N/A \n",
2789 "391 N/A NaN N/A N/A \n",
2790 "394 N/A NaN N/A N/A \n",
2791 "410 N/A NaN N/A N/A \n",
2792 "421 N/A NaN N/A N/A \n",
2793 "434 N/A NaN N/A N/A \n",
2794 "443 N/A NaN N/A N/A \n",
2795 "458 N/A NaN N/A N/A \n",
2796 "459 N/A NaN N/A N/A \n",
2797 "463 N/A NaN N/A N/A \n",
2798 "482 N/A NaN N/A N/A \n",
2799 "488 N/A NaN N/A N/A \n",
2800 "503 N/A NaN N/A N/A "
2801 ]
2802 },
2803 "execution_count": 14,
2804 "metadata": {},
2805 "output_type": "execute_result"
2806 }
2807 ],
2808 "source": [
2809 "rv_dataframe[rv_dataframe.isnull().any(axis=1)]"
2810 ]
2811 },
2812 {
2813 "cell_type": "markdown",
2814 "metadata": {},
2815 "source": [
2816 "Dealing with missing data is an important topic in data science.\n",
2817 "\n",
2818 "There are two main approaches:\n",
2819 "\n",
2820 "* Drop missing data from the data set (pandas' `dropna` method is useful here)\n",
2821 "* Replace missing data with a new value (pandas' `fillna` method is useful here)\n",
2822 "\n",
2823 "In this tutorial, we will replace missing data with the average non-`NaN` data point from that column. \n",
2824 "\n",
2825 "Here is the code to do this:"
2826 ]
2827 },
2828 {
2829 "cell_type": "code",
2830 "execution_count": 15,
2831 "metadata": {},
2832 "outputs": [],
2833 "source": [
2834 "for column in ['Price-to-Earnings Ratio', 'Price-to-Book Ratio','Price-to-Sales Ratio', 'EV/EBITDA','EV/GP']:\n",
2835 " rv_dataframe[column].fillna(rv_dataframe[column].mean(), inplace = True)"
2836 ]
2837 },
2838 {
2839 "cell_type": "markdown",
2840 "metadata": {},
2841 "source": [
2842 "Now, if we run the statement from earlier to print rows that contain missing data, nothing should be returned:"
2843 ]
2844 },
2845 {
2846 "cell_type": "code",
2847 "execution_count": 16,
2848 "metadata": {},
2849 "outputs": [
2850 {
2851 "data": {
2852 "text/html": [
2853 "<div>\n",
2854 "<style scoped>\n",
2855 " .dataframe tbody tr th:only-of-type {\n",
2856 " vertical-align: middle;\n",
2857 " }\n",
2858 "\n",
2859 " .dataframe tbody tr th {\n",
2860 " vertical-align: top;\n",
2861 " }\n",
2862 "\n",
2863 " .dataframe thead th {\n",
2864 " text-align: right;\n",
2865 " }\n",
2866 "</style>\n",
2867 "<table border=\"1\" class=\"dataframe\">\n",
2868 " <thead>\n",
2869 " <tr style=\"text-align: right;\">\n",
2870 " <th></th>\n",
2871 " <th>Ticker</th>\n",
2872 " <th>Price</th>\n",
2873 " <th>Number of Shares to Buy</th>\n",
2874 " <th>Price-to-Earnings Ratio</th>\n",
2875 " <th>PE Percentile</th>\n",
2876 " <th>Price-to-Book Ratio</th>\n",
2877 " <th>PB Percentile</th>\n",
2878 " <th>EV/EBITDA</th>\n",
2879 " <th>EV/EBITDA Percentile</th>\n",
2880 " <th>EV/GP</th>\n",
2881 " <th>EV/GP Percentile</th>\n",
2882 " <th>RV Score</th>\n",
2883 " </tr>\n",
2884 " </thead>\n",
2885 " <tbody>\n",
2886 " </tbody>\n",
2887 "</table>\n",
2888 "</div>"
2889 ],
2890 "text/plain": [
2891 "Empty DataFrame\n",
2892 "Columns: [Ticker, Price, Number of Shares to Buy, Price-to-Earnings Ratio, PE Percentile, Price-to-Book Ratio, PB Percentile, EV/EBITDA, EV/EBITDA Percentile, EV/GP, EV/GP Percentile, RV Score]\n",
2893 "Index: []"
2894 ]
2895 },
2896 "execution_count": 16,
2897 "metadata": {},
2898 "output_type": "execute_result"
2899 }
2900 ],
2901 "source": [
2902 "rv_dataframe[rv_dataframe.isnull().any(axis=1)]"
2903 ]
2904 },
2905 {
2906 "cell_type": "markdown",
2907 "metadata": {},
2908 "source": [
2909 "## Calculating Value Percentiles\n",
2910 "\n",
2911 "We now need to calculate value score percentiles for every stock in the universe. More specifically, we need to calculate percentile scores for the following metrics for every stock:\n",
2912 "\n",
2913 "* Price-to-earnings ratio\n",
2914 "* Price-to-book ratio\n",
2915 "* Price-to-sales ratio\n",
2916 "* EV/EBITDA\n",
2917 "* EV/GP\n",
2918 "\n",
2919 "Here's how we'll do this:"
2920 ]
2921 },
2922 {
2923 "cell_type": "code",
2924 "execution_count": 17,
2925 "metadata": {},
2926 "outputs": [
2927 {
2928 "name": "stdout",
2929 "output_type": "stream",
2930 "text": [
2931 "0 0.841584\n",
2932 "1 0.112871\n",
2933 "2 0.623762\n",
2934 "3 0.740594\n",
2935 "4 0.427723\n",
2936 " ... \n",
2937 "500 0.6\n",
2938 "501 0.994059\n",
2939 "502 0.693069\n",
2940 "503 0.257426\n",
2941 "504 0.843564\n",
2942 "Name: PE Percentile, Length: 505, dtype: object\n",
2943 "0 0.752475\n",
2944 "1 0.0158416\n",
2945 "2 0.510891\n",
2946 "3 0.940594\n",
2947 "4 0.0257426\n",
2948 " ... \n",
2949 "500 0.049505\n",
2950 "501 0.415842\n",
2951 "502 0.811881\n",
2952 "503 0.132673\n",
2953 "504 0.956436\n",
2954 "Name: PB Percentile, Length: 505, dtype: object\n",
2955 "0 0.877228\n",
2956 "1 0.0732673\n",
2957 "2 0.50099\n",
2958 "3 0.861386\n",
2959 "4 0.350495\n",
2960 " ... \n",
2961 "500 0.744554\n",
2962 "501 0.572277\n",
2963 "502 0.762376\n",
2964 "503 0.69505\n",
2965 "504 0.924752\n",
2966 "Name: EV/EBITDA Percentile, Length: 505, dtype: object\n",
2967 "0 0.552475\n",
2968 "1 0.0574257\n",
2969 "2 0.0653465\n",
2970 "3 0.865347\n",
2971 "4 0.340594\n",
2972 " ... \n",
2973 "500 0.744554\n",
2974 "501 0.326733\n",
2975 "502 0.4\n",
2976 "503 0.644554\n",
2977 "504 0.869307\n",
2978 "Name: EV/GP Percentile, Length: 505, dtype: object\n"
2979 ]
2980 },
2981 {
2982 "data": {
2983 "text/html": [
2984 "<div>\n",
2985 "<style scoped>\n",
2986 " .dataframe tbody tr th:only-of-type {\n",
2987 " vertical-align: middle;\n",
2988 " }\n",
2989 "\n",
2990 " .dataframe tbody tr th {\n",
2991 " vertical-align: top;\n",
2992 " }\n",
2993 "\n",
2994 " .dataframe thead th {\n",
2995 " text-align: right;\n",
2996 " }\n",
2997 "</style>\n",
2998 "<table border=\"1\" class=\"dataframe\">\n",
2999 " <thead>\n",
3000 " <tr style=\"text-align: right;\">\n",
3001 " <th></th>\n",
3002 " <th>Ticker</th>\n",
3003 " <th>Price</th>\n",
3004 " <th>Number of Shares to Buy</th>\n",
3005 " <th>Price-to-Earnings Ratio</th>\n",
3006 " <th>PE Percentile</th>\n",
3007 " <th>Price-to-Book Ratio</th>\n",
3008 " <th>PB Percentile</th>\n",
3009 " <th>EV/EBITDA</th>\n",
3010 " <th>EV/EBITDA Percentile</th>\n",
3011 " <th>EV/GP</th>\n",
3012 " <th>EV/GP Percentile</th>\n",
3013 " <th>RV Score</th>\n",
3014 " </tr>\n",
3015 " </thead>\n",
3016 " <tbody>\n",
3017 " <tr>\n",
3018 " <th>0</th>\n",
3019 " <td>A</td>\n",
3020 " <td>100.010</td>\n",
3021 " <td>N/A</td>\n",
3022 " <td>46.95</td>\n",
3023 " <td>0.841584</td>\n",
3024 " <td>6.596140</td>\n",
3025 " <td>0.752475</td>\n",
3026 " <td>26.372976</td>\n",
3027 " <td>0.877228</td>\n",
3028 " <td>11.311629</td>\n",
3029 " <td>0.552475</td>\n",
3030 " <td>N/A</td>\n",
3031 " </tr>\n",
3032 " <tr>\n",
3033 " <th>1</th>\n",
3034 " <td>AAL</td>\n",
3035 " <td>13.360</td>\n",
3036 " <td>N/A</td>\n",
3037 " <td>-1.71</td>\n",
3038 " <td>0.112871</td>\n",
3039 " <td>-60.417952</td>\n",
3040 " <td>0.0158416</td>\n",
3041 " <td>5.952664</td>\n",
3042 " <td>0.0732673</td>\n",
3043 " <td>3.098483</td>\n",
3044 " <td>0.0574257</td>\n",
3045 " <td>N/A</td>\n",
3046 " </tr>\n",
3047 " <tr>\n",
3048 " <th>2</th>\n",
3049 " <td>AAP</td>\n",
3050 " <td>161.440</td>\n",
3051 " <td>N/A</td>\n",
3052 " <td>29.00</td>\n",
3053 " <td>0.623762</td>\n",
3054 " <td>3.123759</td>\n",
3055 " <td>0.510891</td>\n",
3056 " <td>15.086368</td>\n",
3057 " <td>0.50099</td>\n",
3058 " <td>3.208667</td>\n",
3059 " <td>0.0653465</td>\n",
3060 " <td>N/A</td>\n",
3061 " </tr>\n",
3062 " <tr>\n",
3063 " <th>3</th>\n",
3064 " <td>AAPL</td>\n",
3065 " <td>466.070</td>\n",
3066 " <td>N/A</td>\n",
3067 " <td>34.69</td>\n",
3068 " <td>0.740594</td>\n",
3069 " <td>22.373999</td>\n",
3070 " <td>0.940594</td>\n",
3071 " <td>25.708773</td>\n",
3072 " <td>0.861386</td>\n",
3073 " <td>19.428993</td>\n",
3074 " <td>0.865347</td>\n",
3075 " <td>N/A</td>\n",
3076 " </tr>\n",
3077 " <tr>\n",
3078 " <th>4</th>\n",
3079 " <td>ABBV</td>\n",
3080 " <td>96.680</td>\n",
3081 " <td>N/A</td>\n",
3082 " <td>21.00</td>\n",
3083 " <td>0.427723</td>\n",
3084 " <td>-21.463532</td>\n",
3085 " <td>0.0257426</td>\n",
3086 " <td>12.272585</td>\n",
3087 " <td>0.350495</td>\n",
3088 " <td>7.672427</td>\n",
3089 " <td>0.340594</td>\n",
3090 " <td>N/A</td>\n",
3091 " </tr>\n",
3092 " <tr>\n",
3093 " <th>...</th>\n",
3094 " <td>...</td>\n",
3095 " <td>...</td>\n",
3096 " <td>...</td>\n",
3097 " <td>...</td>\n",
3098 " <td>...</td>\n",
3099 " <td>...</td>\n",
3100 " <td>...</td>\n",
3101 " <td>...</td>\n",
3102 " <td>...</td>\n",
3103 " <td>...</td>\n",
3104 " <td>...</td>\n",
3105 " <td>...</td>\n",
3106 " </tr>\n",
3107 " <tr>\n",
3108 " <th>500</th>\n",
3109 " <td>YUM</td>\n",
3110 " <td>94.320</td>\n",
3111 " <td>N/A</td>\n",
3112 " <td>28.00</td>\n",
3113 " <td>0.6</td>\n",
3114 " <td>-3.659682</td>\n",
3115 " <td>0.049505</td>\n",
3116 " <td>18.841249</td>\n",
3117 " <td>0.744554</td>\n",
3118 " <td>13.891510</td>\n",
3119 " <td>0.744554</td>\n",
3120 " <td>N/A</td>\n",
3121 " </tr>\n",
3122 " <tr>\n",
3123 " <th>501</th>\n",
3124 " <td>ZBH</td>\n",
3125 " <td>143.470</td>\n",
3126 " <td>N/A</td>\n",
3127 " <td>718.10</td>\n",
3128 " <td>0.994059</td>\n",
3129 " <td>2.390128</td>\n",
3130 " <td>0.415842</td>\n",
3131 " <td>17.170711</td>\n",
3132 " <td>0.572277</td>\n",
3133 " <td>7.478460</td>\n",
3134 " <td>0.326733</td>\n",
3135 " <td>N/A</td>\n",
3136 " </tr>\n",
3137 " <tr>\n",
3138 " <th>502</th>\n",
3139 " <td>ZBRA</td>\n",
3140 " <td>288.222</td>\n",
3141 " <td>N/A</td>\n",
3142 " <td>31.86</td>\n",
3143 " <td>0.693069</td>\n",
3144 " <td>8.600669</td>\n",
3145 " <td>0.811881</td>\n",
3146 " <td>19.480804</td>\n",
3147 " <td>0.762376</td>\n",
3148 " <td>8.449885</td>\n",
3149 " <td>0.4</td>\n",
3150 " <td>N/A</td>\n",
3151 " </tr>\n",
3152 " <tr>\n",
3153 " <th>503</th>\n",
3154 " <td>ZION</td>\n",
3155 " <td>35.770</td>\n",
3156 " <td>N/A</td>\n",
3157 " <td>13.24</td>\n",
3158 " <td>0.257426</td>\n",
3159 " <td>0.766237</td>\n",
3160 " <td>0.132673</td>\n",
3161 " <td>18.729176</td>\n",
3162 " <td>0.69505</td>\n",
3163 " <td>12.206556</td>\n",
3164 " <td>0.644554</td>\n",
3165 " <td>N/A</td>\n",
3166 " </tr>\n",
3167 " <tr>\n",
3168 " <th>504</th>\n",
3169 " <td>ZTS</td>\n",
3170 " <td>160.920</td>\n",
3171 " <td>N/A</td>\n",
3172 " <td>47.20</td>\n",
3173 " <td>0.843564</td>\n",
3174 " <td>29.222282</td>\n",
3175 " <td>0.956436</td>\n",
3176 " <td>32.483029</td>\n",
3177 " <td>0.924752</td>\n",
3178 " <td>19.667225</td>\n",
3179 " <td>0.869307</td>\n",
3180 " <td>N/A</td>\n",
3181 " </tr>\n",
3182 " </tbody>\n",
3183 "</table>\n",
3184 "<p>505 rows × 12 columns</p>\n",
3185 "</div>"
3186 ],
3187 "text/plain": [
3188 " Ticker Price Number of Shares to Buy Price-to-Earnings Ratio \\\n",
3189 "0 A 100.010 N/A 46.95 \n",
3190 "1 AAL 13.360 N/A -1.71 \n",
3191 "2 AAP 161.440 N/A 29.00 \n",
3192 "3 AAPL 466.070 N/A 34.69 \n",
3193 "4 ABBV 96.680 N/A 21.00 \n",
3194 ".. ... ... ... ... \n",
3195 "500 YUM 94.320 N/A 28.00 \n",
3196 "501 ZBH 143.470 N/A 718.10 \n",
3197 "502 ZBRA 288.222 N/A 31.86 \n",
3198 "503 ZION 35.770 N/A 13.24 \n",
3199 "504 ZTS 160.920 N/A 47.20 \n",
3200 "\n",
3201 " PE Percentile Price-to-Book Ratio PB Percentile EV/EBITDA \\\n",
3202 "0 0.841584 6.596140 0.752475 26.372976 \n",
3203 "1 0.112871 -60.417952 0.0158416 5.952664 \n",
3204 "2 0.623762 3.123759 0.510891 15.086368 \n",
3205 "3 0.740594 22.373999 0.940594 25.708773 \n",
3206 "4 0.427723 -21.463532 0.0257426 12.272585 \n",
3207 ".. ... ... ... ... \n",
3208 "500 0.6 -3.659682 0.049505 18.841249 \n",
3209 "501 0.994059 2.390128 0.415842 17.170711 \n",
3210 "502 0.693069 8.600669 0.811881 19.480804 \n",
3211 "503 0.257426 0.766237 0.132673 18.729176 \n",
3212 "504 0.843564 29.222282 0.956436 32.483029 \n",
3213 "\n",
3214 " EV/EBITDA Percentile EV/GP EV/GP Percentile RV Score \n",
3215 "0 0.877228 11.311629 0.552475 N/A \n",
3216 "1 0.0732673 3.098483 0.0574257 N/A \n",
3217 "2 0.50099 3.208667 0.0653465 N/A \n",
3218 "3 0.861386 19.428993 0.865347 N/A \n",
3219 "4 0.350495 7.672427 0.340594 N/A \n",
3220 ".. ... ... ... ... \n",
3221 "500 0.744554 13.891510 0.744554 N/A \n",
3222 "501 0.572277 7.478460 0.326733 N/A \n",
3223 "502 0.762376 8.449885 0.4 N/A \n",
3224 "503 0.69505 12.206556 0.644554 N/A \n",
3225 "504 0.924752 19.667225 0.869307 N/A \n",
3226 "\n",
3227 "[505 rows x 12 columns]"
3228 ]
3229 },
3230 "execution_count": 17,
3231 "metadata": {},
3232 "output_type": "execute_result"
3233 }
3234 ],
3235 "source": [
3236 "metrics = {\n",
3237 " 'Price-to-Earnings Ratio': 'PE Percentile',\n",
3238 " 'Price-to-Book Ratio':'PB Percentile',\n",
3239 " 'Price-to-Sales Ratio': 'PS Percentile',\n",
3240 " 'EV/EBITDA':'EV/EBITDA Percentile',\n",
3241 " 'EV/GP':'EV/GP Percentile'\n",
3242 "}\n",
3243 "\n",
3244 "for row in rv_dataframe.index:\n",
3245 " for metric in metrics.keys():\n",
3246 " rv_dataframe.loc[row, metrics[metric]] = stats.percentileofscore(rv_dataframe[metric], rv_dataframe.loc[row, metric])/100\n",
3247 "\n",
3248 "# Print each percentile score to make sure it was calculated properly\n",
3249 "for metric in metrics.values():\n",
3250 " print(rv_dataframe[metric])\n",
3251 "\n",
3252 "#Print the entire DataFrame \n",
3253 "rv_dataframe"
3254 ]
3255 },
3256 {
3257 "cell_type": "markdown",
3258 "metadata": {},
3259 "source": [
3260 "## Calculating the RV Score\n",
3261 "We'll now calculate our RV Score (which stands for Robust Value), which is the value score that we'll use to filter for stocks in this investing strategy.\n",
3262 "\n",
3263 "The RV Score will be the arithmetic mean of the 4 percentile scores that we calculated in the last section.\n",
3264 "\n",
3265 "To calculate arithmetic mean, we will use the mean function from Python's built-in statistics module."
3266 ]
3267 },
3268 {
3269 "cell_type": "code",
3270 "execution_count": 18,
3271 "metadata": {},
3272 "outputs": [
3273 {
3274 "data": {
3275 "text/html": [
3276 "<div>\n",
3277 "<style scoped>\n",
3278 " .dataframe tbody tr th:only-of-type {\n",
3279 " vertical-align: middle;\n",
3280 " }\n",
3281 "\n",
3282 " .dataframe tbody tr th {\n",
3283 " vertical-align: top;\n",
3284 " }\n",
3285 "\n",
3286 " .dataframe thead th {\n",
3287 " text-align: right;\n",
3288 " }\n",
3289 "</style>\n",
3290 "<table border=\"1\" class=\"dataframe\">\n",
3291 " <thead>\n",
3292 " <tr style=\"text-align: right;\">\n",
3293 " <th></th>\n",
3294 " <th>Ticker</th>\n",
3295 " <th>Price</th>\n",
3296 " <th>Number of Shares to Buy</th>\n",
3297 " <th>Price-to-Earnings Ratio</th>\n",
3298 " <th>PE Percentile</th>\n",
3299 " <th>Price-to-Book Ratio</th>\n",
3300 " <th>PB Percentile</th>\n",
3301 " <th>EV/EBITDA</th>\n",
3302 " <th>EV/EBITDA Percentile</th>\n",
3303 " <th>EV/GP</th>\n",
3304 " <th>EV/GP Percentile</th>\n",
3305 " <th>RV Score</th>\n",
3306 " </tr>\n",
3307 " </thead>\n",
3308 " <tbody>\n",
3309 " <tr>\n",
3310 " <th>0</th>\n",
3311 " <td>A</td>\n",
3312 " <td>100.010</td>\n",
3313 " <td>N/A</td>\n",
3314 " <td>46.95</td>\n",
3315 " <td>0.841584</td>\n",
3316 " <td>6.596140</td>\n",
3317 " <td>0.752475</td>\n",
3318 " <td>26.372976</td>\n",
3319 " <td>0.877228</td>\n",
3320 " <td>11.311629</td>\n",
3321 " <td>0.552475</td>\n",
3322 " <td>0.755941</td>\n",
3323 " </tr>\n",
3324 " <tr>\n",
3325 " <th>1</th>\n",
3326 " <td>AAL</td>\n",
3327 " <td>13.360</td>\n",
3328 " <td>N/A</td>\n",
3329 " <td>-1.71</td>\n",
3330 " <td>0.112871</td>\n",
3331 " <td>-60.417952</td>\n",
3332 " <td>0.0158416</td>\n",
3333 " <td>5.952664</td>\n",
3334 " <td>0.0732673</td>\n",
3335 " <td>3.098483</td>\n",
3336 " <td>0.0574257</td>\n",
3337 " <td>0.0648515</td>\n",
3338 " </tr>\n",
3339 " <tr>\n",
3340 " <th>2</th>\n",
3341 " <td>AAP</td>\n",
3342 " <td>161.440</td>\n",
3343 " <td>N/A</td>\n",
3344 " <td>29.00</td>\n",
3345 " <td>0.623762</td>\n",
3346 " <td>3.123759</td>\n",
3347 " <td>0.510891</td>\n",
3348 " <td>15.086368</td>\n",
3349 " <td>0.50099</td>\n",
3350 " <td>3.208667</td>\n",
3351 " <td>0.0653465</td>\n",
3352 " <td>0.425248</td>\n",
3353 " </tr>\n",
3354 " <tr>\n",
3355 " <th>3</th>\n",
3356 " <td>AAPL</td>\n",
3357 " <td>466.070</td>\n",
3358 " <td>N/A</td>\n",
3359 " <td>34.69</td>\n",
3360 " <td>0.740594</td>\n",
3361 " <td>22.373999</td>\n",
3362 " <td>0.940594</td>\n",
3363 " <td>25.708773</td>\n",
3364 " <td>0.861386</td>\n",
3365 " <td>19.428993</td>\n",
3366 " <td>0.865347</td>\n",
3367 " <td>0.85198</td>\n",
3368 " </tr>\n",
3369 " <tr>\n",
3370 " <th>4</th>\n",
3371 " <td>ABBV</td>\n",
3372 " <td>96.680</td>\n",
3373 " <td>N/A</td>\n",
3374 " <td>21.00</td>\n",
3375 " <td>0.427723</td>\n",
3376 " <td>-21.463532</td>\n",
3377 " <td>0.0257426</td>\n",
3378 " <td>12.272585</td>\n",
3379 " <td>0.350495</td>\n",
3380 " <td>7.672427</td>\n",
3381 " <td>0.340594</td>\n",
3382 " <td>0.286139</td>\n",
3383 " </tr>\n",
3384 " <tr>\n",
3385 " <th>...</th>\n",
3386 " <td>...</td>\n",
3387 " <td>...</td>\n",
3388 " <td>...</td>\n",
3389 " <td>...</td>\n",
3390 " <td>...</td>\n",
3391 " <td>...</td>\n",
3392 " <td>...</td>\n",
3393 " <td>...</td>\n",
3394 " <td>...</td>\n",
3395 " <td>...</td>\n",
3396 " <td>...</td>\n",
3397 " <td>...</td>\n",
3398 " </tr>\n",
3399 " <tr>\n",
3400 " <th>500</th>\n",
3401 " <td>YUM</td>\n",
3402 " <td>94.320</td>\n",
3403 " <td>N/A</td>\n",
3404 " <td>28.00</td>\n",
3405 " <td>0.6</td>\n",
3406 " <td>-3.659682</td>\n",
3407 " <td>0.049505</td>\n",
3408 " <td>18.841249</td>\n",
3409 " <td>0.744554</td>\n",
3410 " <td>13.891510</td>\n",
3411 " <td>0.744554</td>\n",
3412 " <td>0.534653</td>\n",
3413 " </tr>\n",
3414 " <tr>\n",
3415 " <th>501</th>\n",
3416 " <td>ZBH</td>\n",
3417 " <td>143.470</td>\n",
3418 " <td>N/A</td>\n",
3419 " <td>718.10</td>\n",
3420 " <td>0.994059</td>\n",
3421 " <td>2.390128</td>\n",
3422 " <td>0.415842</td>\n",
3423 " <td>17.170711</td>\n",
3424 " <td>0.572277</td>\n",
3425 " <td>7.478460</td>\n",
3426 " <td>0.326733</td>\n",
3427 " <td>0.577228</td>\n",
3428 " </tr>\n",
3429 " <tr>\n",
3430 " <th>502</th>\n",
3431 " <td>ZBRA</td>\n",
3432 " <td>288.222</td>\n",
3433 " <td>N/A</td>\n",
3434 " <td>31.86</td>\n",
3435 " <td>0.693069</td>\n",
3436 " <td>8.600669</td>\n",
3437 " <td>0.811881</td>\n",
3438 " <td>19.480804</td>\n",
3439 " <td>0.762376</td>\n",
3440 " <td>8.449885</td>\n",
3441 " <td>0.4</td>\n",
3442 " <td>0.666832</td>\n",
3443 " </tr>\n",
3444 " <tr>\n",
3445 " <th>503</th>\n",
3446 " <td>ZION</td>\n",
3447 " <td>35.770</td>\n",
3448 " <td>N/A</td>\n",
3449 " <td>13.24</td>\n",
3450 " <td>0.257426</td>\n",
3451 " <td>0.766237</td>\n",
3452 " <td>0.132673</td>\n",
3453 " <td>18.729176</td>\n",
3454 " <td>0.69505</td>\n",
3455 " <td>12.206556</td>\n",
3456 " <td>0.644554</td>\n",
3457 " <td>0.432426</td>\n",
3458 " </tr>\n",
3459 " <tr>\n",
3460 " <th>504</th>\n",
3461 " <td>ZTS</td>\n",
3462 " <td>160.920</td>\n",
3463 " <td>N/A</td>\n",
3464 " <td>47.20</td>\n",
3465 " <td>0.843564</td>\n",
3466 " <td>29.222282</td>\n",
3467 " <td>0.956436</td>\n",
3468 " <td>32.483029</td>\n",
3469 " <td>0.924752</td>\n",
3470 " <td>19.667225</td>\n",
3471 " <td>0.869307</td>\n",
3472 " <td>0.898515</td>\n",
3473 " </tr>\n",
3474 " </tbody>\n",
3475 "</table>\n",
3476 "<p>505 rows × 12 columns</p>\n",
3477 "</div>"
3478 ],
3479 "text/plain": [
3480 " Ticker Price Number of Shares to Buy Price-to-Earnings Ratio \\\n",
3481 "0 A 100.010 N/A 46.95 \n",
3482 "1 AAL 13.360 N/A -1.71 \n",
3483 "2 AAP 161.440 N/A 29.00 \n",
3484 "3 AAPL 466.070 N/A 34.69 \n",
3485 "4 ABBV 96.680 N/A 21.00 \n",
3486 ".. ... ... ... ... \n",
3487 "500 YUM 94.320 N/A 28.00 \n",
3488 "501 ZBH 143.470 N/A 718.10 \n",
3489 "502 ZBRA 288.222 N/A 31.86 \n",
3490 "503 ZION 35.770 N/A 13.24 \n",
3491 "504 ZTS 160.920 N/A 47.20 \n",
3492 "\n",
3493 " PE Percentile Price-to-Book Ratio PB Percentile EV/EBITDA \\\n",
3494 "0 0.841584 6.596140 0.752475 26.372976 \n",
3495 "1 0.112871 -60.417952 0.0158416 5.952664 \n",
3496 "2 0.623762 3.123759 0.510891 15.086368 \n",
3497 "3 0.740594 22.373999 0.940594 25.708773 \n",
3498 "4 0.427723 -21.463532 0.0257426 12.272585 \n",
3499 ".. ... ... ... ... \n",
3500 "500 0.6 -3.659682 0.049505 18.841249 \n",
3501 "501 0.994059 2.390128 0.415842 17.170711 \n",
3502 "502 0.693069 8.600669 0.811881 19.480804 \n",
3503 "503 0.257426 0.766237 0.132673 18.729176 \n",
3504 "504 0.843564 29.222282 0.956436 32.483029 \n",
3505 "\n",
3506 " EV/EBITDA Percentile EV/GP EV/GP Percentile RV Score \n",
3507 "0 0.877228 11.311629 0.552475 0.755941 \n",
3508 "1 0.0732673 3.098483 0.0574257 0.0648515 \n",
3509 "2 0.50099 3.208667 0.0653465 0.425248 \n",
3510 "3 0.861386 19.428993 0.865347 0.85198 \n",
3511 "4 0.350495 7.672427 0.340594 0.286139 \n",
3512 ".. ... ... ... ... \n",
3513 "500 0.744554 13.891510 0.744554 0.534653 \n",
3514 "501 0.572277 7.478460 0.326733 0.577228 \n",
3515 "502 0.762376 8.449885 0.4 0.666832 \n",
3516 "503 0.69505 12.206556 0.644554 0.432426 \n",
3517 "504 0.924752 19.667225 0.869307 0.898515 \n",
3518 "\n",
3519 "[505 rows x 12 columns]"
3520 ]
3521 },
3522 "execution_count": 18,
3523 "metadata": {},
3524 "output_type": "execute_result"
3525 }
3526 ],
3527 "source": [
3528 "from statistics import mean\n",
3529 "\n",
3530 "for row in rv_dataframe.index:\n",
3531 " value_percentiles = []\n",
3532 " for metric in metrics.keys():\n",
3533 " value_percentiles.append(rv_dataframe.loc[row, metrics[metric]])\n",
3534 " rv_dataframe.loc[row, 'RV Score'] = mean(value_percentiles)\n",
3535 " \n",
3536 "rv_dataframe"
3537 ]
3538 },
3539 {
3540 "cell_type": "markdown",
3541 "metadata": {},
3542 "source": [
3543 "## Selecting the 50 Best Value Stocks¶\n",
3544 "\n",
3545 "As before, we can identify the 50 best value stocks in our universe by sorting the DataFrame on the RV Score column and dropping all but the top 50 entries."
3546 ]
3547 },
3548 {
3549 "cell_type": "code",
3550 "execution_count": 19,
3551 "metadata": {},
3552 "outputs": [],
3553 "source": [
3554 "rv_dataframe.sort_values(by = 'RV Score', inplace = True)\n",
3555 "rv_dataframe = rv_dataframe[:50]\n",
3556 "rv_dataframe.reset_index(drop = True, inplace = True)"
3557 ]
3558 },
3559 {
3560 "cell_type": "markdown",
3561 "metadata": {},
3562 "source": [
3563 "## Calculating the Number of Shares to Buy\n",
3564 "We'll use the `portfolio_input` function that we created earlier to accept our portfolio size. Then we will use similar logic in a for loop to calculate the number of shares to buy for each stock in our investment universe."
3565 ]
3566 },
3567 {
3568 "cell_type": "code",
3569 "execution_count": 20,
3570 "metadata": {},
3571 "outputs": [
3572 {
3573 "name": "stdout",
3574 "output_type": "stream",
3575 "text": [
3576 "Enter the value of your portfolio:1000000\n"
3577 ]
3578 }
3579 ],
3580 "source": [
3581 "portfolio_input()"
3582 ]
3583 },
3584 {
3585 "cell_type": "code",
3586 "execution_count": 21,
3587 "metadata": {},
3588 "outputs": [
3589 {
3590 "data": {
3591 "text/html": [
3592 "<div>\n",
3593 "<style scoped>\n",
3594 " .dataframe tbody tr th:only-of-type {\n",
3595 " vertical-align: middle;\n",
3596 " }\n",
3597 "\n",
3598 " .dataframe tbody tr th {\n",
3599 " vertical-align: top;\n",
3600 " }\n",
3601 "\n",
3602 " .dataframe thead th {\n",
3603 " text-align: right;\n",
3604 " }\n",
3605 "</style>\n",
3606 "<table border=\"1\" class=\"dataframe\">\n",
3607 " <thead>\n",
3608 " <tr style=\"text-align: right;\">\n",
3609 " <th></th>\n",
3610 " <th>Ticker</th>\n",
3611 " <th>Price</th>\n",
3612 " <th>Number of Shares to Buy</th>\n",
3613 " <th>Price-to-Earnings Ratio</th>\n",
3614 " <th>PE Percentile</th>\n",
3615 " <th>Price-to-Book Ratio</th>\n",
3616 " <th>PB Percentile</th>\n",
3617 " <th>EV/EBITDA</th>\n",
3618 " <th>EV/EBITDA Percentile</th>\n",
3619 " <th>EV/GP</th>\n",
3620 " <th>EV/GP Percentile</th>\n",
3621 " <th>RV Score</th>\n",
3622 " </tr>\n",
3623 " </thead>\n",
3624 " <tbody>\n",
3625 " <tr>\n",
3626 " <th>0</th>\n",
3627 " <td>HPE</td>\n",
3628 " <td>9.830</td>\n",
3629 " <td>1994</td>\n",
3630 " <td>-293.68</td>\n",
3631 " <td>0.0118812</td>\n",
3632 " <td>0.768699</td>\n",
3633 " <td>0.134653</td>\n",
3634 " <td>4.757379</td>\n",
3635 " <td>0.0455446</td>\n",
3636 " <td>2.575722</td>\n",
3637 " <td>0.039604</td>\n",
3638 " <td>0.0579208</td>\n",
3639 " </tr>\n",
3640 " <tr>\n",
3641 " <th>1</th>\n",
3642 " <td>FTI</td>\n",
3643 " <td>8.870</td>\n",
3644 " <td>2210</td>\n",
3645 " <td>-0.68</td>\n",
3646 " <td>0.134653</td>\n",
3647 " <td>0.526579</td>\n",
3648 " <td>0.0732673</td>\n",
3649 " <td>2.722313</td>\n",
3650 " <td>0.00792079</td>\n",
3651 " <td>1.745265</td>\n",
3652 " <td>0.0178218</td>\n",
3653 " <td>0.0584158</td>\n",
3654 " </tr>\n",
3655 " <tr>\n",
3656 " <th>2</th>\n",
3657 " <td>AAL</td>\n",
3658 " <td>13.360</td>\n",
3659 " <td>1467</td>\n",
3660 " <td>-1.71</td>\n",
3661 " <td>0.112871</td>\n",
3662 " <td>-60.417952</td>\n",
3663 " <td>0.0158416</td>\n",
3664 " <td>5.952664</td>\n",
3665 " <td>0.0732673</td>\n",
3666 " <td>3.098483</td>\n",
3667 " <td>0.0574257</td>\n",
3668 " <td>0.0648515</td>\n",
3669 " </tr>\n",
3670 " <tr>\n",
3671 " <th>3</th>\n",
3672 " <td>CCL</td>\n",
3673 " <td>15.610</td>\n",
3674 " <td>1256</td>\n",
3675 " <td>-3.90</td>\n",
3676 " <td>0.10297</td>\n",
3677 " <td>0.380999</td>\n",
3678 " <td>0.0554455</td>\n",
3679 " <td>3.751649</td>\n",
3680 " <td>0.0237624</td>\n",
3681 " <td>3.458441</td>\n",
3682 " <td>0.0811881</td>\n",
3683 " <td>0.0658416</td>\n",
3684 " </tr>\n",
3685 " <tr>\n",
3686 " <th>4</th>\n",
3687 " <td>HFC</td>\n",
3688 " <td>25.660</td>\n",
3689 " <td>764</td>\n",
3690 " <td>-26.18</td>\n",
3691 " <td>0.0435644</td>\n",
3692 " <td>0.716944</td>\n",
3693 " <td>0.120792</td>\n",
3694 " <td>3.301222</td>\n",
3695 " <td>0.0118812</td>\n",
3696 " <td>3.693071</td>\n",
3697 " <td>0.0891089</td>\n",
3698 " <td>0.0663366</td>\n",
3699 " </tr>\n",
3700 " <tr>\n",
3701 " <th>5</th>\n",
3702 " <td>HPQ</td>\n",
3703 " <td>19.140</td>\n",
3704 " <td>1024</td>\n",
3705 " <td>9.17</td>\n",
3706 " <td>0.172277</td>\n",
3707 " <td>-23.323972</td>\n",
3708 " <td>0.0237624</td>\n",
3709 " <td>5.822424</td>\n",
3710 " <td>0.0673267</td>\n",
3711 " <td>2.516859</td>\n",
3712 " <td>0.0356436</td>\n",
3713 " <td>0.0747525</td>\n",
3714 " </tr>\n",
3715 " <tr>\n",
3716 " <th>6</th>\n",
3717 " <td>XRX</td>\n",
3718 " <td>17.800</td>\n",
3719 " <td>1101</td>\n",
3720 " <td>9.35</td>\n",
3721 " <td>0.174257</td>\n",
3722 " <td>0.640297</td>\n",
3723 " <td>0.0990099</td>\n",
3724 " <td>3.620185</td>\n",
3725 " <td>0.019802</td>\n",
3726 " <td>1.604068</td>\n",
3727 " <td>0.0138614</td>\n",
3728 " <td>0.0767327</td>\n",
3729 " </tr>\n",
3730 " <tr>\n",
3731 " <th>7</th>\n",
3732 " <td>TPR</td>\n",
3733 " <td>16.010</td>\n",
3734 " <td>1224</td>\n",
3735 " <td>-20.58</td>\n",
3736 " <td>0.049505</td>\n",
3737 " <td>1.200922</td>\n",
3738 " <td>0.231683</td>\n",
3739 " <td>3.870569</td>\n",
3740 " <td>0.029703</td>\n",
3741 " <td>1.149885</td>\n",
3742 " <td>0.0019802</td>\n",
3743 " <td>0.0782178</td>\n",
3744 " </tr>\n",
3745 " <tr>\n",
3746 " <th>8</th>\n",
3747 " <td>LB</td>\n",
3748 " <td>27.330</td>\n",
3749 " <td>717</td>\n",
3750 " <td>-10.49</td>\n",
3751 " <td>0.0633663</td>\n",
3752 " <td>-5.047485</td>\n",
3753 " <td>0.0455446</td>\n",
3754 " <td>8.144139</td>\n",
3755 " <td>0.170297</td>\n",
3756 " <td>3.119623</td>\n",
3757 " <td>0.0613861</td>\n",
3758 " <td>0.0851485</td>\n",
3759 " </tr>\n",
3760 " <tr>\n",
3761 " <th>9</th>\n",
3762 " <td>SYF</td>\n",
3763 " <td>25.300</td>\n",
3764 " <td>775</td>\n",
3765 " <td>7.77</td>\n",
3766 " <td>0.152475</td>\n",
3767 " <td>0.971730</td>\n",
3768 " <td>0.180198</td>\n",
3769 " <td>2.129599</td>\n",
3770 " <td>0.0039604</td>\n",
3771 " <td>1.604512</td>\n",
3772 " <td>0.0158416</td>\n",
3773 " <td>0.0881188</td>\n",
3774 " </tr>\n",
3775 " <tr>\n",
3776 " <th>10</th>\n",
3777 " <td>TAP</td>\n",
3778 " <td>39.340</td>\n",
3779 " <td>498</td>\n",
3780 " <td>-52.36</td>\n",
3781 " <td>0.0356436</td>\n",
3782 " <td>0.578118</td>\n",
3783 " <td>0.0891089</td>\n",
3784 " <td>7.089363</td>\n",
3785 " <td>0.108911</td>\n",
3786 " <td>4.272303</td>\n",
3787 " <td>0.120792</td>\n",
3788 " <td>0.0886139</td>\n",
3789 " </tr>\n",
3790 " <tr>\n",
3791 " <th>11</th>\n",
3792 " <td>NWS</td>\n",
3793 " <td>15.981</td>\n",
3794 " <td>1226</td>\n",
3795 " <td>-7.49</td>\n",
3796 " <td>0.0752475</td>\n",
3797 " <td>0.414744</td>\n",
3798 " <td>0.0653465</td>\n",
3799 " <td>8.449938</td>\n",
3800 " <td>0.190099</td>\n",
3801 " <td>2.998177</td>\n",
3802 " <td>0.0514851</td>\n",
3803 " <td>0.0955446</td>\n",
3804 " </tr>\n",
3805 " <tr>\n",
3806 " <th>12</th>\n",
3807 " <td>UAL</td>\n",
3808 " <td>36.500</td>\n",
3809 " <td>537</td>\n",
3810 " <td>-5.93</td>\n",
3811 " <td>0.0891089</td>\n",
3812 " <td>0.931935</td>\n",
3813 " <td>0.170297</td>\n",
3814 " <td>3.812066</td>\n",
3815 " <td>0.0277228</td>\n",
3816 " <td>4.040411</td>\n",
3817 " <td>0.10495</td>\n",
3818 " <td>0.0980198</td>\n",
3819 " </tr>\n",
3820 " <tr>\n",
3821 " <th>13</th>\n",
3822 " <td>IVZ</td>\n",
3823 " <td>10.900</td>\n",
3824 " <td>1798</td>\n",
3825 " <td>10.63</td>\n",
3826 " <td>0.19802</td>\n",
3827 " <td>0.371514</td>\n",
3828 " <td>0.0534653</td>\n",
3829 " <td>6.906037</td>\n",
3830 " <td>0.10297</td>\n",
3831 " <td>2.917683</td>\n",
3832 " <td>0.0475248</td>\n",
3833 " <td>0.100495</td>\n",
3834 " </tr>\n",
3835 " <tr>\n",
3836 " <th>14</th>\n",
3837 " <td>COTY</td>\n",
3838 " <td>4.270</td>\n",
3839 " <td>4592</td>\n",
3840 " <td>-1.07</td>\n",
3841 " <td>0.122772</td>\n",
3842 " <td>0.699648</td>\n",
3843 " <td>0.112871</td>\n",
3844 " <td>7.926827</td>\n",
3845 " <td>0.148515</td>\n",
3846 " <td>2.045210</td>\n",
3847 " <td>0.0277228</td>\n",
3848 " <td>0.10297</td>\n",
3849 " </tr>\n",
3850 " <tr>\n",
3851 " <th>15</th>\n",
3852 " <td>DAL</td>\n",
3853 " <td>29.300</td>\n",
3854 " <td>669</td>\n",
3855 " <td>-5.14</td>\n",
3856 " <td>0.0970297</td>\n",
3857 " <td>1.266128</td>\n",
3858 " <td>0.243564</td>\n",
3859 " <td>3.522233</td>\n",
3860 " <td>0.0178218</td>\n",
3861 " <td>3.119418</td>\n",
3862 " <td>0.0594059</td>\n",
3863 " <td>0.104455</td>\n",
3864 " </tr>\n",
3865 " <tr>\n",
3866 " <th>16</th>\n",
3867 " <td>DISCA</td>\n",
3868 " <td>22.700</td>\n",
3869 " <td>863</td>\n",
3870 " <td>9.60</td>\n",
3871 " <td>0.180198</td>\n",
3872 " <td>0.363484</td>\n",
3873 " <td>0.0514851</td>\n",
3874 " <td>5.844247</td>\n",
3875 " <td>0.0693069</td>\n",
3876 " <td>4.354043</td>\n",
3877 " <td>0.124752</td>\n",
3878 " <td>0.106436</td>\n",
3879 " </tr>\n",
3880 " <tr>\n",
3881 " <th>17</th>\n",
3882 " <td>PVH</td>\n",
3883 " <td>56.280</td>\n",
3884 " <td>348</td>\n",
3885 " <td>-5.12</td>\n",
3886 " <td>0.0990099</td>\n",
3887 " <td>0.664241</td>\n",
3888 " <td>0.10297</td>\n",
3889 " <td>9.261763</td>\n",
3890 " <td>0.223762</td>\n",
3891 " <td>1.476319</td>\n",
3892 " <td>0.0118812</td>\n",
3893 " <td>0.109406</td>\n",
3894 " </tr>\n",
3895 " <tr>\n",
3896 " <th>18</th>\n",
3897 " <td>BKR</td>\n",
3898 " <td>17.780</td>\n",
3899 " <td>1102</td>\n",
3900 " <td>-1.10</td>\n",
3901 " <td>0.120792</td>\n",
3902 " <td>0.537604</td>\n",
3903 " <td>0.0792079</td>\n",
3904 " <td>7.481882</td>\n",
3905 " <td>0.122772</td>\n",
3906 " <td>4.347533</td>\n",
3907 " <td>0.122772</td>\n",
3908 " <td>0.111386</td>\n",
3909 " </tr>\n",
3910 " <tr>\n",
3911 " <th>19</th>\n",
3912 " <td>BEN</td>\n",
3913 " <td>22.480</td>\n",
3914 " <td>872</td>\n",
3915 " <td>10.95</td>\n",
3916 " <td>0.205941</td>\n",
3917 " <td>1.127782</td>\n",
3918 " <td>0.217822</td>\n",
3919 " <td>3.458549</td>\n",
3920 " <td>0.0138614</td>\n",
3921 " <td>1.475177</td>\n",
3922 " <td>0.00990099</td>\n",
3923 " <td>0.111881</td>\n",
3924 " </tr>\n",
3925 " <tr>\n",
3926 " <th>20</th>\n",
3927 " <td>NWSA</td>\n",
3928 " <td>15.300</td>\n",
3929 " <td>1281</td>\n",
3930 " <td>-7.31</td>\n",
3931 " <td>0.0772277</td>\n",
3932 " <td>0.802438</td>\n",
3933 " <td>0.140594</td>\n",
3934 " <td>8.385668</td>\n",
3935 " <td>0.186139</td>\n",
3936 " <td>2.917670</td>\n",
3937 " <td>0.0455446</td>\n",
3938 " <td>0.112376</td>\n",
3939 " </tr>\n",
3940 " <tr>\n",
3941 " <th>21</th>\n",
3942 " <td>DISCK</td>\n",
3943 " <td>21.080</td>\n",
3944 " <td>930</td>\n",
3945 " <td>9.11</td>\n",
3946 " <td>0.168317</td>\n",
3947 " <td>0.706494</td>\n",
3948 " <td>0.116832</td>\n",
3949 " <td>5.620235</td>\n",
3950 " <td>0.0594059</td>\n",
3951 " <td>4.154303</td>\n",
3952 " <td>0.112871</td>\n",
3953 " <td>0.114356</td>\n",
3954 " </tr>\n",
3955 " <tr>\n",
3956 " <th>22</th>\n",
3957 " <td>NCLH</td>\n",
3958 " <td>15.590</td>\n",
3959 " <td>1257</td>\n",
3960 " <td>-1.68</td>\n",
3961 " <td>0.114851</td>\n",
3962 " <td>0.660699</td>\n",
3963 " <td>0.10099</td>\n",
3964 " <td>6.134222</td>\n",
3965 " <td>0.0851485</td>\n",
3966 " <td>5.089065</td>\n",
3967 " <td>0.182178</td>\n",
3968 " <td>0.120792</td>\n",
3969 " </tr>\n",
3970 " <tr>\n",
3971 " <th>23</th>\n",
3972 " <td>MRO</td>\n",
3973 " <td>6.030</td>\n",
3974 " <td>3251</td>\n",
3975 " <td>-7.26</td>\n",
3976 " <td>0.0792079</td>\n",
3977 " <td>0.386172</td>\n",
3978 " <td>0.0594059</td>\n",
3979 " <td>3.135953</td>\n",
3980 " <td>0.00990099</td>\n",
3981 " <td>7.809283</td>\n",
3982 " <td>0.346535</td>\n",
3983 " <td>0.123762</td>\n",
3984 " </tr>\n",
3985 " <tr>\n",
3986 " <th>24</th>\n",
3987 " <td>MPC</td>\n",
3988 " <td>37.350</td>\n",
3989 " <td>524</td>\n",
3990 " <td>-3.23</td>\n",
3991 " <td>0.106931</td>\n",
3992 " <td>0.733937</td>\n",
3993 " <td>0.126733</td>\n",
3994 " <td>5.509754</td>\n",
3995 " <td>0.0554455</td>\n",
3996 " <td>5.622738</td>\n",
3997 " <td>0.215842</td>\n",
3998 " <td>0.126238</td>\n",
3999 " </tr>\n",
4000 " <tr>\n",
4001 " <th>25</th>\n",
4002 " <td>DXC</td>\n",
4003 " <td>20.160</td>\n",
4004 " <td>972</td>\n",
4005 " <td>-0.87</td>\n",
4006 " <td>0.130693</td>\n",
4007 " <td>1.088626</td>\n",
4008 " <td>0.205941</td>\n",
4009 " <td>4.093135</td>\n",
4010 " <td>0.0356436</td>\n",
4011 " <td>4.734496</td>\n",
4012 " <td>0.158416</td>\n",
4013 " <td>0.132673</td>\n",
4014 " </tr>\n",
4015 " <tr>\n",
4016 " <th>26</th>\n",
4017 " <td>MHK</td>\n",
4018 " <td>92.450</td>\n",
4019 " <td>212</td>\n",
4020 " <td>13.45</td>\n",
4021 " <td>0.265347</td>\n",
4022 " <td>0.787058</td>\n",
4023 " <td>0.136634</td>\n",
4024 " <td>6.000217</td>\n",
4025 " <td>0.0772277</td>\n",
4026 " <td>3.333651</td>\n",
4027 " <td>0.0693069</td>\n",
4028 " <td>0.137129</td>\n",
4029 " </tr>\n",
4030 " <tr>\n",
4031 " <th>27</th>\n",
4032 " <td>GPS</td>\n",
4033 " <td>15.440</td>\n",
4034 " <td>1269</td>\n",
4035 " <td>-7.10</td>\n",
4036 " <td>0.0831683</td>\n",
4037 " <td>1.721678</td>\n",
4038 " <td>0.316832</td>\n",
4039 " <td>7.537870</td>\n",
4040 " <td>0.128713</td>\n",
4041 " <td>1.919594</td>\n",
4042 " <td>0.0257426</td>\n",
4043 " <td>0.138614</td>\n",
4044 " </tr>\n",
4045 " <tr>\n",
4046 " <th>28</th>\n",
4047 " <td>VIAC</td>\n",
4048 " <td>27.563</td>\n",
4049 " <td>711</td>\n",
4050 " <td>13.15</td>\n",
4051 " <td>0.255446</td>\n",
4052 " <td>1.126636</td>\n",
4053 " <td>0.215842</td>\n",
4054 " <td>2.049573</td>\n",
4055 " <td>0.0019802</td>\n",
4056 " <td>3.621115</td>\n",
4057 " <td>0.0871287</td>\n",
4058 " <td>0.140099</td>\n",
4059 " </tr>\n",
4060 " <tr>\n",
4061 " <th>29</th>\n",
4062 " <td>FANG</td>\n",
4063 " <td>45.280</td>\n",
4064 " <td>433</td>\n",
4065 " <td>-2.64</td>\n",
4066 " <td>0.108911</td>\n",
4067 " <td>0.532947</td>\n",
4068 " <td>0.0752475</td>\n",
4069 " <td>4.236347</td>\n",
4070 " <td>0.0415842</td>\n",
4071 " <td>7.856301</td>\n",
4072 " <td>0.350495</td>\n",
4073 " <td>0.144059</td>\n",
4074 " </tr>\n",
4075 " <tr>\n",
4076 " <th>30</th>\n",
4077 " <td>WDC</td>\n",
4078 " <td>36.370</td>\n",
4079 " <td>539</td>\n",
4080 " <td>-43.31</td>\n",
4081 " <td>0.0376238</td>\n",
4082 " <td>1.190996</td>\n",
4083 " <td>0.227723</td>\n",
4084 " <td>8.742497</td>\n",
4085 " <td>0.19802</td>\n",
4086 " <td>4.502085</td>\n",
4087 " <td>0.134653</td>\n",
4088 " <td>0.149505</td>\n",
4089 " </tr>\n",
4090 " <tr>\n",
4091 " <th>31</th>\n",
4092 " <td>WRK</td>\n",
4093 " <td>30.710</td>\n",
4094 " <td>638</td>\n",
4095 " <td>10.03</td>\n",
4096 " <td>0.190099</td>\n",
4097 " <td>0.690004</td>\n",
4098 " <td>0.108911</td>\n",
4099 " <td>6.021276</td>\n",
4100 " <td>0.0831683</td>\n",
4101 " <td>5.745581</td>\n",
4102 " <td>0.219802</td>\n",
4103 " <td>0.150495</td>\n",
4104 " </tr>\n",
4105 " <tr>\n",
4106 " <th>32</th>\n",
4107 " <td>CTL</td>\n",
4108 " <td>11.220</td>\n",
4109 " <td>1747</td>\n",
4110 " <td>9.80</td>\n",
4111 " <td>0.184158</td>\n",
4112 " <td>0.912656</td>\n",
4113 " <td>0.168317</td>\n",
4114 " <td>5.277040</td>\n",
4115 " <td>0.0534653</td>\n",
4116 " <td>6.042985</td>\n",
4117 " <td>0.239604</td>\n",
4118 " <td>0.161386</td>\n",
4119 " </tr>\n",
4120 " <tr>\n",
4121 " <th>33</th>\n",
4122 " <td>FOX</td>\n",
4123 " <td>26.460</td>\n",
4124 " <td>741</td>\n",
4125 " <td>16.41</td>\n",
4126 " <td>0.328713</td>\n",
4127 " <td>0.676120</td>\n",
4128 " <td>0.10495</td>\n",
4129 " <td>6.950444</td>\n",
4130 " <td>0.10495</td>\n",
4131 " <td>4.414420</td>\n",
4132 " <td>0.126733</td>\n",
4133 " <td>0.166337</td>\n",
4134 " </tr>\n",
4135 " <tr>\n",
4136 " <th>34</th>\n",
4137 " <td>RCL</td>\n",
4138 " <td>60.600</td>\n",
4139 " <td>323</td>\n",
4140 " <td>-6.41</td>\n",
4141 " <td>0.0871287</td>\n",
4142 " <td>1.033061</td>\n",
4143 " <td>0.194059</td>\n",
4144 " <td>7.174071</td>\n",
4145 " <td>0.110891</td>\n",
4146 " <td>6.567250</td>\n",
4147 " <td>0.275248</td>\n",
4148 " <td>0.166832</td>\n",
4149 " </tr>\n",
4150 " <tr>\n",
4151 " <th>35</th>\n",
4152 " <td>UA</td>\n",
4153 " <td>9.830</td>\n",
4154 " <td>1994</td>\n",
4155 " <td>-6.49</td>\n",
4156 " <td>0.0851485</td>\n",
4157 " <td>1.060941</td>\n",
4158 " <td>0.19604</td>\n",
4159 " <td>12.701717</td>\n",
4160 " <td>0.362376</td>\n",
4161 " <td>2.100650</td>\n",
4162 " <td>0.029703</td>\n",
4163 " <td>0.168317</td>\n",
4164 " </tr>\n",
4165 " <tr>\n",
4166 " <th>36</th>\n",
4167 " <td>SCHW</td>\n",
4168 " <td>36.250</td>\n",
4169 " <td>540</td>\n",
4170 " <td>15.11</td>\n",
4171 " <td>0.306931</td>\n",
4172 " <td>2.069167</td>\n",
4173 " <td>0.372277</td>\n",
4174 " <td>2.325805</td>\n",
4175 " <td>0.00594059</td>\n",
4176 " <td>1.220535</td>\n",
4177 " <td>0.0039604</td>\n",
4178 " <td>0.172277</td>\n",
4179 " </tr>\n",
4180 " <tr>\n",
4181 " <th>37</th>\n",
4182 " <td>EXPE</td>\n",
4183 " <td>88.450</td>\n",
4184 " <td>221</td>\n",
4185 " <td>-8.06</td>\n",
4186 " <td>0.0712871</td>\n",
4187 " <td>3.056182</td>\n",
4188 " <td>0.50297</td>\n",
4189 " <td>7.267947</td>\n",
4190 " <td>0.114851</td>\n",
4191 " <td>1.456282</td>\n",
4192 " <td>0.00792079</td>\n",
4193 " <td>0.174257</td>\n",
4194 " </tr>\n",
4195 " <tr>\n",
4196 " <th>38</th>\n",
4197 " <td>UAA</td>\n",
4198 " <td>11.140</td>\n",
4199 " <td>1760</td>\n",
4200 " <td>-7.52</td>\n",
4201 " <td>0.0732673</td>\n",
4202 " <td>0.988515</td>\n",
4203 " <td>0.184158</td>\n",
4204 " <td>13.812802</td>\n",
4205 " <td>0.433663</td>\n",
4206 " <td>2.184035</td>\n",
4207 " <td>0.0316832</td>\n",
4208 " <td>0.180693</td>\n",
4209 " </tr>\n",
4210 " <tr>\n",
4211 " <th>39</th>\n",
4212 " <td>FOXA</td>\n",
4213 " <td>25.740</td>\n",
4214 " <td>761</td>\n",
4215 " <td>16.35</td>\n",
4216 " <td>0.326733</td>\n",
4217 " <td>0.880273</td>\n",
4218 " <td>0.164356</td>\n",
4219 " <td>6.900888</td>\n",
4220 " <td>0.10099</td>\n",
4221 " <td>4.540753</td>\n",
4222 " <td>0.136634</td>\n",
4223 " <td>0.182178</td>\n",
4224 " </tr>\n",
4225 " <tr>\n",
4226 " <th>40</th>\n",
4227 " <td>VNO</td>\n",
4228 " <td>35.870</td>\n",
4229 " <td>546</td>\n",
4230 " <td>21.49</td>\n",
4231 " <td>0.431683</td>\n",
4232 " <td>1.072895</td>\n",
4233 " <td>0.20396</td>\n",
4234 " <td>3.485153</td>\n",
4235 " <td>0.0158416</td>\n",
4236 " <td>3.603223</td>\n",
4237 " <td>0.0831683</td>\n",
4238 " <td>0.183663</td>\n",
4239 " </tr>\n",
4240 " <tr>\n",
4241 " <th>41</th>\n",
4242 " <td>DVN</td>\n",
4243 " <td>12.040</td>\n",
4244 " <td>1628</td>\n",
4245 " <td>-1.49</td>\n",
4246 " <td>0.116832</td>\n",
4247 " <td>0.820986</td>\n",
4248 " <td>0.148515</td>\n",
4249 " <td>4.098153</td>\n",
4250 " <td>0.0376238</td>\n",
4251 " <td>9.447806</td>\n",
4252 " <td>0.455446</td>\n",
4253 " <td>0.189604</td>\n",
4254 " </tr>\n",
4255 " <tr>\n",
4256 " <th>42</th>\n",
4257 " <td>NWL</td>\n",
4258 " <td>17.834</td>\n",
4259 " <td>1099</td>\n",
4260 " <td>-7.13</td>\n",
4261 " <td>0.0811881</td>\n",
4262 " <td>1.484674</td>\n",
4263 " <td>0.285149</td>\n",
4264 " <td>10.817287</td>\n",
4265 " <td>0.277228</td>\n",
4266 " <td>4.190990</td>\n",
4267 " <td>0.114851</td>\n",
4268 " <td>0.189604</td>\n",
4269 " </tr>\n",
4270 " <tr>\n",
4271 " <th>43</th>\n",
4272 " <td>WU</td>\n",
4273 " <td>24.380</td>\n",
4274 " <td>804</td>\n",
4275 " <td>16.04</td>\n",
4276 " <td>0.316832</td>\n",
4277 " <td>-244.906130</td>\n",
4278 " <td>0.00792079</td>\n",
4279 " <td>9.080150</td>\n",
4280 " <td>0.213861</td>\n",
4281 " <td>6.028085</td>\n",
4282 " <td>0.237624</td>\n",
4283 " <td>0.194059</td>\n",
4284 " </tr>\n",
4285 " <tr>\n",
4286 " <th>44</th>\n",
4287 " <td>CVS</td>\n",
4288 " <td>66.200</td>\n",
4289 " <td>296</td>\n",
4290 " <td>10.61</td>\n",
4291 " <td>0.19604</td>\n",
4292 " <td>1.353402</td>\n",
4293 " <td>0.263366</td>\n",
4294 " <td>9.882833</td>\n",
4295 " <td>0.241584</td>\n",
4296 " <td>3.620672</td>\n",
4297 " <td>0.0851485</td>\n",
4298 " <td>0.196535</td>\n",
4299 " </tr>\n",
4300 " <tr>\n",
4301 " <th>45</th>\n",
4302 " <td>MGM</td>\n",
4303 " <td>22.160</td>\n",
4304 " <td>884</td>\n",
4305 " <td>5.59</td>\n",
4306 " <td>0.146535</td>\n",
4307 " <td>1.406820</td>\n",
4308 " <td>0.275248</td>\n",
4309 " <td>8.075014</td>\n",
4310 " <td>0.162376</td>\n",
4311 " <td>5.754476</td>\n",
4312 " <td>0.221782</td>\n",
4313 " <td>0.201485</td>\n",
4314 " </tr>\n",
4315 " <tr>\n",
4316 " <th>46</th>\n",
4317 " <td>PSX</td>\n",
4318 " <td>64.660</td>\n",
4319 " <td>303</td>\n",
4320 " <td>-23.44</td>\n",
4321 " <td>0.0475248</td>\n",
4322 " <td>1.148756</td>\n",
4323 " <td>0.219802</td>\n",
4324 " <td>8.790870</td>\n",
4325 " <td>0.20198</td>\n",
4326 " <td>7.691650</td>\n",
4327 " <td>0.342574</td>\n",
4328 " <td>0.20297</td>\n",
4329 " </tr>\n",
4330 " <tr>\n",
4331 " <th>47</th>\n",
4332 " <td>NI</td>\n",
4333 " <td>24.830</td>\n",
4334 " <td>789</td>\n",
4335 " <td>-80.23</td>\n",
4336 " <td>0.0316832</td>\n",
4337 " <td>1.587958</td>\n",
4338 " <td>0.30099</td>\n",
4339 " <td>9.177249</td>\n",
4340 " <td>0.219802</td>\n",
4341 " <td>6.421577</td>\n",
4342 " <td>0.267327</td>\n",
4343 " <td>0.20495</td>\n",
4344 " </tr>\n",
4345 " <tr>\n",
4346 " <th>48</th>\n",
4347 " <td>CXO</td>\n",
4348 " <td>52.680</td>\n",
4349 " <td>372</td>\n",
4350 " <td>-1.06</td>\n",
4351 " <td>0.125743</td>\n",
4352 " <td>0.582959</td>\n",
4353 " <td>0.0910891</td>\n",
4354 " <td>5.069334</td>\n",
4355 " <td>0.0514851</td>\n",
4356 " <td>11.573139</td>\n",
4357 " <td>0.566337</td>\n",
4358 " <td>0.208663</td>\n",
4359 " </tr>\n",
4360 " <tr>\n",
4361 " <th>49</th>\n",
4362 " <td>NLSN</td>\n",
4363 " <td>16.140</td>\n",
4364 " <td>1214</td>\n",
4365 " <td>-9.03</td>\n",
4366 " <td>0.0673267</td>\n",
4367 " <td>2.590361</td>\n",
4368 " <td>0.457426</td>\n",
4369 " <td>8.050020</td>\n",
4370 " <td>0.160396</td>\n",
4371 " <td>4.710304</td>\n",
4372 " <td>0.152475</td>\n",
4373 " <td>0.209406</td>\n",
4374 " </tr>\n",
4375 " <tr>\n",
4376 " <th>50</th>\n",
4377 " <td>HWM</td>\n",
4378 " <td>17.680</td>\n",
4379 " <td>N/A</td>\n",
4380 " <td>14.73</td>\n",
4381 " <td>0.29901</td>\n",
4382 " <td>1.738266</td>\n",
4383 " <td>0.320792</td>\n",
4384 " <td>5.696284</td>\n",
4385 " <td>0.0613861</td>\n",
4386 " <td>5.228678</td>\n",
4387 " <td>0.192079</td>\n",
4388 " <td>0.218317</td>\n",
4389 " </tr>\n",
4390 " </tbody>\n",
4391 "</table>\n",
4392 "</div>"
4393 ],
4394 "text/plain": [
4395 " Ticker Price Number of Shares to Buy Price-to-Earnings Ratio \\\n",
4396 "0 HPE 9.830 1994 -293.68 \n",
4397 "1 FTI 8.870 2210 -0.68 \n",
4398 "2 AAL 13.360 1467 -1.71 \n",
4399 "3 CCL 15.610 1256 -3.90 \n",
4400 "4 HFC 25.660 764 -26.18 \n",
4401 "5 HPQ 19.140 1024 9.17 \n",
4402 "6 XRX 17.800 1101 9.35 \n",
4403 "7 TPR 16.010 1224 -20.58 \n",
4404 "8 LB 27.330 717 -10.49 \n",
4405 "9 SYF 25.300 775 7.77 \n",
4406 "10 TAP 39.340 498 -52.36 \n",
4407 "11 NWS 15.981 1226 -7.49 \n",
4408 "12 UAL 36.500 537 -5.93 \n",
4409 "13 IVZ 10.900 1798 10.63 \n",
4410 "14 COTY 4.270 4592 -1.07 \n",
4411 "15 DAL 29.300 669 -5.14 \n",
4412 "16 DISCA 22.700 863 9.60 \n",
4413 "17 PVH 56.280 348 -5.12 \n",
4414 "18 BKR 17.780 1102 -1.10 \n",
4415 "19 BEN 22.480 872 10.95 \n",
4416 "20 NWSA 15.300 1281 -7.31 \n",
4417 "21 DISCK 21.080 930 9.11 \n",
4418 "22 NCLH 15.590 1257 -1.68 \n",
4419 "23 MRO 6.030 3251 -7.26 \n",
4420 "24 MPC 37.350 524 -3.23 \n",
4421 "25 DXC 20.160 972 -0.87 \n",
4422 "26 MHK 92.450 212 13.45 \n",
4423 "27 GPS 15.440 1269 -7.10 \n",
4424 "28 VIAC 27.563 711 13.15 \n",
4425 "29 FANG 45.280 433 -2.64 \n",
4426 "30 WDC 36.370 539 -43.31 \n",
4427 "31 WRK 30.710 638 10.03 \n",
4428 "32 CTL 11.220 1747 9.80 \n",
4429 "33 FOX 26.460 741 16.41 \n",
4430 "34 RCL 60.600 323 -6.41 \n",
4431 "35 UA 9.830 1994 -6.49 \n",
4432 "36 SCHW 36.250 540 15.11 \n",
4433 "37 EXPE 88.450 221 -8.06 \n",
4434 "38 UAA 11.140 1760 -7.52 \n",
4435 "39 FOXA 25.740 761 16.35 \n",
4436 "40 VNO 35.870 546 21.49 \n",
4437 "41 DVN 12.040 1628 -1.49 \n",
4438 "42 NWL 17.834 1099 -7.13 \n",
4439 "43 WU 24.380 804 16.04 \n",
4440 "44 CVS 66.200 296 10.61 \n",
4441 "45 MGM 22.160 884 5.59 \n",
4442 "46 PSX 64.660 303 -23.44 \n",
4443 "47 NI 24.830 789 -80.23 \n",
4444 "48 CXO 52.680 372 -1.06 \n",
4445 "49 NLSN 16.140 1214 -9.03 \n",
4446 "50 HWM 17.680 N/A 14.73 \n",
4447 "\n",
4448 " PE Percentile Price-to-Book Ratio PB Percentile EV/EBITDA \\\n",
4449 "0 0.0118812 0.768699 0.134653 4.757379 \n",
4450 "1 0.134653 0.526579 0.0732673 2.722313 \n",
4451 "2 0.112871 -60.417952 0.0158416 5.952664 \n",
4452 "3 0.10297 0.380999 0.0554455 3.751649 \n",
4453 "4 0.0435644 0.716944 0.120792 3.301222 \n",
4454 "5 0.172277 -23.323972 0.0237624 5.822424 \n",
4455 "6 0.174257 0.640297 0.0990099 3.620185 \n",
4456 "7 0.049505 1.200922 0.231683 3.870569 \n",
4457 "8 0.0633663 -5.047485 0.0455446 8.144139 \n",
4458 "9 0.152475 0.971730 0.180198 2.129599 \n",
4459 "10 0.0356436 0.578118 0.0891089 7.089363 \n",
4460 "11 0.0752475 0.414744 0.0653465 8.449938 \n",
4461 "12 0.0891089 0.931935 0.170297 3.812066 \n",
4462 "13 0.19802 0.371514 0.0534653 6.906037 \n",
4463 "14 0.122772 0.699648 0.112871 7.926827 \n",
4464 "15 0.0970297 1.266128 0.243564 3.522233 \n",
4465 "16 0.180198 0.363484 0.0514851 5.844247 \n",
4466 "17 0.0990099 0.664241 0.10297 9.261763 \n",
4467 "18 0.120792 0.537604 0.0792079 7.481882 \n",
4468 "19 0.205941 1.127782 0.217822 3.458549 \n",
4469 "20 0.0772277 0.802438 0.140594 8.385668 \n",
4470 "21 0.168317 0.706494 0.116832 5.620235 \n",
4471 "22 0.114851 0.660699 0.10099 6.134222 \n",
4472 "23 0.0792079 0.386172 0.0594059 3.135953 \n",
4473 "24 0.106931 0.733937 0.126733 5.509754 \n",
4474 "25 0.130693 1.088626 0.205941 4.093135 \n",
4475 "26 0.265347 0.787058 0.136634 6.000217 \n",
4476 "27 0.0831683 1.721678 0.316832 7.537870 \n",
4477 "28 0.255446 1.126636 0.215842 2.049573 \n",
4478 "29 0.108911 0.532947 0.0752475 4.236347 \n",
4479 "30 0.0376238 1.190996 0.227723 8.742497 \n",
4480 "31 0.190099 0.690004 0.108911 6.021276 \n",
4481 "32 0.184158 0.912656 0.168317 5.277040 \n",
4482 "33 0.328713 0.676120 0.10495 6.950444 \n",
4483 "34 0.0871287 1.033061 0.194059 7.174071 \n",
4484 "35 0.0851485 1.060941 0.19604 12.701717 \n",
4485 "36 0.306931 2.069167 0.372277 2.325805 \n",
4486 "37 0.0712871 3.056182 0.50297 7.267947 \n",
4487 "38 0.0732673 0.988515 0.184158 13.812802 \n",
4488 "39 0.326733 0.880273 0.164356 6.900888 \n",
4489 "40 0.431683 1.072895 0.20396 3.485153 \n",
4490 "41 0.116832 0.820986 0.148515 4.098153 \n",
4491 "42 0.0811881 1.484674 0.285149 10.817287 \n",
4492 "43 0.316832 -244.906130 0.00792079 9.080150 \n",
4493 "44 0.19604 1.353402 0.263366 9.882833 \n",
4494 "45 0.146535 1.406820 0.275248 8.075014 \n",
4495 "46 0.0475248 1.148756 0.219802 8.790870 \n",
4496 "47 0.0316832 1.587958 0.30099 9.177249 \n",
4497 "48 0.125743 0.582959 0.0910891 5.069334 \n",
4498 "49 0.0673267 2.590361 0.457426 8.050020 \n",
4499 "50 0.29901 1.738266 0.320792 5.696284 \n",
4500 "\n",
4501 " EV/EBITDA Percentile EV/GP EV/GP Percentile RV Score \n",
4502 "0 0.0455446 2.575722 0.039604 0.0579208 \n",
4503 "1 0.00792079 1.745265 0.0178218 0.0584158 \n",
4504 "2 0.0732673 3.098483 0.0574257 0.0648515 \n",
4505 "3 0.0237624 3.458441 0.0811881 0.0658416 \n",
4506 "4 0.0118812 3.693071 0.0891089 0.0663366 \n",
4507 "5 0.0673267 2.516859 0.0356436 0.0747525 \n",
4508 "6 0.019802 1.604068 0.0138614 0.0767327 \n",
4509 "7 0.029703 1.149885 0.0019802 0.0782178 \n",
4510 "8 0.170297 3.119623 0.0613861 0.0851485 \n",
4511 "9 0.0039604 1.604512 0.0158416 0.0881188 \n",
4512 "10 0.108911 4.272303 0.120792 0.0886139 \n",
4513 "11 0.190099 2.998177 0.0514851 0.0955446 \n",
4514 "12 0.0277228 4.040411 0.10495 0.0980198 \n",
4515 "13 0.10297 2.917683 0.0475248 0.100495 \n",
4516 "14 0.148515 2.045210 0.0277228 0.10297 \n",
4517 "15 0.0178218 3.119418 0.0594059 0.104455 \n",
4518 "16 0.0693069 4.354043 0.124752 0.106436 \n",
4519 "17 0.223762 1.476319 0.0118812 0.109406 \n",
4520 "18 0.122772 4.347533 0.122772 0.111386 \n",
4521 "19 0.0138614 1.475177 0.00990099 0.111881 \n",
4522 "20 0.186139 2.917670 0.0455446 0.112376 \n",
4523 "21 0.0594059 4.154303 0.112871 0.114356 \n",
4524 "22 0.0851485 5.089065 0.182178 0.120792 \n",
4525 "23 0.00990099 7.809283 0.346535 0.123762 \n",
4526 "24 0.0554455 5.622738 0.215842 0.126238 \n",
4527 "25 0.0356436 4.734496 0.158416 0.132673 \n",
4528 "26 0.0772277 3.333651 0.0693069 0.137129 \n",
4529 "27 0.128713 1.919594 0.0257426 0.138614 \n",
4530 "28 0.0019802 3.621115 0.0871287 0.140099 \n",
4531 "29 0.0415842 7.856301 0.350495 0.144059 \n",
4532 "30 0.19802 4.502085 0.134653 0.149505 \n",
4533 "31 0.0831683 5.745581 0.219802 0.150495 \n",
4534 "32 0.0534653 6.042985 0.239604 0.161386 \n",
4535 "33 0.10495 4.414420 0.126733 0.166337 \n",
4536 "34 0.110891 6.567250 0.275248 0.166832 \n",
4537 "35 0.362376 2.100650 0.029703 0.168317 \n",
4538 "36 0.00594059 1.220535 0.0039604 0.172277 \n",
4539 "37 0.114851 1.456282 0.00792079 0.174257 \n",
4540 "38 0.433663 2.184035 0.0316832 0.180693 \n",
4541 "39 0.10099 4.540753 0.136634 0.182178 \n",
4542 "40 0.0158416 3.603223 0.0831683 0.183663 \n",
4543 "41 0.0376238 9.447806 0.455446 0.189604 \n",
4544 "42 0.277228 4.190990 0.114851 0.189604 \n",
4545 "43 0.213861 6.028085 0.237624 0.194059 \n",
4546 "44 0.241584 3.620672 0.0851485 0.196535 \n",
4547 "45 0.162376 5.754476 0.221782 0.201485 \n",
4548 "46 0.20198 7.691650 0.342574 0.20297 \n",
4549 "47 0.219802 6.421577 0.267327 0.20495 \n",
4550 "48 0.0514851 11.573139 0.566337 0.208663 \n",
4551 "49 0.160396 4.710304 0.152475 0.209406 \n",
4552 "50 0.0613861 5.228678 0.192079 0.218317 "
4553 ]
4554 },
4555 "execution_count": 21,
4556 "metadata": {},
4557 "output_type": "execute_result"
4558 }
4559 ],
4560 "source": [
4561 "position_size = float(portfolio_size) / len(rv_dataframe.index)\n",
4562 "for i in range(0, len(rv_dataframe['Ticker'])-1):\n",
4563 " rv_dataframe.loc[i, 'Number of Shares to Buy'] = math.floor(position_size / rv_dataframe['Price'][i])\n",
4564 "rv_dataframe"
4565 ]
4566 },
4567 {
4568 "cell_type": "markdown",
4569 "metadata": {},
4570 "source": [
4571 "## Formatting Our Excel Output\n",
4572 "\n",
4573 "We will be using the XlsxWriter library for Python to create nicely-formatted Excel files.\n",
4574 "\n",
4575 "XlsxWriter is an excellent package and offers tons of customization. However, the tradeoff for this is that the library can seem very complicated to new users. Accordingly, this section will be fairly long because I want to do a good job of explaining how XlsxWriter works."
4576 ]
4577 },
4578 {
4579 "cell_type": "code",
4580 "execution_count": 22,
4581 "metadata": {},
4582 "outputs": [],
4583 "source": [
4584 "writer = pd.ExcelWriter('value_strategy.xlsx', engine='xlsxwriter')\n",
4585 "rv_dataframe.to_excel(writer, sheet_name='Value Strategy', index = False)"
4586 ]
4587 },
4588 {
4589 "cell_type": "markdown",
4590 "metadata": {},
4591 "source": [
4592 "## Creating the Formats We'll Need For Our .xlsx File\n",
4593 "You'll recall from our first project that formats include colors, fonts, and also symbols like % and $. We'll need four main formats for our Excel document:\n",
4594 "\n",
4595 "* String format for tickers\n",
4596 "* \\$XX.XX format for stock prices\n",
4597 "* \\$XX,XXX format for market capitalization\n",
4598 "* Integer format for the number of shares to purchase\n",
4599 "* Float formats with 1 decimal for each valuation metric\n",
4600 "\n",
4601 "Since we already built some formats in past sections of this course, I've included them below for you. Run this code cell before proceeding."
4602 ]
4603 },
4604 {
4605 "cell_type": "code",
4606 "execution_count": 23,
4607 "metadata": {},
4608 "outputs": [],
4609 "source": [
4610 "background_color = '#0a0a23'\n",
4611 "font_color = '#ffffff'\n",
4612 "\n",
4613 "string_template = writer.book.add_format(\n",
4614 " {\n",
4615 " 'font_color': font_color,\n",
4616 " 'bg_color': background_color,\n",
4617 " 'border': 1\n",
4618 " }\n",
4619 " )\n",
4620 "\n",
4621 "dollar_template = writer.book.add_format(\n",
4622 " {\n",
4623 " 'num_format':'$0.00',\n",
4624 " 'font_color': font_color,\n",
4625 " 'bg_color': background_color,\n",
4626 " 'border': 1\n",
4627 " }\n",
4628 " )\n",
4629 "\n",
4630 "integer_template = writer.book.add_format(\n",
4631 " {\n",
4632 " 'num_format':'0',\n",
4633 " 'font_color': font_color,\n",
4634 " 'bg_color': background_color,\n",
4635 " 'border': 1\n",
4636 " }\n",
4637 " )\n",
4638 "\n",
4639 "float_template = writer.book.add_format(\n",
4640 " {\n",
4641 " 'num_format':'0',\n",
4642 " 'font_color': font_color,\n",
4643 " 'bg_color': background_color,\n",
4644 " 'border': 1\n",
4645 " }\n",
4646 " )\n",
4647 "\n",
4648 "percent_template = writer.book.add_format(\n",
4649 " {\n",
4650 " 'num_format':'0.0%',\n",
4651 " 'font_color': font_color,\n",
4652 " 'bg_color': background_color,\n",
4653 " 'border': 1\n",
4654 " }\n",
4655 " )"
4656 ]
4657 },
4658 {
4659 "cell_type": "code",
4660 "execution_count": 24,
4661 "metadata": {},
4662 "outputs": [],
4663 "source": [
4664 "column_formats = {\n",
4665 " 'A': ['Ticker', string_template],\n",
4666 " 'B': ['Price', dollar_template],\n",
4667 " 'C': ['Number of Shares to Buy', integer_template],\n",
4668 " 'D': ['Price-to-Earnings Ratio', float_template],\n",
4669 " 'E': ['PE Percentile', percent_template],\n",
4670 " 'F': ['Price-to-Book Ratio', float_template],\n",
4671 " 'G': ['PB Percentile',percent_template],\n",
4672 " 'H': ['Price-to-Sales Ratio', float_template],\n",
4673 " 'I': ['PS Percentile', percent_template],\n",
4674 " 'J': ['EV/EBITDA', float_template],\n",
4675 " 'K': ['EV/EBITDA Percentile', percent_template],\n",
4676 " 'L': ['EV/GP', float_template],\n",
4677 " 'M': ['EV/GP Percentile', percent_template],\n",
4678 " 'N': ['RV Score', percent_template]\n",
4679 " }\n",
4680 "\n",
4681 "for column in column_formats.keys():\n",
4682 " writer.sheets['Value Strategy'].set_column(f'{column}:{column}', 25, column_formats[column][1])\n",
4683 " writer.sheets['Value Strategy'].write(f'{column}1', column_formats[column][0], column_formats[column][1])"
4684 ]
4685 },
4686 {
4687 "cell_type": "markdown",
4688 "metadata": {},
4689 "source": [
4690 "## Saving Our Excel Output\n",
4691 "As before, saving our Excel output is very easy:"
4692 ]
4693 },
4694 {
4695 "cell_type": "code",
4696 "execution_count": 25,
4697 "metadata": {},
4698 "outputs": [],
4699 "source": [
4700 "writer.save()"
4701 ]
4702 }
4703 ],
4704 "metadata": {
4705 "kernelspec": {
4706 "display_name": "Python 3",
4707 "language": "python",
4708 "name": "python3"
4709 },
4710 "language_info": {
4711 "codemirror_mode": {
4712 "name": "ipython",
4713 "version": 3
4714 },
4715 "file_extension": ".py",
4716 "mimetype": "text/x-python",
4717 "name": "python",
4718 "nbconvert_exporter": "python",
4719 "pygments_lexer": "ipython3",
4720 "version": "3.8.5"
4721 }
4722 },
4723 "nbformat": 4,
4724 "nbformat_minor": 4
4725 }