{ "cells": [ { "cell_type": "markdown", "id": "7ed2159f-a99a-43e1-bb37-912d6189b331", "metadata": {}, "source": [ "### Searching for KungFu Bonds (Chinese-issued U.S. dollars bonds) with the Data Library - Search function" ] }, { "cell_type": "code", "execution_count": 1, "id": "8418cdcd-4faa-4d74-8cf3-a0de0168384c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# [Step 1] Set up the environment\n", "import lseg.data as ld\n", "from lseg.data.content import search\n", "\n", "import datetime\n", "import pandas as pd\n", "ld.open_session()" ] }, { "cell_type": "code", "execution_count": 2, "id": "66b11af3-46fa-4d97-911b-152d6ace4938", "metadata": { "scrolled": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\U6086063\\Miniconda3\\envs\\ldlib-24\\lib\\site-packages\\lseg\\data\\content\\search\\_data_provider.py:298:UserWarning: Search result for None not full.\n", "Result is maxed at 10000 while the total is 11864 rows.\n", "Requested - 10000, skipped - 0 rows.\n", "C:\\Users\\U6086063\\Miniconda3\\envs\\ldlib-24\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py:1057:RuntimeWarning: invalid value encountered in cast\n", "C:\\Users\\U6086063\\Miniconda3\\envs\\ldlib-24\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py:1081:RuntimeWarning: invalid value encountered in cast\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RICRCSTRBC2012LeafIssueDateEOMAmountOutstandingPricingRedemDateIssuerLegalNamePricingClosingYieldCurrentYieldFaceIssuedTotalEOMPriceChangeRCSBondGradeLeafEOMPriceReturnRCSRiskOrganisationCountry
0VG226919449=Highway & Bridge Construction2021-04-01500000000NaTPowerchina Roadbridge Group (British Virgin Is...8.3533713.158974500000000.00.625Investment Grade0.0G:53
1CN207889792=Auto & Truck Manufacturers (NEC)2019-12-09500000000NaTGEELY AUTOMOBILE HOLDINGS LIMITED8.9607894.020101500000000.00.125High Yield0.124602G:3H
2KY230704619=Multiline Utilities2021-06-023000000002026-06-02Cheung Kong Infrastructure Finance (BVI) Limited20.9311425.42811300000000.00.625<NA>0.803101G:3H
3CH278532615=Real Estate Rental, Development & Operations (...2024-03-201571186629NaTCHINA AOYUAN GROUP LIMITED<NA>0.01571186629.0-0.25<NA>-33.333333G:53
4CN223856128=Banks (NEC)2020-11-1828000000002025-11-18Jiao Tong Yin Hang Gu Fen You Xian Gong Si5.0418343.8517292800000000.00.56High Yield0.55382G:53
..........................................
9995<NA>Business Support Services (NEC)2024-06-25<NA>NaTBOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED<NA><NA>10000000.0<NA><NA><NA>G:3H
9996<NA>Business Support Services (NEC)2022-07-27<NA>NaTBOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED<NA><NA>2400000.0<NA><NA><NA>G:3H
9997<NA>Banks (NEC)2022-04-29<NA>NaTBank of China Ltd (Macau Branch)<NA><NA>6500000.0<NA><NA><NA>G:53
9998<NA>Corporate Financial Services (NEC)2024-09-18<NA>NaTCSI Financial Products Limited<NA><NA>2000000.0<NA><NA><NA>G:3H
9999<NA>Corporate Financial Services (NEC)2024-09-19<NA>NaTCSI Financial Products Limited<NA><NA>300000.0<NA><NA><NA>G:3H
\n", "

10000 rows × 13 columns

\n", "
" ], "text/plain": [ " RIC RCSTRBC2012Leaf \\\n", "0 VG226919449= Highway & Bridge Construction \n", "1 CN207889792= Auto & Truck Manufacturers (NEC) \n", "2 KY230704619= Multiline Utilities \n", "3 CH278532615= Real Estate Rental, Development & Operations (... \n", "4 CN223856128= Banks (NEC) \n", "... ... ... \n", "9995 Business Support Services (NEC) \n", "9996 Business Support Services (NEC) \n", "9997 Banks (NEC) \n", "9998 Corporate Financial Services (NEC) \n", "9999 Corporate Financial Services (NEC) \n", "\n", " IssueDate EOMAmountOutstanding PricingRedemDate \\\n", "0 2021-04-01 500000000 NaT \n", "1 2019-12-09 500000000 NaT \n", "2 2021-06-02 300000000 2026-06-02 \n", "3 2024-03-20 1571186629 NaT \n", "4 2020-11-18 2800000000 2025-11-18 \n", "... ... ... ... \n", "9995 2024-06-25 NaT \n", "9996 2022-07-27 NaT \n", "9997 2022-04-29 NaT \n", "9998 2024-09-18 NaT \n", "9999 2024-09-19 NaT \n", "\n", " IssuerLegalName PricingClosingYield \\\n", "0 Powerchina Roadbridge Group (British Virgin Is... 8.353371 \n", "1 GEELY AUTOMOBILE HOLDINGS LIMITED 8.960789 \n", "2 Cheung Kong Infrastructure Finance (BVI) Limited 20.931142 \n", "3 CHINA AOYUAN GROUP LIMITED \n", "4 Jiao Tong Yin Hang Gu Fen You Xian Gong Si 5.041834 \n", "... ... ... \n", "9995 BOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED \n", "9996 BOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED \n", "9997 Bank of China Ltd (Macau Branch) \n", "9998 CSI Financial Products Limited \n", "9999 CSI Financial Products Limited \n", "\n", " CurrentYield FaceIssuedTotal EOMPriceChange RCSBondGradeLeaf \\\n", "0 3.158974 500000000.0 0.625 Investment Grade \n", "1 4.020101 500000000.0 0.125 High Yield \n", "2 5.42811 300000000.0 0.625 \n", "3 0.0 1571186629.0 -0.25 \n", "4 3.851729 2800000000.0 0.56 High Yield \n", "... ... ... ... ... \n", "9995 10000000.0 \n", "9996 2400000.0 \n", "9997 6500000.0 \n", "9998 2000000.0 \n", "9999 300000.0 \n", "\n", " EOMPriceReturn RCSRiskOrganisationCountry \n", "0 0.0 G:53 \n", "1 0.124602 G:3H \n", "2 0.803101 G:3H \n", "3 -33.333333 G:53 \n", "4 0.55382 G:53 \n", "... ... ... \n", "9995 G:3H \n", "9996 G:3H \n", "9997 G:53 \n", "9998 G:3H \n", "9999 G:3H \n", "\n", "[10000 rows x 13 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# [Step 2] Using search function to retrieve the data\n", "df = ld.discovery.search(\n", " view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,\n", " top = 10000,\n", " filter = \"((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\\G:K\\G:S\\G:6\\G:53'))\",\n", " select = \"RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn,RCSRiskOrganisationCountry\",\n", ")\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 3, "id": "844d1ae5-4c6f-426b-9000-2540954428a3", "metadata": { "scrolled": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\U6086063\\Miniconda3\\envs\\ldlib-24\\lib\\site-packages\\lseg\\data\\content\\search\\_data_provider.py:298:UserWarning: Search result for None not full.\n", "Result is maxed at 10000 while the total is 11864 rows.\n", "Requested - 10000, skipped - 0 rows.\n", "C:\\Users\\U6086063\\Miniconda3\\envs\\ldlib-24\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py:1057:RuntimeWarning: invalid value encountered in cast\n", "C:\\Users\\U6086063\\Miniconda3\\envs\\ldlib-24\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py:1081:RuntimeWarning: invalid value encountered in cast\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RICRCSTRBC2012LeafIssueDateEOMAmountOutstandingPricingRedemDateIssuerLegalNamePricingClosingYieldCurrentYieldFaceIssuedTotalEOMPriceChangeRCSBondGradeLeafEOMPriceReturn
0VG226919449=Highway & Bridge Construction2021-04-01500000000NaTPowerchina Roadbridge Group (British Virgin Is...8.3533713.158974500000000.00.625Investment Grade0.0
1CN207889792=Auto & Truck Manufacturers (NEC)2019-12-09500000000NaTGEELY AUTOMOBILE HOLDINGS LIMITED8.9607894.020101500000000.00.125High Yield0.124602
2KY230704619=Multiline Utilities2021-06-023000000002026-06-02Cheung Kong Infrastructure Finance (BVI) Limited20.9311425.42811300000000.00.625<NA>0.803101
3CH278532615=Real Estate Rental, Development & Operations (...2024-03-201571186629NaTCHINA AOYUAN GROUP LIMITED<NA>0.01571186629.0-0.25<NA>-33.333333
4CN223856128=Banks (NEC)2020-11-1828000000002025-11-18Jiao Tong Yin Hang Gu Fen You Xian Gong Si5.0418343.8517292800000000.00.56High Yield0.55382
.......................................
9995<NA>Business Support Services (NEC)2024-06-25<NA>NaTBOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED<NA><NA>10000000.0<NA><NA><NA>
9996<NA>Business Support Services (NEC)2022-07-27<NA>NaTBOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED<NA><NA>2400000.0<NA><NA><NA>
9997<NA>Banks (NEC)2022-04-29<NA>NaTBank of China Ltd (Macau Branch)<NA><NA>6500000.0<NA><NA><NA>
9998<NA>Corporate Financial Services (NEC)2024-09-18<NA>NaTCSI Financial Products Limited<NA><NA>2000000.0<NA><NA><NA>
9999<NA>Corporate Financial Services (NEC)2024-09-19<NA>NaTCSI Financial Products Limited<NA><NA>300000.0<NA><NA><NA>
\n", "

10000 rows × 12 columns

\n", "
" ], "text/plain": [ " RIC RCSTRBC2012Leaf \\\n", "0 VG226919449= Highway & Bridge Construction \n", "1 CN207889792= Auto & Truck Manufacturers (NEC) \n", "2 KY230704619= Multiline Utilities \n", "3 CH278532615= Real Estate Rental, Development & Operations (... \n", "4 CN223856128= Banks (NEC) \n", "... ... ... \n", "9995 Business Support Services (NEC) \n", "9996 Business Support Services (NEC) \n", "9997 Banks (NEC) \n", "9998 Corporate Financial Services (NEC) \n", "9999 Corporate Financial Services (NEC) \n", "\n", " IssueDate EOMAmountOutstanding PricingRedemDate \\\n", "0 2021-04-01 500000000 NaT \n", "1 2019-12-09 500000000 NaT \n", "2 2021-06-02 300000000 2026-06-02 \n", "3 2024-03-20 1571186629 NaT \n", "4 2020-11-18 2800000000 2025-11-18 \n", "... ... ... ... \n", "9995 2024-06-25 NaT \n", "9996 2022-07-27 NaT \n", "9997 2022-04-29 NaT \n", "9998 2024-09-18 NaT \n", "9999 2024-09-19 NaT \n", "\n", " IssuerLegalName PricingClosingYield \\\n", "0 Powerchina Roadbridge Group (British Virgin Is... 8.353371 \n", "1 GEELY AUTOMOBILE HOLDINGS LIMITED 8.960789 \n", "2 Cheung Kong Infrastructure Finance (BVI) Limited 20.931142 \n", "3 CHINA AOYUAN GROUP LIMITED \n", "4 Jiao Tong Yin Hang Gu Fen You Xian Gong Si 5.041834 \n", "... ... ... \n", "9995 BOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED \n", "9996 BOCOM INTERNATIONAL PRODUCT AND SOLUTION LIMITED \n", "9997 Bank of China Ltd (Macau Branch) \n", "9998 CSI Financial Products Limited \n", "9999 CSI Financial Products Limited \n", "\n", " CurrentYield FaceIssuedTotal EOMPriceChange RCSBondGradeLeaf \\\n", "0 3.158974 500000000.0 0.625 Investment Grade \n", "1 4.020101 500000000.0 0.125 High Yield \n", "2 5.42811 300000000.0 0.625 \n", "3 0.0 1571186629.0 -0.25 \n", "4 3.851729 2800000000.0 0.56 High Yield \n", "... ... ... ... ... \n", "9995 10000000.0 \n", "9996 2400000.0 \n", "9997 6500000.0 \n", "9998 2000000.0 \n", "9999 300000.0 \n", "\n", " EOMPriceReturn \n", "0 0.0 \n", "1 0.124602 \n", "2 0.803101 \n", "3 -33.333333 \n", "4 0.55382 \n", "... ... \n", "9995 \n", "9996 \n", "9997 \n", "9998 \n", "9999 \n", "\n", "[10000 rows x 12 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# [Step 2] Using search function to retrieve the data\n", "df = ld.discovery.search(\n", " view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,\n", " top = 10000,\n", " filter = \"((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\\G:K\\G:S\\G:6\\G:53'))\",\n", " select = \"RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn\",\n", ")\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 5, "id": "7b6d1265-cdb4-41eb-baa2-0754bd352293", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[{'Label': 'G:3H', 'Count': 6898},\n", " {'Label': 'G:53', 'Count': 4694},\n", " {'Label': 'G:7U', 'Count': 158},\n", " {'Label': 'G:3I', 'Count': 114}]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "response = search.Definition(\n", " view = search.Views.GOV_CORP_INSTRUMENTS,\n", " filter = \"((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\\G:K\\G:S\\G:6\\G:53'))\",\n", " top = 0,\n", " navigators = \"RCSRiskOrganisationCountry\"\n", ").get_data()\n", "response.data.raw[\"Navigators\"][\"RCSRiskOrganisationCountry\"][\"Buckets\"]" ] }, { "cell_type": "code", "execution_count": 6, "id": "1d2899c2-0bac-4e8e-8646-7ec112122750", "metadata": {}, "outputs": [], "source": [ "filter1 = \"(RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3I')\"\n", "filter2 = \"(RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U')\"\n", "\n", "def bond_search(filter):\n", " response = search.Definition(\n", " view = search.Views.GOV_CORP_INSTRUMENTS,\n", " filter = f\"((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ({filter} and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\\G:K\\G:S\\G:6\\G:53'))\",\n", " top = 10000,\n", " select = \"RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn\"\n", " ).get_data()\n", " df = response.data.df\n", " return df\n", "\n", "df1 = bond_search(filter1)\n", "df2 = bond_search(filter2)" ] }, { "cell_type": "code", "execution_count": 7, "id": "669bdcf5-5398-4192-a735-3d65f7a3468f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RICRCSTRBC2012LeafIssueDateEOMAmountOutstandingPricingRedemDateIssuerLegalNamePricingClosingYieldCurrentYieldFaceIssuedTotalEOMPriceChangeRCSBondGradeLeafEOMPriceReturn
0VG226919449=Highway & Bridge Construction2021-04-01500000000NaTPowerchina Roadbridge Group (British Virgin Is...8.3533713.158974500000000.00.625Investment Grade0.0
1CH278532615=Real Estate Rental, Development & Operations (...2024-03-201571186629NaTCHINA AOYUAN GROUP LIMITED<NA>0.01571186629.0-0.25<NA>-33.333333
2CN223856128=Banks (NEC)2020-11-1828000000002025-11-18Jiao Tong Yin Hang Gu Fen You Xian Gong Si5.0418343.8517292800000000.00.56High Yield0.55382
3CN245541155=Construction & Engineering (NEC)2022-04-145000000002027-04-14DIANJIAN HAIYU LIMITED5.0999414.336735500000000.00.375Investment Grade0.377728
4CN209223643=Banks (NEC)2019-12-13<NA>NaTChina Construction Bank Corp (Hong Kong Branch)<NA><NA>500000000.0<NA><NA><NA>
.......................................
11859CN254312533=Residential Real Estate Rental & Development2022-10-20<NA>NaTLandsea Green Management Limited<NA><NA>118994400.0<NA><NA><NA>
11860HK208588311=Real Estate Rental, Development & Operations (...2019-12-12288840000NaTHua Nan Cheng Kong Gu You Xian Gong Si<NA><NA>350000000.00.0<NA><NA>
11861XS2369300681=CDCMCorporate Financial Services (NEC)2021-07-28<NA>NaTCSI Financial Products Limited<NA><NA>7700000.0<NA><NA><NA>
11862VG233315486=Real Estate Rental, Development & Operations (...2021-05-17230000000NaTJIAYUAN INTERNATIONAL GROUP LIMITED<NA><NA>230000000.0-0.5<NA><NA>
11863<NA>Investment Holding Companies (NEC)2005-03-16<NA>NaTNEW ASAT (FINANCE) LIMITED<NA><NA>150000000.0<NA><NA><NA>
\n", "

11864 rows × 12 columns

\n", "
" ], "text/plain": [ " RIC RCSTRBC2012Leaf \\\n", "0 VG226919449= Highway & Bridge Construction \n", "1 CH278532615= Real Estate Rental, Development & Operations (... \n", "2 CN223856128= Banks (NEC) \n", "3 CN245541155= Construction & Engineering (NEC) \n", "4 CN209223643= Banks (NEC) \n", "... ... ... \n", "11859 CN254312533= Residential Real Estate Rental & Development \n", "11860 HK208588311= Real Estate Rental, Development & Operations (... \n", "11861 XS2369300681=CDCM Corporate Financial Services (NEC) \n", "11862 VG233315486= Real Estate Rental, Development & Operations (... \n", "11863 Investment Holding Companies (NEC) \n", "\n", " IssueDate EOMAmountOutstanding PricingRedemDate \\\n", "0 2021-04-01 500000000 NaT \n", "1 2024-03-20 1571186629 NaT \n", "2 2020-11-18 2800000000 2025-11-18 \n", "3 2022-04-14 500000000 2027-04-14 \n", "4 2019-12-13 NaT \n", "... ... ... ... \n", "11859 2022-10-20 NaT \n", "11860 2019-12-12 288840000 NaT \n", "11861 2021-07-28 NaT \n", "11862 2021-05-17 230000000 NaT \n", "11863 2005-03-16 NaT \n", "\n", " IssuerLegalName PricingClosingYield \\\n", "0 Powerchina Roadbridge Group (British Virgin Is... 8.353371 \n", "1 CHINA AOYUAN GROUP LIMITED \n", "2 Jiao Tong Yin Hang Gu Fen You Xian Gong Si 5.041834 \n", "3 DIANJIAN HAIYU LIMITED 5.099941 \n", "4 China Construction Bank Corp (Hong Kong Branch) \n", "... ... ... \n", "11859 Landsea Green Management Limited \n", "11860 Hua Nan Cheng Kong Gu You Xian Gong Si \n", "11861 CSI Financial Products Limited \n", "11862 JIAYUAN INTERNATIONAL GROUP LIMITED \n", "11863 NEW ASAT (FINANCE) LIMITED \n", "\n", " CurrentYield FaceIssuedTotal EOMPriceChange RCSBondGradeLeaf \\\n", "0 3.158974 500000000.0 0.625 Investment Grade \n", "1 0.0 1571186629.0 -0.25 \n", "2 3.851729 2800000000.0 0.56 High Yield \n", "3 4.336735 500000000.0 0.375 Investment Grade \n", "4 500000000.0 \n", "... ... ... ... ... \n", "11859 118994400.0 \n", "11860 350000000.0 0.0 \n", "11861 7700000.0 \n", "11862 230000000.0 -0.5 \n", "11863 150000000.0 \n", "\n", " EOMPriceReturn \n", "0 0.0 \n", "1 -33.333333 \n", "2 0.55382 \n", "3 0.377728 \n", "4 \n", "... ... \n", "11859 \n", "11860 \n", "11861 \n", "11862 \n", "11863 \n", "\n", "[11864 rows x 12 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = pd.concat([df1, df2], ignore_index=True)\n", "df" ] }, { "cell_type": "code", "execution_count": 8, "id": "b2f51000-f24c-4811-b0b5-d009b4f5df5f", "metadata": {}, "outputs": [ { "data": { "text/html": [ " \n", " " ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.plotly.v1+json": { "config": { "plotlyServerURL": "https://plot.ly" }, "data": [ { "domain": { "x": [ 0, 1 ], "y": [ 0, 1 ] }, "hovertemplate": "RCSTRBC2012Leaf=%{label}
RIC=%{value}", "labels": [ "Corporate Banks", "Banks (NEC)", "Corporate Financial Services (NEC)", "Real Estate Rental, Development & Operations (NEC)", "Investment Management & Fund Operators (NEC)", "Construction & Engineering (NEC)", "Investment Banking & Brokerage Services (NEC)", "Commercial Leasing", "Diversified Investment Services", "Investment Holding Companies (NEC)" ], "legendgroup": "", "name": "", "showlegend": true, "type": "pie", "values": [ 2523, 2080, 530, 368, 228, 192, 83, 82, 76, 67 ] } ], "layout": { "autosize": true, "legend": { "tracegroupgap": 0 }, "piecolorway": [ "rgb(103,0,31)", "rgb(178,24,43)", "rgb(214,96,77)", "rgb(244,165,130)", "rgb(253,219,199)", "rgb(247,247,247)", "rgb(209,229,240)", "rgb(146,197,222)", "rgb(67,147,195)", "rgb(33,102,172)", "rgb(5,48,97)" ], "template": { "data": { "bar": [ { "error_x": { "color": "#2a3f5f" }, "error_y": { "color": "#2a3f5f" }, "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "bar" } ], "barpolar": [ { "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "barpolar" } ], "carpet": [ { "aaxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "baxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "type": "carpet" } ], "choropleth": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "choropleth" } ], "contour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "contour" } ], "contourcarpet": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "contourcarpet" } ], "heatmap": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmap" } ], "heatmapgl": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmapgl" } ], "histogram": [ { "marker": { "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "histogram" } ], "histogram2d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2d" } ], "histogram2dcontour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2dcontour" } ], "mesh3d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "mesh3d" } ], "parcoords": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "parcoords" } ], "pie": [ { "automargin": true, "type": "pie" } ], "scatter": [ { "fillpattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 }, "type": "scatter" } ], "scatter3d": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatter3d" } ], "scattercarpet": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattercarpet" } ], "scattergeo": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergeo" } ], "scattergl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergl" } ], "scattermapbox": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattermapbox" } ], "scatterpolar": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolar" } ], "scatterpolargl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolargl" } ], "scatterternary": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterternary" } ], "surface": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "surface" } ], "table": [ { "cells": { "fill": { "color": "#EBF0F8" }, "line": { "color": "white" } }, "header": { "fill": { "color": "#C8D4E3" }, "line": { "color": "white" } }, "type": "table" } ] }, "layout": { "annotationdefaults": { "arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1 }, "autotypenumbers": "strict", "coloraxis": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "colorscale": { "diverging": [ [ 0, "#8e0152" ], [ 0.1, "#c51b7d" ], [ 0.2, "#de77ae" ], [ 0.3, "#f1b6da" ], [ 0.4, "#fde0ef" ], [ 0.5, "#f7f7f7" ], [ 0.6, "#e6f5d0" ], [ 0.7, "#b8e186" ], [ 0.8, "#7fbc41" ], [ 0.9, "#4d9221" ], [ 1, "#276419" ] ], "sequential": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "sequentialminus": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ] }, "colorway": [ "#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52" ], "font": { "color": "#2a3f5f" }, "geo": { "bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white" }, "hoverlabel": { "align": "left" }, "hovermode": "closest", "mapbox": { "style": "light" }, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": { "angularaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "radialaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "scene": { "xaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "yaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "zaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" } }, "shapedefaults": { "line": { "color": "#2a3f5f" } }, "ternary": { "aaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "baxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "caxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "title": { "x": 0.05 }, "xaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 }, "yaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 } } }, "title": { "text": "TRBC Pie" } } }, "text/html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# [Step 3] Visualize the Kungfu bonds\n", "# 3.1 ) TRBC Pie\n", "import plotly.express as px\n", "rt = df.groupby(\"RCSTRBC2012Leaf\",as_index=False).agg('count')\n", "rt = rt.sort_values('RIC', ascending = False).head(10)\n", "fig = px.pie(rt, values='RIC', names='RCSTRBC2012Leaf', title='TRBC Pie',color_discrete_sequence=px.colors.sequential.RdBu)\n", "fig.show()" ] }, { "cell_type": "code", "execution_count": 9, "id": "f7c774d9-770d-4607-89b3-adbdf985ff51", "metadata": {}, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "config": { "plotlyServerURL": "https://plot.ly" }, "data": [ { "domain": { "x": [ 0, 1 ], "y": [ 0, 1 ] }, "hovertemplate": "RCSBondGradeLeaf=%{label}
RIC=%{value}", "labels": [ "High Yield", "Investment Grade", "No Grade" ], "legendgroup": "", "name": "", "showlegend": true, "type": "pie", "values": [ 148, 1017, 6181 ] } ], "layout": { "autosize": true, "legend": { "tracegroupgap": 0 }, "piecolorway": [ "rgb(103,0,31)", "rgb(178,24,43)", "rgb(214,96,77)", "rgb(244,165,130)", "rgb(253,219,199)", "rgb(247,247,247)", "rgb(209,229,240)", "rgb(146,197,222)", "rgb(67,147,195)", "rgb(33,102,172)", "rgb(5,48,97)" ], "template": { "data": { "bar": [ { "error_x": { "color": "#2a3f5f" }, "error_y": { "color": "#2a3f5f" }, "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "bar" } ], "barpolar": [ { "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "barpolar" } ], "carpet": [ { "aaxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "baxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "type": "carpet" } ], "choropleth": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "choropleth" } ], "contour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "contour" } ], "contourcarpet": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "contourcarpet" } ], "heatmap": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmap" } ], "heatmapgl": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmapgl" } ], "histogram": [ { "marker": { "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "histogram" } ], "histogram2d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2d" } ], "histogram2dcontour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2dcontour" } ], "mesh3d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "mesh3d" } ], "parcoords": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "parcoords" } ], "pie": [ { "automargin": true, "type": "pie" } ], "scatter": [ { "fillpattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 }, "type": "scatter" } ], "scatter3d": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatter3d" } ], "scattercarpet": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattercarpet" } ], "scattergeo": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergeo" } ], "scattergl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergl" } ], "scattermapbox": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattermapbox" } ], "scatterpolar": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolar" } ], "scatterpolargl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolargl" } ], "scatterternary": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterternary" } ], "surface": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "surface" } ], "table": [ { "cells": { "fill": { "color": "#EBF0F8" }, "line": { "color": "white" } }, "header": { "fill": { "color": "#C8D4E3" }, "line": { "color": "white" } }, "type": "table" } ] }, "layout": { "annotationdefaults": { "arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1 }, "autotypenumbers": "strict", "coloraxis": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "colorscale": { "diverging": [ [ 0, "#8e0152" ], [ 0.1, "#c51b7d" ], [ 0.2, "#de77ae" ], [ 0.3, "#f1b6da" ], [ 0.4, "#fde0ef" ], [ 0.5, "#f7f7f7" ], [ 0.6, "#e6f5d0" ], [ 0.7, "#b8e186" ], [ 0.8, "#7fbc41" ], [ 0.9, "#4d9221" ], [ 1, "#276419" ] ], "sequential": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "sequentialminus": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ] }, "colorway": [ "#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52" ], "font": { "color": "#2a3f5f" }, "geo": { "bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white" }, "hoverlabel": { "align": "left" }, "hovermode": "closest", "mapbox": { "style": "light" }, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": { "angularaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "radialaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "scene": { "xaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "yaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "zaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" } }, "shapedefaults": { "line": { "color": "#2a3f5f" } }, "ternary": { "aaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "baxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "caxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "title": { "x": 0.05 }, "xaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 }, "yaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 } } }, "title": { "text": "Bond Grade Pie" } } }, "text/html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# 3.2 ) Bond Grade Pie \n", "df['RCSBondGradeLeaf'].fillna(\"No Grade\", inplace=True)\n", "grad = df.groupby(\"RCSBondGradeLeaf\",as_index=False).agg('count')\n", "fig = px.pie(grad, values='RIC', names='RCSBondGradeLeaf', title='Bond Grade Pie',color_discrete_sequence=px.colors.sequential.RdBu)\n", "fig.show()" ] }, { "cell_type": "code", "execution_count": 10, "id": "d61f2857-e9e1-4acc-a341-d2a734d61e1c", "metadata": {}, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "config": { "plotlyServerURL": "https://plot.ly" }, "data": [ { "alignmentgroup": "True", "hovertemplate": "index=%{x}
FaceIssuedTotal=%{y}", "legendgroup": "", "marker": { "color": "rgb(103,0,31)", "pattern": { "shape": "" } }, "name": "", "offsetgroup": "", "orientation": "v", "showlegend": false, "textposition": "auto", "type": "bar", "x": [ "2023-09-29T00:00:00", "2023-10-31T00:00:00", "2023-11-30T00:00:00", "2023-12-29T00:00:00", "2024-01-31T00:00:00", "2024-02-29T00:00:00", "2024-03-29T00:00:00", "2024-04-30T00:00:00", "2024-05-31T00:00:00", "2024-06-28T00:00:00", "2024-07-31T00:00:00", "2024-08-30T00:00:00", "2024-09-30T00:00:00" ], "xaxis": "x", "y": [ 10996556350, 7496302153, 12307444328, 8700238738, 15092956440, 7101940306.59, 21416648403, 24604791243, 34628576731, 26024643641, 35853141562, 33136439540, 43152206960 ], "yaxis": "y" } ], "layout": { "autosize": true, "barmode": "relative", "legend": { "tracegroupgap": 0 }, "template": { "data": { "bar": [ { "error_x": { "color": "#2a3f5f" }, "error_y": { "color": "#2a3f5f" }, "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "bar" } ], "barpolar": [ { "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "barpolar" } ], "carpet": [ { "aaxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "baxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "type": "carpet" } ], "choropleth": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "choropleth" } ], "contour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "contour" } ], "contourcarpet": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "contourcarpet" } ], "heatmap": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmap" } ], "heatmapgl": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmapgl" } ], "histogram": [ { "marker": { "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "histogram" } ], "histogram2d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2d" } ], "histogram2dcontour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2dcontour" } ], "mesh3d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "mesh3d" } ], "parcoords": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "parcoords" } ], "pie": [ { "automargin": true, "type": "pie" } ], "scatter": [ { "fillpattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 }, "type": "scatter" } ], "scatter3d": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatter3d" } ], "scattercarpet": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattercarpet" } ], "scattergeo": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergeo" } ], "scattergl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergl" } ], "scattermapbox": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattermapbox" } ], "scatterpolar": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolar" } ], "scatterpolargl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolargl" } ], "scatterternary": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterternary" } ], "surface": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "surface" } ], "table": [ { "cells": { "fill": { "color": "#EBF0F8" }, "line": { "color": "white" } }, "header": { "fill": { "color": "#C8D4E3" }, "line": { "color": "white" } }, "type": "table" } ] }, "layout": { "annotationdefaults": { "arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1 }, "autotypenumbers": "strict", "coloraxis": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "colorscale": { "diverging": [ [ 0, "#8e0152" ], [ 0.1, "#c51b7d" ], [ 0.2, "#de77ae" ], [ 0.3, "#f1b6da" ], [ 0.4, "#fde0ef" ], [ 0.5, "#f7f7f7" ], [ 0.6, "#e6f5d0" ], [ 0.7, "#b8e186" ], [ 0.8, "#7fbc41" ], [ 0.9, "#4d9221" ], [ 1, "#276419" ] ], "sequential": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "sequentialminus": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ] }, "colorway": [ "#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52" ], "font": { "color": "#2a3f5f" }, "geo": { "bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white" }, "hoverlabel": { "align": "left" }, "hovermode": "closest", "mapbox": { "style": "light" }, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": { "angularaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "radialaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "scene": { "xaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "yaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "zaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" } }, "shapedefaults": { "line": { "color": "#2a3f5f" } }, "ternary": { "aaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "baxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "caxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "title": { "x": 0.05 }, "xaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 }, "yaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 } } }, "title": { "text": "Issued amount last 12 months" }, "xaxis": { "anchor": "y", "autorange": true, "domain": [ 0, 1 ], "range": [ "2023-09-15", "2024-10-14" ], "title": { "text": "index" }, "type": "date" }, "yaxis": { "anchor": "x", "autorange": true, "domain": [ 0, 1 ], "range": [ 0, 45423375747.36842 ], "title": { "text": "FaceIssuedTotal" }, "type": "linear" } } }, "text/html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# 3.3 ) Issued amount last 12 months \n", "from datetime import date\n", "from dateutil.relativedelta import relativedelta\n", "decb = date.today() + relativedelta(months=-12)\n", "start = datetime.datetime(decb.year, decb.month, 1).strftime(\"%Y-%m-%d %H:%M:%S\")[0:10]\n", "end = date.today().strftime(\"%Y-%m-%d %H:%M:%S\")[0:10]\n", "\n", "cols = ['RIC','IssueDate','FaceIssuedTotal']\n", "c = df[cols].copy()\n", "c.loc[:,\"IssueDate\"] = pd.to_datetime(c[\"IssueDate\"])\n", "c = c.set_index(\"IssueDate\").sort_index(ascending=False)\n", "c_month = c.resample('BM').sum()\n", "c_monthb = c_month[start:end]\n", "c_monthb.index.name = None\n", "\n", "import plotly.express as px\n", "fig = px.bar(c_monthb, y='FaceIssuedTotal', title = 'Issued amount last 12 months', color_discrete_sequence=px.colors.sequential.RdBu)\n", "fig.show()" ] }, { "cell_type": "code", "execution_count": 11, "id": "fb66b1cf-f645-496d-9901-8c9ce1819cf6", "metadata": {}, "outputs": [ { "data": { "application/vnd.plotly.v1+json": { "config": { "plotlyServerURL": "https://plot.ly" }, "data": [ { "alignmentgroup": "True", "hovertemplate": "index=%{x}
FaceIssuedTotal=%{y}", "legendgroup": "", "marker": { "color": "rgb(103,0,31)", "pattern": { "shape": "" } }, "name": "", "offsetgroup": "", "orientation": "v", "showlegend": false, "textposition": "auto", "type": "bar", "x": [ "2024-12-31T00:00:00", "2025-12-31T00:00:00", "2026-12-31T00:00:00", "2027-12-31T00:00:00", "2028-12-29T00:00:00", "2029-12-31T00:00:00", "2030-12-31T00:00:00", "2031-12-31T00:00:00", "2032-12-31T00:00:00", "2033-12-30T00:00:00", "2034-12-29T00:00:00" ], "xaxis": "x", "y": [ 116479848016, 249004403254, 152848027266, 116034515575, 64612603641, 72164494000, 67415375000, 39966276000, 11772000000, 16925000000, 12134670000 ], "yaxis": "y" } ], "layout": { "autosize": true, "barmode": "relative", "legend": { "tracegroupgap": 0 }, "template": { "data": { "bar": [ { "error_x": { "color": "#2a3f5f" }, "error_y": { "color": "#2a3f5f" }, "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "bar" } ], "barpolar": [ { "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "barpolar" } ], "carpet": [ { "aaxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "baxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "type": "carpet" } ], "choropleth": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "choropleth" } ], "contour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "contour" } ], "contourcarpet": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "contourcarpet" } ], "heatmap": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmap" } ], "heatmapgl": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmapgl" } ], "histogram": [ { "marker": { "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "histogram" } ], "histogram2d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2d" } ], "histogram2dcontour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2dcontour" } ], "mesh3d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "mesh3d" } ], "parcoords": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "parcoords" } ], "pie": [ { "automargin": true, "type": "pie" } ], "scatter": [ { "fillpattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 }, "type": "scatter" } ], "scatter3d": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatter3d" } ], "scattercarpet": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattercarpet" } ], "scattergeo": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergeo" } ], "scattergl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergl" } ], "scattermapbox": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattermapbox" } ], "scatterpolar": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolar" } ], "scatterpolargl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolargl" } ], "scatterternary": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterternary" } ], "surface": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "surface" } ], "table": [ { "cells": { "fill": { "color": "#EBF0F8" }, "line": { "color": "white" } }, "header": { "fill": { "color": "#C8D4E3" }, "line": { "color": "white" } }, "type": "table" } ] }, "layout": { "annotationdefaults": { "arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1 }, "autotypenumbers": "strict", "coloraxis": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "colorscale": { "diverging": [ [ 0, "#8e0152" ], [ 0.1, "#c51b7d" ], [ 0.2, "#de77ae" ], [ 0.3, "#f1b6da" ], [ 0.4, "#fde0ef" ], [ 0.5, "#f7f7f7" ], [ 0.6, "#e6f5d0" ], [ 0.7, "#b8e186" ], [ 0.8, "#7fbc41" ], [ 0.9, "#4d9221" ], [ 1, "#276419" ] ], "sequential": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "sequentialminus": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ] }, "colorway": [ "#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52" ], "font": { "color": "#2a3f5f" }, "geo": { "bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white" }, "hoverlabel": { "align": "left" }, "hovermode": "closest", "mapbox": { "style": "light" }, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": { "angularaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "radialaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "scene": { "xaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "yaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "zaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" } }, "shapedefaults": { "line": { "color": "#2a3f5f" } }, "ternary": { "aaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "baxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "caxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "title": { "x": 0.05 }, "xaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 }, "yaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 } } }, "title": { "text": "Redem amount in 10 years" }, "xaxis": { "anchor": "y", "autorange": true, "domain": [ 0, 1 ], "range": [ "2024-07-02", "2035-06-29" ], "title": { "text": "index" }, "type": "date" }, "yaxis": { "anchor": "x", "autorange": true, "domain": [ 0, 1 ], "range": [ 0, 262109898162.10526 ], "title": { "text": "FaceIssuedTotal" }, "type": "linear" } } }, "text/html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# 3.4 ) Redem amount in 10 years\n", "yearsf = date.today() + relativedelta(years=+10)\n", "end = datetime.datetime(yearsf.year, yearsf.month, 1).strftime(\"%Y-%m-%d %H:%M:%S\")[0:4]\n", "start = date.today().strftime(\"%Y-%m-%d %H:%M:%S\")[0:4]\n", "cols = ['RIC','PricingRedemDate','FaceIssuedTotal']\n", "d = df[cols].copy()\n", "d.loc[:,\"PricingRedemDate\"] = pd.to_datetime(d[\"PricingRedemDate\"], errors = 'coerce')\n", "d = d.set_index(\"PricingRedemDate\").sort_index(ascending=True)\n", "d_month = d.resample('BY').sum()\n", "d_monthf = d_month[start:end]\n", "d_monthf.index.name = None\n", "\n", "import plotly.express as px\n", "fig = px.bar(d_monthf, y='FaceIssuedTotal',title = 'Redem amount in 10 years', color_discrete_sequence=px.colors.sequential.RdBu)\n", "fig.show()" ] }, { "cell_type": "code", "execution_count": 12, "id": "d56b7aad-7346-4e14-bb46-304be267dd94", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2024-06-01 2024-09-30\n" ] }, { "data": { "application/vnd.plotly.v1+json": { "config": { "plotlyServerURL": "https://plot.ly" }, "data": [ { "domain": { "x": [ 0, 1 ], "y": [ 0, 1 ] }, "hovertemplate": "RCSTRBC2012Leaf=%{label}
RIC=%{value}", "labels": [ "Corporate Banks", "Banks (NEC)", "Corporate Financial Services (NEC)", "Investment Management & Fund Operators (NEC)", "Construction & Engineering (NEC)", "Investment Banking & Brokerage Services (NEC)", "Commercial Leasing", "Brokerage Services", "Public Finance Activities", "Diversified Investment Services" ], "legendgroup": "", "name": "", "showlegend": true, "type": "pie", "values": [ 1798, 1059, 102, 46, 24, 15, 10, 9, 8, 6 ] } ], "layout": { "autosize": true, "legend": { "tracegroupgap": 0 }, "piecolorway": [ "rgb(103,0,31)", "rgb(178,24,43)", "rgb(214,96,77)", "rgb(244,165,130)", "rgb(253,219,199)", "rgb(247,247,247)", "rgb(209,229,240)", "rgb(146,197,222)", "rgb(67,147,195)", "rgb(33,102,172)", "rgb(5,48,97)" ], "template": { "data": { "bar": [ { "error_x": { "color": "#2a3f5f" }, "error_y": { "color": "#2a3f5f" }, "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "bar" } ], "barpolar": [ { "marker": { "line": { "color": "#E5ECF6", "width": 0.5 }, "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "barpolar" } ], "carpet": [ { "aaxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "baxis": { "endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f" }, "type": "carpet" } ], "choropleth": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "choropleth" } ], "contour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "contour" } ], "contourcarpet": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "contourcarpet" } ], "heatmap": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmap" } ], "heatmapgl": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "heatmapgl" } ], "histogram": [ { "marker": { "pattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 } }, "type": "histogram" } ], "histogram2d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2d" } ], "histogram2dcontour": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "histogram2dcontour" } ], "mesh3d": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "type": "mesh3d" } ], "parcoords": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "parcoords" } ], "pie": [ { "automargin": true, "type": "pie" } ], "scatter": [ { "fillpattern": { "fillmode": "overlay", "size": 10, "solidity": 0.2 }, "type": "scatter" } ], "scatter3d": [ { "line": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatter3d" } ], "scattercarpet": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattercarpet" } ], "scattergeo": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergeo" } ], "scattergl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattergl" } ], "scattermapbox": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scattermapbox" } ], "scatterpolar": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolar" } ], "scatterpolargl": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterpolargl" } ], "scatterternary": [ { "marker": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "type": "scatterternary" } ], "surface": [ { "colorbar": { "outlinewidth": 0, "ticks": "" }, "colorscale": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "type": "surface" } ], "table": [ { "cells": { "fill": { "color": "#EBF0F8" }, "line": { "color": "white" } }, "header": { "fill": { "color": "#C8D4E3" }, "line": { "color": "white" } }, "type": "table" } ] }, "layout": { "annotationdefaults": { "arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1 }, "autotypenumbers": "strict", "coloraxis": { "colorbar": { "outlinewidth": 0, "ticks": "" } }, "colorscale": { "diverging": [ [ 0, "#8e0152" ], [ 0.1, "#c51b7d" ], [ 0.2, "#de77ae" ], [ 0.3, "#f1b6da" ], [ 0.4, "#fde0ef" ], [ 0.5, "#f7f7f7" ], [ 0.6, "#e6f5d0" ], [ 0.7, "#b8e186" ], [ 0.8, "#7fbc41" ], [ 0.9, "#4d9221" ], [ 1, "#276419" ] ], "sequential": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ], "sequentialminus": [ [ 0, "#0d0887" ], [ 0.1111111111111111, "#46039f" ], [ 0.2222222222222222, "#7201a8" ], [ 0.3333333333333333, "#9c179e" ], [ 0.4444444444444444, "#bd3786" ], [ 0.5555555555555556, "#d8576b" ], [ 0.6666666666666666, "#ed7953" ], [ 0.7777777777777778, "#fb9f3a" ], [ 0.8888888888888888, "#fdca26" ], [ 1, "#f0f921" ] ] }, "colorway": [ "#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52" ], "font": { "color": "#2a3f5f" }, "geo": { "bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white" }, "hoverlabel": { "align": "left" }, "hovermode": "closest", "mapbox": { "style": "light" }, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": { "angularaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "radialaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "scene": { "xaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "yaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" }, "zaxis": { "backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white" } }, "shapedefaults": { "line": { "color": "#2a3f5f" } }, "ternary": { "aaxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "baxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" }, "bgcolor": "#E5ECF6", "caxis": { "gridcolor": "white", "linecolor": "white", "ticks": "" } }, "title": { "x": 0.05 }, "xaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 }, "yaxis": { "automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": { "standoff": 15 }, "zerolinecolor": "white", "zerolinewidth": 2 } } }, "title": { "text": "newly issued bonds last 3 months" } } }, "text/html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# 3.5 ) Newly issued bonds last 3 months\n", "monb3 = date.today() + relativedelta(months=-3)\n", "start = datetime.datetime(monb3.year, monb3.month, 1).strftime(\"%Y-%m-%d %H:%M:%S\")[0:10]\n", "end = date.today().strftime(\"%Y-%m-%d %H:%M:%S\")[0:10]\n", "print(start, end)\n", "cols = ['RIC','IssueDate','FaceIssuedTotal','RCSTRBC2012Leaf']\n", "f = df[cols].copy()\n", "f.loc[:,\"IssueDate\"] = pd.to_datetime(f[\"IssueDate\"])\n", "f = f.set_index(\"IssueDate\").sort_index(ascending=True)\n", "f_3m = f[start:end]\n", "rt = f_3m.groupby(\"RCSTRBC2012Leaf\",as_index=False).agg('count')\n", "rt = rt.sort_values('RIC', ascending = False).head(10)\n", "fig = px.pie(rt, values='RIC', names='RCSTRBC2012Leaf', title='newly issued bonds last 3 months',color_discrete_sequence=px.colors.sequential.RdBu)\n", "fig.show()" ] }, { "cell_type": "code", "execution_count": 13, "id": "0d824db2-6f2a-4517-b0fe-7f57135e6d6c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RICIssuerLegalNameEOMPriceReturnEOMPriceChange
4746VG229375067=ZHENRO PROPERTIES GROUP LIMITED300.00.375
4516VG222689821=ZHENRO PROPERTIES GROUP LIMITED300.00.375
11843KY224431511=REDSUN PROPERTIES GROUP LIMITED50.00.25
395662914VAG1=NIO INC.36.812524.4052
401062914VAH9=NIO INC.32.33302120.0063
40USG11056AA91=BEST INC.25.8714620.499
4108653CAA4=BEST INC.25.86987320.4995
3908653CAB2=BEST INC.25.86987320.4995
3944CN285974623=Zhong Guo Ping An Bao Xian (Ji Tuan) Gu Fen Yo...20.63904121.237
3252VG226484086=CHINA HONGQIAO GROUP LIMITED17.6113830.125
\n", "
" ], "text/plain": [ " RIC IssuerLegalName \\\n", "4746 VG229375067= ZHENRO PROPERTIES GROUP LIMITED \n", "4516 VG222689821= ZHENRO PROPERTIES GROUP LIMITED \n", "11843 KY224431511= REDSUN PROPERTIES GROUP LIMITED \n", "3956 62914VAG1= NIO INC. \n", "4010 62914VAH9= NIO INC. \n", "40 USG11056AA91= BEST INC. \n", "41 08653CAA4= BEST INC. \n", "39 08653CAB2= BEST INC. \n", "3944 CN285974623= Zhong Guo Ping An Bao Xian (Ji Tuan) Gu Fen Yo... \n", "3252 VG226484086= CHINA HONGQIAO GROUP LIMITED \n", "\n", " EOMPriceReturn EOMPriceChange \n", "4746 300.0 0.375 \n", "4516 300.0 0.375 \n", "11843 50.0 0.25 \n", "3956 36.8125 24.4052 \n", "4010 32.333021 20.0063 \n", "40 25.87146 20.499 \n", "41 25.869873 20.4995 \n", "39 25.869873 20.4995 \n", "3944 20.639041 21.237 \n", "3252 17.61138 30.125 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 3.6 ) Price top 10\n", "cols = ['RIC','IssuerLegalName','EOMPriceReturn','EOMPriceChange']\n", "price = df[cols].copy()\n", "\n", "price_top10 = price.sort_values('EOMPriceReturn', ascending = False).head(10)\n", "price_top10" ] }, { "cell_type": "code", "execution_count": 14, "id": "16e85059-ada3-4256-b15a-27b6f2f78775", "metadata": {}, "outputs": [], "source": [ "# 3.7 ) Yield\n", "cols = ['RIC','IssuerLegalName','CurrentYield','PricingClosingYield']\n", "y = df[cols]\n", "y = y[y['CurrentYield']>0]" ] }, { "cell_type": "code", "execution_count": 15, "id": "2b6e70db-1586-45d7-863d-ca3ab4c4d447", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RICIssuerLegalNameCurrentYieldPricingClosingYield
4694CN224803265=Yang Guang Cheng Jia Shi Guo Ji You Xian Gong Si6000.01390889.198562
4749VG201351260=ZHENRO PROPERTIES GROUP LIMITED2944.8<NA>
4722VG229030884=RONSHINE CHINA HOLDINGS LIMITED2840.0126000.0
4516VG222689821=ZHENRO PROPERTIES GROUP LIMITED1470.057774.193548
4746VG229375067=ZHENRO PROPERTIES GROUP LIMITED1340.0642.837833
11843KY224431511=REDSUN PROPERTIES GROUP LIMITED973.33333346705.882353
4644VG200347161=AGILE GROUP HOLDINGS LIMITED827.946667<NA>
4077CH278530264=RRPSCHINA AOYUAN GROUP LIMITED733.33333384.258514
4076CH278530299=CHINA AOYUAN GROUP LIMITED733.33333384.258514
4607VG178542273=AGILE GROUP HOLDINGS LIMITED598.933333<NA>
\n", "
" ], "text/plain": [ " RIC IssuerLegalName \\\n", "4694 CN224803265= Yang Guang Cheng Jia Shi Guo Ji You Xian Gong Si \n", "4749 VG201351260= ZHENRO PROPERTIES GROUP LIMITED \n", "4722 VG229030884= RONSHINE CHINA HOLDINGS LIMITED \n", "4516 VG222689821= ZHENRO PROPERTIES GROUP LIMITED \n", "4746 VG229375067= ZHENRO PROPERTIES GROUP LIMITED \n", "11843 KY224431511= REDSUN PROPERTIES GROUP LIMITED \n", "4644 VG200347161= AGILE GROUP HOLDINGS LIMITED \n", "4077 CH278530264=RRPS CHINA AOYUAN GROUP LIMITED \n", "4076 CH278530299= CHINA AOYUAN GROUP LIMITED \n", "4607 VG178542273= AGILE GROUP HOLDINGS LIMITED \n", "\n", " CurrentYield PricingClosingYield \n", "4694 6000.0 1390889.198562 \n", "4749 2944.8 \n", "4722 2840.0 126000.0 \n", "4516 1470.0 57774.193548 \n", "4746 1340.0 642.837833 \n", "11843 973.333333 46705.882353 \n", "4644 827.946667 \n", "4077 733.333333 84.258514 \n", "4076 733.333333 84.258514 \n", "4607 598.933333 " ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 3.7.1 ) Yield top 10\n", "yield_top10 = y.sort_values('CurrentYield', ascending = False).head(10)\n", "yield_top10" ] }, { "cell_type": "code", "execution_count": 16, "id": "05c8d499-5aff-4d80-9d57-2da46e32044a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RICIssuerLegalNameCurrentYieldPricingClosingYield
2457BCMKFC24013=RRPSJiao Tong Yin Hang Gu Fen You Xian Gong Si0.0514864.700459
3874VG233295710=Li Auto Inc.0.207793-4.865266
387350202MAB8=Li Auto Inc.0.210542-4.506101
387550202MAA0=Li Auto Inc.0.210542-4.506101
3938G8208BAA9=JD.COM, INC.0.220932-2.396061
393747215PAH9=JD.COM, INC.0.220932-2.396061
3924USGDS0329=GDS HOLDINGS LIMITED0.250.249998
4042G01719AJ5=ALIBABA GROUP HOLDING LIMITED0.411184-2.460243
404301609WBF8=ALIBABA GROUP HOLDING LIMITED0.411184-2.460243
3538090040AE6=BILIBILI INC.0.480377-1.349106
\n", "
" ], "text/plain": [ " RIC IssuerLegalName \\\n", "2457 BCMKFC24013=RRPS Jiao Tong Yin Hang Gu Fen You Xian Gong Si \n", "3874 VG233295710= Li Auto Inc. \n", "3873 50202MAB8= Li Auto Inc. \n", "3875 50202MAA0= Li Auto Inc. \n", "3938 G8208BAA9= JD.COM, INC. \n", "3937 47215PAH9= JD.COM, INC. \n", "3924 USGDS0329= GDS HOLDINGS LIMITED \n", "4042 G01719AJ5= ALIBABA GROUP HOLDING LIMITED \n", "4043 01609WBF8= ALIBABA GROUP HOLDING LIMITED \n", "3538 090040AE6= BILIBILI INC. \n", "\n", " CurrentYield PricingClosingYield \n", "2457 0.051486 4.700459 \n", "3874 0.207793 -4.865266 \n", "3873 0.210542 -4.506101 \n", "3875 0.210542 -4.506101 \n", "3938 0.220932 -2.396061 \n", "3937 0.220932 -2.396061 \n", "3924 0.25 0.249998 \n", "4042 0.411184 -2.460243 \n", "4043 0.411184 -2.460243 \n", "3538 0.480377 -1.349106 " ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 3.7.2 ) Yield last 10\n", "yield_last10 = y.sort_values('CurrentYield', ascending = True).head(10)\n", "yield_last10" ] }, { "cell_type": "code", "execution_count": 17, "id": "f80ee917-f0ae-43d7-a2aa-8f91f47abdfb", "metadata": {}, "outputs": [], "source": [ "ld.close_session()" ] } ], "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.9.12" } }, "nbformat": 4, "nbformat_minor": 5 }