analysis/每日分析模板.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('..')\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "from settings import DBSelector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = DBSelector()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = db.get_engine('db_stock','qq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = 'bond'\n",
    "df = pd.read_sql(table,con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>可转债代码</th>\n",
       "      <th>可转债名称</th>\n",
       "      <th>可转债价格</th>\n",
       "      <th>正股名称</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股现价</th>\n",
       "      <th>正股涨跌幅</th>\n",
       "      <th>最新转股价</th>\n",
       "      <th>溢价率</th>\n",
       "      <th>可转债涨幅</th>\n",
       "      <th>回售触发价</th>\n",
       "      <th>转股起始日</th>\n",
       "      <th>到期时间</th>\n",
       "      <th>成交额(万元)</th>\n",
       "      <th>强赎价格</th>\n",
       "      <th>剩余时间</th>\n",
       "      <th>回售起始日</th>\n",
       "      <th>评级</th>\n",
       "      <th>下修提示</th>\n",
       "      <th>下调次数</th>\n",
       "      <th>转债剩余占总市值比</th>\n",
       "      <th>剩余规模</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>股东配售率</th>\n",
       "      <th>强赎标志</th>\n",
       "      <th>强赎日期</th>\n",
       "      <th>更新日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>113581</td>\n",
       "      <td>龙蟠转债</td>\n",
       "      <td>257.300</td>\n",
       "      <td>龙蟠科技</td>\n",
       "      <td>603906</td>\n",
       "      <td>27.61</td>\n",
       "      <td>-2.44</td>\n",
       "      <td>9.48</td>\n",
       "      <td>-11.65</td>\n",
       "      <td>-5.18</td>\n",
       "      <td>6.64</td>\n",
       "      <td>2020-10-29</td>\n",
       "      <td>26-04-22</td>\n",
       "      <td>37567.25</td>\n",
       "      <td>120.0</td>\n",
       "      <td>5.622</td>\n",
       "      <td>2024-04-22</td>\n",
       "      <td>A+</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>76.13</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-09-08 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123063</td>\n",
       "      <td>大禹转债</td>\n",
       "      <td>130.502</td>\n",
       "      <td>大禹节水</td>\n",
       "      <td>300021</td>\n",
       "      <td>7.05</td>\n",
       "      <td>12.44</td>\n",
       "      <td>4.94</td>\n",
       "      <td>-8.55</td>\n",
       "      <td>3.48</td>\n",
       "      <td>3.46</td>\n",
       "      <td>2021-02-03</td>\n",
       "      <td>26-07-27</td>\n",
       "      <td>48202.03</td>\n",
       "      <td>120.0</td>\n",
       "      <td>5.885</td>\n",
       "      <td>2025-07-28</td>\n",
       "      <td>AA-</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>11.3</td>\n",
       "      <td>6.380</td>\n",
       "      <td>6.380</td>\n",
       "      <td>68.99</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-09-08 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>113575</td>\n",
       "      <td>东时转债</td>\n",
       "      <td>143.020</td>\n",
       "      <td>东方时尚</td>\n",
       "      <td>603377</td>\n",
       "      <td>22.76</td>\n",
       "      <td>-1.39</td>\n",
       "      <td>14.56</td>\n",
       "      <td>-8.51</td>\n",
       "      <td>-1.81</td>\n",
       "      <td>10.19</td>\n",
       "      <td>2020-10-15</td>\n",
       "      <td>26-04-08</td>\n",
       "      <td>1368.22</td>\n",
       "      <td>108.0</td>\n",
       "      <td>5.584</td>\n",
       "      <td>2024-04-08</td>\n",
       "      <td>AA</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>4.280</td>\n",
       "      <td>4.280</td>\n",
       "      <td>20.73</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-09-08 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123050</td>\n",
       "      <td>聚飞转债</td>\n",
       "      <td>145.000</td>\n",
       "      <td>聚飞光电</td>\n",
       "      <td>300303</td>\n",
       "      <td>8.15</td>\n",
       "      <td>3.43</td>\n",
       "      <td>5.18</td>\n",
       "      <td>-7.84</td>\n",
       "      <td>-0.92</td>\n",
       "      <td>3.63</td>\n",
       "      <td>2020-10-20</td>\n",
       "      <td>26-04-13</td>\n",
       "      <td>31066.36</td>\n",
       "      <td>118.0</td>\n",
       "      <td>5.597</td>\n",
       "      <td>2024-04-15</td>\n",
       "      <td>AA-</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>7.050</td>\n",
       "      <td>7.050</td>\n",
       "      <td>43.22</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-09-08 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>123051</td>\n",
       "      <td>今天转债</td>\n",
       "      <td>138.282</td>\n",
       "      <td>今天国际</td>\n",
       "      <td>300532</td>\n",
       "      <td>13.02</td>\n",
       "      <td>3.42</td>\n",
       "      <td>8.80</td>\n",
       "      <td>-6.53</td>\n",
       "      <td>1.16</td>\n",
       "      <td>6.16</td>\n",
       "      <td>2020-12-10</td>\n",
       "      <td>26-06-03</td>\n",
       "      <td>7182.65</td>\n",
       "      <td>116.0</td>\n",
       "      <td>5.737</td>\n",
       "      <td>2024-06-03</td>\n",
       "      <td>A+</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>2.800</td>\n",
       "      <td>2.800</td>\n",
       "      <td>76.80</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-09-08 15:03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    可转债代码 可转债名称    可转债价格  正股名称    正股代码   正股现价        ...          剩余规模   发行规模  股东配售率  强赎标志  强赎日期              更新日期\n",
       "0  113581  龙蟠转债  257.300  龙蟠科技  603906  27.61        ...         4.000  4.000  76.13     X  None  2020-09-08 15:03\n",
       "1  123063  大禹转债  130.502  大禹节水  300021   7.05        ...         6.380  6.380  68.99     X  None  2020-09-08 15:03\n",
       "2  113575  东时转债  143.020  东方时尚  603377  22.76        ...         4.280  4.280  20.73     X  None  2020-09-08 15:03\n",
       "3  123050  聚飞转债  145.000  聚飞光电  300303   8.15        ...         7.050  7.050  43.22     X  None  2020-09-08 15:03\n",
       "4  123051  今天转债  138.282  今天国际  300532  13.02        ...         2.800  2.800  76.80     X  None  2020-09-08 15:03\n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "cyb_df = df[df['正股代码'].str.startswith('3')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.9328000000000003"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cyb_df['正股涨跌幅'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "non_cyb_df = df[~df['正股代码'].str.startswith('3')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.39790983606557384"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_cyb_df['正股涨跌幅'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.3807843137254903"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cyb_df['可转债涨幅'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.2745491803278688"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_cyb_df['可转债涨幅'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 港股中签率分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "table = 'tb_hk_new_stock'\n",
    "df = pd.read_sql(table,con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 497 entries, 0 to 496\n",
      "Data columns (total 15 columns):\n",
      "id                  497 non-null int64\n",
      "name                497 non-null object\n",
      "code                497 non-null object\n",
      "issue_date          0 non-null datetime64[ns]\n",
      "each_hand_stock     497 non-null object\n",
      "share_value_Yi      497 non-null object\n",
      "margin_price        497 non-null object\n",
      "price               457 non-null float64\n",
      "over_price_part     497 non-null object\n",
      "hit_least_num       457 non-null float64\n",
      "hit_ratio           457 non-null float64\n",
      "current_price       493 non-null float64\n",
      "first_day_raise     497 non-null float64\n",
      "accumulate_raise    490 non-null float64\n",
      "crawltime           0 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](2), float64(6), int64(1), object(6)\n",
      "memory usage: 58.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>code</th>\n",
       "      <th>issue_date</th>\n",
       "      <th>each_hand_stock</th>\n",
       "      <th>share_value_Yi</th>\n",
       "      <th>margin_price</th>\n",
       "      <th>price</th>\n",
       "      <th>over_price_part</th>\n",
       "      <th>hit_least_num</th>\n",
       "      <th>hit_ratio</th>\n",
       "      <th>current_price</th>\n",
       "      <th>first_day_raise</th>\n",
       "      <th>accumulate_raise</th>\n",
       "      <th>crawltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>233</td>\n",
       "      <td>洛钼瑞银八零七购(@EC)</td>\n",
       "      <td>09987.HK</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50</td>\n",
       "      <td>1,961.42</td>\n",
       "      <td>468.00</td>\n",
       "      <td>412.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>14.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>234</td>\n",
       "      <td>农夫山泉</td>\n",
       "      <td>09633.HK</td>\n",
       "      <td>NaT</td>\n",
       "      <td>200</td>\n",
       "      <td>970.40-1,069.93</td>\n",
       "      <td>19.50-21.50</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1,147.3</td>\n",
       "      <td>150.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>235</td>\n",
       "      <td>智勤控股</td>\n",
       "      <td>09913.HK</td>\n",
       "      <td>NaT</td>\n",
       "      <td>4,000</td>\n",
       "      <td>5.20-6.40</td>\n",
       "      <td>0.52-0.64</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.2</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-18.0</td>\n",
       "      <td>-23.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>236</td>\n",
       "      <td>泰格医药</td>\n",
       "      <td>03347.HK</td>\n",
       "      <td>NaT</td>\n",
       "      <td>100</td>\n",
       "      <td>94.22-107.07</td>\n",
       "      <td>88.00-100.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>413.4</td>\n",
       "      <td>60.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>237</td>\n",
       "      <td>立德教育</td>\n",
       "      <td>01449.HK</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1,000</td>\n",
       "      <td>14.00-17.33</td>\n",
       "      <td>2.10-2.60</td>\n",
       "      <td>2.0</td>\n",
       "      <td>26.9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-42.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id           name      code issue_date    ...     current_price first_day_raise accumulate_raise  crawltime\n",
       "0  233  洛钼瑞银八零七购(@EC)  09987.HK        NaT    ...               NaN             0.0              NaN        NaT\n",
       "1  234           农夫山泉  09633.HK        NaT    ...              33.0            54.0             54.0        NaT\n",
       "2  235           智勤控股  09913.HK        NaT    ...               0.0           -18.0            -23.0        NaT\n",
       "3  236           泰格医药  03347.HK        NaT    ...             110.0            13.0             10.0        NaT\n",
       "4  237           立德教育  01449.HK        NaT    ...               1.0           -12.0            -42.0        NaT\n",
       "\n",
       "[5 rows x 15 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 获取第一天收盘大于0的新股数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "win=df[df['first_day_raise']>0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 获取第一天收盘小于0的新股数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "lost=df[df['first_day_raise']<0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "254"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(win)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "181"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(lost)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "140.3314917127072"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(win)/len(lost)*100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算收益期望  概率x收益"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 数据清洗，格式转换， 把 3,000 的字符转为 3000 int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convertor(src):\n",
    "    try:\n",
    "        dst = int(src.strip().replace(',',''))\n",
    "    except:\n",
    "        dst = None\n",
    "    return dst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['each_hand_stock']=df['each_hand_stock'].map(convertor)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 首日涨幅*中签率*每手股数*上市价格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "E=df['first_day_raise']*df['hit_ratio']*df['each_hand_stock']*df['price']/100/100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43882.78999999999"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "E.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "497"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(E)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24500"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "490*50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}