Example: How to create a simple financial report in Excel with the Konnect Framework

This example shows how you can build a simple financial report in Excel using data from the Konnect Framework.

The report is based on two key elements:

A structured dataset (from Konsolidator tables)
Standard Excel formulas (SUMIFS and VLOOKUP)

By combining these, you can create a flexible report, such as a Profit & Loss statement.

We will first select the "ConsolidatedAccounts" table for this example and then click "Load."

Important to note

Please be aware that with our new API we are following the Industry-standard fair use policies including limitation per server call via single mandatory filters.
This means that you must need to filter on 4 mandatory filters; Year, Month, Source, and Structure.

You will now retrieve your data to a table in a new sheet of your current Excel workbook.

Note: Once the connection has been established and data has been retrieved in any workbook you can refresh the data using the "Refresh" function in excel. To refresh all data in your workbook, go to the "Data"-tab in excel and click "Refresh all."

Having retrieved the data, we can now start using that data in our reporting.

For example, go to an empty sheet in the same workbook (the workbook containing your data).

In this sheet, enter the following information:

Cell	Information
C1	The company short name of the company you want to be reflected in your report (this should be from your company structure in Konsolidator)
C2	The company role that should be reflected in the report (Group, Parent, or Contribution)
C3	The source you want to be reflected in the report (Actual, Budget, etc.)
C4	The year you want to be shown in the report (in format YYYY and must be a year where consolidation has been completed in Konsolidator for data to be available)
C5	The month you want to be shown in the report (in format M and must be a month that has been consolidated in Konsolidator for data to be available)
A8	A group account number that you know contains data

Now using the "SUMIFS" formula in excel, we will be able to retrieve the consolidated figure as specified. In this example, the company's consolidated amount at the group level with the short name "DK02" in December 2020 on account "11999".

To do so, enter this formula in cell C8:

=SUMIFS(
   ConsolidatedAccounts[Amount];
   ConsolidatedAccounts[CompanyShortName];C$1;
   ConsolidatedAccounts[CompanyRole];C$2;
   ConsolidatedAccounts[Source];C$3;
   ConsolidatedAccounts[Year];C$4;
   ConsolidatedAccounts[Month];C$5;
   ConsolidatedAccounts[AccountCode];$A8
 )

To look up the account name, add the following formula to cell B8:

=VLOOKUP(
   CONCAT($A8);
   ConsolidatedAccounts[[AccountCode]:[AccountName]];
   2;
   FALSE
 )

This "lookup" can, of course, also be done from the "GroupAccounts" table, but for the simplicity of this example, we have only added the "ConsolidatedAccounts" table to the Excel workbook.

The CONCAT() excel function is used on the typed account number in cell A7, as part of the above VLOOKUP(), to ensure that it is handled as text by Excel. This because the account code in the Konnect tables are defined as text and Excel by default handles typed in numbers in cells as numbers and formats them as such.

Your sheet in your Excel workbook should now look something like this:

We can now copy down the entire row 8 and start creating a simple reporting layout by just changing the account number. By copying out the column C, we can also add more months or show different companies or sources side by side into something like this:

Here is the link to the Excel file from the example above:

example.xlsx - INDSÆT LINK