def create_excel_table_from_df(df: pd.DataFrame, sheet_: Worksheet, row_ini: 1,
table_name):
"""Crea tabla de Excel en la hoja indicada a partir de un pandas DataFrame.
Parametros:
df: pandas DataFrame
row_ini: fila inicial, por default 1
sheet_: Worksheet object openpyxl
table_name: nombre de la tabla"""
col_last = get_excel_style(1, df.shape[1])[:-1]
# Crear tabla de Excel
tabla_excel = Table(
displayName=table_name,
ref=f"A{row_ini}:{col_last}{df.shape[0] + row_ini}") # nombre y tamaño
# declarar estilos a la tabla
style = TableStyleInfo(name="TableStyleMedium2", showRowStripes=False)
# asignar el estilo
tabla_excel.tableStyleInfo = style
# agregar tabla a la hoja
sheet_.add_table(tabla_excel)
def remove_empty_worksheet_rows(
*,
ws: Worksheet,
empty_rows: List[int]
) -> Worksheet:
"""
Delete empty worksheet rows.
Parameters
----------
ws : Worksheet
A worksheet from a workbook.
empty_rows : List[int]
List of row numbers.
Returns
-------
ws : Worksheet
A worksheet from a workbook.
Example
-------
Remove empty rows found.
>>> import datasense as ds
>>> ws = ds.remove_empty_worksheet_rows(
>>> ws=ws,
>>> empty_rows=empty_rows
>>> )
"""
for row_idx in reversed(empty_rows):
ws.delete_rows(
idx=row_idx,
amount=1
)
return ws
def df_to_excel(wb: Workbook, df: pd.DataFrame, sheet_: Worksheet, row_ini: 1,
as_table: False, **kwargs):
"""Agregar pandas DataFrame a hoja de Excel.
Parametros:
df: pandas DataFrame
sheet_: Worksheet object openpyxl
row_ini: fila inicial, por default 1
as_table: boolean, crear Tabla de Excel"""
# Agregar dataframe de Python a Excel
rows = dataframe_to_rows(df, index=False, header=True)
# agregar filas a Excel
for r_idx, row in enumerate(rows, row_ini):
for c_idx, value in enumerate(row, 1):
sheet_.cell(row=r_idx, column=c_idx, value=value)
if as_table:
try:
table_name = kwargs['table_name']
create_excel_table_from_df(df, sheet_, row_ini, table_name)
except KeyError:
raise ValueError(
'A table name must be specified if as_table is True.')
try:
for sheet in ['Sheet', 'Hoja', 'Hoja1']:
wb.remove(wb[sheet])
except KeyError:
pass
def __parseMonthly(worksheet: Worksheet) -> list:
res = []
# Timestamp of the data.
data_timestamp = datetime.now().isoformat()
minRowNum = FIRST_DATA_LINE_NUMBER
maxRowNum = len(worksheet['B'])
for rownum in range(minRowNum, maxRowNum + 1):
row = {}
if worksheet.cell(column=2, row=rownum).value is not None:
DataFileParser.__fillRow(row, PropertyName.TIME_PERIOD.value,
worksheet.cell(column=2, row=rownum),
False)
DataFileParser.__fillRow(row, PropertyName.VOLUME.value,
worksheet.cell(column=3, row=rownum),
True)
DataFileParser.__fillRow(row, PropertyName.ENERGY.value,
worksheet.cell(column=4, row=rownum),
True)
row[PropertyName.TIMESTAMP.value] = data_timestamp
res.append(row)
DataFileParser.logger.debug(
f"Monthly data read successfully between row #{minRowNum} and row #{maxRowNum}"
)
return res
def populate(self, ws: Worksheet, cg: ConnectGroup):
ws.title = cg.name
self.create_column_headers(ws)
for person in sorted(
cg.members, key=lambda p: p.personal_attributes[PERSONAL_ATTRIBUTE_NAME]
):
ws.append(self.person_as_row_values(person))
def read_von_neumann_excel(sheet: Worksheet, start_row: int, start_column: int,
cells_states: List[Tuple[str, str]]) -> List[int]:
zero_color = sheet.cell(start_row + 1, start_column).fill.start_color.value
zero_color = "#" + zero_color[2:]
zero_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == zero_color: zero_type = i
one_color = sheet.cell(start_row, start_column + 1).fill.start_color.value
one_color = "#" + one_color[2:]
one_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == one_color: one_type = i
two_color = sheet.cell(start_row + 1, start_column + 1).fill.start_color.value
two_color = "#" + two_color[2:]
two_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == two_color: two_type = i
three_color = sheet.cell(start_row + 2, start_column + 1).fill.start_color.value
three_color = "#" + three_color[2:]
three_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == three_color: three_type = i
four_color = sheet.cell(start_row + 1, start_column + 2).fill.start_color.value
four_color = "#" + four_color[2:]
four_type = 0
for i in range(len(cells_states)):
if cells_states[i][1] == four_color: four_type = i
rule = [zero_type, one_type, two_type, three_type, four_type]
return rule
def first_difference(left: Worksheet,
right: Worksheet) -> Optional[Tuple[Diff, int]]:
"""
Compare two sheets and return the first difference found, if any,
plus the index (starting from 1) of the row that differed.
:param left: The left-hand sheet
:param right: The right-hand sheet
:return: The first difference and index or None
"""
left_rows = left.iter_rows()
right_rows = right.iter_rows()
left_row: Union[Row, None]
right_row: Union[Row, None]
row_index = 0
while True:
left_row = next(left_rows, None)
right_row = next(right_rows, None)
row_index += 1
if left_row is None and right_row is None:
return None
if left_row is None:
return (None, right_row), row_index
if right_row is None:
return (left_row, None), row_index
if not rows_match(left_row, right_row):
return (left_row, right_row), row_index
def remove_worksheet_rows(
*,
ws: Worksheet,
rows_to_remove: List[int]
) -> Worksheet:
"""
Remove worksheet rows.
Parameters
----------
ws : Worksheet
A worksheet from a workbook.
rows_to_remove: List[int]
The list of row numbers to remove.
Returns
-------
ws : Worksheet
A worksheet from a workbook.
Example
-------
>>> import datasense as ds
>>> ws = ds.remove_worksheet_rows(
>>> ws=ws,
>>> rows_to_remove=rows_to_remove
>>> )
"""
for row in reversed(rows_to_remove):
ws.delete_rows(
idx=row,
amount=1
)
return ws
def load_initial_schedules(ws: Worksheet, monitor_dict: dict):
"""
指定シートからあらかじめ代入されている予定を読み取り、各監視者のスケジュールを初期化する。
:param ws: ワークシート
:param monitor_dict: 監視者の辞書(key:=name, item:=Monitor)
:return: 監視者のlatestシートにおける列インデックスの辞書(key:=name, item:=column index),
日付の辞書(key:=行番号, item:=datetime)
"""
monitor_column_dict = create_monitor_col_dict(ws, monitor_dict)
num_of_monitors = len(monitor_dict)
weekday_dict = {}
holiday_col = find_col_idx_by_val(ws, HEADER_ROW_IDX, 'Holiday')
for row_idx, row in enumerate(
ws.iter_rows(min_row=DATA_START_ROW_IDX,
max_col=num_of_monitors + 2), DATA_START_ROW_IDX):
day = row[0].value
if not day:
break
if not is_weekday(day, ws.cell(row=row_idx, column=holiday_col)):
continue
weekday_dict[row_idx] = day
for idx, monitor in enumerate(monitor_dict.values(), 1):
if val := row[idx].value:
role = convert_val_to_role(val)
monitor.schedule[day] = role
def _add_values_to_sheet(tag: SongTag,
song: Song,
sheet: Worksheet,
second_sheet: Union[Worksheet, None] = None):
"""Add the values from a speicifc tag to the indicated sheet.
If a second sheet is added,
add the second values from the tag.
Parameters
----------
tag : SongTag -
Which tag the data should be received from\n
song : Song -
Which song the data should be receieved from\n
sheet : Worksheet -
Which sheet the data should be added to\n
second_sheet : Union[Worksheet, None], optional -
Second sheet where data should be added, by default None
"""
values = tag.get_tag(song.id3)
if values is None:
return
for value in values:
if tag.value_length == 1:
value = cast(str, value)
sheet.append([value])
elif tag.value_length == 2:
value = cast(List[str], value)
role, person = value
sheet.append([role])
if second_sheet is not None:
second_sheet.append([person])
def merge_region(sheet: Worksheet, min_row, max_row, min_col, max_col):
assert max_row >= min_row > 0 and max_col >= min_col > 0
merged_region = (
f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}"
)
sheet.merge_cells(merged_region)
def write_samples_sheet(sheet: Worksheet, df_samples: pd.DataFrame,
min_coverage: int):
"""
Write and format samples sheet in workbook
"""
# write data
for r in dataframe_to_rows(df_samples, header=True, index=False):
sheet.append(r)
# apply conditional filling to perc_covered column
perc_column = next(x[0].column_letter for x in sheet.columns
if x[0].value == "perc_covered")
for i, row in enumerate(df_samples.itertuples(), start=2):
if pd.isna(row.perc_covered) or round(row.perc_covered) < min_coverage:
color = "fbc1c1"
else:
color = "cefbc1"
sheet[f"{perc_column}{i}"].fill = PatternFill(start_color=color,
fill_type="solid")
# improve style
for col in sheet.columns:
col[0].font = Font(name="Calibri", bold=True)
col[0].border = Border(
bottom=Side(border_style="medium", color="000000"))
col[0].alignment = Alignment(horizontal="center")
sheet.column_dimensions[col[0].column_letter].width = 13
def parse_xlsx_product_option(ws: Worksheet,
product_options_sheet: ProductOptionsSheet,
options_rows: List[int]) -> List[ProductOption]:
res = list()
for index, row_index in enumerate(options_rows):
name = ws.cell(row=row_index,
column=product_options_sheet.name_column_index).value
if isinstance(name, int):
name = str(name)
else:
name.strip()
price = float(
ws.cell(row=row_index,
column=product_options_sheet.price_column_index).value)
res.append(
ProductOption(
name=name,
price=price,
index=index,
is_default=index == 0,
xlsx_row=row_index,
))
return res
def saveActiveProjectTaskMapping(worksheet: Worksheet,
startRow: int = 1,
startColumn: int = 1):
"""Saves the active project list and active task list data.
The projects are stored in one row, the tasks corresponding to the project are saved in the same column as the project
Arguments:
worksheet {Worksheet} -- [description]
startRow {int} -- starting row form where the data should be stored
startColumn {int} -- starting column form where the data should be stored
"""
projectList = excelDatabase.getActiveProjects()
if projectList is None:
return
for project in projectList:
taskList = excelDatabase.getActiveTasksByProjectId(project.projectId)
if taskList is None:
taskList = []
task = Task()
task.taskName = "-"
taskList.append(task)
project.taskList = taskList
defaultProject = getDefaultProjects()
projectList.extend(defaultProject)
column = startColumn
for project in projectList:
row = startRow
worksheet.cell(row=row, column=column).value = project.projectName
for task in project.taskList:
row += 1
worksheet.cell(row=row, column=column).value = task.taskName
column += 1
def create_all_cards(sheet: Worksheet, index: ScryfallDataIndex) -> None:
"""Create all cards sheet from card_db."""
sheet.title = "All Cards"
sheet.append(ALL_CARDS_SHEET_HEADER)
for name in sorted(index.name_to_cards):
row = [name, get_references(index, name)]
sheet.append(row)
def _fill_neat_settings(self, sheet: Worksheet, col_to_paste: int, row_to_paste: int, neat_settings: list) -> tuple:
next_row = row_to_paste
section: dict
end_column_index = col_to_paste
for section in neat_settings:
should_render = section.get('show')
if not should_render:
continue
translate: str = ReportPredictionResultsExcelMaker.neat_settings_translations[section.get('header')]
sheet.cell(row=next_row, column=col_to_paste, value=translate)
next_row += 1
section_params = section.get('params')
param: dict
for param in section_params:
should_render = param.get('showInGui')
if not should_render:
continue
translate: str = ReportPredictionResultsExcelMaker.neat_settings_translations[param.get('name')]
sheet.cell(row=next_row, column=col_to_paste, value=translate)
value = param.get('value')
if type(value) is list:
for i in range(len(value)):
end_column_index = max(end_column_index, col_to_paste + i + 1)
sheet.cell(row=next_row, column=col_to_paste + i + 1,
value=ReportPredictionResultsExcelMaker.neat_settings_translations[value[i]])
elif type(value) is bool:
sheet.cell(row=next_row, column=col_to_paste + 1, value='Да' if value is True else 'Нет')
else:
sheet.cell(row=next_row, column=col_to_paste + 1, value=value)
next_row += 1
return next_row, end_column_index
def cell_fill_down(
*,
ws: Worksheet,
min_row: int,
max_row: int,
min_col: int,
max_col: int
) -> Worksheet:
"""
Fill empty cell with the value from the cell above
Parameters
----------
ws : Worksheet
The worksheet in which to change the case of column(s).
min_row : int
The first row in the range to change.
max_row : int
The last row in the range to change.
min_col : int
The first column in the range to change.
max_col : int
The last column in the range to change.
Returns
-------
ws : Worksheet
The worksheet in which cells were modified.
Example
-------
>>> for column in fill_down_columns:
>>> ws = ds.cell_fill_down(
>>> ws=ws,
>>> min_row=row_below_labels,
>>> max_row=ws.max_row,
>>> min_col=column_names_numbers[column],
>>> max_col=column_names_numbers[column]
>>> )
"""
row_count = 0
for row in ws.iter_rows(
min_col=min_col,
max_col=max_col
):
for cell in row:
if cell.value:
row_count += 1
if row_count > 0:
for row in ws.iter_rows(
min_row=min_row + 1, # start one row below the 'start' row
max_row=max_row,
min_col=min_col,
max_col=max_col
):
for cell in row:
if cell.value in [None, 'None', '']:
cell.value = ws[cell.row - 1][min_col - 1].value
return ws
def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__LEFT_COLUMNS[0] + str(current_row)
end_cell = self.__RIGHT_COLUMNS[3] + str(current_row)
sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 2
self._style_sheet_title(sheet)
def _get_last_cell(ws: Worksheet,
row=-1,
col=1) -> Tuple[int, datetime.date]:
row_count = 1
while ws.cell(row_count + 1, col).value:
row_count += 1
row = max(row_count + row + 1, 1)
return row, ws.cell(row, col)
def copy_sheet_header(main_ws: Worksheet, dst_ws: Worksheet):
# determine max column
#dst_ws.merge_cells('A1:M7')
dst_ws.merged_cells.ranges = main_ws.merged_cells.ranges
for r in main_ws.iter_rows(1, NUM_ROW_HEADER):
for c in r:
#dst_ws.cell(c.row, c.column, c.value)
dst_ws.copy_cell(c.row, c.column, c)
def parse_metadata_command(sh: Worksheet, area: AreaTupleType, name: str = None) -> IssuesLabelContentTripleType:
"""
Most "parse" methods are mostly syntactic (as opposed to semantic). They do not check existence of names.
But in this case, the valid field names are fixed beforehand, so they are checked at this time.
Some of the fields will be controlled also, according to some
:param sh: Input worksheet
:param area: Tuple (top, bottom, left, right) representing the rectangular area of the input worksheet where the
command is present
:return: list of issues (issue_type, message), command label, command content
"""
some_error = False
issues = []
controlled = create_dictionary()
mandatory = create_dictionary()
keys = create_dictionary()
for t in metadata_fields:
controlled[t[4]] = t[3]
mandatory[t[4]] = t[2]
keys[t[0]] = t[4]
# Scan the sheet, the first column must be one of the keys of "k_list", following
# columns can contain repeating values
# Map key to a list of values
content = {} # Dictionary of lists, one per metadata key
for r in range(area[0], area[1]):
label = sh.cell(row=r, column=area[2]).value
if label in keys:
key = keys[label]
for c in range(area[2]+1, area[3]):
value = sh.cell(row=r, column=c).value
if value:
value = str(value).strip()
if controlled[key]:
# Control "value" if the field is controllable
cl = {"dimensions": ["water", "energy", "food", "land", "climate"],
"subject_topic_keywords": None,
"geographical_level": ["local", "regional", "region", "country", "europe", "global", "sectoral", "sector"],
"geographical_situation": None, # TODO Read the list of all geographical regions (A long list!!)
"restriction_level": ["internal", "confidential", "public"],
"language": None, # TODO Read the list of ALL languages (or just "English"??)
}
if cl[key] and value.lower() not in cl[key]:
issues.append((3, "The key '"+key+"' should be one of: "+",".join(cl[key])))
if key not in content:
content[key] = []
content[key].append(value)
else:
issues.append((2, "Row "+str(r)+": unknown metadata label '"+label+"'"))
for key in keys.values():
if mandatory[key] and key not in content:
some_error = True
issues.append((3, "The value '"+key+"' is mandatory in the definition of the metadata"))
return issues, None, content
def __writeCurveChartData(sheet: Worksheet, tests: Iterable[str],
order: Iterable[str], data: Dict[str, dict]) -> dict:
from .TestSuite import _PSNR, _KBS, _TIME, parseSheetLayer
to_write_data = []
out_ref = {}
col = 2
ref_len = 4
def toRefFunc(sheet, cells):
return [
"=" +
__SR_FORMAT.format(sheet=parseSheetLayer(sheet)[0], cell=cell)
for cell in cells
]
for seq in order:
to_write_data.append([
seq,
])
out_ref[seq] = {}
to_write_data.append([
"Data Type", "Test", "Data Point 1", "Data Point 2",
"Data Point 3", "Data Point 4"
])
for test in tests:
out_ref[seq][test] = {}
to_write_data.append(
[_KBS, test, *toRefFunc(test, data[test][seq][_KBS])])
to_write_data.append(
[_PSNR, test, *toRefFunc(test, data[test][seq][_PSNR])])
to_write_data.append(
[_TIME, test, *toRefFunc(test, data[test][seq][_TIME])])
row = len(to_write_data)
out_ref[seq][test][__RATE] = Reference(sheet,
min_col=col,
max_col=col + ref_len,
min_row=row - 2)
out_ref[seq][test][__PSNR] = Reference(sheet,
min_col=col,
max_col=col + ref_len,
min_row=row - 1)
out_ref[seq][test][__TIME] = Reference(sheet,
min_col=col,
max_col=col + ref_len,
min_row=row)
for row in to_write_data:
sheet.append(row)
# hide chart data
sheet.column_dimensions.group('A', 'F', hidden=True)
return out_ref
def _fill_sheet(data, sheet: Worksheet, fields):
if sheet.max_row == 1:
for col, field in enumerate(fields, 1):
sheet.cell(1, col, field["name"])
row = sheet.max_row + 1
for col, field in enumerate(fields, 1):
value = data.get(field["name"])
if isinstance(value, (list, dict)):
value = json.dumps(value)
sheet.cell(row, col, value)
def collect_table(worksheet: Worksheet, year: int, fund_name, row: int,
column: int) -> object:
"""
Start to run on the fields and look for the anchor cell which from there
the data lay out.
"""
data = {}
for iterated_row in range(row, row + 21):
row_label: str = worksheet.cell(row=iterated_row, column=column).value
if not row_label:
# Some of the years has less than 21 fields which mean we'll fail
# with older files. Skipping on an empty row label.
continue
row_data = {}
for iterated_column in range(column + 1, column + 1 + 24):
# Getting the matching month of the current cell and resetting
# the cell value.
month_index = int(iterated_column / 2)
if month_index not in months:
continue
month_name = months[month_index]
if month_name not in row_data:
row_data[month_name] = {
'התרומה לתשואה': '',
'שיעור מסך הנכסים': '',
'year': year,
'fund': fund_name,
}
cell_kwargs = {'row': iterated_row, 'column': iterated_column}
cell_value = worksheet.cell(**cell_kwargs).value
if cell_value:
if type(cell_value) is not str:
cell_value = format(cell_value * 100, '.2f')
# Values in cell with even index number have a label and odd
# indexed-cell have a different label.
if iterated_column % 2 == 0:
key = 'התרומה לתשואה'
else:
key = 'שיעור מסך הנכסים'
row_data[month_name][key] = cell_value
data[row_label.strip()] = row_data
return data
def find_or_create_row(self, rls_date: datetime.date, ws: Worksheet):
row = 1
while True:
row += 1
date = ws.cell(row, 1).value
if date == rls_date:
return row
if not date or date > rls_date:
ws.insert_rows(row)
ws.cell(row, 1, rls_date)
return row
def add_headers_and_title(sheet: Worksheet):
headers = ('序号', '考试日期', '考试时间', '考试名称', '开课院系', '考试地点', '班级', '考生人数',
'考生院系')
for index, header in enumerate(headers):
sheet.cell(2, index + 1, header)
title = '东北大学考试日程表'
sheet.merge_cells(start_row=1, end_row=1, start_column=1, end_column=9)
cell = sheet['A1']
cell.value = title
cell.alignment = Alignment(horizontal="center", vertical="center")
def _find_free_cell(self, col_index: int, row_index: int,
sheet: Worksheet) -> Tuple[Cell, int]:
target_cell = sheet.cell(row_index + 1, col_index + 1)
while True:
if isinstance(target_cell, MergedCell):
col_index += 1
target_cell = sheet.cell(row_index + 1, col_index + 1)
else:
break
return target_cell, col_index
def _finalize_styling(self, sheet: Worksheet):
sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4
sheet.sheet_properties.pageSetUpPr.fitToPage = True
sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH)
sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42
sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42
for rows in sheet.iter_rows(min_row=3):
for row in rows:
sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT
def add_sale_data(sheet: Worksheet, sale_record: InvestmentRecord,
recs_and_quants_to_sell: list):
"""Returns the number of rows added for subquantities.
"""
net_capital_gain_formula = "=(" + str(sale_record.quantity) + "*" \
+ cell.get_column_letter(COLUMNS["Average price"]) \
+ str(sale_record.row_idx) + ")"
num_recs_and_quants = len(recs_and_quants_to_sell)
using_subquantities = True if num_recs_and_quants > 1 else False
current_row_idx = sale_record.row_idx + 1 if using_subquantities else sale_record.row_idx
quantity_column_to_use = COLUMNS[
"Subquantity"] if using_subquantities else COLUMNS["Quantity"]
for rec, quant in recs_and_quants_to_sell:
if using_subquantities:
sheet.cell(current_row_idx, quantity_column_to_use).value = quant
# Else we assume the Quantity of the investment record was written earlier
cost_base_formula = "=" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "*" + cell.get_column_letter(COLUMNS["Average price"]) + str(rec.row_idx) \
+ "+(" + cell.get_column_letter(COLUMNS["Brokerage"]) + str(rec.row_idx) \
+ "*" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "/" + cell.get_column_letter(COLUMNS["Quantity"]) + str(rec.row_idx) + ")" \
+ "+(" + cell.get_column_letter(COLUMNS["Brokerage"]) + str(sale_record.row_idx) \
+ "*" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "/" + cell.get_column_letter(COLUMNS["Quantity"]) + str(sale_record.row_idx) + ")"
sheet.cell(current_row_idx,
COLUMNS["Cost base"]).value = cost_base_formula
capital_gain_formula = "=(" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \
+ "*" + cell.get_column_letter(COLUMNS["Average price"]) + str(sale_record.row_idx) \
+ ")-" + cell.get_column_letter(COLUMNS["Cost base"]) + str(current_row_idx)
if sale_record.trade_date > rec.trade_date + timedelta(days=365):
capital_gain_column_to_use = COLUMNS["Capital gain > 1 year"]
else:
capital_gain_column_to_use = COLUMNS["Capital gain <= 1 year"]
sheet.cell(current_row_idx,
capital_gain_column_to_use).value = capital_gain_formula
history = sheet.cell(rec.row_idx, COLUMNS["History"]).value
new_history = "Sold " + str(
quant) + " on " + sale_record.trade_date.strftime("%d/%m/%Y. ")
sheet.cell(rec.row_idx,
COLUMNS["History"]).value = (history + new_history
if history else new_history)
current_row_idx += 1
return num_recs_and_quants if using_subquantities else 0
def __writeCharts(sheet: Worksheet, tests: Iterable[str], order: Iterable[str],
charts: Iterable[Tuple[str, str]], data: Dict[str,
dict]) -> None:
row = 1
bound_scale = 10
def updateBounds(bounds, x_data, y_data):
from statistics import mean
if not bounds:
bounds = [0, 0, 0, 0, 0, 0, 0]
bounds[0] = min(bounds[0], *x_data)
bounds[1] = max(bounds[1], *x_data)
bounds[2] = mean(bounds[2], *x_data)
bounds[3] = min(bounds[3], *y_data)
bounds[4] = max(bounds[4], *y_data)
bounds[5] = mean(bounds[5], *y_data)
return bounds
for seq in order:
col = 0
for (typeX, typeY) in charts:
chart = ScatterChart(scatterStyle='lineMarker')
chart.title = seq
chart.x_axis.title = typeX
chart.y_axis.title = typeY
chart.visible_cells_only = False
bounds = None
for test in tests:
#bounds = updateBounds(bounds, data[seq][test][__DATA][typeX], data[seq][test][__DATA][typeY])
rX = data[seq][test][typeX]
rY = data[seq][test][typeY]
series = Series(Reference(sheet,
min_col=rY.min_col,
max_col=rY.max_col,
min_row=rY.min_row),
Reference(sheet,
min_col=rX.min_col + 1,
max_col=rX.max_col,
min_row=rX.min_row),
title_from_data=True)
series.marker.symbol = 'auto'
chart.series.append(series)
if bounds:
sheet.x_axis.scaling.min = max(
bounds[0] - bounds[2] / bound_scale, 0)
sheet.x_axis.scaling.max = bounds[1] + bounds[2] / bound_scale
sheet.y_axis.scaling.min = max(
bounds[3] - bounds[5] / bound_scale, 0)
sheet.y_axis.scaling.max = bounds[4] + bounds[5] / bound_scale
sheet.add_chart(chart, get_column_letter(7 + col) + str(row))
col += 9
row += 15
