Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

-- coding: utf-8 -- import os import xlwings as xw import sys import pymysql import…

  1. 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()

  2. Assistant

    The code seems to be mostly correct. However, there are a few minor issues:

    1. Importing libraries: There is no need to import pandas and xlwings twice. Please remove the duplicate import statements.

    2. Function stock_list(): The function definition line should be properly indented like the rest of the code for consistency.

    3. Looping through rows in log: Since you are using the iterrows() method, there is no need to call index separately in the loop. You can directly unpack the row values like for begindate, enddate, reportdate in log.iterrows():.

    4. 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 pymysql library instead of pyodbc. 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.