data_scraping/CVM_funds_cadastral_info/CVM_funds_cadastral_info.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8536624f",
   "metadata": {},
   "source": [
    "# Getting the brazilians funds cadastral information\n",
    "\n",
    "**What ?** Brazilian funds are regulated by a governmental institution called CVM (Comissão de Valores Mobiliários). Therefore, every Brazilian fund that is regulated needs to register with this entity, providing information such as name, total assets under management, mandate, administrator, manager, address, asset classes, among others. This cadastral information is publicly available on the CVM webpage.\n",
    "\n",
    "\n",
    "**Why ?** With this cadastral information, it is possible to perform aggregate analysis of fund sectors and their characteristics. Additionally, it is possible to consolidate funds by management firm to identify management patterns within a group of funds.\n",
    "\n",
    "**How ?** In this code, we will download the cadastral .csv file from the CVM [webpage](https://dados.cvm.gov.br/dataset/fi-cad) containing information about every Brazilian fund that is regulated. This file is updated from Tuesday to Saturday on the webpage and is already structured as a database. Finally, the downloaded dataframe will be uploaded to a local SQLite database for further analysis.\n",
    "\n",
    "\n",
    "<img src=\"https://lh3.googleusercontent.com/d/1KwJrcsJvAZPLej-SsJAlBTr1E6X5c80K\" alt=\"icon_cadastral_info\" width=\"300\" align=\"center\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "385c243b",
   "metadata": {},
   "source": [
    "### Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ac5a5add",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import csv\n",
    "import os\n",
    "import tempfile\n",
    "import io\n",
    "\n",
    "import requests\n",
    "import zipfile\n",
    "import sqlite3\n",
    "from sqlite3 import Error\n",
    "\n",
    "pd.options.display.float_format = '{:.4f}'.format #formating data displayed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0086af68",
   "metadata": {},
   "source": [
    "### Download cadastral information from CVM website"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5e434b88",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\lucas\\AppData\\Local\\Temp\\ipykernel_16788\\4172064473.py:5: DtypeWarning: Columns (14,17,18,20,22,24,27,37,38,39,40) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_cad_fi = pd.read_csv(io.StringIO(s), sep = \";\", encoding = \"ISO-8859-1\")\n"
     ]
    }
   ],
   "source": [
    "url = 'https://dados.cvm.gov.br/dados/FI/CAD/DADOS/cad_fi.csv'\n",
    "\n",
    "s = requests.get(url).text\n",
    "\n",
    "df_cad_fi = pd.read_csv(io.StringIO(s), sep = \";\", encoding = \"ISO-8859-1\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f13b1e9b",
   "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>TP_FUNDO</th>\n",
       "      <th>CNPJ_FUNDO</th>\n",
       "      <th>DENOM_SOCIAL</th>\n",
       "      <th>DT_REG</th>\n",
       "      <th>DT_CONST</th>\n",
       "      <th>CD_CVM</th>\n",
       "      <th>DT_CANCEL</th>\n",
       "      <th>SIT</th>\n",
       "      <th>DT_INI_SIT</th>\n",
       "      <th>DT_INI_ATIV</th>\n",
       "      <th>...</th>\n",
       "      <th>CPF_CNPJ_GESTOR</th>\n",
       "      <th>GESTOR</th>\n",
       "      <th>CNPJ_AUDITOR</th>\n",
       "      <th>AUDITOR</th>\n",
       "      <th>CNPJ_CUSTODIANTE</th>\n",
       "      <th>CUSTODIANTE</th>\n",
       "      <th>CNPJ_CONTROLADOR</th>\n",
       "      <th>CONTROLADOR</th>\n",
       "      <th>INVEST_CEMPR_EXTER</th>\n",
       "      <th>CLASSE_ANBIMA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FACFIF</td>\n",
       "      <td>00.000.684/0001-21</td>\n",
       "      <td>DEUTSCHE BANK FDO APLIC QUOTAS FDO INV FINANCE...</td>\n",
       "      <td>2003-04-30</td>\n",
       "      <td>1994-12-20</td>\n",
       "      <td>19.0000</td>\n",
       "      <td>2000-08-01</td>\n",
       "      <td>CANCELADA</td>\n",
       "      <td>2000-08-01</td>\n",
       "      <td>NaN</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>1</th>\n",
       "      <td>FACFIF</td>\n",
       "      <td>00.000.731/0001-37</td>\n",
       "      <td>ITAMARITI CASH FUNDO APLICACAO QUOTAS FDOS INV...</td>\n",
       "      <td>2003-04-30</td>\n",
       "      <td>1994-05-18</td>\n",
       "      <td>40681.0000</td>\n",
       "      <td>1996-01-26</td>\n",
       "      <td>CANCELADA</td>\n",
       "      <td>1996-01-26</td>\n",
       "      <td>NaN</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>2</th>\n",
       "      <td>FACFIF</td>\n",
       "      <td>00.000.732/0001-81</td>\n",
       "      <td>FUNDO APLIC. QUOTAS DE F.I. SANTANDER CURTO PRAZO</td>\n",
       "      <td>2003-04-30</td>\n",
       "      <td>1994-05-24</td>\n",
       "      <td>27.0000</td>\n",
       "      <td>1999-09-03</td>\n",
       "      <td>CANCELADA</td>\n",
       "      <td>1999-09-03</td>\n",
       "      <td>NaN</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>3</th>\n",
       "      <td>FACFIF</td>\n",
       "      <td>00.000.740/0001-28</td>\n",
       "      <td>FUNDO DE APLIC EM QUOTAS DE FUNDOS DE INV BMC ...</td>\n",
       "      <td>2003-04-30</td>\n",
       "      <td>1994-05-23</td>\n",
       "      <td>40690.0000</td>\n",
       "      <td>1996-06-10</td>\n",
       "      <td>CANCELADA</td>\n",
       "      <td>1996-06-10</td>\n",
       "      <td>NaN</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>4</th>\n",
       "      <td>FACFIF</td>\n",
       "      <td>00.000.749/0001-39</td>\n",
       "      <td>BALANCE FUNDO APLICACAO QUOTAS FUNDO INVESTIME...</td>\n",
       "      <td>2003-04-30</td>\n",
       "      <td>1994-05-12</td>\n",
       "      <td>35.0000</td>\n",
       "      <td>2000-06-26</td>\n",
       "      <td>CANCELADA</td>\n",
       "      <td>2000-06-26</td>\n",
       "      <td>NaN</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  TP_FUNDO          CNPJ_FUNDO  \\\n",
       "0   FACFIF  00.000.684/0001-21   \n",
       "1   FACFIF  00.000.731/0001-37   \n",
       "2   FACFIF  00.000.732/0001-81   \n",
       "3   FACFIF  00.000.740/0001-28   \n",
       "4   FACFIF  00.000.749/0001-39   \n",
       "\n",
       "                                        DENOM_SOCIAL      DT_REG    DT_CONST  \\\n",
       "0  DEUTSCHE BANK FDO APLIC QUOTAS FDO INV FINANCE...  2003-04-30  1994-12-20   \n",
       "1  ITAMARITI CASH FUNDO APLICACAO QUOTAS FDOS INV...  2003-04-30  1994-05-18   \n",
       "2  FUNDO APLIC. QUOTAS DE F.I. SANTANDER CURTO PRAZO  2003-04-30  1994-05-24   \n",
       "3  FUNDO DE APLIC EM QUOTAS DE FUNDOS DE INV BMC ...  2003-04-30  1994-05-23   \n",
       "4  BALANCE FUNDO APLICACAO QUOTAS FUNDO INVESTIME...  2003-04-30  1994-05-12   \n",
       "\n",
       "      CD_CVM   DT_CANCEL        SIT  DT_INI_SIT DT_INI_ATIV  ...  \\\n",
       "0    19.0000  2000-08-01  CANCELADA  2000-08-01         NaN  ...   \n",
       "1 40681.0000  1996-01-26  CANCELADA  1996-01-26         NaN  ...   \n",
       "2    27.0000  1999-09-03  CANCELADA  1999-09-03         NaN  ...   \n",
       "3 40690.0000  1996-06-10  CANCELADA  1996-06-10         NaN  ...   \n",
       "4    35.0000  2000-06-26  CANCELADA  2000-06-26         NaN  ...   \n",
       "\n",
       "  CPF_CNPJ_GESTOR GESTOR CNPJ_AUDITOR AUDITOR CNPJ_CUSTODIANTE CUSTODIANTE  \\\n",
       "0             NaN    NaN          NaN     NaN              NaN         NaN   \n",
       "1             NaN    NaN          NaN     NaN              NaN         NaN   \n",
       "2             NaN    NaN          NaN     NaN              NaN         NaN   \n",
       "3             NaN    NaN          NaN     NaN              NaN         NaN   \n",
       "4             NaN    NaN          NaN     NaN              NaN         NaN   \n",
       "\n",
       "  CNPJ_CONTROLADOR CONTROLADOR INVEST_CEMPR_EXTER CLASSE_ANBIMA  \n",
       "0              NaN         NaN                NaN           NaN  \n",
       "1              NaN         NaN                NaN           NaN  \n",
       "2              NaN         NaN                NaN           NaN  \n",
       "3              NaN         NaN                NaN           NaN  \n",
       "4              NaN         NaN                NaN           NaN  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cad_fi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8a269af0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 78337 entries, 0 to 78336\n",
      "Data columns (total 41 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   TP_FUNDO            78337 non-null  object \n",
      " 1   CNPJ_FUNDO          78337 non-null  object \n",
      " 2   DENOM_SOCIAL        78337 non-null  object \n",
      " 3   DT_REG              78337 non-null  object \n",
      " 4   DT_CONST            76238 non-null  object \n",
      " 5   CD_CVM              78333 non-null  float64\n",
      " 6   DT_CANCEL           42205 non-null  object \n",
      " 7   SIT                 78337 non-null  object \n",
      " 8   DT_INI_SIT          78337 non-null  object \n",
      " 9   DT_INI_ATIV         56775 non-null  object \n",
      " 10  DT_INI_EXERC        60644 non-null  object \n",
      " 11  DT_FIM_EXERC        60644 non-null  object \n",
      " 12  CLASSE              65039 non-null  object \n",
      " 13  DT_INI_CLASSE       65039 non-null  object \n",
      " 14  RENTAB_FUNDO        49201 non-null  object \n",
      " 15  CONDOM              64691 non-null  object \n",
      " 16  FUNDO_COTAS         65045 non-null  object \n",
      " 17  FUNDO_EXCLUSIVO     54973 non-null  object \n",
      " 18  TRIB_LPRAZO         27736 non-null  object \n",
      " 19  PUBLICO_ALVO        44768 non-null  object \n",
      " 20  ENTID_INVEST        2529 non-null   object \n",
      " 21  TAXA_PERFM          34846 non-null  float64\n",
      " 22  INF_TAXA_PERFM      6213 non-null   object \n",
      " 23  TAXA_ADM            35057 non-null  float64\n",
      " 24  INF_TAXA_ADM        8133 non-null   object \n",
      " 25  VL_PATRIM_LIQ       67571 non-null  float64\n",
      " 26  DT_PATRIM_LIQ       67571 non-null  object \n",
      " 27  DIRETOR             41409 non-null  object \n",
      " 28  CNPJ_ADMIN          51719 non-null  object \n",
      " 29  ADMIN               51719 non-null  object \n",
      " 30  PF_PJ_GESTOR        51179 non-null  object \n",
      " 31  CPF_CNPJ_GESTOR     51179 non-null  object \n",
      " 32  GESTOR              51179 non-null  object \n",
      " 33  CNPJ_AUDITOR        50490 non-null  object \n",
      " 34  AUDITOR             50490 non-null  object \n",
      " 35  CNPJ_CUSTODIANTE    49634 non-null  object \n",
      " 36  CUSTODIANTE         49634 non-null  object \n",
      " 37  CNPJ_CONTROLADOR    41242 non-null  object \n",
      " 38  CONTROLADOR         41242 non-null  object \n",
      " 39  INVEST_CEMPR_EXTER  54973 non-null  object \n",
      " 40  CLASSE_ANBIMA       31067 non-null  object \n",
      "dtypes: float64(4), object(37)\n",
      "memory usage: 24.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df_cad_fi.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "022dd970",
   "metadata": {},
   "source": [
    "### Write the dataframe into the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1283694a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to a local SQLite database previously created\n",
    "conn = sqlite3.connect('D:/finance_data/finance_database.db') \n",
    "\n",
    "df_cad_fi.to_sql('CVM_funds_cadastral_info',conn,if_exists='replace',index=False)\n",
    "\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  }
 ],
 "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
}