bsb_excel_fill.py

bsb_excel_fill.py

import sys
import openpyxl
import os
import requests
import logging

# Configure logging
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s')

SHEET_FILE_NAME = 'target.xlsx'
SHEET_WORKBOOK_NAME = 'TRN'

BSB_COL = 'R'
BSB_ROW_START = 2
BSB_ROW_END = 1000

BANK_NAME_COL = 'S'
ADDRESS_COL = 'T'

searched_bsb = dict()


def extract_bsb_data(bsb_num):
    if bsb_num in searched_bsb:
        logging.info(f"Returning cached info for BSB: {bsb_num}")
        return searched_bsb[bsb_num]

    try:
        logging.info(f"Sending request for BSB: {bsb_num}")
        # send post request to https://searchmybank.com/NewZealand/PopulateBankBranchData
        data = {
            "draw": 1,
            "columns[0][data]": "nationalClearingCode",
            "columns[0][name]": "",
            "columns[0][searchable]": True,
            "columns[0][orderable]": False,
            "columns[0][search][value]": "",
            "columns[0][search][regex]": False,
            "columns[1][data]": "bankName",
            "columns[1][name]": "",
            "columns[1][searchable]": True,
            "columns[1][orderable]": False,
            "columns[1][search][value]": "",
            "columns[1][search][regex]": False,
            "columns[2][data]": "fullAddress",
            "columns[2][name]": "",
            "columns[2][searchable]": True,
            "columns[2][orderable]": False,
            "columns[2][search][value]": "",
            "columns[2][search][regex]": False,
            "columns[3][data]": "resourceId",
            "columns[3][name]": "",
            "columns[3][searchable]": True,
            "columns[3][orderable]": False,
            "columns[3][search][value]": "",
            "columns[3][search][regex]": False,
            "order[0][column]": 0,
            "order[0][dir]": "asc",
            "start": 0,
            "length": 50,
            "search[value]": "",
            "search[regex]": False,
            "BsbNumber": bsb_num,
            "Name": "",
            "City": ""
        }

        response = requests.post('https://searchmybank.com/NewZealand/PopulateBankBranchData', data=data)

        response_json = response.json()

        # check if response_json['data'] is empty
        if response_json['data'] is None or len(response_json['data']) == 0:
            return None

        bank_name = response_json['data'][0]['bankName']
        address = response_json['data'][0]['fullAddress']

        searched_bsb[bsb_num] = (bank_name, address)
        logging.info(f"BSB: {bsb_num} info retrieved and cached.")
        return bank_name, address
    except Exception as e:
        logging.error(f"Error retrieving BSB: {bsb_num}", exc_info=True)
        return None

if __name__ == '__main__':
    logging.info("Script started")

    try:
        wb = openpyxl.load_workbook(SHEET_FILE_NAME)
        ws = wb.worksheets[0]
        logging.info(f"{SHEET_FILE_NAME} loaded successfully.")
    except Exception as e:
        logging.error("Failed to load the workbook", exc_info=True)
        sys.exit(1)

    for row_index in range(BSB_ROW_START, BSB_ROW_END):
        bsb_cell = ws[BSB_COL + str(row_index)]
        bank_name_cell = ws[BANK_NAME_COL + str(row_index)]
        address_cell = ws[ADDRESS_COL + str(row_index)]

        if bank_name_cell.value is not None and address_cell.value is not None:
            logging.debug(f"Row {row_index}: Bank name and address already exist. Skipping.")
            continue

        bsb_num = bsb_cell.value
        if bsb_num is None:
            logging.debug(f"Row {row_index}: BSB number is None. Skipping.")
            continue

        info = extract_bsb_data(bsb_num)
        if info is None:
            logging.warning(f"Row {row_index}: No information found for BSB {bsb_num}")
            continue

        bank_name, address = info
        bank_name_cell.value = bank_name
        address_cell.value = address

        logging.info(f"Row {row_index}: Bank name and address updated for BSB {bsb_num}")

    try:
        wb.save(SHEET_FILE_NAME)
        logging.info(f"{SHEET_FILE_NAME} saved successfully.")
    except Exception as e:
        logging.error("Failed to save the workbook", exc_info=True)

    logging.info("Done")