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