So I didn't get the CSV import tool to work. However I was able to
write a python script to do the import. I would think that this would
be possible with the CSV import, but I can't seem to figure out how to
do the right column assignments to make it work. I have attached the
python script and a sample input CSV file.
If someone that knows how the CSV import code works and can compare
with my script and tell me how to use the CSV import tool for this,
that would be great.
Thanks,
Jon
On Tue, 2022-07-12 at 08:45 -0500, Jon Schewe wrote:
> That looks similar to what I'm doing, except all of my data is on a
> single line in the CSV file. The biggest problem seems to be how to
> convince GnuCash to use 2 different commodities for the transaction
> when importing from CSV.
>
>
> On Tue, 2022-07-12 at 13:04 +1000, Geoff wrote:
> > Hi Jon
> >
> > This isn't exactly what you are asking for, but it may give you
> > some
> > clues. Importing Dividends from CSV:-
> >
> > https://lists.gnucash.org/pipermail/gnucash-user/2020-August/092768.html
> >
> >
> > Good luck!
> >
> > Geoff
> > =====
> >
> > On 12/07/2022 12:35 pm, Jon Schewe wrote:
> > > Does anyone have an example of importing stock transactions from
> > > CSV?
> > >
> > > I have matched columns to Date, Description, Account, Price,
> > > Deposit,
> > > Transfer Account.
> > > The "Account" column is the mutual fund account.
> > > The "Transfer Account" column is the currency account.
> > >
> > > When I start the import process all of my transactions need an
> > > account
> > > to transfer from, despite specifying the Transfer Account. When I
> > > try
> > > and select my currency account I'm told that it has the wrong
> > > commodity.
> > >
> > > I have another column for the currency amount, but I don't know
> > > what to
> > > map that to in the importer.
> > >
> > > I tried checkout out
> > > https://www.gnucash.org/docs/v4/C/gnucash-help/trans-import.html#trans-import-csv
> > >
> > > but it doesn't seem to address this.
> > > Suggestions on where I'm going wrong.
> > >
> > > Jon
> > >
> > >
> > > _______________________________________________
> > > gnucash-user mailing list
> > > gnucash-user@gnucash.org
> > >
> > > To update your subscription preferences or to unsubscribe:
> > > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > >
> > > If you are using Nabble or Gmane, please see
> > > https://wiki.gnucash.org/wiki/Mailing_Lists
> > > for more information.
> > > -----
> > > Please remember to CC this list on all your replies.
> > > You can do this by using Reply-To-List or Reply-All.
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
#!/usr/bin/env python3
import warnings
with warnings.catch_warnings():
import re
import sys
import argparse
import os
import logging
import logging.config
import json
from pathlib import Path
import gnucash
import itertools
import functools
import csv
import datetime
SCRIPT_DIR=Path(__file__).parent.absolute()
def get_logger():
return logging.getLogger(__name__)
def setup_logging(
default_path='logging.json',
default_level=logging.INFO,
env_key='LOG_CFG'
):
"""
Setup logging configuration
"""
try:
path = Path(default_path)
value = os.getenv(env_key, None)
if value:
path = Path(value)
if path.exists():
with open(path, 'r') as f:
config = json.load(f)
logging.config.dictConfig(config)
else:
logging.basicConfig(level=default_level)
except:
print(f"Error configuring logging, using default configuration with level {default_level}")
logging.basicConfig(level=default_level)
@functools.cache
def find_commodity(book, fund_name : str):
"""
Arguments:
book: GnuCash book
fund_name: name of fund to find as substring of commodity full name
Returns:
list: All commodities that match the name
"""
comm_table = book.get_table()
# chain and * flatten the list of lists
return list(itertools.chain(*[ [x for x in comm_table.get_commodities(ns) if fund_name in x.get_fullname() ] for ns in comm_table.get_namespaces() ]))
def get_usd(book):
currencies = book.get_table().find_namespace('CURRENCY')
possible_usd = [ x for x in currencies.get_commodity_list() if x.get_mnemonic() == 'USD' ]
if len(possible_usd) < 1:
raise RuntimeError("Cannot find USD")
if len(possible_usd) > 1:
raise RuntimeError(f"Found multiple USD currencies: {[x.get_full_name() for x in possible_usd]}")
return possible_usd[0]
def search_for_subaccount(parent_account, name_substring):
"""
Arguments:
parent_account : parent GnuCash account, all descendants will be checked
name_substring : partial name to search for
Returns:
list : accounts that have the specified substring in their name
"""
return [ x for x in parent_account.get_descendants() if name_substring in x.GetName() ]
def main_method(args):
# TODO make account names arguments
expense_account_name = 'Expenses'
retirement_account_name = 'Raytheon BBN 401k'
with gnucash.Session(args.book, gnucash.SessionOpenMode.SESSION_NORMAL_OPEN) as session, open(args.csv, 'r') as csv_file:
expense_account = session.book.get_root_account().lookup_by_name(expense_account_name)
if expense_account is None:
raise RuntimeError(f"Unable to find expenses account: '{expense_account_name}'")
retirement_account = session.book.get_root_account().lookup_by_name(retirement_account_name)
if retirement_account is None:
raise RuntimeError(f"Unable to find retirement account: '{retirement_account}'")
usd_commodity = get_usd(session.book)
get_logger().debug("Found USD: %s", usd_commodity.get_fullname())
csv_reader = csv.DictReader(csv_file)
for row in csv_reader:
get_logger().debug("---- Processing transaction ---")
date_str = row['VALUATION DATE']
date = datetime.datetime.strptime(date_str, '%m-%d-%Y')
description = row['ACTIVITY TYPE']
fund_name = row['FUND']
amount_dollar = float(row['AMOUNT'])
amount_fund = float(row['FUND UNITS'])
# amount_dollar / amount_fund - should have gnucash compute the price
price = float(row['FUND NAV/PRICE'])
amount_dollar_gnc = gnucash.GncNumeric(amount_dollar)
amount_fund_gnc = gnucash.GncNumeric(amount_fund)
price_gnc = amount_dollar_gnc.div(amount_fund_gnc, gnucash.GNC_DENOM_AUTO, gnucash.GNC_HOW_DENOM_LCD)
get_logger().debug("amount_dollar: %s amount_fund: %s price: %s", amount_dollar_gnc, amount_fund_gnc, price_gnc)
possible_account_funds = search_for_subaccount(retirement_account, fund_name)
if len(possible_account_funds) < 1:
get_logger().error("Cannot find retirement account with name similar to '%s'", fund_name)
continue
if len(possible_account_funds) > 1:
get_logger().error("Found multiple retirement accounts with name similar to '%s'", fund_name)
continue
fund_account = possible_account_funds[0]
get_logger().debug("Found fund account '%s'", fund_account.GetName())
possible_commodities = find_commodity(session.book, fund_name)
if len(possible_commodities) < 1:
get_logger().error("Cannot find fund '%s'", fund_name)
continue
if len(possible_commodities) > 1:
get_logger().error("Found multiple funds for '%s': %s", fund_name, [x.get_fullname() for x in possible_commodities])
continue
fund_commodity = possible_commodities[0]
get_logger().debug("Found commodity '%s' for '%s'", fund_commodity.get_fullname(), fund_name)
if not fund_commodity.equal(fund_account.GetCommodity()):
get_logger().error("Fund commodity doesn't match '%s' != '%s'", fund_commodity.get_fullname(), fund_account.GetCommodity().get_fullname())
continue
if amount_dollar < 0:
source_account = expense_account
else:
source_account = retirement_account
get_logger().debug("Source account is '%s'", source_account.GetName())
if not usd_commodity.equal(source_account.GetCommodity()):
get_logger().error("Source commodity doesn't match '%s' != '%s'", usd_commodity.get_fullname(), source_account.GetCommodity().get_fullname())
continue
# create split
transaction = gnucash.Transaction(session.book)
transaction.BeginEdit()
transaction.SetDate(date.day, date.month, date.year)
transaction.SetCurrency(usd_commodity)
transaction.SetDescription(description)
# source split
source_split = gnucash.Split(session.book)
source_split.SetParent(transaction)
source_split.SetReconcile('c')
source_split.SetValue(amount_dollar_gnc.neg())
source_split.SetAmount(amount_dollar_gnc.neg())
source_split.SetAccount(source_account)
# fund split
fund_split = gnucash.Split(session.book)
fund_split.SetParent(transaction)
fund_split.SetReconcile('c')
fund_split.SetValue(amount_dollar_gnc)
fund_split.SetAmount(amount_fund_gnc)
fund_split.SetAccount(fund_account)
transaction.CommitEdit()
get_logger().info("Finished with import")
def main(argv=None):
if argv is None:
argv = sys.argv[1:]
class ArgumentParserWithDefaults(argparse.ArgumentParser):
"""
From https://stackoverflow.com/questions/12151306/argparse-way-to-include-default-values-in-help
"""
def add_argument(self, *args, help=None, default=None, **kwargs):
if help is not None:
kwargs['help'] = help
if default is not None and args[0] != '-h':
kwargs['default'] = default
if help is not None:
kwargs['help'] += ' (default: {})'.format(default)
super().add_argument(*args, **kwargs)
parser = ArgumentParserWithDefaults(formatter_class=argparse.RawTextHelpFormatter)
parser.add_argument("-l", "--logconfig", dest="logconfig", help="logging configuration (default: logging.json)", default='logging.json')
parser.add_argument("--debug", dest="debug", help="Enable interactive debugger on error", action='store_true')
parser.add_argument("--book", dest="book", help="GnuCash file to read", required=True)
parser.add_argument("--csv", dest="csv", help="CSV file from Alight to read", required=True)
args = parser.parse_args(argv)
setup_logging(default_path=args.logconfig)
if 'multiprocessing' in sys.modules:
# requires the multiprocessing-logging module - see https://github.com/jruere/multiprocessing-logging
import multiprocessing_logging
multiprocessing_logging.install_mp_handler()
if args.debug:
import pdb, traceback
try:
return main_method(args)
except:
extype, value, tb = sys.exc_info()
traceback.print_exc()
pdb.post_mortem(tb)
else:
return main_method(args)
if __name__ == "__main__":
sys.exit(main())
VALUATION DATE,POSTING DATE,ACTIVITY TYPE,PLAN,ACCOUNT,FUND,AMOUNT,FUND NAV/PRICE,FUND UNITS,Transfer Account,Currency,Fund Commodity
06-17-2022,06-17-2022,Account Maintenance Fee (RAYSIP),Savings and Investment Plan,Company Match,Equity Fund (S&P 500),-0.12,7.766721,-0.015451,Expenses,USD,Alight Equity Fund (S&P 500)
06-17-2022,06-17-2022,Company Match,Savings and Investment Plan,Company Match,Equity Fund (S&P 500),67.78,7.766721,8.726978,401k,USD,Alight Equity Fund (S&P 500)
_______________________________________________
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
