Skip to main content

Customize the bookkeeping export file

When exporting accounting postings to your accounting software, you may find yourself in a situation where the standard accounting files in the FA does not fit the situation (Postings export, SIE report).

You can also use a query and configure the columns (and their headings) as needed. Below you can find two sample queries: one that can be used for exporting specific postings based on their IDs and one for fetching postings in a given portfolio or group of portfolios between given dates.

Note

Be sure to use accounting reporting to keep your export statuses up to date.

Sample query 1: export of specific postings based on their IDs

This query lists all accounting postings given in the postingIds-parameter. This query can be used manually to check postings between certain dates as well as for exporting in XLS-format from the Queries-view, but is more conveniently used with the Bookkeeping reporting functionality. Posting ID is not shown on any view, except when included in a query on the Queries view. The correct column is the id column of the table pm2_posting.

3040280618.png

To add the query, go to the Queries view, click Show query on the default tab titled Queries. Copy the query below and make any modifications that you wish, keeping in mind that the WHERE pos.id in ($P(postingIds)) should be left intact. Save the query by clicking + in the upper right corner, giving it a name in the provided text field and clicking Save.

select pos.portfolio_short_name AS "Portfolio",pos.account_number AS "Bookkeeping account", pos.effective_date AS "Date", pos.amount AS "Amount", CONCAT ("Transaction number: ",pos.transaction_ext_id,", ",s.name) AS "Description"
FROM pm2_posting pos
LEFT JOIN pm2_security s ON pos.security_id = s.id
WHERE pos.id IN ($P(postingIds))

Using the query in Bookkeeping reporting

In order to use the query in Bookkeeping reporting, do the configuration in the Accounting postings export tab in Tools → Administer → Data aggregation… Fill in the requested parameters:

  • Enter the name of the query that you used in the previous step under Query name.

  • Select a language for number formatting. The language affects what kind of thousand and decimal separators are used for formatting numbers in the exported files.

  • Select a date format from the options provided. The abbreviations are the following:

    • d means day

    • M means month

    • y means year

  • Select the delimiter to use in the file, such as comma or semi-colon.

  • Select the encoding depending on what your accounting software supports. The default is the widely supported Unicode UTF-8.

  • Select omit header row, in case your accounting software expects a file without headers.

bookkeeping_administer_query_export.png

The previous step needs to be done only the first time the query is used or if you wish to change the query or any of the other settings.

In order to export your postings, go to the Bookkeeping view, enter the relevant search criteria, do a search and select Bookkeeping reporting → Custom CSV. The tool provides you with the CSV export and gives you an option to mark the exported postings as reported. Please note that the tool only includes postings that are in the status Ready in the export.

Sample query 2: postings in a given portfolio between given dates

This query lists the ready accounting exports (selected portfolio and time). This query can be used manually to check postings between certain dates as well as for exporting in XLS-format from the Queries-view.

2877554698.png
select pos.portfolio_short_name AS "Portfolio",pos.account_number AS "Bookkeeping account",DATE_FORMAT(pos.effective_date, "%e.%c.%Y") AS "Date", pos.amount AS "Amount", CONCAT ("Transaction number: ",pos.transaction_ext_id,", ",s.name) AS "Description"
FROM pm2_posting pos
        LEFT JOIN pm2_security s ON pos.security_id = s.id
WHERE (pos.pf_id IN $P(portfolioId))
        AND ($P(startDate) IS NULL OR $P(startDate) <= pos.posting_date) 
        AND ($P(endDate) IS NULL OR $P(endDate) >= pos.posting_date)
        AND pos.status = "READY"
In this section: