{% import '_query_table.html' as querytable %} {% set holdings_all = 'SELECT account, units(sum(position)) as units, cost_number as cost, first(getprice(currency, cost_currency)) as price, cost(sum(position)) as book_value, value(sum(position)) as market_value, cost_date as acquisition_date WHERE account_sortkey(account) ~ "^[01]" GROUP BY account, cost_date, currency, cost_currency, cost_number, account_sortkey(account) ORDER BY account_sortkey(account), currency, cost_date' %} {% set holdings_by = { 'account': 'SELECT account, units(sum(position)) as units, cost(sum(position)) as book_value, value(sum(position)) as market_value WHERE account_sortkey(account) ~ "^[01]" GROUP BY account, cost_currency, account_sortkey(account), currency ORDER BY account_sortkey(account), currency', 'currency': 'SELECT units(sum(position)) as units, SAFEDIV(number(only(first(cost_currency), cost(sum(position)))), number(only(first(currency), units(sum(position))))) as average_cost, cost(sum(position)) as book_value, value(sum(position)) as market_value WHERE account_sortkey(account) ~ "^[01]" GROUP BY currency, cost_currency ORDER BY currency, cost_currency', 'cost_currency': 'SELECT units(sum(position)) as units, cost(sum(position)) as book_value, value(sum(position)) as market_value WHERE account_sortkey(account) ~ "^[01]" GROUP BY cost_currency ORDER BY cost_currency', } %} {% set units_column = { 'account': 1, 'currency': 0, 'cost_currency': 0, } %}
Query {{ querytable.download_links(query_string) }}
{% set contents, result_types, result_rows = ledger.query_shell.execute_query(query_string) %} {{ querytable.querytable(contents, result_types, result_rows, filter_empty=units_column.get(aggregation_key, 1)) }}