Financials.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import liberaries and functions\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load financials from WRDS\n",
    "financials = pd.read_csv(\n",
    "    filepath_or_buffer=\"Data\\Financials.csv\",\n",
    "    decimal=\".\", \n",
    "    thousands=',',\n",
    "    parse_dates=[\"datadate\"]\n",
    ").drop_duplicates()\n",
    "\n",
    "financials.sort_values(['cik', 'datadate'], inplace=True)\n",
    "\n",
    "financials.fillna({\"seq\": financials[\"teq\"]}, inplace=True)\n",
    "financials.dropna(subset=[\"cik\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load financials from EIKON\n",
    "financials2 = pd.read_csv(\"Data/Financials2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "financials2.dropna(inplace=True)\n",
    "\n",
    "financials2.columns = [\n",
    "    'cik', 'datadate', 'TOTALASSETS', 'TOTALDEBT', 'NETINCOME', 'TOTALREVENUE', 'TOTALEQUITY',\n",
    "    'INTANGIBLESNET', 'RnD', 'TOTALOPERATINGEXPENSE', 'TOTALCURRENTASSETS', 'TOTALCURRLIABILITIES'\n",
    "    ]\n",
    "\n",
    "financials2['datadate'] = pd.to_datetime(financials2['datadate'], errors='coerce').dt.tz_localize(None)\n",
    "\n",
    "financials2.sort_values(by=['cik', 'datadate'], inplace=True)\n",
    "\n",
    "# scale all values to 10^6 similar to WRDS\n",
    "financials2[financials2.columns[2:]] = financials2[financials2.columns[2:]]/1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "financials['ryear'] = financials['datadate'].dt.year\n",
    "financials['rmonth'] = financials['datadate'].dt.month\n",
    "\n",
    "financials2['ryear'] = financials2['datadate'].dt.year\n",
    "financials2['rmonth'] = financials2['datadate'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_fin = pd.merge(\n",
    "    left=financials, \n",
    "    right=financials2, \n",
    "    on=['cik', 'ryear', 'rmonth'], \n",
    "    how='outer'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_fin.fillna({\n",
    "    'datadate_x': merged_fin['datadate_y'], \n",
    "    'at': merged_fin['TOTALASSETS'], \n",
    "    'dt': merged_fin['TOTALDEBT'],\n",
    "    'ni': merged_fin['NETINCOME'], \n",
    "    'revt': merged_fin['TOTALREVENUE'], \n",
    "    'seq': merged_fin['TOTALEQUITY'], \n",
    "    'intan': merged_fin['INTANGIBLESNET'], \n",
    "    'xrd': merged_fin['RnD'], \n",
    "    'xopr': merged_fin['TOTALOPERATINGEXPENSE'], \n",
    "    'act': merged_fin['TOTALCURRENTASSETS'], \n",
    "    'lct': merged_fin['TOTALCURRLIABILITIES']\n",
    "}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_fin.sort_values(by=['cik', 'ryear', 'rmonth'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['act', 'at', 'dt', 'ebit', 'ebitda', 'intan', 'lct', 'lt', 'ni', 'revt', 'seq', \n",
    "        'teq', 'xopr', 'xrd', 'xt', 'naicsh', 'sich', 'mkvalt', 'naics', 'sic']\n",
    "\n",
    "merged_fin[cols] = merged_fin.groupby('cik')[cols].ffill(limit=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_fin.dropna(\n",
    "    subset=['act', 'at', 'dt', 'lct', 'ni', 'revt', 'seq'], \n",
    "    how='all', \n",
    "    inplace=True\n",
    ")\n",
    "\n",
    "merged_fin.rename(columns={'datadate_x': 'datadate'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_fin[[\n",
    "    'cik', 'datadate', 'act', 'at', 'dt', 'ebit', 'ebitda', 'intan', 'lct', 'lt',\n",
    "    'ni', 'revt', 'seq', 'teq', 'xopr', 'xrd', 'xt', 'naicsh', 'sich', 'mkvalt', 'naics', 'sic'\n",
    "]].to_csv(\"Data/Financials3.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ETM_env",
   "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.14"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}