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 From d992b439e0ba706812b6db44824506c52e447d0d Mon Sep 17 00:00:00 2001 From: kennykos <“gkosmacher01@gmail.com”> Date: Wed, 15 Feb 2023 11:01:08 -0600 Subject: clearing notebook, sorry --- ui_security_inventory_23_parsing.ipynb | 42 ++++++++++++++-------------------- 1 file changed, 17 insertions(+), 25 deletions(-) (limited to 'ui_security_inventory_23_parsing.ipynb') diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb index 3248cde..84be050 100644 --- a/ui_security_inventory_23_parsing.ipynb +++ b/ui_security_inventory_23_parsing.ipynb @@ -2,7 +2,7 @@ "cells": [ { "cell_type": "code", - "execution_count": 5, + "execution_count": 1, "id": "8ce6b023", "metadata": {}, "outputs": [], @@ -15,7 +15,7 @@ }, { "cell_type": "code", - "execution_count": 6, + "execution_count": 2, "id": "8304939a", "metadata": {}, "outputs": [ @@ -43,7 +43,7 @@ }, { "cell_type": "code", - "execution_count": 7, + "execution_count": 3, "id": "96dee5bc", "metadata": {}, "outputs": [ @@ -179,7 +179,7 @@ "40 False " ] }, - "execution_count": 7, + "execution_count": 3, "metadata": {}, "output_type": "execute_result" } @@ -207,7 +207,7 @@ }, { "cell_type": "code", - "execution_count": 8, + "execution_count": 4, "id": "29c95d09", "metadata": {}, "outputs": [ @@ -343,7 +343,7 @@ "40 Asset-Backed Securities False " ] }, - "execution_count": 8, + "execution_count": 4, "metadata": {}, "output_type": "execute_result" } @@ -364,7 +364,7 @@ }, { "cell_type": "code", - "execution_count": 9, + "execution_count": 5, "id": "3ea40a3e", "metadata": {}, "outputs": [ @@ -394,7 +394,7 @@ " 'U.S. Treasury Securities'}" ] }, - "execution_count": 9, + "execution_count": 5, "metadata": {}, "output_type": "execute_result" } @@ -413,7 +413,7 @@ }, { "cell_type": "code", - "execution_count": 10, + "execution_count": 6, "id": "fe124c92", "metadata": {}, "outputs": [ @@ -457,7 +457,7 @@ }, { "cell_type": "code", - "execution_count": 11, + "execution_count": 7, "id": "d78a9733", "metadata": {}, "outputs": [], @@ -556,7 +556,7 @@ }, { "cell_type": "code", - "execution_count": 12, + "execution_count": 8, "id": "659c0ba7", "metadata": {}, "outputs": [], @@ -588,7 +588,7 @@ }, { "cell_type": "code", - "execution_count": 13, + "execution_count": 9, "id": "0ba92284", "metadata": {}, "outputs": [ @@ -598,7 +598,7 @@ "408" ] }, - "execution_count": 13, + "execution_count": 9, "metadata": {}, "output_type": "execute_result" } @@ -617,7 +617,7 @@ }, { "cell_type": "code", - "execution_count": 14, + "execution_count": 10, "id": "e7e06dfe", "metadata": {}, "outputs": [ @@ -858,7 +858,7 @@ "[1222 rows x 11 columns]" ] }, - "execution_count": 14, + "execution_count": 10, "metadata": {}, "output_type": "execute_result" } @@ -877,7 +877,7 @@ }, { "cell_type": "code", - "execution_count": 53, + "execution_count": 11, "id": "6fe3d0df", "metadata": {}, "outputs": [ @@ -902,19 +902,11 @@ "\n", "# print(ticker)" ] - }, - { - "cell_type": "code", - "execution_count": null, - "id": "db95aa9c", - "metadata": {}, - "outputs": [], - "source": [] } ], "metadata": { "kernelspec": { - "display_name": "Python 3", + "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, -- cgit v1.2.3 From 368e280732a23dea5bfdfd6ffa8b51441b26d75f Mon Sep 17 00:00:00 2001 From: kennykos <“gkosmacher01@gmail.com”> Date: Wed, 15 Feb 2023 11:03:06 -0600 Subject: now its cleared, again my bad --- ui_security_inventory_23_parsing.ipynb | 623 +-------------------------------- 1 file changed, 19 insertions(+), 604 deletions(-) (limited to 'ui_security_inventory_23_parsing.ipynb') diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb index 84be050..0091ac6 100644 --- a/ui_security_inventory_23_parsing.ipynb +++ b/ui_security_inventory_23_parsing.ipynb @@ -2,7 +2,7 @@ "cells": [ { "cell_type": "code", - "execution_count": 1, + "execution_count": null, "id": "8ce6b023", "metadata": {}, "outputs": [], @@ -15,18 +15,10 @@ }, { "cell_type": "code", - "execution_count": 2, + "execution_count": null, "id": "8304939a", "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "Sheet1\n" - ] - } - ], + "outputs": [], "source": [ "file_path_23 = \"./data/23-138.Records.xlsx\"\n", " # skip header rows so column names align, drop all NaN rows\n", @@ -43,147 +35,10 @@ }, { "cell_type": "code", - "execution_count": 3, + "execution_count": null, "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": 3, - "metadata": {}, - "output_type": "execute_result" - } - ], + "outputs": [], "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", @@ -207,147 +62,10 @@ }, { "cell_type": "code", - "execution_count": 4, + "execution_count": null, "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": 4, - "metadata": {}, - "output_type": "execute_result" - } - ], + "outputs": [], "source": [ "bank_name = pd.NA\n", "asset_type = pd.NA\n", @@ -364,41 +82,10 @@ }, { "cell_type": "code", - "execution_count": 5, + "execution_count": null, "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": 5, - "metadata": {}, - "output_type": "execute_result" - } - ], + "outputs": [], "source": [ "all_asset_types = set([op_df.loc[i]['Asset Type'] for i in op_df.index]);all_asset_types" ] @@ -413,20 +100,10 @@ }, { "cell_type": "code", - "execution_count": 6, + "execution_count": null, "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" - ] - } - ], + "outputs": [], "source": [ "cb_df = op_df[op_df['Asset Type'].str.contains(\"Corporate Bonds\", na=False)]\n", "print(\"Corperate Bond Totals\")\n", @@ -457,7 +134,7 @@ }, { "cell_type": "code", - "execution_count": 7, + "execution_count": null, "id": "d78a9733", "metadata": {}, "outputs": [], @@ -556,7 +233,7 @@ }, { "cell_type": "code", - "execution_count": 8, + "execution_count": null, "id": "659c0ba7", "metadata": {}, "outputs": [], @@ -588,21 +265,10 @@ }, { "cell_type": "code", - "execution_count": 9, + "execution_count": null, "id": "0ba92284", "metadata": {}, - "outputs": [ - { - "data": { - "text/plain": [ - "408" - ] - }, - "execution_count": 9, - "metadata": {}, - "output_type": "execute_result" - } - ], + "outputs": [], "source": [ "len(company_names)" ] @@ -617,252 +283,10 @@ }, { "cell_type": "code", - "execution_count": 10, + "execution_count": null, "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": 10, - "metadata": {}, - "output_type": "execute_result" - } - ], + "outputs": [], "source": [ "cb_df" ] @@ -877,19 +301,10 @@ }, { "cell_type": "code", - "execution_count": 11, + "execution_count": null, "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" - ] - } - ], + "outputs": [], "source": [ "# Replace \"company_name\" with the name of the company you're searching for\n", "company_name = \"ABBV\"\n", -- cgit v1.2.3 From 8d42c3651a60339085e9bd94d282022052a16dcf Mon Sep 17 00:00:00 2001 From: kennykos <“gkosmacher01@gmail.com”> Date: Wed, 15 Feb 2023 12:20:35 -0600 Subject: getting ticker and integrating into datafram next step is to integrate info from yfinance into the dataframe --- ui_security_inventory_23_parsing.ipynb | 131 ++++++++++++++++++++++++++++----- 1 file changed, 111 insertions(+), 20 deletions(-) (limited to 'ui_security_inventory_23_parsing.ipynb') diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb index 0091ac6..aef55ec 100644 --- a/ui_security_inventory_23_parsing.ipynb +++ b/ui_security_inventory_23_parsing.ipynb @@ -10,7 +10,7 @@ "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" + "import requests" ] }, { @@ -48,6 +48,7 @@ "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.insert(11, 'Ticker', pd.NA)\n", "# op_df = op_df.insert(7, 'Bank', pd.NA)\n", "op_df.head()" ] @@ -266,37 +267,133 @@ { "cell_type": "code", "execution_count": null, - "id": "0ba92284", + "id": "e7e06dfe", "metadata": {}, "outputs": [], "source": [ - "len(company_names)" + "cb_df" ] }, { "cell_type": "markdown", - "id": "6accaca4", + "id": "8b78d9cd", "metadata": {}, "source": [ - "It looks like we are invested in 408 companies" + "##### get ticker" ] }, { "cell_type": "code", "execution_count": null, - "id": "e7e06dfe", + "id": "ba37103f", "metadata": {}, "outputs": [], "source": [ - "cb_df" + "# taken from https://gist.github.com/bruhbruhroblox/dd9d981c8c37983f61e423a45085e063\n", + "def getTicker(company_name):\n", + " yfinance = \"https://query2.finance.yahoo.com/v1/finance/search\"\n", + " user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36'\n", + " params = {\"q\": company_name, \"quotes_count\": 1, \"country\": \"United States\"}\n", + "\n", + " res = requests.get(url=yfinance, params=params, headers={'User-Agent': user_agent})\n", + " data = res.json()\n", + "\n", + " company_code = data['quotes'][0]['symbol']\n", + " return company_code" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "f2e232f5", + "metadata": {}, + "outputs": [], + "source": [ + "%%time\n", + "company_name_to_ticker = dict()\n", + "for name in company_names:\n", + " try:\n", + " # try to get the ticker\n", + " ticker = getTicker(name)\n", + " except:\n", + " try:\n", + " # shorten the name and try again\n", + " short_name = name.split(' ')[0]\n", + " ticker = getTicker(short_name)\n", + " except:\n", + " # no ticker could be found, probably a private company, check to make sure\n", + " ticker = 'NO_TICKER_FOUND'\n", + " company_name_to_ticker[name] = ticker" ] }, { "cell_type": "markdown", - "id": "8b78d9cd", + "id": "e3009168", "metadata": {}, "source": [ - "##### get ticker" + "##### match company name to ticker in DF" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "d80dca43", + "metadata": {}, + "outputs": [], + "source": [ + "company_name_to_ticker" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "921c344d", + "metadata": {}, + "outputs": [], + "source": [ + "company_names" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "7abeab84", + "metadata": {}, + "outputs": [], + "source": [ + "company_name_to_ticker[cb_df.at[104,'Company']]" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "e8f64024", + "metadata": {}, + "outputs": [], + "source": [ + "for i in cb_df.index:\n", + " try:\n", + " cb_df.at[i,'Ticker'] = company_name_to_ticker[cb_df.at[i,'Company']]\n", + " except:\n", + " assert cb_df.at[i,'Company'] == 'Corporate Bonds'" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "04e49491", + "metadata": {}, + "outputs": [], + "source": [ + "cb_df.head()" + ] + }, + { + "cell_type": "markdown", + "id": "2342f360", + "metadata": {}, + "source": [ + "## Get info from ticker " ] }, { @@ -306,16 +403,10 @@ "metadata": {}, "outputs": [], "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)" + "def get_info_from_ticker(ticker):\n", + " # Search for the company on Yahoo Finance\n", + " search_results = yf.Tickers(company_name)\n", + " return search_results.tickers['T'].info" ] } ], @@ -335,7 +426,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.10.6" + "version": "3.10.9" }, "vscode": { "interpreter": { -- cgit v1.2.3 From 49cbd4bbe57cb81c63c564343b8f85186ea58b21 Mon Sep 17 00:00:00 2001 From: kennykos <“gkosmacher01@gmail.com”> Date: Wed, 15 Feb 2023 14:27:15 -0600 Subject: added info to df, functionality uptodate Notebook is good, is ready for pr before dev night, but I'd still like to clean things up before then --- ui_security_inventory_23_parsing.ipynb | 109 +++++++++++++++++++++++---------- 1 file changed, 77 insertions(+), 32 deletions(-) (limited to 'ui_security_inventory_23_parsing.ipynb') diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb index aef55ec..297203f 100644 --- a/ui_security_inventory_23_parsing.ipynb +++ b/ui_security_inventory_23_parsing.ipynb @@ -10,7 +10,9 @@ "import numpy as np\n", "import pandas as pd\n", "import yfinance as yf\n", - "import requests" + "import requests\n", + "import concurrent.futures\n", + "import json" ] }, { @@ -49,6 +51,7 @@ "op_df.insert(9, 'Industry', pd.NA)\n", "op_df.insert(10, 'Private Placement', False)\n", "op_df.insert(11, 'Ticker', pd.NA)\n", + "op_df.insert(11, 'Info', object)\n", "# op_df = op_df.insert(7, 'Bank', pd.NA)\n", "op_df.head()" ] @@ -305,13 +308,12 @@ { "cell_type": "code", "execution_count": null, - "id": "f2e232f5", + "id": "bc68a7ab", "metadata": {}, "outputs": [], "source": [ "%%time\n", - "company_name_to_ticker = dict()\n", - "for name in company_names:\n", + "def get_ticker(name):\n", " try:\n", " # try to get the ticker\n", " ticker = getTicker(name)\n", @@ -321,9 +323,16 @@ " short_name = name.split(' ')[0]\n", " ticker = getTicker(short_name)\n", " except:\n", - " # no ticker could be found, probably a private company, check to make sure\n", + " # no ticker could be found, probably a private company, check by hand to make sure\n", " ticker = 'NO_TICKER_FOUND'\n", - " company_name_to_ticker[name] = ticker" + " return (name, ticker)\n", + "\n", + "company_name_to_ticker = dict()\n", + "with concurrent.futures.ThreadPoolExecutor() as executor:\n", + " futures = [executor.submit(get_ticker, name) for name in company_names]\n", + " for future in concurrent.futures.as_completed(futures):\n", + " name, ticker = future.result()\n", + " company_name_to_ticker[name] = ticker" ] }, { @@ -337,76 +346,112 @@ { "cell_type": "code", "execution_count": null, - "id": "d80dca43", + "id": "e8f64024", "metadata": {}, "outputs": [], "source": [ - "company_name_to_ticker" + "for i in cb_df.index:\n", + " try:\n", + " cb_df.at[i,'Ticker'] = company_name_to_ticker[cb_df.at[i,'Company']]\n", + " except:\n", + " assert cb_df.at[i,'Company'] == 'Corporate Bonds', f\"Expected Cororate Bonds, got {cb_df.at[i,'Company']}\"" ] }, { "cell_type": "code", "execution_count": null, - "id": "921c344d", + "id": "04e49491", "metadata": {}, "outputs": [], "source": [ - "company_names" + "cb_df.head()" ] }, { - "cell_type": "code", - "execution_count": null, - "id": "7abeab84", + "cell_type": "markdown", + "id": "2342f360", "metadata": {}, - "outputs": [], "source": [ - "company_name_to_ticker[cb_df.at[104,'Company']]" + "## Get info from ticker " ] }, { "cell_type": "code", "execution_count": null, - "id": "e8f64024", + "id": "6fe3d0df", "metadata": {}, "outputs": [], "source": [ - "for i in cb_df.index:\n", - " try:\n", - " cb_df.at[i,'Ticker'] = company_name_to_ticker[cb_df.at[i,'Company']]\n", - " except:\n", - " assert cb_df.at[i,'Company'] == 'Corporate Bonds'" + "def get_info_from_ticker(ticker):\n", + " # Search for the company on Yahoo Finance\n", + " search_results = yf.Tickers(ticker)\n", + " return search_results.tickers[ticker].info" ] }, { "cell_type": "code", "execution_count": null, - "id": "04e49491", - "metadata": {}, + "id": "9ed7c317", + "metadata": { + "scrolled": true + }, "outputs": [], "source": [ - "cb_df.head()" + "%%time\n", + "def get_info(name):\n", + " try:\n", + " ticker = company_name_to_ticker[name]\n", + " info = get_info_from_ticker(ticker)\n", + " except:\n", + " info = 'No Info Found'\n", + " return (name, info)\n", + "\n", + "## use parallelization to speed up this process\n", + "company_info_dict = dict()\n", + "with concurrent.futures.ThreadPoolExecutor() as executor:\n", + " futures = [executor.submit(get_info, name) for name in company_names]\n", + " for future in concurrent.futures.as_completed(futures):\n", + " name, info = future.result()\n", + " company_info_dict[name] = info" ] }, { "cell_type": "markdown", - "id": "2342f360", + "id": "180e9785", "metadata": {}, "source": [ - "## Get info from ticker " + "##### Link Info to Company, saved as a json dump in the dataframe" ] }, { "cell_type": "code", "execution_count": null, - "id": "6fe3d0df", + "id": "570e70b3", + "metadata": { + "scrolled": true + }, + "outputs": [], + "source": [ + "for i in cb_df.index:\n", + " if cb_df.at[i,'Company'] == 'Corporate Bonds':\n", + " continue\n", + " if company_info_dict[cb_df.at[i,'Company']] is None:\n", + " continue\n", + " info_dict = dict(company_info_dict[cb_df.at[i,'Company']])\n", + " json_str = json.dumps(my_dict)\n", + " cb_df.at[i,'Info'] = json_str\n", + "\n", + "# assert cb_df.at[i,'Company'] == 'Corporate Bonds', f\"Expected Cororate Bonds, got {cb_df.at[i,'Company']}\"" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "3bd1606d", "metadata": {}, "outputs": [], "source": [ - "def get_info_from_ticker(ticker):\n", - " # Search for the company on Yahoo Finance\n", - " search_results = yf.Tickers(company_name)\n", - " return search_results.tickers['T'].info" + "cb_df.head()" ] } ], @@ -426,7 +471,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.10.9" + "version": "3.10.6" }, "vscode": { "interpreter": { -- cgit v1.2.3