bankstatement.cls – A LaTeX class for bank statements based on csv data

Feliz Navidad y un Feliz Año Nuevo

kleberj 24. December 2016 0 Comments

bankstatement 0.9.2 available in CTAN

kleberj 16. November 2015 0 Comments

Recently, the new version 0.9.2 of bankstatement is available in CTAN (see Announcement (ctan-ann@ctan.org)). Here is a brief summary of the new features in the README:

Changes in v0.9.2:

* use of siunitx to force two decimals, if csv data contains
other formats like 16.2 -> 16.20

* added support for Standard Bank (Namibia) and namibian

feature requests by Dr. Eberhard Lisse

* organized package in dtx format

The update should be available soon also in TeXLive and MiKTeX!

bankstatement, develop

bankstatement, CTAN

HowTo support arbitrary CSV formats

kleberj 13. November 2015 0 Comments

#bankstatement #howto

Using the example of the new csv format file csv-standard-bank-na.def I would like to explain – hopefully comprehensible – how to support more or less arbitrary csv formats. Take just one of the existing files and adjust them accordingly!

\renewcommand*\STM@JK@dbkeys{ValueDate,PostingDate,Reference,Narrative,Amount,Balance}%
\renewcommand*\STM@JK@DTLforeach%
{%
  \DTLforeach{\STM@JK@dbname}%
  {\postingdate=PostingDate,\valuedate=ValueDate,\reference=Reference,\narrative=Narrative,\amount=Amount,\balance=Balance}%
  {%
    \parbox[t]{\widthof{ (YYYY-MM-DD) }}{\leavevmode\hphantom{(}\postingdate\\(\valuedate)} &%
    \parbox[t]{\STM@JK@referencewidth}{\raggedright%
                                       \DTLifstringeq{\reference}{}{}{\reference\\}%
                                       \DTLifstringeq{\narrative}{}{}{\narrative\\}%
                                      } &%
    \parbox[t]{\widthof{1234567.89)}}{\raggedleft\DTLifStartsWith{\amount}{-}{\textcolor{\STM@JK@negativecolor}{\num{\amount}\hphantom{)}}}{\num{\amount}\hphantom{)}}\\(\num{\balance})}
    \DTLiflastrow{%
                   \ifthenelse{\equal{\STM@JK@closingbalance}{none}}%
                   {}%
                   {\\\midrule\\ & \STM@JK@closingbalancename & \DTLifStartsWith{\STM@JK@closingbalance}{-}%
                    {\textcolor{\STM@JK@negativecolor}{\num{\STM@JK@closingbalance}}}{\num{\STM@JK@closingbalance}}}%
                   \\\bottomrule%
                 }%
                 {\\ & & \\}%
  }%
}%
\DTLsetseparator{,}%
\renewcommand*\STM@JK@noheader{false}%
\sisetup{detect-all,round-integer-to-decimal,round-mode=places,round-precision=2}%

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

\renewcommand*\STM@JK@dbkeys{ValueDate,PostingDate,Reference,Narrative,Amount,Balance}%

\renewcommand*\STM@JK@DTLforeach%

{%

\DTLforeach{\STM@JK@dbname}%

{\postingdate=PostingDate,\valuedate=ValueDate,\reference=Reference,\narrative=Narrative,\amount=Amount,\balance=Balance}%

{%

\parbox[t]{\widthof{ (YYYY-MM-DD) }}{\leavevmode\hphantom{(}\postingdate\\(\valuedate)} &%

\parbox[t]{\STM@JK@referencewidth}{\raggedright%

\DTLifstringeq{\reference}{}{}{\reference\\}%

\DTLifstringeq{\narrative}{}{}{\narrative\\}%

} &%

\parbox[t]{\widthof{1234567.89)}}{\raggedleft\DTLifStartsWith{\amount}{-}{\textcolor{\STM@JK@negativecolor}{\num{\amount}\hphantom{)}}}{\num{\amount}\hphantom{)}}\\(\num{\balance})}

\DTLiflastrow{%

\ifthenelse{\equal{\STM@JK@closingbalance}{none}}%

{}%

{\\\midrule\\ & \STM@JK@closingbalancename & \DTLifStartsWith{\STM@JK@closingbalance}{-}%

{\textcolor{\STM@JK@negativecolor}{\num{\STM@JK@closingbalance}}}{\num{\STM@JK@closingbalance}}}%

\\\bottomrule%

}%

{\\ & & \\}%

}%

\DTLsetseparator{,}%

\renewcommand*\STM@JK@noheader{false}%

\sisetup{detect-all,round-integer-to-decimal,round-mode=places,round-precision=2}%

First one determines the order of the keys in the csv file.

TeX

\renewcommand*\STM@JK@dbkeys{ValueDate,PostingDate,Reference,Narrative,Amount,Balance}%

1

\renewcommand*\STM@JK@dbkeys{ValueDate,PostingDate,Reference,Narrative,Amount,Balance}%

Then we define \STM@JK@DTLforeach ; a macro that reads the csv file line by line and outputs them as LaTeX table row. It uses to the \DTLforeach macro (datatool.sty)

\renewcommand*\STM@JK@DTLforeach%
{%
  \DTLforeach{\STM@JK@dbname}%
  {\postingdate=PostingDate,\valuedate=ValueDate,\reference=Reference,\narrative=Narrative,\amount=Amount,\balance=Balance}%
  {%
    \parbox[t]{\widthof{ (YYYY-MM-DD) }}{\leavevmode\hphantom{(}\postingdate\\(\valuedate)} &%
    \parbox[t]{\STM@JK@referencewidth}{\raggedright%
                                       \DTLifstringeq{\reference}{}{}{\reference\\}%
                                       \DTLifstringeq{\narrative}{}{}{\narrative\\}%
                                      } &%
    \parbox[t]{\widthof{1234567.89)}}{\raggedleft\DTLifStartsWith{\amount}{-}{\textcolor{\STM@JK@negativecolor}{\num{\amount}\hphantom{)}}}{\num{\amount}\hphantom{)}}\\(\num{\balance})}
    \DTLiflastrow{%
                   \ifthenelse{\equal{\STM@JK@closingbalance}{none}}%
                   {}%
                   {\\\midrule\\ & \STM@JK@closingbalancename & \DTLifStartsWith{\STM@JK@closingbalance}{-}%
                    {\textcolor{\STM@JK@negativecolor}{\num{\STM@JK@closingbalance}}}{\num{\STM@JK@closingbalance}}}%
                   \\\bottomrule%
                 }%
                 {\\ & & \\}%
  }%
}%

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

\renewcommand*\STM@JK@DTLforeach%

{%

\DTLforeach{\STM@JK@dbname}%

{\postingdate=PostingDate,\valuedate=ValueDate,\reference=Reference,\narrative=Narrative,\amount=Amount,\balance=Balance}%

{%

\parbox[t]{\widthof{ (YYYY-MM-DD) }}{\leavevmode\hphantom{(}\postingdate\\(\valuedate)} &%

\parbox[t]{\STM@JK@referencewidth}{\raggedright%

\DTLifstringeq{\reference}{}{}{\reference\\}%

\DTLifstringeq{\narrative}{}{}{\narrative\\}%

} &%

\parbox[t]{\widthof{1234567.89)}}{\raggedleft\DTLifStartsWith{\amount}{-}{\textcolor{\STM@JK@negativecolor}{\num{\amount}\hphantom{)}}}{\num{\amount}\hphantom{)}}\\(\num{\balance})}

\DTLiflastrow{%

\ifthenelse{\equal{\STM@JK@closingbalance}{none}}%

{}%

{\\\midrule\\ & \STM@JK@closingbalancename & \DTLifStartsWith{\STM@JK@closingbalance}{-}%

{\textcolor{\STM@JK@negativecolor}{\num{\STM@JK@closingbalance}}}{\num{\STM@JK@closingbalance}}}%

\\\bottomrule%

}%

{\\ & & \\}%

}%

As the first argument you pass the name of the csv file. No change!
The second argument maps the csv keys to macros

In the third and final argument, the output of the three-column table row is done

In the first column we output the posting and value dates

TeX

\parbox[t]{\widthof{ (YYYY-MM-DD) }}{\leavevmode\hphantom{(}\postingdate\\(\valuedate)} &%

1

\parbox[t]{\widthof{ (YYYY-MM-DD) }}{\leavevmode\hphantom{(}\postingdate\\(\valuedate)} &%

In the second column we output the reference, which may be composed of several csv keys.

\parbox[t]{\STM@JK@referencewidth}{\raggedright%
                                   \DTLifstringeq{\reference}{}{}{\reference\\}%
                                   \DTLifstringeq{\narrative}{}{}{\narrative\\}%
                                  } &%

1

2

3

4

\parbox[t]{\STM@JK@referencewidth}{\raggedright%

\DTLifstringeq{\reference}{}{}{\reference\\}%

\DTLifstringeq{\narrative}{}{}{\narrative\\}%

} &%

In the third column we output the value. A negative amount will be typeset in red (negativcolor option)

\parbox[t]{\widthof{1234567.89)}}{\raggedleft\DTLifStartsWith{\amount}{-}{\textcolor{\STM@JK@negativecolor}{\num{\amount}\hphantom{)}}}{\num{\amount}\hphantom{)}}\\(\num{\balance})}

1	\parbox[t]{\widthof{1234567.89)}}{\raggedleft\DTLifStartsWith{\amount}{-}{\textcolor{\STM@JK@negativecolor}{\num{\amount}\hphantom{)}}}{\num{\amount}\hphantom{)}}\\(\num{\balance})}

Finally, a test on the last row of data is performed and the closing balance or a blank line is issued.

\DTLiflastrow{%
               \ifthenelse{\equal{\STM@JK@closingbalance}{none}}%
               {}%
               {\\\midrule\\ & \STM@JK@closingbalancename & \DTLifStartsWith{\STM@JK@closingbalance}{-}{\textcolor{\STM@JK@negativecolor}{\num{\STM@JK@closingbalance}}}{\num{\STM@JK@closingbalance}}}%
               \\\bottomrule%
             }%
             {\\ & & \\}%

1

2

3

4

5

6

7

\DTLiflastrow{%

\ifthenelse{\equal{\STM@JK@closingbalance}{none}}%

{}%

{\\\midrule\\ & \STM@JK@closingbalancename & \DTLifStartsWith{\STM@JK@closingbalance}{-}{\textcolor{\STM@JK@negativecolor}{\num{\STM@JK@closingbalance}}}{\num{\STM@JK@closingbalance}}}%

\\\bottomrule%

}%

{\\ & & \\}%

In the last step, we define the separator of the csv file and determine whether it has a header row (Caution: header → noheader=false! (noheader option (datatool.sty))). The last line can be usually simply copied (siunitx setup)!

TeX

\DTLsetseparator{,}% \renewcommand*\STM@JK@noheader{false}% \sisetup{detect-all,round-integer-to-decimal,round-mode=places,round-precision=2}%

1
2
3

\DTLsetseparator{,}%
\renewcommand*\STM@JK@noheader{false}%
\sisetup{detect-all,round-integer-to-decimal,round-mode=places,round-precision=2}%

Hopefully this helps to adapt and support other csv formats! 😉

bankstatement, csv, howto

HowTo support other languages

kleberj 13. November 2015 0 Comments

#bankstatement #howto

Using the example of the new file stmnamibian.def I’d like to briefly explain how to support other languages. One recognizes immediately, that the filename must follow the pattern stmLANGUAGE.def. First, we consider the definition file for english:

\renewcommand*\STM@JK@holdername{Account Holder}%
\renewcommand*\STM@JK@bicname{BIC}%
\renewcommand*\STM@JK@ibanname{IBAN}%
\renewcommand*\STM@JK@firstcolumnheading{\bfseries Date}%
\renewcommand*\STM@JK@secondcolumnheading{\bfseries Reference}%
\renewcommand*\STM@JK@thirdcolumnheading{\bfseries Amount (GBP)}%
\renewcommand*\STM@JK@openingbalancename{Opening Balance}%
\renewcommand*\STM@JK@closingbalancename{Closing Balance}%
\sisetup{output-decimal-marker={.}}%
\endinput

1

2

3

4

5

6

7

8

9

10

\renewcommand*\STM@JK@holdername{Account Holder}%

\renewcommand*\STM@JK@bicname{BIC}%

\renewcommand*\STM@JK@ibanname{IBAN}%

\renewcommand*\STM@JK@firstcolumnheading{\bfseries Date}%

\renewcommand*\STM@JK@secondcolumnheading{\bfseries Reference}%

\renewcommand*\STM@JK@thirdcolumnheading{\bfseries Amount (GBP)}%

\renewcommand*\STM@JK@openingbalancename{Opening Balance}%

\renewcommand*\STM@JK@closingbalancename{Closing Balance}%

\sisetup{output-decimal-marker={.}}%

\endinput

If you want to support a namibian bank now, it requires of course the change of currency (3rd column) and the SEPA terms BIC and IBAN:

\renewcommand*\STM@JK@holdername{Account Holder}%
\renewcommand*\STM@JK@bicname{Branch Code}%
\renewcommand*\STM@JK@ibanname{Account Number}%
\renewcommand*\STM@JK@firstcolumnheading{\bfseries Date}%
\renewcommand*\STM@JK@secondcolumnheading{\bfseries Reference}%
\renewcommand*\STM@JK@thirdcolumnheading{\bfseries Amount (NAD)}%
\renewcommand*\STM@JK@openingbalancename{Opening Balance}%
\renewcommand*\STM@JK@closingbalancename{Closing Balance}%
\sisetup{output-decimal-marker={.}}%
\endinput

1

2

3

4

5

6

7

8

9

10

\renewcommand*\STM@JK@holdername{Account Holder}%

\renewcommand*\STM@JK@bicname{Branch Code}%

\renewcommand*\STM@JK@ibanname{Account Number}%

\renewcommand*\STM@JK@firstcolumnheading{\bfseries Date}%

\renewcommand*\STM@JK@secondcolumnheading{\bfseries Reference}%

\renewcommand*\STM@JK@thirdcolumnheading{\bfseries Amount (NAD)}%

\renewcommand*\STM@JK@openingbalancename{Opening Balance}%

\renewcommand*\STM@JK@closingbalancename{Closing Balance}%

\sisetup{output-decimal-marker={.}}%

\endinput

bankstatement, howto, language

Support for namibian and Standard Bank (Namibia)

kleberj 11. November 2015 0 Comments

#bankstatement #develop

The latest version of bankstatement now includes an adapted language definition file for namibian.
In addition, the csv format of the Standard Bank (Namibia) is supported.

bankstatement, develop

bankstatement, csv, language

siunitx for rounding to 2 decimals exactly

kleberj 10. November 2015 0 Comments

#bankstatement #develop

The latest version of bankstatement ensures that account transactions having exactly two decimals. Especially when an amount of e.g. 16.2€ is stored in the csv file (16.2 → 16.20).

In addition, the use of siunitx allows the determination of the decimal marker on dependence on the language – however independent of notation in the csv file, like \sisetup{output-decimal-marker={.}} in the file stmenglish.def. This is more important in other languages where the comma acts as decimal marker!

bankstatement, develop

bankstatement, siunitx

bankstatement.jklatex.de online

kleberj 7. November 2015 0 Comments

Hello World or bankstatement.jklatex.de!

Welcome on the new homepage of the LaTeX class bankstatement. In the future you will find here examples, new features or anything new about bankstatement.cls!