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,\'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,'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 Continue reading...
