summaryrefslogtreecommitdiff
path: root/ui_security_inventory_23_parsing.ipynb
diff options
context:
space:
mode:
authorkennykos <“gkosmacher01@gmail.com”>2023-02-15 10:13:40 -0600
committerkennykos <“gkosmacher01@gmail.com”>2023-02-15 10:13:40 -0600
commit7bfaf2fb28d6f7bc6a35b031c29c221eef54dda0 (patch)
treecb37127d0c8b9404e7c1f3f905dd9ca0bd2d0c08 /ui_security_inventory_23_parsing.ipynb
parenteb4132862f19c157289f126e5a071863ebeedc2c (diff)
Pushing Script for UI System
+ Beginning data analysis on University of Illinois System + added .gitignore to specificaly exclude carbon underground data + added data from UI system made avalable via FOIA request
Diffstat (limited to 'ui_security_inventory_23_parsing.ipynb')
-rw-r--r--ui_security_inventory_23_parsing.ipynb941
1 files changed, 941 insertions, 0 deletions
diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb
new file mode 100644
index 0000000..3248cde
--- /dev/null
+++ b/ui_security_inventory_23_parsing.ipynb
@@ -0,0 +1,941 @@
+{
+ "cells": [
+ {
+ "cell_type": "code",
+ "execution_count": 5,
+ "id": "8ce6b023",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import numpy as np\n",
+ "import pandas as pd\n",
+ "import yfinance as yf\n",
+ "# extra dependencies et-xmlfile-1.1.0 openpyxl-3.1.1"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 6,
+ "id": "8304939a",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Sheet1\n"
+ ]
+ }
+ ],
+ "source": [
+ "file_path_23 = \"./data/23-138.Records.xlsx\"\n",
+ " # skip header rows so column names align, drop all NaN rows\n",
+ "df = pd.read_excel(file_path_23, parse_dates=True, skiprows=6).dropna(how='all')"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "538233a8",
+ "metadata": {},
+ "source": [
+ "#### Setting up the Operating Pool DataFrame "
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 7,
+ "id": "96dee5bc",
+ "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>Account or Security</th>\n",
+ " <th>Coupon</th>\n",
+ " <th>Date</th>\n",
+ " <th>Quantity</th>\n",
+ " <th>Cost Value</th>\n",
+ " <th>Market Value</th>\n",
+ " <th>Bank</th>\n",
+ " <th>Asset Type</th>\n",
+ " <th>Company</th>\n",
+ " <th>Industry</th>\n",
+ " <th>Private Placement</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>36</th>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>37</th>\n",
+ " <td>Asset-Backed Securities</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>38</th>\n",
+ " <td>CHASE FDG MTG LN 2004-1 MTG LN</td>\n",
+ " <td>2.08</td>\n",
+ " <td>1933-12-25 00:00:00</td>\n",
+ " <td>6462.53</td>\n",
+ " <td>6462.53</td>\n",
+ " <td>6068.88</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>39</th>\n",
+ " <td>MVW 2021-2 LLC TIMESHARE LN BK</td>\n",
+ " <td>1.43</td>\n",
+ " <td>1939-05-20 00:00:00</td>\n",
+ " <td>737880.25</td>\n",
+ " <td>737679.54</td>\n",
+ " <td>672033.22</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>40</th>\n",
+ " <td>PFS FING CORP .77% 08-15-2026</td>\n",
+ " <td>0.77</td>\n",
+ " <td>2026-08-15 00:00:00</td>\n",
+ " <td>1090000.00</td>\n",
+ " <td>1089872.25</td>\n",
+ " <td>1010524.50</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "text/plain": [
+ " Account or Security Coupon Date Quantity \\\n",
+ "36 9-200100-Neuberger Intermediate NaN NaN NaN \n",
+ "37 Asset-Backed Securities NaN NaN NaN \n",
+ "38 CHASE FDG MTG LN 2004-1 MTG LN 2.08 1933-12-25 00:00:00 6462.53 \n",
+ "39 MVW 2021-2 LLC TIMESHARE LN BK 1.43 1939-05-20 00:00:00 737880.25 \n",
+ "40 PFS FING CORP .77% 08-15-2026  0.77 2026-08-15 00:00:00 1090000.00 \n",
+ "\n",
+ " Cost Value Market Value Bank Asset Type Company Industry \\\n",
+ "36 NaN NaN <NA> <NA> <NA> <NA> \n",
+ "37 NaN NaN <NA> <NA> <NA> <NA> \n",
+ "38 6462.53 6068.88 <NA> <NA> <NA> <NA> \n",
+ "39 737679.54 672033.22 <NA> <NA> <NA> <NA> \n",
+ "40 1089872.25 1010524.50 <NA> <NA> <NA> <NA> \n",
+ "\n",
+ " Private Placement \n",
+ "36 False \n",
+ "37 False \n",
+ "38 False \n",
+ "39 False \n",
+ "40 False "
+ ]
+ },
+ "execution_count": 7,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "bank_i = df[df['Account or Security'].str.contains(\"9-200100\", na=False)].index\n",
+ "op_i = df[df['Account or Security'].str.contains(\"Operating Funds Pool\", na=False)].index\n",
+ "op_df = df.loc[bank_i[0]:op_i[1]-1]\n",
+ "op_df.insert(6, 'Bank', pd.NA)\n",
+ "op_df.insert(7, 'Asset Type', pd.NA)\n",
+ "op_df.insert(8, 'Company', pd.NA)\n",
+ "op_df.insert(9, 'Industry', pd.NA)\n",
+ "op_df.insert(10, 'Private Placement', False)\n",
+ "# op_df = op_df.insert(7, 'Bank', pd.NA)\n",
+ "op_df.head()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "cc1f037f",
+ "metadata": {},
+ "source": [
+ "#### Add the Bank and Asset Type "
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 8,
+ "id": "29c95d09",
+ "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>Account or Security</th>\n",
+ " <th>Coupon</th>\n",
+ " <th>Date</th>\n",
+ " <th>Quantity</th>\n",
+ " <th>Cost Value</th>\n",
+ " <th>Market Value</th>\n",
+ " <th>Bank</th>\n",
+ " <th>Asset Type</th>\n",
+ " <th>Company</th>\n",
+ " <th>Industry</th>\n",
+ " <th>Private Placement</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>36</th>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>37</th>\n",
+ " <td>Asset-Backed Securities</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Asset-Backed Securities</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>38</th>\n",
+ " <td>CHASE FDG MTG LN 2004-1 MTG LN</td>\n",
+ " <td>2.08</td>\n",
+ " <td>1933-12-25 00:00:00</td>\n",
+ " <td>6462.53</td>\n",
+ " <td>6462.53</td>\n",
+ " <td>6068.88</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Asset-Backed Securities</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>39</th>\n",
+ " <td>MVW 2021-2 LLC TIMESHARE LN BK</td>\n",
+ " <td>1.43</td>\n",
+ " <td>1939-05-20 00:00:00</td>\n",
+ " <td>737880.25</td>\n",
+ " <td>737679.54</td>\n",
+ " <td>672033.22</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Asset-Backed Securities</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>40</th>\n",
+ " <td>PFS FING CORP .77% 08-15-2026</td>\n",
+ " <td>0.77</td>\n",
+ " <td>2026-08-15 00:00:00</td>\n",
+ " <td>1090000.00</td>\n",
+ " <td>1089872.25</td>\n",
+ " <td>1010524.50</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Asset-Backed Securities</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "text/plain": [
+ " Account or Security Coupon Date Quantity \\\n",
+ "36 9-200100-Neuberger Intermediate NaN NaN NaN \n",
+ "37 Asset-Backed Securities NaN NaN NaN \n",
+ "38 CHASE FDG MTG LN 2004-1 MTG LN 2.08 1933-12-25 00:00:00 6462.53 \n",
+ "39 MVW 2021-2 LLC TIMESHARE LN BK 1.43 1939-05-20 00:00:00 737880.25 \n",
+ "40 PFS FING CORP .77% 08-15-2026  0.77 2026-08-15 00:00:00 1090000.00 \n",
+ "\n",
+ " Cost Value Market Value Bank \\\n",
+ "36 NaN NaN 9-200100-Neuberger Intermediate \n",
+ "37 NaN NaN 9-200100-Neuberger Intermediate \n",
+ "38 6462.53 6068.88 9-200100-Neuberger Intermediate \n",
+ "39 737679.54 672033.22 9-200100-Neuberger Intermediate \n",
+ "40 1089872.25 1010524.50 9-200100-Neuberger Intermediate \n",
+ "\n",
+ " Asset Type Company Industry Private Placement \n",
+ "36 <NA> <NA> <NA> False \n",
+ "37 Asset-Backed Securities <NA> <NA> False \n",
+ "38 Asset-Backed Securities <NA> <NA> False \n",
+ "39 Asset-Backed Securities <NA> <NA> False \n",
+ "40 Asset-Backed Securities <NA> <NA> False "
+ ]
+ },
+ "execution_count": 8,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "bank_name = pd.NA\n",
+ "asset_type = pd.NA\n",
+ "for i in op_df.index:\n",
+ " if np.isnan(op_df.loc[i][\"Quantity\"]):\n",
+ " if \"9-200100\" in df.loc[i][\"Account or Security\"]:\n",
+ " bank_name = df.loc[i][\"Account or Security\"]\n",
+ " else:\n",
+ " asset_type = df.loc[i][\"Account or Security\"]\n",
+ " op_df.at[i,'Bank'] = bank_name\n",
+ " op_df.at[i,'Asset Type'] = asset_type\n",
+ "op_df.head()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 9,
+ "id": "3ea40a3e",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "{<NA>,\n",
+ " 'Account Totals',\n",
+ " 'Account Totals - Allspring Conservative Income Fund',\n",
+ " 'Account Totals - Allspring Short Duration',\n",
+ " 'Account Totals - Galliard Capital',\n",
+ " 'Account Totals - Income Research Intermediate',\n",
+ " 'Account Totals - Income Research Short Duration',\n",
+ " 'Account Totals - JP Morgan Managed Reserves ',\n",
+ " 'Account Totals - Neuberger Berman Enhanced Cash',\n",
+ " 'Account Totals - Neuberger Intermediate',\n",
+ " 'Account Totals - Northern Trust UI Short Duration',\n",
+ " 'Asset Backed Securities',\n",
+ " 'Asset-Backed Securities',\n",
+ " 'Cash & Cash Equivalents',\n",
+ " 'Commercial Mortgage Backed Securities',\n",
+ " 'Commercial Paper',\n",
+ " 'Corporate Bonds',\n",
+ " 'International Government Bonds',\n",
+ " 'Municipal Bonds',\n",
+ " 'U.S. Agency Bonds',\n",
+ " 'U.S. Treasury Securities'}"
+ ]
+ },
+ "execution_count": 9,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "all_asset_types = set([op_df.loc[i]['Asset Type'] for i in op_df.index]);all_asset_types"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "274d0b39",
+ "metadata": {},
+ "source": [
+ "#### First, we just check the corperate bonds "
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 10,
+ "id": "fe124c92",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Corperate Bond Totals\n",
+ "Cost Value\t $1,020,500,769.44\n",
+ "Market Value\t $985,000,673.71\n"
+ ]
+ }
+ ],
+ "source": [
+ "cb_df = op_df[op_df['Asset Type'].str.contains(\"Corporate Bonds\", na=False)]\n",
+ "print(\"Corperate Bond Totals\")\n",
+ "print(\"Cost Value\\t\",'${:,.2f}'.format(cb_df.sum(numeric_only=True)[\"Cost Value\"]))\n",
+ "print(\"Market Value\\t\",'${:,.2f}'.format(cb_df.sum(numeric_only=True)[\"Market Value\"]))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "d4aba4d7",
+ "metadata": {},
+ "source": [
+ "Yup, you read that right"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "9879b032",
+ "metadata": {},
+ "source": [
+ "##### get company name\n",
+ "+ Parse our 'PVTPL', which is an abreviation for privatly placed https://www.investopedia.com/terms/p/privateplacement.asp\n",
+ "+ Remove everything after and including the tokens ```['%']```\n",
+ "+ Remove everything after `[\" CAP\", \" INC\", \" FDG\", \" CORP\", \" CO\", \" LLC\", \" CR\"]`\n",
+ "+ Add the company names to a set\n",
+ "+ Map different semantic names to the same syntax for the same company"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 11,
+ "id": "d78a9733",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "company_name_dict = {\n",
+ " \"AMERICAN EXPRESS\" : \"AMERICAN EXPRESS CO\",\n",
+ " 'AIG GLOBAL' : \"AMERICAN INTL GROUP INC\",\n",
+ " \"ANHEUSER-BUSCH\" : \"ANHEUSER-BUSCH CO\",\n",
+ " \"APTIV\" : \"APTIV CO\",\n",
+ " \"ASTRAZENECA\" : \"ASTRAZENECA PLC\",\n",
+ " \"AUSTRALIA & NEW\" : \"AUSTRALIA & NEW ZEALAND BKG GR\",\n",
+ " \"BAE SYS\" : \"BAE SYS PLC\",\n",
+ " \"BANCO SANTANDER\" : \"BANCO SANTANDER SA\",\n",
+ " \"BANK MONTREAL\" : \"BANK OF MONTREAL\",\n",
+ " \"BANK OF MONTREAL\" : \"BANK OF MONTREAL\",\n",
+ " \"BK MONTREAL\" : \"BANK OF MONTREAL\",\n",
+ " \"BANK NOVA SCOTIA\" : \"BANK OF NOVA SCOTIA\",\n",
+ " \"BANK OF NOVA SCOTIA\" : \"BANK OF NOVA SCOTIA\",\n",
+ " \"BANK AMER\" : \"BANK OF AMERICA CO\",\n",
+ " \"BAXTER INT\" : \"BAXTER INTERNATIONAL INC\",\n",
+ " \"BAYER US FIN\" : \"BAYER US FINANCE LLC\",\n",
+ " \"BB&T\" : \"BB&T CO\",\n",
+ " \"BLACKSTONE\" : \"BLACKSTONE\",\n",
+ " \"BMW\" : \"BMW\",\n",
+ " \"BNP PARIBAS\" : \"BNP PARIBAS\",\n",
+ " \"BRIGHTHOUSE\" : \"BRIGHTHOUSE\",\n",
+ " \"BRISTOL MYERS SQUIBB\" : \"BRISTOL MYERS SQUIBB CO\",\n",
+ " \"BRISTOL-MYERS SQUIBB\" : \"BRISTOL MYERS SQUIBB CO\",\n",
+ " \"CANADIAN IMPERIAL BK\" : \"CANADIAN IMPERIAL BK CO\",\n",
+ " \"CAPITAL ONE\" : \"CAPITAL ONE FINL CO\",\n",
+ " \"CATERPILLAR FINL\" : \"CATERPILLAR FINL\",\n",
+ " \"CENTERPOINT ENERGY\" : \"CENTERPOINT ENERGY INC\",\n",
+ " \"CHEVRON U S A\" : \"CHEVRON CO\",\n",
+ " \"CREDIT AGRICOLE\" : \"CREDIT AGRICOLE\",\n",
+ " \"CREDIT SUISSE\" : \"CREDIT SUISSE GROUP AG\",\n",
+ " \"CROWN CASTLE INTL\" : \"CROWN CASTLE INTL\",\n",
+ " \"DAIMLER\" : \"DAIMLER\",\n",
+ " \"DELTA AIR LINES\" : \"DELTA AIR LINES\",\n",
+ " \"DTE E\" : \"DTE ELEC\",\n",
+ " \"DUKE ENERGY\" : \"DUKE ENERGY CO\",\n",
+ " \"DOWDUPONT INC\" : \"DUPONT DE NEMOURS INC\",\n",
+ " \"ENTERGY\" : \"ENTERGY CO\",\n",
+ " \"EQUITABLE FINL LIFE\" : \"EQUITABLE FINL LIFE GLOBAL FDG\",\n",
+ " \"ESC CB LEHMAN BROS\" : \"ESC LEHMAN BROTH HLD INC\",\n",
+ " \"FIFTH THIRD BANCORP\" : \"FIFTH THIRD BANCORP\",\n",
+ " \"FLORIDA P\" : \"FLORIDA POWER & LIGHT CO\",\n",
+ " \"GENERAL MTRS\" : \"GENERAL MOTORS\",\n",
+ " \"GENERAL MOTORS\" : \"GENERAL MOTORS\",\n",
+ " \"HEWLETT PACKARD\" : \"HEWLETT PACKARD ENTERPRISE CO\",\n",
+ " \"HP INC\" : \"HEWLETT PACKARD ENTERPRISE CO\",\n",
+ " \"HUNTINGTON\" : \"HUNTINGTON NATL BK MD\",\n",
+ " \"JACKSON FINANCIAL INC\" : \"JACKSON NATIONAL LIFE GL\",\n",
+ " \"JPM CHASE\" : \"JPMORGAN CHASE & CO\",\n",
+ " \"KINDER MORGAN\" : \"KINDER MORGAN INC\",\n",
+ " \"LLOYDS BKG\" : \"LLOYDS BANKING GROUP PLC FORME\",\n",
+ " \"MACQUARIE\" : \"MACQUARIE BK LTD\",\n",
+ " \"MIZUHO\" : \"MIZUHO CO\",\n",
+ " \"MONDELEZ INT\" : \"MONDELEZ INTERNATIONAL INC\",\n",
+ " \"MORGAN STANLEY\" : \"MORGAN STANLEY\",\n",
+ " \"NATIONAL AUSTRALIA B\" : \"NATIONAL AUSTRALIA BANK\",\n",
+ " \"NATIONWIDE BLDG SOC\" : \"NATIONWIDE BLDG SOCIETY\",\n",
+ " \"NATIONAL BANK OF CANADA\" : \"NATIONAL BANK OF CANADA\",\n",
+ " \"NATL BK CDA\" : \"NATIONAL BANK OF CANADA\",\n",
+ " \"NATWEST M\" : \"NATWEST MARKETS PLC\",\n",
+ " \"NEXTERA ENERGY\" : \"NEXTERA ENERGY CAP\",\n",
+ " \"NORDEA BANK\" : \"NORDEA BANK\",\n",
+ " \"NORTHWESTERN\" : \"NORTHWESTERN MUT\",\n",
+ " \"NXP B V\" : \"NXP B V\",\n",
+ " \"PHILLIPS 66\" : \"PHILLIPS 66\",\n",
+ " \"PRINCIPAL LIFE GLOBAL\" : \"PRINCIPAL LIFE GLOBAL FDG\",\n",
+ " \"PROTECTIVE LIFE G\" : \"PROTECTIVE LIFE GLOBAL\",\n",
+ " \"PUBLIC SVC\" : \"PUBLIC SERVICE ELECTRIC & GAS\",\n",
+ " \"RABOBANK NEDERLAND\" : \"RABOBANK NEDERLAND\",\n",
+ " \"ROCHE H\" : \"ROCHE HOLDINGS INC\",\n",
+ " \"ROPER \" : \"ROPER TECHNOLOGIES INC\",\n",
+ " \"ROYAL BANK OF CANADA\" : \"ROYAL BANK OF CANADA\",\n",
+ " \"ROYAL BK CDA\" : \"ROYAL BANK OF CANADA\",\n",
+ " \"SCHLUMBERGER\" : \"SCHLUMBERGER\", \n",
+ " \"SIEMENS FINANCIERINGSMAA\" : \"SIEMENS FINANCIERINGSMAATSCHAP\",\n",
+ " \"SIMON PPTY GROUP\" : \"SIMON PPTY GROUP\",\n",
+ " \"STATE STR\" : \"STATE STREET CO\",\n",
+ " \"SUMITOMO MITSUI\" : \"SUMITOMO MITSUI BANKING\",\n",
+ " \"SWEDBANK AB\" : \"SWEDBANK AB\",\n",
+ " \"TORONTO DOMINION B\" : \"TORONTO DOMINION BANK\",\n",
+ " \"TOYOTA M\" : \"TOYOTA MOTOR\",\n",
+ " \"TRANS-CDA PIPELINES\" : \"TRANS-CDA PIPELINES\",\n",
+ " \"TRUIST \" : \"TRUIST BANK\",\n",
+ " \"TSMC \" : \"TSMC GLOBAL LTD\",\n",
+ " \"U S B\" : \"U S BANCORP\",\n",
+ " \"UBS \" : \"UBS AG LONDON\",\n",
+ " \"VENTAS REALTY\" : \"VENTAS REALTY LP\",\n",
+ " \"VOLKSWAGEN GROUP\" : \"VOLKSWAGEN GROUP\",\n",
+ " \"WESTPAC B\" : \"WESTPAC BANKING\",\n",
+ "}"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 12,
+ "id": "659c0ba7",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "company_names = set()\n",
+ "for i in cb_df.index:\n",
+ " # Set the Company to be cleaned\n",
+ " cb_df.at[i,'Company'] = cb_df.at[i,'Account or Security']\n",
+ " if not np.isnan(cb_df.at[i,'Quantity']):\n",
+ " # clean private placement\n",
+ " for prefix in [\"PVTPL\", \"PVPTL\", \"PVYPL\", \"PVT PL\", \"PVPTL\"]:\n",
+ " if prefix in cb_df.loc[i][\"Company\"]:\n",
+ " cb_df.at[i,'Private Placement'] = True\n",
+ " cb_df.at[i,'Company'] = cb_df.at[i,'Company'][6:].strip()\n",
+ " for end in [\" CAP\", \" INC\", \" FDG\", \" CORP\", \" CO\", \" LLC\", \" CR\", \" SR\", \" A/S\", \" LP\", \" ASA\", \" LTD\", ]:\n",
+ " if end in cb_df.at[i, \"Company\"]:\n",
+ " cb_df.at[i, \"Company\"] = cb_df.at[i, 'Company'].split(end)[0].strip()+\" \"+end\n",
+ " for token in ['%']:\n",
+ " if token in cb_df.at[i, \"Company\"]:\n",
+ " # get everythng before the token, then get eveything before the last space\n",
+ " cb_df.at[i, \"Company\"] = cb_df.at[i, \"Company\"].split(token)[0].rsplit(' ', 1)[0].strip()\n",
+ " for key, value in company_name_dict.items():\n",
+ " if key in cb_df.at[i, \"Company\"]:\n",
+ " cb_df.at[i, \"Company\"] = value\n",
+ " company_names.add(cb_df.at[i, \"Company\"])\n",
+ " else:\n",
+ " cb_df.drop(i, axis=0)\n"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 13,
+ "id": "0ba92284",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "408"
+ ]
+ },
+ "execution_count": 13,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "len(company_names)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "6accaca4",
+ "metadata": {},
+ "source": [
+ "It looks like we are invested in 408 companies"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 14,
+ "id": "e7e06dfe",
+ "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>Account or Security</th>\n",
+ " <th>Coupon</th>\n",
+ " <th>Date</th>\n",
+ " <th>Quantity</th>\n",
+ " <th>Cost Value</th>\n",
+ " <th>Market Value</th>\n",
+ " <th>Bank</th>\n",
+ " <th>Asset Type</th>\n",
+ " <th>Company</th>\n",
+ " <th>Industry</th>\n",
+ " <th>Private Placement</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>103</th>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>NaN</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>104</th>\n",
+ " <td>ABBVIE INC 2.6% DUE 11-21-2024</td>\n",
+ " <td>2.6</td>\n",
+ " <td>2024-11-21 00:00:00</td>\n",
+ " <td>1065000.0</td>\n",
+ " <td>1135158.13</td>\n",
+ " <td>1030877.86</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>ABBVIE INC</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>105</th>\n",
+ " <td>ABBVIE INC 2.95% DUE</td>\n",
+ " <td>2.95</td>\n",
+ " <td>2026-11-21 00:00:00</td>\n",
+ " <td>495000.0</td>\n",
+ " <td>545836.80</td>\n",
+ " <td>469035.55</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>ABBVIE INC</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>106</th>\n",
+ " <td>AERCAP IRELAND CAP / GLOBA 3.0</td>\n",
+ " <td>3</td>\n",
+ " <td>2028-10-29 00:00:00</td>\n",
+ " <td>440000.0</td>\n",
+ " <td>439199.20</td>\n",
+ " <td>370620.29</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>AERCAP IRELAND CAP</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>107</th>\n",
+ " <td>AERCAP IRELAND CAP / GLOBA</td>\n",
+ " <td>3.56</td>\n",
+ " <td>1932-01-30 00:00:00</td>\n",
+ " <td>430000.0</td>\n",
+ " <td>428383.20</td>\n",
+ " <td>344211.28</td>\n",
+ " <td>9-200100-Neuberger Intermediate</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>AERCAP IRELAND CAP</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</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",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2706</th>\n",
+ " <td>UBS AG LONDON BRH NT FLTG RATE</td>\n",
+ " <td>1.39</td>\n",
+ " <td>2024-08-09 00:00:00</td>\n",
+ " <td>1766000.0</td>\n",
+ " <td>1766000.00</td>\n",
+ " <td>1754003.40</td>\n",
+ " <td>9-200100-JP Morgan Managed Reserves</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>UBS AG LONDON</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2707</th>\n",
+ " <td>UBS GROUP FDG SWITZ AG NT FLTG</td>\n",
+ " <td>2.36</td>\n",
+ " <td>2023-08-15 00:00:00</td>\n",
+ " <td>2000000.0</td>\n",
+ " <td>1999460.00</td>\n",
+ " <td>1999374.24</td>\n",
+ " <td>9-200100-JP Morgan Managed Reserves</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>UBS AG LONDON</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2708</th>\n",
+ " <td>UNITED PARCEL SVC INC 2.45% DU</td>\n",
+ " <td>2.45</td>\n",
+ " <td>2022-10-01 00:00:00</td>\n",
+ " <td>145000.0</td>\n",
+ " <td>148575.70</td>\n",
+ " <td>144922.71</td>\n",
+ " <td>9-200100-JP Morgan Managed Reserves</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>UNITED PARCEL SVC INC</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2709</th>\n",
+ " <td>WESTPAC BKG CORP 2.0% DUE 01-1</td>\n",
+ " <td>2</td>\n",
+ " <td>2023-01-13 00:00:00</td>\n",
+ " <td>72000.0</td>\n",
+ " <td>73785.60</td>\n",
+ " <td>71637.73</td>\n",
+ " <td>9-200100-JP Morgan Managed Reserves</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>WESTPAC BANKING</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2710</th>\n",
+ " <td>WESTPAC BKG CORP 2.75% DUE 01-</td>\n",
+ " <td>2.75</td>\n",
+ " <td>2023-01-11 00:00:00</td>\n",
+ " <td>2497000.0</td>\n",
+ " <td>2589713.61</td>\n",
+ " <td>2492680.54</td>\n",
+ " <td>9-200100-JP Morgan Managed Reserves</td>\n",
+ " <td>Corporate Bonds</td>\n",
+ " <td>WESTPAC BANKING</td>\n",
+ " <td>&lt;NA&gt;</td>\n",
+ " <td>False</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "<p>1222 rows × 11 columns</p>\n",
+ "</div>"
+ ],
+ "text/plain": [
+ " Account or Security Coupon Date Quantity \\\n",
+ "103 Corporate Bonds NaN NaN NaN \n",
+ "104 ABBVIE INC 2.6% DUE 11-21-2024 2.6 2024-11-21 00:00:00 1065000.0 \n",
+ "105 ABBVIE INC 2.95% DUE  2.95 2026-11-21 00:00:00 495000.0 \n",
+ "106 AERCAP IRELAND CAP / GLOBA 3.0 3 2028-10-29 00:00:00 440000.0 \n",
+ "107 AERCAP IRELAND CAP / GLOBA  3.56 1932-01-30 00:00:00 430000.0 \n",
+ "... ... ... ... ... \n",
+ "2706 UBS AG LONDON BRH NT FLTG RATE 1.39 2024-08-09 00:00:00 1766000.0 \n",
+ "2707 UBS GROUP FDG SWITZ AG NT FLTG 2.36 2023-08-15 00:00:00 2000000.0 \n",
+ "2708 UNITED PARCEL SVC INC 2.45% DU 2.45 2022-10-01 00:00:00 145000.0 \n",
+ "2709 WESTPAC BKG CORP 2.0% DUE 01-1 2 2023-01-13 00:00:00 72000.0 \n",
+ "2710 WESTPAC BKG CORP 2.75% DUE 01- 2.75 2023-01-11 00:00:00 2497000.0 \n",
+ "\n",
+ " Cost Value Market Value Bank \\\n",
+ "103 NaN NaN 9-200100-Neuberger Intermediate \n",
+ "104 1135158.13 1030877.86 9-200100-Neuberger Intermediate \n",
+ "105 545836.80 469035.55 9-200100-Neuberger Intermediate \n",
+ "106 439199.20 370620.29 9-200100-Neuberger Intermediate \n",
+ "107 428383.20 344211.28 9-200100-Neuberger Intermediate \n",
+ "... ... ... ... \n",
+ "2706 1766000.00 1754003.40 9-200100-JP Morgan Managed Reserves \n",
+ "2707 1999460.00 1999374.24 9-200100-JP Morgan Managed Reserves \n",
+ "2708 148575.70 144922.71 9-200100-JP Morgan Managed Reserves \n",
+ "2709 73785.60 71637.73 9-200100-JP Morgan Managed Reserves \n",
+ "2710 2589713.61 2492680.54 9-200100-JP Morgan Managed Reserves \n",
+ "\n",
+ " Asset Type Company Industry Private Placement \n",
+ "103 Corporate Bonds Corporate Bonds <NA> False \n",
+ "104 Corporate Bonds ABBVIE INC <NA> False \n",
+ "105 Corporate Bonds ABBVIE INC <NA> False \n",
+ "106 Corporate Bonds AERCAP IRELAND CAP <NA> False \n",
+ "107 Corporate Bonds AERCAP IRELAND CAP <NA> False \n",
+ "... ... ... ... ... \n",
+ "2706 Corporate Bonds UBS AG LONDON <NA> False \n",
+ "2707 Corporate Bonds UBS AG LONDON <NA> False \n",
+ "2708 Corporate Bonds UNITED PARCEL SVC INC <NA> False \n",
+ "2709 Corporate Bonds WESTPAC BANKING <NA> False \n",
+ "2710 Corporate Bonds WESTPAC BANKING <NA> False \n",
+ "\n",
+ "[1222 rows x 11 columns]"
+ ]
+ },
+ "execution_count": 14,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "cb_df"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "8b78d9cd",
+ "metadata": {},
+ "source": [
+ "##### get ticker"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 53,
+ "id": "6fe3d0df",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "WARNING: No decryption keys could be extracted from JS file. Falling back to backup decrypt methods.\n",
+ "Drug Manufacturers—General\n"
+ ]
+ }
+ ],
+ "source": [
+ "# Replace \"company_name\" with the name of the company you're searching for\n",
+ "company_name = \"ABBV\"\n",
+ "\n",
+ "# Search for the company on Yahoo Finance\n",
+ "search_results = yf.Tickers(company_name)\n",
+ "\n",
+ "# Get the ticker symbol for the first result\n",
+ "print (search_results.tickers['ABBV'].info['industry'])\n",
+ "\n",
+ "# print(ticker)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "db95aa9c",
+ "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.10.6"
+ },
+ "vscode": {
+ "interpreter": {
+ "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
+ }
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 5
+}
generated by cgit v1.2.3 (git 2.25.1) at 2025-12-06 08:09:28 +0000