def day_rate(rowNum: int, colNum: int, sheet: xlrd.sheet.Sheet) -> list:
"""
日收益率函数
:param rowNum:
:param colNum:
:param sheet:
:return:
"""
# 账户日收益率
day_rate_list = []
# 股票数
stock_num = colNum - 3
for i in range(2, rowNum):
cnt = 0
sum_x = 0.0
avg_x = 0.0
# colNum - 2 股票数读取控制
for j in range(1, colNum - 2):
var_type = sheet.cell(i, j).ctype
var = sheet.cell(i, j).value
if var_type and var:
sum_x = sum_x + sheet.cell(i, j).value
avg_x = sum_x / stock_num
day_rate_list.append(avg_x)
return day_rate_list
def read_firm(firm_sheet: xlrd.sheet.Sheet):
"""
Note row & column is 0-index based
:param firm_sheet:
:return:
"""
# get firm name
firm_name = firm_sheet.cell(7, 1).value
# selected data column, 0 based index
if firm_sheet.ncols == 10:
# [存货编号, 存货全名, 采购数量, 价税合计]
selected_columns = [1, 2, 3, 6]
elif firm_sheet.ncols == 11:
selected_columns = [1, 2, 3, 4, 7]
else:
print(firm_sheet.ncols, firm_sheet.name)
raise Exception("Unsupported data sheet format")
rows = []
for i in range(20, firm_sheet.nrows):
row = [firm_name]
for j in selected_columns:
entry = firm_sheet.cell(i, j).value
try:
entry = float(entry)
except Exception:
pass
row.append(entry)
has_empty_data = False
# check empty entry in data
for k in [-2, -1]:
if row[k] == '':
has_empty_data = True
break
if has_empty_data:
continue
# print(row)
row.append(round(row[-1] / row[-2], 2)) # 含税单价 = 价税合计 / 采购数量
rows.append(row)
data_frame = pd.DataFrame(rows, columns=["公司名称"] + [firm_sheet.cell(19, i).value for i in selected_columns] + ["含税单价"])
print(data_frame)
if "基本单位" not in data_frame.columns:
data_frame.insert(3, "基本单位", np.nan, True)
return data_frame
def _get_current_date_by_field(self, field: dict, sheet: xlrd.sheet.Sheet,
row: int) -> str:
date = sheet.name
if re.search(NIGHT_PATTERN, date):
date = date.split('-')
time = sheet.cell(rowx=row, colx=field['column_number']).value
if time == '':
return ''
time = self._get_correct_time(time)
if datetime.datetime.strptime(time,
TIME_PATTERN).time() >= NIGHT_SHIFT:
if date[1].split(
'.')[0] == '01' and date[1].split('.')[1] != '01':
# TODO: make its better in the future
date = date[0] + '.' + '.'.join(
date[1].split('.')[1:]) + ' ' + time
date = date[:3] + str(int(date[3:5]) - 1) + date[5:]
else:
date = date[0] + '.' + '.'.join(
date[1].split('.')[1:]) + ' ' + time
try:
return datetime.datetime.strptime(date, '%d.%m.%y %H:%M')
except ValueError:
# in this case we resolve problem with mismatch of number of month and number of days
self._logger.warning(
'There are conformity of month and days, check corrects of value'
)
date = date[:3] + str(int(date[3:5]) - 1) + date[5:]
return datetime.datetime.strptime(date, '%d.%m.%y %H:%M')
else:
date = date[1] + ' ' + time
return datetime.datetime.strptime(date, '%d.%m.%y %H:%M')
if re.search(DAY_PATTERN, date):
time = sheet.cell(rowx=row, colx=field['column_number']).value
if not time:
return ''
time = self._get_correct_time(time)
date = date + ' ' + time
return datetime.datetime.strptime(date, '%d.%m.%y %H:%M')
raise TimePatternError(
'There is no such time pattern: {}'.format(date))
def buildQuery(sheet: xlrd.sheet.Sheet):
query = list()
for col in range(sheet.ncols):
firstRowCell = sheet.cell(0, col)
query.append(firstRowCell.value)
return query