i.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "from datetime import date, datetime, timedelta\n",
    "from numpy import sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = requests.get(\"https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,SHORTNAME,COUPONVALUE,COUPONPERCENT,MATDATE,NEXTCOUPON,STATUS,FACEVALUE\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds = pd.read_xml(response.content, xpath=\".//row\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SECID</th>\n",
       "      <th>SECNAME</th>\n",
       "      <th>SHORTNAME</th>\n",
       "      <th>COUPONVALUE</th>\n",
       "      <th>COUPONPERCENT</th>\n",
       "      <th>MATDATE</th>\n",
       "      <th>NEXTCOUPON</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>FACEVALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMUNIBB2DER6</td>\n",
       "      <td>UNIBANK OJSC Series 1 21</td>\n",
       "      <td>UBANK02/24</td>\n",
       "      <td>1.33</td>\n",
       "      <td>5.300</td>\n",
       "      <td>2024-02-05</td>\n",
       "      <td>2023-05-05</td>\n",
       "      <td>A</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RU000A0JQ7Z2</td>\n",
       "      <td>\"Российские ЖД\" ОАО 19 обл.</td>\n",
       "      <td>РЖД-19 обл</td>\n",
       "      <td>39.14</td>\n",
       "      <td>7.850</td>\n",
       "      <td>2024-07-08</td>\n",
       "      <td>2023-07-10</td>\n",
       "      <td>A</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RU000A0JQAL8</td>\n",
       "      <td>ДОМ.РФ (АО) обл. сер. А14</td>\n",
       "      <td>ДОМ.РФ14об</td>\n",
       "      <td>49.59</td>\n",
       "      <td>10.000</td>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>A</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RU000A0JQAM6</td>\n",
       "      <td>ДОМ.РФ (АО) обл. сер. А15</td>\n",
       "      <td>ДОМ.РФ15об</td>\n",
       "      <td>52.07</td>\n",
       "      <td>10.500</td>\n",
       "      <td>2028-09-15</td>\n",
       "      <td>2023-03-15</td>\n",
       "      <td>A</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RU000A0JQRD9</td>\n",
       "      <td>\"Российские ЖД\" ОАО 23 обл.</td>\n",
       "      <td>РЖД-23 обл</td>\n",
       "      <td>39.14</td>\n",
       "      <td>7.850</td>\n",
       "      <td>2025-01-16</td>\n",
       "      <td>2023-07-20</td>\n",
       "      <td>A</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>XS2392969395</td>\n",
       "      <td>CBOM Finance P.L.C. 7.625 UNDT</td>\n",
       "      <td>CBOM Finan</td>\n",
       "      <td>19.06</td>\n",
       "      <td>7.625</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>2023-04-04</td>\n",
       "      <td>A</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>XS2393688598</td>\n",
       "      <td>SovCom Capital UNTD</td>\n",
       "      <td>SCB-UNTD</td>\n",
       "      <td>19.00</td>\n",
       "      <td>7.600</td>\n",
       "      <td>0000-00-00</td>\n",
       "      <td>2023-02-17</td>\n",
       "      <td>A</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>XS2415466676</td>\n",
       "      <td>BCS SP Plc Series 229</td>\n",
       "      <td>BCS11/24-8</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.010</td>\n",
       "      <td>2024-11-29</td>\n",
       "      <td>2023-11-29</td>\n",
       "      <td>A</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>XS2417535650</td>\n",
       "      <td>BCS SP Plc Series 234</td>\n",
       "      <td>BCS06/25-A</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-06-01</td>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>A</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>XS2420560869</td>\n",
       "      <td>BCS SP Plc Series 239</td>\n",
       "      <td>BCS01/25</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2025-01-15</td>\n",
       "      <td>2023-04-15</td>\n",
       "      <td>A</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2022 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             SECID                         SECNAME   SHORTNAME  COUPONVALUE  \\\n",
       "0     AMUNIBB2DER6        UNIBANK OJSC Series 1 21  UBANK02/24         1.33   \n",
       "1     RU000A0JQ7Z2     \"Российские ЖД\" ОАО 19 обл.  РЖД-19 обл        39.14   \n",
       "2     RU000A0JQAL8       ДОМ.РФ (АО) обл. сер. А14  ДОМ.РФ14об        49.59   \n",
       "3     RU000A0JQAM6       ДОМ.РФ (АО) обл. сер. А15  ДОМ.РФ15об        52.07   \n",
       "4     RU000A0JQRD9     \"Российские ЖД\" ОАО 23 обл.  РЖД-23 обл        39.14   \n",
       "...            ...                             ...         ...          ...   \n",
       "2017  XS2392969395  CBOM Finance P.L.C. 7.625 UNDT  CBOM Finan        19.06   \n",
       "2018  XS2393688598             SovCom Capital UNTD    SCB-UNTD        19.00   \n",
       "2019  XS2415466676           BCS SP Plc Series 229  BCS11/24-8        10.00   \n",
       "2020  XS2417535650           BCS SP Plc Series 234  BCS06/25-A         0.00   \n",
       "2021  XS2420560869           BCS SP Plc Series 239    BCS01/25         0.00   \n",
       "\n",
       "      COUPONPERCENT     MATDATE  NEXTCOUPON STATUS  FACEVALUE  \n",
       "0             5.300  2024-02-05  2023-05-05      A      100.0  \n",
       "1             7.850  2024-07-08  2023-07-10      A     1000.0  \n",
       "2            10.000  2023-05-15  2023-05-15      A     1000.0  \n",
       "3            10.500  2028-09-15  2023-03-15      A     1000.0  \n",
       "4             7.850  2025-01-16  2023-07-20      A     1000.0  \n",
       "...             ...         ...         ...    ...        ...  \n",
       "2017          7.625  0000-00-00  2023-04-04      A     1000.0  \n",
       "2018          7.600  0000-00-00  2023-02-17      A     1000.0  \n",
       "2019          0.010  2024-11-29  2023-11-29      A   100000.0  \n",
       "2020            NaN  2025-06-01  2023-12-01      A   100000.0  \n",
       "2021            NaN  2025-01-15  2023-04-15      A   100000.0  \n",
       "\n",
       "[2022 rows x 9 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bonds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds = bonds.dropna()\n",
    "bonds = bonds.drop(\n",
    "    bonds[\n",
    "        (bonds.COUPONPERCENT < 9)\n",
    "        | (bonds.COUPONPERCENT > 20)\n",
    "        | (bonds.COUPONVALUE == 0)\n",
    "        | (bonds.MATDATE == \"0000-00-00\")\n",
    "        | (bonds.NEXTCOUPON == \"0000-00-00\")\n",
    "        | (bonds.STATUS != \"A\")\n",
    "        | (bonds.FACEVALUE != 1000)\n",
    "    ].index\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def delta(d):\n",
    "    d = datetime.strptime(d[\"MATDATE\"], '%Y-%m-%d').date()\n",
    "    delta = d - date.today()\n",
    "    y = delta.days // 365\n",
    "    m = delta.days % 365 // 30\n",
    "    if y > 0:\n",
    "        return f\"{y} years {m} months\"\n",
    "    return f\"{m} months\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds[\"DELTA\"] = bonds.apply(delta, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds.sort_values(\"COUPONPERCENT\").to_csv(\"1.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_trades_number(row: str):\n",
    "    date = datetime.today() - timedelta(7)\n",
    "    date = date.strftime(\"%Y-%m-%d\")\n",
    "    response = requests.get(\n",
    "        \"https://iss.moex.com/iss/history/engines/stock/markets/bonds/boards/TQCB/securities/\"\n",
    "        + row[\"SECID\"]\n",
    "        + \".json?iss.meta=off&iss.only=history&history.columns=NUMTRADES&limit=20&from=\"\n",
    "        + date\n",
    "    )\n",
    "    res = sum(response.json()[\"history\"][\"data\"])\n",
    "    print(res)\n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "60\n",
      "23\n",
      "0\n",
      "1\n",
      "11\n",
      "0\n",
      "174\n",
      "17\n",
      "46\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "22\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "192\n",
      "0\n",
      "0\n",
      "72\n",
      "0\n",
      "0\n",
      "40\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "94\n",
      "6\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "10\n",
      "0\n",
      "1\n",
      "0\n",
      "0\n",
      "1\n",
      "2\n",
      "194\n",
      "0\n",
      "8\n",
      "5\n",
      "10\n",
      "45\n",
      "282\n",
      "2\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "101\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "11\n",
      "0\n",
      "0\n",
      "50\n",
      "8\n",
      "0\n",
      "17\n",
      "7\n",
      "58\n",
      "0\n",
      "0\n",
      "110\n",
      "47\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "159\n",
      "0\n",
      "250\n",
      "682\n",
      "166\n",
      "304\n",
      "484\n",
      "0\n",
      "47\n",
      "13\n",
      "0\n",
      "0\n",
      "46\n",
      "291\n",
      "0\n",
      "0\n",
      "2\n",
      "103\n",
      "0\n",
      "0\n",
      "76\n",
      "50\n",
      "0\n",
      "1488\n",
      "450\n",
      "142\n",
      "173\n",
      "68\n",
      "0\n",
      "0\n",
      "27\n",
      "22\n",
      "11\n",
      "112\n",
      "77\n",
      "58\n",
      "177\n",
      "0\n",
      "0\n",
      "0\n",
      "10\n",
      "86\n",
      "0\n",
      "0\n",
      "414\n",
      "0\n",
      "198\n",
      "0\n",
      "0\n",
      "0\n",
      "0\n",
      "69\n",
      "27\n",
      "0\n",
      "318\n",
      "0\n",
      "0\n",
      "268\n",
      "673\n",
      "17\n",
      "2\n",
      "0\n",
      "574\n",
      "166\n",
      "527\n",
      "1018\n",
      "7\n",
      "201\n",
      "79\n",
      "45\n",
      "738\n",
      "375\n",
      "1\n",
      "565\n",
      "725\n",
      "497\n",
      "175\n",
      "66\n",
      "47\n",
      "200\n",
      "21\n",
      "6\n",
      "0\n",
      "180\n",
      "118\n",
      "37\n",
      "120\n",
      "11\n",
      "1472\n",
      "0\n",
      "0\n",
      "0\n",
      "7\n",
      "9\n",
      "808\n",
      "17\n",
      "79\n",
      "0\n",
      "28\n",
      "417\n",
      "917\n",
      "130\n",
      "114\n",
      "0\n",
      "84\n",
      "75\n",
      "2750\n",
      "490\n",
      "119\n",
      "0\n",
      "0\n",
      "471\n",
      "474\n",
      "332\n",
      "286\n",
      "264\n",
      "22\n",
      "619\n",
      "686\n",
      "72\n",
      "373\n",
      "2463\n",
      "151\n",
      "0\n",
      "1294\n",
      "248\n",
      "316\n",
      "637\n",
      "70\n",
      "494\n",
      "0\n",
      "259\n",
      "2270\n",
      "784\n",
      "520\n",
      "790\n",
      "1062\n",
      "229\n",
      "152\n",
      "306\n",
      "79\n",
      "1515\n",
      "1223\n",
      "841\n",
      "226\n",
      "605\n",
      "599\n",
      "53\n",
      "69\n",
      "480\n",
      "355\n",
      "227\n",
      "201\n",
      "123\n",
      "283\n",
      "21\n",
      "221\n",
      "348\n",
      "554\n",
      "284\n",
      "253\n",
      "1307\n",
      "100\n",
      "2559\n",
      "667\n",
      "682\n",
      "796\n",
      "103\n",
      "464\n",
      "437\n",
      "319\n",
      "1068\n",
      "88\n",
      "87\n",
      "1119\n",
      "132\n",
      "546\n",
      "310\n",
      "0\n",
      "295\n",
      "0\n",
      "225\n",
      "169\n",
      "1045\n",
      "102\n",
      "245\n",
      "91\n",
      "235\n",
      "0\n",
      "57\n",
      "166\n",
      "168\n",
      "647\n",
      "0\n",
      "106\n",
      "0\n",
      "380\n",
      "16\n",
      "0\n",
      "45\n",
      "105\n",
      "528\n",
      "78\n",
      "242\n",
      "1622\n",
      "1861\n",
      "141\n",
      "1586\n",
      "31\n",
      "134\n",
      "436\n",
      "236\n",
      "301\n",
      "105\n",
      "140\n",
      "263\n",
      "0\n",
      "172\n",
      "368\n",
      "20\n",
      "0\n",
      "673\n",
      "0\n",
      "61\n",
      "82\n",
      "237\n",
      "116\n",
      "17\n",
      "160\n",
      "0\n",
      "108\n",
      "2983\n",
      "320\n",
      "332\n",
      "181\n",
      "106\n",
      "212\n",
      "58\n",
      "218\n",
      "830\n",
      "124\n",
      "0\n",
      "0\n",
      "26\n",
      "0\n",
      "0\n",
      "545\n",
      "404\n",
      "241\n",
      "379\n",
      "136\n",
      "0\n",
      "16\n",
      "247\n",
      "37\n",
      "221\n",
      "8\n",
      "263\n",
      "418\n",
      "1213\n",
      "165\n",
      "262\n",
      "335\n",
      "1275\n",
      "250\n",
      "235\n",
      "590\n",
      "0\n",
      "6\n",
      "4370\n",
      "696\n",
      "0\n",
      "470\n",
      "496\n",
      "5208\n",
      "0\n",
      "350\n",
      "204\n",
      "603\n",
      "1166\n",
      "700\n",
      "446\n",
      "34\n",
      "1\n",
      "318\n",
      "1173\n",
      "0\n",
      "0\n",
      "255\n",
      "160\n",
      "0\n",
      "570\n",
      "393\n",
      "3\n",
      "864\n",
      "94\n",
      "68\n",
      "458\n",
      "0\n",
      "4\n",
      "867\n",
      "599\n",
      "37\n",
      "362\n",
      "458\n",
      "0\n",
      "475\n",
      "391\n",
      "1131\n",
      "1447\n",
      "1567\n",
      "265\n",
      "0\n",
      "0\n",
      "884\n",
      "877\n",
      "16\n",
      "0\n",
      "379\n",
      "612\n",
      "64\n",
      "382\n",
      "28\n",
      "0\n",
      "15\n",
      "251\n",
      "187\n",
      "88\n",
      "642\n",
      "496\n",
      "187\n",
      "369\n",
      "735\n",
      "178\n",
      "6\n",
      "372\n",
      "302\n",
      "0\n",
      "983\n",
      "0\n",
      "18\n",
      "373\n",
      "362\n",
      "0\n",
      "26\n",
      "60\n",
      "10\n",
      "0\n",
      "205\n",
      "0\n",
      "887\n",
      "7467\n",
      "475\n",
      "84\n",
      "0\n",
      "375\n",
      "1066\n",
      "863\n",
      "1785\n",
      "7\n",
      "0\n",
      "1759\n",
      "690\n",
      "1492\n",
      "5275\n",
      "1939\n",
      "99\n",
      "309\n",
      "0.0\n",
      "0.0\n",
      "214\n"
     ]
    }
   ],
   "source": [
    "bonds[\"TRADES\"] = bonds.apply(get_trades_number, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds = bonds.drop(bonds[(bonds.TRADES < 10)].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds = bonds.drop(bonds[(bonds.COUPONPERCENT < 10)].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "bonds.to_csv(\"1.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "496b5a6164915bbbcf1534d28ac11a084ad828a8af6981fcae7d8de0916b59a0"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}