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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
| ' MT2OFX Input Processing Script for Postbank NL Asc/CSV formats
Option Explicit
Const ScriptVersion = "$Header: /MT2OFX/Postbank-MijnPostbankCSV.vbs 12 7/12/09 20:10 Colin $"
Const ScriptName = "Postbank-MijnPostbankCSV"
Const FormatName = "Postbank (NL) CSV (Mijn Postbank) Formaat"
Const ParseErrorMessage = "Kan regel niet ontleden."
Dim ParseErrorTitle : ParseErrorTitle = ScriptName
' fld# len inhoud
' 1 8 datum yyyymmdd
Const fldDatum = 1
' 2 32 omschrijving 1 = naam
Const fldNaam = 2
' 3 10 rekeningnummer
Const fldRekNr = 3
' 4 10 tegenrekening (onbetrouwbaar)
Const fldTgnRekNr = 4
' 5 3 txn code XXX
Const fldTransCode = 5
' 6 2 Af/Bij
Const fldAfBij = 6
' 7 12 bedrag
Const fldBedrag = 7
' 8 12 mutatiesoort
Const fldMutatieSoort = 8
' 9 32 mededeling
Const fldMededeling = 9
' pattern for Girotel Online ASC
Const MainPat = "(\d{10})(\d{8})(.{3})(\d{3})(\d{10})(.{32})(.{20})(\d{12})(.).(.{32})(.{32})(.{32})(.{32})(.{32})(...)"
' pattern for Girotel Offline ASC
Const OfflinePat = "(\d{10})(\d{8})(.{3})(\d{3})(\d{10})(.{32})(.{20})(\d{12})(...)(.).(.{32})?(.{32})?(.{32})?(.{32})?(.{32})?"
' this holds the pattern to be used
Dim LinePat
' this indicates whether we are processing a CSV file
Dim bIsCSV
Const BADatePat = "PASNR .{7} (\d{2})-(\d{2})-(\d{2}) (\d{2}) UUR (\d{2}).*"
' PASNR 999X999 dd-mm-yy hh UUR mm
Const GMDatePat = " (\d{2})-(\d{2})-(\d{2}) (\d{2}):(\d{2}).*"
' dd-mm-yy 16:52 999X999 9999999
Dim LastMemo ' last non-blank memo field seen
Sub Initialise()
LogProgress ScriptVersion, "Initialise"
If Not CheckVersion() Then
Abort
End If
End Sub
' function DescriptiveName
' returns a string with a descriptive name of this script
Function DescriptiveName()
DescriptiveName = FormatName
End Function
Function StartsWith(s, Prefix)
StartsWith = (Left(s,Len(Prefix)) = Prefix)
End Function
Function ParseDate(sDate) ' expects dd-mm-yyyy
Dim iYear, iMonth, iDay ' for dates
If Mid(sDate, 3, 1) = "-" Then
' Old format = dd-mm-yyyy
iDay = CInt(Left(sDate,2))
iMonth = CInt(Mid(sDate,4,2))
iYear = CInt(Mid(sDate,7,4))
Else
' New format = yyyymmdd
iYear = CInt(left(sDate,4))
iMonth = CInt(Mid(sDate,5,2))
iDay = CInt(Mid(sDate,7,2))
End If
ParseDate = DateSerial(iYear, iMonth, iDay)
End Function
Function TrimTrailingDigits(s)
Dim r
Set r=New regexp
r.Global = False
r.Pattern = "^(.*?) *\d+$"
Dim m
Set m=r.Execute(s)
If m.Count = 0 Then
TrimTrailingDigits = s
Else
TrimTrailingDigits = m(0).SubMatches(0)
End If
End Function
Sub ConcatMemo(s)
If s = "" Then
Exit Sub
End If
If Len(Txn.FurtherInfo) > 0 Then
Txn.FurtherInfo = Txn.FurtherInfo & Cfg.MemoDelimiter
End If
Txn.FurtherInfo = Txn.FurtherInfo & s
LastMemo = s
End Sub
' function RecogniseTextFile
' returns True if the input file is recognised by this script
' returns False if someone else can have a go!
Function RecogniseTextFile()
Dim vFields
Dim sLine
RecogniseTextFile = False
bIsCSV = False
sLine = ReadLine()
' must be CSV format
vFields = ParseLineDelimited(sLine, ",", False)
If TypeName(vFields) <> "Variant()" Then
Exit Function
End If
If UBound(vFields) <> 9 Then
Exit function
End If
If vFields(fldDatum) <> "Datum" Or _
vFields(fldNaam) <> "Naam / Omschrijving" Or _
vFields(fldRekNr) <> "Rekening" Or _
vFields(fldTgnRekNr) <> "Tegenrekening" Or _
vFields(fldTransCode) <> "Code" Or _
vFields(fldAfBij) <> "Af Bij" Or _
vFields(fldBedrag) <> "Bedrag (EUR)" Or _
vFields(fldMutatieSoort) <> "MutatieSoort" Or _
vFields(fldMededeling) <> "Mededelingen" Then
Exit Function
End If
LogProgress ScriptName, "File Recognised"
RecogniseTextFile = True
End Function
Function LoadTextFile()
Dim sLine ' holds a line
Dim sPat ' holds the match pattern
Dim vFields ' array of fields in the line
Dim sType ' record type
Dim sAcct ' last account number
Dim Stmt ' holds the current statement
Dim sTmp ' temporary string
Dim sTmp2 ' another one
Dim vDateBits ' parts of date
Dim iYear ' year
Dim sOms1, sOms2, sOms3, sOms4, sOms5 ' description lines
Dim iSeq ' transaction sequence number
LoadTextFile = False
sAcct = ""
' eat first (header) line
If Not AtEof() Then
sLine = ReadLine()
End If
Do While Not AtEOF()
sLine = ReadLine()
' 20090307 CS: allow double quotes in the text by replacing the real delimiters with single quotes
sLine = Replace(sLine, """,", "',")
sLine = Replace(sLine, ",""", ",'")
If Left(sLine, 1) = """" Then sLine = "'" & Mid(sLine, 2)
If Right(sLine, 1) = """" Then sLine = Left(sLine, Len(sLine)-1) & "'"
If Len(sLine) > 0 Then
' fix up double quotes within the fields which confuses the parser
vFields = ParseLineDelimited(sLine, ",", False)
If TypeName(vFields) <> "Variant()" Then
MsgBox ParseErrorMessage, vbOKOnly+vbCritical, ParseErrorTitle
Abort
Exit Function
End If
' set up new transaction, and start a new statement if the account # changes
If sAcct <> vFields(fldRekNr) Then
Set Stmt = NewStatement()
iSeq = 0
sAcct = vFields(fldRekNr)
Stmt.Acct = Trim(sAcct)
Stmt.BankName = "Postbank"
Stmt.OpeningBalance.Ccy = "EUR"
Stmt.OpeningBalance.BalDate = ParseDate(vFields(fldDatum))
Stmt.ClosingBalance.Ccy = "" ' to force 00000000 as date in ledger bal
End If
NewTransaction
iSeq = iSeq + 1
LastMemo = ""
Txn.Amt = ParseNumber(vFields(fldBedrag), ",")
If vFields(fldAfBij) <> "Bij" Then ' could be "Af" or blank - also DR
Txn.Amt = -Txn.Amt
End If
' this will put the NAME of the payee into Txn.Payee.
Txn.Payee = vFields(fldNaam)
'If vFields(fldTransCode) = "BA" Then
' strip off the 8 digits plus space (what are they for?)
' Txn.Payee = Trim(Mid(Txn.Payee, 9))
' capture trimmed full field in memo
' CS 20041119 Removed as Postbank are now providing better data
' ConcatMemo Txn.Payee
' strip off trailing digits (often branch number)
' comment out this line if required!
' Txn.Payee = TrimTrailingDigits(Txn.Payee)
If vFields(fldTransCode) = "ST" Then
Txn.Payee = "Storting"
Else
' capture full field in memo
' CS 20041119 Removed as Postbank are now providing better data
' ConcatMemo Trim(vFields(fldNaam))
End If
Txn.ValueDate = ParseDate(vFields(fldDatum))
Txn.BookDate = ParseDate(vFields(fldDatum))
Stmt.ClosingBalance.BalDate = Txn.ValueDate
Txn.IsReversal = False
Txn.TxnType = TransType(vFields(fldTransCode), Txn.Amt)
If vFields(fldTransCode) = "BA" Then
vDateBits = ParseLineFixed(vFields(fldMededeling), BADatePat)
ElseIf vFields(fldTransCode) = "GM" Then
vDateBits = ParseLineFixed(vFields(fldMededeling), GMDatePat)
Else
vDateBits = False ' as long as it is not an array!
End If
' the following code works for all cases as long as the date/time fields are in the right order
If TypeName(vDateBits) = "Variant()" Then
If UBound(vDateBits) >= 5 Then
Txn.TxnDate = DateSerial(vDateBits(3), CInt(vDateBits(2)), CInt(vDateBits(1))) _
+ TimeSerial(CInt(vDateBits(4)), CInt(vDateBits(5)), 0)
Txn.TxnDateValid = True
End If
End If
' cash withdrawals - special string for payee
If vFields(fldTransCode) = "GM" Or vFields(fldTransCode) = "PK" Then
Txn.Payee = "Kasopname"
ElseIf vFields(fldTransCode) = "DV" Then
Txn.Payee = "Incasso Creditcard"
End If
ConcatMemo Trim(Mid(vFields(fldMededeling),1,32))
ConcatMemo Trim(Mid(vFields(fldMededeling),33,32))
ConcatMemo Trim(Mid(vFields(fldMededeling),65,32))
ConcatMemo Trim(Mid(vFields(fldMededeling),97,32))
ConcatMemo Trim(Mid(vFields(fldMededeling),129,32))
' very special checks!
sTmp = Trim(Left(vFields(fldMededeling),32))
sTmp2 = Trim(Mid(vFields(fldMededeling), 33, 32))
' direct debits almost always have the payee name in the memo field
'If vFields(fldTransCode) = "IC" Then
' Txn.Payee = sTmp
' If StartsWith(Txn.Payee, "KN: ") Then
' Txn.Payee = Trim(Mid(vFields(fldMededeling), 22, 32))
' End If
'End If
' sort out a transaction ID - date, sequence are provided!
Txn.FITID = CStr(Year(Txn.BookDate)) & _
Right("0" & CStr(Month(Txn.BookDate)), 2) & _
Right("0" & CStr(Day(Txn.BookDate)), 2) & _
"." & CStr(iSeq)
End If
Loop
LoadTextFile = True
End Function
Function TransType(PostbankCode, Amt)
Select Case PostbankCode
Case "AC"
TransType = "DIRECTDEBIT"
Case "BA"
TransType = "POS"
Case "CH"
TransType = "CHECK"
Case "GB"
TransType = "CHECK"
Case "GM"
TransType = "ATM"
Case "IC"
TransType = "DIRECTDEBIT"
Case "PK"
TransType = "CASH"
Case "ST"
TransType = "DEP"
Case "VZ"
TransType = "DIRECTDEP"
Case Else
TransType = "OTHER"
End Select
End Function
' other known codes:
' RES, TAN, GIN, GEW, DV, EUR, FL, GF, GT, OV, PO, TA |
/u/37422/crop614599366878b_cropped.png?f=community)
maar bij mij komt er niet 'B33280789' te staan, maar gewoon de naam van mijn betaalrekening ('Hr A. Nandus', zeg maar
), dus ik kan het niet mappen.
:strip_icc():strip_exif()/u/488509/crop55d63a35844c4_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/410362/crop5dba9cda00d45_cropped.jpeg?f=community)
/u/147561/vaag2.JPG?f=community)
. En moet je nu zien!
:strip_icc():strip_exif()/u/36083/crop5dca9e34ce091_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/383592/av-11.jpg?f=community)
:strip_icc():strip_exif()/u/36378/crop5a3f931ecfec0_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/658580/crop58cedbd7edb9a_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/85747/crop5e3491f382a9e_cropped.jpeg?f=community)
:strip_exif()/u/259931/stuiterbal2.gif?f=community)
/u/70160/herko-icon.png?f=community){kind=icon}
:strip_icc():strip_exif()/u/40490/avatar23.jpg?f=community){kind=avatar}
/u/236420/crop61cd5787a391f_cropped.png?f=community)
:strip_icc():strip_exif()/u/497365/crop5a942743b8f96_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/347700/honeybadger.jpg?f=community)
:strip_icc():strip_exif()/u/465845/crop582b3716168b5_cropped.jpeg?f=community)
:strip_icc():strip_exif()/u/56758/falconlogo.jpg?f=community)
:strip_exif()/u/23102/anandus.gif?f=community)
:strip_icc():strip_exif()/u/227462/crop5884c2333b039_cropped.jpeg?f=community)
/u/263626/crop5db0af5550f2c_cropped.png?f=community)
