{
 "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"
   ]
  },
  {
   "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 = 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": "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": "e3009168",
   "metadata": {},
   "source": [
    "##### 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 "
   ]
  },
  {
   "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(company_name)\n",
    "    return search_results.tickers['T'].info"
   ]
  }
 ],
 "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.9"
  },
  "vscode": {
   "interpreter": {
    "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}