trial_balance_parser.py

from openpyxl import load_workbook
import json

structure = [
    {'id': '1', 'name': 'Equipment', 'category': 'Assets'},
    {'id': '122', 'name': 'Fixed assets', 'category': 'Assets'},
    {'id': '2', 'name': 'Current assets', 'category': 'Assets'},
    {'id': '201', 'name': 'Supplies', 'category': 'Assets'},
    {'id': '3', 'name': 'Equity', 'category': 'Equity'},
]

def get_account_info(account_id):
    if isinstance(account_id, int):
        account_id = str(account_id)
    account_found = None
    for account in structure:
        if account_id.startswith(account['id']):
            account_found = account

    if account_found == None:
        return { 'name': 'Other', 'category': 'Other' }
    else:
        return account_found

def print_file_info(ws):
    period_info = ws['A8'].value.split(': ')[1]
    period = period_info.split('..')
    company = ws['A11'].value
    print(f"Reading trial/balance report of '{company}' for period {period[0]} to {period[1]}")


def extract_row_info(row):
    account_id = row[0].value
    account_info = get_account_info(account_id)
    
    account = {
        'category': account_info['category'],
        'name': account_info['category'],
        'account_id': account_id,
        'description': row[1].value,
        'period_info': {
            'debit':  row[3].value,
            'credit':  row[5].value,
        },
        'acc_info': {
            'debit':  row[9].value,
            'credit':  row[11].value,
        }
    }
    return account

def extract_trial_balance_info(ws):
    accounts = (extract_row_info(row) for row in ws.iter_rows(min_row=23, max_col=12, max_row=131))

    return list(accounts)


def main():
    wb = load_workbook(filename="./tmp/trial-balance-badi.xlsm", read_only=True, data_only=True)
    ws = wb['Hoja1']
    print_file_info(ws)
    accounts = extract_trial_balance_info(ws)
    
    print(accounts)


if __name__ == "__main__":
    main()