CASE transactions drop: check which costs could be marked as an investment

One of the returning tasks for closing years, is to check whether or not certain booked costs couldn’t be marked as an investment, and are therefor wrongly booked as a cost.

Through STL you can always access all transaction lines of a certain account (if those transactions are synced or uploaded of course), in the so-called transactions drop. More info here.

You can loop over these transactions like this:

{% for tr in period.accounts.400000.first.transactions %}
  {{ tr.value }}
{% endfor %} 

We add the first method in there, because we need to tell Silverfin that the first account starting with 400000 needs to be accessed (of course, there always can be a account number like 4000000 f.i.).

Objects like value, date and relation can be accessed but other objects as well that are shown in a ledger of an account:

It’s important to know that the spelling of the field you want, needs to be respected. In the view of a ledger everything is capital but that doesn’t mean it’s in capital.
Above example for instance, the field REFERENTIE was uploaded as Referentie, so it should be accessed as Referentie and not REFERENTIE or referentie.
Spaces and points in the names should also be replaced by underscores!

Case

Let’s say we want a template that checks each transactionline to see if there are amounts there that should be booked as an investment instead of a cost:

This can be done this way:

  1. Creating a collection for the accounts and an input for the check on amount
{::infotext}
{% t "Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag" %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs  %}
{% t "Amount to be checked upon:" %}  {% input custom.check.amount_investment as:currency placeholder:0,00 %}
{:/infotext} 

We’ll loop over every account in the accounts_var costs later.

  1. create some variables we’ll use.
{% assign ok_check = "✔" %}
{% assign check_amount = custom.check.amount_investment %} 

The variable ok_check is some html-code to visually display a check.

  1. loop over the collection costs
{% for account in costs %}
...
{% endfor %}

In each loop we’ll define this variable:

{% assign current_acc = account.number %}

We will need this to display the account number for each loop, and also to access the transactions-drop of each account (= loop):

{% assign transactions = period.accounts[current_acc].first.transactions %}
  1. In each loop (account) we can loop over the collection transactions
{% for tr in transactions %}
...
{% endfor %}
  1. create some needed logic
{% assign too_high_amount = tr.value-check_amount %}

with:

              {% unless check_amount == blank %}
                {% if check_amount >= tr.value %}
                  {{ ok_check }}
                {% else %}
                  {::warningtext as="hover"}
                    {{ too_high_amount | currency }} {% t "too high" %}
                  {:/warningtext}                  
                {% endif %}
              {% endunless %} 

All the logic doesn’t needs to be executed when there’s no check amount inputted of course, that’s why we add the unless-statement in there:

{% unless check_amount == blank %} 
... 
{% endunless %}

If the var check_amount is bigger than the transaction value, we’ll display a checkmark; if not (else), we’ll display a warning-tag:

                {% if check_amount >= tr.value %}
                  {{ ok_check }}
                {% else %}
                  {::warningtext as="hover"}
                    {{ too_high_amount | currency }} {% t "too high" %}
                  {:/warningtext}                  
                {% endif %}

Here’s the complete code you can use:


{::infotext}
{% t "Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag" %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs  %}
{% t "Amount to be checked upon:" %}  {% input custom.check.amount_investment as:currency placeholder:0,00 %}
{:/infotext}

<br>

{% assign ok_check = "&#10004;" %}
{% assign check_amount = custom.check.amount_investment %}

{% stripnewlines %}
| {% t "Account" %}
| {% t "Tr. Date" %}
| {% t "Check" %}
| {% t "Tr. Value" %}
| {% t "Tr. Document" %}
| {% t "Tr. Reference" %}
{% newline %}
|----15%----
|:---10%----:
|-----5%----
|----10%----:
|----15%----
|----45%----#
{% for account in costs %}
  {% comment %}take current account to display for each account, and use it to call on transactions-drop of that account{% endcomment %}
    {% assign current_acc = account.number %}
    {% assign transactions = period.accounts[current_acc].first.transactions %}
      {% comment %}loop over all transactions for each account{% endcomment %}
        {% for tr in transactions %}
          {% newline %}
            | {% if forloop.first %}
                {% linkto account %}{{ current_acc }}{% endlinkto %}
              {% endif %}
            | {{ tr.date | date:"%d-%m-%Y" }}
            | {% assign too_high_amount = tr.value-check_amount %}
              {% unless check_amount == blank %}
                {% if check_amount >= tr.value %}
                  {{ ok_check }}
                {% else %}
                  {::warningtext as="hover"}
                    {{ too_high_amount | currency }} {% t "too high" %}
                  {:/warningtext}                  
                {% endif %}
              {% endunless %}
            | {{ tr.value | currency }}  
            | {{ tr.Documentnummer }}
            | {{ tr.referentie }}
        {% endfor %}
{% endfor %}
{% endstripnewlines %}

By accessing the transactions-drop you could make some of the needed repetitive tasks for closing a book year a lot more easy (for instance: checking whether transactions are 12 times present in a ledger, check whether or not double bookings are present (by checking the reference or doc number), … !

1 Like

Hi @sven

Would it be possible to choose the period?

We have around 2.000 invoices on these cost accounts and if we check this on 12/2018 we see a lot of transactions - It should be easier when we can add a certain period for the mutation.

Because this is a cumul check - the list getting bigger by each month.

Regards

Michael

Hi @MID,

You could make the list work with the current period only like this:

{% assign begindate_current = period.minus_1p.end_date+1 | date:"%s" %} 
{% assign enddate_current = period.end_date | date:"%s" %} 

In above code we create 2 variables (begin date and end date of the current period) which we’ll need in our for-loop.

The “%s” expresses a date in a certain number, so it’s easier to compare dates without having to format dates (transaction date can be like 01/02/2018 while the date of the period is 2018-03-31)

In the forloop we’ll express the date of the transaction like this too:

{% assign tr_date = tr.date | date:"%s" %}  

If you have these, you can create an if-statement like this within the forloop:

{% if tr_date > begindate_current and tr_date < enddate_current %} 
... 
{% endif %}

Could this work for you?

Hi @Sven

Thx - I have tried to adjust the code but I think ik do something wrong :slight_smile:

Can you take a quick look at it ? :slight_smile:

It looks like this in Silverfin

Code

{::infotext}
{% t “Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag” %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs %}
{% t “Amount to be checked upon:” %} {% input custom.check.amount_investment as:currency placeholder:0,00 %}

{% assign begindate_current = period.minus_1p.end_date+1 | date:"%s" %} {{ begindate_current }}
{% assign enddate_current = period.end_date | date:"%s" %} {{ enddate_current }}

{:/infotext}


{% assign ok_check = “:heavy_check_mark:” %}
{% assign check_amount = custom.check.amount_investment %}

{% stripnewlines %}
| {% t “Account” %}
| {% t “Tr. Date” %}
| {% t “Check” %}
| {% t “Tr. Value” %}
| {% t “Tr. Document” %}
| {% t “Tr. Reference” %}
{% newline %}
|----15%----
|:—10%----:
|-----5%----
|----10%----:
|----15%----
|----45%----#
{% for account in costs %}
{% comment %}take current account to display for each account, and use it to call on transactions-drop of that account{% endcomment %}
{% assign current_acc = account.number %}
{% assign transactions = period.accounts[current_acc].first.transactions %}
{% comment %}loop over all transactions for each account{% endcomment %}
{% for tr in transactions %}
{% newline %}
| {% if forloop.first %}
{% linkto account %}{{ current_acc }}{% endlinkto %}
{% endif %}
| {% assign tr_date = tr.date | date:"%s" %}
{% if tr_date > begindate_current and tr_date < enddate_current %}
{% endif %}
| {% assign too_high_amount = tr.value-check_amount %}
{% unless check_amount == blank %}
{% if check_amount >= tr.value %}
{{ ok_check }}
{% else %}
{::warningtext as=“hover”}
{{ too_high_amount | currency }} {% t “too high” %}
{:/warningtext}
{% endif %}
{% endunless %}
| {{ tr.value | currency }}
| {{ tr.Documentnummer }}
| {{ tr.referentie }}
{% endfor %}
{% endfor %}
{% endstripnewlines %}

Sorry @MID,

That code needs to be changed into this:

{% assign begindate_current = period.minus_1p.end_date+1 | date:"%s" %} 
{% assign enddate_current = period.end_date | date:"%s" %} 

In the previous code I was testing the output of the variable

Hi @Sven

Still not working :slight_smile:

It keeps giving me all the transactions - I think I missed a line?

Thx
Michael

Post your code again @MID, but put it in between three ` like this:

Not sure what we are missing here for now

Hi @sven

Here you go

{% t "Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag" %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs  %}
{% t "Amount to be checked upon:" %}  {% input custom.check.amount_investment as:currency placeholder:0,00 %}

{% assign begindate_current = period.minus_1p.end_date+1 | date:"%s" %} 
{% assign enddate_current = period.end_date | date:"%s" %} 

{:/infotext}

<br>

{% assign ok_check = "&#10004;" %}
{% assign check_amount = custom.check.amount_investment %}

{% stripnewlines %}
| {% t "Account" %}
| {% t "Tr. Date" %}
| {% t "Check" %}
| {% t "Tr. Value" %}
| {% t "Tr. Document" %}
| {% t "Tr. Reference" %}
{% newline %}
|----15%----
|:---10%----:
|-----5%----
|----10%----:
|----15%----
|----45%----#
{% for account in costs %}
  {% comment %}take current account to display for each account, and use it to call on transactions-drop of that account{% endcomment %}
    {% assign current_acc = account.number %}
    {% assign transactions = period.accounts[current_acc].first.transactions %}
      {% comment %}loop over all transactions for each account{% endcomment %}
        {% for tr in transactions %}
          {% newline %}
            | {% if forloop.first %}
                {% linkto account %}{{ current_acc }}{% endlinkto %}
              {% endif %}
            | {% assign tr_date = tr.date | date:"%s" %}  
              {% if tr_date > begindate_current and tr_date < enddate_current %}
              {% endif %}
            | {% assign too_high_amount = tr.value-check_amount %}
              {% unless check_amount == blank %}
                {% if check_amount >= tr.value %}
                  {{ ok_check }}
                {% else %}
                  {::warningtext as="hover"}
                    {{ too_high_amount | currency }} {% t "too high" %}
                  {:/warningtext} 
                {% endif %}
              {% endunless %}
            | {{ tr.value | currency }}  
            | {{ tr.Documentnummer }}
            | {{ tr.referentie }}
        {% endfor %}
{% endfor %}
{% endstripnewlines %}```

Your if-statement is not covering the whole loop of the transactions for each account @MID.

Modified and tweaked it a little bit (additional info added between the comment tags):

{::infotext}
{% t "Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag" %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs  %}
{% t "Amount to be checked upon:" %}  {% input custom.check.amount_investment as:currency placeholder:0,00 %}
{:/infotext}

{% comment %}create begin and end date of the current period{% endcomment %}
{% assign begindate_current = period.minus_1p.end_date+1 | date:"%s" %} 
{% assign enddate_current = period.end_date | date:"%s" %} 

<br>

{% assign ok_check = "&#10004;" %}
{% assign check_amount = custom.check.amount_investment %}

{% stripnewlines %}
| {% t "Account" %}
| {% t "Tr. Date" %}
| {% t "Check" %}
| {% t "Tr. Value" %}
| {% t "Tr. Document" %}
| {% t "Tr. Reference" %}
{% newline %}
|----15%----
|:---10%----:
|-----5%----
|----10%----:
|----15%----
|----45%----#
{% for account in costs %}
  {% comment %}take current account to display for each account, and use it to call on transactions-drop of that account{% endcomment %}
    {% assign current_acc = account.number %}
    {% assign transactions = period.accounts[current_acc].first.transactions %}
    {% if transactions.value != 0 %}
      {% newline %}
      | {% linkto account %}{{ account.number }}{% endlinkto %}
    {% endif %}
      {% comment %}loop over all transactions for each account{% endcomment %}
        {% for tr in transactions %}
          {% comment %}assign the date of tranaction to local var to compare with dates current period{% endcomment %}
          {% assign tr_date = tr.date | date:"%s" %}
          {% comment %}check each transaction if it's in the current period or not{% endcomment %}
          {% if tr_date >= begindate_current and tr_date <= enddate_current %} 
          {% newline %}
            | 
            | {{ tr.date }}
            | {% assign too_high_amount = tr.value-check_amount %}
              {% unless check_amount == blank %}
                {% if check_amount >= tr.value %}
                  {{ ok_check }}
                {% else %}
                  {::warningtext as="hover"}
                    {{ too_high_amount | currency }} {% t "too high" %}
                  {:/warningtext} 
                {% endif %}
              {% endunless %}
            | {{ tr.value | currency }}  
            | {{ tr.Documentnummer }}
            | {{ tr.referentie }}
          {% endif %}  
        {% endfor %}
{% endfor %}
{% endstripnewlines %}