本文中Python的版本使用的是3.11，数据库使用的是SQL SERVER 2008，项目运行效果图如下：
蒙特卡洛相关：
突出风险最小点：
夏普比率相关
夏普比率无风险最大点
文章中的股票每日交易数据摘取自：英为财情 https://cn.investing.com/
蒙特卡洛及夏普相关计算参考【胖哥真不错】的一片csdn文章：Python对5支股票的投资组合进行分析_每天买入板块中股价最低5只股票python-CSDN博客
界面代码如下：
界面设计使用pyqt5design,设计界面如下图：
ui代码如下：
<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>MainWindow</class>
<widget class="QMainWindow" name="MainWindow">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>1116</width>
<height>909</height>
</rect>
</property>
<property name="windowTitle">
<string>MainWindow</string>
</property>
<widget class="QWidget" name="centralwidget">
<widget class="QGroupBox" name="groupBox_2">
<property name="geometry">
<rect>
<x>0</x>
<y>140</y>
<width>1111</width>
<height>331</height>
</rect>
</property>
<property name="title">
<string>数据展示</string>
</property>
<widget class="QTableWidget" name="Dt_View">
<property name="geometry">
<rect>
<x>10</x>
<y>20</y>
<width>1091</width>
<height>301</height>
</rect>
</property>
</widget>
</widget>
<widget class="QTabWidget" name="tabWidget">
<property name="geometry">
<rect>
<x>0</x>
<y>470</y>
<width>1111</width>
<height>381</height>
</rect>
</property>
<property name="currentIndex">
<number>0</number>
</property>
<widget class="QWidget" name="tab">
<attribute name="title">
<string>条件筛选数据与行业第一股票组合相关</string>
</attribute>
<widget class="QGroupBox" name="groupBox_3">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>1101</width>
<height>341</height>
</rect>
</property>
<property name="title">
<string>条件筛选</string>
</property>
<widget class="QListView" name="LV_Plate">
<property name="geometry">
<rect>
<x>10</x>
<y>20</y>
<width>191</width>
<height>301</height>
</rect>
</property>
</widget>
<widget class="QLabel" name="label_8">
<property name="geometry">
<rect>
<x>260</x>
<y>160</y>
<width>72</width>
<height>15</height>
</rect>
</property>
<property name="text">
<string>MOMENTUM:</string>
</property>
</widget>
<widget class="QComboBox" name="cbox_momentum">
<property name="geometry">
<rect>
<x>350</x>
<y>160</y>
<width>71</width>
<height>22</height>
</rect>
</property>
</widget>
<widget class="QComboBox" name="cbox_profitability">
<property name="geometry">
<rect>
<x>350</x>
<y>120</y>
<width>71</width>
<height>22</height>
</rect>
</property>
</widget>
<widget class="QComboBox" name="cbox_esp">
<property name="geometry">
<rect>
<x>350</x>
<y>210</y>
<width>71</width>
<height>22</height>
</rect>
</property>
</widget>
<widget class="QComboBox" name="cbox_value">
<property name="geometry">
<rect>
<x>350</x>
<y>40</y>
<width>71</width>
<height>22</height>
</rect>
</property>
</widget>
<widget class="QLabel" name="label_9">
<property name="geometry">
<rect>
<x>240</x>
<y>210</y>
<width>111</width>
<height>16</height>
</rect>
</property>
<property name="text">
<string>EPSREVISION:</string>
</property>
</widget>
<widget class="QLabel" name="label_10">
<property name="geometry">
<rect>
<x>220</x>
<y>120</y>
<width>121</width>
<height>16</height>
</rect>
</property>
<property name="text">
<string>PROFITABILITY:</string>
</property>
</widget>
<widget class="QComboBox" name="cbox_growth">
<property name="geometry">
<rect>
<x>350</x>
<y>80</y>
<width>71</width>
<height>22</height>
</rect>
</property>
</widget>
<widget class="QLabel" name="label_11">
<property name="geometry">
<rect>
<x>290</x>
<y>40</y>
<width>72</width>
<height>15</height>
</rect>
</property>
<property name="text">
<string>VALUE:</string>
</property>
</widget>
<widget class="QLabel" name="label_12">
<property name="geometry">
<rect>
<x>280</x>
<y>80</y>
<width>72</width>
<height>15</height>
</rect>
</property>
<property name="text">
<string>GROWTH:</string>
</property>
</widget>
<widget class="QPushButton" name="pushButton">
<property name="geometry">
<rect>
<x>440</x>
<y>40</y>
<width>171</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>条件筛选生成图表</string>
</property>
</widget>
<widget class="QPushButton" name="export_megred_data">
<property name="geometry">
<rect>
<x>440</x>
<y>100</y>
<width>171</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>导出本次图表数据</string>
</property>
</widget>
<widget class="QTableWidget" name="tw_top_one_data">
<property name="geometry">
<rect>
<x>610</x>
<y>130</y>
<width>481</width>
<height>211</height>
</rect>
</property>
</widget>
<widget class="QPushButton" name="load_top_one_tickets_data">
<property name="geometry">
<rect>
<x>620</x>
<y>40</y>
<width>221</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>加载行业第一市值股票数据</string>
</property>
</widget>
<widget class="QPushButton" name="btn_draw_info">
<property name="geometry">
<rect>
<x>850</x>
<y>40</y>
<width>221</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>行业第一市值与组合的图表</string>
</property>
</widget>
<widget class="QPushButton" name="btn_export_top_one_and_portfolio">
<property name="geometry">
<rect>
<x>860</x>
<y>90</y>
<width>211</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>导出第一市值与组合的数据</string>
</property>
</widget>
<widget class="QPushButton" name="btn_top_one_and_select_option">
<property name="geometry">
<rect>
<x>620</x>
<y>90</y>
<width>221</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>第一市值与条件筛选图表</string>
</property>
</widget>
</widget>
</widget>
<widget class="QWidget" name="tab_2">
<attribute name="title">
<string>蒙特卡洛及夏普相关</string>
</attribute>
<widget class="QPushButton" name="btn_get_mtkl_data">
<property name="geometry">
<rect>
<x>10</x>
<y>10</y>
<width>281</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>获取当前日期的股票每日收益率</string>
</property>
</widget>
<widget class="QTableWidget" name="tw_mtkl_data">
<property name="geometry">
<rect>
<x>10</x>
<y>50</y>
<width>421</width>
<height>251</height>
</rect>
</property>
</widget>
<widget class="QPushButton" name="btn_xie_fang_cha_ju_zhen">
<property name="geometry">
<rect>
<x>440</x>
<y>10</y>
<width>171</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>斜方差矩阵</string>
</property>
</widget>
<widget class="QPushButton" name="btn_year_xie_fang_cha_ju_zhen">
<property name="geometry">
<rect>
<x>770</x>
<y>10</y>
<width>171</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>年化斜方差矩阵</string>
</property>
</widget>
<widget class="QTableWidget" name="tw_xie_fang_cha_ju_zhen">
<property name="geometry">
<rect>
<x>440</x>
<y>50</y>
<width>331</width>
<height>251</height>
</rect>
</property>
</widget>
<widget class="QTableWidget" name="tw_year_xie_fang_cha_ju_zhen">
<property name="geometry">
<rect>
<x>780</x>
<y>50</y>
<width>311</width>
<height>251</height>
</rect>
</property>
</widget>
<widget class="QPushButton" name="btn_mtkl_points_view">
<property name="geometry">
<rect>
<x>50</x>
<y>310</y>
<width>181</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>蒙特卡洛散点图</string>
</property>
</widget>
<widget class="QPushButton" name="btn_mtkl_min_point">
<property name="geometry">
<rect>
<x>250</x>
<y>310</y>
<width>191</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>突出风险最小点</string>
</property>
</widget>
<widget class="QPushButton" name="btn_shap_point_view">
<property name="geometry">
<rect>
<x>490</x>
<y>310</y>
<width>221</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>夏普比率</string>
</property>
</widget>
<widget class="QPushButton" name="btn_shap_point_max">
<property name="geometry">
<rect>
<x>760</x>
<y>310</y>
<width>171</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>夏普比率最大点</string>
</property>
</widget>
</widget>
</widget>
<widget class="QTabWidget" name="tabWidget_2">
<property name="geometry">
<rect>
<x>0</x>
<y>10</y>
<width>1111</width>
<height>131</height>
</rect>
</property>
<property name="currentIndex">
<number>0</number>
</property>
<widget class="QWidget" name="tab_4">
<attribute name="title">
<string>数据查询</string>
</attribute>
<widget class="QGroupBox" name="groupBox">
<property name="geometry">
<rect>
<x>0</x>
<y>10</y>
<width>1101</width>
<height>80</height>
</rect>
</property>
<property name="title">
<string>查询条件</string>
</property>
<widget class="QLabel" name="label">
<property name="geometry">
<rect>
<x>20</x>
<y>30</y>
<width>72</width>
<height>15</height>
</rect>
</property>
<property name="text">
<string>开始日期：</string>
</property>
</widget>
<widget class="QLabel" name="label_2">
<property name="geometry">
<rect>
<x>250</x>
<y>30</y>
<width>72</width>
<height>15</height>
</rect>
</property>
<property name="text">
<string>结束日期：</string>
</property>
</widget>
<widget class="QPushButton" name="Search_Data">
<property name="geometry">
<rect>
<x>490</x>
<y>30</y>
<width>221</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>获取当前日期区间股票数据</string>
</property>
</widget>
<widget class="QDateEdit" name="start_date">
<property name="geometry">
<rect>
<x>100</x>
<y>30</y>
<width>141</width>
<height>22</height>
</rect>
</property>
<property name="dateTime">
<datetime>
<hour>0</hour>
<minute>0</minute>
<second>0</second>
<year>2022</year>
<month>1</month>
<day>1</day>
</datetime>
</property>
<property name="displayFormat">
<string notr="true">yyyy-MM-dd</string>
</property>
<property name="calendarPopup">
<bool>true</bool>
</property>
</widget>
<widget class="QDateEdit" name="end_date">
<property name="geometry">
<rect>
<x>340</x>
<y>30</y>
<width>141</width>
<height>22</height>
</rect>
</property>
<property name="dateTime">
<datetime>
<hour>0</hour>
<minute>0</minute>
<second>0</second>
<year>2024</year>
<month>5</month>
<day>1</day>
</datetime>
</property>
<property name="displayFormat">
<string>yyyy-MM-dd</string>
</property>
<property name="calendarPopup">
<bool>true</bool>
</property>
<property name="date">
<date>
<year>2024</year>
<month>5</month>
<day>1</day>
</date>
</property>
</widget>
<widget class="QPushButton" name="Search_Data_2">
<property name="geometry">
<rect>
<x>720</x>
<y>30</y>
<width>131</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>组合价值曲线</string>
</property>
</widget>
<widget class="QPushButton" name="btn_export_data">
<property name="geometry">
<rect>
<x>880</x>
<y>30</y>
<width>141</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>导出下表数据</string>
</property>
</widget>
</widget>
</widget>
<widget class="QWidget" name="tab_5">
<attribute name="title">
<string>历史数据导入</string>
</attribute>
<widget class="QPushButton" name="btn_ticket_history_data">
<property name="geometry">
<rect>
<x>10</x>
<y>10</y>
<width>261</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>组合股票历史数据导入</string>
</property>
</widget>
<widget class="QPushButton" name="btn_top_one_ticket_data">
<property name="geometry">
<rect>
<x>10</x>
<y>57</y>
<width>261</width>
<height>31</height>
</rect>
</property>
<property name="text">
<string>行业市值第一的股票历史数据导入</string>
</property>
</widget>
</widget>
<widget class="QWidget" name="tab_6">
<attribute name="title">
<string>未来数据获取</string>
</attribute>
<widget class="QPushButton" name="get_new_StockInfo">
<property name="geometry">
<rect>
<x>190</x>
<y>0</y>
<width>151</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>获取最新股票数据</string>
</property>
</widget>
<widget class="QPushButton" name="get_new_indexValue">
<property name="geometry">
<rect>
<x>30</x>
<y>0</y>
<width>131</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>获取最新指数</string>
</property>
</widget>
<widget class="QPushButton" name="btn_get_new_top_one_ticket_data">
<property name="geometry">
<rect>
<x>360</x>
<y>0</y>
<width>281</width>
<height>28</height>
</rect>
</property>
<property name="text">
<string>获取行业市值第一最新股票数据</string>
</property>
</widget>
</widget>
</widget>
</widget>
<widget class="QStatusBar" name="statusbar"/>
<widget class="QMenuBar" name="menubar">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>1116</width>
<height>26</height>
</rect>
</property>
<property name="nativeMenuBar">
<bool>false</bool>
</property>
</widget>
</widget>
<resources/>
<connections/>
</ui>
使用pychrm外部工具可将UI界面的xml可转换为.py文件
转换为MainForm.py代码如下：
# -*- coding: utf-8 -*-
from PyQt5.QtCore import QStringListModel
# Form implementation generated from reading ui file 'MainForm.ui'
#
# Created by: PyQt5 UI code generator 5.15.9
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again. Do not edit this file unless you know what you are doing.
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_MainWindow(object):
def __init__(self):
self.df = None
self.stockcode_df = None
self.index_value_df = None
self.portfolio_value_daily = None
self.old_merged_df = None
self.top_one_tickets_value_df = None
self.top_one_portfolio_value = None
self.top_one_portfolio_value_df = None
self.stock_return = None
self.xie_fang_cha = None
self.year_xie_fang_cha = None
self.randomp_ortfolios = None
self.lv_model=QStringListModel()
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(1116, 909)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.groupBox_2 = QtWidgets.QGroupBox(self.centralwidget)
self.groupBox_2.setGeometry(QtCore.QRect(0, 140, 1111, 331))
self.groupBox_2.setObjectName("groupBox_2")
self.Dt_View = QtWidgets.QTableWidget(self.groupBox_2)
self.Dt_View.setGeometry(QtCore.QRect(10, 20, 1091, 301))
self.Dt_View.setObjectName("Dt_View")
self.Dt_View.setColumnCount(0)
self.Dt_View.setRowCount(0)
self.tabWidget = QtWidgets.QTabWidget(self.centralwidget)
self.tabWidget.setGeometry(QtCore.QRect(0, 470, 1111, 381))
self.tabWidget.setObjectName("tabWidget")
self.tab = QtWidgets.QWidget()
self.tab.setObjectName("tab")
self.groupBox_3 = QtWidgets.QGroupBox(self.tab)
self.groupBox_3.setGeometry(QtCore.QRect(0, 0, 1101, 341))
self.groupBox_3.setObjectName("groupBox_3")
self.LV_Plate = QtWidgets.QListView(self.groupBox_3)
self.LV_Plate.setGeometry(QtCore.QRect(10, 20, 191, 301))
self.LV_Plate.setObjectName("LV_Plate")
self.label_8 = QtWidgets.QLabel(self.groupBox_3)
self.label_8.setGeometry(QtCore.QRect(260, 160, 72, 15))
self.label_8.setObjectName("label_8")
self.cbox_momentum = QtWidgets.QComboBox(self.groupBox_3)
self.cbox_momentum.setGeometry(QtCore.QRect(350, 160, 71, 22))
self.cbox_momentum.setObjectName("cbox_momentum")
self.cbox_profitability = QtWidgets.QComboBox(self.groupBox_3)
self.cbox_profitability.setGeometry(QtCore.QRect(350, 120, 71, 22))
self.cbox_profitability.setObjectName("cbox_profitability")
self.cbox_esp = QtWidgets.QComboBox(self.groupBox_3)
self.cbox_esp.setGeometry(QtCore.QRect(350, 210, 71, 22))
self.cbox_esp.setObjectName("cbox_esp")
self.cbox_value = QtWidgets.QComboBox(self.groupBox_3)
self.cbox_value.setGeometry(QtCore.QRect(350, 40, 71, 22))
self.cbox_value.setObjectName("cbox_value")
self.label_9 = QtWidgets.QLabel(self.groupBox_3)
self.label_9.setGeometry(QtCore.QRect(240, 210, 111, 16))
self.label_9.setObjectName("label_9")
self.label_10 = QtWidgets.QLabel(self.groupBox_3)
self.label_10.setGeometry(QtCore.QRect(220, 120, 121, 16))
self.label_10.setObjectName("label_10")
self.cbox_growth = QtWidgets.QComboBox(self.groupBox_3)
self.cbox_growth.setGeometry(QtCore.QRect(350, 80, 71, 22))
self.cbox_growth.setObjectName("cbox_growth")
self.label_11 = QtWidgets.QLabel(self.groupBox_3)
self.label_11.setGeometry(QtCore.QRect(290, 40, 72, 15))
self.label_11.setObjectName("label_11")
self.label_12 = QtWidgets.QLabel(self.groupBox_3)
self.label_12.setGeometry(QtCore.QRect(280, 80, 72, 15))
self.label_12.setObjectName("label_12")
self.pushButton = QtWidgets.QPushButton(self.groupBox_3)
self.pushButton.setGeometry(QtCore.QRect(440, 40, 171, 28))
self.pushButton.setObjectName("pushButton")
self.export_megred_data = QtWidgets.QPushButton(self.groupBox_3)
self.export_megred_data.setGeometry(QtCore.QRect(440, 100, 171, 28))
self.export_megred_data.setObjectName("export_megred_data")
self.tw_top_one_data = QtWidgets.QTableWidget(self.groupBox_3)
self.tw_top_one_data.setGeometry(QtCore.QRect(610, 130, 481, 211))
self.tw_top_one_data.setObjectName("tw_top_one_data")
self.tw_top_one_data.setColumnCount(0)
self.tw_top_one_data.setRowCount(0)
self.load_top_one_tickets_data = QtWidgets.QPushButton(self.groupBox_3)
self.load_top_one_tickets_data.setGeometry(QtCore.QRect(620, 40, 221, 28))
self.load_top_one_tickets_data.setObjectName("load_top_one_tickets_data")
self.btn_draw_info = QtWidgets.QPushButton(self.groupBox_3)
self.btn_draw_info.setGeometry(QtCore.QRect(850, 40, 221, 28))
self.btn_draw_info.setObjectName("btn_draw_info")
self.btn_export_top_one_and_portfolio = QtWidgets.QPushButton(self.groupBox_3)
self.btn_export_top_one_and_portfolio.setGeometry(QtCore.QRect(860, 90, 211, 28))
self.btn_export_top_one_and_portfolio.setObjectName("btn_export_top_one_and_portfolio")
self.btn_top_one_and_select_option = QtWidgets.QPushButton(self.groupBox_3)
self.btn_top_one_and_select_option.setGeometry(QtCore.QRect(620, 90, 221, 28))
self.btn_top_one_and_select_option.setObjectName("btn_top_one_and_select_option")
self.tabWidget.addTab(self.tab, "")
self.tab_2 = QtWidgets.QWidget()
self.tab_2.setObjectName("tab_2")
self.btn_get_mtkl_data = QtWidgets.QPushButton(self.tab_2)
self.btn_get_mtkl_data.setGeometry(QtCore.QRect(10, 10, 281, 28))
self.btn_get_mtkl_data.setObjectName("btn_get_mtkl_data")
self.tw_mtkl_data = QtWidgets.QTableWidget(self.tab_2)
self.tw_mtkl_data.setGeometry(QtCore.QRect(10, 50, 421, 251))
self.tw_mtkl_data.setObjectName("tw_mtkl_data")
self.tw_mtkl_data.setColumnCount(0)
self.tw_mtkl_data.setRowCount(0)
self.btn_xie_fang_cha_ju_zhen = QtWidgets.QPushButton(self.tab_2)
self.btn_xie_fang_cha_ju_zhen.setGeometry(QtCore.QRect(440, 10, 171, 28))
self.btn_xie_fang_cha_ju_zhen.setObjectName("btn_xie_fang_cha_ju_zhen")
self.btn_year_xie_fang_cha_ju_zhen = QtWidgets.QPushButton(self.tab_2)
self.btn_year_xie_fang_cha_ju_zhen.setGeometry(QtCore.QRect(770, 10, 171, 28))
self.btn_year_xie_fang_cha_ju_zhen.setObjectName("btn_year_xie_fang_cha_ju_zhen")
self.tw_xie_fang_cha_ju_zhen = QtWidgets.QTableWidget(self.tab_2)
self.tw_xie_fang_cha_ju_zhen.setGeometry(QtCore.QRect(440, 50, 331, 251))
self.tw_xie_fang_cha_ju_zhen.setObjectName("tw_xie_fang_cha_ju_zhen")
self.tw_xie_fang_cha_ju_zhen.setColumnCount(0)
self.tw_xie_fang_cha_ju_zhen.setRowCount(0)
self.tw_year_xie_fang_cha_ju_zhen = QtWidgets.QTableWidget(self.tab_2)
self.tw_year_xie_fang_cha_ju_zhen.setGeometry(QtCore.QRect(780, 50, 311, 251))
self.tw_year_xie_fang_cha_ju_zhen.setObjectName("tw_year_xie_fang_cha_ju_zhen")
self.tw_year_xie_fang_cha_ju_zhen.setColumnCount(0)
self.tw_year_xie_fang_cha_ju_zhen.setRowCount(0)
self.btn_mtkl_points_view = QtWidgets.QPushButton(self.tab_2)
self.btn_mtkl_points_view.setGeometry(QtCore.QRect(50, 310, 181, 28))
self.btn_mtkl_points_view.setObjectName("btn_mtkl_points_view")
self.btn_mtkl_min_point = QtWidgets.QPushButton(self.tab_2)
self.btn_mtkl_min_point.setGeometry(QtCore.QRect(250, 310, 191, 28))
self.btn_mtkl_min_point.setObjectName("btn_mtkl_min_point")
self.btn_shap_point_view = QtWidgets.QPushButton(self.tab_2)
self.btn_shap_point_view.setGeometry(QtCore.QRect(490, 310, 221, 28))
self.btn_shap_point_view.setObjectName("btn_shap_point_view")
self.btn_shap_point_max = QtWidgets.QPushButton(self.tab_2)
self.btn_shap_point_max.setGeometry(QtCore.QRect(760, 310, 171, 28))
self.btn_shap_point_max.setObjectName("btn_shap_point_max")
self.tabWidget.addTab(self.tab_2, "")
self.tabWidget_2 = QtWidgets.QTabWidget(self.centralwidget)
self.tabWidget_2.setGeometry(QtCore.QRect(0, 10, 1111, 131))
self.tabWidget_2.setObjectName("tabWidget_2")
self.tab_4 = QtWidgets.QWidget()
self.tab_4.setObjectName("tab_4")
self.groupBox = QtWidgets.QGroupBox(self.tab_4)
self.groupBox.setGeometry(QtCore.QRect(0, 10, 1101, 80))
self.groupBox.setObjectName("groupBox")
self.label = QtWidgets.QLabel(self.groupBox)
self.label.setGeometry(QtCore.QRect(20, 30, 72, 15))
self.label.setObjectName("label")
self.label_2 = QtWidgets.QLabel(self.groupBox)
self.label_2.setGeometry(QtCore.QRect(250, 30, 72, 15))
self.label_2.setObjectName("label_2")
self.Search_Data = QtWidgets.QPushButton(self.groupBox)
self.Search_Data.setGeometry(QtCore.QRect(490, 30, 221, 28))
self.Search_Data.setObjectName("Search_Data")
self.start_date = QtWidgets.QDateEdit(self.groupBox)
self.start_date.setGeometry(QtCore.QRect(100, 30, 141, 22))
self.start_date.setDateTime(QtCore.QDateTime(QtCore.QDate(2022, 1, 1), QtCore.QTime(0, 0, 0)))
self.start_date.setDisplayFormat("yyyy-MM-dd")
self.start_date.setCalendarPopup(True)
self.start_date.setObjectName("start_date")
self.end_date = QtWidgets.QDateEdit(self.groupBox)
self.end_date.setGeometry(QtCore.QRect(340, 30, 141, 22))
self.end_date.setDateTime(QtCore.QDateTime(QtCore.QDate(2024, 5, 1), QtCore.QTime(0, 0, 0)))
self.end_date.setCalendarPopup(True)
self.end_date.setDate(QtCore.QDate(2024, 5, 1))
self.end_date.setObjectName("end_date")
self.Search_Data_2 = QtWidgets.QPushButton(self.groupBox)
self.Search_Data_2.setGeometry(QtCore.QRect(720, 30, 131, 28))
self.Search_Data_2.setObjectName("Search_Data_2")
self.btn_export_data = QtWidgets.QPushButton(self.groupBox)
self.btn_export_data.setGeometry(QtCore.QRect(880, 30, 141, 28))
self.btn_export_data.setObjectName("btn_export_data")
self.tabWidget_2.addTab(self.tab_4, "")
self.tab_5 = QtWidgets.QWidget()
self.tab_5.setObjectName("tab_5")
self.btn_ticket_history_data = QtWidgets.QPushButton(self.tab_5)
self.btn_ticket_history_data.setGeometry(QtCore.QRect(10, 10, 261, 28))
self.btn_ticket_history_data.setObjectName("btn_ticket_history_data")
self.btn_top_one_ticket_data = QtWidgets.QPushButton(self.tab_5)
self.btn_top_one_ticket_data.setGeometry(QtCore.QRect(10, 57, 261, 31))
self.btn_top_one_ticket_data.setObjectName("btn_top_one_ticket_data")
self.tabWidget_2.addTab(self.tab_5, "")
self.tab_6 = QtWidgets.QWidget()
self.tab_6.setObjectName("tab_6")
self.get_new_StockInfo = QtWidgets.QPushButton(self.tab_6)
self.get_new_StockInfo.setGeometry(QtCore.QRect(190, 0, 151, 28))
self.get_new_StockInfo.setObjectName("get_new_StockInfo")
self.get_new_indexValue = QtWidgets.QPushButton(self.tab_6)
self.get_new_indexValue.setGeometry(QtCore.QRect(30, 0, 131, 28))
self.get_new_indexValue.setObjectName("get_new_indexValue")
self.btn_get_new_top_one_ticket_data = QtWidgets.QPushButton(self.tab_6)
self.btn_get_new_top_one_ticket_data.setGeometry(QtCore.QRect(360, 0, 281, 28))
self.btn_get_new_top_one_ticket_data.setObjectName("btn_get_new_top_one_ticket_data")
self.tabWidget_2.addTab(self.tab_6, "")
MainWindow.setCentralWidget(self.centralwidget)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 1116, 26))
self.menubar.setNativeMenuBar(False)
self.menubar.setObjectName("menubar")
MainWindow.setMenuBar(self.menubar)
self.retranslateUi(MainWindow)
self.tabWidget.setCurrentIndex(0)
self.tabWidget_2.setCurrentIndex(0)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.groupBox_2.setTitle(_translate("MainWindow", "数据展示"))
self.groupBox_3.setTitle(_translate("MainWindow", "条件筛选"))
self.label_8.setText(_translate("MainWindow", "MOMENTUM:"))
self.label_9.setText(_translate("MainWindow", "EPSREVISION:"))
self.label_10.setText(_translate("MainWindow", "PROFITABILITY:"))
self.label_11.setText(_translate("MainWindow", "VALUE:"))
self.label_12.setText(_translate("MainWindow", "GROWTH:"))
self.pushButton.setText(_translate("MainWindow", "条件筛选生成图表"))
self.export_megred_data.setText(_translate("MainWindow", "导出本次图表数据"))
self.load_top_one_tickets_data.setText(_translate("MainWindow", "加载行业第一市值股票数据"))
self.btn_draw_info.setText(_translate("MainWindow", "行业第一市值与组合的图表"))
self.btn_export_top_one_and_portfolio.setText(_translate("MainWindow", "导出第一市值与组合的数据"))
self.btn_top_one_and_select_option.setText(_translate("MainWindow", "第一市值与条件筛选图表"))
self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "条件筛选数据与行业第一股票组合相关"))
self.btn_get_mtkl_data.setText(_translate("MainWindow", "获取当前日期的股票每日收益率"))
self.btn_xie_fang_cha_ju_zhen.setText(_translate("MainWindow", "斜方差矩阵"))
self.btn_year_xie_fang_cha_ju_zhen.setText(_translate("MainWindow", "年化斜方差矩阵"))
self.btn_mtkl_points_view.setText(_translate("MainWindow", "蒙特卡洛散点图"))
self.btn_mtkl_min_point.setText(_translate("MainWindow", "突出风险最小点"))
self.btn_shap_point_view.setText(_translate("MainWindow", "夏普比率"))
self.btn_shap_point_max.setText(_translate("MainWindow", "夏普比率最大点"))
self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "蒙特卡洛及夏普相关"))
self.groupBox.setTitle(_translate("MainWindow", "查询条件"))
self.label.setText(_translate("MainWindow", "开始日期："))
self.label_2.setText(_translate("MainWindow", "结束日期："))
self.Search_Data.setText(_translate("MainWindow", "获取当前日期区间股票数据"))
self.end_date.setDisplayFormat(_translate("MainWindow", "yyyy-MM-dd"))
self.Search_Data_2.setText(_translate("MainWindow", "组合价值曲线"))
self.btn_export_data.setText(_translate("MainWindow", "导出下表数据"))
self.tabWidget_2.setTabText(self.tabWidget_2.indexOf(self.tab_4), _translate("MainWindow", "数据查询"))
self.btn_ticket_history_data.setText(_translate("MainWindow", "组合股票历史数据导入"))
self.btn_top_one_ticket_data.setText(_translate("MainWindow", "行业市值第一的股票历史数据导入"))
self.tabWidget_2.setTabText(self.tabWidget_2.indexOf(self.tab_5), _translate("MainWindow", "历史数据导入"))
self.get_new_StockInfo.setText(_translate("MainWindow", "获取最新股票数据"))
self.get_new_indexValue.setText(_translate("MainWindow", "获取最新指数"))
self.btn_get_new_top_one_ticket_data.setText(_translate("MainWindow", "获取行业市值第一最新股票数据"))
self.tabWidget_2.setTabText(self.tabWidget_2.indexOf(self.tab_6), _translate("MainWindow", "未来数据获取"))
背后的数据库表如下：
TicketHistoryData(股票历史数据)
IndexValueHistoryData（指数历史数据）
Tickets(股票代码表)
数据库访问帮助：
import pyodbc
import pandas as pd
class MSSQL:
def __init__(self, IP, UserID, Pwd, db):
self.host = IP;
self.user = UserID;
self.password = Pwd;
self.dbname = db;
def __getConnect(self):
if not self.dbname:
raise (NameError, "db name undefine error")
else:
connSTR = "Driver={ODBC Driver 17 for SQL Server};SERVER=%s,1433;DATABASE=%s;UID=%s;PWD=%s" % (
self.host, self.dbname, self.user, self.password)
##example: Driver={ODBC Driver 11 for SQL Server};SERVER=127.0.0.1,1433;DATABASE=MyTestDB;UID=sa;PWD=Abc123
self.conn = pyodbc.connect(connSTR);
cu = self.conn
if not cu:
raise (NameError, "db connect error");
else:
return cu;
def ExecuteTableQuery(self, selectSql):
cuu = self.__getConnect();
selectRows = cuu.execute(selectSql);
resList = selectRows.fetchall();
self.conn.close();
return resList;
def ExecuteTableQueryHead(self,selectSql):
connSTR = "Driver={ODBC Driver 17 for SQL Server};SERVER=%s,1433;DATABASE=%s;UID=%s;PWD=%s" % (
self.host, self.dbname, self.user, self.password)
conn =pyodbc.connect(connSTR)
df=pd.read_sql(selectSql,conn)
return df
# 获取列名
columns = [column[0] for column in cursor.description]
# 将数据转换为DataFrame
df = pd.DataFrame(cursor.fetchall())
df.columns = columns
# 关闭游标和连接
cursor.close()
conn.close()
return df
def ExecuteTableByProc(self,start_date,end_date):
connSTR = "Driver={ODBC Driver 17 for SQL Server};SERVER=%s,1433;DATABASE=%s;UID=%s;PWD=%s" % (
self.host, self.dbname, self.user, self.password)
conn =pyodbc.connect(connSTR)
#cursor=conn.cursor()
sql="{CALL PIVOT_TicketsHisData (?, ?)}"
# 读取数据到 Pandas DataFrame
df = pd.read_sql_query(sql,params=(start_date,end_date),con=conn) # 如果存储过程需要参数，这里需要传递参数列表
# 关闭游标和连接
#cursor.close()
conn.close()
return df
def ExecuteNonQuery(self, sql):
cuu = self.__getConnect();
cuu.execute(sql);
self.conn.commit();
self.conn.close();
def PrintODBCDrivers(self):
print(pyodbc.drivers());通用方法py文件
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import DBHelper
import CommonHelper as common
import warnings
import configparser
warnings.filterwarnings('ignore', category=UserWarning)
warnings.filterwarnings('ignore', category=FutureWarning)
# 指定中文字体，例如使用微软雅黑
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号
# 读取配置文件
configer = configparser.ConfigParser()
configer.read('config.ini')
Ip = configer.get('database','ip')
# 预定义数据库连接
db = DBHelper.MSSQL(IP=Ip, UserID="sa", Pwd="dj@1234", db="MGData")
# 初始投资额
initial_investment = 10000000
# 等级到数值的映射（你可以根据你的实际情况来调整这些值）
grade_to_value = {
'A+': 6,
'A': 5.5,
'A-': 5,
'B+': 4.5,
'B': 4,
'B-': 3.5,
'C+': 3,
'C': 2.5,
'C-': 2,
'D+': 1.5,
'D': 1,
'D-': 0.5,
'E': 0
}
def select_index_value(start_date, end_date):
"""
查询指数信息
:param start_date: 开始日期
:param end_date: 结束日期
:return: 返回指数df
"""
# 查询指数数据
index_value_sql = (f"SELECT ExponentDate as date,isnull(DJI,0) as dji,isnull(NDX,0) as ndx,isnull(SPXINX,0) as inx "
f"FROM "
f"( "
f" SELECT ExponentDate, ExponentCode, ExponentADJClose "
f" FROM MG_ExponentHistoryData "
f") AS SourceTable "
f"PIVOT "
f"( "
f" SUM(ExponentADJClose) "
f" FOR ExponentCode IN (DJI,NDX,SPXINX) "
f") AS PivotTable "
f"where ExponentDate>='{start_date}' and ExponentDate<='{end_date}' order by ExponentDate asc")
index_value_df = db.ExecuteTableQueryHead(index_value_sql)
index_value_df = pd.DataFrame(index_value_df)
index_value_df['date'] = pd.to_datetime(index_value_df['date'])
index_value_df.set_index('date', inplace=True)
return index_value_df
def select_stockcode_df(start_date, end_date):
"""
查询股票代码及行业信息，其余指标信息
:param start_date: 开始日期
:param end_date: 结束日期
:return: 返回stockcode_df
"""
stockcode_sql = (f"select RTRIM(A.TicketCode) stockcode,B.PlateName,B.IndustryName,"
f"RTRIM(B.SValue) as SValue,RTRIM(B.GROWTH) as GROWTH,RTRIM(B.PROFITABILITY) as PROFITABILITY,"
f"RTRIM(B.MOMENTUM) as MOMENTUM,RTRIM(B.EPSREVISION) as EPSREVISION from "
f"(select distinct TicketCode from dbo.MG_TicketsHistoryData where "
f"TicketDate>='{start_date}' and TicketDate<='{end_date}' ) as A "
f"inner join MG_Tickets B on A.TicketCode=B.TicketCode order by A.TicketCode")
data_stockcode = db.ExecuteTableQueryHead(stockcode_sql)
stockcode_df = pd.DataFrame(data_stockcode)
return stockcode_df
def select_stockcode_df_ex(stockcode_df):
"""
等级映射方法
:param stockcode_df:
:return:
"""
stockcode_df['SValue_grade'] = stockcode_df['SValue'].map(grade_to_value)
stockcode_df['GROWTH_grade'] = stockcode_df['GROWTH'].map(grade_to_value)
stockcode_df['PROFITABILITY_grade'] = stockcode_df['PROFITABILITY'].map(grade_to_value)
stockcode_df['MOMENTUM_grade'] = stockcode_df['MOMENTUM'].map(grade_to_value)
stockcode_df['EPSREVISION_grade'] = stockcode_df['EPSREVISION'].map(grade_to_value)
return stockcode_df
def select_df(start_date, end_date):
"""
计算组合价值
:param start_date:
:param end_date:
:return:
"""
# 查询数据库股票的数据：
data_sql = (
f"select T1.TicketDate as date,RTRIM(T1.TicketCode) as stockcode ,T1.TicketADJClose as price,T2.PlateName,"
f"T2.IndustryName,T2.SValue,T2.GROWTH,T2.PROFITABILITY,T2.MOMENTUM,T2.EPSREVISION "
f"from MG_TicketsHistoryData T1 inner join MG_Tickets T2 on T1.TicketCode=T2.TicketCode "
f"where TicketDate>='{start_date}' and TicketDate<='{end_date}' order by TicketDate asc")
data = db.ExecuteTableQueryHead(data_sql)
df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(by='date')
return df
def select_portfolio_value(df):
portfolio_value_daily = common.Calc_Portfolio(initial_investment=initial_investment, df=df)
return portfolio_value_daily
def merge_index_value_and_portfolio_value(index_value_df, portfolio_value_daily):
"""
返回指数与组合价值的组合表
:param index_value_df:
:param portfolio_value_daily:
:return:
"""
# 组合价值与指数的数据组合
# df_pf_inx=common.return_df_value_index(index_value_df,portfolio_value_daily)
df_pf_inx = index_value_df # 指数时间序列
pf_df = portfolio_value_daily[['date', 'portfolio_value']] # 组合时间序列
df_pf_inx = pd.merge(df_pf_inx, pf_df, on='date', how='left') # 将指数，组合时间序列合并
df_pf_inx = df_pf_inx.dropna() # 删除有空值的行
df_pf_inx.set_index('date', inplace=True) # 设置date为索引
return df_pf_inx
def draw_portfolio_value(df_pf_inx):
common.Draw_plt(df_pf_inx, "股票组合价值及指数时间序列图")
def select_plate(start_date, end_date):
"""
查询当前日期区间的股票板块信息
:param start_date: 开始日期
:param end_date: 结束日期
:return: 返回plate_df
"""
plate_sql = f"select PlateName from dbo.MG_Tickets group by PlateName"
data_plate = db.ExecuteTableQueryHead(plate_sql)
plate_df = pd.DataFrame(data_plate)
return plate_df
def selct_data_by_input(df, stockcode_df, merged_df, plates, value, growth, profitability, momentum, eps):
input_plate_list = []
if plates is not None:
input_plate_list = plates.split(',')
df_plate = pd.DataFrame() # 板块筛选的数据
df_value = pd.DataFrame() # value筛选的数据
df_growth = pd.DataFrame() # growth筛选的数据
df_profitability = pd.DataFrame() # profitability筛选的数据
df_momentum = pd.DataFrame() # momentum值筛选的数据
df_eps = pd.DataFrame() # eps值筛选的数据
merged_df = merged_df
df_input_flag = df
code_df = stockcode_df
if len(input_plate_list) > 0: # 根据所属板块筛选数据
code_df = code_df[code_df['PlateName'].isin(input_plate_list)]
code_list = code_df.get('stockcode')
if len(input_plate_list) > 1:
for pl in input_plate_list: # 将每一个板块对应的数据添加到df中
code_df_pl = code_df[code_df['PlateName'] == pl]
if len(code_df_pl) > 0:
code_list_pl = code_df_pl.get('stockcode')
condition_plat2 = df['stockcode'].isin(code_list_pl)
df_plate = df[condition_plat2]
df_plate = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_plate)
df_plate.rename(columns={'portfolio_value': 'portfolio_plateis' + pl},
inplace=True)
df_plate = df_plate[['date', 'portfolio_plateis' + pl]]
merged_df = pd.merge(merged_df, df_plate, on='date', how='left') # 合并每一个板块数据
condition_plate = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_plate]
condition_plate2 = df['stockcode'].isin(code_list)
df_plate = df[condition_plate2]
df_plate = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_plate)
df_plate.rename(columns={'portfolio_value': 'portfolio_plate' + plates.replace(',', '')},
inplace=True)
df_plate = df_plate[['date', 'portfolio_plate' + plates.replace(',', '')]]
merged_df = pd.merge(merged_df, df_plate, on='date', how='left')
else:
if len(code_list) > 0:
condition_plate = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_plate]
condition_plate2 = df['stockcode'].isin(code_list)
df_plate = df[condition_plate2]
df_plate = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_plate)
df_plate.rename(columns={'portfolio_value': 'portfolio_plate' + plates.replace(',', '')},
inplace=True)
df_plate = df_plate[['date', 'portfolio_plate' + plates.replace(',', '')]]
merged_df = pd.merge(merged_df, df_plate, on='date', how='left')
if len(value) > 0: # 根据value值筛选数据
int_value = float(grade_to_value[value])
code_df_value = code_df.copy()
code_df_value = code_df_value[code_df_value['SValue_grade'] >= int_value]
code_list = code_df_value.get('stockcode')
if len(code_list) > 0:
condition_value = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_value]
df_value = df.copy()
condition_value2 = df_value['stockcode'].isin(code_list)
df_value = df_value[condition_value2]
df_value = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_value)
df_value.rename(columns={'portfolio_value': 'portfolio_svalue' + value},
inplace=True)
df_value = df_value[['date', 'portfolio_svalue' + value]]
merged_df = pd.merge(merged_df, df_value, on='date', how='left')
if len(growth) > 0: # 根据growth值筛选数据
int_growth = float(grade_to_value[growth])
code_df_growth = code_df.copy()
code_df_growth = code_df_growth[code_df_growth['GROWTH_grade'] >= int_growth]
code_list = code_df_growth.get('stockcode')
if len(code_list) > 0:
condition_growth = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_growth]
df_growth = df.copy()
condition_growth2 = df_growth['stockcode'].isin(code_list)
df_growth = df_growth[condition_growth2]
df_growth = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_growth)
df_growth.rename(columns={'portfolio_value': 'portfolio_growth' + growth},
inplace=True)
df_growth = df_growth[['date', 'portfolio_growth' + growth]]
merged_df = pd.merge(merged_df, df_growth, on='date', how='left')
if len(profitability) > 0: # 根据PROFITABILITY_grade值筛选数据 MOMENTUM_grade
int_profitability = float(grade_to_value[profitability])
code_df_profitability = code_df.copy()
code_df_profitability = code_df_profitability[code_df_profitability['PROFITABILITY_grade'] >= int_profitability]
code_list = code_df_profitability.get('stockcode')
if len(code_list) > 0:
condition_profitability = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_profitability]
df_profitability = df.copy()
condition_profitability2 = df_profitability['stockcode'].isin(code_list)
df_profitability = df_profitability[condition_profitability2]
df_profitability = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_profitability)
df_profitability.rename(
columns={'portfolio_value': 'portfolio_profitability' + profitability},
inplace=True)
df_profitability = df_profitability[
['date', 'portfolio_profitability' + profitability]]
merged_df = pd.merge(merged_df, df_profitability, on='date', how='left')
if len(momentum) > 0: # 根据MOMENTUM_grade值筛选数据
int_momentum = float(grade_to_value[momentum])
code_df_momentum = code_df.copy()
code_df_momentum = code_df_momentum[code_df_momentum['MOMENTUM_grade'] >= int_momentum]
code_list = code_df_momentum.get('stockcode')
if len(code_list) > 0:
condition_momentum = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_momentum]
df_momentum = df.copy()
condition_momentum2 = df_momentum['stockcode'].isin(code_list)
df_momentum = df_momentum[condition_momentum2]
df_momentum = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_momentum)
df_momentum.rename(
columns={'portfolio_value': 'portfolio_momentum' + momentum},
inplace=True)
df_momentum = df_momentum[['date', 'portfolio_momentum' + momentum]]
merged_df = pd.merge(merged_df, df_momentum, on='date', how='left')
if len(eps) > 0: # 根据EPSREVISION_grade值筛选数据
int_eps_revision = float(grade_to_value[eps])
code_df_eps = code_df.copy()
code_df_eps = code_df_eps[code_df_eps['EPSREVISION_grade'] >= int_eps_revision]
code_list = code_df_eps.get('stockcode')
if len(code_list) > 0:
condition_eps_revision = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_eps_revision]
df_eps = df.copy()
condition_eps_revision2 = df_eps['stockcode'].isin(code_list)
df_eps = df_eps[condition_eps_revision2]
df_eps = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_eps)
df_eps.rename(
columns={'portfolio_value': 'portfolio_eps' + eps},
inplace=True)
df_eps = df_eps[['date', 'portfolio_eps' + eps]]
merged_df = pd.merge(merged_df, df_eps, on='date', how='left')
portfolio_value_daily_flag = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_input_flag)
if len(portfolio_value_daily_flag) > 0:
portfolio_value_daily_flag = portfolio_value_daily_flag.rename(
columns={'portfolio_value': 'portfolio_value多条件筛选'})
portfolio_value_daily_flag = portfolio_value_daily_flag[['date', 'portfolio_value多条件筛选']]
merged_df = pd.merge(merged_df, portfolio_value_daily_flag, on='date', how='left')
old_merged_df = merged_df.copy()
# 创建一个集合来存储不重复的列名
unique_cols = set()
# 遍历所有列，检查是否已存在数值上完全相同的列
for col in merged_df.columns:
if not any(merged_df[col].equals(merged_df[other_col]) for other_col in unique_cols):
unique_cols.add(col)
# 对列名进行排序
unique_cols = sorted(unique_cols)
# 创建一个新的数据框，只包含不重复的列
merged_df = merged_df[list(unique_cols)]
merged_df.set_index('date', inplace=True)
# 绘制指定日期区间内的组合价值及随机组合的价值对应的时间序列图
common.Draw_plt(merged_df, "股票组合价值时间序列图")
return old_merged_df
def selct_data_by_input_and_top_one(df, stockcode_df, merged_df, plates, value, growth, profitability, momentum, eps,top_one):
input_plate_list = []
if plates is not None:
input_plate_list = plates.split(',')
df_plate = pd.DataFrame() # 板块筛选的数据
df_value = pd.DataFrame() # value筛选的数据
df_growth = pd.DataFrame() # growth筛选的数据
df_profitability = pd.DataFrame() # profitability筛选的数据
df_momentum = pd.DataFrame() # momentum值筛选的数据
df_eps = pd.DataFrame() # eps值筛选的数据
merged_df = merged_df
df_input_flag = df
code_df = stockcode_df
if len(input_plate_list) > 0: # 根据所属板块筛选数据
code_df = code_df[code_df['PlateName'].isin(input_plate_list)]
code_list = code_df.get('stockcode')
if len(input_plate_list) > 1:
for pl in input_plate_list: # 将每一个板块对应的数据添加到df中
code_df_pl = code_df[code_df['PlateName'] == pl]
if len(code_df_pl) > 0:
code_list_pl = code_df_pl.get('stockcode')
condition_plat2 = df['stockcode'].isin(code_list_pl)
df_plate = df[condition_plat2]
df_plate = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_plate)
df_plate.rename(columns={'portfolio_value': 'portfolio_plateis' + pl},
inplace=True)
df_plate = df_plate[['date', 'portfolio_plateis' + pl]]
merged_df = pd.merge(merged_df, df_plate, on='date', how='left') # 合并每一个板块数据
condition_plate = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_plate]
condition_plate2 = df['stockcode'].isin(code_list)
df_plate = df[condition_plate2]
df_plate = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_plate)
df_plate.rename(columns={'portfolio_value': 'portfolio_plate' + plates.replace(',', '')},
inplace=True)
df_plate = df_plate[['date', 'portfolio_plate' + plates.replace(',', '')]]
merged_df = pd.merge(merged_df, df_plate, on='date', how='left')
else:
if len(code_list) > 0:
condition_plate = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_plate]
condition_plate2 = df['stockcode'].isin(code_list)
df_plate = df[condition_plate2]
df_plate = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_plate)
df_plate.rename(columns={'portfolio_value': 'portfolio_plate' + plates.replace(',', '')},
inplace=True)
df_plate = df_plate[['date', 'portfolio_plate' + plates.replace(',', '')]]
merged_df = pd.merge(merged_df, df_plate, on='date', how='left')
if len(value) > 0: # 根据value值筛选数据
int_value = float(grade_to_value[value])
code_df_value = code_df.copy()
code_df_value = code_df_value[code_df_value['SValue_grade'] >= int_value]
code_list = code_df_value.get('stockcode')
if len(code_list) > 0:
condition_value = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_value]
df_value = df.copy()
condition_value2 = df_value['stockcode'].isin(code_list)
df_value = df_value[condition_value2]
df_value = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_value)
df_value.rename(columns={'portfolio_value': 'portfolio_svalue' + value},
inplace=True)
df_value = df_value[['date', 'portfolio_svalue' + value]]
merged_df = pd.merge(merged_df, df_value, on='date', how='left')
if len(growth) > 0: # 根据growth值筛选数据
int_growth = float(grade_to_value[growth])
code_df_growth = code_df.copy()
code_df_growth = code_df_growth[code_df_growth['GROWTH_grade'] >= int_growth]
code_list = code_df_growth.get('stockcode')
if len(code_list) > 0:
condition_growth = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_growth]
df_growth = df.copy()
condition_growth2 = df_growth['stockcode'].isin(code_list)
df_growth = df_growth[condition_growth2]
df_growth = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_growth)
df_growth.rename(columns={'portfolio_value': 'portfolio_growth' + growth},
inplace=True)
df_growth = df_growth[['date', 'portfolio_growth' + growth]]
merged_df = pd.merge(merged_df, df_growth, on='date', how='left')
if len(profitability) > 0: # 根据PROFITABILITY_grade值筛选数据 MOMENTUM_grade
int_profitability = float(grade_to_value[profitability])
code_df_profitability = code_df.copy()
code_df_profitability = code_df_profitability[code_df_profitability['PROFITABILITY_grade'] >= int_profitability]
code_list = code_df_profitability.get('stockcode')
if len(code_list) > 0:
condition_profitability = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_profitability]
df_profitability = df.copy()
condition_profitability2 = df_profitability['stockcode'].isin(code_list)
df_profitability = df_profitability[condition_profitability2]
df_profitability = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_profitability)
df_profitability.rename(
columns={'portfolio_value': 'portfolio_profitability' + profitability},
inplace=True)
df_profitability = df_profitability[
['date', 'portfolio_profitability' + profitability]]
merged_df = pd.merge(merged_df, df_profitability, on='date', how='left')
if len(momentum) > 0: # 根据MOMENTUM_grade值筛选数据
int_momentum = float(grade_to_value[momentum])
code_df_momentum = code_df.copy()
code_df_momentum = code_df_momentum[code_df_momentum['MOMENTUM_grade'] >= int_momentum]
code_list = code_df_momentum.get('stockcode')
if len(code_list) > 0:
condition_momentum = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_momentum]
df_momentum = df.copy()
condition_momentum2 = df_momentum['stockcode'].isin(code_list)
df_momentum = df_momentum[condition_momentum2]
df_momentum = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_momentum)
df_momentum.rename(
columns={'portfolio_value': 'portfolio_momentum' + momentum},
inplace=True)
df_momentum = df_momentum[['date', 'portfolio_momentum' + momentum]]
merged_df = pd.merge(merged_df, df_momentum, on='date', how='left')
if len(eps) > 0: # 根据EPSREVISION_grade值筛选数据
int_eps_revision = float(grade_to_value[eps])
code_df_eps = code_df.copy()
code_df_eps = code_df_eps[code_df_eps['EPSREVISION_grade'] >= int_eps_revision]
code_list = code_df_eps.get('stockcode')
if len(code_list) > 0:
condition_eps_revision = df_input_flag['stockcode'].isin(code_list)
df_input_flag = df_input_flag[condition_eps_revision]
df_eps = df.copy()
condition_eps_revision2 = df_eps['stockcode'].isin(code_list)
df_eps = df_eps[condition_eps_revision2]
df_eps = common.Calc_Portfolio(initial_investment=initial_investment,
df=df_eps)
df_eps.rename(
columns={'portfolio_value': 'portfolio_eps' + eps},
inplace=True)
df_eps = df_eps[['date', 'portfolio_eps' + eps]]
merged_df = pd.merge(merged_df, df_eps, on='date', how='left')
if len(top_one)>0:
merged_df = pd.merge(merged_df,top_one,on='date',how='left')
# 创建一个集合来存储不重复的列名
unique_cols = set()
# 遍历所有列，检查是否已存在数值上完全相同的列
for col in merged_df.columns:
if not any(merged_df[col].equals(merged_df[other_col]) for other_col in unique_cols):
unique_cols.add(col)
# 对列名进行排序
unique_cols = sorted(unique_cols)
# 创建一个新的数据框，只包含不重复的列
merged_df = merged_df[list(unique_cols)]
merged_df.set_index('date', inplace=True)
# 绘制指定日期区间内的组合价值及随机组合的价值对应的时间序列图
common.Draw_plt(merged_df, "股票组合价值时间序列图")
def get_top_one_tickets_data(start_date,end_date):
"""
查询行业市值第一的历史股票价值
:param start_date:
:param end_date:
:return:
"""
# 查询数据库股票的数据：
data_sql = (
f"select T1.TicketDate as date,RTRIM(T1.TicketCode) as stockcode ,T1.TicketADJClose as price "
f"from MG_TopOneTicketsHistoryData T1 inner join MG_TopOneTickets T2 on T1.TicketCode=T2.TicketCode "
f"where TicketDate>='{start_date}' and TicketDate<='{end_date}' order by TicketDate asc")
data = db.ExecuteTableQueryHead(data_sql)
top_one_df = pd.DataFrame(data)
top_one_df['date'] = pd.to_datetime(top_one_df['date'])
top_one_df = top_one_df.sort_values(by='date')
return top_one_df
def select_top_one_stockcode_df(start_date, end_date):
"""
查询股票代码及行业信息
:param start_date: 开始日期
:param end_date: 结束日期
:return: 返回top_one_stockcode_df
"""
stockcode_sql = (f"select RTRIM(A.TicketCode) stockcode,B.Description,B.IndustryName from "
f"(select distinct TicketCode from dbo.MG_TopOneTicketsHistoryData where "
f"TicketDate>='{start_date}' and TicketDate<='{end_date}' ) as A "
f"inner join MG_TopOneTickets B on A.TicketCode=B.TicketCode order by A.TicketCode")
data_stockcode = db.ExecuteTableQueryHead(stockcode_sql)
top_one_stockcode_df = pd.DataFrame(data_stockcode)
return top_one_stockcode_df
def calc_top_one_portfilo_value(top_one_tickets_value_df):
"""
计算行业市值第一对应的组合价值
:param top_one_tickets_df:
:return:
"""
#top_one_portfilo_value = common.Calc_Portfolio(initial_investment=initial_investment,df=top_one_tickets_value_df)
top_one_portfilo_value = select_portfolio_value(top_one_tickets_value_df)
return top_one_portfilo_value
def get_mtkl_data(start_date,end_date):
mtklData = common.get_mengtekaluo_data(start_date, end_date)
stockPrice = pd.DataFrame(mtklData)
stockPrice['date'] = pd.to_datetime(stockPrice['date'])
# 按照日期排序（如果数据已经是按日期排序的，可以跳过此步骤）
stockPrice = stockPrice.sort_values(by='date')
return stockPrice
def calc_stock_price(stockPrice):
StockReturns = stockPrice.set_index('date').pct_change().dropna() + 1
stock_return = StockReturns.copy()
# 替换inf为NaN
stock_return.replace([np.inf, -np.inf], np.nan, inplace=True)
# 删除包含NaN的列
stock_return.dropna(axis=1, how='all', inplace=True)
return stock_return
def calc_xie_fang_cha(stock_return):
"""
计算斜方差
:param stock_return:
:return: 返回斜方差
"""
# 计算协方差矩阵
cov_mat = stock_return.dropna().cov()
cov_mat=cov_mat.dropna()
return cov_mat
def calc_year_xie_fang_cha(cov_mat):
"""
计算年化斜方差
:param cov_mat: 斜方差
:return: 返回年化斜方差
"""
# 年化协方差矩阵
cov_mat_annual = cov_mat * 252
return cov_mat_annual
def calc_mengtekaluo_data(stock_return,cov_mat_annual,ticker_list,number=10000):
'''
计算蒙特卡洛模型图
:param stock_return:
:param cov_mat_annual:
:param ticker_list:
:return:
'''
# 设置模拟的次数
number = number
# 设置空的numpy数组，用于存储每次模拟得到的权重、收益率和标准差
random_p = np.empty((number, len(ticker_list)+2))
# 设置随机数种子，这里是为了结果可重复
np.random.seed(len(ticker_list)+2)
# 循环模拟10000次随机的投资组合
for i in range(number):
# 生成股票列表数量对应的个随机数，并归一化，得到一组随机的权重数据
random5 = np.random.random(len(ticker_list))
random_weight = random5 / np.sum(random5)
# 计算年平均收益率
mean_return = stock_return.mul(random_weight, axis=1).sum(axis=1).mean()
annual_return = (1 + mean_return) ** 252 - 1
# 计算年化标准差，也成为波动率
random_volatility = np.sqrt(np.dot(random_weight.T, np.dot(cov_mat_annual, random_weight)))
# 将上面生成的权重，和计算得到的收益率、标准差存入数组random_p中
random_p[i][:len(ticker_list)] = random_weight
random_p[i][len(ticker_list)] = annual_return
random_p[i][len(ticker_list)+1] = random_volatility
# 将Numpy数组转化为DataF数据框
RandomPortfolios = pd.DataFrame(random_p)
# 设置数据框RandomPortfolios每一列的名称
RandomPortfolios.columns = [ticker + '_weight' for ticker in ticker_list] + ['Returns', 'Volatility']
return RandomPortfolios
def draw_mtkl_point_view(randomPortfolios):
# 绘制散点图
randomPortfolios.plot('Volatility', 'Returns', kind='scatter', alpha=0.3)
plt.show()
def draw_mtkl_point_min_index_view(randomPortfolios):
# 找到标准差最小数据的索引值
min_index = randomPortfolios.Volatility.idxmin()
# 在收益-风险散点图中突出风险最小的点
randomPortfolios.plot('Volatility', 'Returns', kind='scatter', alpha=0.3)
x = randomPortfolios.loc[min_index, 'Volatility']
y = randomPortfolios.loc[min_index, 'Returns']
plt.scatter(x, y, color='red')
# 将该点坐标显示在图中并保留四位小数
plt.text(np.round(x, 4), np.round(y, 4), (np.round(x, 4), np.round(y, 4)), ha='left', va='bottom', fontsize=10)
plt.show()
def draw_shap_point_view(randomPortfolios):
#####夏普比率相关#######
# 设置无风险回报率为0
risk_free = 0
# 计算每项资产的夏普比率
randomPortfolios['Sharpe'] = (randomPortfolios.Returns - risk_free) / randomPortfolios.Volatility
# 绘制收益-标准差的散点图，并用颜色描绘夏普比率
plt.scatter(randomPortfolios.Volatility, randomPortfolios.Returns, c=randomPortfolios.Sharpe)
plt.colorbar(label='Sharpe Ratio')
plt.show()
def draw_shap_point_max_index_view(randomPortfolios):
# 找到夏普比率最大数据对应的索引值
max_index = randomPortfolios['Sharpe'].idxmax()
# 在收益-风险散点图中突出夏普比率最大的点
randomPortfolios.plot('Volatility', 'Returns', kind='scatter', alpha=0.3)
x = randomPortfolios.loc[max_index, 'Volatility']
y = randomPortfolios.loc[max_index, 'Returns']
plt.scatter(x, y, color='red')
# 将该点坐标显示在图中并保留四位小数
plt.text(np.round(x, 4), np.round(y, 4), (np.round(x, 4), np.round(y, 4)), ha='left', va='bottom', fontsize=10)
plt.show()
主程序的相关代码：
import datetime
from PyQt5.QtCore import Qt, QThread, pyqtSignal, QObject, QStringListModel, QDate
from MainForm import Ui_MainWindow
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableWidgetItem, QAbstractItemView, QWidget, QMessageBox, \
QFileDialog
from PyQt5.QtGui import QFont
import pandas as pd
import CalcInint as calc
import SpyData as spydata
import ReadCvsData as readCvsTicketsData
class WorkerSignals(QObject):
# 定义一个信号来传递 DataFrame
df_ready = pyqtSignal(pd.DataFrame)
class SelectDataWorker(QThread):
def __init__(self, start_date, end_date, *args, **kwargs):
super().__init__(*args, **kwargs)
self.start_date = start_date
self.end_date = end_date
self.signal = WorkerSignals()
def run(self):
start_date = self.start_date
end_date = self.end_date
# 在这里执行后台操作
df = calc.select_stockcode_df(start_date, end_date)
df = calc.select_stockcode_df_ex(df)
self.signal.df_ready.emit(df) # 发出完成信号
class MainWindow(QMainWindow):
def __init__(self):
super(MainWindow, self).__init__()
self.ui = Ui_MainWindow() # 创建一个 Ui_MainWindow 实例
self.ui.setupUi(self) # 初始化界面，将控件设置到 MyMainWindow 上
# 给相应的控件附加初始值
items = ['', 'A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'D-', 'E']
self.ui.cbox_value.addItems(items)
self.ui.cbox_growth.addItems(items)
self.ui.cbox_profitability.addItems(items)
self.ui.cbox_momentum.addItems(items)
self.ui.cbox_esp.addItems(items)
current_date=QDate.currentDate()
self.ui.end_date.setDate(current_date)
# 设置相关控件的属性
self.ui.LV_Plate.setSelectionMode(QAbstractItemView.MultiSelection) # 设置list_view可多选
self.ui.pushButton.setEnabled(False)
self.ui.Search_Data_2.setEnabled(False)
self.ui.btn_export_data.setEnabled(False)
self.ui.export_megred_data.setEnabled(False)
self.ui.btn_draw_info.setEnabled(False)
self.ui.btn_export_top_one_and_portfolio.setEnabled(False)
self.ui.btn_xie_fang_cha_ju_zhen.setEnabled(False)
self.ui.btn_year_xie_fang_cha_ju_zhen.setEnabled(False)
self.ui.btn_mtkl_points_view.setEnabled(False)
self.ui.btn_mtkl_min_point.setEnabled(False)
self.ui.btn_shap_point_view.setEnabled(False)
self.ui.btn_shap_point_max.setEnabled(False)
self.ui.btn_shap_point_view.setEnabled(False)
self.ui.btn_shap_point_max.setEnabled(False)
self.ui.btn_get_mtkl_data.setEnabled(False)
self.ui.btn_top_one_and_select_option.setEnabled(False)
self.ui.load_top_one_tickets_data.setEnabled(False)
# 给相应控件增加事件
self.ui.Search_Data.clicked.connect(self.btn_search_clicked)
self.ui.Search_Data_2.clicked.connect(self.btn_a_clicks)
self.ui.pushButton.clicked.connect(self.btn_tiaojian_clicks)
self.ui.get_new_indexValue.clicked.connect(self.btn_get_new_index_value)
self.ui.get_new_StockInfo.clicked.connect(self.btn_get_new_stocket_data)
self.ui.btn_export_data.clicked.connect(self.btn_export_data)
self.ui.export_megred_data.clicked.connect(self.btn_export_megred_data)
self.ui.btn_ticket_history_data.clicked.connect(self.import_tickets_his_data)
self.ui.btn_top_one_ticket_data.clicked.connect(self.import_top_one_ticket_his_data)
self.ui.load_top_one_tickets_data.clicked.connect(self.load_top_one_tickets_data)
self.ui.btn_draw_info.clicked.connect(self.btn_draw_top_one)
self.ui.btn_export_top_one_and_portfolio.clicked.connect(self.btn_export_top_one_portfolio_value)
self.ui.btn_get_mtkl_data.clicked.connect(self.load_mtkl_data)
self.ui.btn_xie_fang_cha_ju_zhen.clicked.connect(self.load_xie_fang_cha)
self.ui.btn_year_xie_fang_cha_ju_zhen.clicked.connect(self.load_year_xie_fang_cha)
self.ui.btn_get_new_top_one_ticket_data.clicked.connect(self.btn_get_new_top_one_stocket_data)
self.ui.btn_mtkl_points_view.clicked.connect(self.show_mtkl_point_view)
self.ui.btn_mtkl_min_point.clicked.connect(self.show_mtkl_point_min_index_view)
self.ui.btn_shap_point_view.clicked.connect(self.show_shap_point_view)
self.ui.btn_shap_point_max.clicked.connect(self.show_shap_point_max_index_view)
self.ui.btn_top_one_and_select_option.clicked.connect(self.draw_top_one_and_select_option)
# 初始化工作线程
start_date = self.ui.start_date.date().toString('yyyy-MM-dd')
end_date = self.ui.end_date.date().toString('yyyy-MM-dd')
self.worker = SelectDataWorker(start_date, end_date)
self.worker.signal.df_ready.connect(self.on_search_df_ready)
def show_message_box(self,text='数据正在处理中，请稍后...'):
"""
开启对话框
:param text: 对话框内容
:return:
"""
# 开启对话框
self.msg_box_hint = QMessageBox()
self.msg_box_hint.setIcon(QMessageBox.Information)
self.msg_box_hint.setWindowTitle('操作提示')
# 标题自己设置
self.msg_box_hint.setText(text)
self.msg_box_hint.show()
QApplication.processEvents()
def close_message_box(self):
"""
关闭对话框
:return:
"""
# 关闭对话框
self.msg_box_hint.close()
def btn_search_clicked(self):
"""
数据查询
:return:
"""
# start_date = self.ui.start_date.date().toString('yyyy-MM-dd')
# end_date = self.ui.end_date.date().toString('yyyy-MM-dd')
if not self.worker.isRunning():
self.worker.start()
self.ui.Search_Data.setEnabled(False) # 禁用开始按钮，防止重复点击
self.ui.pushButton.setEnabled(False)
self.ui.Search_Data_2.setEnabled(False)
self.ui.btn_export_data.setEnabled(False)
self.ui.load_top_one_tickets_data.setEnabled(False)
def btn_a_clicks(self):
"""
绘制组合价值曲线图
:return:
"""
df_pf_inx = calc.merge_index_value_and_portfolio_value(self.ui.index_value_df, self.ui.portfolio_value_daily)
calc.draw_portfolio_value(df_pf_inx)
def btn_tiaojian_clicks(self):
"""
条件组合生成曲线图
:return:
"""
self.show_message_box()
self.ui.pushButton.setEnabled(False)
indexs = self.ui.LV_Plate.selectedIndexes()
select_items = []
for index in indexs:
item = self.ui.lv_model.itemData(index)
select_items.append(item[0])
plates = None
if len(select_items) > 0:
plates = ','.join(select_items)
df_pf_inx = calc.merge_index_value_and_portfolio_value(self.ui.index_value_df, self.ui.portfolio_value_daily)
self.ui.old_merged_df = calc.selct_data_by_input(df=self.ui.df, stockcode_df=self.ui.stockcode_df,
merged_df=df_pf_inx,
plates=plates,
value=self.ui.cbox_value.currentText(),
growth=self.ui.cbox_growth.currentText(),
profitability=self.ui.cbox_profitability.currentText(),
momentum=self.ui.cbox_momentum.currentText(),
eps=self.ui.cbox_esp.currentText())
self.ui.pushButton.setEnabled(True)
self.close_message_box()
self.ui.export_megred_data.setEnabled(True)
def btn_get_new_index_value(self):
self.ui.get_new_indexValue.setEnabled(False)
self.show_message_box(text="正在获取最新指数数据,请稍后.....")
try:
spydata.get_new_index_value(input_menu=1)
except Exception as e:
self.show_message_box(text=e)
self.close_message_box()
self.ui.get_new_indexValue.setEnabled(True)
def btn_get_new_stocket_data(self):
self.ui.get_new_StockInfo.setEnabled(False)
self.show_message_box(text='正在获取最新股票数据,请稍后.....')
try:
spydata.get_stock_data(input_menu=2)
except Exception as e:
self.show_message_box(text=e)
self.close_message_box()
self.ui.get_new_StockInfo.setEnabled(True)
def btn_get_new_top_one_stocket_data(self):
self.ui.btn_get_new_top_one_ticket_data.setEnabled(False)
self.show_message_box(text='正在获取行业第一股票的最新数据,请稍后.....')
try:
spydata.get_new_top_one_stock_data(input_menu=3)
except Exception as e:
self.show_message_box(text=e)
self.close_message_box()
self.ui.btn_get_new_top_one_ticket_data.setEnabled(True)
def export_data(self, data):
"""
导出数据为excel
:param data:
:return:
"""
# 创建一个QFileDialog来选择保存路径和文件名
filename, _ = QFileDialog.getSaveFileName(self, '保存文件', '', 'Excel Files (*.xlsx)')
if filename:
self.show_message_box(text='正在导出数据,请稍后....')
# 假设self.data是一个包含数据的列表
data = data
df = pd.DataFrame(data)
df.to_excel(filename, index=False)
self.close_message_box()
def import_top_one_ticket_his_data(self):
"""
导入行业第一股票的历史数据
:return:
"""
self.show_message_box()
readCvsTicketsData.add_TopOnetickets_data()
self.close_message_box()
def import_tickets_his_data(self):
"""
导入股当前股票组合的历史数据
:return:
"""
self.show_message_box()
readCvsTicketsData.add_tickets_data()
self.close_message_box()
# 加载行业市值第一的股票数据
def load_top_one_tickets_data(self):
"""
加载行业第一股票数据
:return:
"""
start_date = self.ui.start_date.date().toString('yyyy-MM-dd')
end_date = self.ui.end_date.date().toString('yyyy-MM-dd')
self.show_message_box()
# 清空原表
self.ui.tw_top_one_data.clearContents()
self.ui.tw_top_one_data.setRowCount(0)
top_one_df = calc.select_top_one_stockcode_df(start_date=start_date,end_date=end_date)
#装载数据
if len(top_one_df) > 0:
self.ui.tw_top_one_data.setRowCount(top_one_df.shape[0])
self.ui.tw_top_one_data.setColumnCount(top_one_df.shape[1])
# 设置表头
self.ui.tw_top_one_data.setHorizontalHeaderLabels(top_one_df.columns.tolist())
# 填充数据
try:
for row in range(top_one_df.shape[0]):
for column in range(top_one_df.shape[1]):
item = QTableWidgetItem(str(top_one_df.iat[row, column]))
self.ui.tw_top_one_data.setItem(row, column, item)
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
top_one_tickets_value_df = calc.get_top_one_tickets_data(start_date=start_date, end_date=end_date)
self.ui.top_one_tickets_value_df = top_one_tickets_value_df
self.close_message_box()
self.ui.btn_draw_info.setEnabled(True)
def btn_draw_top_one(self):
"""
绘制行业第一股票组合的曲线图
:return:
"""
self.show_message_box()
portfolio = calc.calc_top_one_portfilo_value(self.ui.top_one_tickets_value_df)
portfolio = portfolio[['date', 'portfolio_value']] # 组合时间序列
portfolio.rename(columns={'portfolio_value': 'TopOnePV'},
inplace=True)
portfolio.set_index('date', inplace=True) # 设置date为索引
self.ui.top_one_portfolio_value_df = portfolio
try:
df_pf_inx = calc.merge_index_value_and_portfolio_value(self.ui.index_value_df,
self.ui.portfolio_value_daily)
merged_df = pd.merge(portfolio,df_pf_inx, on='date', how='left')
calc.draw_portfolio_value(merged_df)
self.ui.top_one_portfolio_value=merged_df
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
self.close_message_box()
self.ui.btn_export_top_one_and_portfolio.setEnabled(True)
if len(self.ui.old_merged_df)>0:
self.ui.btn_top_one_and_select_option.setEnabled(True)
def btn_export_top_one_portfolio_value(self):
"""
导出行业第一市值股票组合与当前股票组合的数据
:return:
"""
data = self.ui.top_one_portfolio_value
self.export_data(data)
def btn_export_data(self):
"""
导出股票组合价值数据
:return:
"""
data = self.ui.stockcode_df
self.export_data(data)
def btn_export_megred_data(self):
"""
导出合并后的数据
:return:
"""
data = self.ui.old_merged_df
self.export_data(data)
def on_search_df_ready(self, df):
"""
查询按钮加载完成展示数据
:param df:
:return:
"""
self.show_message_box()
# 清空原表
self.ui.Dt_View.clearContents()
self.ui.Dt_View.setRowCount(0)
start_date = self.ui.start_date.date().toString('yyyy-MM-dd')
end_date = self.ui.end_date.date().toString('yyyy-MM-dd')
if len(df) > 0:
self.ui.Dt_View.setRowCount(df.shape[0])
self.ui.Dt_View.setColumnCount(df.shape[1])
# 设置表头
self.ui.Dt_View.setHorizontalHeaderLabels(df.columns.tolist())
# 填充数据
try:
for row in range(df.shape[0]):
for column in range(df.shape[1]):
item = QTableWidgetItem(str(df.iat[row, column]))
self.ui.Dt_View.setItem(row, column, item)
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
self.ui.stockcode_df = df
# 给plate_lv绑定数据
plate_df = calc.select_plate(start_date, end_date)
self.ui.lv_model.setStringList(plate_df.values.flatten().tolist())
self.ui.LV_Plate.setModel(self.ui.lv_model)
self.ui.index_value_df = calc.select_index_value(start_date, end_date)
self.ui.df = calc.select_df(start_date, end_date)
self.ui.portfolio_value_daily = calc.select_portfolio_value(self.ui.df)
self.ui.pushButton.setEnabled(True)
self.ui.Search_Data_2.setEnabled(True)
self.ui.Search_Data.setEnabled(True) # 重新启用开始按钮
self.ui.btn_export_data.setEnabled(True)
self.ui.btn_get_mtkl_data.setEnabled(True)
self.ui.load_top_one_tickets_data.setEnabled(True)
self.close_message_box()
def load_mtkl_data(self):
"""
加载蒙特卡洛相关数据
:return:
"""
self.show_message_box()
# 清空原表
self.ui.tw_mtkl_data.clearContents()
self.ui.tw_mtkl_data.setRowCount(0)
start_date = self.ui.start_date.date().toString('yyyy-MM-dd')
end_date = self.ui.end_date.date().toString('yyyy-MM-dd')
stockprice = calc.get_mtkl_data(start_date,end_date)
stock_return = calc.calc_stock_price(stockprice)
if len(stock_return) > 0:
self.ui.tw_mtkl_data.setRowCount(stock_return.shape[0])
self.ui.tw_mtkl_data.setColumnCount(stock_return.shape[1])
# 设置表头
self.ui.tw_mtkl_data.setHorizontalHeaderLabels(stock_return.columns.tolist())
# 填充数据
try:
for row in range(stock_return.shape[0]):
for column in range(stock_return.shape[1]):
item = QTableWidgetItem(str(stock_return.iat[row, column]))
self.ui.tw_mtkl_data.setItem(row, column, item)
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
self.ui.stock_return = stock_return
self.close_message_box()
self.ui.btn_xie_fang_cha_ju_zhen.setEnabled(True)
def load_xie_fang_cha(self):
"""
计算斜方差
:return:
"""
self.show_message_box()
xie_fang_cah = calc.calc_xie_fang_cha(self.ui.stock_return)
if len(xie_fang_cah) > 0:
self.ui.tw_xie_fang_cha_ju_zhen.setRowCount(xie_fang_cah.shape[0])
self.ui.tw_xie_fang_cha_ju_zhen.setColumnCount(xie_fang_cah.shape[1])
# 设置表头
self.ui.tw_xie_fang_cha_ju_zhen.setHorizontalHeaderLabels(xie_fang_cah.columns.tolist())
# 填充数据
try:
for row in range(xie_fang_cah.shape[0]):
for column in range(xie_fang_cah.shape[1]):
item = QTableWidgetItem(str(xie_fang_cah.iat[row, column]))
self.ui.tw_xie_fang_cha_ju_zhen.setItem(row, column, item)
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
self.ui.xie_fang_cha = xie_fang_cah
self.close_message_box()
self.ui.btn_year_xie_fang_cha_ju_zhen.setEnabled(True)
def load_year_xie_fang_cha(self):
"""
计算年化斜方差
:return:
"""
self.show_message_box()
year_xie_fang_cha = calc.calc_year_xie_fang_cha(self.ui.xie_fang_cha)
if len(year_xie_fang_cha) > 0:
self.ui.tw_year_xie_fang_cha_ju_zhen.setRowCount(year_xie_fang_cha.shape[0])
self.ui.tw_year_xie_fang_cha_ju_zhen.setColumnCount(year_xie_fang_cha.shape[1])
# 设置表头
self.ui.tw_year_xie_fang_cha_ju_zhen.setHorizontalHeaderLabels(year_xie_fang_cha.columns.tolist())
# 填充数据
try:
for row in range(year_xie_fang_cha.shape[0]):
for column in range(year_xie_fang_cha.shape[1]):
item = QTableWidgetItem(str(year_xie_fang_cha.iat[row, column]))
self.ui.tw_year_xie_fang_cha_ju_zhen.setItem(row, column, item)
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
self.ui.year_xie_fang_cha = year_xie_fang_cha
ticker_list = self.ui.stockcode_df['stockcode']
randomportfolios = calc.calc_mengtekaluo_data(self.ui.stock_return, self.ui.year_xie_fang_cha, ticker_list)
self.ui.randomp_ortfolios = randomportfolios
self.close_message_box()
self.ui.btn_mtkl_points_view.setEnabled(True)
self.ui.btn_mtkl_min_point.setEnabled(True)
self.ui.btn_shap_point_view.setEnabled(True)
self.ui.btn_shap_point_max.setEnabled(True)
def show_mtkl_point_view(self):
calc.draw_mtkl_point_view(self.ui.randomp_ortfolios)
def show_mtkl_point_min_index_view(self):
calc.draw_mtkl_point_min_index_view(self.ui.randomp_ortfolios)
def show_shap_point_view(self):
calc.draw_shap_point_view(self.ui.randomp_ortfolios)
def show_shap_point_max_index_view(self):
calc.draw_shap_point_max_index_view(self.ui.randomp_ortfolios)
def draw_top_one_and_select_option(self):
self.show_message_box()
try:
top_one_portfolio = calc.calc_top_one_portfilo_value(self.ui.top_one_tickets_value_df)
top_one_portfolio = top_one_portfolio[['date', 'portfolio_value']] # 组合时间序列
top_one_portfolio.rename(columns={'portfolio_value': 'TopOnePV'},
inplace=True)
top_one_portfolio.set_index('date', inplace=True) # 设置date为索引
indexs = self.ui.LV_Plate.selectedIndexes()
select_items = []
for index in indexs:
item = self.ui.lv_model.itemData(index)
select_items.append(item[0])
plates = None
if len(select_items) > 0:
plates = ','.join(select_items)
df_pf_inx = calc.merge_index_value_and_portfolio_value(self.ui.index_value_df,
self.ui.portfolio_value_daily)
calc.selct_data_by_input_and_top_one(df=self.ui.df, stockcode_df=self.ui.stockcode_df,
merged_df=df_pf_inx,
plates=plates,
value=self.ui.cbox_value.currentText(),
growth=self.ui.cbox_growth.currentText(),
profitability=self.ui.cbox_profitability.currentText(),
momentum=self.ui.cbox_momentum.currentText(),
eps=self.ui.cbox_esp.currentText(),top_one=top_one_portfolio)
except Exception as e:
self.show_message_box(text=f"Error filling data:{e}")
return
self.close_message_box()
# 在这里添加其他方法
# 应用程序入口
if __name__ == '__main__':
import sys
app = QApplication(sys.argv)
# 设置应用程序的默认字体大小
font = QFont()
font.setPointSize(10) # 设置字体大小为12
app.setFont(font)
mainWin = MainWindow() # 创建 MyMainWindow 实例
mainWin.setWindowTitle("股票相关计算程序")
# 禁用最大化按钮
mainWin.setWindowFlags(mainWin.windowFlags() & ~Qt.WindowMaximizeButtonHint)
mainWin.show() # 显示窗口
sys.exit(app.exec_()) # 进入主事件循环