GnuCash  4.11-243-g1cac132214+
new_book_with_opening_balances.py
Go to the documentation of this file.
1 #!/usr/bin/env python3
2 
3 # new_book_with_opening_balances.py -- Replicate the account structure of a
4 # book and apply basis opening balances from the original
5 #
6 # Copyright (C) 2009, 2010 ParIT Worker Co-operative <transparency@parit.ca>
7 # This program is free software; you can redistribute it and/or
8 # modify it under the terms of the GNU General Public License as
9 # published by the Free Software Foundation; either version 2 of
10 # the License, or (at your option) any later version.
11 #
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
16 #
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, contact:
19 # Free Software Foundation Voice: +1-617-542-5942
20 # 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652
21 # Boston, MA 02110-1301, USA gnu@gnu.org
22 #
23 # @author Mark Jenkins, ParIT Worker Co-operative <mark@parit.ca>
24 
25 
30 
31 from gnucash import (
32  Session, Account, Transaction, Split, GncNumeric, SessionOpenMode)
33 from gnucash.gnucash_core_c import \
34  GNC_DENOM_AUTO, GNC_HOW_DENOM_EXACT, \
35  ACCT_TYPE_ASSET, ACCT_TYPE_BANK, ACCT_TYPE_CASH, ACCT_TYPE_CHECKING, \
36  ACCT_TYPE_CREDIT, ACCT_TYPE_EQUITY, ACCT_TYPE_EXPENSE, ACCT_TYPE_INCOME, \
37  ACCT_TYPE_LIABILITY, ACCT_TYPE_MUTUAL, ACCT_TYPE_PAYABLE, \
38  ACCT_TYPE_RECEIVABLE, ACCT_TYPE_STOCK, ACCT_TYPE_ROOT, ACCT_TYPE_TRADING
39 
40 from sys import argv
41 from os.path import abspath
42 from datetime import date
43 
44 # This script takes a gnucash url
45 # and creates a new file/db at a second url that has the same
46 # account tree and an equivalent opening balance on all the simple balance
47 # sheet accounts (not for income and expense accounts)
48 #
49 # This is done a per currency basis, one opening balance account for each
50 #
51 # For non-simple balance sheet accounts (like payable, recievable, stock,
52 # mutual, and trading, you'll have to put the opening balance in yourself
53 #
54 # Invocation examples:
55 # python3 new_book_with_opening_balances.py \
56 # '/home/mark/test.gnucash'
57 # 'sqlite3:///home/mark/new_test.gnucash'
58 #
59 # python3 new_book_with_opening_balances.py \
60 # '/home/mark/test.gnucash' \
61 # 'xml:///crypthome/mark/parit-financial-system/new_test.gnucash'
62 #
63 # Remember that the gnucash python package has to be in your PYTHONPATH
64 # if you're installed GnuCash in a non-standard location, you'll have to do
65 # something like this
66 # export PYTHONPATH=gnucash_install_path/lib/python2.x/site-packages/
67 
68 # argv[1] should be the path to an existing gnucash file/database
69 # For a file, simply pass the pathname. GnuCash will determine the data format
70 # xml or sqlite3 automatically.
71 # For a database you can use these forms:
72 # mysql://user:password@host/dbname
73 # postgres://user:password@host[:port]/dbname (the port is optional)
74 #
75 # argv[2] should be the path for the new gnucash file/database
76 # For a file, simply pass the pathname prefixed with the requested data format
77 # like:
78 # xml:///home/blah/blah.gnucash
79 # sqlite3:///home/blah/blah.gnucash
80 # Paths can also be relative, for example:
81 # xml://from-here/to/there/blah.gnucash
82 # For a database you can use these forms:
83 # mysql://user:password@host/dbname
84 # postgres://user:password@host[:port]/dbname (the port is optional)
85 
86 
87 OPENING_DATE = (1, 1, 2011) # day, month, year
88 
89 # possible account types of interest for opening balances
90 ACCOUNT_TYPES_TO_OPEN = set( (
91  ACCT_TYPE_BANK,
92  ACCT_TYPE_CASH,
93  ACCT_TYPE_CREDIT,
94  ACCT_TYPE_ASSET,
95  ACCT_TYPE_LIABILITY,
96  ACCT_TYPE_STOCK,
97  ACCT_TYPE_MUTUAL,
98  ACCT_TYPE_INCOME,
99  ACCT_TYPE_EXPENSE,
100  ACCT_TYPE_EQUITY,
101  ACCT_TYPE_RECEIVABLE,
102  ACCT_TYPE_PAYABLE,
103  ACCT_TYPE_TRADING,
104 ))
105 
106 # You don't need an opening balance for income and expenses, past income
107 # and expenses should be in Equity->retained earnings
108 # so we remove them from the above set
109 ACCOUNT_TYPES_TO_OPEN = ACCOUNT_TYPES_TO_OPEN.difference( set((
110  ACCT_TYPE_INCOME,
111  ACCT_TYPE_EXPENSE,
112  )) )
113 
114 # This script isn't capable of properly creating the lots required for
115 # STOCK, MUTUAL, RECEIVABLE, and PAYABLE -- you'll have to create opening
116 # balances for them manually; so they are not included in the set for
117 # opening balances
118 ACCOUNT_TYPES_TO_OPEN = ACCOUNT_TYPES_TO_OPEN.difference( set((
119  ACCT_TYPE_STOCK,
120  ACCT_TYPE_MUTUAL,
121  ACCT_TYPE_RECEIVABLE,
122  ACCT_TYPE_PAYABLE,
123  )) )
124 
125 # this script isn't capable of properly setting up the transactions for
126 # ACCT_TYPE_TRADING, you'll have to create opening balances for them manually;
127 # so, they are not included in the set of accounts used for opening balances
128 ACCOUNT_TYPES_TO_OPEN.remove(ACCT_TYPE_TRADING)
129 
130 OPENING_BALANCE_ACCOUNT = ( 'Equity', 'Opening Balances')
131 
132 # if possible, this program will try to use the account above for the
133 # currency listed below, and a variation of the above
134 # Equity->"Opening Balances Symbol" for all other currencies
135 PREFERED_CURRENCY_FOR_SIMPLE_OPENING_BALANCE = ("CURRENCY", "CAD")
136 
137 def initialize_split(book, value, account, trans):
138  split = Split(book)
139  split.SetValue(value)
140  split.SetAccount(account)
141  split.SetParent(trans)
142  return split
143 
144 
145 def record_opening_balance(original_account, new_account, new_book,
146  opening_balance_per_currency, commodity_tuple
147  ):
148  # create an opening balance if the account type is right
149  if new_account.GetType() in ACCOUNT_TYPES_TO_OPEN:
150  final_balance = original_account.GetBalance()
151  if final_balance.num() != 0:
152  # if there is a new currency type, associate with the currency
153  # a Transaction which will be the opening transaction for that
154  # currency and a GncNumeric value which will be the opening
155  # balance account amount
156  if commodity_tuple not in opening_balance_per_currency:
157  trans = Transaction(new_book)
158  trans.BeginEdit()
159  opening_balance_per_currency[commodity_tuple] = (
160  trans, GncNumeric(0, 1) )
161  trans, total = opening_balance_per_currency[commodity_tuple]
162 
163  new_total = total.sub(
164  final_balance,
165  GNC_DENOM_AUTO, GNC_HOW_DENOM_EXACT )
166 
167  initialize_split(
168  new_book,
169  final_balance,
170  new_account, trans)
171  opening_balance_per_currency[commodity_tuple] = \
172  (trans, new_total )
173 
174 def recursivly_build_account_tree(original_parent_account,
175  new_parent_account,
176  new_book,
177  new_commodity_table,
178  opening_balance_per_currency,
179  account_types_to_open ):
180 
181  for child in original_parent_account.get_children():
182  original_account = child
183  new_account = Account(new_book)
184  # attach new account to its parent
185  new_parent_account.append_child(new_account)
186 
187  # copy simple attributes
188  for attribute in ('Name', 'Type', 'Description', 'Notes',
189  'Code', 'TaxRelated', 'Placeholder'):
190  # new_account.SetAttribute( original_account.GetAttribute() )
191  getattr(new_account, 'Set' + attribute)(
192  getattr(original_account, 'Get' + attribute)() )
193 
194  # copy commodity
195  orig_commodity = original_account.GetCommodity()
196  namespace = orig_commodity.get_namespace()
197  mnemonic = orig_commodity.get_mnemonic()
198  new_commodity = new_commodity_table.lookup(namespace, mnemonic)
199  if new_commodity == None:
200  new_commodity = orig_commodity.clone(new_book)
201  new_commodity_table.insert(new_commodity)
202  new_account.SetCommodity(new_commodity)
203 
204  record_opening_balance( original_account, new_account,
205  new_book, opening_balance_per_currency,
206  (namespace, mnemonic),
207  )
208 
209  recursivly_build_account_tree(original_account,
210  new_account,
211  new_book,
212  new_commodity_table,
213  opening_balance_per_currency,
214  account_types_to_open)
215 
216 def reconstruct_account_name_with_mnemonic(account_tuple, mnemonic):
217  opening_balance_account_pieces = list(account_tuple)
218  opening_balance_account_pieces[
219  len(opening_balance_account_pieces) - 1 ] += " - " + mnemonic
220  return opening_balance_account_pieces
221 
222 def find_or_make_account(account_tuple, root_account, book,
223  currency ):
224  current_account_name, account_path = account_tuple[0], account_tuple[1:]
225  current_account = root_account.lookup_by_name(current_account_name)
226  if current_account == None:
227  current_account = Account(book)
228  current_account.SetName(current_account_name)
229  current_account.SetCommodity(currency)
230  root_account.append_child(current_account)
231 
232  if len(account_path) > 0:
233  return find_or_make_account(account_path, current_account, book,
234  currency)
235  else:
236  account_commod = current_account.GetCommodity()
237  if (account_commod.get_mnemonic(),
238  account_commod.get_namespace() ) == \
239  (currency.get_mnemonic(),
240  currency.get_namespace()) :
241  return current_account
242  else:
243  return None
244 
245 def choke_on_none_for_no_account(opening_account, extra_string ):
246  if opening_account == None:
247  raise Exception("account currency and name mismatch, " + extra_string)
248 
249 def create_opening_balance_transaction(commodtable, namespace, mnemonic,
250  new_book_root, new_book,
251  opening_trans, opening_amount,
252  simple_opening_name_used):
253  currency = commodtable.lookup(namespace, mnemonic)
254  assert( currency.get_instance() != None )
255 
256  if simple_opening_name_used:
257  account_pieces = reconstruct_account_name_with_mnemonic(
258  OPENING_BALANCE_ACCOUNT,
259  mnemonic)
260  opening_account = find_or_make_account(
261  account_pieces, new_book_root, new_book, currency )
262  choke_on_none_for_no_account(opening_account,
263  ', '.join(account_pieces) )
264  else:
265  opening_account = find_or_make_account(OPENING_BALANCE_ACCOUNT,
266  new_book_root, new_book,
267  currency )
268  simple_opening_name_used = True
269  if opening_account == None:
270  account_pieces = reconstruct_account_name_with_mnemonic(
271  OPENING_BALANCE_ACCOUNT,
272  mnemonic)
273  opening_account = find_or_make_account(
274  account_pieces, new_book_root, new_book, currency )
275  choke_on_none_for_no_account(opening_account,
276  ', '.join(account_pieces) )
277 
278  # we don't need to use the opening balance account at all if all
279  # the accounts being given an opening balance balance out
280  if opening_amount.num() != 0:
281  initialize_split(new_book, opening_amount, opening_account,
282  opening_trans)
283 
284  opening_trans.SetDate( *OPENING_DATE )
285  opening_trans.SetCurrency(currency)
286  opening_trans.SetDescription("Opening Balance")
287  opening_trans.CommitEdit()
288 
289  return simple_opening_name_used
290 
291 def main():
292 
293  if len(argv) < 3:
294  print('not enough parameters')
295  print('usage: new_book_with_opening_balances.py {source_book_url} {destination_book_url}')
296  print('examples:')
297  print("python3 new_book_with_opening_balances.py '/home/username/test.gnucash' 'sqlite3:///home/username/new_test.gnucash'")
298  print("python3 new_book_with_opening_balances.py '/home/username/test.gnucash' 'xml:///crypthome/username/finances/new_test.gnucash'")
299  return
300 
301  #have everything in a try block to unable us to release our hold on stuff to the extent possible
302  try:
303  original_book_session = Session(argv[1], SessionOpenMode.SESSION_NORMAL_OPEN)
304  new_book_session = Session(argv[2], SessionOpenMode.SESSION_NEW_STORE)
305  new_book = new_book_session.get_book()
306  new_book_root = new_book.get_root_account()
307 
308  commodtable = new_book.get_table()
309  # we discovered that if we didn't have this save early on, there would
310  # be trouble later
311  new_book_session.save()
312 
313  opening_balance_per_currency = {}
314  recursivly_build_account_tree(
315  original_book_session.get_book().get_root_account(),
316  new_book_root,
317  new_book,
318  commodtable,
319  opening_balance_per_currency,
320  ACCOUNT_TYPES_TO_OPEN
321  )
322 
323  (namespace, mnemonic) = PREFERED_CURRENCY_FOR_SIMPLE_OPENING_BALANCE
324  if (namespace, mnemonic) in opening_balance_per_currency:
325  opening_trans, opening_amount = opening_balance_per_currency[
326  (namespace, mnemonic)]
327  simple_opening_name_used = create_opening_balance_transaction(
328  commodtable, namespace, mnemonic,
329  new_book_root, new_book,
330  opening_trans, opening_amount,
331  False )
332  del opening_balance_per_currency[
333  PREFERED_CURRENCY_FOR_SIMPLE_OPENING_BALANCE]
334  else:
335  simple_opening_name_used = False
336 
337  for (namespace, mnemonic), (opening_trans, opening_amount) in \
338  opening_balance_per_currency.items() :
339  simple_opening_name_used = create_opening_balance_transaction(
340  commodtable, namespace, mnemonic,
341  new_book_root, new_book,
342  opening_trans, opening_amount,
343  simple_opening_name_used )
344 
345  new_book_session.save()
346  new_book_session.end()
347  original_book_session.end()
348  except:
349  if "original_book_session" in locals():
350  original_book_session.end()
351 
352  if "new_book_session" in locals():
353  new_book_session.end()
354 
355  raise
356 
357 
358 if __name__ == "__main__":
359  main()
360 
361 
The primary numeric class for representing amounts and values.
Definition: gnc-numeric.hpp:59