Ledger is a powerful command-line plain-text double-entry bookkeeping system, with the capacity to import transactions from CSV files. In America, many banks provide APIs to access transaction feeds, which can be then directly imported into Ledger. However, in Australia, this is not the case.

Some commercial bookkeeping software, such as Xero and MYOB, have worked with banks to implement proprietary bank feeds, allowing transactions to be directly streamed to Xero/MYOB. To get transaction data into Ledger in a similarly automated way, though, I need to manually log into my bank's website, click a bunch of menu items, choose the relevant date range, export the transactions to a file, and then finally run that file through a converter for Ledger.

I would like to automate this process, but this is hampered by a few considerations:

  1. I store my passwords securely in the KeePass2 password manager, and so they are not directly accessible by a script. I very much want to avoid needing to store this password in plaintext, and while secure RPC plugins exist for KeePass2, they are quite technical and I would not want to screw it up.

  2. In addition to simply serving pages over HTTPS, my bank's login page also serves custom Javascript code hooking the keyboard input event on the password field to mangle (‘encrypt’) the password as it is being typed. Given the page is served over HTTPS, this seems to add little security, but does mean we cannot easily programmatically send login requests.

Enter Selenium, a programming framework for browser automation. Using Selenium, I can open an instance of Firefox which I can programmatically control. As it is largely a normal instance of Firefox, I can use the normal KeePass2 keyboard entry mechanism to enter the password, obviating both the need to access the password and reverse engineer the password encryption mechanism.

Once logged in, I can then simply automate the browser to download the transaction data, and convert it for use in Ledger.

The script

First some general imports:

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait

from datetime import datetime
from io import StringIO
import requests
import subprocess
import xml.etree.ElementTree as ET

Now I execute Ledger to find the date of the last not-pending transaction, to determine when I need to request transactions from:

# Get dates
proc = subprocess.run(['ledger', 'csv', '--csv-format', '%(date),%(state)\n', '^Assets:Current:CCE:Transaction$'], capture_output=True, encoding='utf-8')
trn_lines = [x.strip().split(',') for x in proc.stdout.splitlines()]
trn_lines = [x for x in trn_lines if x[1] == '0']
dt_last_trn = datetime.strptime(trn_lines[-1][0], '%Y-%m-%d')

Then I open a Firefox window using Selenium and navigate to the login page:

# Open Selenium driver
driver = webdriver.Firefox()
driver.get('https://ib.example.com.au/IB/Login.aspx')

At this point, I use KeePass2 to automatically input the password into the browser window and submit the form.

I wait for the next page to load (by waiting for an element found on the next page), then navigate to the transaction export page:

# Wait for user to log in
WebDriverWait(driver, 600).until(EC.element_to_be_clickable((By.CLASS_NAME, 'memberinformation')))
driver.find_element_by_id('btnConfirm').click()

# Wait for home page load
wait = WebDriverWait(driver, 10)
wait.until(EC.element_to_be_clickable((By.ID, 'Main_Balances_lblTitle')))

# Open transaction page
driver.get('https://ib.example.com.au/IB/ExportTransactions.aspx')
wait.until(EC.element_to_be_clickable((By.ID, 'txtFrom')))

I can't directly submit this form, because Selenium does not allow hooking the browser file download mechanism. Instead, I scrape the data from the form (e.g. the CSRF token) so I can prepare a request myself:

# Prepare payload
payload = {
	'__EVENTTARGET': 'ctl00$Main$ExportTransactions$btnDownload',
	'__EVENTARGUMENT': '',
	'__VIEWSTATE': driver.find_element_by_id('__VIEWSTATE').get_attribute('value'),
	'__VIEWSTATEGENERATOR': driver.find_element_by_id('__VIEWSTATEGENERATOR').get_attribute('value'),
	'__VIEWSTATEENCRYPTED': '',
	'__EVENTVALIDATION': driver.find_element_by_id('__EVENTVALIDATION').get_attribute('value'),
	'ctl00$nonce': '',
	'ctl00$Menu$Menu$menuOrientation': 'horizontal',
	'ctl00$Main$ExportTransactions$txtFrom': dt_last_trn.strftime('%d/%m/%Y'),
	'ctl00$Main$ExportTransactions$txtTo': datetime.now().strftime('%d/%m/%Y'),
	'ctl00$Main$ExportTransactions$dplFilter': '0',
	'ctl00$Main$ExportTransactions$dplAccounts': '0',
	'ctl00$Main$ExportTransactions$dplFormat': 'ofx',
}

Similarly, I also get the cookies from the browser so I can include them with my scripted request:

# Load cookies into Requests
cookies = driver.get_cookies()
session = requests.Session()
for cookie in cookies:
	session.cookies.set(cookie['name'], cookie['value'])

driver.close()

Now I can construct a request to the transaction export URL using the information above:

# Get transactions
response = session.post('https://ib.example.com.au/IB/ExportTransactions.aspx', data=payload, headers={'User-Agent': driver.execute_script('return navigator.userAgent;'), 'Referer': 'https://ib.example.com.au/IB/ExportTransactions.aspx'})
ofx_str = response.content.decode('utf-8')

Finally, I parse the OFX data and pass it to Ledger:

# Parse OFX
ofxinput = StringIO(ofx_str)
tree = ET.parse(ofxinput)
root = tree.getroot()

for transaction in root.find('BANKMSGSRSV1').find('STMTTRNRS').find('STMTRS').find('BANKTRANLIST').findall('STMTTRN'):
	date = transaction.find('DTPOSTED').text
	date = date[0:4] + '-' + date[4:6] + '-' + date[6:8]
	description = transaction.find('NAME').text
	amount = transaction.find('TRNAMT').text
	amount = ('$' + amount) if not amount.startswith('-') else ('-$' + amount[1:])
	
	ledger_args = ['ledger', 'convert', '--invert', '--rich-data', '--account', 'Assets:Current:CCE:Transaction', '/dev/stdin']
	proc = subprocess.run(ledger_args, input='date,description,amount\n{},{},{},{},{}\n'.format(date, description, amount), stdout=subprocess.PIPE, encoding='utf-8')
	
	result = proc.stdout.replace(' * ', ' ')
	if result:
		print(result)

This approach is fairly convenient, as I can also use the same script to perform other housekeeping duties, like scraping the list of uncleared Visa authorisations to also import into Ledger, and opening a pre-filled transfer form to keep funds available in my transaction account.