Advanced [Exercise 5: Rollforward] (Training slides exercise)

  • Create an account template that details purchases and sales for certain shares (types), together with a begin- and end value
  • When copying details in the same book year, everything can be copied. In different book year, certain logic needs to be applied:
  • the end value current year needs to be copied over to end value previous year;
  • purchases and sales need to be empty again
  • if end value is zero, nothing should be copied over of the detail!

|928px;x192px;

Solution:

{% stripnewlines %}
| Type shares
| Comment
| End value {{ period.minus_1y.year_end_date | date:"%Y" }}
| Purchases
| Sales
| End value {{ period.year_end_date | date:"%Y" }}
{% newline %}
|------
|------
|------:
|------:
|------:
|------:+
{% fori item in custom.items %}
  {% newline %}
  | {% input item.type as:select options:"A|B" %}
  | {% input item.information as:text %}
  | {% =$10+input item.value_ly as:currency %}
  | {% =$11+input item.purchases as:currency %}
  | {% =$12+input item.sales as:currency %}
  | {% assign total_value = item.value_ly-item.purchases+item.sales %}
    {{ total_value | currency }}
  {% rollforward total_value item.value_ly %}
  {% rollforward nil item.purchases %}
  {% rollforward nil item.sales %}
{% endfori %}
{% newline %}
|^ ^
|^ ^
|^ {{ $10 | currency }} ^
|^ {{ $11 | currency }} ^
|^ {{ $12 | currency }} ^
|^ {{ $10+$11+$12 | currency }} ^
{% endstripnewlines %}

Solution using HTML Tables:

<table class="usr-width-100">
  <thead>
    <tr>
      <th class="usr-line-bottom"><b>Type shares</b></th>
      <th class="usr-line-bottom"><b>Comment</b></th>
      <th class="usr-line-bottom usr-align-right"><b>End value {{ period.minus_1y.year_end_date | date:"%Y" }}</b></th>
      <th class="usr-line-bottom usr-align-right"><b>Purchases</b></th>
      <th class="usr-line-bottom usr-align-right"><b>Sales</b></th>
      <th class="usr-line-bottom usr-align-right"><b>End value {{ period.year_end_date | date:"%Y" }}</b></th>
    </tr>
  </thead>
  <tbody>
    {% fori item in custom.items %}
      <tr>
        <td class="">{% input item.type as:select options:"A|B" %}</td>
        <td class="">{% input item.information as:text %}</td>
        <td class="usr-align-right">{% input item.value_ly as:currency  %}{% assign total_value_ly = total_value_ly+item.value_ly %}</td>
        <td class="usr-align-right">{% input item.purchases as:currency %}{% assign total_purchases = total_purchases+item.purchases %}</td>
        <td class="usr-align-right">{% input item.sales as:currency %}{% assign total_sales = total_sales+item.sales %}</td>
        <td class="usr-align-right">{% assign total_value = item.value_ly-item.purchases+item.sales %}{{ total_value | currency }}</td>
      </tr>
      {% rollforward total_value item.value_ly %}
      {% rollforward nil item.purchases %}
      {% rollforward nil item.sales %}
    {% endfori %}
    <tr>
      <td class="usr-line-top usr-line-bottom"></td>
      <td class="usr-line-top usr-line-bottom"></td>
      <td class="usr-line-top usr-line-bottom usr-align-right">{{ total_value_ly | currency }}</td>
      <td class="usr-line-top usr-line-bottom usr-align-right">{{ total_purchases | currency }}</td>
      <td class="usr-line-top usr-line-bottom usr-align-right">{{ total_sales | currency }}</td>
      <td class="usr-line-top usr-line-bottom usr-align-right">{{ total_value_ly+total_purchases+total_sales | currency }}</td>
    </tr>
  </tbody>
</table>