Adjustment entries template

Dear team,

First of all my best wishes.

We tried to create a separate sheet (via “reconciliation template”) which will basically take all the information from your Adjustment report and put that in a different layout (with additional information). The purpose is to add this new sheet in our export package (i.e. we need something different than your standard report). So we were able to build it but we are still struggling with some items.

that’s how it currently looks like

our issues :

  1. we were not able to insert a blank row or a double line after adjustment #1 and #2 … in order to clearly make a distinction between each adjustment
  2. column 2 : instead of just taking the tags we created, it also includes " {“name”=> how can we delete that ?
  3. last column : we would like to include here the amount in credit or debit, only if it relates to a P&L account
  4. when we add this sheet to our export package, we get a blank page

That’s a lot of question but we’d grateful if you can assist us with

Here are the liquid codes

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
| Adjustment nb
| PwC / Client / Audit
| Description
| Account xxx
| Account Client
| Account Name
| Debit
| credit
| P&L impact
{% newline %}
|:----25%----:
|:----25%----:
|:----25%----:
|:----25%----:
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{%assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
{%assign lineindex = 0%}{% fori transaction in adjustment.transactions %}
{%assign lineindex = lineindex+1%}
{% assign adj_number = adjustment.number %}
{% assign adj_tags = adjustment.tags %}
{% assign tran_account_pwc = transaction.account.number %}
{% assign tran_account_client = transaction.account.original_number %}
{% assign tran_account_name_client = transaction.account.original_name %}
{% assign tran_description = transaction.description %}
{% assign tran_amount_debit = transaction.value %}
{% assign tran_amount_credit = transaction.credit %}
{% assign tran_amount_P&L = transaction.profit&loss %}
|# {{ adj_number }}
|{{ adj_tags }}
|{{ tran_description }}
|{{ tran_account_xxx }}
|{{ tran_account_client }}
|{{ tran_account_name_client }}
| {% if transaction.value > 0 %}{%assign tranval = transaction.value %}{%else%}{%assign tranval = - %}{% endif %} {{ tranval | currency }}
| {% if transaction.value < 0 %}{%assign tranval = (-1)*transaction.value %}{%else%}{%assign tranval = - %}{% endif %} {{ tranval | currency }}
|{% assign tran_amount_P&L = balance_sheet %}
|{%newline%}
{%endfori%}
{%endfori%}
{% endstripnewlines %}

Hi Mehdi,

Welcome to our Community,

I have managed to amend your code to get the expected results:

  1. I have introduced the variable prev_adj_number to compare the adjustment numbers between the rows and add a blank space when they are different. Like this:
    {% if adj_number != blank %}
      {% assign prev_adj_number = adj_number %}
    {% else %}
      {% assign prev_adj_number = adjustment.number %}
    {% endif %}

    {% if adj_number != prev_adj_number %}
      |
      {% newline %}
    {% endif %}
  1. Since adjustment.tags is a collection containing all the tags, you need to loop over it to print the name. Like this:
{% assign adj_tags = adjustment.tags %}

    {% for tag in adj_tags %}
      {{ tag.name }}
    {% endfor %}
  1. First you need to define what your PnL range is. Then you loop over it to check whether your account belongs to that range. Like this:
{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:'4__9' %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

    {% for account in pnl_accounts %}
      {% if tran_account_pwc == account.mapped_number %}
        {{ tranval | currenct }}
        {% break %}
      {% endif %}
    {% endfor %}
  1. You are probably missing the virtual account number on your reconciliation. Please make sure you have a number here:

image

Here is the full script of the code:

{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:'4__9' %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
| Adjustment nb
| PwC / Client / Audit
| Description
| Account xxx
| Account Client
| Account Name
| Debit
| credit
| P&L impact
{% newline %}
|:----25%----:
|:----25%----:
|:----25%----:
|:----25%----:
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{% assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
  {% assign lineindex = 0 %}
  {% fori transaction in adjustment.transactions %}
    {% assign lineindex = lineindex+1 %}
    {% if adj_number != blank %}
      {% assign prev_adj_number = adj_number %}
    {% else %}
      {% assign prev_adj_number = adjustment.number %}
    {% endif %}
    {% assign adj_number = adjustment.number %}
    {% assign adj_tags = adjustment.tags %}
    {% assign tran_account_pwc = transaction.account.mapped_number %}
    {% assign tran_account_client = transaction.account.original_number %}
    {% assign tran_account_name_client = transaction.account.original_name %}
    {% assign tran_description = transaction.description %}
    {% assign tran_amount_debit = transaction.value %}
    {% assign tran_amount_credit = transaction.credit %}
    {% if adj_number != prev_adj_number %}
      |
      {% newline %}
    {% endif %}
    |# {{ adj_number }}
    |
    {% for tag in adj_tags %}
      {{ tag.name }}
    {% endfor %}
    |{{ tran_description }}
    |{{ tran_account_xxx }}
    |{{ tran_account_client }}
    |{{ tran_account_name_client }}
    | {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {{ tranval | currency }}
    | {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {{ tranval | currency }}
    |
    {% for account in pnl_accounts %}
      {% if tran_account_pwc == account.mapped_number %}
        {{ tranval | currenct }}
        {% break %}
      {% endif %}
    {% endfor %}
    {% newline %}
  {% endfori %}
{% endfori %}
{% endstripnewlines %}

Let us know if you have any questions.

Best,
Borja

Hi Borja,

We currently have a nice output from this template (see screenshot)

However, we are still struggling to include a total in the “total” line and if possible subtotals per adjustment as we do not know how to include the registers (As I think that is the solution).

Please find below our latest Liquid coding:

{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:'6__9' %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
|]^_ Adj nb_^[
|]^_ Type_^[
|]^_ Description_^[
|]^_ Acc PwC_^[
|]^_ Acc Client_^[
|]^_ Acc Name_^[
|]^_ Debit_^[
|]^_ credit_^[
|]^_ P&L impact_^[
{% newline %}
|:----5%----:
|:----5%----
|:----25%----
|:----10%----
|:----10%----
|:----25%----
|:----10%----
|:----10%----
|:----10%----
|:----5%----|
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{% assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
  {% assign lineindex = 0 %}
  {% fori transaction in adjustment.transactions %}
    {% assign lineindex = lineindex+1 %}
    {% if adj_number != |]^_blank_^[ %}
      {% assign prev_adj_number = adj_number %}
    {% else %}
      {% assign prev_adj_number = adjustment.number %}
    {% endif %}
     {% assign adj_number = adjustment.number %}
     {% assign adj_tags = adjustment.tags %}
     {% assign tran_account_pwc = transaction.account.mapped_number %}
     {% assign tran_account_client = transaction.account.original_number %}
     {% assign tran_account_name_client = transaction.account.original_name %}
     {% assign tran_description = transaction.description %}
      {% assign tran_amount_debit = transaction.value %}
     {% assign tran_amount_credit = transaction.value %}
     {% if adj_number != prev_adj_number %}
        |]^__^[
        {% newline %}
     {% endif %}
    |] # {{ adj_number }}
    |] {% for tag in adj_tags %}
        {{ tag.name }}
      {% endfor %}
    |]{{ tran_description }}
    |]{{ tran_account_pwc }}
    |]{{ tran_account_client }}
    |]{{ tran_account_name_client }}
    |] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {{ tranval | currency }}  
    |] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {{ tranval | currency }}
    |] {% for account in pnl_accounts %}
       {% if tran_account_pwc == account.mapped_number %}
         {{ transaction.value | currency }}
         {% break %}
       {% endif %}
     {% endfor %} [
     {% newline %}
   {% endfori %}
 {% endfori %}

|]^^__**Total**__^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^__**{{$1 | currency }}**__^^
|]^^__**{{$2 | currency }}**__^^
|]^^__**{{$1+$2 | currency }}**__^^[

{% endstripnewlines %}

Would it be possible for you to have a look at this?

Kind regards,

Ruben

Hi @RubenAnd ,

We do not know how to include the registers (as I think that is the solution)

Indeed, to fix this total and subtotal rows, the use of the registers are in fact the solution. There is a nice example on the use of registers here:

and some more information on registers can be found here:

So in your specific case, there are a few ways to get the result you want. I created the subtotals using the registers by adding this in your code:

|] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {%=$1+ tranval | currency %}
|] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {%=$2+ tranval | currency %}

$1 and $2 will now contain the sum of every row. I have chosen to use these registers for the subtotals as such

{% if adj_number != prev_adj_number %}
|]^Subtotal[1]^^^^
|]^^^^
|]^^^^
|]^^^^
|]^^____^^
|]^^{{$1 | currency }}^^
|]^^{{$2 | currency }}^^
|]^^^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% newline %}
{% endif %}

So now, after each adjustment the subtotal of the specific adjustment is shown based on the data stored in the registers. Note that we also need to reset the registers to zero every time a new adjustment starts.

For the total, I added

{% assign tranval_debit_total = tranval_debit_total+tranval %}
{% assign tranval_credit_total = tranval_credit_total+tranval %}

We could have also worked with registers, but that would perhaps make it even more complicated. These variables tranval_debit_total and tranval_credit_total just take a lump sum of every row specifically for the credit and debit column.

The entire code can be found here:

{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:'6__9' %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
|]^_ Adj nb_^[
|]^_ Type_^[
|]^_ Description_^[
|]^_ Acc PwC_^[
|]^_ Acc Client_^[
|]^_ Acc Name_^[
|]^_ Debit_^[
|]^_ credit_^[
|]^_ P&L impact_^[
{% newline %}
|:----5%----:
|:----5%----
|:----25%----
|:----10%----
|:----10%----
|:----25%----
|:----10%----
|:----10%----
|:----10%----
|:----5%----|
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{% assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
  {% assign lineindex = 0 %}
  {% fori transaction in adjustment.transactions %}
    {% assign lineindex = lineindex+1 %}
    {% if adj_number != |]^_blank_^[ %}
      {% assign prev_adj_number = adj_number %}
    {% else %}
      {% assign prev_adj_number = adjustment.number %}
    {% endif %}
     {% assign adj_number = adjustment.number %}
     {% assign adj_tags = adjustment.tags %}
     {% assign tran_account_pwc = transaction.account.mapped_number %}
     {% assign tran_account_client = transaction.account.original_number %}
     {% assign tran_account_name_client = transaction.account.original_name %}
     {% assign tran_description = transaction.description %}
      {% assign tran_amount_debit = transaction.value %}
     {% assign tran_amount_credit = transaction.value %}
     {% if adj_number != prev_adj_number %}
|]^_Subtotal_^[
|]^^^^
|]^^^^
|]^^^^
|]^^^^
|]^^____^^
|]^^{{$1 | currency }}^^
|]^^{{$2 | currency }}^^
|]^^^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% newline %}
    {% endif %}
    |] # {{ adj_number }}
    |] {% for tag in adj_tags %}
        {{ tag.name }}
      {% endfor %}
    |]{{ tran_description }}
    |]{{ tran_account_pwc }}
    |]{{ tran_account_client }}
    |]{{ tran_account_name_client }}
    |] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {%=$1+ tranval | currency %}{% assign tranval_debit_total = tranval_debit_total+tranval %}
    |] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {%=$2+ tranval | currency %}{% assign tranval_credit_total = tranval_credit_total+tranval %}
    |] {% for account in pnl_accounts %}
       {% if tran_account_pwc == account.mapped_number %}
         {{ transaction.value | currency }}
         {% break %}
       {% endif %}
     {% endfor %} [
     {% newline %}
   {% endfori %}
 {% endfori %}

|]^^__**Total**__^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^{{tranval_debit_total | currency }}^^
|]^^{{tranval_credit_total | currency }}^^
|]^^{{tranval_debit_total+tranval_credit_total | currency }}^^[

{% endstripnewlines %}

Notice how I also changed to fori in a regular for loop, as I think the use of fori is not necessary here.

Hope this helps!

Happy to hear if you have any other questions.

Kind regards,
Robin



  1. | ↩︎

Hi Robin,

Thank you, this was really helpful. The only item with which I am still struggling is the following:

image

Due to this “if” statement the subtotal for the last adjustment is not included. Is there a way to change the formula and include the subtotal of the last adjustment?

This is the most recent version of the liquid coding I have:

{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:‘6__9’ %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
|]^_ Adj nb_^[
|]^_ Type_^[
|]^_ Description_^[
|]^_ Acc PwC_^[
|]^_ Acc Client_^[
|]^_ Acc Name_^[
|]^_ Debit_^[
|]^_ credit_^[
|]^_ P&L impact_^[
{% newline %}
|:----5%----:
|:----5%----
|:----25%----
|:----10%----
|:----10%----
|:----25%----
|:----10%----
|:----10%----
|:----10%----
|:----5%----|
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{% assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
{% assign lineindex = 0 %}
{% fori transaction in adjustment.transactions %}
{% assign lineindex = lineindex+1 %}
{% if adj_number != |]^blank^[ %}
{% assign prev_adj_number = adj_number %}
{% else %}
{% assign prev_adj_number = adjustment.number %}
{% endif %}
{% assign adj_number = adjustment.number %}
{% assign adj_tags = adjustment.tags %}
{% assign tran_account_pwc = transaction.account.mapped_number %}
{% assign tran_account_client = transaction.account.original_number %}
{% assign tran_account_name_client = transaction.account.original_name %}
{% assign tran_description = transaction.description %}
{% assign tran_amount_debit = transaction.value %}
{% assign tran_amount_credit = transaction.value %}
{% if adj_number != prev_adj_number %}

|]^Subtotal^[
|]^^[
|]^
^[
|]^^[
|]^
^[
|]^__^[
|]^^{{$1 | currency }}^^[
|]^^{{$2 | currency }}^^[
|]^^{{$3 | currency }}^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% assign $3 = 0 %}
{% newline %}
{% endif %}
|] # {{ adj_number }}
|] {% for tag in adj_tags %}
{{ tag.name }}
{% endfor %}
|]{{ tran_description }}
|]{{ tran_account_pwc }}
|]{{ tran_account_client }}
|]{{ tran_account_name_client }}
|] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {%=$1+ tranval | currency %}{% assign tranval_debit_total = tranval_debit_total+tranval %}
|] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {%=$2+ tranval | currency %}{% assign tranval_credit_total = tranval_credit_total+tranval %}
|] {% for account in pnl_accounts %}
{% if tran_account_pwc == account.mapped_number %}{% assign tranval = transaction.value %}{%=$3+ tranval | currency %}{% assign tranval_total = tranval_total+tranval %}
{%endif%}
{% endfor %}[
{% newline %}
{% endfori %}
{% endfori %}

|]^^Total^^[
|]^^^^[
|]^^
^^[
|]^^^^[
|]^^
^^[
|]^^____^^[
|]^^{{tranval_debit_total | currency }}^^[
|]^^{{tranval_credit_total | currency }}^^[
|]^^{{tranval_total | currency }}^^[

{% endstripnewlines %}

Could you please take a look?

Thanks in advance,

Ruben

Hi Ruben,

You are right. The subtotal section is included inside both fori-loops, but before we show each of the transactions, not after. That’s the reason why in the last section it is not included (we show the subtotal of the previous section and then each of the row of the current one).

|]^Subtotal^[
|]^^[
|]^^[
|]^^[
|]^^[
|]^__^[
|]^^{{$1 | currency }}^^[
|]^^{{$2 | currency }}^^[
|]^^{{$3 | currency }}^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% assign $3 = 0 %}
{% newline %}

So, I tried moving this entire ‘Subtotal’ section outside and right after the inner fori-loop ends ( {% fori transaction in adjustment.transactions %} ) and only inside the outer fori-loop ({% fori adjustment in adjs %}). That way, we include one subtotal per adjustment, after all the transactions of that adjustments were printed.

This is how the entire code looks now:

{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:‘6__9’ %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
|]^_ Adj nb_^[
|]^_ Type_^[
|]^_ Description_^[
|]^_ Acc PwC_^[
|]^_ Acc Client_^[
|]^_ Acc Name_^[
|]^_ Debit_^[
|]^_ credit_^[
|]^_ P&L impact_^[
{% newline %}
|:----5%----:
|:----5%----
|:----25%----
|:----10%----
|:----10%----
|:----25%----
|:----10%----
|:----10%----
|:----10%----
|:----5%----|
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{% assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
{% assign lineindex = 0 %}
{% fori transaction in adjustment.transactions %}
{% assign lineindex = lineindex+1 %}
{% if adj_number != |]^blank^[ %}
{% assign prev_adj_number = adj_number %}
{% else %}
{% assign prev_adj_number = adjustment.number %}
{% endif %}
{% assign adj_number = adjustment.number %}
{% assign adj_tags = adjustment.tags %}
{% assign tran_account_pwc = transaction.account.mapped_number %}
{% assign tran_account_client = transaction.account.original_number %}
{% assign tran_account_name_client = transaction.account.original_name %}
{% assign tran_description = transaction.description %}
{% assign tran_amount_debit = transaction.value %}
{% assign tran_amount_credit = transaction.value %}

|] # {{ adj_number }}
|] {% for tag in adj_tags %}
{{ tag.name }}
{% endfor %}
|]{{ tran_description }}
|]{{ tran_account_pwc }}
|]{{ tran_account_client }}
|]{{ tran_account_name_client }}
|] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {%=$1+ tranval | currency %}{% assign tranval_debit_total = tranval_debit_total+tranval %}
|] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {%=$2+ tranval | currency %}{% assign tranval_credit_total = tranval_credit_total+tranval %}
|] {% for account in pnl_accounts %}
{% if tran_account_pwc == account.mapped_number %}{% assign tranval = transaction.value %}{%=$3+ tranval | currency %}{% assign tranval_total = tranval_total+tranval %}
{%endif%}
{% endfor %}[
{% newline %}
{% endfori %}

|]^^_ Subtotal _^^[
|]^^_ _^^[
|]^^_ _^^[
|]^^_ _^^[
|]^^_ _^^[
|]^^_ _^^[
|]^^_{{$1 | currency }}_^^[
|]^^_{{$2 | currency }}_^^[
|]^^_{{$3 | currency }}_^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% assign $3 = 0 %}
{% newline %}

{% endfori %}

|]^^Total^^[
|]^^^^[
|]^^^^[
|]^^^^[
|]^^^^[
|]^^____^^[
|]^^{{tranval_debit_total | currency }}^^[
|]^^{{tranval_credit_total | currency }}^^[
|]^^{{tranval_total | currency }}^^[

{% endstripnewlines %}

Hope this helps, and it’s closer to what you expect to achieve!
Kind regards,
Agustín