1#!/usr/bin/env python3
2# coding: utf-8
3
4# update/nz_banks.py - script to download Bank list from Bank Branch Register
5#
6# Copyright (C) 2019 Arthur de Jong
7#
8# This library is free software; you can redistribute it and/or
9# modify it under the terms of the GNU Lesser General Public
10# License as published by the Free Software Foundation; either
11# version 2.1 of the License, or (at your option) any later version.
12#
13# This library is distributed in the hope that it will be useful,
14# but WITHOUT ANY WARRANTY; without even the implied warranty of
15# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
16# Lesser General Public License for more details.
17#
18# You should have received a copy of the GNU Lesser General Public
19# License along with this library; if not, write to the Free Software
20# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
21# 02110-1301 USA
22
23"""This script downloads the list of banks with bank codes as used in the
24New Zealand bank account numbers."""
25
26import re
27from collections import OrderedDict, defaultdict
28
29import requests
30import xlrd
31
32
33# The page that contains a link to the latest XLS version of the codes.
34download_url = 'https://www.paymentsnz.co.nz/resources/industry-registers/bank-branch-register/download/xls/'
35
36
37def get_values(sheet):
38    """Return rows from the worksheet as a dict per row."""
39    rows = sheet.get_rows()
40    # the first row has column names
41    columns = [column.value.lower().replace(' ', '_') for column in next(rows)]
42    # go over rows with values
43    for row in rows:
44        yield dict(zip(columns, [column.value for column in row]))
45
46
47def branch_list(branches):
48    """Return a compact representation of a list of branch numbers."""
49    branches = sorted(int(b) for b in branches)
50    first = None
51    prev = None
52    res = ''
53    for branch in branches:
54        if first is not None and branch == prev + 1:
55            # this branch is consecutive to the previous: make a range
56            if prev > first:
57                res = res[:-5]
58            res += '-%04d' % branch
59            prev = branch
60        else:
61            # this is a new branch, add a new one to the list
62            res += ',%04d' % branch
63            first = prev = branch
64    return res.lstrip(',')
65
66
67if __name__ == '__main__':
68    # parse the download as an XLS
69    response = requests.get(download_url)
70    response.raise_for_status()
71    content_disposition = response.headers.get('content-disposition', '')
72    filename = re.findall(r'filename=?(.+)"?', content_disposition)[0].strip('"')
73    workbook = xlrd.open_workbook(file_contents=response.content)
74    sheet = workbook.sheet_by_index(0)
75    # print header
76    print('# generated from %s downloaded from ' % filename)
77    print('# %s' % download_url)
78    # build banks list from spreadsheet
79    banks = defaultdict(dict)
80    for line in get_values(sheet):
81        banks[line['bank_number']]['bank'] = line['bank_name']
82        branches = banks[line['bank_number']].setdefault('branches', OrderedDict())
83        branches.setdefault((line['branch_information'], line['bic']), list()).append(line['branch_number'])
84    # output bank information
85    for bank_number in sorted(banks.keys()):
86        bank = banks[bank_number]
87        print('%s bank="%s"' % (bank_number, bank['bank']))
88        for (branch, bic), branch_numbers in bank['branches'].items():
89            print(' %s%s branch="%s"' % (
90                branch_list(branch_numbers), ' bic="%s"' % bic if bic else '', branch))
91