{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import create_engine\n", "import pandas as pd\n", "from datetime import timedelta\n", "import jqdatasdk as jq\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# load existing portfolio" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# check if database need update\n", "def fetch_data_for_table():\n", " '''\n", " return None if no update needed\n", " else return (starttime, endtime, frequency)\n", " '''\n", " pass" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Need to fetch stock price periodically from jqdatasdk, also need to check if portfolio updated, but this case can be handled seperately \n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# fetch stock price from api\n", "def fetch_stock_price():\n", " pass" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "auth success \n" ] } ], "source": [ "# TODO auth, remove later\n", "user_name = \"13126862272\"\n", "password = \"862272\"\n", "jq.auth(user_name, password)" ] }, { "cell_type": "code", "execution_count": 9, "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", "
openclosehighlowvolumemoney
2021-01-05 09:31:0032.5431.7332.5431.721205715.038788451.0
2021-01-05 09:32:0031.6832.0032.0031.391084879.034358448.0
2021-01-05 09:33:0031.9131.6231.9131.61811350.025785154.0
2021-01-05 09:34:0031.6232.0732.0731.62760611.024135183.0
2021-01-05 09:35:0032.1131.9832.1631.98655989.021046238.0
.....................
2021-01-05 11:26:0032.2532.2932.2932.20118071.03807211.0
2021-01-05 11:27:0032.2932.3032.3032.21114251.03686602.0
2021-01-05 11:28:0032.2632.2432.3032.2160077.01938244.0
2021-01-05 11:29:0032.2532.2832.3032.2265634.02117204.0
2021-01-05 11:30:0032.2332.3032.3032.2349052.01582947.0
\n", "

120 rows × 6 columns

\n", "
" ], "text/plain": [ " open close high low volume money\n", "2021-01-05 09:31:00 32.54 31.73 32.54 31.72 1205715.0 38788451.0\n", "2021-01-05 09:32:00 31.68 32.00 32.00 31.39 1084879.0 34358448.0\n", "2021-01-05 09:33:00 31.91 31.62 31.91 31.61 811350.0 25785154.0\n", "2021-01-05 09:34:00 31.62 32.07 32.07 31.62 760611.0 24135183.0\n", "2021-01-05 09:35:00 32.11 31.98 32.16 31.98 655989.0 21046238.0\n", "... ... ... ... ... ... ...\n", "2021-01-05 11:26:00 32.25 32.29 32.29 32.20 118071.0 3807211.0\n", "2021-01-05 11:27:00 32.29 32.30 32.30 32.21 114251.0 3686602.0\n", "2021-01-05 11:28:00 32.26 32.24 32.30 32.21 60077.0 1938244.0\n", "2021-01-05 11:29:00 32.25 32.28 32.30 32.22 65634.0 2117204.0\n", "2021-01-05 11:30:00 32.23 32.30 32.30 32.23 49052.0 1582947.0\n", "\n", "[120 rows x 6 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## exam return type of each api \n", "\n", "# range of stock price in different resolution\n", "single_stock_df = jq.get_price('002709.XSHE',start_date='2021-01-05 9:00:00', end_date='2021-01-05 12:00:00', frequency='1m')\n", "single_stock_df\n", "\n" ] }, { "cell_type": "code", "execution_count": 10, "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", "
timecodeopenclosehighlowvolumemoney
02021-01-05 09:31:00600409.XSHG9.239.169.239.081417593.013000397.0
12021-01-05 09:32:00600409.XSHG9.179.179.179.15394595.03613283.0
22021-01-05 09:33:00600409.XSHG9.169.159.169.13492224.04501667.0
32021-01-05 09:34:00600409.XSHG9.149.219.229.14451578.04152355.0
42021-01-05 09:35:00600409.XSHG9.209.209.229.20503040.04631781.0
...........................
7152021-01-05 11:26:00600415.XSHG5.875.875.875.87279118.01639550.0
7162021-01-05 11:27:00600415.XSHG5.875.875.875.87253667.01490048.0
7172021-01-05 11:28:00600415.XSHG5.875.875.875.87137293.0806465.0
7182021-01-05 11:29:00600415.XSHG5.875.875.875.87218351.01282600.0
7192021-01-05 11:30:00600415.XSHG5.875.875.875.87248941.01462285.0
\n", "

720 rows × 8 columns

\n", "
" ], "text/plain": [ " time code open close high low volume \\\n", "0 2021-01-05 09:31:00 600409.XSHG 9.23 9.16 9.23 9.08 1417593.0 \n", "1 2021-01-05 09:32:00 600409.XSHG 9.17 9.17 9.17 9.15 394595.0 \n", "2 2021-01-05 09:33:00 600409.XSHG 9.16 9.15 9.16 9.13 492224.0 \n", "3 2021-01-05 09:34:00 600409.XSHG 9.14 9.21 9.22 9.14 451578.0 \n", "4 2021-01-05 09:35:00 600409.XSHG 9.20 9.20 9.22 9.20 503040.0 \n", ".. ... ... ... ... ... ... ... \n", "715 2021-01-05 11:26:00 600415.XSHG 5.87 5.87 5.87 5.87 279118.0 \n", "716 2021-01-05 11:27:00 600415.XSHG 5.87 5.87 5.87 5.87 253667.0 \n", "717 2021-01-05 11:28:00 600415.XSHG 5.87 5.87 5.87 5.87 137293.0 \n", "718 2021-01-05 11:29:00 600415.XSHG 5.87 5.87 5.87 5.87 218351.0 \n", "719 2021-01-05 11:30:00 600415.XSHG 5.87 5.87 5.87 5.87 248941.0 \n", "\n", " money \n", "0 13000397.0 \n", "1 3613283.0 \n", "2 4501667.0 \n", "3 4152355.0 \n", "4 4631781.0 \n", ".. ... \n", "715 1639550.0 \n", "716 1490048.0 \n", "717 806465.0 \n", "718 1282600.0 \n", "719 1462285.0 \n", "\n", "[720 rows x 8 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# range of multiple stocks price in different resolution\n", "mul_stocks_df = jq.get_..0n63\n", "\n", "\n", "price(['002709.XSHE',\n", " '002920.XSHE',\n", " '300274.XSHE',\n", " '600409.XSHG',\n", " '600415.XSHG',\n", " '603882.XSHG'],start_date='2021-01-05 9:00:00', end_date='2021-01-05 12:00:00', frequency='1m')\n", "mul_stocks_df\n", "\n" ] }, { "cell_type": "code", "execution_count": 17, "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", "
dateweightdisplay_name
000008.XSHE2020-12-310.088神州高铁
000009.XSHE2020-12-310.344中国宝安
000012.XSHE2020-12-310.180南玻A
000021.XSHE2020-12-310.297深科技
001872.XSHE2020-12-310.030招商港口
............
688002.XSHG2020-12-310.438睿创微纳
688099.XSHG2020-12-310.287晶晨股份
688088.XSHG2020-12-310.252虹软科技
688321.XSHG2020-12-310.134微芯生物
688029.XSHG2020-12-310.130南微医学
\n", "

500 rows × 3 columns

\n", "
" ], "text/plain": [ " date weight display_name\n", "000008.XSHE 2020-12-31 0.088 神州高铁\n", "000009.XSHE 2020-12-31 0.344 中国宝安\n", "000012.XSHE 2020-12-31 0.180 南玻A\n", "000021.XSHE 2020-12-31 0.297 深科技\n", "001872.XSHE 2020-12-31 0.030 招商港口\n", "... ... ... ...\n", "688002.XSHG 2020-12-31 0.438 睿创微纳\n", "688099.XSHG 2020-12-31 0.287 晶晨股份\n", "688088.XSHG 2020-12-31 0.252 虹软科技\n", "688321.XSHG 2020-12-31 0.134 微芯生物\n", "688029.XSHG 2020-12-31 0.130 南微医学\n", "\n", "[500 rows x 3 columns]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# composition of benchmark\n", "jq.get_index_weights('000905.XSHG',date='2021-01-20 9:00:00')\n" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'600409.XSHG': {'sw_l1': {'industry_code': '801030', 'industry_name': '化工I'},\n", " 'sw_l2': {'industry_code': '801033', 'industry_name': '化学原料II'},\n", " 'sw_l3': {'industry_code': '850321', 'industry_name': '纯碱III'},\n", " 'zjw': {'industry_code': 'C26', 'industry_name': '化学原料和化学制品制造业'},\n", " 'jq_l2': {'industry_code': 'HY02107', 'industry_name': '粘胶'},\n", " 'jq_l1': {'industry_code': 'HY002', 'industry_name': '原材料'}},\n", " '603882.XSHG': {'sw_l1': {'industry_code': '801150',\n", " 'industry_name': '医药生物I'},\n", " 'sw_l2': {'industry_code': '801156', 'industry_name': '医疗服务II'},\n", " 'sw_l3': {'industry_code': '851562', 'industry_name': '诊断服务III'},\n", " 'zjw': {'industry_code': 'Q83', 'industry_name': '卫生'},\n", " 'jq_l2': {'industry_code': 'HY06103', 'industry_name': '体外诊断'},\n", " 'jq_l1': {'industry_code': 'HY006', 'industry_name': '医药卫生'}},\n", " '300274.XSHE': {'sw_l1': {'industry_code': '801730',\n", " 'industry_name': '电气设备I'},\n", " 'sw_l2': {'industry_code': '801735', 'industry_name': '光伏设备II'},\n", " 'sw_l3': {'industry_code': '857353', 'industry_name': '逆变器III'},\n", " 'zjw': {'industry_code': 'C38', 'industry_name': '电气机械和器材制造业'},\n", " 'jq_l2': {'industry_code': 'HY03111', 'industry_name': '光伏设备'},\n", " 'jq_l1': {'industry_code': 'HY003', 'industry_name': '工业'}},\n", " '002709.XSHE': {'sw_l1': {'industry_code': '801730',\n", " 'industry_name': '电气设备I'},\n", " 'sw_l2': {'industry_code': '801737', 'industry_name': '电池II'},\n", " 'sw_l3': {'industry_code': '857372', 'industry_name': '电池化学品III'},\n", " 'zjw': {'industry_code': 'C26', 'industry_name': '化学原料和化学制品制造业'},\n", " 'jq_l2': {'industry_code': 'HY03119', 'industry_name': '电池部件及材料'},\n", " 'jq_l1': {'industry_code': 'HY003', 'industry_name': '工业'}},\n", " '002920.XSHE': {'sw_l1': {'industry_code': '801750', 'industry_name': '计算机I'},\n", " 'sw_l2': {'industry_code': '801104', 'industry_name': '软件开发II'},\n", " 'sw_l3': {'industry_code': '851041', 'industry_name': '垂直应用软件III'},\n", " 'zjw': {'industry_code': 'C36', 'industry_name': '汽车制造业'},\n", " 'jq_l2': {'industry_code': 'HY04103', 'industry_name': '汽车电子'},\n", " 'jq_l1': {'industry_code': 'HY004', 'industry_name': '可选消费'}},\n", " '600415.XSHG': {'sw_l1': {'industry_code': '801200',\n", " 'industry_name': '商业贸易I'},\n", " 'sw_l2': {'industry_code': '801203', 'industry_name': '一般零售II'},\n", " 'sw_l3': {'industry_code': '852034', 'industry_name': '商业物业经营III'},\n", " 'zjw': {'industry_code': 'L72', 'industry_name': '商务服务业'},\n", " 'jq_l2': {'industry_code': 'HY03147', 'industry_name': '市场服务'},\n", " 'jq_l1': {'industry_code': 'HY003', 'industry_name': '工业'}}}" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# sector information of each stock\n", "jq.get_industry(['002709.XSHE',\n", " '002920.XSHE',\n", " '300274.XSHE',\n", " '600409.XSHG',\n", " '600415.XSHG',\n", " '603882.XSHG'])\n", "# display name of each stock" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([datetime.date(2005, 1, 4), datetime.date(2005, 1, 5),\n", " datetime.date(2005, 1, 6), ..., datetime.date(2025, 8, 11),\n", " datetime.date(2025, 8, 12), datetime.date(2025, 8, 13)],\n", " dtype=object)" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# get all trading days\n", "jq.get_all_trade_days()" ] }, { "cell_type": "code", "execution_count": 3, "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", "
display_namenamestart_dateend_datetype
000001.XSHE平安银行PAYH1991-04-032200-01-01stock
000002.XSHE万科AWKA1991-01-292200-01-01stock
000004.XSHE国华网安GHWA1990-12-012200-01-01stock
000005.XSHEST星源STXY1990-12-102200-01-01stock
000006.XSHE深振业ASZYA1992-04-272200-01-01stock
..................
688799.XSHG华纳药厂HNYC2021-07-132200-01-01stock
688800.XSHG瑞可达RKD2021-07-222200-01-01stock
688819.XSHG天能股份TNGF2021-01-182200-01-01stock
688981.XSHG中芯国际ZXGJ2020-07-162200-01-01stock
689009.XSHG九号公司JHGS2020-10-292200-01-01stock
\n", "

5248 rows × 5 columns

\n", "
" ], "text/plain": [ " display_name name start_date end_date type\n", "000001.XSHE 平安银行 PAYH 1991-04-03 2200-01-01 stock\n", "000002.XSHE 万科A WKA 1991-01-29 2200-01-01 stock\n", "000004.XSHE 国华网安 GHWA 1990-12-01 2200-01-01 stock\n", "000005.XSHE ST星源 STXY 1990-12-10 2200-01-01 stock\n", "000006.XSHE 深振业A SZYA 1992-04-27 2200-01-01 stock\n", "... ... ... ... ... ...\n", "688799.XSHG 华纳药厂 HNYC 2021-07-13 2200-01-01 stock\n", "688800.XSHG 瑞可达 RKD 2021-07-22 2200-01-01 stock\n", "688819.XSHG 天能股份 TNGF 2021-01-18 2200-01-01 stock\n", "688981.XSHG 中芯国际 ZXGJ 2020-07-16 2200-01-01 stock\n", "689009.XSHG 九号公司 JHGS 2020-10-29 2200-01-01 stock\n", "\n", "[5248 rows x 5 columns]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "jq.get_all_securities()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'engine' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[4], line 9\u001b[0m\n\u001b[1;32m 7\u001b[0m df[\u001b[39m'\u001b[39m\u001b[39mdate\u001b[39m\u001b[39m'\u001b[39m] \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mto_datetime(df[\u001b[39m'\u001b[39m\u001b[39mdate\u001b[39m\u001b[39m'\u001b[39m])\n\u001b[1;32m 8\u001b[0m \u001b[39mreturn\u001b[39;00m df\n\u001b[0;32m----> 9\u001b[0m get_most_recent_profile(\u001b[39m'\u001b[39;49m\u001b[39mportfolio\u001b[39;49m\u001b[39m'\u001b[39;49m)\n", "Cell \u001b[0;32mIn[4], line 5\u001b[0m, in \u001b[0;36mget_most_recent_profile\u001b[0;34m(type)\u001b[0m\n\u001b[1;32m 3\u001b[0m table_name \u001b[39m=\u001b[39m \u001b[39m'\u001b[39m\u001b[39mbenchmark_profile\u001b[39m\u001b[39m'\u001b[39m \u001b[39mif\u001b[39;00m \u001b[39mtype\u001b[39m \u001b[39m==\u001b[39m \u001b[39m'\u001b[39m\u001b[39mbenchmark\u001b[39m\u001b[39m'\u001b[39m \u001b[39melse\u001b[39;00m \u001b[39m'\u001b[39m\u001b[39mportfolio_profile\u001b[39m\u001b[39m'\u001b[39m\n\u001b[1;32m 4\u001b[0m query \u001b[39m=\u001b[39m \u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mSELECT * FROM \u001b[39m\u001b[39m{\u001b[39;00mtable_name\u001b[39m}\u001b[39;00m\u001b[39m WHERE date = (SELECT MAX(date) FROM \u001b[39m\u001b[39m{\u001b[39;00mtable_name\u001b[39m}\u001b[39;00m\u001b[39m)\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m----> 5\u001b[0m df \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mread_sql(query, con\u001b[39m=\u001b[39mengine)\n\u001b[1;32m 6\u001b[0m \u001b[39m# convert date to datetime object\u001b[39;00m\n\u001b[1;32m 7\u001b[0m df[\u001b[39m'\u001b[39m\u001b[39mdate\u001b[39m\u001b[39m'\u001b[39m] \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mto_datetime(df[\u001b[39m'\u001b[39m\u001b[39mdate\u001b[39m\u001b[39m'\u001b[39m])\n", "\u001b[0;31mNameError\u001b[0m: name 'engine' is not defined" ] } ], "source": [ "# when update stock price just need the latest portfolio frame\n", "def get_most_recent_profile(type):\n", " table_name = 'benchmark_profile' if type == 'benchmark' else 'portfolio_profile'\n", " query = f\"SELECT * FROM {table_name} WHERE date = (SELECT MAX(date) FROM {table_name})\"\n", " with create_engine(db_url).connect() as conn:\n", " df = pd.read_sql(query, con=engine)\n", " # convert date to datetime object\n", " df['date'] = pd.to_datetime(df['date'])\n", " return df\n", "get_most_recent_profile('portfolio')" ] } ], "metadata": { "kernelspec": { "display_name": "portfolio_risk_assesment", "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.11.4" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }