OFX Importer for Investment Accounts

[hann...@gmail.com]

Hello, all. 

Can anyone point in the direction of some examples on setting up the built-in OFX importer for investment accounts? I have it working fine for regular bank accounts (where there is one account number), but I'm not sure how to configure it for accounts that have multiple "subaccounts." For example, when I export transactions from Vanguard, I'm exporting for 5 real accounts (4 IRA accounts and one brokerage account) and a larger number of funds under each one. 

In looking through the documentation and old email threads, I can't tell if this is something one accomplishes in the ".config" file using the built-in importer or if I need to write a custom importer for investment accounts. Old emails reference a ofx_invest importer that was part of ledgerhub, but I know that is discontinued. 

Thanks in advance!

-JH

[Red S]

Does the built-in ofx importer handle investment transactions (eg: buy/sell stocks/funds)?

I built my own generic investment ofx importer on top of ofxparse, and I've been using it for years. It works for a number of brokerages including the case you describe, vanguard with multiple real accounts. I've been wanting to share my ofx importers, and will do so shortly.

It solves a number of issues including:
- stock and fund transactions including buys and sells (lot matching, however, is left to the user)
- special handling of money market transactions (price conversions)
- customizable for each brokerage house, to account for ofx differences, filenames, etc.
- produces balance assertions
- handles commissions, fees
- produces price entries (many brokerages include ticker prices of all active tickers in the account on the date of download, in addition to of course, the prices gleaned from transactions)
- available cash computation
- cusip to fund matching (some brokerage houses identify funds only by cusip or other custom tickers in the ofx)

[Jacob Hannan]

The built-in importer may well not. I recall an older email thread in which Martin mentioned merging in bits of ledgerhub into the main project, but it may be the case that the ofx_invest importer wasn't part of that. 

I'd love to take a look at anything you are willing to share. I'm not a programmer, let alone a python programmer, so I'm mainly working from examples.

Thanks!

-JH

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/JtE9JLQY3Og/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/54d2b408-e27f-44bb-a439-0eaf093646bf%40googlegroups.com.

[Red S]

I've cleaned it up and checked it in to a repo here. It should have enough to import multi-account Vanguard qfx files mostly out of the box. See the README.

My test infrastructure is unfortunately dependent on my personal files. So I'm unable to share it for now, without a fair amount of work.

Let me know your experience.

To unsubscribe from this group and all its topics, send an email to bean...@googlegroups.com.

[Red S]

It's now on PyPI:

pip install beancount-reds-importers

See the examples directory in the installed files, or on github (eg: /usr/lib/python3.6/site-packages/beancount_reds_importers/example/my.import)

[Jonathan Goldman]

Thanks so much for starting this project. I'm trying to get your importer working but I must have done something wrong as I don't get any extracted content

And here is what I get when I invoke bean-extract:

bean-extract my.import ~/staging/20180706-20200106--1578348252.ofx
;; -*- mode: beancount -*-

Here is my.import file:

#!/usr/bin/env python3                                                                                                            
"""Import configuration."""

import sys
from os import path
sys.path.insert(0, path.join(path.dirname(__file__)))
from beancount_reds_importers import vanguard
from fund_info import *

# Setting this variable provides a list of importer instances.                                                                    
CONFIG = [
    # Investments                                                                                                                
    # --------------------------------------------------------------------------------------                                      

    vanguard.Importer({
        'main_account'   : 'Assets:US:Investments:Vanguard:Brokerage',
        'account_number' : '123456',
        'transfer'       : 'Assets:zero-Sum-Accounts:Transfers:Bank-Account',
        'dividends'      : 'Income:Taxable:Dividends:Brokerage',
        'cg'             : 'Income:Taxable:Capital-Gains:Brokerage',
        'fees'           : 'Expenses:Brokerage-Fees:TradIRA',
        'rounding_error' : 'Equity:Rounding-Errors:Imports',
        'fund_info'       : fund_info,
    }),
]

[Red S]

1. Rename your file to OfxDownload.qfx. I'll fix it later so this is not a requirement.

2. Set the account number in your import file you posted: see the default of '123456'.

3. Then, first run bean-identify and ensure it works before running bean-extract

Let me know if that worked.

[Jonathan Goldman]

Yes that is working now. Thanks!

I'm now trying to debug the ticker issue. I have been adding tickers in my file to the fund_info.py file but it doesn't appear to be registering them. That is I put the new ticker in the CUSIP map and in the ticker_map but I still get an error for all tickers that weren't originally there. I'm just starting to look at the code but perhaps you know if there is something here as well.

[Jonathan Goldman]

@Red: I think I've entered all the tickers but I still get this error...

Error: cusip_map and ticker_map not found for: ['9...', 9...', etc].

thanks

[Red S]

Looks like your fund info is not being imported. I'm unable to tell what the problem is without looking at your entire output. If you can anonymize and send the output, I can help.

If you want to find the problem by yourself, add this after line 15 here:
https://www.github.com/redstreet/beancount_reds_ingestor/tree/master/beancount_reds_importers%2Fvanguard%2F__init__.py

print(security, self.cusip_map, self.inv_ticker_map)

That should give you insight into whether your maps are being picked up.

[Red S]

Also: are you importing the correct file with your maps? Or are you importing the example file that ships with the package by accident?

[Jonathan Goldman]

Hi,

I'm not a software engineer so might be slow in figuring this out so I appreciate all your help and creating this importer. I'm still not able to figure out the issue. Here is what I'm doing:

bean-extract jonathan.import ~/staging/OfxDownload.qfx > ~/staging/OfxDownload.qfx.extract

In that same local folder I have a file called "fund_info.py" that I have edited with the funds and maps. If I delete that file that I get an error that fund_info is missing. Here is my config file:

JONATHANs-MacBook-Pro:beandata jonathan$ more jonathan.import

#!/usr/bin/env python3

"""Import configuration."""

import sys

from os import path

sys.path.insert(0, path.join(path.dirname(__file__)))

from beancount_reds_importers import vanguard

from fund_info import *

# Setting this variable provides a list of importer instances.

CONFIG = [

    # Investments

    # --------------------------------------------------------------------------------------

    vanguard.Importer({

        'main_account'   : 'Assets:US:Investments:Vanguard:Brokerage',

        'account_number' : 'XXXXXXX',

        'transfer'       : 'Assets:Zero-Sum-Accounts:Transfers:Bank-Account',

        'dividends'      : 'Income:Taxable:Dividends:Brokerage',

        'cg'             : 'Income:Taxable:Capital-Gains:Brokerage',

        'fees'           : 'Expenses:Brokerage-Fees:TradIRA',

        'rounding_error' : 'Equity:Rounding-Errors:Imports',

        'fund_info'       : fund_info,

    }),

]

I tried modifying the the Vanguard importer...the __init__.py file with the print command and this seemed to confirm that the mapper is not being created.

(/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/beancount_reds_importers/vanguard/__init__.py)

I don't see how it would pick up our fund_info.py in the example file but I haven't looked fully at the code.

Anyway, I'll keep investigating when I have some time but if you see anything obvious I appreciate any help.

thanks.

[Red S]

I can't see anything that's amiss. Unfortunately, this is difficult to debug further without trying to reproduce this using your input file, which is hard to do since it contains private information.

You could try downloading this file, renaming it to OfxDownload.qfx, setting your account_number to "12345678.123456-01" and running your command on it:

https://raw.githubusercontent.com/jseutter/ofxparse/master/tests/fixtures/investment_401k.ofx

It should complain about 'FOO' not being found. This example has an oddity where it also needs 'BAR' and 'BAZ'. These three are cusips you'll need to add. Once you do, this is the expected error you should see (because this is not a Vanguard file):
AttributeError: 'InvestmentStatement' object has no attribute 'available_cash'

Are you able to get to that point?

See inline for a couple more comments.

Can you copy/paste the output?

 

I don't see how it would pick up our fund_info.py in the example file but I haven't looked fully at the code.

From the from fund_info import * in jonathan.import.

[Red S]

I've added:

1) more specific error checking.

2) A Vanguard test case in the examples directory

This might help you get up and running.

To try it out:

pip install --upgrade beancount-reds-importers

[Jonathan Goldman]

Thanks for the update. Now everything worked fine! Extraction ran perfectly.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/fe85b237-fa45-4505-ac5a-3a972d3680d1o%40googlegroups.com.

[Red S]

Great!

I'm not sure what made it work -- I only touched code that is run after an exception occurs (not what makes it occur) -- but I'm glad it's works for you now.

Feel free to post here or open bugs on github if you run into other issues or have feature requests.

To unsubscribe from this group and stop receiving emails from it, send an email to bean...@googlegroups.com.

[rac...@rachelblum.com]

Heh. Then you'll be glad to know the mystery resurfaced :) Vanguard again... 

After sprinkling a bunch of print's, it looks like a Vanguard 401(k) simply doesn't have available cash. (Which makes sense). The transactionbuilder *mostly* handles that error, but in libtransactionbuilder/investments.py, line 292, there's one call for get_available_cash outside the try block. I've hacked this locally by just having the vanguard importer return False for available_cash, but probably not the right fix overall :)