Trim the memos?

[christopherlam]

Hi @goodvibes2
I use your useful jar regularly.
Most useful is the date filter because ING stupidly exports every transaction ever.
Don't you find the "DESC - Visa Purchase - Receipt XXX" rather annoying?
How about some heuristics; DESC is copied into Description field, and the rest into Memo field? I'm not 100% sure of OFX format but it "shouldn't be too difficult".
Chris Lam

[christopherlam]

So the pseudo code would be

if MEMO has " - " and DESC is empty-string, then
set DESC to be string before " - "
set MEMO to be string after " - "

This would be an optional feature, default TRUE?

[christopherlam]

Ok I read the OFX spec -- there's no DESC but there's NAME which is not used by ING.

[christopherlam]

And would also be useful to replace any <BR/> in MEMO to single-space...

Summary: <MEMO> field: replace all "<BR/>" with " ", then search " - ". If found then:

• write <NAME> with before-string
• write <MEMO> with after-string

[goodvibes2]

Hi @christopherlam,
Glad to hear there is another person using my software. Especially someone actively contributing so much to GnuCash.

FYI, you can export only transactions for a specific date range from the ING Aus webpage by searching for the transactions in the date range (More options, Date range), then exporting those.

These 2 mods seem to be something I would like to do.
I don't have any transactions with "DESC - Visa Purchase - Receipt XXX". Are they from credit card accounts? I only have ING savings accounts.

If so, can you please post a sample .ofx file, with all personal info changed? Just a couple of transactions should be enough.

Have you tested loading a file into GnuCash with a transaction with both MEMO and NAME fields?
Not much point doing this mod if it won't load into GnuCash as you wish.

Can you please give me an actual example of how you would like the field to be split?
I'm still a little unclear exactly what you would like.

I have also seen the issue with the close break tag.

[christopherlam]

Here's an excerpt from the original download - illustrates both break tag and desc - rubbish pattern.

<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20181218000000
<TRNAMT>-20.00
<FITID>137049
<MEMO>SMARTRIDER - Direct Debit - Receipt 137049 #06658024218121752
</STMTTRN>
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20181218000000
<TRNAMT>-32.98
<FITID>83109
<MEMO>IGA W - EFTPOS Purchase with Cash Out - Receipt 83109 - Cash amount $20.00<BR/>Date 18 Dec 2018 Time 12:52PM Card 462263xxxxxx3981
</STMTTRN>
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20181218000000
<TRNAMT>-14.95
<FITID>174938
<MEMO>JB HI FI - Visa Purchase - Receipt 174938In Date 14 Dec 2018 Card 462263xxxxxx3981
</STMTTRN>

I'd be happy with the 2nd transaction be modified to

<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20181218000000
<TRNAMT>-32.98
<FITID>83109
<NAME>IGA W
<MEMO>EFTPOS Purchase with Cash Out - Receipt 83109 - Cash amount $20.00 Date 18 Dec 2018 Time 12:52PM Card 462263xxxxxx3981
</STMTTRN>

[christopherlam]

Of note, NAME must appear before MEMO for libofx to understand it.

Here's a guile version that mostly replicates your version. Very compact. I may be able to create a basic GUI (but not sure if I will).

(use-modules (ice-9 rdelim))

(define bankid "000000")
(define acctid "12345678")
(define accttype "SAVINGS")

(define (in-date? datestr)
  (and datestr
       (let ((yy (substring/shared datestr 0 4))
             (mm (substring/shared datestr 4 6))
             (dd (substring/shared datestr 6 8)))
         (and (equal? yy "2019")
              (equal? mm "03")))))

;; by a.wingo
(define (string-replace-substring str substring replacement)
  (let ((sublen (string-length substring)))
    (with-output-to-string
      (lambda ()
        (let lp ((start 0))
          (cond
           ((string-contains str substring start)
            => (lambda (end)
                 (display (substring/shared str start end))
                 (display replacement)
                 (lp (+ end sublen))))
           (else
            (display (substring/shared str start)))))))))

(define (render-section outport name alist)
  (format outport "<~a>\n" name)
  (for-each (lambda (p) (format outport "<~a>~a\n" (car p) (cdr p))) alist)
  (format outport "</~a>\n" name))

(define (writebankacctfrom outport)
  (render-section outport "BANKACCTFROM"
                  `(("BANKID" . ,bankid)
                    ("ACCTID" . ,acctid)
                    ("ACCTTYPE" . ,accttype))))

(define (render trans outport)
  (let* ((memo (assoc-ref trans "MEMO"))
         (dtposted (assoc-ref trans "DTPOSTED"))
         (fitid (string-append (assoc-ref trans "FITID") "."
                               (substring/shared dtposted 0 8) "."
                               (assoc-ref trans "TRNAMT")))
         (memo (string-replace-substring memo "<BR/>" " "))
         (trans (assoc-set! trans "FITID" fitid)))
    (cond
     ((string-contains memo " - ") =>
      (lambda (idx)
        (let* ((trans (assoc-remove! trans "MEMO"))
               (trans (assoc-set! trans "NAME" (substring/shared memo 0 idx)))
               (trans (assoc-set! trans "MEMO" (substring/shared memo (+ idx 3)))))
          (render-section outport "STMTTRN" (reverse trans)))))
     (else
      (let ((trans (assoc-set! trans "MEMO" memo)))
        (render-section outport "STMTTRN" (reverse trans)))))))

(define (process inputfile outputfile)
  (call-with-input-file inputfile
    (lambda (inport)
      (call-with-output-file outputfile
        (lambda (outport)
          (let lp ((line (read-line inport))
                   (trans '())
                   (ntrans 0)
                   (ntotal 0)
                   (has-bankacctfrom? #f)
                   (in-trans? #f))
            (cond

             ((eof-object? line)
              (format #t "Processed ~a/~a transactions\n" ntrans ntotal))

             ((string-prefix? "<STMTTRN>" line)
              (lp (read-line inport) '() ntrans (1+ ntotal) has-bankacctfrom? #t))

             ((string-prefix? "</STMTTRN>" line)
              (cond
               ((in-date? (assoc-ref trans "DTPOSTED"))
                (render trans outport)
                (lp (read-line inport) '() (1+ ntrans) ntotal has-bankacctfrom? #f))
               (else
                (lp (read-line inport) '() ntrans ntotal has-bankacctfrom? #f))))

             ((string-prefix? "<BANKACCTFROM>" line)
              (display line outport)
              (lp (read-line inport) trans ntrans ntotal #t in-trans?))

             ((and (not has-bankacctfrom?) (string-prefix? "<BANKTRANLIST>" line))
              (writebankacctfrom outport)
              (display line outport)
              (lp (read-line inport) trans ntrans ntotal has-bankacctfrom? in-trans?))

             ((and in-trans? (string-prefix? "<" line) (string-contains line ">")) =>
              (lambda (close)
                (let ((k (substring/shared line 1 close))
                      (v (substring/shared line (1+ close))))
                  (lp (read-line inport) (assoc-set! trans k v)
                      ntrans ntotal has-bankacctfrom? in-trans?))))

             (else
              (display line outport)
              (newline outport)
              (lp (read-line inport) trans ntrans ntotal has-bankacctfrom?
                  in-trans?)))))))))

(process "Transactions.ofx" "Output.ofx")

[goodvibes2]

Hi ChristopherLam,
These mods are in Release 2.01 and documented in the README.md.
Please test.

[christopherlam]

Very wordy and I don't have a java build environment! I'll have to take your word for it :)

[goodvibes2]

You don't need a Java build environment, or even Java! Follow instructions for using the runtime image. I guess I was right to be concerned instructions were too confusing when including both Java 8 & 11 instructions in the 1 README...

[christopherlam]

Works well :)