"ERROR: unknown entry type" for custom TD Ameritrade CSV importer

[reedlaw]

I'm basing my importer on schwab_csv_brokerage.py. There is no suitable type field that can be mapped. Instead there is a DESCRIPTION column with values such as "Sold 30 BP @ 28.15". Is there a way to map types with transaction_type_map and regexps or lambdas?

Here is the CSV:

DATE,TRANSACTION ID,DESCRIPTION,QUANTITY,SYMBOL,PRICE,COMMISSION,AMOUNT,REG FEE,SHORT-TERM RDM FEE,FUND REDEMPTION FEE, DEFERRED SALES CHARGE
06/10/2021,35392489790,CLIENT REQUESTED ELECTRONIC FUNDING RECEIPT (FUNDS NOW),,,,,6000.00,,,,
06/11/2021,35417292898,Bought 30 BP @ 27.71,30,BP,27.71,0.00,-831.30,,,,
06/11/2021,35417258165,Bought 50 LUV @ 57.44,50,LUV,57.44,0.00,-2872.00,,,,
06/11/2021,35417318519,Bought 5 COST @ 383.86,5,COST,383.86,0.00,-1919.30,,,,
08/13/2021,36765798623,QUALIFIED DIVIDEND (COST),,COST,,,3.95,,,,
09/24/2021,37594162919,QUALIFIED DIVIDEND (BP),,BP,,,9.83,,,,
09/24/2021,37594162920,ADR FEE (BP),,BP,,,-0.15,,,,
09/27/2021,37623582592,Bought 7 ON @ 49.55,7,ON,49.55,0.00,-346.85,,,,
10/25/2021,38216559411,Sold 5 COST @ 490.045,5,COST,490.045,0.00,2450.22,0.01,,,
11/03/2021,38462866073,Sold 7 ON @ 57.85,7,ON,57.85,0.00,404.95,,,,
11/05/2021,38535502875,Bought 38 VLO @ 76.28,38,VLO,76.28,0.00,-2898.64,,,,
12/09/2021,39262753915,QUALIFIED DIVIDEND (VLO),,VLO,,,37.24,,,,
12/17/2021,39494229796,QUALIFIED DIVIDEND (BP),,BP,,,9.68,,,,
12/23/2021,39634430392,QUALIFIED DIVIDEND (BP),,BP,,,0.15,,,,
12/23/2021,39634793012,QUALIFIED DIVIDEND (BP),,BP,,,-0.15,,,,
***END OF FILE***

My importer:

""" TD Ameritrade .csv importer."""

from beancount_reds_importers.libreader import csvreader
from beancount_reds_importers.libtransactionbuilder import investments


class Importer(csvreader.Importer, investments.Importer):
    IMPORTER_NAME = 'TD Ameritrade CSV'

    def custom_init(self):
        self.max_rounding_error = 0.04
        self.filename_pattern_def = 'transactions'
        self.header_identifier = 'DATE,TRANSACTION ID,DESCRIPTION.*'
        self.get_ticker_info = self.get_ticker_info_from_id
        self.date_format = '%m/%d/%Y'
        self.funds_db_txt = 'funds_by_ticker'
        self.skip_head_rows = 0
        self.skip_tail_rows = 1
        self.header_map = {
            "DATE":        'date',
            "TRANSACTION ID": 'memo',
            "DESCRIPTION": 'type',
            "SYMBOL":      'security',
            "QUANTITY":    'units',
            "PRICE":       'unit_price',
            "AMOUNT":      'amount',
            "SHORT-TERM RDM FEE": 'short_term_fee',
            "FUND REDEMPTION FEE": 'fund_redmpt_fee',
            " DEFERRED SALES CHARGE": 'deferred_sales_charge',
            "REG FEE": 'fees',
        }
        self.transaction_type_map = {}
        self.skip_transaction_types = []

    def get_target_acct_custom(self, transaction, ticker=None):
        if transaction.starts_with("Bought"):
            return self.config['buy']
        if transaction.starts_with("Sold"):
            return self.config['sell']
        return None

[redstreet]

Yes, see my message here:

Yes! A contributor just ran into the same situation a couple days ago and used a neat solution using petl.capture(). See here. You can split your description field into the type for the first 3 words (if that's valid), and then use the remaining for the memo (or throw it away). It'll look something like this (untested code!):

rdr = rdr.capture('Description', '(?:\s)(?:\w*)(.*)', ['type', 'memo'])

More info is in the petl reference.

[redstreet]

Since yours description is a variable number of words, I would define a list of possibilities (Bought, Sold, QUALIFIED DIVIDENDS, etc.) and use .startswith() like this, for example:

def f(desc):
  for k in types:
    if desc.startswith(k):
      return k
  return 'Unknown type'
rdr = rdr.addfield('type', lambda x: f(x['Description']))

[reedlaw]

That worked, thanks!

How would I add a txid metadata like so?

2021-09-24 * "Dividends" "[BP] BP p.l.c. ADR"
  txid: "37594162919"
  Assets:Ameritrade:USD            9.83 USD
  Income:Dividends:Ameritrade:BP  -9.83 USD

[reedlaw]

I figured it out and opened a PR: #54