search

redstreet / beancount_reds_importers

Simple ingesting tools for Beancount (plain text, double entry accounting software). More importantly, a framework to allow you to easily write your own importers.
GNU General Public License v3.0
111 stars 38 forks source link

Schwab Banking CSV Corrections #63

Closed doughepi closed 1 year ago

doughepi commented 1 year ago

Wanted to put up an example of a Schwab banking CSV, as it seems to error out with the current format. Stack trace:

(.venv) doughepi@Pipers-MacBook-Pro finances %  bean-extract config.py ./data
ERROR:root:Importer beancount_reds_importers.importers.schwab.schwab_csv_checking.Importer.extract() raised an unexpected error: selection is not a field or valid field index: 'Date'
Traceback (most recent call last):
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/beancount/ingest/extract.py", line 182, in extract
    new_entries = extract_from_file(
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/beancount/ingest/extract.py", line 67, in extract_from_file
    new_entries = importer.extract(file, **kwargs)
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/smart_importer/hooks.py", line 40, in patched_extract_method
    imported_entries = unpatched_extract(
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/beancount_reds_importers/libtransactionbuilder/banking.py", line 107, in extract
    self.read_file(file)
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/beancount_reds_importers/libreader/csvreader.py", line 180, in read_file
    rdr = self.convert_columns(rdr)
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/beancount_reds_importers/libreader/csvreader.py", line 99, in convert_columns
    if 'type' in rdr.header():
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/petl/util/base.py", line 338, in header
    return tuple(next(it))
  File "/Users/doughepi/Library/Mobile Documents/com~apple~CloudDocs/Personal/Code/finances/.venv/lib/python3.9/site-packages/petl/transform/headers.py", line 90, in iterrename
    raise FieldSelectionError(x)
petl.errors.FieldSelectionError: selection is not a field or valid field index: 'Date'
;; -*- mode: beancount -*-

Here's an example of the CSV:

"Transactions  for Checking account ...000 as of 05/10/2023 01:41:36 PM ET"
"Date","Type","Check #","Description","Withdrawal (-)","Deposit (+)","RunningBalance"
"Pending Transactions"
"Total Pending Check and other Credit(s)","","","","","$0.00",""
"05/09/2023","","","APPLE CASH CUPERTINO, CA, US","$130.00","",""
"05/09/2023","","","APPLE CASH CUPERTINO, CA, US","$50.00","",""
"05/09/2023","","","APPLE CASH CUPERTINO, CA, US","$15.00","",""
"Posted Transactions"
"12/30/2022","INTADJUST","","Interest Paid","","$1.00","$3,951.00"
"12/25/2022","VISA","","Grocery Store","$50.00","","$3,950.00"
"12/20/2022","TRANSFER","","Funds Transfer to Brokerage -XXXX","$1,000.00","","$4,000.00"
"12/14/2022","ACH","","Electronic Deposit","","$5,000.00","$5,000.00"
redstreet commented 1 year ago

Hmm, this is in a very different format than the files I've seen in the past for Schwab Checking. Either they changed the format, or have multiple types of checking accounts. I've jotted down how they're different here. This requires a bit of work since the importers don't have out of the box support for "subtables," which needs to be added. I'm unlikely to get time to work on this soon, but will leave this ticket open for when I do.

Meanwhile, if you feel like it, you can take a look at the multitable reader and create a similar one for a "subtable" reader (meaning, one table with one row of headers, but divided into multiple tables within), which will then make an importer for this Schwab example above trivial to write.

redstreet commented 1 year ago

Done. Thanks for the samples, they're now checked in as tests. It's all working now! Give it a shot and let me know.