I am trying to populate a cell with a dynamic array formula using xlwings but each time I try the formula either ends up with @ after the = or the formula is being put inbetween {} and I can't seem to fix this. This is the code for the function in question:
def process_files():
# Disable button and change image
button_1.configure(state=tk.DISABLED, image=button_image_1_processing)
window.update_idletasks() # Force GUI update
try:
update_entry_2('Process Started...')
directory = entry_1.get()
if not directory:
update_entry_2("Directory not selected.")
return
start_date_str = entry_6.get()
end_date_str = entry_5.get()
forward_date_str = entry_4.get()
if not validate_date(start_date_str):
update_entry_2('Start Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
return
if not validate_date(end_date_str):
update_entry_2('End Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
return
if not validate_date(forward_date_str):
update_entry_2('Forward Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
return
# Convert dates from strings to datetime objects
start_date = datetime.strptime(start_date_str, "%d/%m/%Y")
end_date = datetime.strptime(end_date_str, "%d/%m/%Y")
forward_date = datetime.strptime(forward_date_str, "%d/%m/%Y")
# Check if end date is earlier than start date
if end_date < start_date:
update_entry_2("End date cannot be earlier than start date.")
return
# Check if forward date is earlier than start date
if forward_date < start_date:
update_entry_2("Forward date cannot be earlier than start date.")
return
# Check if end date is earlier than forward date
if end_date < forward_date:
update_entry_2("End date cannot be earlier than forward date.")
return
if not validate_currency(entry_3.get()):
update_entry_2('Account balance is not correct, this must be a number.')
return
xml_files = [f for f in os.listdir(directory) if f.endswith('.xml')]
paired_files = [(f, f.replace('.forward', '')) for f in xml_files if
f.endswith('.forward.xml') and f.replace('.forward', '') in xml_files]
for fwd_file, back_file in paired_files:
update_entry_2(f"{fwd_file} Found, Converting to XLSX")
fwd_file_path = os.path.join(directory, fwd_file)
back_file_path = os.path.join(directory, back_file)
template_path = os.path.join(directory, 'SetFinderTemplate.xlsx')
fwd_xlsx_path = os.path.join(directory, fwd_file.replace('.xml', '.xlsx'))
back_xlsx_path = os.path.join(directory, back_file.replace('.xml', '.xlsx'))
output_file_path = os.path.join(directory, fwd_file.replace('.forward.xml', '_Filtered.xlsx'))
# Convert XML to XLSX
convert_xml_to_xlsx(fwd_file_path, fwd_xlsx_path)
convert_xml_to_xlsx(back_file_path, back_xlsx_path)
update_entry_2('Files converted, importing data into Jake’s Template')
# Load the excel template
template_wb = load_workbook(template_path)
# Load dataframes
fwd_df = pd.read_excel(fwd_xlsx_path)
back_df = pd.read_excel(back_xlsx_path)
# Make sure the columns are in the correct order
fwd_columns = ['Pass', 'Forward Result', 'Back Result', 'Profit', 'Expected Payoff', 'Profit Factor',
'Recovery Factor', 'Sharpe Ratio', 'Custom', 'Equity DD %', 'Trades']
back_columns = ['Pass', 'Result', 'Profit', 'Expected Payoff', 'Profit Factor', 'Recovery Factor',
'Sharpe Ratio', 'Custom', 'Equity DD %', 'Trades']
fwd_df = fwd_df[fwd_columns]
back_df = back_df[back_columns]
# Get the sheets
fwd_sheet = template_wb['Fwd Data']
back_sheet = template_wb['Back Data']
# Write dataframes to the sheets
for i, row in enumerate(fwd_df.values, start=3):
for j, value in enumerate(row, start=6): # Start from column F (6)
fwd_sheet.cell(row=i, column=j, value=value)
for i, row in enumerate(back_df.values, start=3):
for j, value in enumerate(row, start=1):
back_sheet.cell(row=i, column=j, value=value)
instructions_notes_sheet = template_wb['INSTRUCTIONSNOTES']
instructions_notes_sheet['B15'] = entry_6.get() # Start Date
instructions_notes_sheet['B17'] = entry_5.get() # End Date
instructions_notes_sheet['B16'] = entry_4.get() # Forward Date
instructions_notes_sheet['B18'] = entry_3.get() # Account balance
# Save workbook
template_wb.save(output_file_path)
update_entry_2('Template populated and Filtered file saved')
# Load workbook
wb = xw.Book(output_file_path)
# Get the sheet
sheet = wb.sheets['RESULTS-ExcelVersion']
# Assign the formula to cell A9
sheet.range(
'A9').formula_array = '=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")'
# Save and close the workbook
wb.save()
wb.close()
I am not super experienced with code and I have tried reading xlwings documentation along with openpyxl and also XlsxWriter to try and solve this problem but just keep hitting brick wall after brick wall. The formula I want to have in cell A9 is;
=IFERROR(SORT(FILTER('Fwd Data'!B:P,'Fwd Data'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")
I have also tried putting the formula in the cell without the = and just getting python to add the = for me after the file has been created but this also resulted in an error. I have Office 365 and it is fully up to date so I know the formula is supported and when putting it in manually I get the result I wanted.
I have read through the forums and seen This post but no solutions I have seen have given results as yet. Also a lot of the posts are a year plus old so I was hoping that there may be a solution to this by now.
Any help anyone can offer is appreciated. Thank you
The question concerns these lines in your code:
# Assign the formula to cell A9
sheet.range(
'A9').formula_array = '=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")'
Consider the following three variants of returning a formula to an Excel cell with xlwings:
formula = """=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")"""
# 1: .formula_array '={...}'
sheet.range("A9").formula_array = formula
# 2: .formula '=@'
sheet.range("A9").formula = formula
# 3: .formula2 '='
sheet.range("A9").formula2 = formula
You will find that the output for option 1 contains {}, option 2 has =@, and option 3 is just =.
.formula_arrayThis returns an array of formula, which can also be recreated by highlighting multiple cells in Excel, typing a formula in one and pressing Ctrl + Shift + Enter.
This Microsoft webpage provides a good explanation on when this might be used.
.formula and .formula2As mentioned in your post, this answer provides a good explanation on the differences between the two.
.formula2 is explained here by Microsoft, which also contains a link to a very detailed comparison of the two. This variation allows for spilling of outputs from the formula.
So, if you do not want either {} or @ in the returned formula, you should use .formula2 in your code.
Also note that I have used triple quotes """ around of the Excel formula, because the formula includes both ' and ".