-- 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']] 这是我写的一个函数 现在我有一个excel文件,在sheet1的第一列到第三列,对应着stock_list()函数的三个参数,请帮我写一段代码 循环调用这个excel文件里每一行的三个参数,然后把return的结果Allstock写进本地的accdb数据库里
- Assistant
import xlrd import pyodbc
打开excel文件
workbook = xlrd.open_workbook('your_excel_file.xlsx') sheet = workbook.sheet_by_name('Sheet1')
连接数据库
db_filename = r'your_database.accdb' conn_str = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + db_filename conn = pyodbc.connect(conn_str) cursor = conn.cursor()
循环读取excel文件每一行的参数并调用函数
for row in range(1, sheet.nrows): begindate = sheet.cell_value(row, 0) enddate = sheet.cell_value(row, 1) reportdate = sheet.cell_value(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'] cursor.execute("INSERT INTO your_table (TradingDay, SecuCode, TurnoverRateRM, YearVolatilityByDay, Prc2bk) VALUES (?, ?, ?, ?, ?)", (trading_day, secu_code, turnover_rate, volatility, prc2bk))提交并关闭连接
conn.commit() cursor.close() conn.close()
- User
这里的两层for循环 好像应该有缩进关系吧
- Assistant
你是对的,我犯了一个错误,两个for循环应该有缩进关系。以下是修正后的代码:
import xlrd import pyodbc # 打开excel文件 workbook = xlrd.open_workbook('your_excel_file.xlsx') sheet = workbook.sheet_by_name('Sheet1') # 连接数据库 db_filename = r'your_database.accdb' conn_str = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + db_filename conn = pyodbc.connect(conn_str) cursor = conn.cursor() # 循环读取excel文件每一行的参数并调用函数 for row in range(1, sheet.nrows): begindate = sheet.cell_value(row, 0) enddate = sheet.cell_value(row, 1) reportdate = sheet.cell_value(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'] cursor.execute("INSERT INTO your_table (TradingDay, SecuCode, TurnoverRateRM, YearVolatilityByDay, Prc2bk) VALUES (?, ?, ?, ?, ?)", (trading_day, secu_code, turnover_rate, volatility, prc2bk)) # 提交事务 conn.commit() # 关闭连接 cursor.close() conn.close()感谢你的指正!如果还有其他问题,请随时提问。
- User
FileNotFoundError Traceback (most recent call last) ~\AppData\Local\Temp/ipykernel_9112/464831591.py in 3 4 # 打开excel文件 ----> 5 workbook = xlrd.open_workbook('微盘月度换手参数.xlsx') 6 sheet = workbook.sheet_by_name('Sheet2') 7
~\anaconda3\lib\site-packages\xlrd_init_.py in open_workbook(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows, ignore_workbook_corruption) 164 """ 165 --> 166 file_format = inspect_format(filename, file_contents) 167 # We have to let unknown file formats pass through here, as some ancient 168 # files that xlrd can parse don't start with the expected signature.
~\anaconda3\lib\site-packages\xlrd_init_.py in inspect_format(path, content) 58 else: 59 path = os.path.expanduser(path) ---> 60 with open(path, "rb") as f: 61 peek = f.read(PEEK_SIZE) 62
FileNotFoundError: [Errno 2] No such file or directory: '微盘月度换手参数.xlsx' 报了这个错误, 请帮我看下问题
- Assistant
这个错误表明找不到指定的excel文件。请确保excel文件在当前工作目录或提供了完整的文件路径。可以尝试使用绝对路径或相对路径来打开excel文件。
如果你已经确认文件路径是正确的,还是出现找不到文件的错误,请确保指定的文件名和文件扩展名是正确的,大小写也要匹配。如果文件名中含有特殊字符或空格,也可能导致找不到文件的错误。
另外,还要确保在打开文件之前,先执行了必要的导入语句,例如导入所需的库xlrd和pyodbc。
如果问题仍然存在,请提供更多的细节,以便我能够更好地帮助你解决问题。
- User
import xlrd import pyodbc
打开excel文件
workbook = xlrd.open_workbook('微盘月度换手参数.xlsx') sheet = workbook.sheet_by_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)
循环读取excel文件每一行的参数并调用函数
#for row in range(1, sheet.nrows): begindate = sheet.cell_value(0, 0) enddate = sheet.cell_value(0, 1) reportdate = sheet.cell_value(0, 2)
调用函数
result = stock_list(begindate, enddate, reportdate) 这是我改的代码 哪里要给出路径?如果这是我的路径C:\Users\LIQI\Documents\投资研究\微盘月度换手参数.xlsx
- Assistant
你已经在代码中指定了Excel文件的路径,路径为:
C:\Users\LIQI\Documents\投资研究\微盘月度换手参数.xlsx。根据你的代码,Excel文件的路径已经正确设置为绝对路径。当运行代码时,xlrd库将根据指定的路径打开Excel文件进行处理。
请确保路径中的文件名和扩展名正确匹配,并且文件在指定的路径中存在。
如果你仍然遇到问题,请确保Excel文件的名称、路径和文件扩展名无