Created
January 15, 2020 17:09
-
-
Save sicongzhao/910707912ae7c0fae20b3c159c7162cd to your computer and use it in GitHub Desktop.
Calculating-Stock-Metrics.ipynb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "nbformat": 4, | |
| "nbformat_minor": 0, | |
| "metadata": { | |
| "kernelspec": { | |
| "display_name": "Python 3", | |
| "language": "python", | |
| "name": "python3" | |
| }, | |
| "language_info": { | |
| "codemirror_mode": { | |
| "name": "ipython", | |
| "version": 3 | |
| }, | |
| "file_extension": ".py", | |
| "mimetype": "text/x-python", | |
| "name": "python", | |
| "nbconvert_exporter": "python", | |
| "pygments_lexer": "ipython3", | |
| "version": "3.7.5" | |
| }, | |
| "colab": { | |
| "name": "Calculating-Stock-Metrics.ipynb", | |
| "provenance": [], | |
| "include_colab_link": true | |
| } | |
| }, | |
| "cells": [ | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "view-in-github", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "<a href=\"https://colab.research.google.com/gist/RyC37/910707912ae7c0fae20b3c159c7162cd/calculating-stock-metrics.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "pUVMRN-3IJU7", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "import pandas as pd\n", | |
| "!pip install yfinance\n", | |
| "import yfinance as yf" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "YbpgXGAJIJVE", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "### 1.Load Data" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "7uaPKUZHIJVH", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "aapl = pd.read_csv('https://raw.githubusercontent.com/RyC37/Value-Investing-In-Python/master/data/AAPL%20Key%20Ratios.csv', skiprows=2, index_col='Unnamed: 0')" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "V3GgVINrIJVM", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 360 | |
| }, | |
| "outputId": "84b8da44-ead2-492a-9e1e-54bce1a42fd5" | |
| }, | |
| "source": [ | |
| "aapl.head()" | |
| ], | |
| "execution_count": 12, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>2010-09</th>\n", | |
| " <th>2011-09</th>\n", | |
| " <th>2012-09</th>\n", | |
| " <th>2013-09</th>\n", | |
| " <th>2014-09</th>\n", | |
| " <th>2015-09</th>\n", | |
| " <th>2016-09</th>\n", | |
| " <th>2017-09</th>\n", | |
| " <th>2018-09</th>\n", | |
| " <th>2019-09</th>\n", | |
| " <th>TTM</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>Revenue USD Mil</th>\n", | |
| " <td>65,225</td>\n", | |
| " <td>108,249</td>\n", | |
| " <td>156,508</td>\n", | |
| " <td>170,910</td>\n", | |
| " <td>182,795</td>\n", | |
| " <td>233,715</td>\n", | |
| " <td>215,639</td>\n", | |
| " <td>229,234</td>\n", | |
| " <td>265,595</td>\n", | |
| " <td>260,174</td>\n", | |
| " <td>260,174</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>Gross Margin %</th>\n", | |
| " <td>39.4</td>\n", | |
| " <td>40.5</td>\n", | |
| " <td>43.9</td>\n", | |
| " <td>37.6</td>\n", | |
| " <td>38.6</td>\n", | |
| " <td>40.1</td>\n", | |
| " <td>39.1</td>\n", | |
| " <td>38.5</td>\n", | |
| " <td>38.3</td>\n", | |
| " <td>37.8</td>\n", | |
| " <td>37.8</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>Operating Income USD Mil</th>\n", | |
| " <td>18,385</td>\n", | |
| " <td>33,790</td>\n", | |
| " <td>55,241</td>\n", | |
| " <td>48,999</td>\n", | |
| " <td>52,503</td>\n", | |
| " <td>71,230</td>\n", | |
| " <td>60,024</td>\n", | |
| " <td>61,344</td>\n", | |
| " <td>70,898</td>\n", | |
| " <td>63,930</td>\n", | |
| " <td>63,930</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>Operating Margin %</th>\n", | |
| " <td>28.2</td>\n", | |
| " <td>31.2</td>\n", | |
| " <td>35.3</td>\n", | |
| " <td>28.7</td>\n", | |
| " <td>28.7</td>\n", | |
| " <td>30.5</td>\n", | |
| " <td>27.8</td>\n", | |
| " <td>26.8</td>\n", | |
| " <td>26.7</td>\n", | |
| " <td>24.6</td>\n", | |
| " <td>24.6</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>Net Income USD Mil</th>\n", | |
| " <td>14,013</td>\n", | |
| " <td>25,922</td>\n", | |
| " <td>41,733</td>\n", | |
| " <td>37,037</td>\n", | |
| " <td>39,510</td>\n", | |
| " <td>53,394</td>\n", | |
| " <td>45,687</td>\n", | |
| " <td>48,351</td>\n", | |
| " <td>59,531</td>\n", | |
| " <td>55,256</td>\n", | |
| " <td>55,256</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " 2010-09 2011-09 2012-09 ... 2018-09 2019-09 TTM\n", | |
| "Revenue USD Mil 65,225 108,249 156,508 ... 265,595 260,174 260,174\n", | |
| "Gross Margin % 39.4 40.5 43.9 ... 38.3 37.8 37.8\n", | |
| "Operating Income USD Mil 18,385 33,790 55,241 ... 70,898 63,930 63,930\n", | |
| "Operating Margin % 28.2 31.2 35.3 ... 26.7 24.6 24.6\n", | |
| "Net Income USD Mil 14,013 25,922 41,733 ... 59,531 55,256 55,256\n", | |
| "\n", | |
| "[5 rows x 11 columns]" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 12 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "AYdrkRSRIJVT", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "### 2.Read Metrics From Financial Data\n", | |
| "\n", | |
| "##### (1)Shares Outstanding" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "j_vFXw--IJVU", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "fc6f0543-f630-4026-d01e-aa40cdb0487b" | |
| }, | |
| "source": [ | |
| "# Read the latest data point\n", | |
| "aapl.loc['Shares Mil', '2019-09']" | |
| ], | |
| "execution_count": 13, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "'4,649'" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 13 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "cEnOFDWVIJVa", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "2d0f567f-2cdb-4d13-f89d-3274afe6c5ef" | |
| }, | |
| "source": [ | |
| "# Convert to number, could also use int()\n", | |
| "float(aapl.loc['Shares Mil', '2019-09'].replace(',',''))" | |
| ], | |
| "execution_count": 14, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "4649.0" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 14 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "W3kum0TuIJVh", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "bfec8eca-f699-4bbf-e5c1-40f531ff727e" | |
| }, | |
| "source": [ | |
| "# Read the historical data\n", | |
| "aapl.loc['Shares Mil', ]" | |
| ], | |
| "execution_count": 15, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 6,473\n", | |
| "2011-09 6,557\n", | |
| "2012-09 6,617\n", | |
| "2013-09 6,522\n", | |
| "2014-09 6,123\n", | |
| "2015-09 5,793\n", | |
| "2016-09 5,500\n", | |
| "2017-09 5,252\n", | |
| "2018-09 5,000\n", | |
| "2019-09 4,649\n", | |
| "TTM 4,649\n", | |
| "Name: Shares Mil, dtype: object" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 15 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "24xzTg-oIJVo", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "a50446d8-3aa5-4c79-9a0e-34481cd2e0bd" | |
| }, | |
| "source": [ | |
| "# Convert data type to float\n", | |
| "aapl.loc['Shares Mil', ].str.replace(',','').astype(float)" | |
| ], | |
| "execution_count": 16, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 6473.0\n", | |
| "2011-09 6557.0\n", | |
| "2012-09 6617.0\n", | |
| "2013-09 6522.0\n", | |
| "2014-09 6123.0\n", | |
| "2015-09 5793.0\n", | |
| "2016-09 5500.0\n", | |
| "2017-09 5252.0\n", | |
| "2018-09 5000.0\n", | |
| "2019-09 4649.0\n", | |
| "TTM 4649.0\n", | |
| "Name: Shares Mil, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 16 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "UqttGWPBIJVv", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (2)EPS" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "sN3kc5ERIJVw", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "aab3de88-f291-41b3-a5d9-e701db0b3a65" | |
| }, | |
| "source": [ | |
| "float(aapl.loc['Earnings Per Share USD', '2019-09'])" | |
| ], | |
| "execution_count": 17, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "11.89" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 17 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "dxPWUzEWIJVy", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "ae592d52-adf9-41a9-a422-3263b9916a69" | |
| }, | |
| "source": [ | |
| "aapl.loc['Earnings Per Share USD', ].astype(float)" | |
| ], | |
| "execution_count": 18, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 2.16\n", | |
| "2011-09 3.95\n", | |
| "2012-09 6.31\n", | |
| "2013-09 5.68\n", | |
| "2014-09 6.45\n", | |
| "2015-09 9.22\n", | |
| "2016-09 8.31\n", | |
| "2017-09 9.21\n", | |
| "2018-09 11.91\n", | |
| "2019-09 11.89\n", | |
| "TTM 11.89\n", | |
| "Name: Earnings Per Share USD, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 18 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "OdZJbvJgIJV3", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (3)Dividend Rate" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "sLaNDRjJIJV4", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "def search_value(index_name, date):\n", | |
| " '''This function return a specific stock metric of at a specific date\n", | |
| " from aapl dataframe\n", | |
| " '''\n", | |
| " return float(aapl.loc[index_name, date])" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "YGck0iJLIJV_", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "07980dfc-a1e0-464f-b44a-aac71e719f6c" | |
| }, | |
| "source": [ | |
| "search_value('Dividends USD', '2019-09')" | |
| ], | |
| "execution_count": 20, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "3.0" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 20 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "sa0SIZO7IJWB", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "def historical_value(index_name):\n", | |
| " return aapl.loc[index_name, ].astype(float)" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "lBuLI-XmIJWD", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "966f7c3f-11aa-46ae-f05e-83215b7e778e" | |
| }, | |
| "source": [ | |
| "historical_value('Dividends USD')" | |
| ], | |
| "execution_count": 22, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 NaN\n", | |
| "2011-09 NaN\n", | |
| "2012-09 0.38\n", | |
| "2013-09 1.63\n", | |
| "2014-09 1.81\n", | |
| "2015-09 1.98\n", | |
| "2016-09 2.18\n", | |
| "2017-09 2.40\n", | |
| "2018-09 2.72\n", | |
| "2019-09 3.00\n", | |
| "TTM 3.00\n", | |
| "Name: Dividends USD, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 22 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "iggPHMMBIJWF", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "# NaN manipulation will be introduced later" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "6IrS3Vk1IJWG", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (4)Dividend Yield" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "rBBJbfElIJWI", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "73402a6a-589a-4dca-92c8-2fafdb1900ec" | |
| }, | |
| "source": [ | |
| "search_value('Payout Ratio % *', '2019-09')" | |
| ], | |
| "execution_count": 24, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "25.1" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 24 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "YPvaan_AIJWM", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "e148542b-31d0-4ae2-8cb8-4b5d98c62085" | |
| }, | |
| "source": [ | |
| "historical_value('Payout Ratio % *')" | |
| ], | |
| "execution_count": 25, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 NaN\n", | |
| "2011-09 NaN\n", | |
| "2012-09 NaN\n", | |
| "2013-09 27.4\n", | |
| "2014-09 28.5\n", | |
| "2015-09 22.3\n", | |
| "2016-09 24.8\n", | |
| "2017-09 26.5\n", | |
| "2018-09 23.7\n", | |
| "2019-09 25.1\n", | |
| "TTM 25.2\n", | |
| "Name: Payout Ratio % *, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 25 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "t5MmSkvwIJWO", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (5)Debt/Equity Ratio" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "GsK8JpzXIJWO", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "d6effc84-92da-4a68-a0b2-6b0e459bde39" | |
| }, | |
| "source": [ | |
| "search_value('Debt/Equity', '2019-09')" | |
| ], | |
| "execution_count": 26, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "1.01" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 26 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "arXQQOGVIJWQ", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "fc10a2b3-3a2e-44f2-f355-416dfd8798a0" | |
| }, | |
| "source": [ | |
| "historical_value('Debt/Equity')" | |
| ], | |
| "execution_count": 27, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 NaN\n", | |
| "2011-09 NaN\n", | |
| "2012-09 NaN\n", | |
| "2013-09 0.14\n", | |
| "2014-09 0.26\n", | |
| "2015-09 0.45\n", | |
| "2016-09 0.59\n", | |
| "2017-09 0.73\n", | |
| "2018-09 0.87\n", | |
| "2019-09 1.01\n", | |
| "TTM 1.01\n", | |
| "Name: Debt/Equity, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 27 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "7KazUwjrIJWT", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (6)Book Value Per Share" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "h6RqEP8WIJWU", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "922c45d2-db1d-41a5-e603-c4c50f4c5162" | |
| }, | |
| "source": [ | |
| "search_value('Book Value Per Share * USD', '2019-09')" | |
| ], | |
| "execution_count": 28, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "21.71" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 28 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "FYlRNB-gIJWW", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "d3851fff-ee4a-4c08-9852-8a402c156213" | |
| }, | |
| "source": [ | |
| "historical_value('Book Value Per Share * USD')" | |
| ], | |
| "execution_count": 29, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 7.45\n", | |
| "2011-09 11.78\n", | |
| "2012-09 16.99\n", | |
| "2013-09 19.60\n", | |
| "2014-09 20.62\n", | |
| "2015-09 22.53\n", | |
| "2016-09 23.71\n", | |
| "2017-09 25.83\n", | |
| "2018-09 24.17\n", | |
| "2019-09 21.71\n", | |
| "TTM 20.64\n", | |
| "Name: Book Value Per Share * USD, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 29 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "JGDD-821IJWZ", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (7)ROE (Return on Equity)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "a_5z0KgZIJWZ", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "db1ad314-2bad-479b-e5fa-82a71651c013" | |
| }, | |
| "source": [ | |
| "search_value('Return on Equity %', '2019-09')" | |
| ], | |
| "execution_count": 30, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "55.92" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 30 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "RTYuiIBpIJWb", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "7e7c41f5-1365-4925-d933-81ebf2cd65b6" | |
| }, | |
| "source": [ | |
| "historical_value('Return on Equity %')" | |
| ], | |
| "execution_count": 31, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 35.28\n", | |
| "2011-09 41.67\n", | |
| "2012-09 42.84\n", | |
| "2013-09 30.64\n", | |
| "2014-09 33.61\n", | |
| "2015-09 46.25\n", | |
| "2016-09 36.90\n", | |
| "2017-09 36.87\n", | |
| "2018-09 49.36\n", | |
| "2019-09 55.92\n", | |
| "TTM 55.92\n", | |
| "Name: Return on Equity %, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 31 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "okzk5BQrIJWd", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (8)Current Ratio" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "ynvMbpk0IJWd", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "6e6c5ef3-8105-4d5d-b25a-57d87d6e6c6a" | |
| }, | |
| "source": [ | |
| "search_value('Current Ratio', '2019-09')" | |
| ], | |
| "execution_count": 32, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "1.54" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 32 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "m09tKRPqIJWf", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 221 | |
| }, | |
| "outputId": "78660853-ab7b-439f-c3a7-c812cfd922f6" | |
| }, | |
| "source": [ | |
| "historical_value('Current Ratio')" | |
| ], | |
| "execution_count": 33, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "2010-09 2.01\n", | |
| "2011-09 1.61\n", | |
| "2012-09 1.50\n", | |
| "2013-09 1.68\n", | |
| "2014-09 1.08\n", | |
| "2015-09 1.11\n", | |
| "2016-09 1.35\n", | |
| "2017-09 1.28\n", | |
| "2018-09 1.12\n", | |
| "2019-09 1.54\n", | |
| "TTM 1.54\n", | |
| "Name: Current Ratio, dtype: float64" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 33 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "miWi8QhJIJWh", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "### 3.Get Stock Price" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "ohiyR2huIJWh", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "apple = yf.Ticker('AAPL')" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "G2eWT2MCIJWi", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "apple_price = apple.history(period='max')" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "7P0lQIxeIJWj", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "### 4.Calculate P/E & P/BV\n", | |
| "\n", | |
| "##### (1) Latest P/E" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "NO-ekSyoIJWk", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "c33fac86-41ba-4b20-fbf8-9038f6fdbdd8" | |
| }, | |
| "source": [ | |
| "latest_price = apple_price.loc['2020-01-14','Close']\n", | |
| "latest_eps = search_value('Earnings Per Share USD', '2019-09')\n", | |
| "latest_PtoE = latest_price/latest_eps\n", | |
| "print(latest_PtoE)" | |
| ], | |
| "execution_count": 36, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "text": [ | |
| "26.297729184188395\n" | |
| ], | |
| "name": "stdout" | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "3sYRzw9OIJWl", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (2) Latest P/BV" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "UlF_ATanIJWl", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 34 | |
| }, | |
| "outputId": "fa22cd43-dcee-47d8-ddf5-49c190d620c3" | |
| }, | |
| "source": [ | |
| "latest_price = apple_price.loc['2020-01-14','Close']\n", | |
| "latest_eps = search_value('Book Value Per Share * USD', '2019-09')\n", | |
| "latest_PtoBV = latest_price/latest_eps\n", | |
| "print(latest_PtoBV)" | |
| ], | |
| "execution_count": 37, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "text": [ | |
| "14.402579456471672\n" | |
| ], | |
| "name": "stdout" | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "cf9H7tWeIJWn", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "### 5.Formatting\n", | |
| "\n", | |
| "##### (1) Traspose Dataframe\n", | |
| "For the convenience of calculation, we want to transpose the dataframe. Such that the time would be rows and metrics would be columns." | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "kRxRWL1ZIJWn", | |
| "colab_type": "code", | |
| "colab": {} | |
| }, | |
| "source": [ | |
| "aapl = aapl.T" | |
| ], | |
| "execution_count": 0, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "nV-mDFZ_IJWp", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 423 | |
| }, | |
| "outputId": "fcb75144-0c57-4858-d96c-c55ce0095dc8" | |
| }, | |
| "source": [ | |
| "aapl.head()" | |
| ], | |
| "execution_count": 39, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>Revenue USD Mil</th>\n", | |
| " <th>Gross Margin %</th>\n", | |
| " <th>Operating Income USD Mil</th>\n", | |
| " <th>Operating Margin %</th>\n", | |
| " <th>Net Income USD Mil</th>\n", | |
| " <th>Earnings Per Share USD</th>\n", | |
| " <th>Dividends USD</th>\n", | |
| " <th>Payout Ratio % *</th>\n", | |
| " <th>Shares Mil</th>\n", | |
| " <th>Book Value Per Share * USD</th>\n", | |
| " <th>Operating Cash Flow USD Mil</th>\n", | |
| " <th>Cap Spending USD Mil</th>\n", | |
| " <th>Free Cash Flow USD Mil</th>\n", | |
| " <th>Free Cash Flow Per Share * USD</th>\n", | |
| " <th>Working Capital USD Mil</th>\n", | |
| " <th>Key Ratios -> Profitability</th>\n", | |
| " <th>Margins % of Sales</th>\n", | |
| " <th>Revenue</th>\n", | |
| " <th>COGS</th>\n", | |
| " <th>Gross Margin</th>\n", | |
| " <th>SG&A</th>\n", | |
| " <th>R&D</th>\n", | |
| " <th>Other</th>\n", | |
| " <th>Operating Margin</th>\n", | |
| " <th>Net Int Inc & Other</th>\n", | |
| " <th>EBT Margin</th>\n", | |
| " <th>Profitability</th>\n", | |
| " <th>Tax Rate %</th>\n", | |
| " <th>Net Margin %</th>\n", | |
| " <th>Asset Turnover (Average)</th>\n", | |
| " <th>Return on Assets %</th>\n", | |
| " <th>Financial Leverage (Average)</th>\n", | |
| " <th>Return on Equity %</th>\n", | |
| " <th>Return on Invested Capital %</th>\n", | |
| " <th>Interest Coverage</th>\n", | |
| " <th>Key Ratios -> Growth</th>\n", | |
| " <th>NaN</th>\n", | |
| " <th>Revenue %</th>\n", | |
| " <th>Year over Year</th>\n", | |
| " <th>3-Year Average</th>\n", | |
| " <th>...</th>\n", | |
| " <th>Cap Ex as a % of Sales</th>\n", | |
| " <th>Free Cash Flow/Sales %</th>\n", | |
| " <th>Free Cash Flow/Net Income</th>\n", | |
| " <th>Key Ratios -> Financial Health</th>\n", | |
| " <th>Balance Sheet Items (in %)</th>\n", | |
| " <th>Cash & Short-Term Investments</th>\n", | |
| " <th>Accounts Receivable</th>\n", | |
| " <th>Inventory</th>\n", | |
| " <th>Other Current Assets</th>\n", | |
| " <th>Total Current Assets</th>\n", | |
| " <th>Net PP&E</th>\n", | |
| " <th>Intangibles</th>\n", | |
| " <th>Other Long-Term Assets</th>\n", | |
| " <th>Total Assets</th>\n", | |
| " <th>Accounts Payable</th>\n", | |
| " <th>Short-Term Debt</th>\n", | |
| " <th>Taxes Payable</th>\n", | |
| " <th>Accrued Liabilities</th>\n", | |
| " <th>Other Short-Term Liabilities</th>\n", | |
| " <th>Total Current Liabilities</th>\n", | |
| " <th>Long-Term Debt</th>\n", | |
| " <th>Other Long-Term Liabilities</th>\n", | |
| " <th>Total Liabilities</th>\n", | |
| " <th>Total Stockholders' Equity</th>\n", | |
| " <th>Total Liabilities & Equity</th>\n", | |
| " <th>Liquidity/Financial Health</th>\n", | |
| " <th>Current Ratio</th>\n", | |
| " <th>Quick Ratio</th>\n", | |
| " <th>Financial Leverage</th>\n", | |
| " <th>Debt/Equity</th>\n", | |
| " <th>Key Ratios -> Efficiency Ratios</th>\n", | |
| " <th>Efficiency</th>\n", | |
| " <th>Days Sales Outstanding</th>\n", | |
| " <th>Days Inventory</th>\n", | |
| " <th>Payables Period</th>\n", | |
| " <th>Cash Conversion Cycle</th>\n", | |
| " <th>Receivables Turnover</th>\n", | |
| " <th>Inventory Turnover</th>\n", | |
| " <th>Fixed Assets Turnover</th>\n", | |
| " <th>Asset Turnover</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>2010-09</th>\n", | |
| " <td>65,225</td>\n", | |
| " <td>39.4</td>\n", | |
| " <td>18,385</td>\n", | |
| " <td>28.2</td>\n", | |
| " <td>14,013</td>\n", | |
| " <td>2.16</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>6,473</td>\n", | |
| " <td>7.45</td>\n", | |
| " <td>18,595</td>\n", | |
| " <td>-2,121</td>\n", | |
| " <td>16,474</td>\n", | |
| " <td>2.54</td>\n", | |
| " <td>20,956</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2010-09</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>60.62</td>\n", | |
| " <td>39.38</td>\n", | |
| " <td>8.46</td>\n", | |
| " <td>2.73</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>28.19</td>\n", | |
| " <td>0.24</td>\n", | |
| " <td>28.42</td>\n", | |
| " <td>2010-09</td>\n", | |
| " <td>24.42</td>\n", | |
| " <td>21.48</td>\n", | |
| " <td>1.06</td>\n", | |
| " <td>22.84</td>\n", | |
| " <td>1.57</td>\n", | |
| " <td>35.28</td>\n", | |
| " <td>34.77</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2010-09</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>52.02</td>\n", | |
| " <td>39.54</td>\n", | |
| " <td>...</td>\n", | |
| " <td>3.25</td>\n", | |
| " <td>25.26</td>\n", | |
| " <td>1.18</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2010-09</td>\n", | |
| " <td>34.08</td>\n", | |
| " <td>13.20</td>\n", | |
| " <td>1.40</td>\n", | |
| " <td>6.76</td>\n", | |
| " <td>55.44</td>\n", | |
| " <td>6.34</td>\n", | |
| " <td>1.44</td>\n", | |
| " <td>36.78</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>15.98</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>0.28</td>\n", | |
| " <td>5.87</td>\n", | |
| " <td>5.43</td>\n", | |
| " <td>27.56</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>8.87</td>\n", | |
| " <td>36.43</td>\n", | |
| " <td>63.57</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>2010-09</td>\n", | |
| " <td>2.01</td>\n", | |
| " <td>1.72</td>\n", | |
| " <td>1.57</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2010-09</td>\n", | |
| " <td>24.82</td>\n", | |
| " <td>6.95</td>\n", | |
| " <td>81.30</td>\n", | |
| " <td>-49.53</td>\n", | |
| " <td>14.71</td>\n", | |
| " <td>52.51</td>\n", | |
| " <td>16.89</td>\n", | |
| " <td>1.06</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2011-09</th>\n", | |
| " <td>108,249</td>\n", | |
| " <td>40.5</td>\n", | |
| " <td>33,790</td>\n", | |
| " <td>31.2</td>\n", | |
| " <td>25,922</td>\n", | |
| " <td>3.95</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>6,557</td>\n", | |
| " <td>11.78</td>\n", | |
| " <td>37,529</td>\n", | |
| " <td>-7,452</td>\n", | |
| " <td>30,077</td>\n", | |
| " <td>4.59</td>\n", | |
| " <td>17,018</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2011-09</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>59.52</td>\n", | |
| " <td>40.48</td>\n", | |
| " <td>7.02</td>\n", | |
| " <td>2.24</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>31.22</td>\n", | |
| " <td>0.38</td>\n", | |
| " <td>31.60</td>\n", | |
| " <td>2011-09</td>\n", | |
| " <td>24.22</td>\n", | |
| " <td>23.95</td>\n", | |
| " <td>1.13</td>\n", | |
| " <td>27.07</td>\n", | |
| " <td>1.52</td>\n", | |
| " <td>41.67</td>\n", | |
| " <td>41.13</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2011-09</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>65.96</td>\n", | |
| " <td>49.37</td>\n", | |
| " <td>...</td>\n", | |
| " <td>6.88</td>\n", | |
| " <td>27.79</td>\n", | |
| " <td>1.16</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2011-09</td>\n", | |
| " <td>22.30</td>\n", | |
| " <td>10.07</td>\n", | |
| " <td>0.67</td>\n", | |
| " <td>5.62</td>\n", | |
| " <td>38.66</td>\n", | |
| " <td>6.68</td>\n", | |
| " <td>3.81</td>\n", | |
| " <td>50.85</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>12.57</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>0.98</td>\n", | |
| " <td>6.46</td>\n", | |
| " <td>4.02</td>\n", | |
| " <td>24.04</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>10.13</td>\n", | |
| " <td>34.16</td>\n", | |
| " <td>65.84</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>2011-09</td>\n", | |
| " <td>1.61</td>\n", | |
| " <td>1.35</td>\n", | |
| " <td>1.52</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2011-09</td>\n", | |
| " <td>18.34</td>\n", | |
| " <td>5.17</td>\n", | |
| " <td>75.48</td>\n", | |
| " <td>-51.96</td>\n", | |
| " <td>19.90</td>\n", | |
| " <td>70.53</td>\n", | |
| " <td>17.26</td>\n", | |
| " <td>1.13</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2012-09</th>\n", | |
| " <td>156,508</td>\n", | |
| " <td>43.9</td>\n", | |
| " <td>55,241</td>\n", | |
| " <td>35.3</td>\n", | |
| " <td>41,733</td>\n", | |
| " <td>6.31</td>\n", | |
| " <td>0.38</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>6,617</td>\n", | |
| " <td>16.99</td>\n", | |
| " <td>50,856</td>\n", | |
| " <td>-9,402</td>\n", | |
| " <td>41,454</td>\n", | |
| " <td>6.31</td>\n", | |
| " <td>19,111</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2012-09</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>56.13</td>\n", | |
| " <td>43.87</td>\n", | |
| " <td>6.42</td>\n", | |
| " <td>2.16</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>35.30</td>\n", | |
| " <td>0.33</td>\n", | |
| " <td>35.63</td>\n", | |
| " <td>2012-09</td>\n", | |
| " <td>25.16</td>\n", | |
| " <td>26.67</td>\n", | |
| " <td>1.07</td>\n", | |
| " <td>28.54</td>\n", | |
| " <td>1.49</td>\n", | |
| " <td>42.84</td>\n", | |
| " <td>42.01</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2012-09</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>44.58</td>\n", | |
| " <td>53.94</td>\n", | |
| " <td>...</td>\n", | |
| " <td>6.01</td>\n", | |
| " <td>26.49</td>\n", | |
| " <td>0.99</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2012-09</td>\n", | |
| " <td>16.54</td>\n", | |
| " <td>10.62</td>\n", | |
| " <td>0.45</td>\n", | |
| " <td>5.14</td>\n", | |
| " <td>32.75</td>\n", | |
| " <td>8.78</td>\n", | |
| " <td>3.04</td>\n", | |
| " <td>55.43</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>12.03</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>0.87</td>\n", | |
| " <td>1.45</td>\n", | |
| " <td>7.54</td>\n", | |
| " <td>21.89</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>10.97</td>\n", | |
| " <td>32.86</td>\n", | |
| " <td>67.14</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>2012-09</td>\n", | |
| " <td>1.50</td>\n", | |
| " <td>1.24</td>\n", | |
| " <td>1.49</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2012-09</td>\n", | |
| " <td>19.01</td>\n", | |
| " <td>3.26</td>\n", | |
| " <td>74.38</td>\n", | |
| " <td>-52.13</td>\n", | |
| " <td>19.20</td>\n", | |
| " <td>112.12</td>\n", | |
| " <td>13.48</td>\n", | |
| " <td>1.07</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2013-09</th>\n", | |
| " <td>170,910</td>\n", | |
| " <td>37.6</td>\n", | |
| " <td>48,999</td>\n", | |
| " <td>28.7</td>\n", | |
| " <td>37,037</td>\n", | |
| " <td>5.68</td>\n", | |
| " <td>1.63</td>\n", | |
| " <td>27.4</td>\n", | |
| " <td>6,522</td>\n", | |
| " <td>19.60</td>\n", | |
| " <td>53,666</td>\n", | |
| " <td>-9,076</td>\n", | |
| " <td>44,590</td>\n", | |
| " <td>6.46</td>\n", | |
| " <td>29,628</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2013-09</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>62.38</td>\n", | |
| " <td>37.62</td>\n", | |
| " <td>6.34</td>\n", | |
| " <td>2.62</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>28.67</td>\n", | |
| " <td>0.68</td>\n", | |
| " <td>29.35</td>\n", | |
| " <td>2013-09</td>\n", | |
| " <td>26.15</td>\n", | |
| " <td>21.67</td>\n", | |
| " <td>0.89</td>\n", | |
| " <td>19.34</td>\n", | |
| " <td>1.68</td>\n", | |
| " <td>30.64</td>\n", | |
| " <td>26.08</td>\n", | |
| " <td>369.79</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2013-09</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>9.20</td>\n", | |
| " <td>37.86</td>\n", | |
| " <td>...</td>\n", | |
| " <td>5.31</td>\n", | |
| " <td>26.09</td>\n", | |
| " <td>1.20</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2013-09</td>\n", | |
| " <td>19.59</td>\n", | |
| " <td>9.97</td>\n", | |
| " <td>0.85</td>\n", | |
| " <td>4.99</td>\n", | |
| " <td>35.40</td>\n", | |
| " <td>8.02</td>\n", | |
| " <td>2.78</td>\n", | |
| " <td>53.80</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>10.81</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>0.58</td>\n", | |
| " <td>2.06</td>\n", | |
| " <td>7.65</td>\n", | |
| " <td>21.09</td>\n", | |
| " <td>8.19</td>\n", | |
| " <td>11.03</td>\n", | |
| " <td>40.31</td>\n", | |
| " <td>59.69</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>2013-09</td>\n", | |
| " <td>1.68</td>\n", | |
| " <td>1.40</td>\n", | |
| " <td>1.68</td>\n", | |
| " <td>0.14</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2013-09</td>\n", | |
| " <td>25.66</td>\n", | |
| " <td>4.37</td>\n", | |
| " <td>74.54</td>\n", | |
| " <td>-44.50</td>\n", | |
| " <td>14.22</td>\n", | |
| " <td>83.45</td>\n", | |
| " <td>10.67</td>\n", | |
| " <td>0.89</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2014-09</th>\n", | |
| " <td>182,795</td>\n", | |
| " <td>38.6</td>\n", | |
| " <td>52,503</td>\n", | |
| " <td>28.7</td>\n", | |
| " <td>39,510</td>\n", | |
| " <td>6.45</td>\n", | |
| " <td>1.81</td>\n", | |
| " <td>28.5</td>\n", | |
| " <td>6,123</td>\n", | |
| " <td>20.62</td>\n", | |
| " <td>59,713</td>\n", | |
| " <td>-9,813</td>\n", | |
| " <td>49,900</td>\n", | |
| " <td>7.73</td>\n", | |
| " <td>5,083</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2014-09</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>61.41</td>\n", | |
| " <td>38.59</td>\n", | |
| " <td>6.56</td>\n", | |
| " <td>3.30</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>28.72</td>\n", | |
| " <td>0.54</td>\n", | |
| " <td>29.26</td>\n", | |
| " <td>2014-09</td>\n", | |
| " <td>26.13</td>\n", | |
| " <td>21.61</td>\n", | |
| " <td>0.83</td>\n", | |
| " <td>18.01</td>\n", | |
| " <td>2.08</td>\n", | |
| " <td>33.61</td>\n", | |
| " <td>26.20</td>\n", | |
| " <td>140.28</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2014-09</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>6.95</td>\n", | |
| " <td>19.08</td>\n", | |
| " <td>...</td>\n", | |
| " <td>5.37</td>\n", | |
| " <td>27.30</td>\n", | |
| " <td>1.26</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2014-09</td>\n", | |
| " <td>10.82</td>\n", | |
| " <td>11.74</td>\n", | |
| " <td>0.91</td>\n", | |
| " <td>6.09</td>\n", | |
| " <td>29.56</td>\n", | |
| " <td>8.90</td>\n", | |
| " <td>3.78</td>\n", | |
| " <td>57.77</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>13.02</td>\n", | |
| " <td>2.72</td>\n", | |
| " <td>0.52</td>\n", | |
| " <td>2.80</td>\n", | |
| " <td>8.31</td>\n", | |
| " <td>27.37</td>\n", | |
| " <td>12.50</td>\n", | |
| " <td>12.02</td>\n", | |
| " <td>51.89</td>\n", | |
| " <td>48.11</td>\n", | |
| " <td>100.00</td>\n", | |
| " <td>2014-09</td>\n", | |
| " <td>1.08</td>\n", | |
| " <td>0.82</td>\n", | |
| " <td>2.08</td>\n", | |
| " <td>0.26</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>2014-09</td>\n", | |
| " <td>30.51</td>\n", | |
| " <td>6.30</td>\n", | |
| " <td>85.45</td>\n", | |
| " <td>-48.64</td>\n", | |
| " <td>11.96</td>\n", | |
| " <td>57.94</td>\n", | |
| " <td>9.82</td>\n", | |
| " <td>0.83</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "<p>5 rows × 101 columns</p>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " Revenue USD Mil Gross Margin % ... Fixed Assets Turnover Asset Turnover\n", | |
| "2010-09 65,225 39.4 ... 16.89 1.06\n", | |
| "2011-09 108,249 40.5 ... 17.26 1.13\n", | |
| "2012-09 156,508 43.9 ... 13.48 1.07\n", | |
| "2013-09 170,910 37.6 ... 10.67 0.89\n", | |
| "2014-09 182,795 38.6 ... 9.82 0.83\n", | |
| "\n", | |
| "[5 rows x 101 columns]" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 39 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "Vkp6AyXAIJWr", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "##### (2) Extract metrics we need\n", | |
| "The financial data contains a lot of metrics, we are not going to use most of them. So, let's create a smaller dataframe with whatever metrics we will use." | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "l9qYfm3KIJWr", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 357 | |
| }, | |
| "outputId": "d61526f0-f7d3-4615-e5af-7e2ee9d2f8be" | |
| }, | |
| "source": [ | |
| "col_names = ['Shares Mil','Earnings Per Share USD','Dividends USD',\n", | |
| " 'Payout Ratio % *', 'Debt/Equity', 'Book Value Per Share * USD',\n", | |
| " 'Return on Equity %', 'Current Ratio']\n", | |
| "aapl_df = aapl[col_names]\n", | |
| "aapl_df.head()" | |
| ], | |
| "execution_count": 40, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>Shares Mil</th>\n", | |
| " <th>Earnings Per Share USD</th>\n", | |
| " <th>Dividends USD</th>\n", | |
| " <th>Payout Ratio % *</th>\n", | |
| " <th>Debt/Equity</th>\n", | |
| " <th>Book Value Per Share * USD</th>\n", | |
| " <th>Return on Equity %</th>\n", | |
| " <th>Current Ratio</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>2010-09</th>\n", | |
| " <td>6,473</td>\n", | |
| " <td>2.16</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>7.45</td>\n", | |
| " <td>35.28</td>\n", | |
| " <td>2.01</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2011-09</th>\n", | |
| " <td>6,557</td>\n", | |
| " <td>3.95</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>11.78</td>\n", | |
| " <td>41.67</td>\n", | |
| " <td>1.61</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2012-09</th>\n", | |
| " <td>6,617</td>\n", | |
| " <td>6.31</td>\n", | |
| " <td>0.38</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>16.99</td>\n", | |
| " <td>42.84</td>\n", | |
| " <td>1.50</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2013-09</th>\n", | |
| " <td>6,522</td>\n", | |
| " <td>5.68</td>\n", | |
| " <td>1.63</td>\n", | |
| " <td>27.4</td>\n", | |
| " <td>0.14</td>\n", | |
| " <td>19.60</td>\n", | |
| " <td>30.64</td>\n", | |
| " <td>1.68</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2014-09</th>\n", | |
| " <td>6,123</td>\n", | |
| " <td>6.45</td>\n", | |
| " <td>1.81</td>\n", | |
| " <td>28.5</td>\n", | |
| " <td>0.26</td>\n", | |
| " <td>20.62</td>\n", | |
| " <td>33.61</td>\n", | |
| " <td>1.08</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " Shares Mil Earnings Per Share USD ... Return on Equity % Current Ratio\n", | |
| "2010-09 6,473 2.16 ... 35.28 2.01\n", | |
| "2011-09 6,557 3.95 ... 41.67 1.61\n", | |
| "2012-09 6,617 6.31 ... 42.84 1.50\n", | |
| "2013-09 6,522 5.68 ... 30.64 1.68\n", | |
| "2014-09 6,123 6.45 ... 33.61 1.08\n", | |
| "\n", | |
| "[5 rows x 8 columns]" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 40 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "Y5Tc4CImIJWt", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "Now we have a concise dataframe `aapl_df`, which is easier to use. But the awkward name of columns still puzzles me. Let's get rid of special symbols and unify the name style by renaming them.\n", | |
| "\n", | |
| "##### (3) Rename Columns" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "vOY7_ZngIJWt", | |
| "colab_type": "code", | |
| "colab": { | |
| "base_uri": "https://localhost:8080/", | |
| "height": 309 | |
| }, | |
| "outputId": "067904c5-2cd1-4ee9-b1af-c3759937e5b1" | |
| }, | |
| "source": [ | |
| "new_col = ['shares_outstanding', 'earning_per_share', 'dividend_rate', \n", | |
| " 'dividend_yield', 'debt_to_equity', 'book_value_per_share',\n", | |
| " 'return_on_equity', 'current_ratio']\n", | |
| "aapl_df.columns = new_col\n", | |
| "aapl_df.head()" | |
| ], | |
| "execution_count": 41, | |
| "outputs": [ | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>shares_outstanding</th>\n", | |
| " <th>earning_per_share</th>\n", | |
| " <th>dividend_rate</th>\n", | |
| " <th>dividend_yield</th>\n", | |
| " <th>debt_to_equity</th>\n", | |
| " <th>book_value_per_share</th>\n", | |
| " <th>return_on_equity</th>\n", | |
| " <th>current_ratio</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>2010-09</th>\n", | |
| " <td>6,473</td>\n", | |
| " <td>2.16</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>7.45</td>\n", | |
| " <td>35.28</td>\n", | |
| " <td>2.01</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2011-09</th>\n", | |
| " <td>6,557</td>\n", | |
| " <td>3.95</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>11.78</td>\n", | |
| " <td>41.67</td>\n", | |
| " <td>1.61</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2012-09</th>\n", | |
| " <td>6,617</td>\n", | |
| " <td>6.31</td>\n", | |
| " <td>0.38</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>NaN</td>\n", | |
| " <td>16.99</td>\n", | |
| " <td>42.84</td>\n", | |
| " <td>1.50</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2013-09</th>\n", | |
| " <td>6,522</td>\n", | |
| " <td>5.68</td>\n", | |
| " <td>1.63</td>\n", | |
| " <td>27.4</td>\n", | |
| " <td>0.14</td>\n", | |
| " <td>19.60</td>\n", | |
| " <td>30.64</td>\n", | |
| " <td>1.68</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2014-09</th>\n", | |
| " <td>6,123</td>\n", | |
| " <td>6.45</td>\n", | |
| " <td>1.81</td>\n", | |
| " <td>28.5</td>\n", | |
| " <td>0.26</td>\n", | |
| " <td>20.62</td>\n", | |
| " <td>33.61</td>\n", | |
| " <td>1.08</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " shares_outstanding earning_per_share ... return_on_equity current_ratio\n", | |
| "2010-09 6,473 2.16 ... 35.28 2.01\n", | |
| "2011-09 6,557 3.95 ... 41.67 1.61\n", | |
| "2012-09 6,617 6.31 ... 42.84 1.50\n", | |
| "2013-09 6,522 5.68 ... 30.64 1.68\n", | |
| "2014-09 6,123 6.45 ... 33.61 1.08\n", | |
| "\n", | |
| "[5 rows x 8 columns]" | |
| ] | |
| }, | |
| "metadata": { | |
| "tags": [] | |
| }, | |
| "execution_count": 41 | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "XsMSAdIdIJWv", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "Now the dataframe looks great! Attention, do not get the order of new column (`new_col`) names wrong. It should be in exactly the same order as current column." | |
| ] | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment