summaryrefslogtreecommitdiff
path: root/ui_security_inventory_23_parsing.ipynb
diff options
context:
space:
mode:
Diffstat (limited to 'ui_security_inventory_23_parsing.ipynb')
-rw-r--r--ui_security_inventory_23_parsing.ipynb484
1 files changed, 484 insertions, 0 deletions
diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb
new file mode 100644
index 0000000..297203f
--- /dev/null
+++ b/ui_security_inventory_23_parsing.ipynb
@@ -0,0 +1,484 @@
+{
+ "cells": [
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "8ce6b023",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import numpy as np\n",
+ "import pandas as pd\n",
+ "import yfinance as yf\n",
+ "import requests\n",
+ "import concurrent.futures\n",
+ "import json"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "8304939a",
+ "metadata": {},
+ "outputs": [],
+ "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": null,
+ "id": "96dee5bc",
+ "metadata": {},
+ "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",
+ "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.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()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "cc1f037f",
+ "metadata": {},
+ "source": [
+ "#### Add the Bank and Asset Type "
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "29c95d09",
+ "metadata": {},
+ "outputs": [],
+ "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": null,
+ "id": "3ea40a3e",
+ "metadata": {},
+ "outputs": [],
+ "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": null,
+ "id": "fe124c92",
+ "metadata": {},
+ "outputs": [],
+ "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": null,
+ "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": null,
+ "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": null,
+ "id": "e7e06dfe",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "cb_df"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "8b78d9cd",
+ "metadata": {},
+ "source": [
+ "##### get ticker"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "ba37103f",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "# 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": "bc68a7ab",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "%%time\n",
+ "def get_ticker(name):\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 by hand to make sure\n",
+ " ticker = 'NO_TICKER_FOUND'\n",
+ " 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"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "e3009168",
+ "metadata": {},
+ "source": [
+ "##### match company name to ticker in DF"
+ ]
+ },
+ {
+ "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', f\"Expected Cororate Bonds, got {cb_df.at[i,'Company']}\""
+ ]
+ },
+ {
+ "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 "
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "6fe3d0df",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "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": "9ed7c317",
+ "metadata": {
+ "scrolled": true
+ },
+ "outputs": [],
+ "source": [
+ "%%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": "180e9785",
+ "metadata": {},
+ "source": [
+ "##### Link Info to Company, saved as a json dump in the dataframe"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "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": [
+ "cb_df.head()"
+ ]
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 3 (ipykernel)",
+ "language": "python",
+ "name": "python3"
+ },
+ "language_info": {
+ "codemirror_mode": {
+ "name": "ipython",
+ "version": 3
+ },
+ "file_extension": ".py",
+ "mimetype": "text/x-python",
+ "name": "python",
+ "nbconvert_exporter": "python",
+ "pygments_lexer": "ipython3",
+ "version": "3.10.6"
+ },
+ "vscode": {
+ "interpreter": {
+ "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
+ }
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 5
+}
generated by cgit v1.2.3 (git 2.25.1) at 2025-12-06 06:15:36 +0000