How can my array-formula be improved?

The following image is part of a spreadsheet I build for keeping track of my personal finances.

All my transactions are listed in one worksheet. The part you see is a table that sums up all expenses for each month of a given year and different categories. The table is build using a single formula in the top left cell.

Further below you can see the formula and additional commentary that explains the formula.

My formula scrapes excels limit of 8192 characters per formula and I had to rewrite sections multiple times to stay in the limit. For any future projects this might become a problem.

How can I improve this script?

The MAP-function seemed promising but I couldn’t figure out how to make it work for my needs.

What would be a better way to achieve this altogether?

A Power Query connection would probably work but I‘d have to manually hit refresh whenever I‘d add new data.

What would a professional solution look like?

Using office 2024, the PY-Function isn’t available for me. Being able to use python in an excel-cell seems game-changing to me. Would that be a pretty good solution?

The result

The formula:

=LET( comt1; "External variables"; year; G28; source_table; table_transactions; categories; G4:G11; comt2; "month-variables"; january; EDATE(year; 0); february; EDATE(year; 1); march; EDATE(year; 2); april; EDATE(year; 3); mayo; EDATE(year; 4); june; EDATE(year; 5); july; EDATE(year; 6); august; EDATE(year; 7); september; EDATE(year; 8); october; EDATE(year; 9); november; EDATE(year; 10); december; EDATE(year; 11); comt3; "Individual categroy-variables from external range"; catg1; INDEX(categories; 1); catg2; INDEX(categories; 2); catg3; INDEX(categories; 3); catg4; INDEX(categories; 4); catg5; INDEX(categories; 5); catg6; INDEX(categories; 6); catg7; INDEX(categories; 7); catg8; INDEX(categories; 8); comt4a; "Header row from months with formatting"; header_row; HSTACK( TEXT(january; "[$-de-DE]MMMM;@"); TEXT(february; "[$-de-DE]MMMM;@"); TEXT(march; "[$-de-DE]MMMM;@"); TEXT(april; "[$-de-DE]MMMM;@"); TEXT(mayo; "[$-de-DE]MMMM;@"); TEXT(june; "[$-de-DE]MMMM;@"); TEXT(july; "[$-de-DE]MMMM;@"); TEXT(august; "[$-de-DE]MMMM;@"); TEXT(september; "[$-de-DE]MMMM;@"); TEXT(october; "[$-de-DE]MMMM;@"); TEXT(november; "[$-de-DE]MMMM;@"); TEXT(december; "[$-de-DE]MMMM;@"); "Total" ); comt4b; "Vertical header-col, that will later be displayed on the left of the table"; header_col; VSTACK( TEXT(year; "[$-de-DE]JJJJ;@"); catg1; catg2; catg3; catg4; catg5; catg6; catg7; catg8 ); comt6; "Data for each cell"; comt6a; "Defining function to determin the value for each cell"; func_filter_elements; LAMBDA( table; month; catg; LET( data_month; FILTER( table; (YEAR(INDEX(table;; 1)) = YEAR(month)) * (MONTH(INDEX(table;; 1)) = MONTH(month)) ); comt1; "Finden aller Datensätze mit einer bestimmten Kategorie im jeweiligen Datumsbereich"; filter; FILTER( data_month; INDEX(data_month;; 3) = catg; ); comt2; "Davon auswählen der WERT-Spalte"; choosecols; CHOOSECOLS( filter; 2 ); comt3; "Summe aller Werte dieser Spalte bilden"; sum; sum( choosecols ); comt4; "Wenn es in einem jeweiligen Datumbereich keine Werte gibt -also nicht nur das FILTER-Ergebnis leer ist sondern schon das Array, das durchsucht werden soll leer ist- resultiert die FILTER-Operation in einem #CALC-Error"; comt4a; "#CALC-Error handling"; comt4b; "Wenn kein Fehler -> sum"; comt4c; "Wenn Fehler -> Prüft, ob Fehler = #CALC ist (weil die Filter-Operationen keine Werte geliefert haben)"; comt4d; "Wenn Fehler = #Calc -> Ausgabe leeres Feld"; comt4e; "Wenn anderer Fehler -> Ausgabe sum (was dann den Fehler ausgibt)"; handling; IFERROR( sum; IF( ERROR.TYPE(sum) = 14; 0; sum ) ); formatting; TEXT(handling; "0,00 ;[Red]-0,00 "); handling ) ); comt7; "Making rows by calling individual elements"; func_row_cats; LAMBDA( catg; HSTACK( func_filter_elements(source_table; january; catg); func_filter_elements(source_table; february; catg); func_filter_elements(source_table; march; catg); func_filter_elements(source_table; april; catg); func_filter_elements(source_table; mayo; catg); func_filter_elements(source_table; june; catg); func_filter_elements(source_table; july; catg); func_filter_elements(source_table; august; catg); func_filter_elements(source_table; september; catg); func_filter_elements(source_table; october; catg); func_filter_elements(source_table; november; catg); func_filter_elements(source_table; december; catg) ) ); row_cat1; func_row_cats(catg1); row_cat2; func_row_cats(catg2); row_cat3; func_row_cats(catg3); row_cat4; func_row_cats(catg4); row_cat5; func_row_cats(catg5); row_cat6; func_row_cats(catg6); comt8; "Combining values for every month-category combination to an array"; comt8a; "Array only consists of number values - no headers, no totals"; data1; VSTACK( row_cat1; row_cat2; row_cat3; row_cat4; row_cat5; row_cat6 ); comt9; "Total-column that will be displayed on the far right of the table"; comt9a; "Consists only of sixs rows - thus no total-row-values included"; total_col; VSTACK( SUM(CHOOSEROWS(data1; 1)); SUM(CHOOSEROWS(data1; 2)); SUM(CHOOSEROWS(data1; 3)); SUM(CHOOSEROWS(data1; 4)); SUM(CHOOSEROWS(data1; 5)); SUM(CHOOSEROWS(data1; 6)) ); data2; HSTACK( data1; total_col ); comt10; "Total-row"; total_row; HSTACK( SUM(CHOOSECOLS(data2; 1)); SUM(CHOOSECOLS(data2; 2)); SUM(CHOOSECOLS(data2; 3)); SUM(CHOOSECOLS(data2; 4)); SUM(CHOOSECOLS(data2; 5)); SUM(CHOOSECOLS(data2; 6)); SUM(CHOOSECOLS(data2; 7)); SUM(CHOOSECOLS(data2; 8)); SUM(CHOOSECOLS(data2; 9)); SUM(CHOOSECOLS(data2; 10)); SUM(CHOOSECOLS(data2; 11)); SUM(CHOOSECOLS(data2; 12)); SUM(CHOOSECOLS(data2; 13)) ); data3; VSTACK( data2; total_row ); comt11; "Row total excl Inv"; func_total_inv_row; LAMBDA( col_nr; SUM(CHOOSEROWS(CHOOSECOLS(data3; col_nr); 7) - CHOOSEROWS(CHOOSECOLS(data3; col_nr); 2)) ); total_inv_row; HSTACK( func_total_inv_row(1); func_total_inv_row(2); func_total_inv_row(3); func_total_inv_row(4); func_total_inv_row(5); func_total_inv_row(6); func_total_inv_row(7); func_total_inv_row(8); func_total_inv_row(9); func_total_inv_row(10); func_total_inv_row(11); func_total_inv_row(12); func_total_inv_row(13) ); data4; VSTACK( data3; total_inv_row ); comt12; "Appending header row and header column"; data5; VSTACK( header_row; data4 ); data6; HSTACK( header_col; data5 ); result; data6; result) 

Commentary of the formula