diff options
Diffstat (limited to 'ui_security_inventory_23_parsing.ipynb')
| -rw-r--r-- | ui_security_inventory_23_parsing.ipynb | 54 |
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": { |