Aula_2_-_Automacao_WEB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d68d51f4",
   "metadata": {},
   "source": [
    "# Projeto 2 - Como pegar dados de um site com Python? - Pegando dados de ETFs do mundo inteiro.\n",
    "\n",
    "\n",
    "### Desafio:\n",
    "\n",
    "* Construir um código que vá no site etf.com e busque dados de todos os etfs do mercado americano e, consequentemente, do mundo. Rentabilidades, patrimônio, gestora, taxa...   \n",
    "* Lembrar de sempre trazer outros tipos de cenários onde a pessoa precisa pegar dados de sites etc.\n",
    "\n",
    "\n",
    "### Passo a passo:\n",
    "\n",
    "   **Passo 1** - Definir um navegador que você irá utilizar para navegar com o Python.\n",
    "\n",
    "   **Passo 2** - Importar os módulos e bibliotecas.\n",
    "   \n",
    "   **Passo 3** - Entender como funcionam requisições na internet.\n",
    "   \n",
    "   **Passo 4** - Conhecer e mapear o processo de coleta de dados no site do ETF.com. \n",
    "   \n",
    "   **Passo 5** - Achar todos os elementos necessários dentro do HTML do site.\n",
    "   \n",
    "   **Passo 6** - Ler a tabela de dados.\n",
    "   \n",
    "   **Passo 7** - Construir a tabela final."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d540c01d",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install webdriver-manager"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fcd1c30",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install selenium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "508b9100",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install html5lib"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b02efc7b",
   "metadata": {},
   "source": [
    "# Passo 1: Escolher o navegador.\n",
    "\n",
    "No nosso caso, utilizaremos o Google Chrome. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd5cbf30",
   "metadata": {},
   "source": [
    "# Passo 2: Importar as bibliotecas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c7f7a23e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "import time\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b42b3afb",
   "metadata": {},
   "source": [
    "# Passo 3: Entender como funcionam requisições na internet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "533e215d",
   "metadata": {},
   "outputs": [],
   "source": [
    "driver = webdriver.Chrome(service = Service(ChromeDriverManager().install()))\n",
    "\n",
    "url = \"https://www.etf.com/etfanalytics/etf-finder\"\n",
    "\n",
    "driver.get(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd6ded45",
   "metadata": {},
   "source": [
    "# Passo 4: Conhecer e mapear o processo de coleta de dados no site do ETF.com.\n",
    "\n",
    "www.etf.com"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7bfa68b",
   "metadata": {},
   "source": [
    "# Processo de dados\n",
    "\n",
    "* Abrir o site - Completo\n",
    "* Mudar a visualização pra 100\n",
    "* Ler a tabela\n",
    "* Avançar todas as páginas\n",
    "* Trocar pra outra categoria\n",
    "* Ler todas as tabelas dessa outra categoria"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b248ed43",
   "metadata": {},
   "source": [
    "# Passo 5.1: Achar todos os elementos necessários dentro do HTML do site - Expandindo a tabela para 100 itens."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "0c3294a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "time.sleep(5)\n",
    "\n",
    "botao_100 = driver.find_element(\"xpath\", '''/html/body/div[4]/section/div/div[3]/\n",
    "section/div/div/div/div/div[2]/section[2]/\n",
    "div[2]/section[2]/div[1]/div/div[4]/button/label/span''')\n",
    "\n",
    "driver.execute_script(\"arguments[0].click();\", botao_100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa8e8b25",
   "metadata": {},
   "source": [
    "# Passo 5.2: Achar todos os elementos necessários dentro do HTML do site - Pegando o número de páginas da tabela."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "22ab5688",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32\n"
     ]
    }
   ],
   "source": [
    "numero_paginas = driver.find_element(\"xpath\", '''/html/body/div[4]/section/div/div[3]/\n",
    "section/div/div/div/div/div[2]/section[2]/\n",
    "div[2]/section[2]/div[2]/div/label[2]''')\n",
    "\n",
    "numero_paginas = numero_paginas.text.replace(\"of \", \"\")\n",
    "\n",
    "numero_paginas = int(numero_paginas)\n",
    "\n",
    "print(numero_paginas)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a5f7c38",
   "metadata": {},
   "source": [
    "# Passo 6.1: Lendo a tabela de dados - Lendo a tabela de dados básicos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "808cf90b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[WDM] - Downloading: 100%|██████████| 8.72M/8.72M [57:10<00:00, 2.66kB/s]\n"
     ]
    },
    {
     "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>Ticker</th>\n",
       "      <th>Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Issuer</th>\n",
       "      <th>Expense Ratio</th>\n",
       "      <th>AUM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ZHDG</td>\n",
       "      <td>ZEGA Buy and Hedge ETF</td>\n",
       "      <td>Asset Allocation: U.S. Target Outcome</td>\n",
       "      <td>Tidal</td>\n",
       "      <td>0.98%</td>\n",
       "      <td>$56.76M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZECP</td>\n",
       "      <td>Zacks Earnings Consistent Portfolio ETF</td>\n",
       "      <td>Equity: U.S. - Total Market</td>\n",
       "      <td>Zacks</td>\n",
       "      <td>0.55%</td>\n",
       "      <td>$27.44M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TSLY</td>\n",
       "      <td>YieldMax TSLA Option Income Strategy ETF</td>\n",
       "      <td>Equity: U.S. Automobile Manufacturers</td>\n",
       "      <td>Tidal</td>\n",
       "      <td>0.99%</td>\n",
       "      <td>$825.16K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OARK</td>\n",
       "      <td>YieldMax Innovation Option Income Strategy ETF</td>\n",
       "      <td>Equity: Global Broad Thematic</td>\n",
       "      <td>Tidal</td>\n",
       "      <td>0.99%</td>\n",
       "      <td>$456.33K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HYLB</td>\n",
       "      <td>Xtrackers USD High Yield Corporate Bond ETF</td>\n",
       "      <td>Fixed Income: U.S. - Corporate, Broad-based Hi...</td>\n",
       "      <td>DWS</td>\n",
       "      <td>0.15%</td>\n",
       "      <td>$4.02B</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>PFLD</td>\n",
       "      <td>AAM Low Duration Preferred and Income Securiti...</td>\n",
       "      <td>Fixed Income: U.S. - Corporate, Preferred Shor...</td>\n",
       "      <td>Advisors Asset Management</td>\n",
       "      <td>0.45%</td>\n",
       "      <td>$199.35M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SMIG</td>\n",
       "      <td>AAM Bahl &amp; Gaynor Small/Mid Cap Income Growth ETF</td>\n",
       "      <td>Equity: U.S. - Extended Market</td>\n",
       "      <td>Advisors Asset Management</td>\n",
       "      <td>0.60%</td>\n",
       "      <td>$185.02M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PRNT</td>\n",
       "      <td>3D Printing ETF</td>\n",
       "      <td>Equity: Global Robotics &amp; AI</td>\n",
       "      <td>ARK</td>\n",
       "      <td>0.66%</td>\n",
       "      <td>$191.27M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>EGIS</td>\n",
       "      <td>2ndVote Society Defended ETF</td>\n",
       "      <td>Equity: U.S. - Total Market</td>\n",
       "      <td>2nd Vote Value Investments, Inc</td>\n",
       "      <td>0.75%</td>\n",
       "      <td>$25.81M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>LYFE</td>\n",
       "      <td>2ndVote Life Neutral Plus ETF</td>\n",
       "      <td>Equity: U.S. - Total Market</td>\n",
       "      <td>2nd Vote Value Investments, Inc</td>\n",
       "      <td>0.75%</td>\n",
       "      <td>$18.30M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3113 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Ticker                                               Name  \\\n",
       "0    ZHDG                             ZEGA Buy and Hedge ETF   \n",
       "1    ZECP            Zacks Earnings Consistent Portfolio ETF   \n",
       "2    TSLY           YieldMax TSLA Option Income Strategy ETF   \n",
       "3    OARK     YieldMax Innovation Option Income Strategy ETF   \n",
       "4    HYLB        Xtrackers USD High Yield Corporate Bond ETF   \n",
       "..    ...                                                ...   \n",
       "8    PFLD  AAM Low Duration Preferred and Income Securiti...   \n",
       "9    SMIG  AAM Bahl & Gaynor Small/Mid Cap Income Growth ETF   \n",
       "10   PRNT                                    3D Printing ETF   \n",
       "11   EGIS                       2ndVote Society Defended ETF   \n",
       "12   LYFE                      2ndVote Life Neutral Plus ETF   \n",
       "\n",
       "                                              Segment  \\\n",
       "0               Asset Allocation: U.S. Target Outcome   \n",
       "1                         Equity: U.S. - Total Market   \n",
       "2               Equity: U.S. Automobile Manufacturers   \n",
       "3                       Equity: Global Broad Thematic   \n",
       "4   Fixed Income: U.S. - Corporate, Broad-based Hi...   \n",
       "..                                                ...   \n",
       "8   Fixed Income: U.S. - Corporate, Preferred Shor...   \n",
       "9                      Equity: U.S. - Extended Market   \n",
       "10                       Equity: Global Robotics & AI   \n",
       "11                        Equity: U.S. - Total Market   \n",
       "12                        Equity: U.S. - Total Market   \n",
       "\n",
       "                             Issuer Expense Ratio       AUM  \n",
       "0                             Tidal         0.98%   $56.76M  \n",
       "1                             Zacks         0.55%   $27.44M  \n",
       "2                             Tidal         0.99%  $825.16K  \n",
       "3                             Tidal         0.99%  $456.33K  \n",
       "4                               DWS         0.15%    $4.02B  \n",
       "..                              ...           ...       ...  \n",
       "8         Advisors Asset Management         0.45%  $199.35M  \n",
       "9         Advisors Asset Management         0.60%  $185.02M  \n",
       "10                              ARK         0.66%  $191.27M  \n",
       "11  2nd Vote Value Investments, Inc         0.75%   $25.81M  \n",
       "12  2nd Vote Value Investments, Inc         0.75%   $18.30M  \n",
       "\n",
       "[3113 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "lista_de_tabela_por_pagina = []\n",
    "\n",
    "for pagina in range(0, numero_paginas):\n",
    "    \n",
    "    tabela = driver.find_element(\"xpath\", '''/html/body/div[4]/section/div/\n",
    "    div[3]/section/div/div/div/\n",
    "    div/div[2]/section[2]/div[2]/div/table''')\n",
    "\n",
    "    html_tabela = tabela.get_attribute(\"outerHTML\")\n",
    "\n",
    "    tabela_final = pd.read_html(html_tabela)[0]\n",
    "    \n",
    "    lista_de_tabela_por_pagina.append(tabela_final)\n",
    "    \n",
    "    botao_avancar_pagina = driver.find_element(\"xpath\", '//*[@id=\"nextPage\"]')\n",
    "    \n",
    "    driver.execute_script(\"arguments[0].click();\", botao_avancar_pagina)\n",
    "    \n",
    "\n",
    "base_de_dados_completa = pd.concat(lista_de_tabela_por_pagina)\n",
    "\n",
    "display(base_de_dados_completa)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa8720e5",
   "metadata": {},
   "source": [
    "# Passo 6.2: Ler a tabela de dados - Lendo a tabela de dados de rentabilidade."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "843de731",
   "metadata": {},
   "outputs": [],
   "source": [
    "#primeira coisa é mudar a aba\n",
    "\n",
    "botao_aba = driver.find_element(\"xpath\", ''' /html/body/div[4]/\n",
    "section/div/div[3]/section/\n",
    "div/div/div/div/div[2]/section[2]/div[2]/ul/li[2]/span''')\n",
    "\n",
    "driver.execute_script(\"arguments[0].click();\", botao_aba)\n",
    "\n",
    "#voltando a 1 página\n",
    "\n",
    "for pagina in range(0, numero_paginas):\n",
    "    \n",
    "    botao_voltar_pagina = driver.find_element(\"xpath\", '//*[@id=\"previousPage\"]')\n",
    "    \n",
    "    driver.execute_script(\"arguments[0].click();\", botao_voltar_pagina)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "d13e5f58",
   "metadata": {
    "scrolled": true
   },
   "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>Ticker</th>\n",
       "      <th>Name</th>\n",
       "      <th>1 Month</th>\n",
       "      <th>3 Month</th>\n",
       "      <th>YTD</th>\n",
       "      <th>1 Year</th>\n",
       "      <th>3 Years</th>\n",
       "      <th>5 Years</th>\n",
       "      <th>10 Years</th>\n",
       "      <th>As Of Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PALL</td>\n",
       "      <td>Aberdeen Standard Physical Palladium Shares ETF</td>\n",
       "      <td>-8.78%</td>\n",
       "      <td>-13.10%</td>\n",
       "      <td>-9.22%</td>\n",
       "      <td>-29.25%</td>\n",
       "      <td>-11.80%</td>\n",
       "      <td>9.99%</td>\n",
       "      <td>7.30%</td>\n",
       "      <td>02/09/23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PPLT</td>\n",
       "      <td>abdrn Physical Platinum Shares ETF</td>\n",
       "      <td>-11.73%</td>\n",
       "      <td>-3.35%</td>\n",
       "      <td>-11.05%</td>\n",
       "      <td>-8.34%</td>\n",
       "      <td>-1.06%</td>\n",
       "      <td>-0.76%</td>\n",
       "      <td>-6.26%</td>\n",
       "      <td>02/09/23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>YEAR</td>\n",
       "      <td>AB Ultra Short Income ETF</td>\n",
       "      <td>0.16%</td>\n",
       "      <td>1.06%</td>\n",
       "      <td>0.36%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>02/09/23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TAFI</td>\n",
       "      <td>AB Tax-Aware Short Duration Municipal ETF</td>\n",
       "      <td>0.32%</td>\n",
       "      <td>2.36%</td>\n",
       "      <td>0.68%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>02/09/23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TRFM</td>\n",
       "      <td>AAM Transformers ETF</td>\n",
       "      <td>13.78%</td>\n",
       "      <td>23.64%</td>\n",
       "      <td>17.66%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>02/09/23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Ticker                                             Name  1 Month  3 Month  \\\n",
       "0   PALL  Aberdeen Standard Physical Palladium Shares ETF   -8.78%  -13.10%   \n",
       "1   PPLT               abdrn Physical Platinum Shares ETF  -11.73%   -3.35%   \n",
       "2   YEAR                        AB Ultra Short Income ETF    0.16%    1.06%   \n",
       "3   TAFI        AB Tax-Aware Short Duration Municipal ETF    0.32%    2.36%   \n",
       "4   TRFM                             AAM Transformers ETF   13.78%   23.64%   \n",
       "\n",
       "       YTD   1 Year  3 Years 5 Years 10 Years As Of Date  \n",
       "0   -9.22%  -29.25%  -11.80%   9.99%    7.30%   02/09/23  \n",
       "1  -11.05%   -8.34%   -1.06%  -0.76%   -6.26%   02/09/23  \n",
       "2    0.36%       --       --      --       --   02/09/23  \n",
       "3    0.68%       --       --      --       --   02/09/23  \n",
       "4   17.66%       --       --      --       --   02/09/23  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "lista_de_tabela_por_pagina = []\n",
    "\n",
    "for pagina in range(0, numero_paginas):\n",
    "    \n",
    "    tabela = driver.find_element(\"xpath\", '''/html/body/div[4]/section/div/\n",
    "    div[3]/section/div/div/div/\n",
    "    div/div[2]/section[2]/div[2]/div/table''')\n",
    "\n",
    "    html_tabela = tabela.get_attribute(\"outerHTML\")\n",
    "\n",
    "    tabela_final = pd.read_html(html_tabela)[0]\n",
    "    \n",
    "    lista_de_tabela_por_pagina.append(tabela_final)\n",
    "    \n",
    "    botao_avancar_pagina = driver.find_element(\"xpath\", '//*[@id=\"nextPage\"]')\n",
    "    \n",
    "    driver.execute_script(\"arguments[0].click();\", botao_avancar_pagina)\n",
    "    \n",
    "\n",
    "base_de_dados_performance = pd.concat(lista_de_tabela_por_pagina)\n",
    "\n",
    "display(base_de_dados_performance.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "d9861094",
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "019300db",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_de_dados_performance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d1ee3c1",
   "metadata": {},
   "source": [
    "# Passo 7: Construindo a tabela final."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "55a90b4d",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"None of ['Ticker'] are in the columns\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-42-3137b7861eeb>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mbase_de_dados_completa\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mbase_de_dados_completa\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Ticker\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mbase_de_dados_completa\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/util/_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    329\u001b[0m                     \u001b[0mstacklevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mfind_stack_level\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    330\u001b[0m                 )\n\u001b[0;32m--> 331\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    332\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    333\u001b[0m         \u001b[0;31m# error: \"Callable[[VarArg(Any), KwArg(Any)], Any]\" has no\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mset_index\u001b[0;34m(self, keys, drop, append, inplace, verify_integrity)\u001b[0m\n\u001b[1;32m   6010\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6011\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mmissing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 6012\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"None of {missing} are in the columns\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   6013\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6014\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0minplace\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: \"None of ['Ticker'] are in the columns\""
     ]
    }
   ],
   "source": [
    "base_de_dados_completa = base_de_dados_completa.set_index(\"Ticker\")\n",
    "\n",
    "base_de_dados_completa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "be882412",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"None of ['Ticker'] are in the columns\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-41-494686a647c8>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mbase_de_dados_performance\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mbase_de_dados_performance\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Ticker\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mbase_de_dados_performance\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mbase_de_dados_performance\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'1 Year'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'5 Years'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'10 Years'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mbase_de_dados_performance\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/util/_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    329\u001b[0m                     \u001b[0mstacklevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mfind_stack_level\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    330\u001b[0m                 )\n\u001b[0;32m--> 331\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    332\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    333\u001b[0m         \u001b[0;31m# error: \"Callable[[VarArg(Any), KwArg(Any)], Any]\" has no\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mset_index\u001b[0;34m(self, keys, drop, append, inplace, verify_integrity)\u001b[0m\n\u001b[1;32m   6010\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6011\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mmissing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 6012\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"None of {missing} are in the columns\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   6013\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6014\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0minplace\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: \"None of ['Ticker'] are in the columns\""
     ]
    }
   ],
   "source": [
    "base_de_dados_performance = base_de_dados_performance.set_index(\"Ticker\")\n",
    "base_de_dados_performance = base_de_dados_performance[['1 Year', '5 Years', '10 Years']]\n",
    "\n",
    "base_de_dados_performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "17f94b1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_de_dados_final = base_de_dados_completa.join(base_de_dados_performance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "f5aacfb5",
   "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>Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Issuer</th>\n",
       "      <th>Expense Ratio</th>\n",
       "      <th>AUM</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LYFE</th>\n",
       "      <td>2ndVote Life Neutral Plus ETF</td>\n",
       "      <td>Equity: U.S. - Total Market</td>\n",
       "      <td>2nd Vote Value Investments, Inc</td>\n",
       "      <td>0.75%</td>\n",
       "      <td>$17.54M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EGIS</th>\n",
       "      <td>2ndVote Society Defended ETF</td>\n",
       "      <td>Equity: U.S. - Total Market</td>\n",
       "      <td>2nd Vote Value Investments, Inc</td>\n",
       "      <td>0.75%</td>\n",
       "      <td>$26.10M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRNT</th>\n",
       "      <td>3D Printing ETF</td>\n",
       "      <td>Equity: Global Robotics &amp; AI</td>\n",
       "      <td>ARK</td>\n",
       "      <td>0.66%</td>\n",
       "      <td>$187.71M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SMIG</th>\n",
       "      <td>AAM Bahl &amp; Gaynor Small/Mid Cap Income Growth ETF</td>\n",
       "      <td>Equity: U.S. - Extended Market</td>\n",
       "      <td>Advisors Asset Management</td>\n",
       "      <td>0.60%</td>\n",
       "      <td>$173.39M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PFLD</th>\n",
       "      <td>AAM Low Duration Preferred and Income Securiti...</td>\n",
       "      <td>Fixed Income: U.S. - Corporate, Preferred Shor...</td>\n",
       "      <td>Advisors Asset Management</td>\n",
       "      <td>0.45%</td>\n",
       "      <td>$197.08M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HYLB</th>\n",
       "      <td>Xtrackers USD High Yield Corporate Bond ETF</td>\n",
       "      <td>Fixed Income: U.S. - Corporate, Broad-based Hi...</td>\n",
       "      <td>DWS</td>\n",
       "      <td>0.15%</td>\n",
       "      <td>$4.14B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OARK</th>\n",
       "      <td>YieldMax Innovation Option Income Strategy ETF</td>\n",
       "      <td>Equity: Global Broad Thematic</td>\n",
       "      <td>Tidal</td>\n",
       "      <td>0.99%</td>\n",
       "      <td>$483.93K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLY</th>\n",
       "      <td>YieldMax TSLA Option Income Strategy ETF</td>\n",
       "      <td>Equity: U.S. Automobile Manufacturers</td>\n",
       "      <td>Tidal</td>\n",
       "      <td>0.99%</td>\n",
       "      <td>$386.59K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZECP</th>\n",
       "      <td>Zacks Earnings Consistent Portfolio ETF</td>\n",
       "      <td>Equity: U.S. - Total Market</td>\n",
       "      <td>Zacks</td>\n",
       "      <td>0.55%</td>\n",
       "      <td>$21.24M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZHDG</th>\n",
       "      <td>ZEGA Buy and Hedge ETF</td>\n",
       "      <td>Asset Allocation: U.S. Target Outcome</td>\n",
       "      <td>Tidal</td>\n",
       "      <td>0.98%</td>\n",
       "      <td>$55.59M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3090 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Name  \\\n",
       "Ticker                                                      \n",
       "LYFE                        2ndVote Life Neutral Plus ETF   \n",
       "EGIS                         2ndVote Society Defended ETF   \n",
       "PRNT                                      3D Printing ETF   \n",
       "SMIG    AAM Bahl & Gaynor Small/Mid Cap Income Growth ETF   \n",
       "PFLD    AAM Low Duration Preferred and Income Securiti...   \n",
       "...                                                   ...   \n",
       "HYLB          Xtrackers USD High Yield Corporate Bond ETF   \n",
       "OARK       YieldMax Innovation Option Income Strategy ETF   \n",
       "TSLY             YieldMax TSLA Option Income Strategy ETF   \n",
       "ZECP              Zacks Earnings Consistent Portfolio ETF   \n",
       "ZHDG                               ZEGA Buy and Hedge ETF   \n",
       "\n",
       "                                                  Segment  \\\n",
       "Ticker                                                      \n",
       "LYFE                          Equity: U.S. - Total Market   \n",
       "EGIS                          Equity: U.S. - Total Market   \n",
       "PRNT                         Equity: Global Robotics & AI   \n",
       "SMIG                       Equity: U.S. - Extended Market   \n",
       "PFLD    Fixed Income: U.S. - Corporate, Preferred Shor...   \n",
       "...                                                   ...   \n",
       "HYLB    Fixed Income: U.S. - Corporate, Broad-based Hi...   \n",
       "OARK                        Equity: Global Broad Thematic   \n",
       "TSLY                Equity: U.S. Automobile Manufacturers   \n",
       "ZECP                          Equity: U.S. - Total Market   \n",
       "ZHDG                Asset Allocation: U.S. Target Outcome   \n",
       "\n",
       "                                 Issuer Expense Ratio       AUM  \n",
       "Ticker                                                           \n",
       "LYFE    2nd Vote Value Investments, Inc         0.75%   $17.54M  \n",
       "EGIS    2nd Vote Value Investments, Inc         0.75%   $26.10M  \n",
       "PRNT                                ARK         0.66%  $187.71M  \n",
       "SMIG          Advisors Asset Management         0.60%  $173.39M  \n",
       "PFLD          Advisors Asset Management         0.45%  $197.08M  \n",
       "...                                 ...           ...       ...  \n",
       "HYLB                                DWS         0.15%    $4.14B  \n",
       "OARK                              Tidal         0.99%  $483.93K  \n",
       "TSLY                              Tidal         0.99%  $386.59K  \n",
       "ZECP                              Zacks         0.55%   $21.24M  \n",
       "ZHDG                              Tidal         0.98%   $55.59M  \n",
       "\n",
       "[3090 rows x 5 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>1 Year</th>\n",
       "      <th>5 Years</th>\n",
       "      <th>10 Years</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LYFE</th>\n",
       "      <td>-1.80%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EGIS</th>\n",
       "      <td>2.38%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRNT</th>\n",
       "      <td>-28.09%</td>\n",
       "      <td>-3.51%</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SMIG</th>\n",
       "      <td>-3.34%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PFLD</th>\n",
       "      <td>-8.24%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HYLB</th>\n",
       "      <td>-5.99%</td>\n",
       "      <td>2.33%</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OARK</th>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLY</th>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZECP</th>\n",
       "      <td>-6.60%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZHDG</th>\n",
       "      <td>-16.54%</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3090 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         1 Year 5 Years 10 Years\n",
       "Ticker                          \n",
       "LYFE     -1.80%      --       --\n",
       "EGIS      2.38%      --       --\n",
       "PRNT    -28.09%  -3.51%       --\n",
       "SMIG     -3.34%      --       --\n",
       "PFLD     -8.24%      --       --\n",
       "...         ...     ...      ...\n",
       "HYLB     -5.99%   2.33%       --\n",
       "OARK         --      --       --\n",
       "TSLY         --      --       --\n",
       "ZECP     -6.60%      --       --\n",
       "ZHDG    -16.54%      --       --\n",
       "\n",
       "[3090 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(base_de_dados_completa)\n",
    "display(base_de_dados_performance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a5125e1a",
   "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>Name</th>\n",
       "      <th>Segment</th>\n",
       "      <th>Issuer</th>\n",
       "      <th>Expense Ratio</th>\n",
       "      <th>AUM</th>\n",
       "      <th>1 Year</th>\n",
       "      <th>5 Years</th>\n",
       "      <th>10 Years</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ticker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAA</th>\n",
       "      <td>AXS First Priority CLO Bond ETF</td>\n",
       "      <td>Fixed Income: U.S. - Corporate, Bank Loans Inv...</td>\n",
       "      <td>AXS Investments</td>\n",
       "      <td>0.25%</td>\n",
       "      <td>$7.40M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAAU</th>\n",
       "      <td>Goldman Sachs Physical Gold ETF</td>\n",
       "      <td>Commodities: Precious Metals Gold</td>\n",
       "      <td>Goldman Sachs</td>\n",
       "      <td>0.18%</td>\n",
       "      <td>$605.89M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AADR</th>\n",
       "      <td>AdvisorShares Dorsey Wright ADR ETF</td>\n",
       "      <td>Equity: Global Ex-U.S. - Total Market</td>\n",
       "      <td>AdvisorShares</td>\n",
       "      <td>1.00%</td>\n",
       "      <td>$29.54M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPB</th>\n",
       "      <td>GraniteShares 1.75x Long AAPL Daily ETF</td>\n",
       "      <td>Leveraged Equity: U.S. Technology Hardware Sto...</td>\n",
       "      <td>GraniteShares</td>\n",
       "      <td>1.15%</td>\n",
       "      <td>$998.02K</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AAPD</th>\n",
       "      <td>Direxion Daily AAPL Bear 1X Shares</td>\n",
       "      <td>Inverse Equity: U.S. Technology Hardware Stora...</td>\n",
       "      <td>Direxion</td>\n",
       "      <td>0.97%</td>\n",
       "      <td>$22.58M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZIG</th>\n",
       "      <td>The Acquirers Fund</td>\n",
       "      <td>Equity: U.S. - Large Cap Value</td>\n",
       "      <td>ETF Series Solutions</td>\n",
       "      <td>0.89%</td>\n",
       "      <td>$39.03M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZROZ</th>\n",
       "      <td>PIMCO 25+ Year Zero Coupon US Treasury Index ETF</td>\n",
       "      <td>Fixed Income: U.S. - Government, Treasury Inve...</td>\n",
       "      <td>PIMCO</td>\n",
       "      <td>0.15%</td>\n",
       "      <td>$822.00M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZSB</th>\n",
       "      <td>USCF Sustainable Battery Metals Strategy Fund</td>\n",
       "      <td>Commodities: Broad Market Metals</td>\n",
       "      <td>Marygold</td>\n",
       "      <td>0.59%</td>\n",
       "      <td>$2.54M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZSL</th>\n",
       "      <td>ProShares UltraShort Silver</td>\n",
       "      <td>Inverse Commodities: Precious Metals Silver</td>\n",
       "      <td>ProShares</td>\n",
       "      <td>0.95%</td>\n",
       "      <td>$13.64M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZSPY</th>\n",
       "      <td>ASYMmetric Smart Alpha S&amp;P 500 ETF</td>\n",
       "      <td>Equity: U.S. - Large Cap</td>\n",
       "      <td>ASYMmetric ETFs</td>\n",
       "      <td>0.95%</td>\n",
       "      <td>$755.34K</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3516 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Name  \\\n",
       "Ticker                                                     \n",
       "AAA                      AXS First Priority CLO Bond ETF   \n",
       "AAAU                     Goldman Sachs Physical Gold ETF   \n",
       "AADR                 AdvisorShares Dorsey Wright ADR ETF   \n",
       "AAPB             GraniteShares 1.75x Long AAPL Daily ETF   \n",
       "AAPD                  Direxion Daily AAPL Bear 1X Shares   \n",
       "...                                                  ...   \n",
       "ZIG                                   The Acquirers Fund   \n",
       "ZROZ    PIMCO 25+ Year Zero Coupon US Treasury Index ETF   \n",
       "ZSB        USCF Sustainable Battery Metals Strategy Fund   \n",
       "ZSL                          ProShares UltraShort Silver   \n",
       "ZSPY                  ASYMmetric Smart Alpha S&P 500 ETF   \n",
       "\n",
       "                                                  Segment  \\\n",
       "Ticker                                                      \n",
       "AAA     Fixed Income: U.S. - Corporate, Bank Loans Inv...   \n",
       "AAAU                    Commodities: Precious Metals Gold   \n",
       "AADR                Equity: Global Ex-U.S. - Total Market   \n",
       "AAPB    Leveraged Equity: U.S. Technology Hardware Sto...   \n",
       "AAPD    Inverse Equity: U.S. Technology Hardware Stora...   \n",
       "...                                                   ...   \n",
       "ZIG                        Equity: U.S. - Large Cap Value   \n",
       "ZROZ    Fixed Income: U.S. - Government, Treasury Inve...   \n",
       "ZSB                      Commodities: Broad Market Metals   \n",
       "ZSL           Inverse Commodities: Precious Metals Silver   \n",
       "ZSPY                             Equity: U.S. - Large Cap   \n",
       "\n",
       "                      Issuer Expense Ratio       AUM 1 Year 5 Years 10 Years  \n",
       "Ticker                                                                        \n",
       "AAA          AXS Investments         0.25%    $7.40M    NaN     NaN      NaN  \n",
       "AAAU           Goldman Sachs         0.18%  $605.89M    NaN     NaN      NaN  \n",
       "AADR           AdvisorShares         1.00%   $29.54M    NaN     NaN      NaN  \n",
       "AAPB           GraniteShares         1.15%  $998.02K    NaN     NaN      NaN  \n",
       "AAPD                Direxion         0.97%   $22.58M    NaN     NaN      NaN  \n",
       "...                      ...           ...       ...    ...     ...      ...  \n",
       "ZIG     ETF Series Solutions         0.89%   $39.03M    NaN     NaN      NaN  \n",
       "ZROZ                   PIMCO         0.15%  $822.00M    NaN     NaN      NaN  \n",
       "ZSB                 Marygold         0.59%    $2.54M    NaN     NaN      NaN  \n",
       "ZSL                ProShares         0.95%   $13.64M    NaN     NaN      NaN  \n",
       "ZSPY         ASYMmetric ETFs         0.95%  $755.34K    NaN     NaN      NaN  \n",
       "\n",
       "[3516 rows x 8 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_de_dados_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8981d23d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "540b9665",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e458b16",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c03e0e54",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6311367",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "684fefca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e03ecdb1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cab3151c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e465e581",
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "import time\n",
    "import pandas as pd\n",
    "\n",
    "driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()))\n",
    "\n",
    "url = 'https://www.etf.com/etfanalytics/etf-finder'\n",
    "\n",
    "driver.get(url)\n",
    "\n",
    "time.sleep(5)\n",
    "\n",
    "cem_resultados = driver.find_element(\"xpath\", \n",
    "                    '''/html/body/div[5]/section/div/div[3]/section/div/\n",
    "                    div/div/div/div[2]/section[2]/div[2]/section[2]/div[1]/div/div[4]/button/label/span''')\n",
    "\n",
    "driver.execute_script(\"arguments[0].click();\", cem_resultados)\n",
    "\n",
    "time.sleep(2)\n",
    "\n",
    "numero_paginas = driver.find_element(\"xpath\", '''//*[@id=\"totalPages\"]''')\n",
    "\n",
    "numero_paginas = numero_paginas.text.replace(\"of \", \"\")\n",
    "\n",
    "numero_paginas = int(numero_paginas)\n",
    "\n",
    "lista_data_frames_por_pagina = []\n",
    "\n",
    "for pagina in range(0, numero_paginas):\n",
    "\n",
    "    local_tabela = '''\n",
    "    //*[@id=\"finderTable\"]\n",
    "    '''\n",
    "\n",
    "    elemento = driver.find_element(\"xpath\", local_tabela)\n",
    "\n",
    "    html_tabela = elemento.get_attribute('outerHTML')\n",
    "\n",
    "    tabela = pd.read_html(html_tabela)[0]\n",
    "    \n",
    "    lista_data_frames_por_pagina.append(tabela)\n",
    "    \n",
    "    if pagina != numero_paginas:\n",
    "    \n",
    "        botao_avancar_pag = driver.find_element(\"xpath\", '//*[@id=\"nextPage\"]')\n",
    "\n",
    "        driver.execute_script(\"arguments[0].click();\", botao_avancar_pag)\n",
    "\n",
    "\n",
    "    \n",
    "\n",
    "base_dados_etf_cadastro = pd.concat(lista_data_frames_por_pagina)\n",
    "\n",
    "#voltando tudo\n",
    "\n",
    "for pagina in range(0, numero_paginas):\n",
    "\n",
    "    botao_voltar_pag = driver.find_element(\"xpath\", '//*[@id=\"previousPage\"]')\n",
    "\n",
    "    driver.execute_script(\"arguments[0].click();\", botao_voltar_pag)\n",
    "\n",
    "# a unica coisa que muda é esse botão\n",
    "botao_dados_perf = driver.find_element(\"xpath\", '/html/body/div[5]/section/div/div[3]/section/div/div/div/div/div[2]/section[2]/div[2]/ul/li[2]/span')\n",
    "\n",
    "\n",
    "# a partir daqui é tudo igual\n",
    "driver.execute_script(\"arguments[0].click();\", botao_dados_perf)\n",
    "\n",
    "lista_data_frames_por_pagina = []\n",
    "\n",
    "for pagina in range(0, numero_paginas):\n",
    "\n",
    "\n",
    "    local_tabela = '''\n",
    "    //*[@id=\"finderTable\"]\n",
    "    '''\n",
    "\n",
    "    elemento = driver.find_element(\"xpath\", local_tabela)\n",
    "\n",
    "    html_tabela = elemento.get_attribute('outerHTML')\n",
    "\n",
    "    tabela = pd.read_html(html_tabela)[0]\n",
    "    \n",
    "    lista_data_frames_por_pagina.append(tabela)\n",
    "    \n",
    "    if pagina != numero_paginas:\n",
    "    \n",
    "        botao_avancar_pag = driver.find_element(\"xpath\", '//*[@id=\"nextPage\"]')\n",
    "\n",
    "        driver.execute_script(\"arguments[0].click();\", botao_avancar_pag)\n",
    "    \n",
    "\n",
    "base_dados_etf_performance = pd.concat(lista_data_frames_por_pagina)\n",
    "\n",
    "#fechando navegador\n",
    "driver.quit()\n",
    "\n",
    "base_dados_etf_cadastro = base_dados_etf_cadastro.set_index('Ticker')\n",
    "base_dados_etf_performance = base_dados_etf_performance.set_index(\"Ticker\")\n",
    "base_dados_etf_performance = base_dados_etf_performance[['1 Year', '5 Years', '10 Years']]\n",
    "\n",
    "base_dados_final = base_dados_etf_cadastro.join(base_dados_etf_performance, how = \"inner\")\n",
    "\n",
    "base_dados_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57bd3c4b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}