av_insert_income_statements.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "27c6e2db-b617-4085-bb5d-d19a3fce68d0",
   "metadata": {},
   "source": [
    "## Import packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bd3253cc-f4e5-475f-8052-f6e4c855af2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# python mongo libraries\n",
    "import pymongo\n",
    "\n",
    "# Reading properties\n",
    "from jproperties import Properties\n",
    "\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f48aca3-478c-44e7-9766-46d0691c7ea4",
   "metadata": {},
   "source": [
    "## Constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ef3c1019-9a5e-4b3c-a2ce-4564f8fc357f",
   "metadata": {},
   "outputs": [],
   "source": [
    "FUNCTION = 'INCOME_STATEMENT'\n",
    "BASE_URL = 'https://www.alphavantage.co/query?'\n",
    "\n",
    "# List of fields we need to convert from string to integer\n",
    "FIELDS_TO_INT = [\n",
    "    'grossProfit', 'totalRevenue', 'costOfRevenue', 'costofGoodsAndServicesSold',\n",
    "    'operatingIncome', 'sellingGeneralAndAdministrative', 'researchAndDevelopment',\n",
    "    'operatingExpenses', 'investmentIncomeNet', 'netInterestIncome', 'interestIncome',\n",
    "    'interestExpense', 'nonInterestIncome', 'otherNonOperatingIncome','depreciation',\n",
    "    'depreciationAndAmortization', 'incomeBeforeTax', 'incomeTaxExpense', 'interestAndDebtExpense',\n",
    "    'netIncomeFromContinuingOperations', 'comprehensiveIncomeNetOfTax', 'ebit', 'ebitda', 'netIncome'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78f98b0a-d45f-461a-94c3-c0389dec51dd",
   "metadata": {},
   "source": [
    "## Load properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0c9e7e69-3a4c-4333-8d6a-a590c4b1f025",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initial;ize from property file\n",
    "configs = Properties()\n",
    "\n",
    "with open('config/insert_income_statements.properties', 'rb') as config_file:\n",
    "     configs.load(config_file)\n",
    "\n",
    "TICKERS = configs.get('TICKERS').data.split(',')\n",
    "API_KEY = configs.get('ALPHAV_API_KEY').data\n",
    "MONGO_URI = configs.get('MONGO_URI').data\n",
    "DB = configs.get('DB').data\n",
    "ANNUAL_COLLECTION = configs.get('ANNUAL_COLLECTION').data\n",
    "QUARTERLY_COLLECTION = configs.get('QUARTERLY_COLLECTION').data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6e73b06-8fea-409f-86e3-c60e8798eabf",
   "metadata": {},
   "source": [
    "## Get income statements from Alpha Vantage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e8eca1f6-7a1a-4360-9577-ba94d364ef7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Holds income statements data for each symbol\n",
    "statements = {}\n",
    "\n",
    "# Get earnings for each stock ticker\n",
    "for ticker in TICKERS:\n",
    "    response = requests.get(f'{BASE_URL}function={FUNCTION}&symbol={ticker}&apikey={API_KEY}')\n",
    "    statements[ticker] = response.json()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4379d841-c20a-4aee-82da-faff7cd62d72",
   "metadata": {},
   "source": [
    "## Utility method to create a DF\n",
    "#### __Note:__ it uses the global variable _statements_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "631c96ca-bd42-4963-a8f4-17eeb01da2e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_ticker_df(ticker, report_type):\n",
    "    if report_type.upper() == 'Q':\n",
    "        df_ticker = pd.DataFrame(statements[ticker]['quarterlyReports'])\n",
    "    elif report_type.upper() == 'A':\n",
    "        df_ticker = pd.DataFrame(statements[ticker]['annualReports'])\n",
    "    else:\n",
    "        raise Exception('Unknown report type, valid types are Q or A')\n",
    "        \n",
    "    # Add a column for the ticker\n",
    "    df_ticker['ticker'] = ticker\n",
    "    \n",
    "    for field in FIELDS_TO_INT:\n",
    "        # non numeric are converted to NaN\n",
    "        df_ticker[field] = pd.to_numeric(df_ticker[field], errors='coerce')\n",
    "\n",
    "    # Convert to dates which are in strings in raw format\n",
    "    df_ticker['fiscalDateEnding'] = pd.to_datetime(df_ticker['fiscalDateEnding'])\n",
    "\n",
    "    # Sort by dates - we want the oldest date first (otherway around in the REST response).\n",
    "    # Important for percentage change\n",
    "    df_ticker = df_ticker.sort_values('fiscalDateEnding')\n",
    "    \n",
    "    # Store as thousands\n",
    "    df_ticker[FIELDS_TO_INT] = df_ticker[FIELDS_TO_INT].apply(lambda x: x//1000)\n",
    "\n",
    "    # Add the % change against each integer column\n",
    "    for field in FIELDS_TO_INT:\n",
    "        # Using diff approach as pct_change doesn't handle negative values correctly - https://github.com/pandas-dev/pandas/issues/40911    \n",
    "        df_ticker[field + '_pctChange'] = df_ticker[field].diff() / df_ticker[field].abs().shift()\n",
    "\n",
    "    return df_ticker"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35c7b5d5-5b35-4a9b-8cf2-bb56f5789a1f",
   "metadata": {},
   "source": [
    "## Create DataFrame containing Annual and Quarterly income statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e5614a91-a162-44ce-bbfd-b37415f44b6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DFs to collect all the annual and quarterly income statements for tickers\n",
    "df_annual = pd.DataFrame()\n",
    "df_quarterly = pd.DataFrame()\n",
    "\n",
    "# Loop through statements dictionary which holds earnings for each ticker symbol        \n",
    "for ticker in statements:\n",
    "    df_annual = pd.concat([df_annual, create_ticker_df(ticker, 'A')])\n",
    "    df_quarterly = pd.concat([df_quarterly, create_ticker_df(ticker, 'Q')])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c9d19bf-aa35-47af-b06d-c617ccc44cb3",
   "metadata": {},
   "source": [
    "## Reset the index to ticker + fiscal date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "44bf85ae-58cb-4cb6-ab55-cdc801338db7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>fiscalDateEnding</th>\n",
       "      <th>reportedCurrency</th>\n",
       "      <th>grossProfit</th>\n",
       "      <th>totalRevenue</th>\n",
       "      <th>costOfRevenue</th>\n",
       "      <th>costofGoodsAndServicesSold</th>\n",
       "      <th>operatingIncome</th>\n",
       "      <th>sellingGeneralAndAdministrative</th>\n",
       "      <th>researchAndDevelopment</th>\n",
       "      <th>operatingExpenses</th>\n",
       "      <th>...</th>\n",
       "      <th>depreciation_pctChange</th>\n",
       "      <th>depreciationAndAmortization_pctChange</th>\n",
       "      <th>incomeBeforeTax_pctChange</th>\n",
       "      <th>incomeTaxExpense_pctChange</th>\n",
       "      <th>interestAndDebtExpense_pctChange</th>\n",
       "      <th>netIncomeFromContinuingOperations_pctChange</th>\n",
       "      <th>comprehensiveIncomeNetOfTax_pctChange</th>\n",
       "      <th>ebit_pctChange</th>\n",
       "      <th>ebitda_pctChange</th>\n",
       "      <th>netIncome_pctChange</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiscalDateEnding</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <th>INTC</th>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>12360000</td>\n",
       "      <td>18829000</td>\n",
       "      <td>8386000</td>\n",
       "      <td>6803000</td>\n",
       "      <td>7349000</td>\n",
       "      <td>1605000</td>\n",
       "      <td>3428000</td>\n",
       "      <td>5011000</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <th>INTC</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>USD</td>\n",
       "      <td>11227000</td>\n",
       "      <td>18495000</td>\n",
       "      <td>9000000</td>\n",
       "      <td>7430000</td>\n",
       "      <td>6224000</td>\n",
       "      <td>1720000</td>\n",
       "      <td>3433000</td>\n",
       "      <td>5003000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.114650</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.211005</td>\n",
       "      <td>-0.408602</td>\n",
       "      <td>0.651376</td>\n",
       "      <td>-0.188028</td>\n",
       "      <td>-0.166040</td>\n",
       "      <td>-0.204799</td>\n",
       "      <td>-0.203397</td>\n",
       "      <td>-0.188028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-03-31</th>\n",
       "      <th>INTC</th>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>USD</td>\n",
       "      <td>9089000</td>\n",
       "      <td>15733000</td>\n",
       "      <td>8555000</td>\n",
       "      <td>6972000</td>\n",
       "      <td>4174000</td>\n",
       "      <td>1583000</td>\n",
       "      <td>3332000</td>\n",
       "      <td>4915000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.061429</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.193079</td>\n",
       "      <td>0.302273</td>\n",
       "      <td>-0.233333</td>\n",
       "      <td>-0.235034</td>\n",
       "      <td>-0.223328</td>\n",
       "      <td>-0.187478</td>\n",
       "      <td>-0.185867</td>\n",
       "      <td>-0.235034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-30</th>\n",
       "      <th>INTC</th>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>9878000</td>\n",
       "      <td>16337000</td>\n",
       "      <td>8450000</td>\n",
       "      <td>6627000</td>\n",
       "      <td>4617000</td>\n",
       "      <td>1639000</td>\n",
       "      <td>3438000</td>\n",
       "      <td>5261000</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.035442</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.038927</td>\n",
       "      <td>-0.048866</td>\n",
       "      <td>-0.021739</td>\n",
       "      <td>0.051585</td>\n",
       "      <td>0.056832</td>\n",
       "      <td>0.037140</td>\n",
       "      <td>0.036748</td>\n",
       "      <td>0.051585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-30</th>\n",
       "      <th>INTC</th>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>USD</td>\n",
       "      <td>11295000</td>\n",
       "      <td>19029000</td>\n",
       "      <td>9535000</td>\n",
       "      <td>7895000</td>\n",
       "      <td>6447000</td>\n",
       "      <td>1536000</td>\n",
       "      <td>3208000</td>\n",
       "      <td>4848000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.054884</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.422312</td>\n",
       "      <td>0.337615</td>\n",
       "      <td>-0.207407</td>\n",
       "      <td>0.433357</td>\n",
       "      <td>0.347826</td>\n",
       "      <td>0.404816</td>\n",
       "      <td>0.400693</td>\n",
       "      <td>0.433357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        fiscalDateEnding reportedCurrency  grossProfit  \\\n",
       "fiscalDateEnding ticker                                                  \n",
       "2018-09-30       INTC         2018-09-30              USD     12360000   \n",
       "2018-12-31       INTC         2018-12-31              USD     11227000   \n",
       "2019-03-31       INTC         2019-03-31              USD      9089000   \n",
       "2019-06-30       INTC         2019-06-30              USD      9878000   \n",
       "2019-09-30       INTC         2019-09-30              USD     11295000   \n",
       "\n",
       "                         totalRevenue  costOfRevenue  \\\n",
       "fiscalDateEnding ticker                                \n",
       "2018-09-30       INTC        18829000        8386000   \n",
       "2018-12-31       INTC        18495000        9000000   \n",
       "2019-03-31       INTC        15733000        8555000   \n",
       "2019-06-30       INTC        16337000        8450000   \n",
       "2019-09-30       INTC        19029000        9535000   \n",
       "\n",
       "                         costofGoodsAndServicesSold  operatingIncome  \\\n",
       "fiscalDateEnding ticker                                                \n",
       "2018-09-30       INTC                       6803000          7349000   \n",
       "2018-12-31       INTC                       7430000          6224000   \n",
       "2019-03-31       INTC                       6972000          4174000   \n",
       "2019-06-30       INTC                       6627000          4617000   \n",
       "2019-09-30       INTC                       7895000          6447000   \n",
       "\n",
       "                         sellingGeneralAndAdministrative  \\\n",
       "fiscalDateEnding ticker                                    \n",
       "2018-09-30       INTC                            1605000   \n",
       "2018-12-31       INTC                            1720000   \n",
       "2019-03-31       INTC                            1583000   \n",
       "2019-06-30       INTC                            1639000   \n",
       "2019-09-30       INTC                            1536000   \n",
       "\n",
       "                         researchAndDevelopment  operatingExpenses  ...  \\\n",
       "fiscalDateEnding ticker                                             ...   \n",
       "2018-09-30       INTC                   3428000            5011000  ...   \n",
       "2018-12-31       INTC                   3433000            5003000  ...   \n",
       "2019-03-31       INTC                   3332000            4915000  ...   \n",
       "2019-06-30       INTC                   3438000            5261000  ...   \n",
       "2019-09-30       INTC                   3208000            4848000  ...   \n",
       "\n",
       "                         depreciation_pctChange  \\\n",
       "fiscalDateEnding ticker                           \n",
       "2018-09-30       INTC                       NaN   \n",
       "2018-12-31       INTC                  0.114650   \n",
       "2019-03-31       INTC                  0.061429   \n",
       "2019-06-30       INTC                 -0.035442   \n",
       "2019-09-30       INTC                  0.054884   \n",
       "\n",
       "                         depreciationAndAmortization_pctChange  \\\n",
       "fiscalDateEnding ticker                                          \n",
       "2018-09-30       INTC                                      NaN   \n",
       "2018-12-31       INTC                                      0.0   \n",
       "2019-03-31       INTC                                      0.0   \n",
       "2019-06-30       INTC                                      0.0   \n",
       "2019-09-30       INTC                                      0.0   \n",
       "\n",
       "                         incomeBeforeTax_pctChange  \\\n",
       "fiscalDateEnding ticker                              \n",
       "2018-09-30       INTC                          NaN   \n",
       "2018-12-31       INTC                    -0.211005   \n",
       "2019-03-31       INTC                    -0.193079   \n",
       "2019-06-30       INTC                     0.038927   \n",
       "2019-09-30       INTC                     0.422312   \n",
       "\n",
       "                         incomeTaxExpense_pctChange  \\\n",
       "fiscalDateEnding ticker                               \n",
       "2018-09-30       INTC                           NaN   \n",
       "2018-12-31       INTC                     -0.408602   \n",
       "2019-03-31       INTC                      0.302273   \n",
       "2019-06-30       INTC                     -0.048866   \n",
       "2019-09-30       INTC                      0.337615   \n",
       "\n",
       "                         interestAndDebtExpense_pctChange  \\\n",
       "fiscalDateEnding ticker                                     \n",
       "2018-09-30       INTC                                 NaN   \n",
       "2018-12-31       INTC                            0.651376   \n",
       "2019-03-31       INTC                           -0.233333   \n",
       "2019-06-30       INTC                           -0.021739   \n",
       "2019-09-30       INTC                           -0.207407   \n",
       "\n",
       "                         netIncomeFromContinuingOperations_pctChange  \\\n",
       "fiscalDateEnding ticker                                                \n",
       "2018-09-30       INTC                                            NaN   \n",
       "2018-12-31       INTC                                      -0.188028   \n",
       "2019-03-31       INTC                                      -0.235034   \n",
       "2019-06-30       INTC                                       0.051585   \n",
       "2019-09-30       INTC                                       0.433357   \n",
       "\n",
       "                         comprehensiveIncomeNetOfTax_pctChange  \\\n",
       "fiscalDateEnding ticker                                          \n",
       "2018-09-30       INTC                                      NaN   \n",
       "2018-12-31       INTC                                -0.166040   \n",
       "2019-03-31       INTC                                -0.223328   \n",
       "2019-06-30       INTC                                 0.056832   \n",
       "2019-09-30       INTC                                 0.347826   \n",
       "\n",
       "                         ebit_pctChange  ebitda_pctChange  netIncome_pctChange  \n",
       "fiscalDateEnding ticker                                                         \n",
       "2018-09-30       INTC               NaN               NaN                  NaN  \n",
       "2018-12-31       INTC         -0.204799         -0.203397            -0.188028  \n",
       "2019-03-31       INTC         -0.187478         -0.185867            -0.235034  \n",
       "2019-06-30       INTC          0.037140          0.036748             0.051585  \n",
       "2019-09-30       INTC          0.404816          0.400693             0.433357  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# When setting the index, fiscal date ending and ticker are retained as columns; we need them in the collection\n",
    "df_annual.set_index(['fiscalDateEnding', 'ticker'], drop=False, inplace=True)\n",
    "df_quarterly.set_index(['fiscalDateEnding', 'ticker'], drop=False, inplace=True)\n",
    "df_quarterly.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bad29d6-c65d-4ae8-a500-6969b853bddf",
   "metadata": {},
   "source": [
    "## Insert Annual and Quarterly Income Statements to MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2d46b197-cd58-4f57-872a-f9e371b16422",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initialize mongo client\n",
    "client = pymongo.MongoClient(MONGO_URI)\n",
    "\n",
    "with client:\n",
    "    # The database\n",
    "    db = client[DB]\n",
    "    db[ANNUAL_COLLECTION].insert_many(df_annual.to_dict('records'))\n",
    "    db[QUARTERLY_COLLECTION].insert_many(df_quarterly.to_dict('records'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af154d70-6606-43a6-a19a-94a6c572b121",
   "metadata": {},
   "source": [
    "## Sanity check #1\n",
    "### The first record pctChange should be NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b26f8c4c-4dff-47f1-91ec-7bd1bd3e76f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>netIncome_pctChange</th>\n",
       "      <th>netIncome</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiscalDateEnding</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <th>INTC</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6398000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <th>INTC</th>\n",
       "      <td>-0.188028</td>\n",
       "      <td>5195000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-03-31</th>\n",
       "      <th>INTC</th>\n",
       "      <td>-0.235034</td>\n",
       "      <td>3974000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-30</th>\n",
       "      <th>INTC</th>\n",
       "      <td>0.051585</td>\n",
       "      <td>4179000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-30</th>\n",
       "      <th>INTC</th>\n",
       "      <td>0.433357</td>\n",
       "      <td>5990000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         netIncome_pctChange  netIncome\n",
       "fiscalDateEnding ticker                                \n",
       "2018-09-30       INTC                    NaN    6398000\n",
       "2018-12-31       INTC              -0.188028    5195000\n",
       "2019-03-31       INTC              -0.235034    3974000\n",
       "2019-06-30       INTC               0.051585    4179000\n",
       "2019-09-30       INTC               0.433357    5990000"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_quarterly[['netIncome_pctChange', 'netIncome']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b60317ad-c604-46b4-b192-7f1f13853cd8",
   "metadata": {},
   "source": [
    "## Sanity check #2\n",
    "### with other another Ticker, the first record for the ticker should have NaN for the pctChage column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "004ac795-b0b6-4f12-ab98-9157af3fa24e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>netIncome_pctChange</th>\n",
       "      <th>netIncome</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiscalDateEnding</th>\n",
       "      <th>ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <th>AMD</th>\n",
       "      <td>NaN</td>\n",
       "      <td>116000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <th>AMD</th>\n",
       "      <td>-0.120690</td>\n",
       "      <td>102000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <th>AMD</th>\n",
       "      <td>-0.627451</td>\n",
       "      <td>38000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-03-31</th>\n",
       "      <th>AMD</th>\n",
       "      <td>-0.578947</td>\n",
       "      <td>16000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-30</th>\n",
       "      <th>AMD</th>\n",
       "      <td>1.187500</td>\n",
       "      <td>35000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         netIncome_pctChange  netIncome\n",
       "fiscalDateEnding ticker                                \n",
       "2018-06-30       AMD                     NaN     116000\n",
       "2018-09-30       AMD               -0.120690     102000\n",
       "2018-12-31       AMD               -0.627451      38000\n",
       "2019-03-31       AMD               -0.578947      16000\n",
       "2019-06-30       AMD                1.187500      35000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_quarterly[df_quarterly['ticker'] == 'AMD'][['netIncome_pctChange', 'netIncome']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a1b830a-92c3-4f54-919a-7a1467bb5e49",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}