Over the last few weeks I’ve been migrating my historical financial data, previously maintained as a CSV file, to Beancount, a plain-text ledger format. In my old system, I had detailed history of spending activity but had not been great about maintaining all the investment transactions so I wanted to fill that hole as I moved to Beancount.
To that end, I wanted a good way to import trade history from Fidelity. While they do support a CSV export for the last 5 years, I’d only been saving the PDF statements over time so didn’t have an easy-to-parse format for the full history. But they do have a feature on their website that shows the full trade history for open positions. When you click on a ticker in the Positions tab for an account, it shows the history of trades for that holding.
For closed positions, Fidelity uses a different format which includes the corresponding sell trades in the same row, along with the date sold and the sale proceeds, so the script has logic to parse either format.
I tried copying and pasting that data to a text file and then wrote a Python script to parse the resulting text and convert it to Beancount’s transaction format. While it worked fine, it was clumsy to copy the content from a browser, paste it into a text file, run the script and then place the results into the corresponding ledger file.
The Shorcuts app on macOS does makes it pretty easy to run an automation using the current selection, so I created a simple shortcut to send the selection to the Python script and place the output of the script on the clipboard so I can just paste it directly into the ledger file.
Below is a screen shot of the full shortcut (there aren’t any good ways I know of to show the source of a shortcut). You can also download the shortcut from iCloud.
The shortcut receives input from Quick Actions which you can see on the right that I’ve enabled via the Services menu as well as a keyboard shortcut. There are three additional pieces of information I need to properly create a Beancount transaction that are not present in the trade table: the ticker, the account owner and the account type. The shortcut prompts for those three values and passes them as arguments to the Python script. The selected text is passed as standard input and the output of the script is sent to the clipboard.
The Python source code is available here. If you follow the link, you can see it’s pretty straightforward. It uses two regular expressions for the parsing and two templates to format a buy or sell transaction. Because the closed trade table format shows sell trades split into multiple lots, I aggregate trades by date and if they are all sells, I sum up the quantity and amount and just output one trade. Here’s the output for the closed RWX trade from above.