datasets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "EUADdDljoqEY"
   },
   "source": [
    "# Controle da carteira de ações (Swing Trade)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5VDvumIGo7CL"
   },
   "source": [
    "- Este Notebook é só para gerar o dataset usado para fazer o IR e acampanhar o rendimento da carteira\n",
    "\n",
    "**Quando não usar:**\n",
    "\n",
    "- Se tiver opeações de Day Trade.\n",
    "\n",
    "- Se tiver compras no mesmo dia de ações Fracionárias e a Vista. (Corrigir isso)\n",
    "\n",
    "**Obs.:**\n",
    "\n",
    "- Se fizer compras/vendas da mesma ação em corretoras diferentes, o valor médio para declarar no IR pode estar errado."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "fsXuuZGqCLhC"
   },
   "source": [
    "## Baixando a planinlha com os ativos negociados\n",
    "\n",
    "Para pegar a planinlha de excel com todas as negociações é só ir no site da [B3](http://www.b3.com.br/pt_br/), procurar pelo Canal Eletrônico do Investidor (CEI).\n",
    "\n",
    "> [Link direto](https://cei.b3.com.br/CEI_Responsivo/login.aspx) \n",
    "\n",
    "Depois de acessar a página é só ir em: Extratos e informativos $\\Rightarrow$ Negociações de Ativos. Você deve ver a opção de baixar a planilha por excel nesta página."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inserir os arquivos da B3 (InfoCEI.xls) e csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lista com o nome dos arquivos InfoCEI ou o .csv de uma carteira antiga.\n",
    "ano = '2020'\n",
    "arq_names = ['Negociações da Clear.xls', 'Negociações da Modal.xls', 'Ultima_carteira_2019.csv']\n",
    "des_name = 'desdobramento_agrupamento_' + ano + '.xls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Funções"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleaning_and_organizing(df):\n",
    "\n",
    "    df.rename(columns = {'Preço (R$)':'Preço de C/V', 'Data Negócio': 'Data',\n",
    "                         'Valor Total (R$)':'Valor Total',\n",
    "                         'Especificação do Ativo':'Especificação',\n",
    "                         'C/V':'Tipo de Execução',\n",
    "                         'Quantidade': 'Quantidade de C/V'}, inplace=True);\n",
    "\n",
    "    df['Mercado'] = df['Mercado'].apply(lambda x: x.replace('Merc.',''))\n",
    "    df['Quantidade de C/V'] = df['Quantidade de C/V'].astype(np.int16)\n",
    "    df['Código'] = df['Código'].apply(lambda x: x[:-1] if x[-1] == 'F' else x)\n",
    "    df['Tipo de Execução'] = df['Tipo de Execução'].str.strip()\n",
    "    df['Mercado'] = df['Mercado'].apply(lambda x: x.strip().split()[-1])\n",
    "    df['Especificação'] = df['Especificação'].str.strip()\n",
    "    df['Código'] = df['Código'].str.strip()\n",
    "    df['Corretora'] = df_ativos['Corretora'].apply(lambda x: x.split('-')[1].strip().split(' ')[0] if \n",
    "                                                  len(x.split('-')) > 1 else x)\n",
    "    \n",
    "    df.loc[df['Tipo de Execução'] == 'V', ['Quantidade de C/V']] *= -1\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_price_and_gain(tipo_op, preco_atual_anterior, preco_cv, qnt_atual_anterior, qnt_cv):\n",
    "\n",
    "    if tipo_op == 'C':\n",
    "\n",
    "        mean = np.average([preco_atual_anterior, preco_cv], weights = [qnt_atual_anterior, qnt_cv]) \n",
    "        return mean, 0.0\n",
    "\n",
    "    elif tipo_op == 'V':\n",
    "\n",
    "        mean = preco_atual_anterior\n",
    "        lucro = (preco_cv - preco_atual_anterior) * np.abs(qnt_cv)\n",
    "        return mean, lucro\n",
    "\n",
    "    elif (tipo_op == 'DES') | (tipo_op == 'AGR'):\n",
    "\n",
    "        mean = preco_atual_anterior * preco_cv\n",
    "        return mean, 0.0\n",
    "    \n",
    "    return np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def stock_mean_price(df_acao):\n",
    "    \n",
    "    ''' Calcula o preço médio da ação e em caso de venda o lucro/prejuízo. '''\n",
    "    \n",
    "    preco_cv = df_acao['Preço de C/V']\n",
    "\n",
    "    quantidade_cv = df_acao['Quantidade de C/V']\n",
    "\n",
    "    quantidade_atual = df_acao['Quantidade Atual']\n",
    "\n",
    "    tipo_op = df_acao['Tipo de Execução']\n",
    "\n",
    "    mean_price = np.zeros(df_acao.shape[0], dtype=np.float64)\n",
    "    gain_loss = np.zeros(df_acao.shape[0], dtype=np.float64)\n",
    "\n",
    "    mean_price[0] = preco_cv[0]\n",
    "\n",
    "    for i in range(1, df_acao.shape[0]):\n",
    "\n",
    "        mean_price[i], gain_loss[i] = calc_price_and_gain(tipo_op[i],\n",
    "                                                          mean_price[i - 1], preco_cv[i],\n",
    "                                                          quantidade_atual[i - 1], quantidade_cv[i])\n",
    "    \n",
    "    return mean_price[:], gain_loss[:]        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def combine_InfoCEI_files(arq_names):\n",
    "    \n",
    "    df = pd.DataFrame()\n",
    "    \n",
    "    for name in arq_names:\n",
    "            \n",
    "        if name.find('.csv') != -1:\n",
    "            \n",
    "            aux = pd.read_csv(name)\n",
    "        \n",
    "            aux['Data Negócio'] = pd.to_datetime(aux['Data Negócio'])\n",
    "        \n",
    "            df = pd.concat([df, aux], ignore_index=True)            \n",
    "            \n",
    "        elif name.find('.xls') != -1:\n",
    "                \n",
    "            aux = pd.read_excel(name, header=10, skipfooter=4).dropna(axis=1)\n",
    "            \n",
    "            aux['Data Negócio'] = aux['Data Negócio'].apply(lambda x: x.replace('/','-'))\n",
    "            aux['Data Negócio'] = pd.to_datetime(aux['Data Negócio'], infer_datetime_format=True, dayfirst=True)\n",
    "            \n",
    "            aux['Corretora'] = pd.read_excel(name, header=8, nrows=1).dropna(axis=1).iloc[0, 0]\n",
    "            \n",
    "            df = pd.concat([df, aux], ignore_index=True)\n",
    "    \n",
    "    df.sort_values(by='Data Negócio', ascending=True, inplace=True)\n",
    "    \n",
    "    return df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_last(columns, df):\n",
    "    \n",
    "    df_carteira = pd.DataFrame(columns = columns)\n",
    "    \n",
    "    carteira = carteira_de_acoes_atual(df, offline=True)\n",
    "    \n",
    "    df_carteira['Data Negócio'] = carteira['Data']\n",
    "    df_carteira['C/V'] = 'C'\n",
    "    df_carteira['Quantidade'] = carteira['Quantidade Atual']\n",
    "    df_carteira['Código'] = carteira['Código']\n",
    "    df_carteira['Preço (R$)'] = carteira['<Preço>']\n",
    "    df_carteira['Valor Total (R$)'] = carteira['<Valor Total>']\n",
    "    df_carteira['Mercado'] = carteira['Mercado']\n",
    "    df_carteira.loc[ df_carteira['Mercado'] == 'Fracionário' , 'Código'] = df_carteira.loc[ df_carteira['Mercado'] == 'Fracionário' , 'Código'].apply(lambda x: str(x) + 'F')\n",
    "    df_carteira['Corretora'] = carteira['Corretora']\n",
    "    \n",
    "    for cod in df_carteira['Código']:\n",
    "\n",
    "        df_carteira.loc[df_carteira['Código'] == cod, 'Especificação do Ativo'] = df_all.loc[df_all['Código'] == cod, 'Especificação do Ativo'].iloc[-1]\n",
    "\n",
    "    return df_carteira"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def carteira_de_acoes_atual(df, offline=False):\n",
    "    \n",
    "    carteira = pd.DataFrame(columns=df.columns)\n",
    "\n",
    "    cods = df['Código'].unique()\n",
    "    \n",
    "    for cod in cods:\n",
    "\n",
    "        last_idx = df[df[['Código']] == cod].last_valid_index() \n",
    "\n",
    "        carteira = carteira.append(df.iloc[last_idx], ignore_index=True)\n",
    "\n",
    "    carteira = carteira[carteira['Quantidade Atual'] != 0.0]\n",
    "    \n",
    "    carteira.sort_values(by='Data', ascending=True, inplace=True)\n",
    "    \n",
    "    drop_columns = ['Especificação', 'Tipo de Execução', 'Quantidade de C/V', 'Preço de C/V']\n",
    "    \n",
    "    carteira.drop(drop_columns, inplace=True, axis=1)\n",
    "    \n",
    "    carteira.rename(columns={'Preço Atual': '<Preço>'}, inplace=True)\n",
    "    \n",
    "    carteira.reset_index(drop=True, inplace=True)\n",
    "    \n",
    "    carteira['<Valor Total>'] = carteira['Quantidade Atual'] * carteira['<Preço>']\n",
    "    \n",
    "    if offline == False:\n",
    "    \n",
    "        carteira['Preço Atual'] = get_market_values(carteira['Código'])\n",
    "\n",
    "        carteira['Valor Total Atual'] = carteira['Quantidade Atual'] * carteira['Preço Atual']\n",
    "\n",
    "        carteira['L/P'] = carteira['Quantidade Atual'] * ( carteira['Preço Atual'] - carteira['<Preço>'] )\n",
    "\n",
    "        carteira['L/P (%)'] = (carteira['L/P'] / (carteira['<Preço>'] * carteira['Quantidade Atual'])) * 100.0\n",
    "\n",
    "        carteira['L/P'] = carteira['L/P'].round(2)\n",
    "\n",
    "        carteira['L/P (%)'] = carteira['L/P (%)'].round(2) \n",
    "\n",
    "        carteira['<Preço>'] = carteira['<Preço>'].round(2)\n",
    "\n",
    "\n",
    "        cols_ordenadas = ['Data', 'Mercado', 'Código', 'Quantidade Atual', '<Preço>', '<Valor Total>',\n",
    "                          'Preço Atual', 'Valor Total Atual', 'L/P', 'L/P (%)', 'Corretora']\n",
    "\n",
    "    elif offline == True:\n",
    "        \n",
    "        cols_ordenadas = ['Data', 'Mercado', 'Código', 'Quantidade Atual', '<Preço>', '<Valor Total>', 'Corretora']\n",
    "    \n",
    "    return carteira[cols_ordenadas]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def intersection(l1, l2):\n",
    "    return list(set(l1) & set(l2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lê os arquivos InfoCEI e csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = combine_InfoCEI_files(arq_names)\n",
    "\n",
    "df_ativos = df_all.copy()\n",
    "df_des_agr = pd.read_excel(des_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ativos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Organiza e limpa o dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Limpando e arruamndo os dados\n",
    "df_ativos = cleaning_and_organizing(df_ativos)\n",
    "df_des_agr['Data'] = pd.to_datetime(df_des_agr['Data'], infer_datetime_format=True, dayfirst=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tira a média diária das vendas ou compras. Isso é necessário porque podemos comprar\n",
    "# ações com valores diferentes durante o pregão em um mesmo dia.\n",
    "\n",
    "group = df_ativos.groupby(['Data', 'Código', 'Mercado', 'Especificação', 'Tipo de Execução', 'Corretora'])\n",
    "\n",
    "df_ativos = group[['Quantidade de C/V', 'Valor Total']].sum().reset_index()\n",
    "\n",
    "df_ativos['Preço de C/V'] = np.abs(df_ativos['Valor Total'] / df_ativos['Quantidade de C/V'].astype(np.float64))\n",
    "\n",
    "# Adicionando algumas colunas.\n",
    "df_ativos['Preço Atual'] = 0.0\n",
    "df_ativos['L/P'] = 0.0\n",
    "df_ativos['Quantidade Atual'] = 0.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Faz as contas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cods = intersection(df_ativos['Código'].unique(), df_des_agr['Código'].unique())\n",
    "\n",
    "for cod in cods:\n",
    "\n",
    "    df = df_des_agr[df_des_agr['Código'] == cod]\n",
    "    \n",
    "    for _, (data_split, _, prop1, prop2) in df.iterrows():\n",
    "\n",
    "        mask = (df_ativos['Código'] == cod) & (df_ativos['Data'] < data_split)\n",
    "\n",
    "        row = df_ativos[mask].tail(1).copy()\n",
    "        \n",
    "        # Taxas para converter os valores que serão desdobrados/agrupados.\n",
    "        ratio1 = prop1 / float(prop2)\n",
    "        ratio2 = prop2 / float(prop1)\n",
    "\n",
    "        if ratio1 > ratio2:\n",
    "            row['Tipo de Execução'] = 'DES'\n",
    "        else:\n",
    "            row['Tipo de Execução'] = 'AGR'\n",
    "        \n",
    "        row['Quantidade de C/V'] = df_ativos.loc[mask, 'Quantidade de C/V'].sum() * (ratio1 - 1.0)\n",
    "        row['Valor Total'] = ratio1\n",
    "        row['Preço de C/V'] = ratio2\n",
    "        \n",
    "        row.reset_index(inplace=True)\n",
    "        row['index'] += 1\n",
    "        row.set_index('index', inplace=True)\n",
    "\n",
    "        mask = (df_ativos['Data'] < data_split)\n",
    "        \n",
    "        df_ativos.reset_index(inplace=True)\n",
    "        df_ativos.loc[-mask, 'index'] += 1\n",
    "        df_ativos.set_index('index', inplace=True)\n",
    "\n",
    "        df_ativos = df_ativos.append(row).sort_values(by='index')\n",
    "        df_ativos.reset_index(drop=True, inplace=True)\n",
    "        del row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ativos.loc[df_ativos['Código'] == 'MGLU3', :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cods = df_ativos['Código'].unique()\n",
    "\n",
    "for cod in cods:\n",
    "    \n",
    "    mask = (df_ativos['Código'] == cod)\n",
    "\n",
    "    df_ativos.loc[mask, 'Quantidade Atual'] = df_ativos.loc[mask, 'Quantidade de C/V'].cumsum()\n",
    "    \n",
    "    mean_price, gain_loss = stock_mean_price(df_ativos[mask].reset_index(drop=True))\n",
    "\n",
    "    df_ativos.loc[mask, 'Preço Atual'] = mean_price\n",
    "    df_ativos.loc[mask, 'L/P'] = gain_loss"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Salva o dataset final com as movimentações diárias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ativos.to_csv('Dataset_ações_' + ano + '.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Salva a última carteira"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_carteira = save_last(df_all.columns, df_ativos.copy())\n",
    "df_carteira.to_csv('Ultima_carteira_' + ano + '.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "a4_DKY6nA41N",
    "BkQTtQ6SCPAi",
    "fsXuuZGqCLhC",
    "6HR1XLxnGmA8",
    "IQj8rvhADMrh",
    "QeCQiBzpDa97",
    "0pke8cnhFgy2",
    "wp61ie8kCsy2",
    "BaTwINdeCZ3h"
   ],
   "include_colab_link": true,
   "name": "Ações.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}