I have text files with tables like this:
Investment advisory and
related fees receivable (161,570 ) (71,739 ) (73,135 )
Net purchases of trading
investments (93,261 ) (30,701 ) (11,018 )
Other receivables 61,216 (10,352 ) (69,313 )
Restricted cash 20,658 (20,658 ) -
Other current assets (39,643 ) 14,752 64
Other non-current assets 71,896 (26,639 ) (26,330 )
Since these are accounting numbers, parenthesized numbers indicate negative numbers.
Dashes represent 0 or no number.
I'd like to be able to mark a rectangular region such as third column above,
call a function (format-column), and automatically have
(-73135-11018-69313+64-26330)/1000 sitting in my kill-ring.
Even better would be -73.135-11.018-69.313+0.064-26.330 but I couldn't figure out a way to transform 64 -- 0.064.
This is what I've come up with:
(defun format-column ()
"format accounting numbers in a rectangular column. format-column puts the result
in the kill-ring"
(interactive)
(let ((p (point))
(m (mark))
)
(copy-rectangle-to-register 0 (min m p) (max m p) nil)
(with-temp-buffer
(insert-register 0)
(goto-char (point-min))
(while (search-forward "-" nil t) (replace-match "" nil t))
(goto-char (point-min))
(while (search-forward "," nil t) (replace-match "" nil t))
(goto-char (point-min))
(while (search-forward ")" nil t) (replace-match "" nil t))
(goto-char (point-min))
(while (search-forward "(" nil t)
(replace-match "-" nil t)
(just-one-space)
(delete-backward-char 1)
)
(goto-char (point-min))
(while (search-forward "\n" nil t) (replace-match " " nil t))
(goto-char (point-min))
(kill-new
(mapconcat 'identity
(split-string (buffer-substring (point-min) (point-max))) "+"))
(kill-region (point-min) (point-max))
(insert "(")
(yank 2)
(goto-char (point-min))
(while (search-forward "+-" nil t) (replace-match "-" nil t))
(goto-char (point-max))
(insert ")/1000")
(kill-region (point-min) (point-max))
)
)
)
(global-set-key "\C-c\C-f" 'format-column)
Although it seems to work, I'm sure this function
is poorly coded.
The repetitive calls to goto-char, search-forward,
and replace-match
and the switching from buffer to string and back to buffer seems ugly and inelegant. My entire approach may be wrong-headed, but I don't know enough elisp to make this more beautiful.
Do you see a better way to write format-column, and/or
could you make suggestions on how to improve this code?
