I have some investment accounts which I am tracking with Quicken for macOS. However, I am using the Quicken feature to auto-download the status of the account. However, the company does not support downloading the individual transactions, which I would like to also track inside of Quicken. But, I do not want enter these transitions manually. The do allow me to download the transactions as a CSV file. I had the idea of taking that CSV file and converting it to OFX which Quicken should be able to import. However, the OFX file I am creating is not valid and I am not sure how to fix it so Quicken will import it.
The data below is mocked, but is same format.
When I try to import the OFX file, the error I get from Quicken is:
This FI is inactive, we cannot connect.
If I remove the FI block, I get the error:
Unable to read the selected Web Connect file.
How can I change the OFX file?
The CSV file is:
Brokerage
Run Date,Action,Symbol,Security Description,Security Type,Quantity,Price ($),Commission ($),Fees ($),Accrued Interest ($),Amount ($),Settlement Date
01/02/2023,YOU BOUGHT,A,AGILENT TECHNOLOGIES INC,Cash,42,84,,,,-3528.00,01/03/2023
01/03/2023, YOU BOUGHT,AA,ALCOA CORPORATION,Cash,43,86,,,,-3698.00,01/04/2023
01/04/2023, YOU BOUGHT,AAC,ARES ACQUISITION CORP,Cash,44,88,,,,-3872.00,01/05/2023
The OFX file I am creating is:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?OFX OFXHEADER="200" VERSION="220" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS>
<CODE>0</CODE>
<SEVERITY>INFO</SEVERITY>
</STATUS>
<DTSERVER>20230304170917.174[+0:UTC]</DTSERVER>
<LANGUAGE>ENG</LANGUAGE>
<FI>
<ORG>Investments</ORG>
<FID>1234</FID>
</FI>
</SONRS>
</SIGNONMSGSRSV1>
<INVSTMTMSGSRSV1>
<INVSTMTTRNRS>
<TRNUID>0</TRNUID>
<STATUS>
<CODE>0</CODE>
<SEVERITY>INFO</SEVERITY>
</STATUS>
<INVSTMTRS>
<DTASOF>20230304170917.178[+0:UTC]</DTASOF>
<CURDEF>USD</CURDEF>
<INVACCTFROM>
<BROKERID>investments.com</BROKERID>
<ACCTID>Y12345678</ACCTID>
</INVACCTFROM>
<INVTRANLIST>
<DTSTART>20230102000000.000[+0:UTC]</DTSTART>
<DTEND>20230104000000.000[+0:UTC]</DTEND>
<BUYOTHER>
<INVBUY>
<INVTRAN>
<FITID>725bb8d9-ef22-4b9b-9214-8080a4a59ddb</FITID>
<DTTRADE>20230102000000.000[+0:UTC]</DTTRADE>
<DTSETTLE>20230103000000.000[+0:UTC]</DTSETTLE>
</INVTRAN>
<SECID>
<UNIQUEID>100000000</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<UNITS>42</UNITS>
<UNITPRICE>84</UNITPRICE>
<TOTAL>3528</TOTAL>
<SUBACCTSEC>CASH</SUBACCTSEC>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBUY>
</BUYOTHER>
<BUYOTHER>
<INVBUY>
<INVTRAN>
<FITID>a04dad7f-7374-4920-bf1c-ed90bdef5991</FITID>
<DTTRADE>20230103000000.000[+0:UTC]</DTTRADE>
<DTSETTLE>20230104000000.000[+0:UTC]</DTSETTLE>
</INVTRAN>
<SECID>
<UNIQUEID>100000001</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<UNITS>43</UNITS>
<UNITPRICE>86</UNITPRICE>
<TOTAL>3698</TOTAL>
<SUBACCTSEC>CASH</SUBACCTSEC>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBUY>
</BUYOTHER>
<BUYOTHER>
<INVBUY>
<INVTRAN>
<FITID>f2a9db1d-22e8-43ea-aab9-ad9c27e40cee</FITID>
<DTTRADE>20230104000000.000[+0:UTC]</DTTRADE>
<DTSETTLE>20230105000000.000[+0:UTC]</DTSETTLE>
</INVTRAN>
<SECID>
<UNIQUEID>100000002</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<UNITS>44</UNITS>
<UNITPRICE>88</UNITPRICE>
<TOTAL>3872</TOTAL>
<SUBACCTSEC>CASH</SUBACCTSEC>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBUY>
</BUYOTHER>
</INVTRANLIST>
</INVSTMTRS>
</INVSTMTTRNRS>
</INVSTMTMSGSRSV1>
<SECLISTMSGSRSV1>
<SECLIST>
<MFINFO>
<SECINFO>
<SECID>
<UNIQUEID>100000000</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<SECNAME>AGILENT TECHNOLOGIES INC</SECNAME>
<TICKER>A</TICKER>
</SECINFO>
</MFINFO>
<MFINFO>
<SECINFO>
<SECID>
<UNIQUEID>100000001</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<SECNAME>ALCOA CORPORATION</SECNAME>
<TICKER>AA</TICKER>
</SECINFO>
</MFINFO>
<MFINFO>
<SECINFO>
<SECID>
<UNIQUEID>100000002</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<SECNAME>ARES ACQUISITION CORP</SECNAME>
<TICKER>AAC</TICKER>
</SECINFO>
</MFINFO>
</SECLIST>
</SECLISTMSGSRSV1>
</OFX>
The python code I wrote to do the conversion is:
from ofxtools.models import *
from ofxtools.Types import *
from ofxtools.utils import UTC
from decimal import Decimal
from datetime import datetime
from pprint import pprint
from ofxtools.header import make_header
import xml.etree.ElementTree as ET
import csv
import uuid
import os
import re
PATH = "History_for_Account_Y12345678.csv"
OUT_PATH = "History_for_Account_Y12345678.ofx"
HEADER = ['Run Date', 'Action', 'Symbol', 'Security Description', 'Security Type', 'Quantity', 'Price ($)', 'Commission ($)', 'Fees ($)', 'Accrued Interest ($)', 'Amount ($)', 'Settlement Date' ]
filename = os.path.basename( PATH )
filename = re.search( ".*_(.*)\.csv", filename )
acctid = filename.group(1)
def validate_file( lines ):
if lines[3] != ['Brokerage']:
print( "[!] Forth line does not contain Brokerage" )
print( lines[3] )
return False
fileHeader = lines[5]
if len( HEADER ) != len( fileHeader ):
print( "[!] Header Length Mismatch" )
return False
for column in HEADER:
if column not in fileHeader:
print( f"[!] Header Column Not Found: {column}" )
return False
return True
def extract_unique_securities( lines ):
lines = lines[1:]
uniqueSecurities = set()
identifier = 100000000
for line in lines:
if line[3].strip() != 'No Description':
uniqueSecurities.add( ( line[2].strip(), line[3].strip(), identifier ) )
identifier = identifier + 1
uniqueSecurities = list( uniqueSecurities )
securityMap = {}
for security in uniqueSecurities:
securityMap[ security[0] ] = security
# pprint( securityMap )
return securityMap
def make_security_list_message_set_response_messages( securityMap ): # SECLISTMSGSRSV1
messages = []
securityList = []
for security in securityMap.values():
secid = SECID( uniqueid = str( security[2] ), uniqueidtype = 'OTHER' )
secname = security[1]
ticker = security[0]
secinfo = SECINFO( secid = secid, secname = secname, ticker = ticker )
mfinfo = MFINFO( secinfo = secinfo )
securityList.append( mfinfo )
seclist = SECLIST( *securityList )
messages = SECLISTMSGSRSV1( seclist )
return messages
def make_investment_statement_message_set_response_messages( securityMap, transactions ):
transactionList = []
response = None
trnuid = "0"
status = STATUS( code = 0, severity = 'INFO' )
startDate = datetime( 3000, 1, 1, tzinfo = UTC )
endDate = datetime( 1970, 1, 1, tzinfo = UTC )
for transaction in transactions[1:]:
transactionDate = datetime.strptime( transaction[0].strip(), "%m/%d/%Y" ).replace( tzinfo = UTC )
if startDate > transactionDate:
startDate = transactionDate
if endDate < transactionDate:
endDate = transactionDate
description = transaction[1].strip()
symbol = transaction[2].strip() if len( transaction[2].strip() ) > 0 else None
securityType = transaction[4].strip() if len( transaction[4].strip() ) > 0 else None
quantity = float( transaction[5].strip() ) if len( transaction[5].strip() ) > 0 else None
price = float( transaction[6].strip() ) if len( transaction[6].strip() ) > 0 else None
fee = float( transaction[8].strip() ) if len( transaction[8].strip() ) > 0 else None
amount = float( transaction[10].strip() ) if len( transaction[10].strip() ) > 0 else None
settlementDate = datetime.strptime( transaction[11].strip(), "%m/%d/%Y" ).replace( tzinfo = UTC ) if len( transaction[11].strip() ) > 0 else None
# print( f"{transactionDate} {symbol} {quantity} {price} {fee} {amount} {settlementDate}" )
if symbol and amount and quantity and price and securityType == 'Cash':
if amount < 0:
invtran = INVTRAN( fitid = str( uuid.uuid4() ), dttrade = transactionDate, dtsettle = settlementDate )
secid = SECID( uniqueid = str( securityMap[ symbol ][2] ), uniqueidtype = 'OTHER' )
units = quantity
unitprice = price
fees = fee
total = amount * -1
subacctsec = 'CASH'
subacctfund = 'CASH'
invbuy = INVBUY( invtran = invtran, secid = secid, units = units, unitprice = unitprice, fees = fees, total = total, subacctsec = subacctsec, subacctfund = subacctfund )
buyother = BUYOTHER( invbuy = invbuy )
transactionList.append( buyother )
else:
print( f"[?] Not Handled {transaction}" )
invtranlist = INVTRANLIST( dtstart = startDate, dtend = endDate, *transactionList )
currentDate = datetime.now().replace( tzinfo = UTC )
invacctfrom = INVACCTFROM( brokerid = "investments.com", acctid = acctid )
invstmtrs = INVSTMTRS( dtasof = currentDate, curdef = 'USD', invacctfrom = invacctfrom, invtranlist = invtranlist )
transactionResponse = INVSTMTTRNRS( trnuid = trnuid, status = status, invstmtrs = invstmtrs )
messages = INVSTMTMSGSRSV1( transactionResponse )
return messages
def process_file( lines ):
fileHeader = r[0]
transactions = list( filter( lambda line: len( line ) > 1, lines ) )
currentDate = datetime.now().replace( tzinfo = UTC )
securityMap = extract_unique_securities( transactions )
securityMessages = make_security_list_message_set_response_messages( securityMap )
transactionMessages = make_investment_statement_message_set_response_messages( securityMap, transactions )
status = STATUS( code = 0, severity = 'INFO' )
fi = FI( org = 'Investments', fid = '1234' )
sonrs = SONRS( status = status, dtserver = currentDate, language='ENG', fi = fi )
signonmsgs = SIGNONMSGSRSV1( sonrs = sonrs )
ofx = OFX( signonmsgsrsv1 = signonmsgs, seclistmsgsrsv1 = securityMessages, invstmtmsgsrsv1 = transactionMessages )
root = ofx.to_etree()
ET.indent( root )
fileData = ET.tostring( root ).decode()
header = str( make_header( version = 220 ) )
# print( header )
# print( fileData )
with open( OUT_PATH, "w" ) as fp:
fp.write( header )
fp.write( fileData )
with open(PATH, newline='') as f:
fileLines = list( csv.reader( f ) )
if validate_file( fileLines ):
process_file( fileLines[5:] )
I know this is old, but I believe the answer is relatively simple. The only difference between QFX and OFX is those FI blocks. Their purpose is to enforce financial institutions signing up and paying Intuit for data exchange compatibility. In order to work, the org and fid need to be on the list of subscribers.
Oh, and also you need an <INTU.BID> right after the FI block. Best bet is to download a QFX file from an institution that supports Quicken and model after that.