data_scraping/B3_trade_by_trade/B3_trade_by_trade.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8536624f",
   "metadata": {},
   "source": [
    "# Getting closed trade by trade data from the brazilian stock exchange\n",
    "\n",
    "**What ?** Brazilian stock exchange B3 publishes daily a report on its website with all the closed trades that occurred on the previous market day. This dataset includes information on every negotiated asset, traded quantity, prices, and both sides of the brokers involved at the specific second when each trade was closed.\n",
    "\n",
    "**Why ?** This dataset is higly useful for displaying price performance over an intraday series. It enables the recognition of patterns, analysis, and provides interesting insights into the behavior of each asset tradings.\n",
    "\n",
    "**How ?** The last 20 days of market data trading are available in a .zip file on the B3 website\n",
    "[(see this link)](https://www.b3.com.br/pt_br/market-data-e-indices/servicos-de-dados/market-data/cotacoes/cotacoes/). From there, it will be manually downloaded to a local temporary folder. This notebook demonstrates how to read the .csv database within each .zip file downloaded and apply some data cleaning and transformation according to this guide [(link here)](https://www.b3.com.br/data/files/14/42/28/31/FEC4A8103234E0A8AC094EA8/Glossario_NegociosListados_PT.pdf). At the end, the data is uploaded to a local SQLite database where it can be used for further analysis.\n",
    "\n",
    "<img src=\"https://lh3.googleusercontent.com/d/1e-hu9egDMB2j2ZoRQLKXd0qd0GTLuXmL\" alt=\"texto_alternativo\" width=\"400\" align=\"center\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "385c243b",
   "metadata": {},
   "source": [
    "## Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ac5a5add",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "import sqlite3\n",
    "import requests\n",
    "import zipfile"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a4d557a",
   "metadata": {},
   "source": [
    "#### Search at local SQLite database what is the last available data uploaded "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ad6efe12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19    2024-05-21\n",
       "18    2024-05-20\n",
       "17    2024-05-17\n",
       "Name: DataReferencia, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect(os.getenv('MY_FINANCE_DB_PATH')+'/finance_database.db')\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('''SELECT DataReferencia\n",
    "                    FROM B3_trade_by_trade \n",
    "                    GROUP BY DataReferencia''') # this table was previously created to hold the trade by trade data\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "columns = [description[0] for description in cursor.description]\n",
    "\n",
    "df_dt = pd.DataFrame(rows, columns=columns)\n",
    "conn.close()\n",
    "\n",
    "df_dt['DataReferencia'].sort_values(ascending = False).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9461eb8",
   "metadata": {},
   "source": [
    "####  Looking for new files manually downloaded from B3 website into a local folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5e434b88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['temp_files\\\\22-05-2024_NEGOCIOSAVISTA.zip',\n",
       " 'temp_files\\\\23-05-2024_NEGOCIOSAVISTA.zip',\n",
       " 'temp_files\\\\24-05-2024_NEGOCIOSAVISTA.zip']"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_path = os.path.join('temp_files') # Define the file path within the subfolder\n",
    "all_files = os.listdir(file_path)\n",
    "zip_files_with_paths = [os.path.join(file_path, file) for file in all_files if file.endswith('.zip')]\n",
    "\n",
    "zip_files_with_paths"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "608cb300",
   "metadata": {},
   "source": [
    "#### Extract, Transform and Load dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d5e6b607",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22-05-2024_NEGOCIOSAVISTA.txt\n",
      "23-05-2024_NEGOCIOSAVISTA.txt\n",
      "24-05-2024_NEGOCIOSAVISTA.txt\n"
     ]
    }
   ],
   "source": [
    "# Reading each .csv trade by trade files from .zip downlodaded from B3\n",
    "####################################################################################################\n",
    "df_app = pd.DataFrame()\n",
    "csv_file_name_list = []\n",
    "\n",
    "for zip_file_name in zip_files_with_paths: #zip_files_with_paths[::-1][6:8]\n",
    "\n",
    "    with zipfile.ZipFile(zip_file_name, 'r') as zip_file:\n",
    "        \n",
    "        csv_file_name = zip_file.namelist()[0]\n",
    "        \n",
    "        df_app = pd.read_csv(zip_file.open(csv_file_name),sep = \";\", encoding = \"UTF-8\",low_memory=False, dtype=str)\n",
    "\n",
    "# Data cleaning: changing data types\n",
    "####################################################################################################\n",
    "    def interpret_timestamp(timestamp_str): # transform the integer number representing hour of tradings in a HH:MM:SS format\n",
    "        hours = timestamp_str[:2]\n",
    "        minutes = timestamp_str[2:4]\n",
    "        seconds = timestamp_str[4:6]\n",
    "        timestamp = f\"{hours}:{minutes}:{seconds}\"\n",
    "        return timestamp\n",
    "\n",
    "    # Apply the interpret_timestamp function to all rows in the 'timestamp_str' column\n",
    "    df_app['ClosedHour'] = df_app['HoraFechamento'].apply(lambda x: interpret_timestamp(x))\n",
    "\n",
    "    # Convert combined strings to datetime objects\n",
    "    combined_datetime = df_app['DataNegocio'] + ' ' + df_app['ClosedHour']\n",
    "    df_app['ClosedDateTime'] = pd.to_datetime(combined_datetime)\n",
    "\n",
    "    # ajusting datatype of prices and quantity of each trade\n",
    "    df_app['PrecoNegocio'] = df_app['PrecoNegocio'].str.replace(',', '.').astype(float)\n",
    "    df_app['QuantidadeNegociada'] = df_app['QuantidadeNegociada'].str.replace(',', '.').astype(float)\n",
    "\n",
    "\n",
    "# write the dataframe into the SQLite database\n",
    "####################################################################################################\n",
    "    conn = sqlite3.connect(os.getenv('MY_FINANCE_DB_PATH')+'/finance_database.db')\n",
    "\n",
    "    df_app.to_sql('B3_trade_by_trade',conn,if_exists='append',index=False)\n",
    "    \n",
    "    # printing file read\n",
    "    print(csv_file_name)\n",
    "    print(df_app.head(1))\n",
    "    del df_app \n",
    "    df_app = pd.DataFrame()\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70c74d8f",
   "metadata": {},
   "source": [
    "#### Reading a sample from the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "67b5ba5b",
   "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>DataReferencia</th>\n",
       "      <th>CodigoInstrumento</th>\n",
       "      <th>AcaoAtualizacao</th>\n",
       "      <th>PrecoNegocio</th>\n",
       "      <th>QuantidadeNegociada</th>\n",
       "      <th>HoraFechamento</th>\n",
       "      <th>CodigoIdentificadorNegocio</th>\n",
       "      <th>TipoSessaoPregao</th>\n",
       "      <th>DataNegocio</th>\n",
       "      <th>CodigoParticipanteComprador</th>\n",
       "      <th>CodigoParticipanteVendedor</th>\n",
       "      <th>ClosedHour</th>\n",
       "      <th>ClosedDateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>PETR4</td>\n",
       "      <td>0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100353907</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>238</td>\n",
       "      <td>8</td>\n",
       "      <td>10:03:53</td>\n",
       "      <td>2024-04-30 10:03:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>PETR4</td>\n",
       "      <td>0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>6300.0</td>\n",
       "      <td>100353907</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>77</td>\n",
       "      <td>8</td>\n",
       "      <td>10:03:53</td>\n",
       "      <td>2024-04-30 10:03:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>PETR4</td>\n",
       "      <td>0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100353907</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>4090</td>\n",
       "      <td>8</td>\n",
       "      <td>10:03:53</td>\n",
       "      <td>2024-04-30 10:03:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>PETR4</td>\n",
       "      <td>0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100353907</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>90</td>\n",
       "      <td>8</td>\n",
       "      <td>10:03:53</td>\n",
       "      <td>2024-04-30 10:03:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>PETR4</td>\n",
       "      <td>0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>900.0</td>\n",
       "      <td>100353907</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>10:03:53</td>\n",
       "      <td>2024-04-30 10:03:53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  DataReferencia CodigoInstrumento AcaoAtualizacao  PrecoNegocio  \\\n",
       "0     2024-04-30             PETR4               0          42.0   \n",
       "1     2024-04-30             PETR4               0          42.0   \n",
       "2     2024-04-30             PETR4               0          42.0   \n",
       "3     2024-04-30             PETR4               0          42.0   \n",
       "4     2024-04-30             PETR4               0          42.0   \n",
       "\n",
       "   QuantidadeNegociada HoraFechamento CodigoIdentificadorNegocio  \\\n",
       "0                100.0      100353907                         10   \n",
       "1               6300.0      100353907                         20   \n",
       "2                100.0      100353907                         30   \n",
       "3                100.0      100353907                         40   \n",
       "4                900.0      100353907                         50   \n",
       "\n",
       "  TipoSessaoPregao DataNegocio CodigoParticipanteComprador  \\\n",
       "0                1  2024-04-30                         238   \n",
       "1                1  2024-04-30                          77   \n",
       "2                1  2024-04-30                        4090   \n",
       "3                1  2024-04-30                          90   \n",
       "4                1  2024-04-30                           3   \n",
       "\n",
       "  CodigoParticipanteVendedor ClosedHour       ClosedDateTime  \n",
       "0                          8   10:03:53  2024-04-30 10:03:53  \n",
       "1                          8   10:03:53  2024-04-30 10:03:53  \n",
       "2                          8   10:03:53  2024-04-30 10:03:53  \n",
       "3                          8   10:03:53  2024-04-30 10:03:53  \n",
       "4                          8   10:03:53  2024-04-30 10:03:53  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show a small sample of the data\n",
    "conn = sqlite3.connect(os.getenv('MY_FINANCE_DB_PATH')+'/finance_database.db')\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('''SELECT *\n",
    "            FROM B3_trade_by_trade\n",
    "            WHERE CodigoInstrumento = 'PETR4' and DataReferencia = '2024-04-30' ''') # reading a specificly ticker and date as an exlaple\n",
    "rows = cursor.fetchall()\n",
    "columns = [description[0] for description in cursor.description]\n",
    "\n",
    "df_sample = pd.DataFrame(rows, columns=columns)\n",
    "conn.close()\n",
    "\n",
    "df_sample.head()"
   ]
  }
 ],
 "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
}