Struggling to get my first importer working (using Red's Importers)

[Joshua Cabrera]

I'm trying to create my first importer to ingest Fidelity's CSV files.

I'm not a developer or engineer but do understand the basics.

The fidelity_cma_csv_examples didn't initially work for me, but after much struggle, I was able to get the expected results. I'll share details at the end in case I'm doing something wrong/crazy. And in the chance what I did was correct, I would be very happy to contribute with a pull request.

So, after getting the example to work, I set to work on my actual file, which of course varies slightly. I'm assuming Fidelity changed their file format after the example was initially created.

The variances seem to be minor: fewer empty head and tail rows, one new column, a space before the date, the empty rows don't have commas, and the quantity column is populated with a float.

I've tried eliminating all as issues by adjusting the two skip row variables, adding the new column to the header identifier, and manually editing the file to make the other changes match the example.

Now I find myself stuck. All I get ";; -*- mode: beancount -*-". I've inserted some print functions inside the methods, and it seems like it's not reaching prepare_raw_columns().

I would really appreciate any pointers you can share. I'm not sure what to do at this point besides trying beancount's standard csv importer.

Struggles with the Fidelity CMA examples

1) Cannot import name 'fidelity_cma_csv'.

Fixed by updating fidelity-cma-csv line 9:

before

from beancount_reds_importers import fidelity_cma_csv

after

from beancount_reds_importers.importers.fidelity import fidelity_cma_csv

2) Attribute error: 'Importer' object has no attribute 'skip_transaction_types'.

Fixed by inserting a variable into custom_init() in fidelity_cma_csv.py:

self.skip_transaction_types = []

3) Attribute error: 'row' object has no attribute 'memo', and attribute error: 'row' object has no attribute 'payee'.

Fixed by updating self.header_map:

"Action":               "payee",

"Quantity":             "memo",

Note: "Action" was mapped to "description" and "Quantity" was unmapped.

4) Also had two simple column renames, but I'll skip those details.

Here's the output from the example just to validate I was able to get the example working.

[Joshua Cabrera]

Looks like I forgot to adjust the original picture size so it's readable.

[Red S]

Agree, I ran the run_tests.sh in that importer and ran into the same issues. Sorry you ran into these. The problem seems to be that this importer doesn't use a standard pytest setup, and the unit tests therefore haven't run in a long while, while several things changed underneath it. It also seems to have been an importer for checking-type transactions, which might explain why quantity isn't used. This all needs to be cleaned up, or better yet, this importer needs to be removed given the ofx importer works really well with Fidelity.

Before proceeding further with any of this, I'm wondering are you using the csv importer for a particular reason? If not, I *highly* recommend using the ofx importer. Works great, and Fidelity also supports direct downloads.

-Red

[Red S]

Now I find myself stuck. All I get ";; -*- mode: beancount -*-". I've inserted some print functions inside the methods, and it seems like it's not reaching prepare_raw_columns().

You’re doing nothing wrong, BTW. CSV parsing changed csvreader a while ago, and wasn’t fixed in this importer since it wasn’t a standard pytest (the root of the issue). As shown here, prepare_raw_columns() doesn’t exist any more, and the importer needs to use one of these instead. If there's a reason to use this importer, I'd be happy to review a PR or even assist in making the fixes. If ofx works for you, I'd be inclined to simply remove this importer.

Red

​

[Joshua Cabrera]

My reason for using .csv over .ofx is simply familiarity and ease of starting up.

I have no experience with ofx and thought the learning curve would be much steeper, and I already feel like I'm drowning a bit as a layperson.

I'll look into the csvreader changes you mentioned to try making a replacement for prepare_raw_columns() and also look further into ofx.

Thanks for the help here and confirming I'm doing alright, and also thank you for maintaining it!

[Red S]

My reason for using .csv over .ofx is simply familiarity and ease of starting up.

I have no experience with ofx and thought the learning curve would be much steeper, and I already feel like I'm drowning a bit as a layperson.

I know it may seem like that, especially given ofx is not a human readable format. But might I suggest giving that a shot first, and you might be pleasantly surprised. Because there is nothing to do, no importer to write, since it is a machine format with an official spec, unlike csv. The learning curve should be close to zero. There are other benefits too.

I'll look into the csvreader changes you mentioned to try making a replacement for prepare_raw_columns() and also look further into ofx.

If I were you, I’d definitely spend five minutes with ofx first. And reds-importers ships with ofx-summarize which you can use to peek inside and poke around an ofx file, which makes it a lot less opaque and easy on a layperson.

Thanks for the help here and confirming I'm doing alright, and also thank you for maintaining it!

Happy to, and glad its helpful!

​

[Joshua Cabrera]

Quick update:

Installed ofxget and manage to get my ofxget.cfg updated using ofxget acctinfo. Not sure if this required for bean-download.

Also downloaded a .ofx file using:

ofxget stmt fidelity > c:\users\joshd\fidelity.ofx

Struggling to my download.cfg and bean-download download working - keep getting:

1/1 sites failed:    fidelity

Will keep trying whenever I have time this week.

[Red S]

Glad you got it working with ofxget. bean-download is just a wrapper around ofxget or whatever command you have to parallelize all your downloads. If you don’t have several accounts you’re using it with, it’s of limited use. The built in template for fidelity is here, and lets you store your password securely in pass so it’s not stored in plaintext. You could simply paste your working ofxget command into the bean-download config file to get it to work if the parallelization is useful for you. Either way, that’s the last thing I’d do after I got everything else in the import workflow working first.

And oh wow, I’d forgotten that Fidelity stopped letting you download ofx via their website, and only let you do it via direct download.

​

[Joshua Cabrera]

So, I've got my .ofx file and am not worried about the download task anymore. That's the good news.

The bad news is now I'm stuck on the import task.

bean-extract my.import fidelity_1234_july2024.ofx

Results

ERROR:root:Importer beancount_reds_importers.importers.fidelity.Importer.identify() raised an unexpected error: not enough values to unpack (expected 2, got 1)

Traceback (most recent call last):

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\beancount\ingest\identify.py", line 63, in find_imports

    matched = importer.identify(file)

              ^^^^^^^^^^^^^^^^^^^^^^^

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\beancount_reds_importers\libreader\reader.py", line 26, in identify

    self.initialize_reader(file)

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\beancount_reds_importers\libreader\ofxreader.py", line 26, in initialize_reader

    self.ofx = self.read_file(file)

               ^^^^^^^^^^^^^^^^^^^^

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\beancount_reds_importers\libreader\ofxreader.py", line 57, in read_file

    return ofxparse.OfxParser.parse(fh)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\ofxparse\ofxparse.py", line 396, in parse

    ofx_file = OfxPreprocessedFile(file_handle)

               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\ofxparse\ofxparse.py", line 155, in __init__

    super(OfxPreprocessedFile, self).__init__(fh)

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\ofxparse\ofxparse.py", line 82, in __init__

    self.read_headers()

  File "C:\Users\joshd\AppData\Local\Programs\Python\Python312\Lib\site-packages\ofxparse\ofxparse.py", line 95, in read_headers

    header, value = line.split(six.b(":"))

    ^^^^^^^^^^^^^

ValueError: not enough values to unpack (expected 2, got 1)

;; -*- mode: beancount -*-

If I run ofx-summarize, it throws the same error, so I found a sample .ofx online and tested ofx-summarize with it. 

And of course, the example works, so it looks like Fidelity is providing an incorrectly formatted file? I noticed it doesn't have a single line break until the end of the file.

[Red S]

This is uncommon, but typically because there was an issue downloading the ofx. Usually because of authentication problems. Have you examined the ofx to see if it’s valid?

Try this to make it more readable: sed 's/>/>\n/g' xyz.ofx

​

You should see something like this on the top:

<STATUS> <CODE> 0 </CODE> <SEVERITY> INFO </SEVERITY> <MESSAGE> Success </MESSAGE> </STATUS>

​

If you see that, then try a really simple parse:

#!/usr/bin/env python3 import ofxparse ofx_file_path = "your_ofx_file.ofx" with open(ofx_file_path, 'r') as ofx_file: ofx = ofxparse.OfxParser.parse(ofx_file) print("Account type:", ofx.account.account_type) print("Account ID:", ofx.account.account_id)

​

[Joshua Cabrera]

Thanks, Red. The file itself looks okay after another look.

I tried the new line trick, but it didn't change the results.

The simple parse threw the same unpacking value error on my ofx while working on the sample ofx.

I'm sharing a sanitized version of my ofx (replaced account number, transaction details, etc.) and my import file.

In the meantime, I'll try a different Fidelity account and smaller date/transaction ranges.

[Red S]

Thanks for the file! I can’t tell whether it was an artifact of how the ofx file you attached got created or whether the original ofx file suffers from this problem, but your ofx file is encoded in UTF-16 even though the header claims it’s UTF-8. ofxparse fails because of this. Running this allows it to work normally:

iconv -f UTF-16 -t UTF-8 fidelity_X12345678.ofx -o a.ofx ofx-summarize a.ofx Total number of accounts: 1
----------------
Account info:   X12345678 fidelity.com
Statement info: 2024-07-01 00:00:00 -- 2024-07-31 00:00:00. Bal: []
Types:  {'cash', 'check', 'dep', 'payment'}

2024-07-30 04:00:00 cash THIS IS FUN Note -15.0000
2024-07-30 04:00:00 dep THIS IS FUN Note -15.0000
2024-07-29 04:00:00 check THIS IS FUN Note -15.0000
2024-07-26 04:00:00 cash THIS IS FUN Note -15.0000
2024-07-26 04:00:00 cash THIS IS FUN Note -15.0000

​

It's not the same stack trace as you got originally though. Either way, try this?

[Joshua Cabrera]

Thank you so much! Everything seems to be working now, so I just have to tweak my.import and catch up on recording in my ledger.

Once I'm caught up, I'll come back to automating the download and extract workflow.

Regarding the encoding issue, my first guess was it's because I'm doing all this on Windows and using the Command Prompt shell, instead of bash. That theory appeared to be incorrect after creating some test files from the shell, which resulted in utf-8 encoding.

Next, I tried creating test files using the integrated terminal in VS Code since technically that's where I've been running my commands, and it turns out that was the culprit. ¯\_(ツ)_/¯

[Red S]

You’re welcome, and glad to hear!

Found this if it helps.

​

[Joshua Cabrera]

Yes, it does! Turns out I wasn't paying close enough attention to the terminal type.

Using the VS Code Beancount extension, I have it set to start fava when opening my beancount file. That automated process uses a PowerShell terminal, not Command Prompt, and I was just using that window, instead of a new terminal.

Making sure to use a Command Prompt terminal inside of VS Code, running the stmt command resulted in UTF-8 encoding.