Consolidated FS by Currency

Im trying to Make a simple consolidation FS
What i did was

  1. Modified the and and profit and loss report
  2. Change Period list to Companies Instead of Dates
  3. Added Currency Filter

it looks like this.
TEST2 and TEST 3 are companies

Im currently stuck with these problems:

  1. That amounts are showing on all companies
  2. Accounts are not merging . tried to remove “- TST3” from account name when querying but no accounts are showing.

already tried this:

Code for FS

Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors

License: GNU General Public License v3. See license.txt

from future import unicode_literals
import frappe
from frappe import _, _dict
from frappe.utils import (flt, getdate, get_first_day, get_last_day,
add_months, add_days, formatdate)

def get_period_list(filters):
period_list = []
companies = frappe.db.sql(“”“select name FROM tabCompany WHERE default_currency = %(currency)s “””.format(), filters, as_dict=1)

for i in companies:
period_list.append(_dict({ “companies”: }))

for opts in period_list:
key = opts[“companies”]
label = opts[“companies”]
company = opts[“companies”]
“key”: key,
“label”: label,
“company”: company,
“from_date”: filters.from_date,
“to_date”: filters.to_date,

return period_list

def get_data(currency, root_type, balance_must_be, period_list, ignore_closing_entries=False):
accounts = get_accounts(currency, root_type)
if not accounts:
return None

accounts, accounts_by_name = filter_accounts(accounts)

gl_entries_by_account = {}
for root in frappe.db.sql(“”“select lft, rgt from tabAccount
where root_type=%s and ifnull(parent_account, ‘’) = ‘’”“”, root_type, as_dict=1):
set_gl_entries_by_account(currency, period_list[0][“from_date”], period_list[0][“to_date”], root.lft, root.rgt, gl_entries_by_account,ignore_closing_entries=ignore_closing_entries)

calculate_values(accounts_by_name, gl_entries_by_account, period_list)
accumulate_values_into_parents(accounts, accounts_by_name, period_list)
out = prepare_data(accounts, balance_must_be, period_list)

if out:
add_total_row(out, balance_must_be, period_list)

return out

def calculate_values(accounts_by_name, gl_entries_by_account, period_list):
for entries in gl_entries_by_account.values():
for entry in entries:
d = accounts_by_name.get(entry.account)
for period in period_list:
d[period.key] = flt(entry.debit) - flt(

def accumulate_values_into_parents(accounts, accounts_by_name, period_list):
“”“accumulate children’s values in parent accounts”“”
for d in reversed(accounts):
if d.parent_account:
for period in period_list:
accounts_by_name[d.parent_account][period.key] = accounts_by_name[d.parent_account].get(period.key, 0.0) + d.get(period.key, 0.0)

def prepare_data(accounts, balance_must_be, period_list):
out = []
year_start_date = period_list[0][“from_date”]
year_end_date = period_list[0][“to_date”]
for d in accounts:
# add to output
has_value = False
row = {
“account_name”: d.account_name,
“parent_account”: d.parent_account,
“indent”: flt(d.indent),
“from_date”: year_start_date,
“to_date”: year_end_date
for period in period_list:
if d.get(period.key):
# change sign based on Debit or Credit, since calculation is done using (debit - credit)
d[period.key] *= (1 if balance_must_be==“Debit” else -1)

  	row[period.key] = flt(d.get(period.key, 0.0), 3)
  	if abs(row[period.key]) >= 0.005:
  		# ignore zero values
  		has_value = True
  if has_value:

return out

def add_total_row(out, balance_must_be, period_list):
total_row = {
“account_name”: “'” + _(“Total ({0})”).format(balance_must_be) + “'”,
“account”: None

for row in out:
if not row.get(“parent_account”):
for period in period_list:
total_row.setdefault(period.key, 0.0)
total_row[period.key] += row.get(period.key, 0.0)

  	row[period.key] = ""


blank row after Total


def get_accounts(currency, root_type):
return frappe.db.sql(“”“select name, parent_account, lft, rgt, root_type, report_type, account_name from tabAccount
where account_currency=%s and root_type=%s order by lft”“”, (currency, root_type), as_dict=True)

def filter_accounts(accounts, depth=10):
parent_children_map = {}
accounts_by_name = {}
for d in accounts:
accounts_by_name[] = d
parent_children_map.setdefault(d.parent_account or None, []).append(d)

filtered_accounts = []

def add_to_list(parent, level):
if level < depth:
children = parent_children_map.get(parent) or []
if parent == None:

  	for child in children:
  		child.indent = level
  		add_to_list(, level + 1)

add_to_list(None, 0)

return filtered_accounts, accounts_by_name

def sort_root_accounts(roots):
“”“Sort root types as Asset, Liability, Equity, Income, Expense”“”

def compare_roots(a, b):
if a.report_type != b.report_type and a.report_type == “Balance Sheet”:
return -1
if a.root_type != b.root_type and a.root_type == “Asset”:
return -1
if a.root_type == “Liability” and b.root_type == “Equity”:
return -1
if a.root_type == “Income” and b.root_type == “Expense”:
return -1
return 1


def set_gl_entries_by_account(currency, from_date, to_date, root_lft, root_rgt, gl_entries_by_account,
“”“Returns a dict like { “account”: [gl entries], … }”“”
additional_conditions = []

if ignore_closing_entries:
additional_conditions.append(“and ifnull(voucher_type, ‘’)!=‘Period Closing Voucher’”)

if from_date:
additional_conditions.append(“and posting_date >= %(from_date)s”)

gl_entries = frappe.db.sql(“”“select posting_date, account, debit, credit, is_opening from tabGL Entry
WHERE account_currency = %(currency)s
and posting_date <= %(to_date)s
and account in (select name from tabAccount
where lft >= %(lft)s and rgt <= %(rgt)s)
order by account, posting_date”“”.format(additional_conditions=“\n”.join(additional_conditions)),
“currency”: currency,
“from_date”: from_date,
“to_date”: to_date,
“lft”: root_lft,
“rgt”: root_rgt

for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)

return gl_entries_by_account

def get_columns(period_list):
columns = [{
“fieldname”: “account”,
“label”: _(“Account”),
“fieldtype”: “Link”,
“options”: “Account”,
“width”: 300
for period in period_list:
“fieldname”: period.key,
“label”: period.label,
“fieldtype”: “Currency”,
“width”: 150

return columns


Send a pull request!

@Fred1 @Tropicalrambler also interested and we can collaborate on pull request.


finally got some time to work on this again
managed to finish the Consolidated FS but for v5
will try to convert the code to v7 compatible.

1 Like

HI there,
Any update on this?
I am very interested for this feature

Is there any update on those reports?its very interesting feature.