merge_etfs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b43c7098-8608-4fb6-bcd1-e9c7ac1c4ae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import csv\n",
    "\n",
    "NANC_CSV_FILE = \"10-80-NANC-N-PORT-Part-F-123123-V5-Confidential.csv\"\n",
    "KRUZ_CSV_FILE = \"11-81-KRUZ-N-PORT-Part-F-123123-V5-Confidential.pdf-00-48-16-852.csv\"\n",
    "\n",
    "NANC_STOCKS = set()\n",
    "KRUZ_STOCKS = set()\n",
    "with open(NANC_CSV_FILE, newline='') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for row in reader:\n",
    "        number_filter = re.search(\"[0-9]+(\\.[0-9]+)?%\", row[0]) # don't need lines with just with numbers\n",
    "        # print(row)\n",
    "        if not (number_filter) and (\n",
    "                \"Inc\" in row[0] or \n",
    "                \"Corp\" in row[0] or \n",
    "                \"Co\" in row[0] or \n",
    "                \"PLC\" in row[0] or\n",
    "                \"ADR\" in row[0] or\n",
    "                \"Ltd\" in row[0] or\n",
    "                \"AG\" in row[0] or\n",
    "                \"Bancorp\" in row[0]\n",
    "                ):\n",
    "            row[0] = re.sub(r'[\\x00-\\x1f]+', '', row[0]) # cleanup: replaces \\xad or \\xad\\x0c by nothing\n",
    "            if re.search(\" {2,}\", row[0]):\n",
    "                # ['Silgan Holdings, Inc.                                      44           1,991']\n",
    "                # cleanup: for these kind of rows where there's ridiculous amount of spacing due to table view\n",
    "                row[0] = re.sub('[0-9,,,.,§]', '', row[0])\n",
    "                row[0] = row[0].strip()\n",
    "                #print(row)\n",
    "                NANC_STOCKS.add(row[0])\n",
    "            else:\n",
    "                #print(row)\n",
    "                NANC_STOCKS.add(row[0])\n",
    "\n",
    "# print(NANC_STOCKS)\n",
    "\n",
    "with open(KRUZ_CSV_FILE, newline='') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for row in reader:\n",
    "        number_filter = re.search(\"[0-9]+(\\.[0-9]+)?%\", row[0]) # don't need lines with just with numbers\n",
    "        # print(row)\n",
    "        if not (number_filter) and (\n",
    "                \"Inc\" in row[0] or \n",
    "                \"Corp\" in row[0] or \n",
    "                \"Co\" in row[0] or \n",
    "                \"PLC\" in row[0] or\n",
    "                \"ADR\" in row[0] or\n",
    "                \"Ltd\" in row[0] or\n",
    "                \"AG\" in row[0] or\n",
    "                \"Bancorp\" in row[0]\n",
    "                ):\n",
    "            row[0] = re.sub(r'[\\x00-\\x1f]+', '', row[0]) # cleanup: replaces \\xad or \\xad\\x0c by nothing\n",
    "            if re.search(\" {2,}\", row[0]):\n",
    "                # ['Silgan Holdings, Inc.                                      44           1,991']\n",
    "                # cleanup: for these kind of rows where there's ridiculous amount of spacing due to table view\n",
    "                row[0] = re.sub('[0-9,,,.,§]', '', row[0])\n",
    "                row[0] = row[0].strip()\n",
    "                #print(row)\n",
    "                KRUZ_STOCKS.add(row[0])\n",
    "            else:\n",
    "                #print(row)\n",
    "                KRUZ_STOCKS.add(row[0])\n",
    "\n",
    "# print(NANC_STOCKS.intersection(KRUZ_STOCKS))\n",
    "# print(NANC_STOCKS - KRUZ_STOCKS)\n",
    "# print(KRUZ_STOCKS - NANC_STOCKS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e4ce8233-d9f6-4574-8a5a-cb705f8e9017",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "102fe9a0-092c-46f2-836f-a016e1a37be5",
   "metadata": {},
   "outputs": [],
   "source": [
    "BIPARTISAN_STOCKS = list(NANC_STOCKS.intersection(KRUZ_STOCKS))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "26aecfdf-0d43-462c-8f2f-b47f1e159f52",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe = pd.DataFrame(BIPARTISAN_STOCKS, columns=[\"Stocks\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "97fb447c-3615-4754-9a94-ea08fc5b777e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                         Stocks\n",
      "0                          Freeport-McMoRan Inc\n",
      "1                                      CSX Corp\n",
      "2                         Micron Technology Inc\n",
      "3                         Bath & Body Works Inc\n",
      "4                  Citizens Financial Group Inc\n",
      "5                                   Southern Co\n",
      "6                                     Aflac Inc\n",
      "7                           Elevance Health Inc\n",
      "8                                  Boeing Co(a)\n",
      "9                           Global Payments Inc\n",
      "10                                   AbbVie Inc\n",
      "11                                   AMETEK Inc\n",
      "12            United Airlines Holdings, Inc.(a)\n",
      "13                 Reliance Steel & Aluminum Co\n",
      "14             PNC Financial Services Group Inc\n",
      "15                             Wells Fargo & Co\n",
      "16                       UnitedHealth Group Inc\n",
      "17         International Business Machines Corp\n",
      "18                                   NetApp Inc\n",
      "19                    Intuitive Surgical Inc(a)\n",
      "20                          HealthEquity Inc(a)\n",
      "21                     Agilent Technologies Inc\n",
      "22                        Sony Group Corp - ADR\n",
      "23             Constellation Brands Inc Class A\n",
      "24                        Applied Materials Inc\n",
      "25                                    IDEX Corp\n",
      "26                             Zions Bancorp NA\n",
      "27                             Medtronic PLC(b)\n",
      "28                            Novartis AG - ADR\n",
      "29                          BHP Group Ltd - ADR\n",
      "30                      Tyson Foods Inc Class A\n",
      "31                Vertex Pharmaceuticals Inc(a)\n",
      "32                       Equitable Holdings Inc\n",
      "33                              T-Mobile US Inc\n",
      "34                                     AT&T Inc\n",
      "35                        Amphenol Corp Class A\n",
      "36                             Visa Inc Class A\n",
      "37    Fresenius Medical Care AG & Co KGaA - ADR\n",
      "38                                   Sysco Corp\n",
      "39                              Autodesk Inc(a)\n",
      "40                       PayPal Holdings Inc(a)\n",
      "41                          DENTSPLY SIRONA Inc\n",
      "42        Charter Communications Inc Class A(a)\n",
      "43                       Southwest Airlines Co.\n",
      "44                     Simon Property Group Inc\n",
      "45                Cadence Design Systems Inc(a)\n",
      "46                          Quanta Services Inc\n",
      "47                          Sun Communities Inc\n",
      "48                               Royal Gold Inc\n",
      "49                 Qurate Retail Inc Class A(a)\n",
      "50                                   PACCAR Inc\n",
      "51                 Bank of New York Mellon Corp\n",
      "52             Estee Lauder Cos., Inc., Class A\n",
      "53                                  Paychex Inc\n",
      "54                         Keurig Dr Pepper Inc\n",
      "55                          Dominion Energy Inc\n",
      "56                        Texas Instruments Inc\n",
      "57       Restaurant Brands International Inc(b)\n",
      "58                            Iron Mountain Inc\n",
      "59              Philip Morris International Inc\n",
      "60                Intercontinental Exchange Inc\n",
      "61                           Conagra Brands Inc\n",
      "62                           PPG Industries Inc\n",
      "63                       Motorola Solutions Inc\n",
      "64                                 Block Inc(a)\n",
      "65     Zoom Video Communications Inc Class A(a)\n",
      "66                            Cisco Systems Inc\n",
      "67                                 Chevron Corp\n",
      "68                                       HP Inc\n",
      "69                             Altria Group Inc\n",
      "70                      Cboe Global Markets Inc\n",
      "71                            Plug Power Inc(a)\n",
      "72                Automatic Data Processing Inc\n",
      "73                          Avery Dennison Corp\n",
      "74                                  NVIDIA Corp\n",
      "75                        Stifel Financial Corp\n",
      "76                  Clearway Energy Inc Class A\n",
      "77                               Starbucks Corp\n",
      "78                           Regal Rexnord Corp\n",
      "79                                Allstate Corp\n",
      "80                       Mastercard Inc Class A\n",
      "81                 Goodyear Tire & Rubber Co(a)\n",
      "82                                Citigroup Inc\n",
      "83                        IQVIA Holdings Inc(a)\n",
      "84                 Pioneer Natural Resources Co\n",
      "85                                  TJX Cos Inc\n",
      "86                 Warner Bros Discovery Inc(a)\n",
      "87                               ConocoPhillips\n",
      "88                        Becton Dickinson & Co\n",
      "89           Peloton Interactive Inc Class A(a)\n",
      "90          Crowdstrike Holdings Inc Class A(a)\n",
      "91                                 Broadcom Inc\n",
      "92                         Comcast Corp Class A\n",
      "93                                McKesson Corp\n",
      "94                          EPAM Systems Inc(a)\n",
      "95                                   Zoetis Inc\n",
      "96                         Barrick Gold Corp(b)\n",
      "97                Hilton Worldwide Holdings Inc\n",
      "98                   Zimmer Biomet Holdings Inc\n",
      "99                     Ameriprise Financial Inc\n",
      "100                              Lowe's Cos Inc\n",
      "101                           Lam Research Corp\n",
      "102             Fidelity National Financial Inc\n",
      "103                     Rockwell Automation Inc\n",
      "104              Fortune Brands Innovations Inc\n",
      "105                      Skyworks Solutions Inc\n",
      "106                                   Apple Inc\n",
      "107                         Electronic Arts Inc\n",
      "108                    Uber Technologies Inc(a)\n",
      "109                             Illumina Inc(a)\n",
      "110                  O'Reilly Automotive Inc(a)\n",
      "111                                Tapestry Inc\n",
      "112                                  Nasdag Inc\n",
      "113                Thermo Fisher Scientific Inc\n",
      "114                       Costco Wholesale Corp\n",
      "115            American Airlines Group, Inc.(a)\n",
      "116                                Moody's Corp\n",
      "117                                   Ball Corp\n",
      "118                                 Chemours Co\n",
      "119                 Under Armour Inc Class A(a)\n",
      "120                Edwards Lifesciences Corp(a)\n",
      "121                                 Corning Inc\n",
      "122                     WESCO International Inc\n",
      "123                       Republic Services Inc\n",
      "124              Air Products and Chemicals Inc\n",
      "125                                  FedEx Corp\n",
      "126                                  Intel Corp\n",
      "127                Keysight Technologies Inc(a)\n",
      "128                            Duke Energy Corp\n",
      "129           Berkshire Hathaway Inc Class B(a)\n",
      "130                      Regions Financial Corp\n",
      "131                            Carlisle Cos Inc\n",
      "132                                   Clorox Co\n",
      "133                               CarMax Inc(a)\n",
      "134                                     Dow Inc\n",
      "135                 Honeywell International Inc\n",
      "136                         Otis Worldwide Corp\n",
      "137                             CVS Health Corp\n",
      "138                                     ITT Inc\n",
      "139                                Oshkosh Corp\n",
      "140                                  IAC Inc(a)\n",
      "141                             Eli Lilly & Co.\n",
      "142               Advanced Micro Devices Inc(a)\n",
      "143                                  Ecolab Inc\n",
      "144                      Church & Dwight Co Inc\n",
      "145           United Parcel Service Inc Class B\n",
      "146                       Antero Midstream Corp\n",
      "147               Advance Auto        Parts Inc\n",
      "148                                SAP SE - ADR\n",
      "149                        Jacobs Solutions Inc\n",
      "150                          Ally Financial Inc\n",
      "151                              Walt Disney Co\n",
      "152                                   Kroger Co\n",
      "153                      Synovus Financial Corp\n",
      "154                           Merck & Co., Inc.\n",
      "155                            Crown Castle Inc\n",
      "156                                  Nucor Corp\n",
      "157                      T Rowe Price Group Inc\n",
      "158                    Bristol-Myers Squibb Co.\n",
      "159                       Quest Diagnostics Inc\n",
      "160                    Lennox International Inc\n",
      "161                             CMS Energy Corp\n",
      "162                      Arista Networks Inc(a)\n",
      "163                             Shell PLC - ADR\n",
      "164                    Marsh & McLennan Cos Inc\n",
      "165                         JPMorgan Chase & Co\n",
      "166                Interpublic Group of Cos Inc\n",
      "167                                  Intuit Inc\n",
      "168      Liberty Media Corp-Liberty SiriusXM(a)\n",
      "169                         Paycom Software Inc\n",
      "170                        Bank of America Corp\n",
      "171                                  Deere & Co\n",
      "172                      CenterPoint Energy Inc\n",
      "173                                    KLA Corp\n",
      "174                         Procter & Gamble Co\n",
      "175                             VeriSign Inc(a)\n",
      "176                          Union Pacific Corp\n",
      "177                               Ford Motor Co\n",
      "178                                 Oracle Corp\n",
      "179                 Willis Towers Watson PLC(b)\n",
      "180                              Microsoft Corp\n",
      "181                         American Tower Corp\n",
      "182                           ‘Amazoncom Inc(a)\n",
      "183                          Domino's Pizza Inc\n",
      "184                             Xcel Energy Inc\n",
      "185                         Dollar General Corp\n",
      "186                          Dollar Tree Inc(a)\n",
      "187                  Raytheon Technologies Corp\n",
      "188                             Caterpillar Inc\n",
      "189                              AptarGroup Inc\n",
      "190                         Emerson Electric Co\n",
      "191               Meta Platforms Inc Class A(a)\n",
      "192          Mondelez International Inc Class A\n",
      "193                               Dexcom Inc(a)\n",
      "194                             AutoZone Inc(a)\n",
      "195                              Netflix Inc(a)\n",
      "196       Hartford Financial Services Group Inc\n",
      "197                                Woodward Inc\n",
      "198                            Exxon Mobil Corp\n",
      "199                                     KeyCorp\n",
      "200                       AstraZeneca PLC - ADR\n",
      "201                       Airbnb Inc Class A(a)\n",
      "202                  LPL Financial Holdings Inc\n",
      "203                        Las Vegas Sands Corp\n",
      "204                                KKR & Co Inc\n",
      "205                           ServiceNow Inc(a)\n",
      "206                                     Gap Inc\n",
      "207  Fidelity National Information Services Inc\n",
      "208                         Kimberly-Clark Corp\n",
      "209                            NIKE Inc Class B\n",
      "210                      llinois Tool Works Inc\n",
      "211                         Fifth Third Bancorp\n",
      "212                               BlackRock Inc\n",
      "213                    Accenture PLC Class A(b)\n",
      "214               Old Dominion Freight Line Inc\n",
      "215                             Baker Hughes Co\n",
      "216                      International Paper Co\n",
      "217                                Teradyne Inc\n",
      "218                        Parker-Hannifin Corp\n",
      "219                         Rio Tinto PLC - ADR\n",
      "220                   Palo Alto Networks Inc(a)\n",
      "221                                   ONEOK Inc\n",
      "222                         General Electric Co\n",
      "223                            Progressive Corp\n",
      "224                              S&P Global Inc\n",
      "225              American Electric Power Co Inc\n",
      "226                             Gen Digital Inc\n",
      "227                                 Cummins Inc\n",
      "228                                   Mosaic Co\n",
      "229                                  Hershey Co\n",
      "230                      ROBLOX Corp Class A(a)\n",
      "231                           Kinder Morgan Inc\n",
      "232                                Chubb Ltd(b)\n",
      "233                           Salesforce Inc(a)\n",
      "234                     Cleveland-Cliffs Inc(a)\n",
      "235                                QUALCOMM Inc\n",
      "236                                 Hexcel Corp\n",
      "237                                Tesla Inc(a)\n",
      "238                              Home Depot Inc\n",
      "239                             Fortinet Inc(a)\n",
      "240                             McDonald's Corp\n",
      "241                              PulteGroup Inc\n",
      "242                             Centene Corp(a)\n",
      "243                   Boston Scientific Corp(a)\n",
      "244                             Allegion PLC(b)\n",
      "245                     Goldman Sachs Group Inc\n",
      "246         BJ's Wholesale Club Holdings Inc(a)\n",
      "247                         Gilead Sciences Inc\n",
      "248                                  Dover Corp\n",
      "249                           Omnicom Group Inc\n",
      "250                         Markel Group Inc(a)\n",
      "251                           General Mills Inc\n",
      "252                                 Etsy Inc(a)\n",
      "253                     Alphabet Inc Class C(a)\n",
      "254                         Charles Schwab Corp\n",
      "255                                 Target Corp\n",
      "256                                        M Co\n",
      "257                       Delta Air Lines, Inc.\n",
      "258                  Capital One Financial Corp\n",
      "259                                Coca-Cola Co\n",
      "260                         American Express Co\n",
      "261                        Lockheed Martin Corp\n",
      "262                                Danaher Corp\n",
      "263                          NextEra Energy Inc\n",
      "264                                Adobe Inc(a)\n"
     ]
    }
   ],
   "source": [
    "with pd.option_context('display.max_rows', None,):\n",
    "    print(dataframe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bced89f-20d7-43dc-8768-350cd0f13baa",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}