Feature: Import bank statements CSV about expenses HOT 31 CLOSED

Thanks for this. Assuming that the CSV file would consist of (among possibly many) two columns of type date and debit, the corresponding data could be imported from the file and used in the entry to the expense file. A couple of things to take care of

• the date format could vary from bank to bank.
• column headers could vary, so need to make sure which one corresponds to the date and which one to the debit

I can try to implement something but I don't have a use case for me and it might help if you can point me to a demo file just to see if I can make it work with that.

from expenses.

I'm happy to play around and learn some elisp with this feature. Here is what I have so far with some of my test data. I'm only just starting out with elisp. I've managed to separate the date, debit and description.

I know what you are saying about dates, they are hard. Is there a library which will parse the date and give a standard date output?

Test code

;; CSV to list
(defun parse-csv-file (file)
  (interactive
   (list (read-file-name "CSV file: ")))
  (let ((buf (find-file-noselect file))
        (result nil))
    (with-current-buffer buf
      (goto-char (point-min))
      (while (not (eobp))
        (let ((line (buffer-substring-no-properties
                     (line-beginning-position) (line-end-position))))
          (push (split-string line ",") result))
        (forward-line 1)))
    (reverse result)))

;; Display parts of the CSV
(defun print-list-elements3 (list)
  "print the narrative from the list"

  (setq list (cdr list))                ; Skip first line
  (while list
    (setq row (cdr (car list)))
    (setq date (car row))
    (setq row (cdr row))
    (setq narrative (car row))
    (setq row (cdr row))
    (setq debit (car row))
    (if (> (string-to-number debit) 0)
        (print (concat date " " narrative " " debit))
      )

    (setq list (cdr list))))

(print-list-elements3 (parse-csv-file "~/Downloads/bank_statements/test.csv"))

Test data

Bank Account,Date,Narrative,Debit Amount,Credit Amount,Balance,Categories,Serial
5544,05/12/2021,"THAT CAFE SYDNEY AUS",4.00,,0.00,OTHER,
5544,06/12/2021,"40312444 BPAY TO CITY ND COUNCIL AU",873.00,,0.00,OTHER,
5544,07/12/2021,"GREAT STATION HOTEL CAIRNS AUS",6.00,,0.00,OTHER,
5544,08/12/2021,"GREAT STATION HOTEL CAIRNS AUS",12.00,,0.00,OTHER,
5544,09/12/2021,"PAYPAL *GOOGLE YOUTUBE 4553201151 AUS",11.99,,0.00,OTHER,

from expenses.

Thanks, this looks nice. I have expanded on this here https://github.com/md-arif-shaikh/expenses/blob/main/expenses.el#L622. Specifically expenses-test-import-data can test if the data are correctly imported. Can you give it a try? If it looks fine then I can implement the rest. Also, let me know what improvements could be made in terms of ease of use.

from expenses.

btw, for dates, you can look at parse-time-string. It does not recognize all kinds of formats though. I have tried to make most of it here https://github.com/md-arif-shaikh/expenses/blob/main/expenses.el#L546

from expenses.

expenses-import-expense at https://github.com/md-arif-shaikh/expenses/blob/main/expenses.el#L661 now enables to import and add an entry from CSV file.

from expenses.

When running the expenses-test-import-data I get the following error;
split-string: Wrong type argument: stringp, nil

This is with setting the date column to '1', if I set the date column to '0', then it imports but the date is wrong as it's picking up the account number as the date.

Also I was thinking when it imports it would be nice for it to prompt for a category if none exists per line with autocomplete. Then I could just assign the categories quickly as I go.

NOTE: I'm running Emacs 27.1

from expenses.

Let me check it on 27.1 to see if I can reproduce the error. For category one, I have implemented this in the expenses-import-expense function which is there in the latest commit.

from expenses.

Ok I just found the category section, that's great.

from expenses.

If I remark out the following line, then it works
;(setq date (expenses--convert-date-to-org-format date date-format))

from expenses.

I tested expenses-test-import-data on 27.1 for the demo file and it worked without issue. Are using the correct date-format? For the given file, i.e., it should be dd/mm/yyyy.

from expenses.

Yes I'm using the test data I posted before, weird, might be one of my many plugins. Let me try with just expenses

from expenses.

Still get the error.

GNU Emacs 27.1 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.23, cairo version 1.16.0)
 of 2021-01-18, modified by Debian

All I have in my init.el is

(add-to-list 'load-path "~/.emacs.d/external/expenses") 
(require 'expenses)

from expenses.

Just to be sure can you run these two to see if you get the same error

(defun expenses--convert-date-to-org-format (date format)
  "Convert given DATE to yyyy-mm-dd from given FORMAT."
  (let ((day-position)
	(month-position)
	(year-position)
	(sep)
	(day)
	(month)
	(year)
	(old-list))
    (if (member format '("yyyy-mm-dd" "yyyy/mm/dd" "dd/mm/yyyy"))
	(progn (cond ((string-equal format "yyyy-mm-dd")
		      (setq day-position 2)
		      (setq month-position 1)
		      (setq year-position 0)
		      (setq sep "-"))
		     ((string-equal format "yyyy/mm/dd")
		      (setq day-position 2)
		      (setq month-position 1)
		      (setq year-position 0)
		      (setq sep "/"))
		     ((string-equal format "dd/mm/yyyy")
		      (setq day-position 0)
		      (setq month-position 1)
		      (setq year-position 2)
		      (setq sep "/")))
	       (setq old-list (split-string date sep))
	       (setq day (nth day-position old-list))
	       (setq month (nth month-position old-list))
	       (setq year (nth year-position old-list))
	       (when (< (length year) 4)
		 (setq year (concat "20" year)))
	       (format "%s-%02s-%02s" year month day))
      (let* ((time-list (parse-time-string date)))
	(setq day (nth 3 time-list))
	(setq month (nth 4 time-list))
	(unless month
	  (setq month 1))
	(setq year (nth 5 time-list))
	(when (< year 2000)
	  (setq year (concat "20" year)))
	(format "%s-%02d-%02d" year month day)))))

(expenses--convert-date-to-org-format "25/12/2021" "dd/mm/yyyy")

Output should be 2021-12-25

from expenses.

Yes that works, I get 2021-12-25 in the message area.

from expenses.

Hmmm, then it seems like in the line (setq date (expenses--convert-date-to-org-format date date-format)) the date or date-format is not what it expects. Can you try this expenses-import-expense-with-bank-profile function with your bank profile set using expenses-bank-profiles. For the given file the profile would look like

(setq expenses-bank-profiles '(("bank-name" "," 1 3 "dd/mm/yyyy" 2 6)))

from expenses.

I get the same error
Wrong type argument: stringp, nil

from expenses.

If I display the format

    (if (member format '("yyyy-mm-dd" "yyyy/mm/dd" "dd/mm/yyyy"))
        (message format)
	(progn (cond ((string-equal format "yyyy-mm-dd")

Then I get the error when importing with expenses-import-expense-with-bank-profile
Not an Org time string: y-expen-01

But if I run the
(expenses--convert-date-to-org-format "25/12/2021" "dd/mm/yyyy")

I get
dd/mm/yyyy

from expenses.

In this snippet

    (if (member format '("yyyy-mm-dd" "yyyy/mm/dd" "dd/mm/yyyy"))
        (message format)
	(progn (cond ((string-equal format "yyyy-mm-dd")

it never goes beyond the (message format) line since if returns the first line if the argument is t. The way to do the intended check would be to put that line inside the (prog ...). Something like

 (if (member format '("yyyy-mm-dd" "yyyy/mm/dd" "dd/mm/yyyy"))
	(progn (message format)
        (cond ((string-equal format "yyyy-mm-dd")

from expenses.

ok I did that and got the same result. I think it's setting the format wrong somewhere along the line.

from expenses.

Also, could you check what date is returned just after this line https://github.com/md-arif-shaikh/expenses/blob/main/expenses.el#L614?

from expenses.

do you mean the initial error Wrong type argument: stringp, nil?

from expenses.

No I mean it returns the following still for when I run the test import data;
Not an Org time string: y-expen-01

from expenses.

When I take my message debug out I still get the error
Wrong type argument: stringp, nil

from expenses.

It doesn't get to L614, it errors out before that.

from expenses.

I could setup a CRDT session on emacs so you can see the problem within my emacs if you email me.

from expenses.

Okay, how does that work?

from expenses.

It's a package in emacs which allows you to share a buffer over the internet and work together in the same buffer in real time. It's pretty cool I've been using it when coding with a friend. I can send you the address and you can connect to my buffer which has the emacs with the problem.

from expenses.

Email me at [email protected] and I'll give you the access. Might make it easier for you see the issue directly.

from expenses.

I've done a talk on it here https://mlug-au.org search CRDT

from expenses.

great! Sent you an email, please share the link there.

from expenses.

This looks like fixed now.

from expenses.