Support investment transactions #32

JeffFaer · 2017-12-31T22:29:26Z

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 · 2018-01-07T16:22:48Z

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 · 2018-01-07T16:28:30Z

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 · 2018-01-07T16:30:32Z

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 · 2018-02-05T15:14:19Z

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 · 2018-02-06T07:59:58Z

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 · 2018-02-06T16:09:16Z

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

jaraco · 2018-02-11T18:25:27Z

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 · 2018-06-27T20:07:14Z

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 · 2020-08-23T02:00:17Z

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 · 2021-02-14T05:11:54Z

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

enorms · 2021-02-14T22:09:03Z

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

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

reubano · 2022-06-08T18:47:09Z

CR #37

tim-rohrer · 2022-12-26T18:38:21Z

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

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

Hey @enxyz, I'd like to experiment with this. I'm by no means a git or Python wizard, but I'll try forking your repo. I do see it is behind @reubano's so perhaps you can bring it forward? I'm not sure I'll be able to.

Then, perhaps together we can get a PR suitable for this repo?

reubano added the enhancement label Apr 10, 2018

reubano mentioned this issue Jan 9, 2019

Sell and Buy investment transactions based on positive or negative amount. #49

Closed

reubano mentioned this issue Jun 8, 2022

Schwab conversion no longer working #77

Closed

reubano added the help wanted label Jun 8, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Support investment transactions #32

Support investment transactions #32

JeffFaer commented Dec 31, 2017 •

edited

jaraco commented Jan 7, 2018

reubano commented Jan 7, 2018 •

edited

reubano commented Jan 7, 2018

jaraco commented Feb 5, 2018

reubano commented Feb 6, 2018

jaraco commented Feb 6, 2018 •

edited

jaraco commented Feb 11, 2018

reubano commented Jun 27, 2018 •

edited

enorms commented Aug 23, 2020 •

edited

jwittkoski commented Feb 14, 2021

enorms commented Feb 14, 2021

reubano commented Jun 8, 2022

tim-rohrer commented Dec 26, 2022

Support investment transactions #32

Support investment transactions #32

Comments

JeffFaer commented Dec 31, 2017 • edited

jaraco commented Jan 7, 2018

reubano commented Jan 7, 2018 • edited

reubano commented Jan 7, 2018

jaraco commented Feb 5, 2018

reubano commented Feb 6, 2018

jaraco commented Feb 6, 2018 • edited

jaraco commented Feb 11, 2018

reubano commented Jun 27, 2018 • edited

enorms commented Aug 23, 2020 • edited

jwittkoski commented Feb 14, 2021

enorms commented Feb 14, 2021

reubano commented Jun 8, 2022

tim-rohrer commented Dec 26, 2022

JeffFaer commented Dec 31, 2017 •

edited

reubano commented Jan 7, 2018 •

edited

jaraco commented Feb 6, 2018 •

edited

reubano commented Jun 27, 2018 •

edited

enorms commented Aug 23, 2020 •

edited