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