sp500_script.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import gspread\n",
    "from datetime import datetime\n",
    "from gspread_dataframe import set_with_dataframe\n",
    "from functools import reduce\n",
    "import yfinance as yf\n",
    "pd.set_option('display.max_row', 1500)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extracting actual companies in S&P 500 Index + calculating lenght of companies in the index\n",
    "table=pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')\n",
    "sp500 = table[0]\n",
    "sp500 = sp500[\"Symbol\"].tolist()\n",
    "sp500_len = len(sp500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_stocks=[]\n",
    "for i in range(0,sp500_len):\n",
    "    stock = yf.Ticker(sp500[i])\n",
    "    stock_i=stock.info\n",
    "    stock_i=pd.DataFrame.from_dict(stock_i.items())\n",
    "    stock_i.columns =['Index', 'Value'] \n",
    "    stock_i.rename(columns = {'Value':sp500[i]}, inplace = True)\n",
    "    list_stocks.append(stock_i)\n",
    "\n",
    "final_df = reduce(lambda  left,right: pd.merge(left,right,on=['Index'], how='outer'), list_stocks)\n",
    "final_df = final_df.reset_index(drop=True).T\n",
    "final_df.columns=final_df.iloc[0]\n",
    "final_df=final_df.iloc[1:, :]\n",
    "new_df = final_df[['sector']]\n",
    "new_df = new_df.rename(columns={'sector': 'Sector'})\n",
    "new_df = new_df.reset_index()\n",
    "new_df = new_df.rename(columns={'index': 'Name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "table=pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')\n",
    "sp500 = table[0]\n",
    "sp500 = sp500[\"Symbol\"].tolist()\n",
    "sp500_len = len(sp500)\n",
    "value_stock = yf.Ticker(sp500[0])\n",
    "sp500_len = len(sp500)\n",
    "income_statement = value_stock.income_stmt\n",
    "income_statement = income_statement.reset_index().rename(columns={'index': 'Metrics'})\n",
    "income_statement = pd.melt(income_statement, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "income_statement['Stock'] = value_stock.info['symbol']\n",
    "income_statement['Value'] = income_statement['Value'] / 1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_statement_cobined = pd.DataFrame()\n",
    "\n",
    "# Iterate through each company in the S&P 500\n",
    "for symbol in sp500:\n",
    "    value_stock = yf.Ticker(symbol)\n",
    "    income_statement = value_stock.income_stmt\n",
    "    income_statement = income_statement.reset_index().rename(columns={'index': 'Metrics'})\n",
    "    income_statement = pd.melt(income_statement, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "    income_statement['Name'] = value_stock.info['symbol']\n",
    "    income_statement['Value'] = income_statement['Value'] / 1000000\n",
    "        \n",
    "    # Concatenate data for the current company to the combined DataFrame\n",
    "    income_statement_cobined = pd.concat([income_statement_cobined, income_statement], ignore_index=True)\n",
    "    \n",
    "income_statement_cobined['Year'] = income_statement_cobined['Date'].dt.year\n",
    "income_statement_cobined = income_statement_cobined.drop(columns=['Date'])\n",
    "\n",
    "income_statement_cobined = pd.merge(new_df, income_statement_cobined, on='Name', how='left')\n",
    "income_statement_cobined['Report'] = 'Income_Statement'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "balance_sheet_cobined = pd.DataFrame()\n",
    "\n",
    "# Iterate through each company in the S&P 500\n",
    "for symbol in sp500:\n",
    "    value_stock = yf.Ticker(symbol)\n",
    "    balance_sheet = value_stock.balance_sheet\n",
    "    balance_sheet = balance_sheet.reset_index().rename(columns={'index': 'Metrics'})\n",
    "    balance_sheet = pd.melt(balance_sheet, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "    balance_sheet['Name'] = value_stock.info['symbol']\n",
    "    balance_sheet['Value'] = balance_sheet['Value'] / 1000000\n",
    "        \n",
    "    # Concatenate data for the current company to the combined DataFrame\n",
    "    balance_sheet_cobined = pd.concat([balance_sheet_cobined, balance_sheet], ignore_index=True)\n",
    "\n",
    "balance_sheet_cobined['Year'] = balance_sheet_cobined['Date'].dt.year\n",
    "balance_sheet_cobined = balance_sheet_cobined.drop(columns=['Date'])\n",
    "\n",
    "balance_sheet_cobined = pd.merge(new_df, balance_sheet_cobined, on='Name', how='left')\n",
    "balance_sheet_cobined['Report'] = 'Balance_Sheet'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cashflow_cobined = pd.DataFrame()\n",
    "\n",
    "# Iterate through each company in the S&P 500\n",
    "for symbol in sp500:\n",
    "    value_stock = yf.Ticker(symbol)\n",
    "    cashflow = value_stock.cashflow\n",
    "    cashflow = cashflow.reset_index().rename(columns={'index': 'Metrics'})\n",
    "    cashflow = pd.melt(cashflow, id_vars=['Metrics'], var_name='Date', value_name='Value')\n",
    "    cashflow['Name'] = value_stock.info['symbol']\n",
    "    cashflow['Value'] = cashflow['Value'] / 1000000\n",
    "        \n",
    "    # Concatenate data for the current company to the combined DataFrame\n",
    "    cashflow_cobined = pd.concat([cashflow_cobined, cashflow], ignore_index=True)\n",
    "\n",
    "cashflow_cobined['Year'] = cashflow_cobined['Date'].dt.year\n",
    "cashflow_cobined = cashflow_cobined.drop(columns=['Date'])\n",
    "\n",
    "cashflow_cobined = pd.merge(new_df, cashflow_cobined, on='Name', how='left')\n",
    "cashflow_cobined['Report'] = 'Cash_Flow'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "tota_data = pd.DataFrame()\n",
    "tota_data = pd.concat([balance_sheet_cobined, cashflow_cobined, income_statement_cobined], ignore_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "GSHEET_NAME = 'S&P 500 Reports'\n",
    "TAB_NAME = 'Data_SP500' \n",
    "\n",
    "def write_df_to_sheet(GSHEET_NAME, TAB_NAME, df):\n",
    "    gc= gspread.service_account(filename ='sp500-reporting-848fbf6683ca.json')\n",
    "    sh=gc.open(GSHEET_NAME)\n",
    "    worksheet = sh.worksheet(TAB_NAME)\n",
    "    set_with_dataframe(worksheet, df)\n",
    "\n",
    "write_df_to_sheet(GSHEET_NAME, TAB_NAME, tota_data) "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}