Hi,
I’m trying to build a table of ratios, utilising for-loops to keep the code tidy and easier to maintain should we want to add in more ratios. I’m calculating the ratios and setting them to an array via {% assign %}, which I’ve surfaced into the first table (below) for clarity whilst developing. The idea is that the array will then loop and return the results in the ratio table as indicated below:
However, I’m having a few issues that I’m hoping you may be able to help with! The full code for the screenshot is on the footer of my post:
-
I’m not able to return the formula in the table. I’ve tested returning an assigned formula outside of an array, which works fine, but I appear to have issues returning an array formula within an array: {{ array_key_ratios_current_year_formula[forloop.index0]
-
In addition to returning the formula, I’d like to define and return the type, as some results will be in percentages with decimals, and others as integers. I tried to create an array of types, but it would seem trying to return a type from an assigned variable (or array) doesn’t work. Am I missing something, or is this not possible?
-
As I start to build in more ratios, I’m cautious it will become harder to locate the formulas, titles, keys etc in the array since it is split with a semi-colon and therefore on one line. Is it possible to split by paragraph returns so I can keep each ratio named on a separate line?
-
I’m uncertain how to write equations for prior-year results. Is there any guidance for this? I couldn’t find any posts on the community, but may’ve missed them!
Unrelated to the ratio table, I’m also hoping to link to a text document within this reconciliation. Is this possible, as I understand text documents do not have handles?
Please expand for the code being used for this reconciliation.
{% assign array_key_ratios = "Gross profit margin (%);Net profit margin (%);Return on capital employed (%);Current ratio (x:1);Trade debtor days;Trade creditor days;Stock turnover days" | split:";" %}
{% assign array_key_ratios_key = "gross_profit;net_profit;roce;current_ratio;debtor_days;creditor_days;stock_days" | split:";" %}
_Table of Reference (whilst under-dev only):_
{% stripnewlines %}
{% newline %}
|**Margin**
|**Formula**
|**Type**
|**Result**
{% newline %}
|-15%-|-15%-|-15%-|-15%-
{% newline %}
|Gross profit margin
|(#4__5/#4)
|percentage:1
|{{ (#4__5/#4) | percentage:1 }}
{% newline %}
|Net profit margin
|(#4__9/#4)
|percentage:1
|{{ (#4__9/#4) | percentage:1 }}
{% newline %}
|Return on capital employed
|*TBC*
|integer
|{{ }}
{% newline %}
|Current ratio (x:1)
|*TBC*
|integer
|{{ }}
{% newline %}
|Trade debtor days
|*TBC*
|integer
|{{ }}
{% newline %}
|Trade creditor days
|*TBC*
|integer
|{{ }}
{% newline %}
|Stock turnover days
|*TBC*
|integer
|{{ }}
{% endstripnewlines %}
<br>
{% assign array_key_ratios_current_year_formula = (#4__5/#4);(#4__9/#4);(1+1);(1+1);(1+1);(1+1);(1+1) | split:";" %}
{% assign array_key_ratios_current_year_type = "percentage:1";"percentage:1";"integer";"integer";"integer";"integer";"integer" | split:";" %}
{% stripnewlines %}
{% newline %}{% newline %}
|**{{ title }}**
|**Current Year**
|**Prior Year**
|**Prior Year 2**
|**Comments**
{% newline %}
|-----30%----|-----12%------|----12%-----|------12%-------|---34%---+#
{% for key in array_key_ratios_key %}
{% newline %}
|{{ array_key_ratios[forloop.index0] }}
|{{ array_key_ratios_current_year_formula[forloop.index0] | array_key_ratios_current_year_type[forloop.index0] }}
|{{ custom.[key].prior_year_1 }} *calc*
|{{ custom.[key].prior_year_2 }} *calc*
|{% input custom.[key].comments as:text size:mini placeholder:"" %}
{% endfor %}
{% endstripnewlines %}
Many thanks
Joe
Hello @jhanley,
We understand what you are trying to do, however in the way you structured your code and work with arrays it will not be possible. The main problem is that everything in the array is converted to a string. So when you try to print the formulas in the table it returns the string value. I have tried some workarounds to make sure it is recognized as integer values, but unfortunately I didn’t come up with a solution. However, we have re-worked your code to present you with an alternative. We believe this alternative can also help you with your third point (to keep the code readable). So maybe you can have a look at how we approached it, and based on that feedback you can come up with other ideas to keep the code maintainable.
First thing we did was to use different local variables instead of an array to assign the formulas. We have named these local variables always like this: array_key_ratios_key_formula, e.g.: {% assign gross_profit_formula = (#4__5/#4) %}.
In the table, where we loop through the array_key_ratios_key we call this local var: {% capture key_formula %}{{ key }}_formula{% endcapture %}, and then we print it in the second column {{ [key_formula] }}.
For the type filters, there will be no other solution than to hard code these as well. These will not work in an array. As the example uses two filters, we identify first which key_ratio line it is via a case-statement and then print the correct filter with it.
For prior year results, we also made additional variables to keep them maintainable e.g. {% assign gross_profit_formula_min1y = (#4__5/#4):1y %}.
Please find the whole re-worked code below:
Summary
{% assign array_key_ratios = “Gross profit margin (%);Net profit margin (%);Return on capital employed (%);Current ratio (x:1);Trade debtor days;Trade creditor days;Stock turnover days” | split:“;” %}
{% assign array_key_ratios_key = “gross_profit;net_profit;roce;current_ratio;debtor_days;creditor_days;stock_days” | split:“;” %}
{% assign gross_profit_formula = (#4__5/#4) %}
{% assign gross_profit_formula_min1y = (#4__5/#4):1y %}
{% assign gross_profit_formula_min2y = (#4__5/#4):2y %}
{% assign net_profit_formula = (#4__9/#4) %}
{% assign net_profit_formula_min1y = (#4__9/#4):1y %}
{% assign net_profit_formula_min2y = (#4__9/#4):2y %}
{% assign roce_formula = 1+1 %}
Table of Reference (whilst under-dev only):
{% stripnewlines %}
{% newline %}
|Margin
|Formula
|Type
|Result
{% newline %}
|-15%-|-15%-|-15%-|-15%-
{% newline %}
|Gross profit margin
|(#4__5/#4)
|percentage:1
|{{ (#4__5/#4) | percentage:1 }}
{% newline %}
|Net profit margin
|(#4__9/#4)
|percentage:1
|{{ (#4__9/#4) | percentage:1 }}
{% newline %}
|Return on capital employed
|TBC
|integer
|{{ }}
{% newline %}
|Current ratio (x:1)
|TBC
|integer
|{{ }}
{% newline %}
|Trade debtor days
|TBC
|integer
|{{ }}
{% newline %}
|Trade creditor days
|TBC
|integer
|{{ }}
{% newline %}
|Stock turnover days
|TBC
|integer
|{{ }}
{% endstripnewlines %}
{% stripnewlines %}
{% newline %}{% newline %}
|{{ title }}
|Current Year
|Prior Year
|Prior Year 2
|Comments
{% newline %}
|-----30%----|-----12%------|----12%-----|------12%-------|—34%—+#
{% for key in array_key_ratios_key %}
{% capture key_formula %}{{ key }}_formula{% endcapture %}
{% capture key_formula_min1y %}{{ key }}_formula_min1y{% endcapture %}
{% capture key_formula_min2y %}{{ key }}_formula_min2y{% endcapture %}
{% newline %}
|{{ array_key_ratios[forloop.index0] }}
{% case key %}
{% when “gross_profit” or “net_profit” %}
|{{ [key_formula] | percentage:1 }}
|{{ [key_formula_min1y] | percentage:1 }}
|{{ [key_formula_min2y] | percentage:1 }}
|{% input custom.[key].comments as:text size:mini placeholder:“” %}
{% else %}
|{{ [key_formula] | integer }}
|{{ [key_formula_min1y] | integer }}
|{{ [key_formula_min2y] | integer }}
|{% input custom.[key].comments as:text size:mini placeholder:“” %}
{% endcase %}
{% endfor %}
{% endstripnewlines %}
Regarding your final question on the link between the text document and the reconciliation, indeed text documents do not have handles. So it will not be possible to get information from a text document into a reconciliation unless you link the variables to e.g. company or people drop. But we wouldn’t recommend this approach (depending on the content and use of the text document of course).
Hope this helps!
Please reach out if you have any more questions!
Kind regards,
Robin
