Some work left to do on the Schwab importer

[Zburatorul]

I wanted to collect a few loose ends here:

• Add commodity directives for every new commodity that appears in transactions.
• Handle bonds, and US Treasuries specifically. Bond symbols are CUSIPs, which are not valid Beancount symbols. Quantity * Price != Amount (-Fee).
• Allow users to specify if commodity transactions should be in subaccount, or stay in top-level account like OFX import does it.
• Option expiration, bond maturation.
• Option sold in lots of 100, even though price reported for 1.
• Add/skip Income account posting for capital gains (no need when selling short, but needed when buying to close).

@carljm Comments and suggestions are welcome.

[carljm]

Sounds reasonable to me; I don't have experience with most of those transaction types, so I can review the code but won't have much input on correctness of the handling. Thanks for filling in the gaps!

[bayesianmind]

I have a PR in the works to add Schwab Bank Checking support.

[zhou13]

It seems that schwab_csv importer does not support Reinvest Shares and Reinvest Dividend. I am getting errors like

ValueError: 'Reinvest Shares' is not a valid SchwabAction

The csv looks like (date and amount are modified)

"Date","Action","Symbol","Description","Quantity","Price","Fees & Comm","Amount",
"00/00/0000","Reinvest Dividend","SWVXX","SCHWAB VALUE ADVANTAGE MONEY FUND","","","","$0.1",
"00/00/0000","Reinvest Shares","SWVXX","SCHWAB VALUE ADVANTAGE MONEY FUND","0.1","$1.00","","-$0.1",
Transactions Total,"","","","","","",$0.00

[bayesianmind]

91 Adds support for Reinvest Shares and Reinvest dividend among other new action types.

@Zburatorul Do you mind taking a look at that PR and see what you think of it?

[Zburatorul]

91 Adds support for Reinvest Shares and Reinvest dividend among other new action types.

@Zburatorul Do you mind taking a look at that PR and see what you think of it?

I had a look and I don't have any comments.

[Zburatorul]

A few more actions types that need implementation:

1. Short Term Cap Gain
2. Long Term Cap Gain
3. Stock Merger

I ran across a particularly strange situation whereby on the last day of the tax year I received some cash into my account, and the transaction was marked as "Long Term Cap Gain". I had not sold that ETF, so it was strange to receive cash on some unrealized gains. I think it is likely explained by the fact that the ETF was held in a tax-deferred account (Traditional IRA). I wanted to call this out as an edge case.

Another complication is the Stock Merger transaction type, which involves two rows in the CSV. Example: 12/21/20 Stock Merger OPEN OPENDOOR 100 12/21/20 Stock Merger IPOB SCL CPT -100

UPDATE: Implemented.

[zhou13]

I also notice that the Schwab import does not generate the price statement of each asset even I specify position_csv_filenames. Is this expected for now or I just mess up something?

[carljm]

@zhou13 It does generate price statements for each asset for me; it is expected to work.

[zhou13]

I did some debugging and I found that my position csv files have a header

Positions for account Individual                        XXXX-1234 as of 02:55 PM ET, 03/10/2021

Therefore, the account name is Individual XXXX-1234 rather than XXXX-1234 as in my transaction csv files.

[Zburatorul]

I did some debugging and I found that my position csv files have a header

Positions for account Individual                        XXXX-1234 as of 02:55 PM ET, 03/10/2021

Therefore, the account name is Individual XXXX-1234 rather than XXXX-1234 as in my transaction csv files.

Odd. You might look at changing the regex matching the positions and transactions title line to only match the XXXX-XXXX part. Keep in mind there might be accounts like XXXXXX-XXXX, or XXX-XXXX-XXXX.

[carljm]

@zhou13 Strange. For me both transaction and position CSVs have the account name in that form you showed (with text label followed by account number), so they match each other and there is no problem. I did notice in my Lot Details pull request (https://github.com/jbms/beancount-import/pull/103) that my Lot Details CSV files had only the account number (no text label), so I had to account for that in that PR.

I'm not sure why your transaction CSV files are different, but perhaps throughout the importer we just need to standardize on using only the account number and discarding the textual account label?

[zhou13]

@carljm I agree with that. I also have an account that has a format like Roth Contributory IRA XXXX-1234. My accounts are relatively new, so probably they changed the naming convention recently.

[chandler150]

A few more actions types that need implementation:

1. Short Term Cap Gain
2. Long Term Cap Gain
3. Stock Merger

I ran across a particularly strange situation whereby on the last day of the tax year I received some cash into my account, and the transaction was marked as "Long Term Cap Gain". I had not sold that ETF, so it was strange to receive cash on some unrealized gains. I think it is likely explained by the fact that the ETF was held in a tax-deferred account (Traditional IRA). I wanted to call this out as an edge case.

Another complication is the Stock Merger transaction type, which involves two rows in the CSV. Example: 12/21/20 Stock Merger OPEN OPENDOOR 100 12/21/20 Stock Merger IPOB SCL CPT -100

UPDATE: Implemented.

@Zburatorul I'm still having some issues with Stock Merger.

In this instance the financial situation was a SPAC being converted to long term IPO Name, and the program is breaking at the bottom of "booking_full.py"[Rows 935-946] due to it being a direct conversion, no price, no cost which it specifically disallows.

Secondarily, while your row search is correctly picking up the stock name listed by schwab in the csv file, it is certainly not the name of the prior stock, not sure where that error stems from. No easy way on csv to clarify correct name.

I can probably do a workaround for this one by passing by the errors and manually modifying once posted, but no good ideas come to mind immediately for a long term solution.

Ex) posting headed into booking_full final rows:

Posting(account='Assets:US:Charles:Brokerage:53804W106', units=-30 53804W106, cost=Cost(number=None, currency='USD', date=datetime.date(0000, 00, 00), label=None), price=None, flag=None, meta={'source_desc': 'LIVE OAK ACQUISITION XXXMANDATORY MERGER EFF: 10/20/21', 'date': datetime.date(0000, 00, 00), 'schwab_action': 'Stock Merger', 'filename': '/Users/Documents/Finances/ignored.beancount', 'lineno': 153})

So to clarify, the account is correctly being drawn up from the excel sheet, where 53804W106 is, but those should be LOKB.

Additionally the cost, and price as mentioned above.

[Zburatorul]

Can you provide the two relevant lines from the CSV you're having trouble with? re: price and cost, I did not even attempt to handle that correctly, but it should be possible given that Carl has introduced logic that keeps track of lots.