Support investment transactions

[JeffFaer]

My HSA provider doesn't support Quicken export anymore. It does support Excel export, however. I was hoping to use this tool to bridge the gap, but unfortunately, this tool doesn't seem to support investment transactions (<INVSTMTMSGSETV1> in OFX parlance).

[jaraco]

Investment transactions are harder (impossible?) because each transaction must be associated with a security and each security must be uniquely identified (at the server). Unless your CSV provides a Unique ID (CUSIP) for each security, or unless the library can somehow solicit that information out-of-band, it may not be possible to generate a viable OFX file for investment transactions.

I'm in a similar situation where my payroll provider (Paychex) is only providing broken OFX downloads, so I'm writing a bespoke routine to convert the CSV downloads to OFX, but I'm having to parse out the securities list to resolve those unique ids.

[reubano]

Theres an investment csv in the data folder and an accompanying test for it as well. Let me know if that works for you or not.

Edit... turns out I only have an investment qif example. Not the csv used to produce it.

[reubano]

The csv should provide unique security ids. And your accounting program should then be able to map ids to cusips or whatever else you need. Gnucash works that way at least.

[jaraco]

For what it's worth - here's the routine I used to create an OFX of the security transactions for PayChex. As you can see, it relies on the broken OFX download to get security metadata and header info that's not present in the CSV. It's not a generalizable approach, but it does start to reveal the interfaces that would be necessary to support a generalized approach.

[reubano]

so it appears that while I've added investment support, I didn't add a test, nor did I supply a proper csv file. @jaraco is there any chance you could take your csv file and modify your python script to use the investment feature?

[jaraco]

Yes, perhaps. I'll take a stab at it at some point. (I've set a reminder)

[jaraco]

Here are the first two lines of an export from my paychex account:

Date, Transaction, InvestmentName, Ticker, Amount, Price, Shares
01/09/2018,Mid-Atlantic Trustee Fee,American Funds Washington Mutual Investors R6,RWMGX,-0.12,46.810000,-0.0026,

As you can see, it doesn't have the CUSIP... but I thought maybe I could add a more complex mapping that would load the CUSIP from another source. So I started attempting to create the mapping and running the package, but I get an error:

$ .tox/python/bin/python -m csv2ofx.main -m paychex ~/Downloads/EXPORT.csv export.ofx   
'<' not supported between instances of 'NoneType' and 'NoneType'
No data to write. '<' not supported between instances of 'NoneType' and 'NoneType'. Try again with `-c` option.

And here's the debug output:

$ .tox/python/bin/python -m csv2ofx.main -m paychex ~/Downloads/EXPORT.csv export.ofx --debug
{'account_type': None,
 'chunksize': 16384,
 'collapse': None,
 'custom': None,
 'debug': True,
 'dest': 'export.ofx',
 'encoding': 'utf-8',
 'end': '2018-02-11 13:16:41.057679',
 'language': 'ENG',
 'list_mappings': False,
 'mapping': 'paychex',
 'overwrite': False,
 'qif': False,
 'server_date': None,
 'source': '/Users/jaraco/Downloads/EXPORT.csv',
 'start': None,
 'verbose': False,
 'version': False}

Here's the mapping I used:

mapping = {
    'has_header': True,
    'is_split': False,
    'currency': 'USD',
    'delimiter': ',',
    'date': itemgetter('Date'),
    'amount': itemgetter('Amount'),
    'price': itemgetter('Price'),
    'shares': itemgetter('Shares'),
    'investment': itemgetter('InvestmentName'),
    'ticker': itemgetter('Ticker'),
    'desc': itemgetter('Transaction'),
}

Can you suggest how I could further troubleshoot - and what things might be missing from the mapping file or usage that I could supply to get some output?

[reubano]

After removing extra spaces from your csv header, and pushing a few fixes, I get the following ofx export:

DATA:OFXSGML
ENCODING:UTF-8
<OFX>
	<SIGNONMSGSRSV1>
		<SONRS>
			<STATUS>
				<CODE>0</CODE>
				<SEVERITY>INFO</SEVERITY>
			</STATUS>
			<DTSERVER>20180627143708</DTSERVER>
			<LANGUAGE>ENG</LANGUAGE>
		</SONRS>
	</SIGNONMSGSRSV1>
	<BANKMSGSRSV1>
		<STMTTRNRS>
			<TRNUID></TRNUID>
			<STATUS>
				<CODE>0</CODE>
				<SEVERITY>INFO</SEVERITY>
			</STATUS>
			<STMTRS>
				<CURDEF>USD</CURDEF>
				<BANKACCTFROM>
					<BANKID>382b0f5185773fa0f67a8ed8056c7759</BANKID>
					<ACCTID>382b0f5185773fa0f67a8ed8056c7759</ACCTID>
					<ACCTTYPE>CHECKING</ACCTTYPE>
				</BANKACCTFROM>
				<BANKTRANLIST>
					<DTSTART>19700101</DTSTART>
					<DTEND>20180627</DTEND>
					<STMTTRN>
						<TRNTYPE>DEBIT</TRNTYPE>
						<DTPOSTED>20180109000000</DTPOSTED>
						<TRNAMT>0.12</TRNAMT>
						<FITID>fa5d3eb2d66fbd854953d96cd49f41e7</FITID>
						<MEMO>Mid-Atlantic Trustee Fee</MEMO>
					</STMTTRN>
				</BANKTRANLIST>
			</STMTRS>
		</STMTTRNRS>
	</BANKMSGSRSV1>
</OFX>

[enorms]

So I was able to do a basic version of investments that let me output my portfolio from AngelList as a csv and convest that to an OFX that was accepted into Quicken and can show portfolio returns. If someone is interested to build on this I can check in a branch. It will take a fair amount of work to be ready to merge, it only handles "buy stocks" and hard codes a few variable names.

To make it function for a normal investment account, you'd probably want to add "sell stocks" and the ofx section INVPOSLIST that can assign a current value. OFX can also handle selling short, and bonds and options.

DATA:OFXSGML
ENCODING:UTF-8
<OFX>
	<SIGNONMSGSRSV1>
		<SONRS>
			<FI><ORG>3000</ORG></FI><INTU.BID>3000</INTU.BID>
			<INTU.BID>3000</INTU.BID>
			<STATUS>
				<CODE>0</CODE>
				<SEVERITY>INFO</SEVERITY>
			</STATUS>
			<DTSERVER>20200822185433</DTSERVER>
			<LANGUAGE>ENG</LANGUAGE>
		</SONRS>
	</SIGNONMSGSRSV1>
	<INVSTMTMSGSRSV1>
		<INVSTMTTRNRS>
			<TRNUID>0</TRNUID>
			<STATUS>
				<CODE>0</CODE>
				<SEVERITY>INFO</SEVERITY>
			</STATUS>
			<INVSTMTRS>
				<CURDEF>USD</CURDEF>
				<INVACCTFROM>
					<BROKERID>ANGELLIST</BROKERID>
					<ACCTID>Eric Norman</ACCTID>
					<ACCTTYPE>INVESTMENTS</ACCTTYPE>
				</INVACCTFROM>
				<INVTRANLIST>
					<DTSTART>19700101.000[-4:EDT]</DTSTART>
					<DTEND>20200822.000[-4:EDT]</DTEND>
					<BUYSTOCK>
						<INVBUY>
							<INVTRAN>
								<DTTRADE>20191017001700.000[-4:EDT]</DTTRADE>
								<FITID>d331cf88a0009965aab653d6d14526ae</FITID>
							</INVTRAN>
							<SECID>
								<UNIQUEID>StartupOne</UNIQUEID>
								<UNIQUEIDTYPE>StartupOne</UNIQUEIDTYPE>
							</SECID>
							<UNITS>1</UNITS>
							<UNITPRICE>4000.00</UNITPRICE>
							<COMMISSION>0</COMMISSION>
							<TAXES>0</TAXES>
							<TOTAL>-4000.00</TOTAL>
							<CURRENCY>
								<CURRATE>1.0</CURRATE>
								<CURSYM>USD</CURSYM>
							</CURRENCY>
							<SUBACCTSEC>CASH</SUBACCTSEC>
							<SUBACCTFUND>CASH</SUBACCTFUND>
						</INVBUY>
						<BUYTYPE>BUY</BUYTYPE>
					</BUYSTOCK>
					<BUYSTOCK>
						<INVBUY>
							<INVTRAN>
								<DTTRADE>20191023233700.000[-4:EDT]</DTTRADE>
								<FITID>e4fb9391c96cdb054b25fdd4c716f0ac</FITID>
							</INVTRAN>
							<SECID>
								<UNIQUEID>StartupTwo</UNIQUEID>
								<UNIQUEIDTYPE>StartupTwo</UNIQUEIDTYPE>
							</SECID>
							<UNITS>1</UNITS>
							<UNITPRICE>4000.00</UNITPRICE>
							<COMMISSION>0</COMMISSION>
							<TAXES>0</TAXES>
							<TOTAL>-4000.00</TOTAL>
							<CURRENCY>
								<CURRATE>1.0</CURRATE>
								<CURSYM>USD</CURSYM>
							</CURRENCY>
							<SUBACCTSEC>CASH</SUBACCTSEC>
							<SUBACCTFUND>CASH</SUBACCTFUND>
						</INVBUY>
						<BUYTYPE>BUY</BUYTYPE>
					</BUYSTOCK>
				</INVTRANLIST>
				<DTASOF>2020-08-22 18:54:40.084973.000[-4:EDT]</DTASOF>
			</INVSTMTRS>

		</INVSTMTTRNRS>
	</INVSTMTMSGSRSV1>
	<SECLISTMSGSRSV1>
		<SECLIST>
			<STOCKINFO>
				<SECINFO>
					<SECID>
						<UNIQUEID>StartupOne</UNIQUEID>
						<UNIQUEIDTYPE>StartupOne</UNIQUEIDTYPE>
					</SECID>
					<SECNAME>StartupOne-name</SECNAME>
					<TICKER>1</TICKER>
					<FIID>953832</FIID>
				</SECINFO>
			</STOCKINFO>
			<STOCKINFO>
				<SECINFO>
					<SECID>
						<UNIQUEID>StartupTwo</UNIQUEID>
						<UNIQUEIDTYPE>StartupTwo</UNIQUEIDTYPE>
					</SECID>
					<SECNAME>StartupTwo-name</SECNAME>
					<TICKER>1</TICKER>
					<FIID>846301</FIID>
				</SECINFO>
			</STOCKINFO>
		</SECLIST>
	</SECLISTMSGSRSV1>
</OFX>

[jwittkoski]

@lifekaizen Do you have a branch with your investment changes?

[enorms]

@lifekaizen Do you have a branch with your investment changes?

Yes, here's the branch: https://github.com/lifekaizen/csv2ofx/tree/investments