"ERROR: unknown entry type" for custom TD Ameritrade CSV importer
Closed this issue · 4 comments
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 NoneYes, 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.
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']))
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