回归方程代码

#! /usr/bin/env python3
# .-*- coding:utf-8 .-*-

import time

from openpyxl import load_workbook


class excel():
    def __init__(self, excle_name, stack_code):
        self.excle_name = excle_name
        self.stack_code = stack_code
        self.wb = load_workbook(self.excle_name)
        self.ws = self.wb[stack_code]

    #求均线回归曲线的b值（y = bx +a）
    def return_huiguifangcheng(self,start, long):
        value_list = []
        # min_value = 13.84
        for each in self.ws.iter_rows(min_row=start-long+1, max_row=start):
            value_list.append([list(each)[1].value, list(each)[3].value])
        xiyi = sum([data[0]*data[1] for data in value_list])
        x_ =  sum([data[0] for data in value_list])/long
        y_ = sum([data[1] for data in value_list])/long
        xi2 = sum([data[0]*data[0] for data in value_list])
        b = (xiyi - long*x_*y_)/(xi2 - long*x_*x_)
        return b*100000

    def test(self, value_list):
        long = len(value_list)
        xiyi = sum([data[0] * data[1] for data in value_list])
        x_ = sum([data[0] for data in value_list]) / long
        y_ = sum([data[1] for data in value_list]) / long
        xi2 = sum([data[0] * data[0] for data in value_list])
        b = (xiyi - long * x_ * y_) / (xi2 - long * x_ * x_)
        return b

    #写入回归值
    def return_huiguizhi_avg(self):
        #写入15日股价回归值
        long = 60
        start_15 = long +1
        for each in self.ws.iter_rows(min_row= long+1,max_row=3006):
            price = self.return_huiguifangcheng(start_15, long)
            start_15+=1
            print("J"+str(start_15-1),"---->=", price)
            self.ws["E"+str(start_15-1)] = price
            self.ws["F"+str(start_15-1)] = f"=E{start_15-1}-E{start_15-2}"
            # self.ws["L"+str(start_15-1)] = f"=SUM(J{start_15-30}:J{start_15-1})/{long}"
        self.wb.save(self.excle_name)
        self.wb.close()

    def change_num(self, sheetname=""):
        count = 200
        start = 2
        end = 31

        count = 31
        min_value = 9663.32419564278
        for row in list(self.ws.rows)[end:]:
            #插入计算公式 =G6-15000
            # self.ws["G"+str(count)] = f"=SUM(F{start}:F{end})/5"
            if self.ws["A"+str(end)].value :
                # self.ws["M"+str(end)] = f"=SUM(D{start}:D{end})/60"
                # self.ws["F"+str(end)] = f"=E{count}-{min_value}"
                self.ws["F"+str(end)] = f"=(E{count}/{min_value})*1000"
                count, start, end = count+1, start+1, end+1
        self.wb.save(self.excle_name)
        self.wb.close()

    def read_data_from_excel(self):
        wb = load_workbook('证券公司2.xlsx')
        ws = wb["Sheet1"]
        result = [list(data) for data in ws.iter_rows(min_row=2, max_row=6, min_col=1, max_col=3, values_only=True)]
        wb.close()
        data_dict = {}
        for data in result:
            data_dict.update({str(data[1]): str(data[0])})
        return data_dict

    def find_min_value(self):
        min_value = 9835
        for row in self.ws.iter_rows(min_row= 31,max_row=3006):
            if min_value > list(row)[4].value:
                min_value = list(row)[4].value
                print("min_value  --- >  ",min_value)


    #计算所有行业的数据
    def all_data_total(self):
        start = 2
        end = 6
        self.wb = load_workbook(self.excle_name)
        self.ws = self.wb[f'成交量']
        stack_code_list = []
        for stackCode, stackName in self.read_data_from_excel().items():
            stack_code_list.append(self.wb[f'{stackCode}'])
        datas = []
        for ws in stack_code_list:
            temp = {}
            for row in ws.rows:
                data = (list(row))
                temp.update({f"{data[0].value}":f"{data[5].value}"})
            datas.append(temp)
        count = 1
        for cell in self.ws.rows:
            data = list(cell)
            key = data[0].value
            num = 0
            for d in datas:
                try:
                    num += int(d.get(key, 0))
                    print(d.get(key, 0))
                except Exception as err:
                    print("跳过")

            if self.ws["A" + str(count) ]:
                self.ws["B" + str(count) ] = int(num/100000)
                print("---"*30)
                print(num)
                count += 1
        self.wb.save(self.excle_name)
        self.wb.close()

test = excel("股票价格.xlsx", "000538")
# test.find_min_value()
# test.return_huiguifangcheng(31, 30)
test.return_huiguizhi_avg()
# test.return_huiguifangcheng(26, 25)
# test.change_num()