Cotação_FIIs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Definindo variáveis\n",
    "header = {'User-Agent':'Mozilla/5.0(Windows NT 10.0; Win64) AppleWebKit/537.36 \\(KHTML, like Gecko)Chrome/97.04692.99 Safari/537.36'}\n",
    "url = 'https://www.fundsexplorer.com.br/ranking'\n",
    "request = requests.get(url, headers=header)\n",
    "\n",
    "df = pd.read_html(request.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------\n",
      "0\n",
      "    Códigodo fundo                          Setor Preço Atual  \\\n",
      "0           FIVN11                      Shoppings     R$ 3,27   \n",
      "1           BZLI11            Títulos e Val. Mob.    R$ 17,00   \n",
      "2           XTED11             Lajes Corporativas     R$ 8,82   \n",
      "3           ALMI11             Lajes Corporativas   R$ 921,00   \n",
      "4           PABY11                        Híbrido    R$ 17,78   \n",
      "..             ...                            ...         ...   \n",
      "379         HREC11            Títulos e Val. Mob.    R$ 79,75   \n",
      "380         VCRI11                            NaN     R$ 8,96   \n",
      "381         AIEC11             Lajes Corporativas    R$ 67,19   \n",
      "382         VCRA11  Títulos e Valores Mobiliários   R$ 104,47   \n",
      "383         CYCR11            Títulos e Val. Mob.     R$ 8,89   \n",
      "\n",
      "     Liquidez Diária Dividendo DividendYield DY (3M)Acumulado  \\\n",
      "0            25075.0   R$ 0,00         0,00%            0,00%   \n",
      "1                5.0   R$ 0,00         0,00%            0,00%   \n",
      "2             2223.0   R$ 0,00         0,00%            0,00%   \n",
      "3                4.0   R$ 0,00         0,00%            0,00%   \n",
      "4              469.0   R$ 0,00         0,00%            0,00%   \n",
      "..               ...       ...           ...              ...   \n",
      "379           8592.0   R$ 0,95         1,17%            3,31%   \n",
      "380          36032.0   R$ 0,13         1,38%            4,02%   \n",
      "381          19030.0   R$ 1,32         1,90%            3,98%   \n",
      "382           2264.0   R$ 1,50         1,41%            2,72%   \n",
      "383          14568.0   R$ 0,12         1,31%            3,66%   \n",
      "\n",
      "    DY (6M)Acumulado DY (12M)Acumulado DY (3M)Média  ...     PatrimônioLíq.  \\\n",
      "0              0,00%             0,00%        0,00%  ...   R$ 65.352.435,22   \n",
      "1              0,00%             0,00%        0,00%  ...  R$ 433.127.158,26   \n",
      "2              0,00%             0,00%        0,00%  ...   R$ 27.688.895,17   \n",
      "3              0,00%             0,00%        0,00%  ...  R$ 250.446.109,75   \n",
      "4              0,00%             0,00%        0,00%  ...  R$ -12.026.292,32   \n",
      "..               ...               ...          ...  ...                ...   \n",
      "379            6,14%            12,79%        1,10%  ...  R$ 462.332.546,07   \n",
      "380            8,27%             0,00%        1,34%  ...  R$ 170.030.754,84   \n",
      "381            6,86%            12,92%        1,33%  ...  R$ 494.835.689,55   \n",
      "382            0,00%             0,00%        0,91%  ...  R$ 201.093.976,91   \n",
      "383            6,98%            14,22%        1,22%  ...  R$ 155.109.836,70   \n",
      "\n",
      "             VPA  P/VPA DYPatrimonial VariaçãoPatrimonial  \\\n",
      "0        R$ 6,94   47.0           NaN                 NaN   \n",
      "1       R$ 10,49  162.0           NaN                 NaN   \n",
      "2       R$ 13,26   67.0           NaN                 NaN   \n",
      "3    R$ 2.252,68   41.0           NaN                 NaN   \n",
      "4      R$ -15,86 -112.0           NaN                 NaN   \n",
      "..           ...    ...           ...                 ...   \n",
      "379     R$ 89,66   89.0         1,06%              -1,80%   \n",
      "380      R$ 9,57   94.0         1,25%              -1,33%   \n",
      "381    R$ 102,56   66.0         0,73%               0,24%   \n",
      "382    R$ 102,38  102.0         1,47%               0,00%   \n",
      "383     R$ 95,07    9.0         0,11%              -0,69%   \n",
      "\n",
      "    Rentab. Patr.no Período Rentab. Patr.Acumulada VacânciaFísica  \\\n",
      "0                       NaN                    NaN         56,00%   \n",
      "1                       NaN                    NaN            NaN   \n",
      "2                       NaN                    NaN          0,00%   \n",
      "3                       NaN                    NaN         64,05%   \n",
      "4                       NaN                    NaN            NaN   \n",
      "..                      ...                    ...            ...   \n",
      "379                  -0,76%                  9,87%            NaN   \n",
      "380                  -0,09%                  1,69%            NaN   \n",
      "381                   0,98%                  9,40%          0,00%   \n",
      "382                   1,47%                  1,47%            NaN   \n",
      "383                  -0,59%                  6,16%            NaN   \n",
      "\n",
      "     VacânciaFinanceira QuantidadeAtivos  \n",
      "0                   NaN                1  \n",
      "1                   NaN                0  \n",
      "2               100,00%                1  \n",
      "3                   NaN                1  \n",
      "4                   NaN                1  \n",
      "..                  ...              ...  \n",
      "379                 NaN                0  \n",
      "380                 NaN                0  \n",
      "381                 NaN                2  \n",
      "382                 NaN                0  \n",
      "383                 NaN                0  \n",
      "\n",
      "[384 rows x 26 columns]\n"
     ]
    }
   ],
   "source": [
    "#enumerando DataFrames\n",
    "for i, tabela in enumerate(df):\n",
    "    print('---------------------------')\n",
    "print(i)\n",
    "print(tabela)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Atribuindo tabela principal ao DataFrame \"0\"\n",
    "df = pd.DataFrame(df[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'str' object has no attribute 'float64'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[57], line 20\u001b[0m\n\u001b[0;32m     17\u001b[0m col16 \u001b[39m=\u001b[39m \u001b[39m'\u001b[39m\u001b[39mVacânciaFísica\u001b[39m\u001b[39m'\u001b[39m \u001b[39m#%\u001b[39;00m\n\u001b[0;32m     19\u001b[0m df[col1] \u001b[39m=\u001b[39m df[col1]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39m(x)\u001b[39m.\u001b[39mreplace(\u001b[39m\"\u001b[39m\u001b[39m%\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39m\"\u001b[39m))\n\u001b[1;32m---> 20\u001b[0m df[col1] \u001b[39m=\u001b[39m df[col1]\u001b[39m.\u001b[39;49mapply(\u001b[39mlambda\u001b[39;49;00m x: \u001b[39mstr\u001b[39;49m(x)\u001b[39m.\u001b[39;49mreplace(\u001b[39m\"\u001b[39;49m\u001b[39m,\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m.\u001b[39;49m\u001b[39m\"\u001b[39;49m)\u001b[39m.\u001b[39;49mfloat64())\n\u001b[0;32m     21\u001b[0m df[col2] \u001b[39m=\u001b[39m df[col2]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39m(x)\u001b[39m.\u001b[39mreplace(\u001b[39m\"\u001b[39m\u001b[39m%\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39m\"\u001b[39m))\n\u001b[0;32m     22\u001b[0m df[col2] \u001b[39m=\u001b[39m df[col2]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39m(x)\u001b[39m.\u001b[39mreplace(\u001b[39m\"\u001b[39m\u001b[39m,\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39m.\u001b[39m\u001b[39m\"\u001b[39m))\n",
      "File \u001b[1;32mc:\\Users\\leoro\\anaconda3\\lib\\site-packages\\pandas\\core\\series.py:4433\u001b[0m, in \u001b[0;36mSeries.apply\u001b[1;34m(self, func, convert_dtype, args, **kwargs)\u001b[0m\n\u001b[0;32m   4323\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mapply\u001b[39m(\n\u001b[0;32m   4324\u001b[0m     \u001b[39mself\u001b[39m,\n\u001b[0;32m   4325\u001b[0m     func: AggFuncType,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   4328\u001b[0m     \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs,\n\u001b[0;32m   4329\u001b[0m ) \u001b[39m-\u001b[39m\u001b[39m>\u001b[39m DataFrame \u001b[39m|\u001b[39m Series:\n\u001b[0;32m   4330\u001b[0m     \u001b[39m\"\"\"\u001b[39;00m\n\u001b[0;32m   4331\u001b[0m \u001b[39m    Invoke function on values of Series.\u001b[39;00m\n\u001b[0;32m   4332\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   4431\u001b[0m \u001b[39m    dtype: float64\u001b[39;00m\n\u001b[0;32m   4432\u001b[0m \u001b[39m    \"\"\"\u001b[39;00m\n\u001b[1;32m-> 4433\u001b[0m     \u001b[39mreturn\u001b[39;00m SeriesApply(\u001b[39mself\u001b[39;49m, func, convert_dtype, args, kwargs)\u001b[39m.\u001b[39;49mapply()\n",
      "File \u001b[1;32mc:\\Users\\leoro\\anaconda3\\lib\\site-packages\\pandas\\core\\apply.py:1082\u001b[0m, in \u001b[0;36mSeriesApply.apply\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1078\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39misinstance\u001b[39m(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mf, \u001b[39mstr\u001b[39m):\n\u001b[0;32m   1079\u001b[0m     \u001b[39m# if we are a string, try to dispatch\u001b[39;00m\n\u001b[0;32m   1080\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mapply_str()\n\u001b[1;32m-> 1082\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mapply_standard()\n",
      "File \u001b[1;32mc:\\Users\\leoro\\anaconda3\\lib\\site-packages\\pandas\\core\\apply.py:1137\u001b[0m, in \u001b[0;36mSeriesApply.apply_standard\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1131\u001b[0m         values \u001b[39m=\u001b[39m obj\u001b[39m.\u001b[39mastype(\u001b[39mobject\u001b[39m)\u001b[39m.\u001b[39m_values\n\u001b[0;32m   1132\u001b[0m         \u001b[39m# error: Argument 2 to \"map_infer\" has incompatible type\u001b[39;00m\n\u001b[0;32m   1133\u001b[0m         \u001b[39m# \"Union[Callable[..., Any], str, List[Union[Callable[..., Any], str]],\u001b[39;00m\n\u001b[0;32m   1134\u001b[0m         \u001b[39m# Dict[Hashable, Union[Union[Callable[..., Any], str],\u001b[39;00m\n\u001b[0;32m   1135\u001b[0m         \u001b[39m# List[Union[Callable[..., Any], str]]]]]\"; expected\u001b[39;00m\n\u001b[0;32m   1136\u001b[0m         \u001b[39m# \"Callable[[Any], Any]\"\u001b[39;00m\n\u001b[1;32m-> 1137\u001b[0m         mapped \u001b[39m=\u001b[39m lib\u001b[39m.\u001b[39;49mmap_infer(\n\u001b[0;32m   1138\u001b[0m             values,\n\u001b[0;32m   1139\u001b[0m             f,  \u001b[39m# type: ignore[arg-type]\u001b[39;49;00m\n\u001b[0;32m   1140\u001b[0m             convert\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mconvert_dtype,\n\u001b[0;32m   1141\u001b[0m         )\n\u001b[0;32m   1143\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(mapped) \u001b[39mand\u001b[39;00m \u001b[39misinstance\u001b[39m(mapped[\u001b[39m0\u001b[39m], ABCSeries):\n\u001b[0;32m   1144\u001b[0m     \u001b[39m# GH#43986 Need to do list(mapped) in order to get treated as nested\u001b[39;00m\n\u001b[0;32m   1145\u001b[0m     \u001b[39m#  See also GH#25959 regarding EA support\u001b[39;00m\n\u001b[0;32m   1146\u001b[0m     \u001b[39mreturn\u001b[39;00m obj\u001b[39m.\u001b[39m_constructor_expanddim(\u001b[39mlist\u001b[39m(mapped), index\u001b[39m=\u001b[39mobj\u001b[39m.\u001b[39mindex)\n",
      "File \u001b[1;32mc:\\Users\\leoro\\anaconda3\\lib\\site-packages\\pandas\\_libs\\lib.pyx:2870\u001b[0m, in \u001b[0;36mpandas._libs.lib.map_infer\u001b[1;34m()\u001b[0m\n",
      "Cell \u001b[1;32mIn[57], line 20\u001b[0m, in \u001b[0;36m<lambda>\u001b[1;34m(x)\u001b[0m\n\u001b[0;32m     17\u001b[0m col16 \u001b[39m=\u001b[39m \u001b[39m'\u001b[39m\u001b[39mVacânciaFísica\u001b[39m\u001b[39m'\u001b[39m \u001b[39m#%\u001b[39;00m\n\u001b[0;32m     19\u001b[0m df[col1] \u001b[39m=\u001b[39m df[col1]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39m(x)\u001b[39m.\u001b[39mreplace(\u001b[39m\"\u001b[39m\u001b[39m%\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39m\"\u001b[39m))\n\u001b[1;32m---> 20\u001b[0m df[col1] \u001b[39m=\u001b[39m df[col1]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39;49m(x)\u001b[39m.\u001b[39;49mreplace(\u001b[39m\"\u001b[39;49m\u001b[39m,\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m.\u001b[39;49m\u001b[39m\"\u001b[39;49m)\u001b[39m.\u001b[39;49mfloat64())\n\u001b[0;32m     21\u001b[0m df[col2] \u001b[39m=\u001b[39m df[col2]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39m(x)\u001b[39m.\u001b[39mreplace(\u001b[39m\"\u001b[39m\u001b[39m%\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39m\"\u001b[39m))\n\u001b[0;32m     22\u001b[0m df[col2] \u001b[39m=\u001b[39m df[col2]\u001b[39m.\u001b[39mapply(\u001b[39mlambda\u001b[39;00m x: \u001b[39mstr\u001b[39m(x)\u001b[39m.\u001b[39mreplace(\u001b[39m\"\u001b[39m\u001b[39m,\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39m.\u001b[39m\u001b[39m\"\u001b[39m))\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'str' object has no attribute 'float64'"
     ]
    }
   ],
   "source": [
    "\n",
    "col0 = 'DY (12M)Média'\n",
    "col1 = 'DividendYield'\t\n",
    "col2 = 'DY (3M)Acumulado'\t\n",
    "col3 ='DY (6M)Acumulado'\t\n",
    "col4 ='DY (12M)Acumulado'\t\n",
    "col5 = 'DY (3M)Média'\t\n",
    "col6 = 'DY (6M)Média'\t\n",
    "col7 = 'DY (12M)Média'\t\n",
    "col8 = 'DY Ano'\n",
    "col9 = 'Variação Preço'\t\n",
    "col10 = 'Rentab.Período'\t\n",
    "col11 = 'Rentab.Acumulada' \n",
    "col12 = 'VPA' #$\n",
    "col13 = 'PatrimônioLíq.' #$\n",
    "col14 = 'Dividendo' #$\n",
    "col15 = 'Preço Atual' #$\n",
    "col16 = 'VacânciaFísica' #%\n",
    "\n",
    "df[col1] = df[col1].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col1] = df[col1].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col2] = df[col2].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col2] = df[col2].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col3] = df[col3].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col3] = df[col3].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col4] = df[col4].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col4] = df[col4].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col5] = df[col5].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col5] = df[col5].apply(lambda x: str(x).replace(\".\",\",\"))\n",
    "df[col6] = df[col6].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col6] = df[col6].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col7] = df[col7].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col7] = df[col7].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col8] = df[col8].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col8] = df[col8].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col9] = df[col9].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col10] = df[col10].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col11] = df[col11].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df[col15] = df[col15].apply(lambda x: str(x).replace(\"R$\",\"\"))\n",
    "df[col15] = df[col15].apply(lambda x: str(x).replace(\".\",\"\"))\n",
    "df[col15] = df[col15].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col14] = df[col14].apply(lambda x: str(x).replace(\"R$\",\"\"))\n",
    "df[col14] = df[col14].apply(lambda x: str(x).replace(\".\",\"\"))\n",
    "df[col14] = df[col14].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col13] = df[col13].apply(lambda x: str(x).replace(\"R$\",\"\"))\n",
    "df[col12] = df[col12].apply(lambda x: str(x).replace(\"R$\",\"\"))\n",
    "df[col16] = df[col16].apply(lambda x: str(x).replace(\",\",\".\"))\n",
    "df[col16] = df[col16].apply(lambda x: str(x).replace(\"%\",\"\"))\n",
    "df = df.fillna(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gspread\n",
    "\n",
    "CODE = '1mO-7fwEb6euJCBalaZRgtHsFt4NXFHuP4qkFD0UPtVk'\n",
    "gc = gspread.service_account(filename = 'key.json')\n",
    "sh = gc.open_by_key(CODE)\n",
    "ws = sh.worksheet('Cotacao_fiis')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FIVN11</td>\n",
       "      <td>Shoppings</td>\n",
       "      <td>327</td>\n",
       "      <td>25075.0</td>\n",
       "      <td>000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,00</td>\n",
       "      <td>...</td>\n",
       "      <td>65.352.435,22</td>\n",
       "      <td>6,94</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>56.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BZLI11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>1700</td>\n",
       "      <td>5.0</td>\n",
       "      <td>000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,00</td>\n",
       "      <td>...</td>\n",
       "      <td>433.127.158,26</td>\n",
       "      <td>10,49</td>\n",
       "      <td>162.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>nan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>XTED11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>882</td>\n",
       "      <td>2223.0</td>\n",
       "      <td>000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,00</td>\n",
       "      <td>...</td>\n",
       "      <td>27.688.895,17</td>\n",
       "      <td>13,26</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100,00%</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALMI11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>92100</td>\n",
       "      <td>4.0</td>\n",
       "      <td>000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,00</td>\n",
       "      <td>...</td>\n",
       "      <td>250.446.109,75</td>\n",
       "      <td>2.252,68</td>\n",
       "      <td>41.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64.05</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PABY11</td>\n",
       "      <td>Híbrido</td>\n",
       "      <td>1778</td>\n",
       "      <td>469.0</td>\n",
       "      <td>000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,00</td>\n",
       "      <td>...</td>\n",
       "      <td>-12.026.292,32</td>\n",
       "      <td>-15,86</td>\n",
       "      <td>-112.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>nan</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>HREC11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>7975</td>\n",
       "      <td>8592.0</td>\n",
       "      <td>095</td>\n",
       "      <td>1.17</td>\n",
       "      <td>3.31</td>\n",
       "      <td>6.14</td>\n",
       "      <td>12.79</td>\n",
       "      <td>1,10</td>\n",
       "      <td>...</td>\n",
       "      <td>462.332.546,07</td>\n",
       "      <td>89,66</td>\n",
       "      <td>89.0</td>\n",
       "      <td>1,06%</td>\n",
       "      <td>-1,80%</td>\n",
       "      <td>-0,76%</td>\n",
       "      <td>9,87%</td>\n",
       "      <td>nan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td>VCRI11</td>\n",
       "      <td>0</td>\n",
       "      <td>896</td>\n",
       "      <td>36032.0</td>\n",
       "      <td>013</td>\n",
       "      <td>1.38</td>\n",
       "      <td>4.02</td>\n",
       "      <td>8.27</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1,34</td>\n",
       "      <td>...</td>\n",
       "      <td>170.030.754,84</td>\n",
       "      <td>9,57</td>\n",
       "      <td>94.0</td>\n",
       "      <td>1,25%</td>\n",
       "      <td>-1,33%</td>\n",
       "      <td>-0,09%</td>\n",
       "      <td>1,69%</td>\n",
       "      <td>nan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>AIEC11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>6719</td>\n",
       "      <td>19030.0</td>\n",
       "      <td>132</td>\n",
       "      <td>1.90</td>\n",
       "      <td>3.98</td>\n",
       "      <td>6.86</td>\n",
       "      <td>12.92</td>\n",
       "      <td>1,33</td>\n",
       "      <td>...</td>\n",
       "      <td>494.835.689,55</td>\n",
       "      <td>102,56</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0,73%</td>\n",
       "      <td>0,24%</td>\n",
       "      <td>0,98%</td>\n",
       "      <td>9,40%</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td>VCRA11</td>\n",
       "      <td>Títulos e Valores Mobiliários</td>\n",
       "      <td>10447</td>\n",
       "      <td>2264.0</td>\n",
       "      <td>150</td>\n",
       "      <td>1.41</td>\n",
       "      <td>2.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,91</td>\n",
       "      <td>...</td>\n",
       "      <td>201.093.976,91</td>\n",
       "      <td>102,38</td>\n",
       "      <td>102.0</td>\n",
       "      <td>1,47%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>1,47%</td>\n",
       "      <td>1,47%</td>\n",
       "      <td>nan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>CYCR11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>889</td>\n",
       "      <td>14568.0</td>\n",
       "      <td>012</td>\n",
       "      <td>1.31</td>\n",
       "      <td>3.66</td>\n",
       "      <td>6.98</td>\n",
       "      <td>14.22</td>\n",
       "      <td>1,22</td>\n",
       "      <td>...</td>\n",
       "      <td>155.109.836,70</td>\n",
       "      <td>95,07</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0,11%</td>\n",
       "      <td>-0,69%</td>\n",
       "      <td>-0,59%</td>\n",
       "      <td>6,16%</td>\n",
       "      <td>nan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>384 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Códigodo fundo                          Setor Preço Atual  \\\n",
       "0           FIVN11                      Shoppings         327   \n",
       "1           BZLI11            Títulos e Val. Mob.        1700   \n",
       "2           XTED11             Lajes Corporativas         882   \n",
       "3           ALMI11             Lajes Corporativas       92100   \n",
       "4           PABY11                        Híbrido        1778   \n",
       "..             ...                            ...         ...   \n",
       "379         HREC11            Títulos e Val. Mob.        7975   \n",
       "380         VCRI11                              0         896   \n",
       "381         AIEC11             Lajes Corporativas        6719   \n",
       "382         VCRA11  Títulos e Valores Mobiliários       10447   \n",
       "383         CYCR11            Títulos e Val. Mob.         889   \n",
       "\n",
       "     Liquidez Diária Dividendo DividendYield DY (3M)Acumulado  \\\n",
       "0            25075.0       000          0.00             0.00   \n",
       "1                5.0       000          0.00             0.00   \n",
       "2             2223.0       000          0.00             0.00   \n",
       "3                4.0       000          0.00             0.00   \n",
       "4              469.0       000          0.00             0.00   \n",
       "..               ...       ...           ...              ...   \n",
       "379           8592.0       095          1.17             3.31   \n",
       "380          36032.0       013          1.38             4.02   \n",
       "381          19030.0       132          1.90             3.98   \n",
       "382           2264.0       150          1.41             2.72   \n",
       "383          14568.0       012          1.31             3.66   \n",
       "\n",
       "    DY (6M)Acumulado DY (12M)Acumulado DY (3M)Média  ...   PatrimônioLíq.  \\\n",
       "0               0.00              0.00         0,00  ...    65.352.435,22   \n",
       "1               0.00              0.00         0,00  ...   433.127.158,26   \n",
       "2               0.00              0.00         0,00  ...    27.688.895,17   \n",
       "3               0.00              0.00         0,00  ...   250.446.109,75   \n",
       "4               0.00              0.00         0,00  ...   -12.026.292,32   \n",
       "..               ...               ...          ...  ...              ...   \n",
       "379             6.14             12.79         1,10  ...   462.332.546,07   \n",
       "380             8.27              0.00         1,34  ...   170.030.754,84   \n",
       "381             6.86             12.92         1,33  ...   494.835.689,55   \n",
       "382             0.00              0.00         0,91  ...   201.093.976,91   \n",
       "383             6.98             14.22         1,22  ...   155.109.836,70   \n",
       "\n",
       "           VPA  P/VPA DYPatrimonial VariaçãoPatrimonial  \\\n",
       "0         6,94   47.0             0                   0   \n",
       "1        10,49  162.0             0                   0   \n",
       "2        13,26   67.0             0                   0   \n",
       "3     2.252,68   41.0             0                   0   \n",
       "4       -15,86 -112.0             0                   0   \n",
       "..         ...    ...           ...                 ...   \n",
       "379      89,66   89.0         1,06%              -1,80%   \n",
       "380       9,57   94.0         1,25%              -1,33%   \n",
       "381     102,56   66.0         0,73%               0,24%   \n",
       "382     102,38  102.0         1,47%               0,00%   \n",
       "383      95,07    9.0         0,11%              -0,69%   \n",
       "\n",
       "    Rentab. Patr.no Período Rentab. Patr.Acumulada VacânciaFísica  \\\n",
       "0                         0                      0          56.00   \n",
       "1                         0                      0            nan   \n",
       "2                         0                      0           0.00   \n",
       "3                         0                      0          64.05   \n",
       "4                         0                      0            nan   \n",
       "..                      ...                    ...            ...   \n",
       "379                  -0,76%                  9,87%            nan   \n",
       "380                  -0,09%                  1,69%            nan   \n",
       "381                   0,98%                  9,40%           0.00   \n",
       "382                   1,47%                  1,47%            nan   \n",
       "383                  -0,59%                  6,16%            nan   \n",
       "\n",
       "     VacânciaFinanceira QuantidadeAtivos  \n",
       "0                     0                1  \n",
       "1                     0                0  \n",
       "2               100,00%                1  \n",
       "3                     0                1  \n",
       "4                     0                1  \n",
       "..                  ...              ...  \n",
       "379                   0                0  \n",
       "380                   0                0  \n",
       "381                   0                2  \n",
       "382                   0                0  \n",
       "383                   0                0  \n",
       "\n",
       "[384 rows x 26 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "ws.update('A1:Z400',[df.columns.values.tolist()]+ df.values.tolist())\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 384 entries, 0 to 383\n",
      "Data columns (total 26 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   Códigodo fundo           384 non-null    object \n",
      " 1   Setor                    384 non-null    object \n",
      " 2   Preço Atual              384 non-null    object \n",
      " 3   Liquidez Diária          384 non-null    float64\n",
      " 4   Dividendo                384 non-null    object \n",
      " 5   DividendYield            384 non-null    object \n",
      " 6   DY (3M)Acumulado         384 non-null    object \n",
      " 7   DY (6M)Acumulado         384 non-null    object \n",
      " 8   DY (12M)Acumulado        384 non-null    object \n",
      " 9   DY (3M)Média             384 non-null    object \n",
      " 10  DY (6M)Média             384 non-null    object \n",
      " 11  DY (12M)Média            384 non-null    object \n",
      " 12  DY Ano                   384 non-null    object \n",
      " 13  Variação Preço           384 non-null    object \n",
      " 14  Rentab.Período           384 non-null    object \n",
      " 15  Rentab.Acumulada         384 non-null    object \n",
      " 16  PatrimônioLíq.           384 non-null    object \n",
      " 17  VPA                      384 non-null    object \n",
      " 18  P/VPA                    384 non-null    float64\n",
      " 19  DYPatrimonial            384 non-null    object \n",
      " 20  VariaçãoPatrimonial      384 non-null    object \n",
      " 21  Rentab. Patr.no Período  384 non-null    object \n",
      " 22  Rentab. Patr.Acumulada   384 non-null    object \n",
      " 23  VacânciaFísica           384 non-null    object \n",
      " 24  VacânciaFinanceira       384 non-null    object \n",
      " 25  QuantidadeAtivos         384 non-null    int64  \n",
      "dtypes: float64(2), int64(1), object(23)\n",
      "memory usage: 78.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  }
 ],
 "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.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "0bcc74a3859c92d3d479f83aaab38682f7a8337fbc1b385a67378df8938a88ee"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}