-- coding: utf-8 -- import os import xlwings as xw import sys import pymysql import…
- User
-- coding: utf-8 --
import os import xlwings as xw import sys import pymysql import xlwings as xw import pandas as pd import numpy as np import datetime from sqlalchemy import create_engine import fund_keystock
#定义链接数据库函数 def connection_base(dbname: str)->pymysql.connect: _servername = '172.20.60.4' _user = 'zxdb_read' _pwd = 'Zxdb_read' _port = 3306 _conn = pymysql.connect(host=_servername, user=_user, password=_pwd, database=dbname, port=_port, charset='utf8') return _conn
def stock_list(begindate,enddate,reportdate):
_db = 'jydb' _conn = connection_base(_db) #取数据 try: #查除科创外A股市值 _sqlstr = "select InnerCode,TradingDay,TotalMV,NegotiableMV FROM lc_dindicesforvaluation where TradingDay ='%s' AND\ InnerCode in (select InnerCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 ) AND\ (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or ListedSector=6) and ListedState=1)" %(enddate, begindate) Latest_MV = pd.read_sql_query(_sqlstr, _conn) #查科创A股市值 _sqlstr= "select InnerCode,TradingDay,TotalMV,NegotiableMV FROM LC_STIBDIndiForValue where TradingDay ='%s' AND\ InnerCode in (select InnerCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 ) AND\ (SecuMarket=83 OR secumarket=90) AND ListedSector=7 and ListedState=1)"%(enddate, begindate) Latest_MV2= pd.read_sql_query(_sqlstr, _conn) #查除科创外A股净资产 _sqlstr = "select distinct CompanyCode,SEWithoutMI,IntangibleAssets,GoodWill,InfoPublDate FROM LC_BalanceSheetAll where Enddate ='%s' and\ IfMerged = 1 AND CompanyCode in (select CompanyCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 )\ AND (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or ListedSector=6) and ListedState=1)"%(reportdate,begindate) Latest_BV = pd.read_sql_query(_sqlstr, _conn) #查科创A股净资产 _sqlstr = "select distinct CompanyCode,SEWithoutMI,IntangibleAssets,GoodWill,InfoPublDate FROM LC_STIBBalanceSheet where Enddate ='%s' and\ IfMerged = 1 AND CompanyCode in (select CompanyCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 )\ AND (SecuMarket=83 OR secumarket=90) AND ListedSector=7 and ListedState=1)"%(reportdate,begindate) Latest_BV2 = pd.read_sql_query(_sqlstr, _conn) #查除科创外A股交易数据 _sqlstr ="select InnerCode,ChangePCTRM,TurnoverRateRM,TurnoverValuePDayRY,YearVolatilityByDay from QT_StockPerformance where TradingDay='%s' AND\ InnerCode in (select InnerCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 ) AND\ (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or ListedSector=6) and ListedState=1)"%(enddate,begindate) Latest_Factor=pd.read_sql_query(_sqlstr, _conn) #查科创A股交易数据 _sqlstr ="select InnerCode,ChangePCTRM,TurnoverRateRM,TurnoverValuePDayRY,YVolatility from LC_STIBPerformance where TradingDay='%s' AND\ InnerCode in (select InnerCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 ) AND\ (SecuMarket=83 OR secumarket=90) AND ListedSector=7 and ListedState=1)"%(enddate,begindate) Latest_Factor2=pd.read_sql_query(_sqlstr, _conn) #统一波动率对应列名 Latest_Factor2.rename(columns={'YVolatility':'YearVolatilityByDay'},inplace=True) #查聚源公司代码、内部代码、股票代码的字典 _sqlstr = "select InnerCode,CompanyCode,SecuCode from SecuMain where ListedDate < '%s' and\ (SecuCategory = 1 or SecuCategory = 41 ) AND (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or\ ListedSector=6 or ListedSector=7)and ListedState=1 ORDER BY Secucode"%(begindate) Dict=pd.read_sql_query(_sqlstr, _conn) finally: _conn.close()#拼接市值表 Latest_MV3=pd.concat([Latest_MV,Latest_MV2],ignore_index= True) #拼接净资产表 Latest_BV3=pd.concat([Latest_BV,Latest_BV2],ignore_index= True) #净资产表数据库中Null替换为零 Latest_BV3=Latest_BV3.replace(np.nan,0) #只保留将净资产表中财报更新的结果,去除前值 Latest_BV3=Latest_BV3.sort_values('InfoPublDate',ascending=False).drop_duplicates(subset=['CompanyCode'],keep='first') #拼接交易表 Latest_Factor3=pd.concat([Latest_Factor,Latest_Factor2],ignore_index= True) #构造调整后净资产 Latest_BV3['AdjustedBV']=Latest_BV3['SEWithoutMI']-Latest_BV3['IntangibleAssets']-Latest_BV3['GoodWill'] #将三张表利用关键字与字典相连 Latest_MV3=Latest_MV3.merge(Dict,on='InnerCode',how='left') Latest_BV3=Latest_BV3.merge(Dict,on='CompanyCode',how='left') Latest_Factor3=Latest_Factor3.merge(Dict,on='InnerCode',how='left') #将三张表利用统一的关键字SecuCode相连 Allstock=Latest_MV3.merge(Latest_BV3,on='SecuCode',how='left') Allstock=Allstock.merge(Latest_Factor3,on='SecuCode',how='left') #删除冗余列 Allstock.drop(Allstock.columns[[0,4,6,7,8,9,10,12,13,18]], axis=1, inplace=True) #删除调整净资产为负的行 Allstock.drop(Allstock[Allstock.AdjustedBV<0].index,inplace=True) #按照流通市值排序 Allstock=Allstock.sort_values('NegotiableMV') #剔除日均换手率排名后5%股票 q=Allstock.quantile(q=0.05, axis=0, numeric_only=True, interpolation='linear') Allstock.drop(Allstock[Allstock.TurnoverValuePDayRY<q[5]].index,inplace=True) #计算全市场流通市值 x=Allstock['NegotiableMV'].sum() #计算累计加总占比全市场4%的股票排名 summv=0 i=0 while summv < x*0.04: summv=summv+Allstock.iat[i,1] i = i+1 #截断排名以下的股票行 Allstock.drop(Allstock.tail(len(Allstock)-i).index,inplace=True) #构造调整后PB指标 Allstock['Prc2bk']=Allstock['TotalMV']/Allstock['AdjustedBV']
#方法一 剔除期间涨幅过大过小的股票 q1=Allstock.quantile(q=0.2, axis=0, numeric_only=True, interpolation='linear') q2=Allstock.quantile(q=0.8, axis=0, numeric_only=True, interpolation='linear') Allstock.drop(Allstock[Allstock.ChangePCTRM<q1[3]].index,inplace=True) Allstock.drop(Allstock[Allstock.ChangePCTRM>q2[3]].index,inplace=True)
Allstock.drop(Allstock.columns[[1,2,4,5,7]], axis=1, inplace=True)#计算因子值 Allstock['RankTO']=Allstock['TurnoverRateRM'].rank(ascending=False,method='max') Allstock['RankVol']=Allstock['YearVolatilityByDay'].rank(ascending=False,method='max') Allstock['RankPB']=Allstock['Prc2bk'].rank(ascending=False,method='max') Allstock['Score']=Allstock['RankTO']+Allstock['RankVol']+Allstock['RankPB'] Allstock=Allstock.sort_values('Score',ascending=False) Allstock.drop(Allstock.tail(len(Allstock)-200).index,inplace=True) return Allstock[['TradingDay','SecuCode','TurnoverRateRM','YearVolatilityByDay','Prc2bk']] 我写了一个函数 stock_list(begindate, enddate, reportdate) 用以返回一个padas dataframe. 然后我下了一段代码 想让他跑一个参数序列并把结果放到数据库里 请帮我看下 我的代码错误在哪里import pandas as pd import openpyxl import pyodbc
打开excel文件
excel_file=r"C:\Users\LIQI\Documents\投资研究\微盘月度换手参数.xlsx" workbook = openpyxl.load_workbook(excel_file) #读取工作表 sheet2 = workbook['Sheet2'] log=pd.read_excel(excel_file,sheet_name='Sheet2')
连接到Access数据库
access_db_file = r"C:\Users\LIQI\Documents\microcapstrategy.accdb" conn_str = ( r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' f'DBQ={access_db_file};' ) conn = pyodbc.connect(conn_str) try: cursor = conn.cursor()
for index,row in log.iterrows(): begindate = row[0] enddate = row[1] reportdate = row[2] result = stock_list(begindate, enddate, reportdate) # 将结果写入数据库 for index, row_data in result.iterrows(): trading_day = row_data['TradingDay'] secu_code = row_data['SecuCode'] turnover_rate = row_data['TurnoverRateRM'] volatility = row_data['YearVolatilityByDay'] prc2bk = row_data['Prc2bk'] query=("INSERT INTO portfolio (TradingDay, SecuCode, TurnoverRateRM, YearVolatilityByDay, Prc2bk)"" VALUES (?, ?, ?, ?, ?)") values=(trading_day, secu_code, turnover_rate, volatility, prc2bk) cursor.execute(query,values) # 提交更改并关闭连接 conn.commit()except Exception as e: print("发生错误:", e) conn.rollback()
finally: conn.close()
- Assistant
The code seems to be mostly correct. However, there are a few minor issues:
-
Importing libraries: There is no need to import
pandasandxlwingstwice. Please remove the duplicate import statements. -
Function
stock_list(): The function definition line should be properly indented like the rest of the code for consistency. -
Looping through rows in
log: Since you are using theiterrows()method, there is no need to callindexseparately in the loop. You can directly unpack the row values likefor begindate, enddate, reportdate in log.iterrows():. -
Access database connection: You mentioned that you want to use a MySQL database, but the code provided seems to be connecting to an Access database instead. If you want to connect to a MySQL database, you need to use the
pymysqllibrary instead ofpyodbc. You will also need to modify the connection string accordingly.
Here's the modified code:
import os import pyodbc import pandas as pd import numpy as np from sqlalchemy import create_engine import fund_keystock # ... Other code ... access_db_file = r"C:\Users\LIQI\Documents\microcapstrategy.accdb" conn_str = ( r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' f'DBQ={access_db_file};' ) conn = pyodbc.connect(conn_str) try: cursor = conn.cursor() for begindate, enddate, reportdate in log.iterrows(): result = stock_list(begindate, enddate, reportdate) # 将结果写入数据库 for index, row_data in result.iterrows(): trading_day = row_data['TradingDay'] secu_code = row_data['SecuCode'] turnover_rate = row_data['TurnoverRateRM'] volatility = row_data['YearVolatilityByDay'] prc2bk = row_data['Prc2bk'] query = ("INSERT INTO portfolio (TradingDay, SecuCode, TurnoverRateRM, YearVolatilityByDay, Prc2bk)"" VALUES (?, ?, ?, ?, ?)") values = (trading_day, secu_code, turnover_rate, volatility, prc2bk) cursor.execute(query, values) # 提交更改并关闭连接 conn.commit() except Exception as e: print("发生错误:", e) conn.rollback() finally: conn.close()Please make sure to import the necessary libraries, modify the database connection string, and ensure that the
stock_list()function is defined correctly. -