def main():
if len(argv) < 3:
print "Usage: python make_securities.py <gnucash-file> <symbols-file>"
exit(1)
gnucash_file = argv[1]
secs = read_symbols(argv[2])
sess = Session(gnucash_file)
try:
# Make sure all the commodities exist
ct = sess.book.get_table()
created_secs = 0
updated_secs = 0
total_secs = 0
for ns in ["CUSIP", "ISIN"]:
total_secs += len(secs[ns])
for c in ct.get_commodities(ns):
matched_sec = None
for k in secs[ns]:
sec = secs[ns][k]
if sec["id"] == c.get_cusip():
matched_sec = sec
break
if matched_sec:
matched_sec["c"] = c
updated = False
if c.get_fullname() != matched_sec["name"]:
c.set_fullname(matched_sec["name"])
updated = True
if c.get_mnemonic() != matched_sec["symbol"]:
c.set_mnemonic(matched_sec["symbol"])
updated = True
if updated:
updated_secs += 1
print "DEBUG: Updating Commodity", sec["name"]
del secs[ns][matched_sec["id"]]
for k in secs[ns]:
sec = secs[ns][k]
c = GncCommodity(sess.book, sec["name"], ns, sec["symbol"], sec["id"], 10000)
if c:
ct.insert(c)
created_secs += 1
print "DEBUG: Created Commodity", sec["name"], sec["id"]
else:
print "ERROR: Error creating Commodity", sec["name"]
print "INFO:", total_secs, "commodities total,", created_secs, "created,", updated_secs, "updated."
wait_for_backup_file(gnucash_file)
sess.save()
except BaseException as e:
print "ERROR:", e
finally:
sess.end()
sess.destroy()
def _loadarchive(self):
if self._cache is not None:
return
session = Session(self.url, True, False, False)
root = session.book.get_root_account()
book = session.book
account = book.get_root_account()
commod_table = book.get_table()
pdb = book.get_price_db()
df = full_price_list(commod_table, pdb)
df['value'] = df['num'] * 1.0 / df['denom']
session.end()
session.destroy()
self._cache = dropdupe(df, ['date', 'namespace', 'commod'])
class DenhacGncSession:
_path = None
_session = None
_root = None
_commod = None
_currency = None
def __init__(self, path):
if os.path.exists(path + '.LCK'):
raise AssertionError("""Lock file exists. Is GNUCash running?\n""")
self._path = path
with warnings.catch_warnings():
warnings.simplefilter("ignore")
self._session = Session(path, is_new = False)
self._root = self.getBook().get_root_account()
self._commod = self.getBook().get_table()
self._currency = self._commod.lookup('CURRENCY', 'USD')
def saveAndEndSession(self):
self._session.save()
self._session.end()
def saveSession(self):
self._session.save()
def endSession(self):
self._session.end()
def cancelSession(self):
self._session.end()
self._session.destroy()
def getBook(self):
return self._session.book
def getRoot(self):
return self._session.book.get_root_account()
def getCurrency(self):
return self._currency
def prepare_session(self):
"""
initialization needed for a Gnucash session
:return: message
"""
self.logger.print_info("prepare_session()", BLUE)
msg = TEST
try:
session = Session(self.gnc_file)
self.book = session.book
owner = self.monarch_record.get_owner()
self.logger.print_info("Owner = {}".format(owner), GREEN)
self.set_gnc_rec(InvestmentRecord(owner))
self.create_gnucash_info()
if self.mode == PROD:
self.logger.print_info("Mode = {}: COMMIT Price DB edits and Save session.".format(self.mode), GREEN)
if self.domain != TRADE:
self.price_db.commit_edit()
# only ONE session save for the entire run
session.save()
session.end()
session.destroy()
msg = self.logger.get_log()
except Exception as se:
msg = "prepare_session() EXCEPTION!! '{}'".format(repr(se))
self.logger.print_error(msg)
if "session" in locals() and session is not None:
session.end()
session.destroy()
raise se
return msg
def prepare_session(self):
"""
Take the information from a transaction collection and produce Gnucash transactions to write to a Gnucash file
:return: message
"""
print_info("prepare_session()", MAGENTA)
msg = TEST
try:
session = Session(self.gnc_file)
self.book = session.book
print_info("Owner = {}".format(self.tx_coll[OWNER]), GREEN)
self.report_info = InvestmentRecord(self.tx_coll[OWNER])
self.create_gnucash_info()
if self.mode == PROD:
msg = "Mode = {}: COMMIT Price DB edits and Save session.".format(
self.mode)
print_info(msg, GREEN)
self.price_db.commit_edit()
# only ONE session save for the entire run
session.save()
session.end()
session.destroy()
except Exception as e:
msg = "prepare_session() EXCEPTION!! '{}'".format(repr(e))
print_error(msg)
if "session" in locals() and session is not None:
session.end()
session.destroy()
raise
return msg
print('Need at least one Split to get currency info ... ')
raise SystemExit
cur = ac.GetSplitList()[0].GetParent().GetCurrency()
# Get stock data
pl = pdb.get_prices(stock,cur)
if len(pl)<1:
print('Need at least one database entry to clone ...')
raise SystemExit
pl0 = pl[0]
for i in range(1,len(pl)):
pdb.remove_price(pl[i])
for i in range(0,len(stock_date)):
p_new = pl0.clone(book)
p_new = gnucash.GncPrice(instance=p_new)
print('Adding',i,stock_date[i],stock_price[i])
p_new.set_time(stock_date[i])
v = p_new.get_value()
v.num = int(Fraction.from_float(stock_price[i]).limit_denominator(100000).numerator)
v.denom = int(Fraction.from_float(stock_price[i]).limit_denominator(100000).denominator)
p_new.set_value(v)
p_new.set_source("Finance::Quotes::Historic")
pdb.add_price(p_new)
# Clean up
session.save()
session.end()
session.destroy()
trans.CommitEdit()
tx_guid = trans.GetGUID().to_string()
tx = models.ImportedTransaction()
tx.account_guid = acct_guid
tx.tx_guid = tx_guid
tx.source_tx_id = txinfo['sourceId']
imported_transactions.append(tx)
u = models.Update()
u.account_guid = acct_guid
u.updated = datetime.utcnow()
u.balance = balance
u.save()
for tx in imported_transactions:
tx.update = u
tx.save()
f.close()
finally:
debug_print('Ending GnuCash session')
session.end()
debug_print('Destroying GnuCash session')
session.destroy()
debug_print('Destroyed GnuCash session')
debug_print('Done importing JSON file(s)')
class MonarchQrepToGncPrices:
def __init__(self, fmon, gnc_file, mode):
self.prod = (mode == PROD)
self.mon_file = fmon
self.session = Session(gnc_file)
self.book = self.session.book
self.root = self.book.get_root_account()
self.root.get_instance()
self.price_db = self.book.get_price_db()
commod_tab = self.book.get_table()
self.currency = commod_tab.lookup("ISO4217", "CAD")
def parse_monarch_qtrep(self):
"""
PARSE FOR PRICES TO ADD TO THE PRICE DB
loop:
find: 'For the Period <date1> to <date2>' for the date for the prices
find: MON_MARK or MON_LULU as OWNER
find: 'Page 1' as the key to start finding prices
find: 'OPEN...' or 'TFSA...' or 'RRSP...' as Plan Type
use that as the key for this section of the Tx_Collection
find: '(match1) - (match2) (match3)...'
1) use match1 as Fund Company, match2 as Fund Code for the account
2) use match3 as Fund Company, match2 as Fund Code for the account
find: '$price'
find: 'Transaction Details' as key to search for next Plan Type
OR: another match of 'Fund Company & Fund Code'
:return: Configuration.InvestmentRecord object
"""
print_info("parse_monarch_qtrep()\nRuntime = {}\n".format(strnow), MAGENTA)
# re searches
re_date = re.compile(r"^For the period (.*) to (\w{3}) (\d{1,2}), (\d{4})")
re_mark = re.compile(".*({}).*".format(MON_MARK))
re_lulu = re.compile(".*({}).*".format(MON_LULU))
re_start = re.compile(r"^Page 1.*")
re_comp1 = re.compile(r"^(.*) - ([0-9ATL]{3,5}).*")
re_comp2 = re.compile(r"^(- )?(\d{3,5}) - (.*)")
re_price = re.compile(r"^\$([0-9,]{1,5})\.(\d{2,4}).*")
re_plan = re.compile(r"(OPEN|TFSA|RRSP)(\s?.*)")
re_endplan = re.compile(r"^Transaction Details.*")
re_finish = re.compile(r"^Disclosure.*")
mon_state = FIND_OWNER
with open(self.mon_file) as fp:
ct = 0
for line in fp:
ct += 1
if mon_state == FIND_OWNER:
match_mark = re.match(re_mark, line)
match_lulu = re.match(re_lulu, line)
if match_mark or match_lulu:
if match_mark:
owner = match_mark.group(1)
elif match_lulu:
owner = match_lulu.group(1)
print_info("{}/ Owner: {}".format(ct, owner), RED)
tx_coll = InvestmentRecord(owner)
mon_state = FIND_START
continue
if mon_state == FIND_START:
match_start = re.match(re_start, line)
if match_start:
print_info("{}/ Found Start!".format(ct), GREEN)
mon_state = FIND_DATE
continue
if mon_state == FIND_DATE:
match_date = re.match(re_date, line)
if match_date:
day = match_date.group(3)
month = match_date.group(2)
year = match_date.group(4)
datestring = "{}-{}-{}".format(year, month, day)
pr_date = dt.strptime(datestring, '%Y-%b-%d')
tx_coll.set_date(pr_date)
print_info("date: {}".format(pr_date), CYAN)
mon_state = FIND_PLAN
continue
if mon_state == FIND_PLAN:
match_finish = re.match(re_finish, line)
if match_finish:
print_info("{}/ FINISHED!".format(ct), RED)
break
match_plan = re.match(re_plan, line)
if match_plan:
plan_type = match_plan.group(1)
print_info("{}/ Plan type: {}".format(ct, plan_type), BLUE)
mon_state = FIND_COMPANY
continue
if mon_state == FIND_COMPANY:
match_endsum = re.match(re_endplan, line)
if match_endsum:
print_info("{}/ END of '{}' plan.".format(ct, plan_type), BLUE)
mon_state = FIND_PLAN
continue
match_comp1 = re.match(re_comp1, line)
match_comp2 = re.match(re_comp2, line)
if match_comp1 or match_comp2:
if match_comp1:
company = match_comp1.group(1)
fund_code = match_comp1.group(2)
elif match_comp2:
company = match_comp2.group(3)
fund_code = match_comp2.group(2)
curr_tx = {FUND_CMPY: company, FUND_CODE: fund_code}
print_info("{}/ Fund is: '{}:{}'".format(ct, company, fund_code), MAGENTA)
mon_state = FIND_PRICE
continue
if mon_state == FIND_PRICE:
match_price = re.match(re_price, line)
if match_price:
dollar_str = match_price.group(1)
cents_str = match_price.group(2)
print_info("{}/ price = '${}.{}'".format(ct, dollar_str, cents_str), GREEN)
curr_tx[DOLLARS] = dollar_str
curr_tx[CENTS] = cents_str
tx_coll.add_tx(plan_type, curr_tx)
mon_state = FIND_COMPANY
continue
print_info("Found {} transactions.".format(tx_coll.get_size()))
return tx_coll
def get_prices_and_save(self, tx_coll):
"""
create Gnucash prices, load and save to the Gnucash file's PriceDB
:param tx_coll: InvestmentRecord object: transactions to use to extract Gnucash prices
:return: message
"""
print_info('get_prices_and_save()', MAGENTA)
gncu = GncUtilities()
msg = TEST
self.price_db.begin_edit()
print_info("self.price_db.begin_edit()", MAGENTA)
try:
for plan_type in tx_coll.plans:
print_info("\n\nPlan type = {}".format(plan_type))
for tx in tx_coll.plans[plan_type]:
base = pow(10, len(tx[CENTS]))
int_price = int(tx[DOLLARS] + tx[CENTS])
val = GncNumeric(int_price, base)
ast_parent_path = copy.copy(ACCT_PATHS[ASSET])
ast_parent_path.append(plan_type)
if plan_type != PL_OPEN:
if tx_coll.get_owner() == UNKNOWN:
raise Exception("PROBLEM!! Trying to process plan type '{}' but NO Owner information found"
" in Tx Collection!!".format(plan_type))
ast_parent_path.append(ACCT_PATHS[tx_coll.get_owner()])
print_info("ast_parent_path = {}".format(str(ast_parent_path)), BLUE)
asset_parent = gncu.account_from_path(self.root, ast_parent_path)
# get the asset account name
name_key = tx[FUND_CMPY].split(' ')[0]
print_info("name_key = {}".format(name_key), YELLOW)
if name_key in FUND_NAME_CODE.keys():
name_code = FUND_NAME_CODE[name_key]
# special case
if name_code == ATL:
asset_acct_name = ATL_O59
else:
asset_acct_name = name_code + " " + tx[FUND_CODE]
else:
raise Exception("Could NOT find name key {}!".format(name_key))
print_info("asset_acct_name = {}".format(asset_acct_name), BLUE)
# special location for Trust Asset account
if asset_acct_name == TRUST_AST_ACCT:
asset_parent = self.root.lookup_by_name(TRUST)
print_info("asset_parent = {}".format(asset_parent.GetName()), BLUE)
# get the asset account
asset_acct = asset_parent.lookup_by_name(asset_acct_name)
if asset_acct is None:
# just skip updating cash-holding funds
if str(val) == '100000/10000':
continue
else:
raise Exception(
"Could NOT find acct '{}' under parent '{}'".format(asset_acct_name, asset_parent.GetName()))
print_info("Adding: {}[{}] @ ${}".format(asset_acct_name, tx_coll.get_date_str(), val), GREEN)
pr = GncPrice(self.book)
pr.begin_edit()
pr.set_time64(tx_coll.get_date())
comm = asset_acct.GetCommodity()
print_info("Commodity = {}:{}".format(comm.get_namespace(), comm.get_printname()), YELLOW)
pr.set_commodity(comm)
pr.set_currency(self.currency)
pr.set_value(val)
pr.set_source_string("user:price")
pr.set_typestr('last')
pr.commit_edit()
if self.prod:
print_info("PROD: Add Price to DB.\n", GREEN)
self.price_db.add_price(pr)
else:
print_info("PROD: ABANDON Prices!\n", RED)
if self.prod:
msg = "PROD: COMMIT Price DB edits and Save session."
print_info("PROD: COMMIT Price DB edits and Save session.", GREEN)
self.price_db.commit_edit()
# only ONE session save for the entire run
self.session.save()
self.session.end()
self.session.destroy()
except Exception as e:
msg = "get_prices_and_save() EXCEPTION!! '{}'".format(repr(e))
print_error(msg)
if "session" in locals() and self.session is not None:
self.session.end()
self.session.destroy()
raise
return msg
def main():
if len(argv) < 4:
print 'Usage: python ofxml_make_commodity_accounts.py <gnucash-file> <ofxml-file> <accountmap-file>'
exit(1)
gnucash_file=argv[1]
doc=ElementTree(file=argv[2])
acctids=doc.findall('./INVSTMTMSGSRSV1/INVSTMTTRNRS/INVSTMTRS/INVACCTFROM/ACCTID')
if len(acctids)!=1:
print 'ERROR: No unique account number found in OFX: found', len(acctids)
return
acctid=acctids[0].text.strip()
acctcur='any'
m=re.search('^(.*)-([A-Z]{3})$',acctid)
if m:
acctid=m.group(1)
acctcur=m.group(2)
print "INFO: Account number:", acctid, "Currency:", acctcur
fitids={}
for itran in doc.findall('.//INVBUY')+doc.findall('.//INVSELL')+doc.findall('.//REINVEST'):
fitid=itran.find('./INVTRAN/FITID')
if not fitid is None:
fitid=fitid.text.strip()
if fitid in fitids:
print "ERROR: Non-unique FITID found:", fitid
exit(1)
fitids[fitid]=itran
# Fantastic, the FITID is not saved by GnuCash for income transactions...
# Index by (date,amount,memo) instead
incometrns={}
for itran in doc.findall('.//INCOME'):
fields={}
for path in ('INVTRAN/DTTRADE', 'INVTRAN/MEMO', 'TOTAL'):
el=itran.find('./'+path)
if not el is None and len(el.text.strip())>0:
fields[path]=el.text.strip()
if len(fields)!=3:
print "ERROR: Can't create identifier for INCOME transaction, ignoring."
incometrns[(fields['INVTRAN/DTTRADE'][0:8],fields['INVTRAN/MEMO'],Fraction(fields['TOTAL']))]=itran
sess=Session(gnucash_file)
try:
# Find GNC parent account
root=sess.book.get_root_account()
matched_accts=find_acct_by_number_and_currency(root,acctid,acctcur)
if len(matched_accts)==0:
from_iban=conv_iban(acctid)
if from_iban:
matched_accts=find_acct_by_number_and_currency(root,from_iban,acctcur)
if len(matched_accts)!=1:
print 'ERROR: No unique account this number/currency; found', len(matched_accts)
return
acct=matched_accts[0]
print 'DEBUG: Found parent account:',acct.GetName()
accountmap=read_accountmap(argv[3],acct.GetCode()+'-'+acct.GetCommodity().get_mnemonic())
# Find child Stock/Mutual accounts
secaccts=[] # SwigPyObject is not Hashable :(
for cacct in acct.get_descendants():
atype=cacct.GetType()
if atype==gnucash_core.ACCT_TYPE_STOCK or atype==gnucash_core.ACCT_TYPE_MUTUAL:
secaccts.append(cacct.get_instance())
# Find income accounts
incaccts=[] # SwigPyObject is not Hashable :(
for typ in accountmap:
if typ[0:6]=="INCOME":
inst=find_acct_by_path(root,accountmap[typ]).get_instance()
if not (inst is None or inst in incaccts):
incaccts.append(inst)
if len(incaccts)==0 and len(incometrns)>0:
print 'WARNING: no income accounts defined for account',acct.GetCode()+'-'+acct.GetCommodity().get_mnemonic()
print 'WARNING: income transactions will not be fixed'
# Go through all transactions
for tran in _py_xaccSplitListGetUniqueTransactions(acct.GetSplitList()):
# Consider fixing if transaction ...
# ... has exactly 2 splits
# ... has 1 split with a child Stock/Mutual account
# ... has 1 split with an online ID
splits=tran.GetSplitList()
if len(splits)==2:
cashsplit=None
secsplit=None
incsplit=None
online_id=None
for split in splits:
if split.GetAccount().get_instance() in secaccts:
secsplit=split
if split.GetAccount().get_instance() in incaccts:
incsplit=split
if split.GetAccount().get_instance()==acct.get_instance():
cashsplit=split
oid=_py_gnc_import_get_split_online_id(sess,split)
if not oid is None:
if online_id is None:
online_id=oid
else:
online_id=False
if not (cashsplit is None or secsplit is None or online_id is None or online_id is False):
if not online_id in fitids:
# This can happen if we encounter a transaction outside of this OFX period
#print 'DEBUG: FITID',online_id,'not found in OFX file.'
continue
fix_buysell_transaction(tran,secsplit,cashsplit,fitids[online_id],root,accountmap)
elif not (cashsplit is None or incsplit is None):
date=tran.RetDatePostedTS().strftime('%Y%m%d')
memo=re.sub(' +',' ',tran.GetDescription()) # GnuCash importer likes to insert spaces randomly
amt=gnc_numeric_to_fraction(cashsplit.GetAmount())
if not (date,memo,amt) in incometrns:
# This can happen if we encounter a transaction outside of this OFX period
#print "DEBUG: No match for income transaction",date,memo,amt
continue
fix_income_transaction(tran,incsplit,cashsplit,incometrns[(date,memo,amt)],root,accountmap)
wait_for_backup_file(gnucash_file)
sess.save()
except BaseException as e:
print 'ERROR:',e
finally:
sess.end()
sess.destroy()
def main():
if len(argv) < 3:
print 'Usage: python ofxml_make_commodity_accounts.py <gnucash-file> <ofxml-file> [symbols-file]'
exit(1)
gnucash_file=argv[1]
symbols_file=False
if len(argv)>=4:
symbols_file=read_symbols(argv[3])
doc=ElementTree(file=argv[2])
acctids=doc.findall('./INVSTMTMSGSRSV1/INVSTMTTRNRS/INVSTMTRS/INVACCTFROM/ACCTID')
if len(acctids)!=1:
print 'ERROR: No unique account number found in OFX: found', len(acctids)
return
acctid=acctids[0].text.strip()
acctcur='any'
m=re.search('^(.*)-([A-Z]{3})$',acctid)
if m:
acctid=m.group(1)
acctcur=m.group(2)
print "INFO: Account number:", acctid, "Currency:", acctcur
missing_symbols=False
secs=[]
for sec in doc.findall('./SECLISTMSGSRSV1/SECLIST/*/SECINFO'):
id=sec.findall('./SECID/UNIQUEID')[0].text.strip()
type=sec.findall('./SECID/UNIQUEIDTYPE')[0].text.strip()
name=sec.findall('./SECNAME')[0].text.strip()
symbol=sec.findall('./TICKER')
if len(symbol):
symbol=symbol[0].text.strip()
else:
symbol=None
if symbols_file:
if id in symbols_file[type]:
name=symbols_file[type][id]['name']
symbol=symbols_file[type][id]['symbol']
else:
print "WARNING: Missing symbol for", type, id, name, symbol
missing_symbols=True
secs.append({'id': id, 'type': type, 'name': name, 'symbol': symbol})
print "DEBUG: Found", len(secs), "commodities."
sess=Session(gnucash_file)
try:
# Make sure all the commodities exist
ct=sess.book.get_table()
for ns in ['CUSIP','ISIN']:
for c in ct.get_commodities(ns):
matched_sec=None
for sec in secs:
if sec['type']==ns and sec['id']==c.get_cusip():
sec['c']=c
break
missing_secs=False
for i,sec in enumerate(secs):
if not 'c' in sec:
print 'WARNING: Missing commodity', sec['type'],sec['id'],sec['name'],sec['symbol']
missing_secs=True
if missing_secs or missing_symbols:
print 'ERROR: Missing symbols or commodities, aborting.'
return
# Find GNC parent account
root=sess.book.get_root_account()
matched_accts=find_acct_by_number_and_currency(root,acctid,acctcur)
if len(matched_accts)==0:
from_iban=conv_iban(acctid)
if from_iban:
matched_accts=find_acct_by_number_and_currency(root,from_iban,acctcur)
if len(matched_accts)!=1:
print 'ERROR: No unique account this number/currency; found', len(matched_accts)
return
acct=matched_accts[0]
print 'DEBUG: Found parent account:',acct.GetName()
# Make sure the account has the appropriate stock accounts
created_accounts=0
for sec in secs:
matched_acct=None
for secacct in acct.get_children():
if secacct.GetCommodity().get_instance()==sec['c'].get_instance():
matched_acct=secacct
break
if not matched_acct:
secacct=Account(sess.book)
if secacct:
secacct.SetName(sec['name'])
secacct.SetType(gnucash.ACCT_TYPE_STOCK)
secacct.SetCommodity(sec['c'])
secacct.SetCode(sec['id'])
acct.append_child(secacct)
created_accounts+=1
print 'DEBUG: Created Account',sec['name']
else:
print 'ERROR: Error creating Account',sec['name']
print 'INFO:',len(secs),'accounts total',created_accounts,'created.'
wait_for_backup_file(gnucash_file)
sess.save()
except BaseException as e:
print 'ERROR:',e
finally:
sess.end()
sess.destroy()
