6 turn UI security inventory 23 parsingipynb into python script (#5)

* commit clean notebook

* initilizing investments cleaning script

* finishing up command line prompts, being processing data

* investment_cleaning.py script is implemented

* Investment Cleaning Script is Implemented

---------

Co-authored-by: kennykos <“gkosmacher01@gmail.com”>

1 files changed, 35 insertions, 19 deletions

diff --git a/ui_security_inventory_23_parsing.ipynb b/ui_security_inventory_23_parsing.ipynb
index 7b75cb0..98d1776 100644
--- a/ui_security_inventory_23_parsing.ipynb
+++ b/ui_security_inventory_23_parsing.ipynb
@@ -22,12 +22,14 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "file_path_23 = \"./data/23-138.Records.xlsx\"\n",
+    "input_file_path = \"./data/23-138.Records.xlsx\"\n",
+    "output_file_path = 'data/investments_fy2021.csv'\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')"
+    "df = pd.read_excel(input_file_path, parse_dates=True, skiprows=6).dropna(how='all')"
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "538233a8",
    "metadata": {},
@@ -52,11 +54,11 @@
     "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()"
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "cc1f037f",
    "metadata": {},
@@ -95,6 +97,7 @@
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "274d0b39",
    "metadata": {},
@@ -110,12 +113,13 @@
    "outputs": [],
    "source": [
     "cb_df = op_df[op_df['Asset Type'].str.contains(\"Corporate Bonds\", na=False)]\n",
-    "print(\"Corperate Bond Totals\")\n",
+    "print(\"Corporate 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\"]))"
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "d4aba4d7",
    "metadata": {},
@@ -124,6 +128,7 @@
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "9879b032",
    "metadata": {},
@@ -278,6 +283,7 @@
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "8b78d9cd",
    "metadata": {},
@@ -301,8 +307,8 @@
     "    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"
+    "    ticker = data['quotes'][0]['symbol']\n",
+    "    return ticker"
    ]
   },
   {
@@ -336,6 +342,7 @@
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "e3009168",
    "metadata": {},
@@ -368,6 +375,7 @@
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "2342f360",
    "metadata": {},
@@ -416,6 +424,7 @@
    ]
   },
   {
+   "attachments": {},
    "cell_type": "markdown",
    "id": "180e9785",
    "metadata": {},
@@ -432,19 +441,16 @@
    },
    "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",
-    "    try:\n",
-    "        info_dict = dict(company_info_dict[cb_df.at[i,'Company']])\n",
-    "        json_str = json.dumps(info_dict)\n",
-    "        cb_df.at[i,'Info'] = json_str\n",
-    "    except:\n",
-    "        print(company_info_dict[cb_df.at[i,'Company']])\n",
+    "# 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']}\""
+    "# #         assert cb_df.at[i,'Company'] == 'Corporate Bonds', f\"Expected Cororate Bonds, got {cb_df.at[i,'Company']}\""
    ]
   },
   {
@@ -456,6 +462,16 @@
    "source": [
     "cb_df.head()"
    ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "683a51a5",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "cb_df.to_csv(output_file_path)"
+   ]
   }
  ],
  "metadata": {
@@ -474,7 +490,7 @@
    "name": "python",
    "nbconvert_exporter": "python",
    "pygments_lexer": "ipython3",
-   "version": "3.10.6"
+   "version": "3.10.9"
   },
   "vscode": {
    "interpreter": {