I just wanted to chip in some context to importing from TD. I spent a significant amount of time in the past getting data from TD to a normalized form for importing into systems (including Beancount, but mostly for Johnny, which is better suited to mark P/L on complex trades - https://github.com/beancount/johnny). In short, what I've learned is
- The most complete way to get data out of the system is via the thinkorswim platform's Account Statement tab. - Despite this, you still need to join multiple tables in order to resolve all the infos needed (prices, fees, etc.), and you need to configure the platform to include certain columns - For marking open positions, a separate download of the Activity and Positions tab - also suitably customized - is necessary. - Parsing descriptions to extract some of the infos was necessary to get all the required bits and pieces. - In Johnny I normalize inputs from all brokers to two tables: a table of transactions https://github.com/beancount/johnny/blob/master/johnny/base/transactions.md and a table of positions https://github.com/beancount/johnny/blob/master/johnny/base/positions.md - The downloads do not provide sufficient information about contract multipliers and futures and options expiration dates (e.g. for futures and futures options) so you end up having to store that elsewhere (I built this project for this purpose: https://github.com/blais/mulmat). In the ideal world there would exist some sort of open source instrument master database for retail people doing more complex things than buying mutual funds, but AFAIK this doesn't exist. If all you use is equities and a cash account, you're possibly okay with other sources of downloads. However, if you're using futures or options, you're likely to run into similar issues. You can see the (messy) parsing code here: https://github.com/beancount/johnny/blob/ma]ster/johnny/sources/thinkorswim_csv/transactions.py and here: https://github.com/beancount/johnny/blob/master/johnny/sources/thinkorswim_csv/positions.py ) On Sat, Jan 28, 2023 at 12:09 PM fin <[email protected]> wrote: > i'm working my way through a tdaameritrade_csv converter > (not pretty yet by far), but my lack of experience with > beancount, ledgers and the importers meant i had to wade > through some things to get to the point where i could get > output from my initial stab at a converter. > > the biggest issue is that there is no feedback when > you make an error that prevents anything from loading > at all. so you are not sure if you screwed up something > basic or if your importer isn't working at all. > > if you specify a pattern that doesn't match the input > file enough that bean-* can find it then you get nothing > back which says "no file found" you just get back this: > > > **** /home/me/fin/beancount/t.csv > ;; -*- mode: beancount -*- > **** /home/me/fin/beancount/t.csv > ;; -*- mode: beancount -*- > > to those who know what this means that's fine, but for > someone just kicking the tires and trying things out it > doesn't mean anything at all. > > so one thing i did was to add print statements to the > csvreader.py but in the end the most "keep my changes in > one place" way was to include the following in my __init__.py > (which is taken from csvreader.py). > > ===== > > def read_file(self, file): > if not self.file_read_done: > rdr = self.read_raw(file) > print ("Version 0.0.5 \n After read_raw file : \n", rdr) > rdr = rdr.skip(getattr(self, 'skip_head_rows', 0)) > # chop unwanted header rows > rdr = rdr.head(len(rdr) - getattr(self, 'skip_tail_rows', 0) - > 1) # chop unwanted footer rows > > if hasattr(self, 'skip_comments'): > rdr = rdr.skipcomments(self.skip_comments) > rdr = rdr.rowslice(getattr(self, 'skip_data_rows', 0), None) > rdr = self.prepare_raw_columns(rdr) > print ("After prepare_raw_columns : \n", rdr) > rdr = rdr.rename(self.header_map) > print ("After rename : \n", rdr) > rdr = self.convert_columns(rdr) > print ("After convert_columns : \n", rdr) > rdr = self.prepare_processed_columns(rdr) > print ("After prepare_processed_columns : \n", rdr) > self.rdr = rdr > self.ifile = file > self.file_read_done = True > > ===== > > which then told me that i wasn't even getting to the reading > part at all. so i was able then to finally go back and find > my mistake (not matching the file header properly). > > > so then i was able to start getting output that looked like: > > **** /home/me/fin/beancount/test-trans.csv > Importer: beancount_reds_importers.tdameritrade_csv.Importer > Account: Assets:SB:TDA:MM > > Version 0.0.5 > After read_raw file : > > > +------------+----------------+-------------------------------------------+---- > > ------+--------+-------+------------+---------+---------+--------------------+-- > -------------------+------------------------+ > | DATE | TRANSACTION ID | DESCRIPTION > | QUAN > TITY | SYMBOL | PRICE | COMMISSION | AMOUNT | REG FEE | SHORT-TERM RDM > FEE | FU > ND REDEMPTION FEE | DEFERRED SALES CHARGE | > > +============+================+===========================================+===== > > =====+========+=======+============+=========+=========+====================+=== > ==================+========================+ > | 01/02/2000 | 123456 | CHECK (WRITTEN AGAINST BROKERAGE ACCOUNT) | > | | | | -123.12 | | > | | | > > +------------+----------------+-------------------------------------------+----------+--------+-------+------------+---------+---------+--------------------+---------------------+------------------------+ > ... > ========== > > > one other gotcha that took me awhile to figure out was: > > # header looks like: DATE,TRANSACTION > ID,DESCRIPTION,QUANTITY,SYMBOL,PRICE,COMMISSION,AMOUNT,REG FEE,SHORT-TERM > RDM FEE,FUND REDEMPTION FEE, DEFERRED SALES CHARGE > # note that space on the beginning of that last field... > > which then needed the following to work: > > " DEFERRED SALES CHARGE":'defslschrg', > > > which is great! :) progress... > > i have a long ways to go in understanding but at least now i can > see what my initial version is doing and keep poking around more > to figure out more. > > > fin > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/7f1gaj-je6.ln1%40anthive.com. > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhP%3Dmt%3DsJST2Cx9q14PNk-EmojvBCfCxRrkZ41h%3DpE1h%3DQ%40mail.gmail.com.
