From 7bfaf2fb28d6f7bc6a35b031c29c221eef54dda0 Mon Sep 17 00:00:00 2001 From: kennykos <“gkosmacher01@gmail.com”> Date: Wed, 15 Feb 2023 10:13:40 -0600 Subject: 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 --- ui_security_inventory_23_parsing.ipynb | 941 +++++++++++++++++++++++++++++++++ 1 file changed, 941 insertions(+) create mode 100644 ui_security_inventory_23_parsing.ipynb (limited to 'ui_security_inventory_23_parsing.ipynb') 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": [ + "
\n", + "\n", + "\n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
Account or SecurityCouponDateQuantityCost ValueMarket ValueBankAsset TypeCompanyIndustryPrivate Placement
369-200100-Neuberger IntermediateNaNNaNNaNNaNNaN<NA><NA><NA><NA>False
37Asset-Backed SecuritiesNaNNaNNaNNaNNaN<NA><NA><NA><NA>False
38CHASE FDG MTG LN 2004-1 MTG LN2.081933-12-25 00:00:006462.536462.536068.88<NA><NA><NA><NA>False
39MVW 2021-2 LLC TIMESHARE LN BK1.431939-05-20 00:00:00737880.25737679.54672033.22<NA><NA><NA><NA>False
40PFS FING CORP .77% 08-15-20260.772026-08-15 00:00:001090000.001089872.251010524.50<NA><NA><NA><NA>False
\n", + "
" + ], + "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 \n", + "37 NaN NaN \n", + "38 6462.53 6068.88 \n", + "39 737679.54 672033.22 \n", + "40 1089872.25 1010524.50 \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": [ + "
\n", + "\n", + "\n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
Account or SecurityCouponDateQuantityCost ValueMarket ValueBankAsset TypeCompanyIndustryPrivate Placement
369-200100-Neuberger IntermediateNaNNaNNaNNaNNaN9-200100-Neuberger Intermediate<NA><NA><NA>False
37Asset-Backed SecuritiesNaNNaNNaNNaNNaN9-200100-Neuberger IntermediateAsset-Backed Securities<NA><NA>False
38CHASE FDG MTG LN 2004-1 MTG LN2.081933-12-25 00:00:006462.536462.536068.889-200100-Neuberger IntermediateAsset-Backed Securities<NA><NA>False
39MVW 2021-2 LLC TIMESHARE LN BK1.431939-05-20 00:00:00737880.25737679.54672033.229-200100-Neuberger IntermediateAsset-Backed Securities<NA><NA>False
40PFS FING CORP .77% 08-15-20260.772026-08-15 00:00:001090000.001089872.251010524.509-200100-Neuberger IntermediateAsset-Backed Securities<NA><NA>False
\n", + "
" + ], + "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 False \n", + "37 Asset-Backed Securities False \n", + "38 Asset-Backed Securities False \n", + "39 Asset-Backed Securities False \n", + "40 Asset-Backed Securities 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": [ + "{,\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": [ + "
\n", + "\n", + "\n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
Account or SecurityCouponDateQuantityCost ValueMarket ValueBankAsset TypeCompanyIndustryPrivate Placement
103Corporate BondsNaNNaNNaNNaNNaN9-200100-Neuberger IntermediateCorporate BondsCorporate Bonds<NA>False
104ABBVIE INC 2.6% DUE 11-21-20242.62024-11-21 00:00:001065000.01135158.131030877.869-200100-Neuberger IntermediateCorporate BondsABBVIE INC<NA>False
105ABBVIE INC 2.95% DUE2.952026-11-21 00:00:00495000.0545836.80469035.559-200100-Neuberger IntermediateCorporate BondsABBVIE INC<NA>False
106AERCAP IRELAND CAP / GLOBA 3.032028-10-29 00:00:00440000.0439199.20370620.299-200100-Neuberger IntermediateCorporate BondsAERCAP IRELAND CAP<NA>False
107AERCAP IRELAND CAP / GLOBA3.561932-01-30 00:00:00430000.0428383.20344211.289-200100-Neuberger IntermediateCorporate BondsAERCAP IRELAND CAP<NA>False
....................................
2706UBS AG LONDON BRH NT FLTG RATE1.392024-08-09 00:00:001766000.01766000.001754003.409-200100-JP Morgan Managed ReservesCorporate BondsUBS AG LONDON<NA>False
2707UBS GROUP FDG SWITZ AG NT FLTG2.362023-08-15 00:00:002000000.01999460.001999374.249-200100-JP Morgan Managed ReservesCorporate BondsUBS AG LONDON<NA>False
2708UNITED PARCEL SVC INC 2.45% DU2.452022-10-01 00:00:00145000.0148575.70144922.719-200100-JP Morgan Managed ReservesCorporate BondsUNITED PARCEL SVC INC<NA>False
2709WESTPAC BKG CORP 2.0% DUE 01-122023-01-13 00:00:0072000.073785.6071637.739-200100-JP Morgan Managed ReservesCorporate BondsWESTPAC BANKING<NA>False
2710WESTPAC BKG CORP 2.75% DUE 01-2.752023-01-11 00:00:002497000.02589713.612492680.549-200100-JP Morgan Managed ReservesCorporate BondsWESTPAC BANKING<NA>False
\n", + "

1222 rows × 11 columns

\n", + "
" + ], + "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 False \n", + "104 Corporate Bonds ABBVIE INC False \n", + "105 Corporate Bonds ABBVIE INC False \n", + "106 Corporate Bonds AERCAP IRELAND CAP False \n", + "107 Corporate Bonds AERCAP IRELAND CAP False \n", + "... ... ... ... ... \n", + "2706 Corporate Bonds UBS AG LONDON False \n", + "2707 Corporate Bonds UBS AG LONDON False \n", + "2708 Corporate Bonds UNITED PARCEL SVC INC False \n", + "2709 Corporate Bonds WESTPAC BANKING False \n", + "2710 Corporate Bonds WESTPAC BANKING 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 +} -- cgit v1.2.3