Bundesbank bank data to sqlite

The German Bundesbank releases every quarter of a year a list of all banks in Germany. We build a Django based api website for this data a while ago: https://github.com/opendata-stuttgart/banking-api. The site went offline after a while.

After experimenting a bit with Datasette I built a converter of the Bundesbank Excel file to sqlite.

The code is using sqlite-utils for the sqlite part and openpxl for the Excel part.

Main converting loop:

def convert(db, xlsx_path):
    wb = load_workbook(filename=xlsx_path, read_only=True)

    # use the first worksheet
    ws = wb[wb.sheetnames[0]]

    translate_fieldnames = {
        'Bezeichnung': 'name',
        'Bank-leitzahl': 'blz',
        'BIC': 'bic',
        'PLZ': 'zipcode',
        'Ort': 'city',
        'Kurzbezeichnung': 'short_description',
        'PAN': 'pan',
        'Prüfziffer-berechnungs-methode': 'check_calculation_method',
        'Datensatz-nummer': 'dataset_number',
        'Merkmal': 'merkmal',
        'Änderungs-kennzeichen': 'change_type',
        'Bankleitzahl-löschung': 'is_deletion',
        'Nachfolge-Bankleitzahl': 'following_blz',

    # translate tablenames to English names
    field_names = []
    for item in list(ws.iter_rows(min_row=1, max_row=1, values_only=True))[0]:

    for row in ws.iter_rows(min_row=2, values_only=True):
            record=dict(zip(field_names, row)), pk="blz", replace=True)

Especially the db["tablename"].insert() from sqlite-utils makes adding data to a sqlite databases really easy.

The full code is on Github: https://github.com/mfa/bundesbank-to-sqlite.

Bicycle: repair headset

My bike cracked a little when I steered. So it was time for headset (German: Steuersatz) maintenance.

First I watched this video (in German):

Then I disassembled the bike and the bearing was a bit rusty:


Cleaning and some grease should do. The grease I used was the only one we had:


After greasing and partially assembled:


show image badge in org-mode

I started to use org-mode for readme files. One of the issues was: how to show image badges, i.e. for travis-ci. For later reference this is how it is done:


The trick is the file: in front of the url.