{"id":1025,"date":"2021-09-12T01:34:09","date_gmt":"2021-09-11T22:34:09","guid":{"rendered":"https:\/\/blog.andresgomez.org\/?p=1025"},"modified":"2021-09-12T01:39:11","modified_gmt":"2021-09-11T22:39:11","slug":"basic-libreoffice-invoice-in-finland","status":"publish","type":"post","link":"https:\/\/blog.andresgomez.org\/es\/2021\/09\/12\/basic-libreoffice-invoice-in-finland\/","title":{"rendered":"Basic LibreOffice invoice in Finland"},"content":{"rendered":"\n

WARNING<\/h2>\n\n\n\n

I have no responsibility if this guide and the linked examples are bogus and cause you any harm. The purpose of this post is solely as a personal note for myself. Follow and use it at your own risk.<\/p>\n\n\n\n

\n\n\n\n

Through this post we’ll see how to create an invoice with LibreOffice<\/a> which would include the Finnish Reference Number (Viitenumero) or the RF Creditor Reference generated from that Finnish Reference Number and the Finnish Bank Bar Code (Pankkiviivakoodi).<\/p>\n\n\n\n

\n\n\n\n

The Finnish Reference Number<\/h2>\n\n\n\n

The Finnish Reference Number (viitenumero<\/a>) identifies a specific bank transfer. In practice, it is used in invoices sent to a customer by a company or similar entity.<\/p>\n\n\n\n

When we create an invoice in Finland, we want to include such number so its payment is following a standard set by the Finnish banks. For example, some banks may charge a commission if, when setting a payment, this number is not included.<\/p>\n\n\n\n

The generation of this number follows a simple algorithm<\/a> and I’ve compiled several implementations in different programming languages. Since we want to use it in LibreOffice Calc<\/a>, the most suitable way would be through a (Basic) macro<\/a>. It’s usage is as follows:<\/p>\n\n\n\n

=LASKEVIITE(number)<\/pre>\n\n\n\n
Which number to pass you say? Well, it is pretty much whatever you want to pass (check the algorithm for the restrictions) but I basically pass the incremental invoice number. In my case, I just number my invoices like YYYY###<\/code> meaning the 4 digits of the current year plus 3 digits for the incremental number of that invoice along the year (not in my wildest dreams I would generate more than 999 invoices in a single year). For example 2021023<\/code> would indicated the 23rd invoice generated in 2021. The result in the cell will show 202 10236<\/code>, which is the provided number plus a check digit calculated by the algorithm and returned in a proper formatting (groups of 5 numbers).<\/p>\n\n\n\n
The RF Creditor Reference<\/a><\/h2>\n\n\n\n
The RF Creditor Reference<\/a> is an international business standard for an number preceded by the \u00abRF\u00bb letters which serves a similar purpose than the Finnish Reference Number: identifying a specific payment.<\/p>\n\n\n\n
The generation of this number also follows a simple algorithm<\/a> and it was first implemented within the SEPA rulebook 3.2. Since having both numbers in the same invoice would be redundant and the RF Creditor Reference is international, we could just use this number and it should be accepted by the Finnish banks and some other banks, particularly some of those accepting SEPA transfers.<\/p>\n\n\n\n
If we use the Finnish Reference Number, the calculation of the RF Creditor Reference check digits is pretty simple<\/a>. We can just use the following formula in a cell:<\/p>\n\n\n\n
=TEXT(98-MOD(SUBSTITUTE(LASKEVIITE(number),\" \",\"\")*1000000+271500,97),\"00\")<\/pre>\n\n\n\n
Following the example above, for the generated Finnish Reference Number 202 10236<\/code>, the result in the cell will show 42<\/code>. Then, we only have to concatenate everything together to get the full RF Creditor Reference: RF42 2021 0236<\/code>.<\/p>\n\n\n\n
Bar codes<\/h2>\n\n\n\n
Finnish banks have jointly developed the (Finnish) Bank Bar Code (Pankkiviivakoodi) in order to accelerate payments and to prevent erroneous data from being keyed in. The Bank Bar Code is a Code 128<\/a> bar code explicitly developed to be used with reference numbers in invoicing.<\/p>\n\n\n\n
Hence, we want to add support to our Calc spreadsheet for generating bar codes. Fortunately, some Ji\u0159\u00ed Gabriel<\/strong><\/em> created a sophisticated (basic) macro<\/a> for generating a whole set of 1D and 2D bar codes. This macro is able to generate the bar codes as graphical objects or as text which will be shown as the proper bar code when the companion\u00a0BarsAndSpaces.ttf<\/a>\u00a0font is applied to the text. In my examples, I use the latter but you may prefer the former since that would allow you not to have that font installed<\/strong>.<\/p>\n\n\n\n
This formula will show as cell result a Code 128 bar code containing the passed number<\/code> if we apply the BarsAndSpaces.ttf font to its content:<\/p>\n\n\n\n
=ENCODEBARCODE(CELL(\"SHEET\"),CELL(\"ADDRESS\"),number,0,0)<\/pre>\n\n\n\n
The Finnish Bank Bar Code<\/h2>\n\n\n\n
The Bank Bar Code is a form of presenting payment transaction data, approved by Finance
Finland (FFI). As commented above, it was jointly developed by Finnish banks and needs a reference number.<\/p>\n\n\n\n
The Bank Bar Code has 2 versions currently in active use: version 4 uses the Finnish Reference Number while version 5 uses the RF Creditor Reference. Below, you can see how the 54 length structure of this bar code is divided depending on the version:<\/p>\n\n\n\n
Data<\/td>Length<\/td>Value<\/td><\/tr>
Version<\/td>1<\/td>4<\/td><\/tr>
The numeric part of the payee\u2019s bank
account number (IBAN)<\/td>		16<\/td>N<\/td><\/tr>
Euros<\/td>6<\/td>N<\/td><\/tr>
Cents<\/td>2<\/td>N<\/td><\/tr>
Reserve<\/td>3<\/td>000<\/td><\/tr>
Finnish Reference Number<\/td>20<\/td>N<\/td><\/tr>
Due Date<\/td>6<\/td>YYMMDD<\/td><\/tr><\/tbody><\/table>
(Finnish) Bank Bar Code v.4<\/figcaption><\/figure>\n\n\n\n
Data<\/td>Length<\/td>Value<\/td><\/tr>
Version<\/td>1<\/td>5<\/td><\/tr>
The numeric part of the payee\u2019s bank
account number (IBAN)<\/td>		16<\/td>N<\/td><\/tr>
Euros<\/td>6<\/td>N<\/td><\/tr>
Cents<\/td>2<\/td>N<\/td><\/tr>
The numeric part of the RF
Creditor Reference<\/td>		23<\/td>N<\/td><\/tr>
Due Date<\/td>6<\/td>YYMMDD<\/td><\/tr><\/tbody><\/table>
(Finnish) Bank Bar Code v.5<\/figcaption><\/figure>\n\n\n\n
As you see, the algorithm is simple enough that can be easily implemented in a spreadsheet.<\/p>\n\n\n\n
The full documentation<\/a> for the Bank Bar Code is provided by Finance Finland<\/a>.<\/p>\n\n\n\n
Invoice examples<\/h2>\n\n\n\n
After all the blabbering above we get to the part that is, probably, the most interesting for you: the ODS<\/a> examples.<\/p>\n\n\n\n