summaryrefslogtreecommitdiffstats
path: root/contrib/csvbankingqif.py
blob: c3700dfb48f643773b73f09ada22fff6d5c2c72d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-
#
#***************************************************************************
#                      csvbankingqif.py  -  description
#                         -------------------
# begin                 : Sat 31 Oct. 2009
# copyright             : (C) 2009 by Allan Anderson
# email                 : aganderson@ukonline.co.uk
#
#***************************************************************************/
#
#***************************************************************************
#*                                                                         *
#*   This program is free software; you can redistribute it and/or modify  *
#*   it under the terms of the GNU General Public License as published by  *
#*   the Free Software Foundation; either version 2 of the License, or     *
#*   (at your option) any later version.                                   *
#*                                                                         *
#***************************************************************************/
#
#                         *** NOTE ***
#   It may be necessary to remove the second line, before running.
#   It may be necessary also, to change the currency symbol if your file
#   includes one.
#
#   Simple utility to convert a csv format file, as from a bank, to qif
#   format for KMyMoney2.  There is no standard for the layout of such a
#   file, but generally there will be a header line which indicates the
#   layout of the fields within the file.  Even then though, the order of
#   the columns may vary.  It is assumed, though, that the first column
#   will contain the date, in 'dd MM yy' format, 'MM' being the month
#   name or number.
#   The  second column is the detail.  The third and fourth columns are
#   assumed to be debits and credits.  Even fron the same bank, these
#   columns may be reversed, but the script handles this.  Alternatively,
#   the third column may be the amount.  There may also be additional
#   columns, such as current balance, but these are all ignored.
#   Apart from the header line, there are likely to be other lines, with
#   account number, balance details, etc.  These are skipped.
#
#   First, make the script executable: chmod u+x csvbankinyqif.py .
#   The script should be added to the KMM QIF import profile.  In KMM, open
#   Tools/QIF Profile Editor and click on 'New' at the bottom.  then enter a
#   name, such as csvbank, then click 'OK'.  Next, click on that name in the
#   next window, and open the Filter tab.  For the 'Input filter location',
#   select the location you chose for the script file.  For the Input filter
#   file type, enter *.csv, or whatever extension your data file has.
#   Finally, click 'OK'.
#   When ready, select File/Import/QIF, and browse to your data file, then
#   select your new filter profile, and click 'Import'.
#

#desc="date","detail","debit","credit"
mnths=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
currency = '£'
setup = True
debsfirst = False    #  credit column is before debit
both = False        #  only one amount column
print("!Type:Bank")
while 1:
  try:
      line=input()
  except:break
  if line == "" : continue  #  empty line
  line = line.replace('"','',)  #  Strip out ' "' quotes
  line = line.replace(currency,'',)  #  Strip out '£', etc. symbol
  cols = line.split(',')     #  Seperator  between columns
  if setup:
#
#        *** SETUP ***
#
    dt = cols[0][0:2]       #  Might be a date (day)
    datefound = ((dt > '0') and (dt < '32'))    #this looks like a date
    hdrfound = (cols[0] == 'Date')
    if not datefound and not hdrfound: continue#  still in hdrs
    if hdrfound:
#
#                *** 'Date', so now in header ***
#
      hdrfound = False
      #line = line.replace(' ','',)  #  Strip out spaces in hdr
      cols[2] = cols[2].replace(' ','',)  #  Strip out spaces in hdr
      if cols[2] == 'Debits':
        debsfirst = True
        continue
      elif cols[2] == 'Credits':
         debsfirst = False
         continue
      elif cols[2] == 'Amount':
         both = True
         continue
      else:
         print('Error in col[2]')
         print('*** Error in header - col 2 s/b Debit, Credit, or Amount')
         #continue
         exit
    setup ==False
#
#                *** Transactions ***
#
  cnum = 0                   #  First column
  for col in cols:
      if cnum > 3: break
#
#                            #  Process Date
#
      elif  cnum == 0:
        col =col.replace(' ','/',2)  #  Change date seperator to '/'
        m = col.split('/')
#                *** Check if month not numeric
        mn = m[1][0:3]        #  Extract month string from field 2
        fld = 2
        try:
          mnth = mnths.index(mn)  #  Get month number
        except ValueError:    #  Field 2 not a valid month name
          mn = m[0][0:3]      #  .. so try field 1
          fld = 1
          try:
            mnth = mnths.index(mn)
          except ValueError:  #  Nor is field 1
            dat = ''.join(col)            #  ..so use as is (numeric)
          else:                #  Field 1 is month name
            dat = col[1:3] + str(mnth + 1) + '/' +m[2]
        else:                  #  Field 2 is month name
          dat = col[0:3] + str(mnth + 1) + '/' +m[2]
        line = 'D' + dat+'\n'
#
#                              #  Detail column
#
      elif cnum == 1:
        #col = col.replace('"','')
        line = line + 'P' + col +'\n'
#
#                              #  Debit or credit column
#
      elif cnum == 2:
        if col != "":
          if debsfirst == True:    #  This is Debit column
            col = '-' + col  #  Mark as -ve
          line = line + 'T' + col +'\n'
#
#                              #  Credit or debit?
#
      elif ((cnum == 3) and (both == False)):
        if col != "":
          if ((debsfirst == False) ):
            col = '-' + col  #  Mark as -ve
          line = line + 'T' + col + '\n'
      cnum+=1
  print(line + '^')    #  output this entry