# Create the venv: python -m venv venv ... (Yes) # Activate the venv: .\venv\Scripts\activate # Run the requirements: pip install -r requirements.txt # Set the variables (remember…C:\... Becomes …C:/ … or use r'C:\...' without final backslash) # Run the program # Check the output # 1/ Import python libraries import polars as PI_POLARS import regex as PI_RE import numpy as PI_NUMPY # 2/ Import shared programs from Z_SHARED_FUNCTIONS.FC_IMPORT import FC_IMPORT_TEXT from Z_SHARED_FUNCTIONS.FC_EXPORT import FC_EXPORT_EXCEL from Z_SHARED_FUNCTIONS.FC_BUILD_TFIDF_AND_COSINE_BEST_MATCH import FC_BUILD_TFIDF_AND_COSINE_BEST_MATCH from Z_SHARED_FUNCTIONS.FC_PREPROCESS_TEXT import FC_PREPROCESS_TEXT # 3/ Set variables ZV_ST_SOURCES_FOLDER = r'C:\300FMASTERCLASS\06_PYTHON_MEETING_MC202601\11_PYTHON_MEETING_11\PYTHON_PROGRAM\01_SOURCES' ZV_ST_RESULTS_FOLDER = r'C:\300FMASTERCLASS\06_PYTHON_MEETING_MC202601\11_PYTHON_MEETING_11\PYTHON_PROGRAM\03_RESULTS' ZV_ST_NAME_BSIK_FILE = '20251110_153943_1_BSIK.csv' ZV_ST_NAME_BSAK_FILE = '20251110_153943_1_BSAK.csv' ZV_ST_NAME_T001_FILE = '20251025_170112_1_T001.csv' ZV_ST_NAME_BKPF_FILE = '20250911_172412_1_BKPF.csv' ZV_ST_NAME_BSEG_FILE = '20250910_113032_1_BSEG.csv' ZV_ST_NAME_SKAT_FILE = '20240513_152943_1_SKAT.csv' ZV_ST_NAME_SKA1_FILE = '20240513_152943_1_SKA1.csv' ZV_ST_NAME_SKB1_FILE = '20240513_152943_1_SKB1.csv' ZV_ST_NAME_T003T_FILE = '20240510_152615_1_T003T.csv' ZV_ST_NAME_KEYWORD_FILE = 'AM_KEY_WORDS.txt' ZV_ST_NAME_T706B5_FILE = '20260325_103759_1_T706B5.csv' def HR12_MISSCLASSIFIED_EXPENSES(): # 4/ Import the files ZV_LI_NUM_COLS = [ 'BSAIK_DMBTR' ] ZV_DF_BSIK = FC_IMPORT_TEXT(ZV_ST_NAME_BSIK_FILE, ZV_ST_SOURCES_FOLDER, '|','BSAIK_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [ 'BSAIK_DMBTR' ] ZV_DF_BSAK = FC_IMPORT_TEXT(ZV_ST_NAME_BSAK_FILE, ZV_ST_SOURCES_FOLDER, '|','BSAIK_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_T001 = FC_IMPORT_TEXT(ZV_ST_NAME_T001_FILE, ZV_ST_SOURCES_FOLDER, '|','T001_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_BKPF = FC_IMPORT_TEXT(ZV_ST_NAME_BKPF_FILE, ZV_ST_SOURCES_FOLDER, '|','BKPF_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [ 'BSEG_DMBTR' ] ZV_DF_BSEG = FC_IMPORT_TEXT(ZV_ST_NAME_BSEG_FILE, ZV_ST_SOURCES_FOLDER, '\t','BSEG_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_SKAT = FC_IMPORT_TEXT(ZV_ST_NAME_SKAT_FILE, ZV_ST_SOURCES_FOLDER, '\t','SKAT_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_SKA1 = FC_IMPORT_TEXT(ZV_ST_NAME_SKA1_FILE, ZV_ST_SOURCES_FOLDER, '\t','SKA1_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_SKB1 = FC_IMPORT_TEXT(ZV_ST_NAME_SKB1_FILE, ZV_ST_SOURCES_FOLDER, '\t','SKB1_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_T003T = FC_IMPORT_TEXT(ZV_ST_NAME_T003T_FILE, ZV_ST_SOURCES_FOLDER, '\t','T003T_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_AM_KEYWORDS = FC_IMPORT_TEXT(ZV_ST_NAME_KEYWORD_FILE, ZV_ST_SOURCES_FOLDER, '\t','AMKW_',ZV_LI_NUM_COLS) ZV_LI_NUM_COLS = [] ZV_DF_T706B5 = FC_IMPORT_TEXT(ZV_ST_NAME_T706B5_FILE, ZV_ST_SOURCES_FOLDER, '|','T706B5_',ZV_LI_NUM_COLS) # 5/ Prepare the data # 5.1/ Concatenate the BSIK and BSAK into one table ZV_DF_BSAIK = ( PI_POLARS.concat( [ ZV_DF_BSIK, ZV_DF_BSAK ] ) ) # 5.2/ Join the general ledger detail and header together ZV_DF_BSEGBKPF = ( ZV_DF_BSEG .join( ( ZV_DF_BKPF .filter( PI_POLARS.col('BKPF_BSTAT')=='' ) ), left_on=[ 'BSEG_BUKRS', 'BSEG_GJAHR', 'BSEG_BELNR' ], right_on = [ 'BKPF_BUKRS', 'BKPF_GJAHR', 'BKPF_BELNR' ], how='inner', coalesce=False ) ) # 5.2.1/ Add the chart of accounts to the general ledger # 5.2.2/ Add the general ledger account descriptions # 5.2.3/ Add the document type descriptions ZV_DF_BSEGBKPF = ( ZV_DF_BSEGBKPF .join( ZV_DF_T001, left_on = 'BKPF_BUKRS', right_on = 'T001_BUKRS', how = 'inner' ) .join( ( ZV_DF_SKAT .filter( (PI_POLARS.col('SKAT_SPRAS') == 'E') | (PI_POLARS.col('SKAT_SPRAS') == 'EN') ) ), left_on = [ 'T001_KTOPL', 'BSEG_HKONT' ], right_on = [ 'SKAT_KTOPL', 'SKAT_SAKNR' ], how = 'inner' ) .join( ZV_DF_SKA1, left_on=[ 'T001_KTOPL', 'BSEG_HKONT' ], right_on=[ 'SKA1_KTOPL', 'SKA1_SAKNR' ], how='inner' ) .join( ZV_DF_SKB1, left_on=[ 'BSEG_BUKRS', 'BSEG_HKONT' ], right_on=[ 'SKB1_BUKRS', 'SKB1_SAKNR' ], how='inner' ) .join( ( ZV_DF_T003T .filter( (PI_POLARS.col('T003T_SPRAS') == 'E') | (PI_POLARS.col('T003T_SPRAS') == 'EN') ) ), left_on = 'BKPF_BLART', right_on = 'T003T_BLART', how = 'inner' ) ) # 6/ Re-arrange the general ledger to one line with accounts listed on debit and credit ZV_DF_BSEGBKPF_ACC_SCHEMES = ( ZV_DF_BSEGBKPF .with_columns( PI_POLARS.concat_str( [ PI_POLARS.col('BSEG_HKONT') .str.strip_chars(), PI_POLARS.col('SKAT_TXT20') .str.strip_chars() ], separator='-' ) .alias('ZF_BSEGHKONT_SKATTXT20'), PI_POLARS.concat_str( [ PI_POLARS.col('BKPF_BLART') .str.strip_chars(), PI_POLARS.col('T003T_LTEXT') .str.strip_chars() ], separator='-' ) .alias('ZF_BKPFBLART_T003TLTEXT'), ) .select( [ 'BSEG_BUKRS', 'BSEG_GJAHR', 'BSEG_BELNR', 'BSEG_SHKZG', 'BSEG_DMBTR', 'ZF_BSEGHKONT_SKATTXT20', 'ZF_BKPFBLART_T003TLTEXT', 'SKA1_GVTYP', 'SKB1_XGKON', 'SKB1_HBKID', 'BSEG_KOART', 'BSEG_KTOSL', 'BKPF_BKTXT' ] ) .group_by( [ 'BSEG_BUKRS', 'BSEG_GJAHR', 'BSEG_BELNR', 'BKPF_BKTXT' ] ) .agg( [ PI_POLARS.when( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .then( PI_POLARS.col('BSEG_DMBTR') ) .otherwise( PI_POLARS.lit(0) ) .sum() .alias('ZF_BSEG_DMBTR'), PI_POLARS.col('ZF_BSEGHKONT_SKATTXT20') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ), descending=True ) .head(10) .str.join('_') .alias('ZF_BSEG_HKONTSKAT_DEBIT'), PI_POLARS.col('ZF_BSEGHKONT_SKATTXT20') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ), descending=True ) .head(10) .str.join('_') .alias('ZF_BSEG_HKONTSKAT_CREDIT'), PI_POLARS.col('SKA1_GVTYP') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ), descending=True ) .head(10) .str.join('_') .alias('ZF_SKA1_GVTYP_DEBIT'), PI_POLARS.col('SKA1_GVTYP') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ), descending=True ) .head(10) .str.join('_') .alias('ZF_SKA1_GVTYP_CREDIT'), PI_POLARS.col('SKB1_HBKID') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ), descending=True ) .head(10) .str.join('_') .alias('ZF_SKB1_HBKID_DEBIT'), PI_POLARS.col('SKB1_HBKID') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ), descending=True ) .head(10) .str.join('_') .alias('ZF_SKB1_HBKID_CREDIT'), PI_POLARS.col('SKB1_XGKON') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ), descending=True ) .head(10) .str.join('_') .alias('ZF_SKB1_XGKON_DEBIT'), PI_POLARS.col('SKB1_XGKON') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ), descending=True ) .head(10) .str.join('_') .alias('ZF_SKB1_XGKON_CREDIT'), PI_POLARS.col('BSEG_KOART') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ), descending=True ) .head(10) .str.join('_') .alias('ZF_BSEG_KOART_DEBIT'), PI_POLARS.col('BSEG_KOART') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ), descending=True ) .head(10) .str.join('_') .alias('ZF_BSEG_KOART_CREDIT'), PI_POLARS.col('BSEG_KTOSL') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'S' ), descending=True ) .head(10) .str.join('_') .alias('ZF_BSEG_KTOSL_DEBIT'), PI_POLARS.col('BSEG_KTOSL') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ) .sort_by( PI_POLARS.col('BSEG_DMBTR') .filter( PI_POLARS.col('BSEG_SHKZG') == 'H' ), descending=True ) .head(10) .str.join('_') .alias('ZF_BSEG_KTOSL_CREDIT') ] ) ) print(f'ZV_DF_BSEGBKPF_ACC_SCHEMES: {ZV_DF_BSEGBKPF_ACC_SCHEMES.head(10)}') # 7/ Add the accounting schemes to supplier movements ZV_DF_BSAIK = ( ZV_DF_BSAIK .join( ZV_DF_BSEGBKPF_ACC_SCHEMES, left_on=[ 'BSAIK_BUKRS', 'BSAIK_GJAHR', 'BSAIK_BELNR' ], right_on=[ 'BSEG_BUKRS', 'BSEG_GJAHR', 'BSEG_BELNR' ], how='inner' ) ) # 8/ Bucket definition ZV_DF_BSAIK = ( ZV_DF_BSAIK .with_columns( [ PI_POLARS.when( ( ( PI_POLARS.col('ZF_BSEG_KOART_DEBIT') .str.split('_') .list.first() ) == 'S' ) & ( ( ( PI_POLARS.col('ZF_SKA1_GVTYP_DEBIT') .str.split('_') .list.first() .str.strip_chars() ) != '' ) | ( ( PI_POLARS.col('ZF_BSEG_KTOSL_DEBIT') .str.split('_') .list.first() ) == 'WRX' ) ) & ( ( PI_POLARS.col('ZF_BSEG_KOART_CREDIT') .str.split('_') .list.first() ) == 'K' ) ) .then( PI_POLARS.lit('Supplier invoice') ) .when( ( ( PI_POLARS.col('ZF_BSEG_KOART_DEBIT') .str.split('_') .list.first() .str.strip_chars() ) == 'K' ) & ( ( ( PI_POLARS.col('ZF_SKA1_GVTYP_CREDIT') .str.split('_') .list.first() .str.strip_chars() ) == '' ) & ( ( ( PI_POLARS.col('ZF_SKB1_HBKID_CREDIT') .str.split('_') .list.first() .str.strip_chars() ) != '' ) | ( ( PI_POLARS.col('ZF_SKB1_XGKON_CREDIT') .str.split('_') .list.first() .str.strip_chars() ) != '' ) ) ) ) .then( PI_POLARS.lit('Supplier payment') ) .otherwise( PI_POLARS.lit('Other supplier transaction') ) .alias('ZF_BSAIK_BUCKET') ] ) ) # 9/ Get the list of supplier invoices ZV_DF_BSAIK_INVOICES = ( ZV_DF_BSAIK .filter( PI_POLARS.col('ZF_BSAIK_BUCKET')=='Supplier invoice' ) .with_columns( PI_POLARS.concat_str( [ PI_POLARS.col('BSAIK_SGTXT') .fill_null('') .str.strip_chars(), PI_POLARS.col('BKPF_BKTXT') .fill_null('') .str.strip_chars(), ], separator = ' ' ) .alias('ZF_BSAIKSGTXT_BKPFBKTXT') ) ) # 10/ List of key words ZV_LI_KEY_WORDS = [ str(k) .strip() for k in ZV_DF_AM_KEYWORDS['AMKW_KEY_WORD'] if k is not None and str(k).strip()!='' ] if not ZV_LI_KEY_WORDS: ZV_RE_FILTER_PATTERN = r'(?!)' else: ZV_RE_KEY_WORDS = '|'.join(PI_RE.escape(k) for k in ZV_LI_KEY_WORDS) ZV_RE_FILTER_PATTERN = f'(?i)(?:^|[^A-Za-z0-9_])(?:{ZV_RE_KEY_WORDS})(?:$|[^A-Za-z0-9_])' # 11/ Invoices containing the regex pattern ZV_DF_BSAIK_INVOICES_TANDE = ( ZV_DF_BSAIK_INVOICES .filter( ( ( PI_POLARS.col('BSAIK_SGTXT') .str.contains(ZV_RE_FILTER_PATTERN, literal=False) ) | ( PI_POLARS.col('BKPF_BKTXT') .str.contains(ZV_RE_FILTER_PATTERN, literal=False) ) ) ) ) # ---- Check using Cosine and list of expense types # 12/ List of words from expense categories ZV_LI_T706B5_SPTXT = ( ZV_DF_T706B5 .filter( (PI_POLARS.col('T706B5_SPRAS') == 'E') | (PI_POLARS.col('T706B5_SPRAS') == 'EN') ) .unique(subset='T706B5_SPTXT') .get_column('T706B5_SPTXT') .to_list() ) # 13/ List of words from supplier invoices ZV_LI_BSAIKSGTXT_BKPFBKTXT = ( ( ZV_DF_BSAIK_INVOICES .select( [ 'ZF_BSAIKSGTXT_BKPFBKTXT' ] ) .filter( ( ( PI_POLARS.col('ZF_BSAIKSGTXT_BKPFBKTXT') .fill_null('') .str.strip_chars() != '' ) ) ) .unique() .get_column('ZF_BSAIKSGTXT_BKPFBKTXT') .to_list() ) ) # 14/ Process text to new lists ZV_LI_T706B5_SPTXT_PREP = [] for ZV_ST_TXT in ZV_LI_T706B5_SPTXT: ZV_LI_T706B5_SPTXT_PREP.append( FC_PREPROCESS_TEXT(ZV_ST_TXT) ) ZV_LI_BSAIKSGTXT_BKPFBKTXT_PREP = [] for ZV_ST_TXT in ZV_LI_BSAIKSGTXT_BKPFBKTXT: ZV_LI_BSAIKSGTXT_BKPFBKTXT_PREP.append( FC_PREPROCESS_TEXT(ZV_ST_TXT) ) # 15/ Best category match ZV_AR_BEST_COS = PI_NUMPY.array([], dtype=float) ZV_AR_BEST_IDX = PI_NUMPY.array([], dtype=int) if ( len(ZV_LI_T706B5_SPTXT_PREP) > 0 and len(ZV_LI_BSAIKSGTXT_BKPFBKTXT_PREP) > 0 ): ZV_AR_BEST_COS, ZV_AR_BEST_IDX = FC_BUILD_TFIDF_AND_COSINE_BEST_MATCH( PI_LI_CATEGORIES=ZV_LI_T706B5_SPTXT_PREP, PI_LI_TEXTS=ZV_LI_BSAIKSGTXT_BKPFBKTXT_PREP, PI_ST_NGRAM='1,2', PI_I_MIN_DF=1 ) # 16/ Best match into list ZV_LI_T706B5_SPTXT_BEST_MATCH = [] for ZV_I in range(0, len(ZV_AR_BEST_IDX)): ZV_LI_T706B5_SPTXT_BEST_MATCH.append( ZV_LI_T706B5_SPTXT[int(ZV_AR_BEST_IDX[ZV_I])] ) # 17/ Lookup table by unique invoice text ZV_DF_BSAIK_TEXTS_MATCH = PI_POLARS.DataFrame( { 'ZF_BSAIKSGTXT_BKPFBKTXT': ZV_LI_BSAIKSGTXT_BKPFBKTXT, 'ZF_T706B5_SPTXT_BEST_MATCH': ZV_LI_T706B5_SPTXT_BEST_MATCH, 'ZF_T706B5_BSAIK_COSINE': ZV_AR_BEST_COS.tolist() } ).with_columns( ( PI_POLARS.col('ZF_T706B5_BSAIK_COSINE') < 0.35 ) .alias('ZF_BO_SGTXTBKTXT_NOTIN_T706B5SPTXT') ) # 18/ Join back to main table ZV_DF_BSAIK_INVOICES = ( ZV_DF_BSAIK_INVOICES .join( ZV_DF_BSAIK_TEXTS_MATCH, on=[ 'ZF_BSAIKSGTXT_BKPFBKTXT' ], how='left' ) ) FC_EXPORT_EXCEL( ZV_DF_BSAIK_INVOICES, ZV_ST_RESULTS_FOLDER, 'ZV_DF_BSAIK_INVOICES.xlsx' ) FC_EXPORT_EXCEL( ZV_DF_BSAIK_INVOICES_TANDE, ZV_ST_RESULTS_FOLDER, 'ZV_DF_BSAIK_INVOICES_TANDE.xlsx' ) if __name__ == '__main__': HR12_MISSCLASSIFIED_EXPENSES()