Help using a dynamic array excel formula with XLWings

[FXMonkey]

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.

Any help anyone can offer is appreciated. Thank you

[rajeshgk]

When assigning the formula using xlwings in your code, the formula is being placed between curly braces {} because it is an array formula. To avoid this, you need to use the .formula property instead of .formula_array to assign the formula to the cell. Here's an updated version of your code:

# ...
# 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 = '=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()
# ...

By using .formula instead of .formula_array, the formula will be assigned correctly without the curly braces.

Larz60+ write Jun-06-2023, 11:35 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[FXMonkey]

Hi rajeshgk,

Thank you for the suggestion, I made the change to the code but this caused the formula to be populated in the cell as

=@IFERROR(SORT(FILTER('Fwd Data'!B:P,'Fwd Data'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")

Excel inserted the @ symbol which in order for the array to function correctly needs to be manually removed. I have the most recent version of excel so it isn't a compatibility issue. Do you know how I can resolve this at all?

Thanks in advance for any help.