1#!/usr/bin/env python3 2 3# account_analysis.py -- Output all the credits and debits on an account 4# 5# Copyright (C) 2009, 2010 ParIT Worker Co-operative <transparency@parit.ca> 6# This program is free software; you can redistribute it and/or 7# modify it under the terms of the GNU General Public License as 8# published by the Free Software Foundation; either version 2 of 9# the License, or (at your option) any later version. 10# 11# This program is distributed in the hope that it will be useful, 12# but WITHOUT ANY WARRANTY; without even the implied warranty of 13# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 14# GNU General Public License for more details. 15# 16# You should have received a copy of the GNU General Public License 17# along with this program; if not, contact: 18# Free Software Foundation Voice: +1-617-542-5942 19# 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652 20# Boston, MA 02110-1301, USA gnu@gnu.org 21# 22# @author Mark Jenkins, ParIT Worker Co-operative <mark@parit.ca> 23 24## @file 25# @brief Output all the credits and debits on an account 26# @author Mark Jenkins, ParIT Worker Co-operative <mark@parit.ca> 27# @ingroup python_bindings_examples 28 29# python imports 30from sys import argv, stdout 31from datetime import date, timedelta 32from bisect import bisect_right 33from decimal import Decimal 34from math import log10 35import csv 36 37# gnucash imports 38from gnucash import Session, GncNumeric, Split, SessionOpenMode 39 40# Invoke this script like the following example 41# $ python3 account_analysis.py gnucash_file.gnucash \ 42# 2010 1 monthly 12 \ 43# debits-show credits-show Assets 'Test Account' 44# 45# That will do an analysis on the account 'Assets:Test Account' from 46# gnucash_file.xac, all of the debits and all of the credits will be shown 47# and summed on for 12 monthly periods starting from January (1st month) 2010 48# 49# if you just want to see the credit and debit sums for each period, use 50# the debits-noshow and credits-noshow argument 51# 52# The output goes to stdout and is in csv format. 53# 54# Account path arguments are space separated, so you need to quote parts of 55# the account path with spaces in them 56# 57# available period types are monthly quarterly and yearly 58# 59# At the moment this script only supports GnuCash files of the sqllite3 type 60# its an easy edit to switch to xml: etc... 61 62 63# a dictionary with a period name as key, and number of months in that 64# kind of period as the value 65PERIODS = {"monthly": 1, 66 "quarterly": 3, 67 "yearly": 12 } 68 69NUM_MONTHS = 12 70 71ONE_DAY = timedelta(days=1) 72 73DEBITS_SHOW, CREDITS_SHOW = ("debits-show", "credits-show") 74 75ZERO = Decimal(0) 76 77def gnc_numeric_to_python_Decimal(numeric): 78 negative = numeric.negative_p() 79 if negative: 80 sign = 1 81 else: 82 sign = 0 83 copy = GncNumeric(numeric.num(), numeric.denom()) 84 result = copy.to_decimal(None) 85 if not result: 86 raise Exception("gnc numeric value %s can't be converted to decimal" % 87 copy.to_string() ) 88 digit_tuple = tuple( int(char) 89 for char in str(copy.num()) 90 if char != '-' ) 91 denominator = copy.denom() 92 exponent = int(log10(denominator)) 93 assert( (10 ** exponent) == denominator ) 94 return Decimal( (sign, digit_tuple, -exponent) ) 95 96 97def next_period_start(start_year, start_month, period_type): 98 # add numbers of months for the period length 99 end_month = start_month + PERIODS[period_type] 100 # use integer division to find out if the new end month is in a different 101 # year, what year it is, and what the end month number should be changed 102 # to. 103 # Because this depends on modular arithmetic, we have to curvert the month 104 # values from 1-12 to 0-11 by subtracting 1 and putting it back after 105 # 106 # the really cool part is that this whole thing is implemented without 107 # any branching; if end_month > NUM_MONTHS 108 # 109 # A the super nice thing is that you can add all kinds of period lengths 110 # to PERIODS 111 end_year = start_year + ( (end_month-1) / NUM_MONTHS ) 112 end_month = ( (end_month-1) % NUM_MONTHS ) + 1 113 114 return end_year, end_month 115 116 117def period_end(start_year, start_month, period_type): 118 if period_type not in PERIODS: 119 raise Exception("%s is not a valid period, should be %s" % ( 120 period_type, str(list(PERIODS.keys())) ) ) 121 122 end_year, end_month = next_period_start(start_year, start_month, 123 period_type) 124 125 # last step, the end date is day back from the start of the next period 126 # so we get a period end like 127 # 2010-03-31 for period starting 2010-01 instead of 2010-04-01 128 return date(end_year, end_month, 1) - ONE_DAY 129 130 131def generate_period_boundaries(start_year, start_month, period_type, periods): 132 for i in range(periods): 133 yield ( date(start_year, start_month, 1), 134 period_end(start_year, start_month, period_type) ) 135 start_year, start_month = next_period_start(start_year, start_month, 136 period_type) 137 138def account_from_path(top_account, account_path, original_path=None): 139 if original_path==None: original_path = account_path 140 account, account_path = account_path[0], account_path[1:] 141 142 account = top_account.lookup_by_name(account) 143 if account == None: 144 raise Exception( 145 "path " + ''.join(original_path) + " could not be found") 146 if len(account_path) > 0 : 147 return account_from_path(account, account_path, original_path) 148 else: 149 return account 150 151 152def main(): 153 154 if len(argv) < 10: 155 print('not enough parameters') 156 print('usage: account_analysis.py {book url} {start year} {start month, numeric} {period type: monthly, quarterly, or yearly} {number of periods to show, from start year and month} {whether to show debits: debits-show for true, all other values false} {whether to show credits: credits-show for true, all other values false} {space separated account path, as many nested levels as desired} ') 157 print('examples:\n') 158 print("The following example analyzes 12 months of 'Assets:Test Account' from /home/username/test.gnucash, starting in January of 2010, and shows both credits and debits") 159 print("python3 account_analysis.py '/home/username/test.gnucash' 2010 1 monthly 12 debits-show credits-show Assets 'Test Account'\n") 160 print("The following example analyzes 2 quarters of 'Liabilities:First Level:Second Level' from /home/username/test.gnucash, starting March 2011, and shows credits but not debits") 161 print("python3 account_analysis.py '/home/username/test.gnucash' 2011 3 quarterly 2 debits-noshow credits-show Liabilities 'First Level' 'Second Level") 162 return 163 164 try: 165 (gnucash_file, start_year, start_month, period_type, periods, 166 debits_show, credits_show) = argv[1:8] 167 start_year, start_month, periods = [int(blah) 168 for blah in (start_year, start_month, 169 periods) ] 170 171 debits_show = debits_show == DEBITS_SHOW 172 credits_show = credits_show == CREDITS_SHOW 173 174 account_path = argv[8:] 175 176 gnucash_session = Session(gnucash_file, SessionOpenMode.SESSION_NORMAL_OPEN) 177 root_account = gnucash_session.book.get_root_account() 178 account_of_interest = account_from_path(root_account, account_path) 179 180 # a list of all the periods of interest, for each period 181 # keep the start date, end date, a list to store debits and credits, 182 # and sums for tracking the sum of all debits and sum of all credits 183 period_list = [ 184 [start_date, end_date, 185 [], # debits 186 [], # credits 187 ZERO, # debits sum 188 ZERO, # credits sum 189 ] 190 for start_date, end_date in generate_period_boundaries( 191 start_year, start_month, period_type, periods) 192 ] 193 # a copy of the above list with just the period start dates 194 period_starts = [e[0] for e in period_list ] 195 196 # insert and add all splits in the periods of interest 197 for split in account_of_interest.GetSplitList(): 198 trans = split.parent 199 trans_date = date.fromtimestamp(trans.GetDate()) 200 201 # use binary search to find the period that starts before or on 202 # the transaction date 203 period_index = bisect_right( period_starts, trans_date ) - 1 204 205 # ignore transactions with a date before the matching period start 206 # (after subtracting 1 above start_index would be -1) 207 # and after the last period_end 208 if period_index >= 0 and \ 209 trans_date <= period_list[len(period_list)-1][1]: 210 211 # get the period bucket appropriate for the split in question 212 period = period_list[period_index] 213 214 # more specifically, we'd expect the transaction date 215 # to be on or after the period start, and before or on the 216 # period end, assuming the binary search (bisect_right) 217 # assumptions from above are are right.. 218 # 219 # in other words, we assert our use of binary search 220 # and the filtered results from the above if provide all the 221 # protection we need 222 assert( trans_date>= period[0] and trans_date <= period[1] ) 223 224 split_amount = gnc_numeric_to_python_Decimal(split.GetAmount()) 225 226 # if the amount is negative, this is a credit 227 if split_amount < ZERO: 228 debit_credit_offset = 1 229 # else a debit 230 else: 231 debit_credit_offset = 0 232 233 # store the debit or credit Split with its transaction, using the 234 # above offset to get in the right bucket 235 # 236 # if we wanted to be really cool we'd keep the transactions 237 period[2+debit_credit_offset].append( (trans, split) ) 238 239 # add the debit or credit to the sum, using the above offset 240 # to get in the right bucket 241 period[4+debit_credit_offset] += split_amount 242 243 csv_writer = csv.writer(stdout) 244 csv_writer.writerow( ('period start', 'period end', 'debits', 'credits') ) 245 246 def generate_detail_rows(values): 247 return ( 248 ('', '', '', '', trans.GetDescription(), 249 gnc_numeric_to_python_Decimal(split.GetAmount())) 250 for trans, split in values ) 251 252 253 for start_date, end_date, debits, credits, debit_sum, credit_sum in \ 254 period_list: 255 csv_writer.writerow( (start_date, end_date, debit_sum, credit_sum) ) 256 257 if debits_show and len(debits) > 0: 258 csv_writer.writerow( 259 ('DEBITS', '', '', '', 'description', 'value') ) 260 csv_writer.writerows( generate_detail_rows(debits) ) 261 csv_writer.writerow( () ) 262 if credits_show and len(credits) > 0: 263 csv_writer.writerow( 264 ('CREDITS', '', '', '', 'description', 'value') ) 265 csv_writer.writerows( generate_detail_rows(credits) ) 266 csv_writer.writerow( () ) 267 268 # no save needed, we're just reading.. 269 gnucash_session.end() 270 except: 271 if "gnucash_session" in locals(): 272 gnucash_session.end() 273 274 raise 275 276if __name__ == "__main__": main() 277 278 279