A collection of Python scripts I use to convert various financial statments into ledger format.
- sudo apt-get install ledger python3 python3-pip pdfgrep
- pip3 install xlrd
Processes PDF paystubs downloaded from Ultipro and prints ledger transactions. Example usage:
python3 income.py 2015-06-05_1234.pdf
This script makes a posting for each non-zero amount in the earnings and deductions tables. By default it will name accounts "Income:Pre-tax:<earning>" and "Income:Deductions:<deduction>". There are exceptions for RRSP deductions which are special and have their own variable to set the account name. This script is easily maintainable to add new earning/deduction names as they are introduced. One notable decision was how I decided to handle paystubs regarding stocks see the later section regarding that.
Processes a Questrade excel file (My accounts > Account activity > Export to Excel) and prints ledger transactions to stdout. Example usage:
python3 questrade.py 1601_questrade.xlsx
The primary configuration is setting up the desired account names for Questrade accounts and the account you typically transfer funds from. I haven't found a good way to identify what account is providing funds.
Processes Morgan Stanley PDF documents for share release, sale, and autosale and prints ledger transactions. Example usage:
python3 stock.py 20150925_release.pdf
Each type of document has its own template and set of regex patterns. Share sale not in the autosale program is difficult and requires some manual calculations of capital gains (hopefully you don't have to do too many of those). All account names are configurable by in the string templates.
Dealing with stock data
Stock data comes from three sources: Morgan Stanley, Ultipro, and conversion rates (USD/CAD). I've decided to ignore the Ultipro statements and just use Morgan Stanley transactions in USD. This lets me decide how to handle reporting income in USD (daily/monthly/annual exchange rate). And means I don't have to reconcile a virtual transaction with virtual accounts (GSU offset).