Using data from an account

Hi,

Is it possible to use data from an account which is not the entire period for that fiscal year?

I want to create a template to calculate the VAT-deductability. This needs always to be calculate on 31/12 with no regard on how the fiscal year is.

So for fiscal year that end on 31/12 I have no problem.

But how can you solve that when the fiscal year ends for instance 30/09?
I need then the data 01/10 - 31/12 of the current year and 01/01 - 30/09 of the previous year.

Is this possible?

Kind regards.

Hi @Loic_Cogels,

You probably mean that, when the current period is 30/9 (bookyear 1/10 - 30/9), you want to have the year-to-date value of a certain account? I’m a bit confused, sorry :grimacing:

Anyway, if you call on #61 in a period, it will give you the year-to-date value from the start of the book year until your current period.
Want to call upon previous period? That’s also possible, with adding :1p to go back one period, or :1y to go one bookyear back.

For example:

{{ #61:2p | currency }} gives the YTD value of 2 periods back
{{ #55:1y | currency }} gives the YTD value of current period for previous bookyear

Don’t know if that’s a bit clear?

Hi Sven,

For the current fiscal year 01/10/2020 - 30/09/2021, I want to know what the value of the account is for the period 01/01/2020 - 31/12/2020.

Could you share how you are doing it now? For example, are you looping over the calendar years one book year can hold?

Hi Sven,

I’m not doing it know because I have no idea how to begin.

This is the code I’m using for fiscal years ending on 31/12:

{% ic %}
{% assign bereik_omzet = “70__75” %}
{% stripnewlines %}
{% newline %}

--------
--------
--------

{% newline %}
| Omzet met BTW:
| {% input custom.omzetmetBTW.range as:account_collection range:bereik_omzet %}
| Omzet zonder BTW:
| {% input custom.omzetzonderBTW.range as:account_collection range:bereik_omzet %}
{% endstripnewlines %}
{% endic %}

{% assign bereikmetBTW = custom.omzetmetBTW.range %}
{% assign bereikzonderBTW = custom.omzetzonderBTW.range %}

{% assign accountmetBTW = period.accounts | range:bereikmetBTW %}
{% assign accountmetBTW_afgerond = -accountmetBTW | ceil:-1 %}
{% assign accountmetBTW_1y = period.minus_1y.year_end.accounts | range:bereikmetBTW %}
{% assign accountmetBTW_afgerond_1y = -accountmetBTW_1y | ceil:-1 %}
{% assign accountzonderBTW = period.accounts | range:bereikzonderBTW %}
{% assign accountzonderBTWafgerond = -accountzonderBTW | ceil:-1 %}
{% assign accountzonderBTW_1y = period.minus_1y.year_end.accounts | range:bereikzonderBTW %}
{% assign accountzonderBTWafgerond_1y = -accountzonderBTW_1y | ceil:-1 %}
{% assign totale_omzet = -accountmetBTW-accountzonderBTW %}
{% assign totale_omzet_afgerond = accountmetBTW_afgerond+accountzonderBTWafgerond %}
{% assign totale_omzet_1y = accountmetBTW_1y+accountzonderBTW_1y %}
{% assign totale_omzet_afgerond_1y = -totale_omzet_1y | ceil:-1 %}

{% assign verhoudingsgetal = accountmetBTW_afgerond/totale_omzet_afgerond | ceil:2 %}
{% assign verhoudingsgetal_1y = accountmetBTW_afgerond_1y/totale_omzet_afgerond_1y | ceil:2 %}
{% assign verhoudingsgetal_NA_BTW = 1-verhoudingsgetal | ceil:2 %}
{% assign verhoudingsgetal_NA_BTW_1y = 1-verhoudingsgetal_1y | ceil:2 %}

{% stripnewlines %}
{% newline %}
|----25%----
|----10%----
|----25%----
|----10%----
|----25%----
|--------+
{% newline %}
| Omzet met BTW huidig jaar:
| {{ -accountmetBTW | currency }}
| Omzet zonder BTW huidig jaar:
| {{ -accountzonderBTW | currency }}
| Totale omzet:
| {{ totale_omzet | currency }}
{% newline %}
| Omzet met BTW huidig jaar afgerond:
| {{ accountmetBTW_afgerond | currency }}
| Omzet zonder BTW huidig jaar afgerond:
| {{ accountzonderBTWafgerond | currency }}
| Totale omzet afgerond:
| {{ totale_omzet_afgerond | currency }}
{% newline %}
| Verhoudingsgetal aftrekbare BTW huidig jaar:
| {{ verhoudingsgetal | percentage }}
| Verhoudingsgetal aftrekbare BTW vorig jaar:
| {{ verhoudingsgetal_1y | percentage }}
|
|
{% newline %}
| Verhoudingsgetal NA BTW huidig jaar:
| {{ verhoudingsgetal_NA_BTW | percentage }}
| Verhoudingsgetal NA BTW vorig jaar:
| {{ verhoudingsgetal_NA_BTW_1y |percentage }}
|
|
{% endstripnewlines %}

@Loic_Cogels,

just to be sure… If you have a bookyear running from 1/10/2019 - 30/9/2020, you then want the value of accounts split in:

  • 1/1/2019 - 31/12/2019 (so previous calendar year)
  • 1/1/2020 - 30/9/2020 (so until current period)

Is that correct? Because in above scenario we are looking into period-to-date values from previous book year, together with period-to-date values from current book year.

Also, we are going to need logic related to the company report frequency (quarterly or monthly), to see how many periods we do need to go back

So I just want to be sure we understand the goal you are trying to accomplish here (as it’ll be a bit harder to do).

@sven ,

In this case, I woud need the value of the previous calendar year 01/01/2019 - 31/12/2019 and 01/01/2018 - 31/12/2018 to be able to check the difference between both years.

Hi @Loic_Cogels ,

I think it’s clear for us now what you are trying to build, however due to the high complexity we will need some time to investigate this and provide you with a proper solution.

We will get back to you on this topic in the course of this week.

Kind regards,
Robin

Hi @Loic_Cogels ,

We wanted to update you that we didn’t have the time yet to look more closely into your issue. As mentioned, due to the high complexity it will take a time effort from our end to investigate.

We have taken this up into our planning for the upcoming weeks.

We will keep you updated.

Kind regards,
Robin

Hi @robindeclercq

No problem. I thought it would be a complex item.

Thanks for the update!

Kind regards,
Loïc

Hi @Loic_Cogels,

Here is a possible solution to your problem:

{% assign month_end_year = period.year_end_date | date:"%m" %}
{% assign current_account_number = current_account.number %}
{% capture current_account_number %}{{ current_account.number }}{% endcapture %}


{% if month_end_year == 12 %}
 
  {% assign account_value = current_account.value %}

{% else %}

  {% assign previous_year = period.year_end_date | retract_years:1 | date:'%Y' %}

  {% comment %}Looping over PREVIOUS fiscal year values and adding amounts that belong to the natural calendar year{% endcomment %}
  {% for transaction in period.minus_1y.accounts.[current_account_number].first.transactions %}
    {% assign transaction_year = transaction.date | date:'%Y' %}
    {% if transaction_year == previous_year %}
      {% assign account_value = account_value+transaction.value %}
    {% endif %}
  {% endfor %}
  
  {% comment %}Looping over CURRENT fiscal year values and adding amounts that belong to the natural calendar year{% endcomment %}
  {% for transaction in current_account.transactions %}
    {% assign transaction_year = transaction.date | date:'%Y' %}
    {% if transaction_year == previous_year or transaction_year == blank %}
      {% assign account_value = account_value+transaction.value %}
    {% endif %}
  {% endfor %}
{% endif %}

{% comment %}Deducting opening value for BS accounts{% endcomment %}
{% assign account_value = account_value-current_account.opening_value %}

Account value (Jan-Dec) = {{ account_value | currency }}

What I did in the above code was first checking whether or not the end of the fiscal year is in December, if so, I just take the current account value from December (since Silverfin shows YTD values this would be from Jan till Dec).
If the fiscal year ends in a different month, I check which year I want to get the transactions from (e.g. if the end of the fiscal year is 09/20, then I want to get the transactions of Jan-Dec 2019) and that’s why I retract one year. Then I loop over the current and previous year transactions in the ledger and just accumulate the value of the transaction in the variable account_value if the transaction date is within the natural calendar year I want.
Since you only want values from Jan-Dec, I deducted the opening value.

Let me know if that works for you.

Best,
Borja

Hi,

How can I make it work for a selected range of account?

Kind regards,
Loïc

Hi Loïc,

The code provided by Borja was intended to get the value from a particular account (current_account). And I think specially to be used in an account template as well.

But, to make the same check though multiple accounts, you can make some small adjustments:

{% assign accounts = period.accounts | range:5 %}
{% assign total = 0 %}

{% for current_account in accounts %}
  
  ... original code here ...

  {% assign total = total+account_value %}
  
{% endfor %}

total {{ total }}

Basically, wrap it in a loop over the accounts you need, and keep track of the total.

One thing that I would like to mention is, since the original snippet is looking at the account’s transactions. Do this multiple times, over a big range of account, could slow down your template. So something to consider when implementing something like this.