Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

IDL Xlslink - Read Export Data in MS Excel

Table of contents

Introduction

IDL.XLSLINK is a lean Excel add-in for capturing, providing and evaluating data in interaction with the IDL KONSIS FORECAST database. IDL.XLSLINK is integrated into Microsoft® Excel as an add-in and connects to IDL.KONSIS databases via the IDL.XLSLINK main application. This allows direct access to the data stored in the IDL database.

It can be written from Microsoft Excel into the IDL database and thus into its applications, as well as dynamically and cell-accurately transferred from IDL.KONSIS to Microsoft Excel. Representations and formatting can be individually selected in Microsoft® Excel as usual. In this way, it is possible to simply set up standard forms for individual Group notes, dynamically fill customer-specific evaluation and presentation formats or set up user-specific reporting packages. There are therefore virtually no limits to the customer-specific evaluation and presentation options.

Since IDL.XLSLINK works closely with Microsoft® Excel, basic application knowledge of Microsoft® Excel is essential for understanding the IDL.XLSLINK topic.

Functional scope of IDL.XLSLINK:

Illustrative structure of the application:

IDL.XLSLINK Main Application

Excel Register

1) New: Clicking the button creates a new Excel file.

2) Open: This selects an existing Excel file to open via the directory window.

Excel Register > Extras

1) Install Excel AddIn: To be able to use IDL.XLSLINK, the AddIn must be installed once after the first program start.

2) Remove Excel AddIn: Uninstallation is also possible.

3) Convert documents: enables the conversion of individual files, which can be selected in the following dialog. Click OK to start the conversion.

Tables that have already been created with the IDL.Connector must be converted to be IDL.XLSLINK-compatible. During the conversion, the IDL.Connector formulas are replaced by IDL.XLSLINK formulas. In addition, a backup copy of the original file is created and marked with “.001” at the end of the file name, provided that no other file type has been set for this extension in the control panel. Existing results are not transferred. To do this, the tables must be converted and then unpacked in Excel.

4) Convert documents (batch): allows the simultaneous conversion of all folders in one folder. For this purpose, the folder in which the workbooks to be converted are compiled must be selected at this point. The conversion is started by entering OK.

If the conversion is completed successfully, the following message will appear:

IMPORTANT: Do not work in any (!) Excel worksheet during the conversion, as Excel is controlled remotely. Any action in the Excel tables will lead to an error message during conversion.

During conversion, the IDL.CONNECTOR formulas are replaced by the IDL.XLSLINK formulas, i.e. "IDLSetData" becomes "IDLSetValue" and "IDLGetData" becomes "IDLGetValue". The conversion of the Excel files can also cause a different behavior of certain IDL.XLSLINK references compared to those of the IDL.CONNECTOR.

Values with a positive or negative sign are displayed in XLSLINK based on selected balance sheet/P&L indicators:

  • For the balance sheet/P&L indicators '1,' '4,' '5,' '6' and '9,' debit amounts are shown as positive values and credit amounts as negative values.

  • For the financial statement indicators '2,' '3,' '7' and '8,' credit amounts are shown as positive values and debit amounts as negative values.

The following rules apply for determining this balance sheet/P&L indicator:

  1. If you specify a position in conjunction with a report ID in the formula, the balance sheet/P&L indicator applies to the highest of the items superior to it in the report structure (analogous to the display in the report).

  2. If you specify a position without specifying a report ID in the formula, the balance sheet/P&L indicator applies to this position.

  3. If you do not specify a certain position (chart of positions and position number), the default rule in point 5 applies.

  4. If you do not specify a position, but a unique account in the formula, the balance sheet/P&L indicator for this account applies.

  5. Without determining a balance sheet/P&L indicator according to the previous points, the display is the same as for balance sheet/P&L indicator '1': Debit amounts are shown as positive values and credit amounts as negative values.

If no balance sheet/P&L indicator can be determined because neither a unique position nor a unique account exists (for example, account number specification with placeholder “%,” selection according to transaction development), debit amounts will be shown as positive values and credit amounts as negative values. This procedure is used if a chart of positions, a chart of accounts, and an account number, but no unique position, are specified in a read reference. If the account is assigned to several positions, debit values will generally be displayed as positive and credit values as negative.

5) IDL Excel password (file name): During batch processing, converting password-protected workbooks can be very time-consuming. To enable full data protection despite the automated process, the password/passwords (one sheet for each password) can be stored in an empty file. This file is read in via the button “IDL Excel password,” so that the files to be converted can be decrypted automatically.

IDL.KONSIS Register

Login

1) This establishes a connection to the IDL.KONSIS database. If only one database has been entered in the Settings tab, the IDL.KONSIS logon takes place immediately.

2) If several databases have been entered, the desired database must be selected, and the login data must be entered.

3) Display of the active server connection. IDL.XLSLINK can be used as soon as the connection is displayed in the bar.

Disconnect – check connection – state – reset

1) Disconnect: The connection to IDL.KONSIS databases can be disconnected here.

2) Check connection: You can test whether a connection exists now. The note “True” indicates that there is an existing connection.

3) State: gives an overview of the connected databases.

4) Reset: The open requests are reset. The running processes are terminated.

Load settings – reset settings / value cache

1) Load settings: When you click on the button, all master data will be loaded into the cache outside Excel.

2) Reset settings / value cache: The data of the dropdown-boxes to select data from IDL Konsis will be deleted in the value cache and refreshed.

Settings Register

1) Application: Clicking the button opens another "Settings" window in which the application can be controlled. The individual options are explained in the following subchapters.

2) Open the INI file: Opens the initialization file (.ini).

3) Folder: Opens a directory containing, in addition to the INI file, the cache folder, the LOG folder and the XML folder. If logging is activated, the log files will be stored in the LOG folder (see also subsection 2.4.2 number 3 log level settings). These are not relevant for the IDL.XLSLINK user, but helpful for the IDL hotline for troubleshooting purposes.

Settings Overview

General settings

1) Konsis start folder: Specification of the installation path of IDL.KONSIS relative to the Xlslink installation folder. Entry only necessary for client installation, not when starting via the web browser.

2) Path to Konsis Client: Specification of the installation path of the IDL.KONSIS client. Must only be entered if it differs from the IDL.Konsis installation.

3) INI file used: Specification of the currently used INI file. Further information on the path in IDL.KONSIS under Help -> Info. Entry only necessary for client installation, not when starting via the web browser.

4) Path to Java JRE: Specification of the installation path of the Java JRE of the IDL.KONSIS client. Can be left empty. Then the path of the Web-Start_Launcher or the IDL.KONSIS installation is used.

5) Log level settings: Clicking on the button opens a window where additional logging settings can be entered. For a thorough investigation of a problem by the IDL developers, logs may be required. In this case, these files are requested by the hotline staff.

To create the logs, a check mark is set in this window, the type of logging is selected and confirmed with OK. In the second step, any logging protocols that may already exist must be deleted (see also Chapter 2.4 Tab Settings). Subsequently, the failed function is triggered or the workspace that does not function as expected is updated. Small amounts of data, often only one cell, are sufficient to determine the cause. Afterwards, the tick is removed again and the protocol function is deactivated in order not to cause unnecessary performance restrictions. The written protocols are to be forwarded by e-mail to idlsupport@insightsoftware.com.

6) No Konsis splash screen at logon: Suppresses the display of the Konsis client splash screen at logon.

7) Connection mode: Only the option H can be selected: "H" stands for Https, access to application server (local server instance or cloud) required. This entry cannot be changed at web start (display greyed out).

8) Language: Control of the national language shown on the interface. A change is always carried out at the next start of IDL.XLSLINK.

9) Check: External links: the switch checks the reference to external links in the Excel workbook. If the switch is set, when opening an Excel table with cell references to other Excel tables, it is immediately checked whether these links still exist and, if necessary, an error message is issued.

10) XlsLink COM-UI-Ribbon: The switch controls the display of the menu of IDL.XLSLINK in Excel as a separate ribbon menu (dynamic COM object) or in the conventional menu "Add-Ins".

Preferences

In IDL.XLSLINK, the default values are basically copied from IDL.KONSIS from the VOR application. Certain fields do not exist in this application, with the consequence that the data must be maintained in every input template. IDL.XLSLINK supports the user with additional fields in the "Preassignment" window. The entries in the mask are used when the files are opened.

1) Balance option (individual financial statement): There are three usual balance options to choose from: ALT/BUC/NEW (the individual values are explained in subsection 4.2.5 Account balances).

2) Balance option: Four options are available: SUM/KON/KONO/KTK. (The individual values are explained in subsection 4.3.1 Account balances).

3) Currency code: One of the five variants can be selected: LW for country currency, KW for Group currency, PW for parallel currency, TW for transaction currency, TWKZ for transaction currency flag.

4) Mode: the following modes are defined S/M/C (the individual values are explained in sub-section 4.1.4 ‘Mode’ field).

5) System: If a database is used permanently, we recommend that you enter it here.

6) Transformation group: In the case of direct export, the same possible uses are offered that exist in IDL.KONSIS for the IMPORT application.

Databases

1) System: The system can be renamed at will here. This applies only to the name used for processing in the IDL.XLSLINK formula. This freely selectable name is independent of the database name (point 2).

2) Database: Entry of the actual database to which a connection is to be established.

3) Start folder: Entry of a different installation folder if a different IDL.KONSIS installation from the start folder is to be accessed for this database.

4) Used INI file: Entry of a different INI file only for the current connection.

5) Connection mode: Since only the option H can be selected ("H" stands for Https, access to application server, local server instance or cloud), this column is currently not used.

 

Columns 3) - 5) can be shown or hidden by double-clicking in the space next to the database table:

6) Insert: This button can be used to connect a new database to the IDL.XLSLINK. An additional row will be inserted, into which the new database can be entered.

Delete: This can be used to remove a database from the menu.

Cancel: The Settings application closes without saving any changes made.

Exit: Closes the application and saves changes made.

IDL.XLSLINK Register

1) Exit: terminates the application IDL.XLSLINK.

2) Information for IDL.XLSLINK: shows information about installing the application:

IDL.XLSLINK Functions in Excel (Add-In)

Tab IDL.XLSLINK

Structure of the Input Mask in the IDL Formula

1) IDL Formula: this button creates a window in which the formula can be built up.

2) Headline: Shows the currently selected application

3) Input fields: Each input mask consists of several selection fields that are relevant for the current application. The number of selection fields depends on the application selected and varies.

4) Selection tree: The selection tree is used to navigate between the applications. The selection is divided into two blocks: "Read function (From IDL.KONSIS)" and "Export function (To IDL.KONSIS)". Each block contains three areas: master data and control data, individual financial statement and Group financial statement. Individual applications are attached below it.

5) Information row: displays the active cell in the Excel folder.

6) Caching: The information stored in the cache for this application can be deleted.

Input options in the input mask of the IDL Formula

Drop-down box

Many fields have a drop-down box in which the desired field content can be selected from the choices offered:

Manual input

The entry can be made manually:

Excel name cell

If the Excel cells have been given names, the cell name can be entered manually in the field:

Working with cell references

It is possible to store cell references in all fields. The contents of an Excel cell can thus be transferred directly to the field in the input mask. Cell references can be inserted into the relevant field as follows:

a. Manual entry

The desired data is entered directly into the input field (please use the mode f(x) in this case):

b. Via the IDL.XLSLINK function “Excel cells” (via context menu)

Step 1) Click on the input field with the right mouse button and select "Excel cells" (the mode will automatically change into f(x) then):

Step 2) The window is minimized in order to allow a free view of the available file. Move with the cursor to the the desired Excel, click and then confirm by clicking on the “Apply” button:

With the help of the function key <F4>, the information in the cell references (e.g. C4) can be modified in the references with regard to the fixing of columns and rows during copying (e.g. "C4" à "$C$4" à "C$4" à "$C4" à "C4").

Alternatively, the Excel cells button at the bottom of the window can be used. To do so, the cursor is clicked into the input field and the button is selected:

Input Conventions in the IDL Formula

Type of Entry Mode Example
Text constant T KON001, H4, 12.2014
Numerical value # 1, 100, 5100
Excel formula f(x) =SUM(A1:A5)
Cell reference f(x) A4, $B5, A$1
Excel named range f(x) DB, Company, Fact,… (Individual)

Display Information

Displays general information about connection setup, processing, and error messages. When you double-click on the error message, the system jumps to the incorrect cell:

Tab Refresh

Using the Refresh function, the IDL.XLSLINK accesses the data in the connected IDL database and updates the references. The update can be carried out optionally via:

  • Selected range: all cells that contain IDL.XLSLINK references and are in the selected range are updated.
  • Worksheet: all cells of the active Excel worksheet with IDL.XLSLINK references are updated.
  • Workbook: all cells of the entire Excel workbook with IDL.XLSLINK references are updated.
  • Field/Listbox: updates the selection in the Field/Listbox.

Tab Archives

The Archives function ensures that Excel folders with IDL.XLSLINK formulas can be exchanged with users whose PC does not have IDL.XLSLINK installed.

When the unpacked Excel folders are opened, the IDL.XLSLINK formulas are automatically updated. If it is not possible to connect to the correct database, the formulas fail, and the user will not see any values. To prevent this from happening, the formulas must be packed before sending. Packaging can be done optionally for:

  • Selected range: all cells that contain IDL.XLSLINK references and are in the selected range will be packed.
  • Worksheet: all cells in the active Excel worksheet with IDL.XLSLINK references will be packed.
  • Workbook: all cells in the entire Excel workbook with IDL.XLSLINK references will be packed.

    During packaging, the IDL.XLSLINK formulas are converted to inactive If formulas. These formulas are reactivated during unpacking.

Tab Export

With the Export function, data can be read into the connected database.

  • Selected area: All IDL.XLSLINK references in the selected area are searched for valid "writing" references and exported to the IDL database.
  • Worksheet: All IDL.XLSLINK references in the active Excel worksheet are searched for valid "writing" references and exported to the IDL database.
  • Workbook: All IDL.XLSLINK references of the entire Excel workbook are searched for valid "writing" references and exported to the IDL database.
  • Reset: When you click on this field, all export formulas will be reset so that only the application name appears in the cells. Furthermore, error messages in the info display and pending export requests will be deleted.
  • File: You can use the "File" button to create a copy of the active workbook without the IDL Formulas. This may be necessary for passing on the Excel sheet to a user who does not have IDL.XLSLINK installed:

Tab Tools

Pad null characters

Numerical master data that begins with zero values, such as Ges 0001, is displayed as "1" in Excel. Via the function "Pad null characters", where the total number of digits can be specified, the number will be displayed in full length with leading zeros, in the example of “0001” (Formatting as text required) :

When Action completed:

IDL Excel Password

It is possible to centrally store the password for the current worksheet or for the entire workbook. This enables you to release the sheets with sheet protection at the click of a mouse:

Field/Listbox
  • Insert listbox/active cell: It is possible to implement a selection list in an active Excel cell. This selection list provides an overview of all variants of a particular attribute available in the database, e.g. all companies or periods. This procedure streamlines the Excel tables.

    Step 1) In order to be able to use the field list, an Excel cell is marked and the Field/Listbox button is selected:

    Step 2) Select the desired option from the list of currently 211 options and confirm with OK:

    Step 3) A * and a selection button with triangle automatically appear in the active cell. Above this, the desired variant of the selected attribute can be selected in a selection list:

  • Remove: A selection list cannot be deleted using the "Remove" button on the keyboard. Only the star that indicates the existence of a selection list in the cell will be deleted.

    To remove the complete cell content, select the cell and click on the Remove button in Tools > Field/Listbox. The following error message must be confirmed accordingly:

    The following error message asks for confirmation.

Options (cell content)

If the function “IDL Formulas only” is activated, only those cells that contain IDL Formulas will be updated. You can set whether the update is to be performed automatically or only on manual command:

IDL Clipboard

With the IDL Clipboard, several fields from Excel can be stored simultaneously for the IDL.XLSLINK reference. The required fields are marked and transferred to the IDL Clipboard via Copy cell.

The cell addresses stored in the Clipboard are now ready for selection:

Extras

Under the Extras tab, two further points are stored: external links and conversion. These are explained below:

1) External links: There are three possibilities

  • Check: manual check of workbook for external links
  • Display: Manual display of existing external links.
  • Check when loading automatic check for existing external links when loading the workbook. This check is activated in the Settings tab (see also section General settings):

If a workbook with active external links is opened in Excel, a message from Excel will appear. Irrespective of the procedure selected here, a warning from IDL.XLSLINK is activated:

The result of both the positive confirmation of the display at this point and the execution of the manual check and display appears as a list:

2) Conversion

  • Conversion step 2: The accompanying text of the application "Conversion step 1" implied until release 2017.0 that the conversion was to be completed with step 2. The two steps were combined in the subsequent releases. Up to and including Release 2018.1, this item is retained as a fallback level in the menu.
  • Format: Reset: During conversion, the Excel cells retain their original formatting. The command "Format: Reset" resets the selected cells to "Standard" formatting.
IDL.XLSLINK mainapplication

1) State: indicates whether a connection exists with the main IDL.XLSLINK application or not.

2) Connect: connection to a database is possible from Excel using this button.

3) Login IDL.KONSIS: the login to IDL.KONSIS can be performed directly via this button. The login window is called up automatically during the first data query.

4) Information for IDL.XLSLINK: Information about the "Valid for IDL.KONSIS version". This can be compared with the corresponding information in the main IDL.XLSLINK application. This allows you to check whether the installed add-in is up-to-date, for example.

Read Function

General

Functionality of the Read Function

The target cell in which the IDL.KONSIS data is to be read out is marked in the Excel sheet. Then the function "IDL Formula" is called up.

Depending on the subject matter to be read from IDL.KONSIS in Excel, the corresponding input mask is selected in the selection tree in the area of the read function.

In the input mask, the IDL.XLSLINK formula is created by filling in the individual fields.

The desired data can be selected in "Field definitions" and read from the IDL.KONSIS database.

The selection of the possible variants can be made by clicking on the triangle on the right in the field "Field definitions".

Then mark the desired row and activate it with the button ">>". A selected row that is no longer desired can be removed from the selection by pressing the "<<" button. It is possible to select more than one row in the "S" mode, but after confirmation of the OK button it leads to a hint in the Excel cell "more data than allowed in the cell". (The other values are explained in subchapter Mode field).

If the formula was created correctly (keyword absolute / relative references), it can be copied to other cells:

The settings from the input mask can be found in the formula bar. In the example shown, “STKTO” means master data (ST) and accounts (KTO).

The references read are output in the language that appears first in the list. To get the references in another language, this should be specified in the IDL Formula.

Read reference with multiple selection

If data from more than one variant of the attribute is to be used, e.g. several selected accounts, multiple selection can be selected:

Multiple references in one cell

It is possible to write several Xlslink formulas in one Excel cell. The formulas are entered or copied manually one after the other and linked together using operators (e.g. + or -):

If a formula row is marked:

the user will be taken to the input mask of the formula via OK:

Field Mode

The specification of mode is mandatory and regulates the type of output of the values. There are three different modes to choose from:

  • S: provides the entire value of a formula in a single value, which can also be composed of several values if, for example, a position or a composite value is read with "%". Although it is possible to select more than one row in the "S" mode, the confirmation of the OK button leads to a note in the Excel cell "more data than allowed in the cell".
  • M: displays the quantity of data sets in the current cell that make up the total value of a formula and the individual values in a table.

When the OK button is confirmed, the number of records will appear in the active cell and the table with the desired results either in a newly created workbook or in the cell address specified in the field provided (in the formula editor at the bottom left):

  • C: this mode is technical and is not intended for use by the user. Although it is possible to select this mode. The entry "C" is automatically assigned to cells for which a Field/Listbox is stored and these can be reworked with it.

The subchapter Field/Listbox in this document provides instructions for creating a selection list.

Reading of Master and Control Files

Accounts

You can use this function to read the attributes of accounts (by using the entry field definitions):

1) When you enter fields such as account and position, the fields Chart of accounts /chart of controlling objects or Chart of positions / Schema will be highlighted in green. If the field is left empty, the plans will be selected according to the settings made in the database (application "VOR").

2) Language: If accounts in the IDL.KONSIS database are stored in several languages, the desired language for the output can be selected here.

3) The desired data can be selected in "Field definitions" and read from the IDL.KONSIS database (in detail explained in chapter 5.1.1)

Controlling objects

You can use this function to read the attributes of controlling objects:

Companies

You can use this function to read the attributes of companies:

Mode M can be used to read out lists of companies with certain attributes; example:

All companies with the country code "I" for Italy, with their ID and short word:

Positions

You can use this function to read the attributes of positions in a particular chart of positions.

Account balances

You can use this function to read account balances and postings. Note the various balance options.

  • ALT: The account balances of the individual financial statement are read (KTOSAL).
  • BUC: The postings of the individual financial statement are read (BUCH).
  • NEU: The result of account balances and postings is read.

IC account balances

This function enables you to read the business relationships of the IC companies:

The arrows show the relationships to mandatory fields that should be entered when reading out certain data.

Controlling balances

With this function, it is possible to read out the cost center balances in IDL.KONSIS. As usually it is mandatory to select one of the balance options:

  • ALT: The account balances of the individual financial statement are read (CNTSAL).
  • BUC: The postings of the individual financial statement are read (BUCH).
  • NEU: The result of account balances and postings is read.

Fixed asset transactions

Using the Fixed asset transactions application, IDL.XLSLINK reads the development transactions in IDL.KONSIS in "ANLBEW".

Development transactions

The IDL.XLSLINK reads the development transactions in IDL.KONSIS in the respective applications "xxxBEW" with the applications capital / provision / additional development transactions.

Position balances ex reports

IDL.XLSLINK uses the Position balances ex reports application to read the values in IDL.KONSIS in the POSSAL application. It is mandatory to select one of the balance options:

  • ALT: The account balances of the individual financial statement are read (POSSAL).
  • BUC: The postings of the individual financial statement are read (BUCH).
  • NEU: The result of account balances and postings is read.

To be able to read position balances at the company level with IDL.XLSLINK, it is necessary that the report with the option “Position balances ex reports” has been created in IDL.KONSIS in the application "REP".

Background: Position balances cannot be entered in IDL.KONSIS. They are automatically generated by IDL.KONSIS when the user creates a report with the entry S by pressing the position balance output button.

Shareholding/participations

This function can be used to read the shareholdings maintained in IDL.KONSIS (GESGES). The reading of the shareholding percentages is controlled by the field "Field definitions".

Read function Group financial statement data

Account balances

The read function for account balances on group level has the following special features:

  • Currency: The currency types KW for Group currency and PW for parallel currency are offered for selection for the group.
  • Mode: As in individual closing, you can only work with mode "S" for single value and "M" for value quantity.
  • Balance option: You can choose from four variants typical for the Group:
    • SUM: The account balances of the summarized financial statement are read (KTOSAL).
    • KON: The consolidation postings of the Group financial statement including all subgroups are read (KONBUCH).
    • KONO: The consolidation postings of the Group financial statement are read (KONBUCH). The subgroups are not taken into consideration.
    • KTK: The result from account balances and consolidation postings is read.

Position balances ex reports

With the balance option, the four Group-specific variants are also available for selection in the "Position balances ex reports" export function:

  • SUM: The account balances of the summarized financial statement are read (POSSAL).
  • KON: The consolidation postings of the Group financial statement including all subgroups are read (KONBUCH).
  • KONO: The consolidation postings of the Group financial statement are read (KONBUCH). The subgroups are not taken into consideration.
  • KTK: The result from account balances and consolidation postings is read.

To be able to read position balances at Group level with IDL.XLSLINK, you must have created the report with the setting S or P in the button for position balances in IDL.KONSIS in the "REPK" application.

Background: Position balances cannot be entered in IDL.KONSIS. They are generated automatically by IDL.KONSIS if a report with the corresponding setting has been created in IDL.KONSIS.

Difference between Report Option S and P
P S
Position balances are generated for the entire Group and also for the individual companies. Position balances are only generated for the entire Group.

Export function

The Export function

Step 1) Call up the IDL formula in the cell in which the reference to the export of data is to be created.

Step 2) Depending on the facts, the corresponding input mask is selected in the selection tree in the area of the Export function.

Step 3) In the input mask, the IDL.XLSLINK reference for the export is created by filling individual fields. The fields highlighted in color are mandatory fields.

Step 4) The export formula is displayed and can be copied to other cells:

Step 5) In the "Export" area of the menu bar, select the appropriate submenu item depending on the area to be exported.

If a selected range is to be exported, it must be marked before clicking on the "Selected Range" tab.

Step 6) The export is triggered:

1) Six rows have been found for the selected action.

2) Save: The data is loaded directly into the IDL database.

3) Delete/Save: The data is loaded directly into the IDL database. The existing data in the database is first deleted and then the new data is imported.

4) Export button: starts the export process.

Additional settings for the export can be made in the field "Options".

  • With carry forward: According to the IDL logic, the carry forwards are created as part of the Group and individual financial statement carry forwards and are not imported in any other way. If, however, it is necessary for certain reasons, the export of the carry forward movements can be activated.
  • Select path for export files: The data can either be loaded directly into the IDL database or indirectly via a txt file. If an individual path is to be used, it can be selected via the "Save into textfile" button. By default, the import path of IDL.KONSIS is set here.

Step 7) As soon as the export is completed, the status message for the export will appear; either the processing has gone through without errors or an error message is displayed. The corresponding information is also displayed in the Excel cell.

The settings from the input mask can be found in the formula bar:

The formula name reflects the associated function. Here EA for individual financial statement and KTOSAL for account balances.

Export of master and control files

Accounts

This input mask is used to export accounts to IDL.KONSIS in the "KTO" application.

Positions + accounts allocations

In the IDL.KONSIS logic, each account is assigned to a position. Conversely, each position consists of one or more accounts.

With this function, IDL.KONSIS imports into the POSKTO application which account is to be allocated to which item.

Report line definitions

A report line definition determines how a report is structured and from which positions totals rows are calculated.

The report line definition is necessary for creating reports in IDL.KONSIS and using IDL entry forms.

Export individual financial statements

Account balances

This function imports account balances according to IDL.KONSIS into the "KTOSAL" application.

IC account balances

This function is used to record intercompany business relationships.

In addition to the usual country currency, you can also maintain a transaction currency.

Development transactions

With these applications, IDL.XLSLINK exports the development transactions to IDL.KONSIS to the respective application "xxxBEW".

Shareholding/participations

With this application, IDL.XLSLINK exports the shareholding transactions to IDL.KONSIS to the "GESGES" application.

Vouchers and postings

Before one or more postings can be exported to IDL.KONSIS, a corresponding document header must be set up in the "BEL" application. If no document header has been set up, the message "No posting document exists" will appear in the export notice.

Vouchers can be exported to IDL.KONSIS via the input mask "Vouchers".

With this application, IDL.XLSLINK exports the postings to IDL.KONSIS:

Export Group financial statement

Consolidation vouchers

To be able to load consolidation postings into IDL.KONSIS, the consolidation vouchers must first be available in the database. These can be imported into the system using IDL.XLSLINK.

For the setup, specifications in ten mandatory fields are required:

Consolidation postings

After creating or importing the consolidation documents, the consolidation entries can be imported. The corresponding Voucher no. with its individual components must be entered:

All three relevant fields for the entry of the posting amounts

- Debit/credit code

- Debit amount group currency

- Credit amount group currency

are enabled for entry independently of each other:

The following cell content can be in the S/H Indicator field - other content will result in an error:

- Empty - no content at all in the Excel cell.

- 'D' - indicates debit value => amount from the field for GG Debit will be exported

- 'C' - indicates credit value => amount from the field for CG Credit will be exported

(Note: If you want to use the English variant D/C, you have additionally to enter ENG in the field for ‘Coding Language’, or otherwise work with the German characters S (for D) or H (for C)).

The following cell contents can be in the fields for the debit or credit amount - other contents will result in an error:

- Empty - i.e. no content in the input field or in the assigned Excel cell.

- 0 - zero by direct input, formula or empty cell reference

- a positive value by direct input, formula or cell reference to pos. value

- a negative value by direct input, formula or cell reference to neg. value

If an S (for debit) is entered in the S/H indicator field, the value entered in the debit amount field will be exported. If this value is positive, it will be written as a debit entry; if it is negative, it will be written as a credit amount.

Similarly, if "H" is entered in the field for the S/H indicator, the amount entered in the credit field will be taken into account; if the value is positive, it will be written as a credit entry, if it is negative, it will be written as a debit entry.

If there is no entry in the field for the D/C code, the amount from the field that contains an entry is exported. If amounts are entered in both fields without an S/H entry, no export will take place because no unique assignment is possible.

Thus, only one of the two amount fields can be used as "the" input field, analogous to the single record entry in IDL Konsis.

Excursus – Excel

Each formula begins with an equal sign (=). The work is preferably done with cell references. This offers the advantage that the formula result always adapts when the corresponding cell content is changed (variable values). However, fixed values (constant values) can also be used in a formula.

The result of a formula is shown in the cell in which the formula was entered. The formula itself can be seen in the editing toolbar. The formula can also be edited there if required.

Functions

Excel offers many ready-made formulas, the so-called functions.

Components of each function are function name and parenthesis open/closed. The information contained in the brackets is called arguments. The number of arguments varies from function to function. There are also functions that do not require arguments, e.g. TODAY(), NOW().

When a function is entered into a cell, a quick info with the syntax is displayed. When entering, the arguments required by the function will be displayed.

Multiple arguments are separated by a semicolon. Arguments can be cell references (individual cells, cell ranges), constant values (numbers, text) or other functions (nested functions).

Cell references

Relative cell reference

Basically, all cell references are relative, i.e. when copying, these cell references are adapted "relative" to the target position: When copying in the row (horizontal), the column letter is adapted and when copying in the column (vertical), the row number is adapted.

Example: in cell B7, the column sum is to be calculated. The formula is = TOTAL(B3:B6). The formula is then copied to cell C7. The cell references are adjusted automatically.

Absolute cell reference

If a cell reference in a formula always refers to a specific cell, this cell reference must be specified as the absolute cell reference so that the result is correct after copying the formula.

It remains unchanged during copying and therefore does not adapt. To mark a cell reference as an absolute reference, a dollar sign ($) is entered before the row and column caption. This can be done when entering the cell reference or later via the editing bar.

Example: In this example, cell B1, which contains the VAT rate, must be entered in the formula as the absolute cell reference so that the formula can be copied. The other cell references remain relative because they are to be adapted during copying.

Tip: The $ characters can also be set with a single click of the F4 key on the keyboard.

Mixed cell reference

Cell references can also be entered as mixed references. This means that the entire cell is not taken as a fixed reference, but only the column or row is fixed:

  • for the absolute column, enter the dollar sign before the letters ($A3).
  • for the absolute row, the dollar sign is entered before the number (A$3).

Example: Starting from actual sales, you want to calculate two plan scenarios. For the formula of PLAN B, however, the formula of PLAN A should be used. So, the formula always remains in one row (absolute), but the column adjusts itself (relative).

Names for cells / cell ranges

For better clarity, individual cells or cell ranges can be assigned names. Formulas become clearer and more comprehensible if names are used instead of cell addresses. In addition, the navigation becomes easier, since the named cells/cell ranges can be controlled by their names. Names assigned once are available on all worksheets of the workbook.

There are two ways of naming the cell/cell ranges:

Variant 1:

The cell or area to be named is marked and the cursor is moved to the name field in the upper left corner of the editing bar. The desired name is entered and confirmed with Enter. The name must correspond either to a valid cell reference or to an existing name in the table.

Variant 2:

The name can also be entered via the "Formulas - Name manager – New" tab.

In IDL.XLSLINK, an Excel name field can be selected and inserted in the input window by using the right mouse click:
Published:

IDL Xlslink - Read Export Data in MS Excel

Table of contents

Introduction

IDL.XLSLINK is a lean Excel add-in for capturing, providing and evaluating data in interaction with the IDL KONSIS FORECAST database. IDL.XLSLINK is integrated into Microsoft® Excel as an add-in and connects to IDL.KONSIS databases via the IDL.XLSLINK main application. This allows direct access to the data stored in the IDL database.

It can be written from Microsoft Excel into the IDL database and thus into its applications, as well as dynamically and cell-accurately transferred from IDL.KONSIS to Microsoft Excel. Representations and formatting can be individually selected in Microsoft® Excel as usual. In this way, it is possible to simply set up standard forms for individual Group notes, dynamically fill customer-specific evaluation and presentation formats or set up user-specific reporting packages. There are therefore virtually no limits to the customer-specific evaluation and presentation options.

Since IDL.XLSLINK works closely with Microsoft® Excel, basic application knowledge of Microsoft® Excel is essential for understanding the IDL.XLSLINK topic.

Functional scope of IDL.XLSLINK:

Illustrative structure of the application:

IDL.XLSLINK Main Application

Excel Register

1) New: Clicking the button creates a new Excel file.

2) Open: This selects an existing Excel file to open via the directory window.

Excel Register > Extras

1) Install Excel AddIn: To be able to use IDL.XLSLINK, the AddIn must be installed once after the first program start.

2) Remove Excel AddIn: Uninstallation is also possible.

3) Convert documents: enables the conversion of individual files, which can be selected in the following dialog. Click OK to start the conversion.

Tables that have already been created with the IDL.Connector must be converted to be IDL.XLSLINK-compatible. During the conversion, the IDL.Connector formulas are replaced by IDL.XLSLINK formulas. In addition, a backup copy of the original file is created and marked with “.001” at the end of the file name, provided that no other file type has been set for this extension in the control panel. Existing results are not transferred. To do this, the tables must be converted and then unpacked in Excel.

4) Convert documents (batch): allows the simultaneous conversion of all folders in one folder. For this purpose, the folder in which the workbooks to be converted are compiled must be selected at this point. The conversion is started by entering OK.

If the conversion is completed successfully, the following message will appear:

IMPORTANT: Do not work in any (!) Excel worksheet during the conversion, as Excel is controlled remotely. Any action in the Excel tables will lead to an error message during conversion.

During conversion, the IDL.CONNECTOR formulas are replaced by the IDL.XLSLINK formulas, i.e. "IDLSetData" becomes "IDLSetValue" and "IDLGetData" becomes "IDLGetValue". The conversion of the Excel files can also cause a different behavior of certain IDL.XLSLINK references compared to those of the IDL.CONNECTOR.

Values with a positive or negative sign are displayed in XLSLINK based on selected balance sheet/P&L indicators:

  • For the balance sheet/P&L indicators '1,' '4,' '5,' '6' and '9,' debit amounts are shown as positive values and credit amounts as negative values.

  • For the financial statement indicators '2,' '3,' '7' and '8,' credit amounts are shown as positive values and debit amounts as negative values.

The following rules apply for determining this balance sheet/P&L indicator:

  1. If you specify a position in conjunction with a report ID in the formula, the balance sheet/P&L indicator applies to the highest of the items superior to it in the report structure (analogous to the display in the report).

  2. If you specify a position without specifying a report ID in the formula, the balance sheet/P&L indicator applies to this position.

  3. If you do not specify a certain position (chart of positions and position number), the default rule in point 5 applies.

  4. If you do not specify a position, but a unique account in the formula, the balance sheet/P&L indicator for this account applies.

  5. Without determining a balance sheet/P&L indicator according to the previous points, the display is the same as for balance sheet/P&L indicator '1': Debit amounts are shown as positive values and credit amounts as negative values.

If no balance sheet/P&L indicator can be determined because neither a unique position nor a unique account exists (for example, account number specification with placeholder “%,” selection according to transaction development), debit amounts will be shown as positive values and credit amounts as negative values. This procedure is used if a chart of positions, a chart of accounts, and an account number, but no unique position, are specified in a read reference. If the account is assigned to several positions, debit values will generally be displayed as positive and credit values as negative.

5) IDL Excel password (file name): During batch processing, converting password-protected workbooks can be very time-consuming. To enable full data protection despite the automated process, the password/passwords (one sheet for each password) can be stored in an empty file. This file is read in via the button “IDL Excel password,” so that the files to be converted can be decrypted automatically.

IDL.KONSIS Register

Login

1) This establishes a connection to the IDL.KONSIS database. If only one database has been entered in the Settings tab, the IDL.KONSIS logon takes place immediately.

2) If several databases have been entered, the desired database must be selected, and the login data must be entered.

3) Display of the active server connection. IDL.XLSLINK can be used as soon as the connection is displayed in the bar.

Disconnect – check connection – state – reset

1) Disconnect: The connection to IDL.KONSIS databases can be disconnected here.

2) Check connection: You can test whether a connection exists now. The note “True” indicates that there is an existing connection.

3) State: gives an overview of the connected databases.

4) Reset: The open requests are reset. The running processes are terminated.

Load settings – reset settings / value cache

1) Load settings: When you click on the button, all master data will be loaded into the cache outside Excel.

2) Reset settings / value cache: The data of the dropdown-boxes to select data from IDL Konsis will be deleted in the value cache and refreshed.

Settings Register

1) Application: Clicking the button opens another "Settings" window in which the application can be controlled. The individual options are explained in the following subchapters.

2) Open the INI file: Opens the initialization file (.ini).

3) Folder: Opens a directory containing, in addition to the INI file, the cache folder, the LOG folder and the XML folder. If logging is activated, the log files will be stored in the LOG folder (see also subsection 2.4.2 number 3 log level settings). These are not relevant for the IDL.XLSLINK user, but helpful for the IDL hotline for troubleshooting purposes.

Settings Overview

General settings

1) Konsis start folder: Specification of the installation path of IDL.KONSIS relative to the Xlslink installation folder. Entry only necessary for client installation, not when starting via the web browser.

2) Path to Konsis Client: Specification of the installation path of the IDL.KONSIS client. Must only be entered if it differs from the IDL.Konsis installation.

3) INI file used: Specification of the currently used INI file. Further information on the path in IDL.KONSIS under Help -> Info. Entry only necessary for client installation, not when starting via the web browser.

4) Path to Java JRE: Specification of the installation path of the Java JRE of the IDL.KONSIS client. Can be left empty. Then the path of the Web-Start_Launcher or the IDL.KONSIS installation is used.

5) Log level settings: Clicking on the button opens a window where additional logging settings can be entered. For a thorough investigation of a problem by the IDL developers, logs may be required. In this case, these files are requested by the hotline staff.

To create the logs, a check mark is set in this window, the type of logging is selected and confirmed with OK. In the second step, any logging protocols that may already exist must be deleted (see also Chapter 2.4 Tab Settings). Subsequently, the failed function is triggered or the workspace that does not function as expected is updated. Small amounts of data, often only one cell, are sufficient to determine the cause. Afterwards, the tick is removed again and the protocol function is deactivated in order not to cause unnecessary performance restrictions. The written protocols are to be forwarded by e-mail to idlsupport@insightsoftware.com.

6) No Konsis splash screen at logon: Suppresses the display of the Konsis client splash screen at logon.

7) Connection mode: Only the option H can be selected: "H" stands for Https, access to application server (local server instance or cloud) required. This entry cannot be changed at web start (display greyed out).

8) Language: Control of the national language shown on the interface. A change is always carried out at the next start of IDL.XLSLINK.

9) Check: External links: the switch checks the reference to external links in the Excel workbook. If the switch is set, when opening an Excel table with cell references to other Excel tables, it is immediately checked whether these links still exist and, if necessary, an error message is issued.

10) XlsLink COM-UI-Ribbon: The switch controls the display of the menu of IDL.XLSLINK in Excel as a separate ribbon menu (dynamic COM object) or in the conventional menu "Add-Ins".

Preferences

In IDL.XLSLINK, the default values are basically copied from IDL.KONSIS from the VOR application. Certain fields do not exist in this application, with the consequence that the data must be maintained in every input template. IDL.XLSLINK supports the user with additional fields in the "Preassignment" window. The entries in the mask are used when the files are opened.

1) Balance option (individual financial statement): There are three usual balance options to choose from: ALT/BUC/NEW (the individual values are explained in subsection 4.2.5 Account balances).

2) Balance option: Four options are available: SUM/KON/KONO/KTK. (The individual values are explained in subsection 4.3.1 Account balances).

3) Currency code: One of the five variants can be selected: LW for country currency, KW for Group currency, PW for parallel currency, TW for transaction currency, TWKZ for transaction currency flag.

4) Mode: the following modes are defined S/M/C (the individual values are explained in sub-section 4.1.4 ‘Mode’ field).

5) System: If a database is used permanently, we recommend that you enter it here.

6) Transformation group: In the case of direct export, the same possible uses are offered that exist in IDL.KONSIS for the IMPORT application.

Databases

1) System: The system can be renamed at will here. This applies only to the name used for processing in the IDL.XLSLINK formula. This freely selectable name is independent of the database name (point 2).

2) Database: Entry of the actual database to which a connection is to be established.

3) Start folder: Entry of a different installation folder if a different IDL.KONSIS installation from the start folder is to be accessed for this database.

4) Used INI file: Entry of a different INI file only for the current connection.

5) Connection mode: Since only the option H can be selected ("H" stands for Https, access to application server, local server instance or cloud), this column is currently not used.

 

Columns 3) - 5) can be shown or hidden by double-clicking in the space next to the database table:

6) Insert: This button can be used to connect a new database to the IDL.XLSLINK. An additional row will be inserted, into which the new database can be entered.

Delete: This can be used to remove a database from the menu.

Cancel: The Settings application closes without saving any changes made.

Exit: Closes the application and saves changes made.

IDL.XLSLINK Register

1) Exit: terminates the application IDL.XLSLINK.

2) Information for IDL.XLSLINK: shows information about installing the application:

IDL.XLSLINK Functions in Excel (Add-In)

Tab IDL.XLSLINK

Structure of the Input Mask in the IDL Formula

1) IDL Formula: this button creates a window in which the formula can be built up.

2) Headline: Shows the currently selected application

3) Input fields: Each input mask consists of several selection fields that are relevant for the current application. The number of selection fields depends on the application selected and varies.

4) Selection tree: The selection tree is used to navigate between the applications. The selection is divided into two blocks: "Read function (From IDL.KONSIS)" and "Export function (To IDL.KONSIS)". Each block contains three areas: master data and control data, individual financial statement and Group financial statement. Individual applications are attached below it.

5) Information row: displays the active cell in the Excel folder.

6) Caching: The information stored in the cache for this application can be deleted.

Input options in the input mask of the IDL Formula

Drop-down box

Many fields have a drop-down box in which the desired field content can be selected from the choices offered:

Manual input

The entry can be made manually:

Excel name cell

If the Excel cells have been given names, the cell name can be entered manually in the field:

Working with cell references

It is possible to store cell references in all fields. The contents of an Excel cell can thus be transferred directly to the field in the input mask. Cell references can be inserted into the relevant field as follows:

a. Manual entry

The desired data is entered directly into the input field (please use the mode f(x) in this case):

b. Via the IDL.XLSLINK function “Excel cells” (via context menu)

Step 1) Click on the input field with the right mouse button and select "Excel cells" (the mode will automatically change into f(x) then):

Step 2) The window is minimized in order to allow a free view of the available file. Move with the cursor to the the desired Excel, click and then confirm by clicking on the “Apply” button:

With the help of the function key <F4>, the information in the cell references (e.g. C4) can be modified in the references with regard to the fixing of columns and rows during copying (e.g. "C4" à "$C$4" à "C$4" à "$C4" à "C4").

Alternatively, the Excel cells button at the bottom of the window can be used. To do so, the cursor is clicked into the input field and the button is selected:

Input Conventions in the IDL Formula

Type of Entry Mode Example
Text constant T KON001, H4, 12.2014
Numerical value # 1, 100, 5100
Excel formula f(x) =SUM(A1:A5)
Cell reference f(x) A4, $B5, A$1
Excel named range f(x) DB, Company, Fact,… (Individual)

Display Information

Displays general information about connection setup, processing, and error messages. When you double-click on the error message, the system jumps to the incorrect cell:

Tab Refresh

Using the Refresh function, the IDL.XLSLINK accesses the data in the connected IDL database and updates the references. The update can be carried out optionally via:

  • Selected range: all cells that contain IDL.XLSLINK references and are in the selected range are updated.
  • Worksheet: all cells of the active Excel worksheet with IDL.XLSLINK references are updated.
  • Workbook: all cells of the entire Excel workbook with IDL.XLSLINK references are updated.
  • Field/Listbox: updates the selection in the Field/Listbox.

Tab Archives

The Archives function ensures that Excel folders with IDL.XLSLINK formulas can be exchanged with users whose PC does not have IDL.XLSLINK installed.

When the unpacked Excel folders are opened, the IDL.XLSLINK formulas are automatically updated. If it is not possible to connect to the correct database, the formulas fail, and the user will not see any values. To prevent this from happening, the formulas must be packed before sending. Packaging can be done optionally for:

  • Selected range: all cells that contain IDL.XLSLINK references and are in the selected range will be packed.
  • Worksheet: all cells in the active Excel worksheet with IDL.XLSLINK references will be packed.
  • Workbook: all cells in the entire Excel workbook with IDL.XLSLINK references will be packed.

    During packaging, the IDL.XLSLINK formulas are converted to inactive If formulas. These formulas are reactivated during unpacking.

Tab Export

With the Export function, data can be read into the connected database.

  • Selected area: All IDL.XLSLINK references in the selected area are searched for valid "writing" references and exported to the IDL database.
  • Worksheet: All IDL.XLSLINK references in the active Excel worksheet are searched for valid "writing" references and exported to the IDL database.
  • Workbook: All IDL.XLSLINK references of the entire Excel workbook are searched for valid "writing" references and exported to the IDL database.
  • Reset: When you click on this field, all export formulas will be reset so that only the application name appears in the cells. Furthermore, error messages in the info display and pending export requests will be deleted.
  • File: You can use the "File" button to create a copy of the active workbook without the IDL Formulas. This may be necessary for passing on the Excel sheet to a user who does not have IDL.XLSLINK installed:

Tab Tools

Pad null characters

Numerical master data that begins with zero values, such as Ges 0001, is displayed as "1" in Excel. Via the function "Pad null characters", where the total number of digits can be specified, the number will be displayed in full length with leading zeros, in the example of “0001” (Formatting as text required) :

When Action completed:

IDL Excel Password

It is possible to centrally store the password for the current worksheet or for the entire workbook. This enables you to release the sheets with sheet protection at the click of a mouse:

Field/Listbox
  • Insert listbox/active cell: It is possible to implement a selection list in an active Excel cell. This selection list provides an overview of all variants of a particular attribute available in the database, e.g. all companies or periods. This procedure streamlines the Excel tables.

    Step 1) In order to be able to use the field list, an Excel cell is marked and the Field/Listbox button is selected:

    Step 2) Select the desired option from the list of currently 211 options and confirm with OK:

    Step 3) A * and a selection button with triangle automatically appear in the active cell. Above this, the desired variant of the selected attribute can be selected in a selection list:

  • Remove: A selection list cannot be deleted using the "Remove" button on the keyboard. Only the star that indicates the existence of a selection list in the cell will be deleted.

    To remove the complete cell content, select the cell and click on the Remove button in Tools > Field/Listbox. The following error message must be confirmed accordingly:

    The following error message asks for confirmation.

Options (cell content)

If the function “IDL Formulas only” is activated, only those cells that contain IDL Formulas will be updated. You can set whether the update is to be performed automatically or only on manual command:

IDL Clipboard

With the IDL Clipboard, several fields from Excel can be stored simultaneously for the IDL.XLSLINK reference. The required fields are marked and transferred to the IDL Clipboard via Copy cell.

The cell addresses stored in the Clipboard are now ready for selection:

Extras

Under the Extras tab, two further points are stored: external links and conversion. These are explained below:

1) External links: There are three possibilities

  • Check: manual check of workbook for external links
  • Display: Manual display of existing external links.
  • Check when loading automatic check for existing external links when loading the workbook. This check is activated in the Settings tab (see also section General settings):

If a workbook with active external links is opened in Excel, a message from Excel will appear. Irrespective of the procedure selected here, a warning from IDL.XLSLINK is activated:

The result of both the positive confirmation of the display at this point and the execution of the manual check and display appears as a list:

2) Conversion

  • Conversion step 2: The accompanying text of the application "Conversion step 1" implied until release 2017.0 that the conversion was to be completed with step 2. The two steps were combined in the subsequent releases. Up to and including Release 2018.1, this item is retained as a fallback level in the menu.
  • Format: Reset: During conversion, the Excel cells retain their original formatting. The command "Format: Reset" resets the selected cells to "Standard" formatting.
IDL.XLSLINK mainapplication

1) State: indicates whether a connection exists with the main IDL.XLSLINK application or not.

2) Connect: connection to a database is possible from Excel using this button.

3) Login IDL.KONSIS: the login to IDL.KONSIS can be performed directly via this button. The login window is called up automatically during the first data query.

4) Information for IDL.XLSLINK: Information about the "Valid for IDL.KONSIS version". This can be compared with the corresponding information in the main IDL.XLSLINK application. This allows you to check whether the installed add-in is up-to-date, for example.

Read Function

General

Functionality of the Read Function

The target cell in which the IDL.KONSIS data is to be read out is marked in the Excel sheet. Then the function "IDL Formula" is called up.

Depending on the subject matter to be read from IDL.KONSIS in Excel, the corresponding input mask is selected in the selection tree in the area of the read function.

In the input mask, the IDL.XLSLINK formula is created by filling in the individual fields.

The desired data can be selected in "Field definitions" and read from the IDL.KONSIS database.

The selection of the possible variants can be made by clicking on the triangle on the right in the field "Field definitions".

Then mark the desired row and activate it with the button ">>". A selected row that is no longer desired can be removed from the selection by pressing the "<<" button. It is possible to select more than one row in the "S" mode, but after confirmation of the OK button it leads to a hint in the Excel cell "more data than allowed in the cell". (The other values are explained in subchapter Mode field).

If the formula was created correctly (keyword absolute / relative references), it can be copied to other cells:

The settings from the input mask can be found in the formula bar. In the example shown, “STKTO” means master data (ST) and accounts (KTO).

The references read are output in the language that appears first in the list. To get the references in another language, this should be specified in the IDL Formula.

Read reference with multiple selection

If data from more than one variant of the attribute is to be used, e.g. several selected accounts, multiple selection can be selected:

Multiple references in one cell

It is possible to write several Xlslink formulas in one Excel cell. The formulas are entered or copied manually one after the other and linked together using operators (e.g. + or -):

If a formula row is marked:

the user will be taken to the input mask of the formula via OK:

Field Mode

The specification of mode is mandatory and regulates the type of output of the values. There are three different modes to choose from:

  • S: provides the entire value of a formula in a single value, which can also be composed of several values if, for example, a position or a composite value is read with "%". Although it is possible to select more than one row in the "S" mode, the confirmation of the OK button leads to a note in the Excel cell "more data than allowed in the cell".
  • M: displays the quantity of data sets in the current cell that make up the total value of a formula and the individual values in a table.

When the OK button is confirmed, the number of records will appear in the active cell and the table with the desired results either in a newly created workbook or in the cell address specified in the field provided (in the formula editor at the bottom left):

  • C: this mode is technical and is not intended for use by the user. Although it is possible to select this mode. The entry "C" is automatically assigned to cells for which a Field/Listbox is stored and these can be reworked with it.

The subchapter Field/Listbox in this document provides instructions for creating a selection list.

Reading of Master and Control Files

Accounts

You can use this function to read the attributes of accounts (by using the entry field definitions):

1) When you enter fields such as account and position, the fields Chart of accounts /chart of controlling objects or Chart of positions / Schema will be highlighted in green. If the field is left empty, the plans will be selected according to the settings made in the database (application "VOR").

2) Language: If accounts in the IDL.KONSIS database are stored in several languages, the desired language for the output can be selected here.

3) The desired data can be selected in "Field definitions" and read from the IDL.KONSIS database (in detail explained in chapter 5.1.1)

Controlling objects

You can use this function to read the attributes of controlling objects:

Companies

You can use this function to read the attributes of companies:

Mode M can be used to read out lists of companies with certain attributes; example:

All companies with the country code "I" for Italy, with their ID and short word:

Positions

You can use this function to read the attributes of positions in a particular chart of positions.

Account balances

You can use this function to read account balances and postings. Note the various balance options.

  • ALT: The account balances of the individual financial statement are read (KTOSAL).
  • BUC: The postings of the individual financial statement are read (BUCH).
  • NEU: The result of account balances and postings is read.

IC account balances

This function enables you to read the business relationships of the IC companies:

The arrows show the relationships to mandatory fields that should be entered when reading out certain data.

Controlling balances

With this function, it is possible to read out the cost center balances in IDL.KONSIS. As usually it is mandatory to select one of the balance options:

  • ALT: The account balances of the individual financial statement are read (CNTSAL).
  • BUC: The postings of the individual financial statement are read (BUCH).
  • NEU: The result of account balances and postings is read.

Fixed asset transactions

Using the Fixed asset transactions application, IDL.XLSLINK reads the development transactions in IDL.KONSIS in "ANLBEW".

Development transactions

The IDL.XLSLINK reads the development transactions in IDL.KONSIS in the respective applications "xxxBEW" with the applications capital / provision / additional development transactions.

Position balances ex reports

IDL.XLSLINK uses the Position balances ex reports application to read the values in IDL.KONSIS in the POSSAL application. It is mandatory to select one of the balance options:

  • ALT: The account balances of the individual financial statement are read (POSSAL).
  • BUC: The postings of the individual financial statement are read (BUCH).
  • NEU: The result of account balances and postings is read.

To be able to read position balances at the company level with IDL.XLSLINK, it is necessary that the report with the option “Position balances ex reports” has been created in IDL.KONSIS in the application "REP".

Background: Position balances cannot be entered in IDL.KONSIS. They are automatically generated by IDL.KONSIS when the user creates a report with the entry S by pressing the position balance output button.

Shareholding/participations

This function can be used to read the shareholdings maintained in IDL.KONSIS (GESGES). The reading of the shareholding percentages is controlled by the field "Field definitions".

Read function Group financial statement data

Account balances

The read function for account balances on group level has the following special features:

  • Currency: The currency types KW for Group currency and PW for parallel currency are offered for selection for the group.
  • Mode: As in individual closing, you can only work with mode "S" for single value and "M" for value quantity.
  • Balance option: You can choose from four variants typical for the Group:
    • SUM: The account balances of the summarized financial statement are read (KTOSAL).
    • KON: The consolidation postings of the Group financial statement including all subgroups are read (KONBUCH).
    • KONO: The consolidation postings of the Group financial statement are read (KONBUCH). The subgroups are not taken into consideration.
    • KTK: The result from account balances and consolidation postings is read.

Position balances ex reports

With the balance option, the four Group-specific variants are also available for selection in the "Position balances ex reports" export function:

  • SUM: The account balances of the summarized financial statement are read (POSSAL).
  • KON: The consolidation postings of the Group financial statement including all subgroups are read (KONBUCH).
  • KONO: The consolidation postings of the Group financial statement are read (KONBUCH). The subgroups are not taken into consideration.
  • KTK: The result from account balances and consolidation postings is read.

To be able to read position balances at Group level with IDL.XLSLINK, you must have created the report with the setting S or P in the button for position balances in IDL.KONSIS in the "REPK" application.

Background: Position balances cannot be entered in IDL.KONSIS. They are generated automatically by IDL.KONSIS if a report with the corresponding setting has been created in IDL.KONSIS.

Difference between Report Option S and P
P S
Position balances are generated for the entire Group and also for the individual companies. Position balances are only generated for the entire Group.

Export function

The Export function

Step 1) Call up the IDL formula in the cell in which the reference to the export of data is to be created.

Step 2) Depending on the facts, the corresponding input mask is selected in the selection tree in the area of the Export function.

Step 3) In the input mask, the IDL.XLSLINK reference for the export is created by filling individual fields. The fields highlighted in color are mandatory fields.

Step 4) The export formula is displayed and can be copied to other cells:

Step 5) In the "Export" area of the menu bar, select the appropriate submenu item depending on the area to be exported.

If a selected range is to be exported, it must be marked before clicking on the "Selected Range" tab.

Step 6) The export is triggered:

1) Six rows have been found for the selected action.

2) Save: The data is loaded directly into the IDL database.

3) Delete/Save: The data is loaded directly into the IDL database. The existing data in the database is first deleted and then the new data is imported.

4) Export button: starts the export process.

Additional settings for the export can be made in the field "Options".

  • With carry forward: According to the IDL logic, the carry forwards are created as part of the Group and individual financial statement carry forwards and are not imported in any other way. If, however, it is necessary for certain reasons, the export of the carry forward movements can be activated.
  • Select path for export files: The data can either be loaded directly into the IDL database or indirectly via a txt file. If an individual path is to be used, it can be selected via the "Save into textfile" button. By default, the import path of IDL.KONSIS is set here.

Step 7) As soon as the export is completed, the status message for the export will appear; either the processing has gone through without errors or an error message is displayed. The corresponding information is also displayed in the Excel cell.

The settings from the input mask can be found in the formula bar:

The formula name reflects the associated function. Here EA for individual financial statement and KTOSAL for account balances.

Export of master and control files

Accounts

This input mask is used to export accounts to IDL.KONSIS in the "KTO" application.

Positions + accounts allocations

In the IDL.KONSIS logic, each account is assigned to a position. Conversely, each position consists of one or more accounts.

With this function, IDL.KONSIS imports into the POSKTO application which account is to be allocated to which item.

Report line definitions

A report line definition determines how a report is structured and from which positions totals rows are calculated.

The report line definition is necessary for creating reports in IDL.KONSIS and using IDL entry forms.

Export individual financial statements

Account balances

This function imports account balances according to IDL.KONSIS into the "KTOSAL" application.

IC account balances

This function is used to record intercompany business relationships.

In addition to the usual country currency, you can also maintain a transaction currency.

Development transactions

With these applications, IDL.XLSLINK exports the development transactions to IDL.KONSIS to the respective application "xxxBEW".

Shareholding/participations

With this application, IDL.XLSLINK exports the shareholding transactions to IDL.KONSIS to the "GESGES" application.

Vouchers and postings

Before one or more postings can be exported to IDL.KONSIS, a corresponding document header must be set up in the "BEL" application. If no document header has been set up, the message "No posting document exists" will appear in the export notice.

Vouchers can be exported to IDL.KONSIS via the input mask "Vouchers".

With this application, IDL.XLSLINK exports the postings to IDL.KONSIS:

Export Group financial statement

Consolidation vouchers

To be able to load consolidation postings into IDL.KONSIS, the consolidation vouchers must first be available in the database. These can be imported into the system using IDL.XLSLINK.

For the setup, specifications in ten mandatory fields are required:

Consolidation postings

After creating or importing the consolidation documents, the consolidation entries can be imported. The corresponding Voucher no. with its individual components must be entered:

All three relevant fields for the entry of the posting amounts

- Debit/credit code

- Debit amount group currency

- Credit amount group currency

are enabled for entry independently of each other:

The following cell content can be in the S/H Indicator field - other content will result in an error:

- Empty - no content at all in the Excel cell.

- 'D' - indicates debit value => amount from the field for GG Debit will be exported

- 'C' - indicates credit value => amount from the field for CG Credit will be exported

(Note: If you want to use the English variant D/C, you have additionally to enter ENG in the field for ‘Coding Language’, or otherwise work with the German characters S (for D) or H (for C)).

The following cell contents can be in the fields for the debit or credit amount - other contents will result in an error:

- Empty - i.e. no content in the input field or in the assigned Excel cell.

- 0 - zero by direct input, formula or empty cell reference

- a positive value by direct input, formula or cell reference to pos. value

- a negative value by direct input, formula or cell reference to neg. value

If an S (for debit) is entered in the S/H indicator field, the value entered in the debit amount field will be exported. If this value is positive, it will be written as a debit entry; if it is negative, it will be written as a credit amount.

Similarly, if "H" is entered in the field for the S/H indicator, the amount entered in the credit field will be taken into account; if the value is positive, it will be written as a credit entry, if it is negative, it will be written as a debit entry.

If there is no entry in the field for the D/C code, the amount from the field that contains an entry is exported. If amounts are entered in both fields without an S/H entry, no export will take place because no unique assignment is possible.

Thus, only one of the two amount fields can be used as "the" input field, analogous to the single record entry in IDL Konsis.

Excursus – Excel

Each formula begins with an equal sign (=). The work is preferably done with cell references. This offers the advantage that the formula result always adapts when the corresponding cell content is changed (variable values). However, fixed values (constant values) can also be used in a formula.

The result of a formula is shown in the cell in which the formula was entered. The formula itself can be seen in the editing toolbar. The formula can also be edited there if required.

Functions

Excel offers many ready-made formulas, the so-called functions.

Components of each function are function name and parenthesis open/closed. The information contained in the brackets is called arguments. The number of arguments varies from function to function. There are also functions that do not require arguments, e.g. TODAY(), NOW().

When a function is entered into a cell, a quick info with the syntax is displayed. When entering, the arguments required by the function will be displayed.

Multiple arguments are separated by a semicolon. Arguments can be cell references (individual cells, cell ranges), constant values (numbers, text) or other functions (nested functions).

Cell references

Relative cell reference

Basically, all cell references are relative, i.e. when copying, these cell references are adapted "relative" to the target position: When copying in the row (horizontal), the column letter is adapted and when copying in the column (vertical), the row number is adapted.

Example: in cell B7, the column sum is to be calculated. The formula is = TOTAL(B3:B6). The formula is then copied to cell C7. The cell references are adjusted automatically.

Absolute cell reference

If a cell reference in a formula always refers to a specific cell, this cell reference must be specified as the absolute cell reference so that the result is correct after copying the formula.

It remains unchanged during copying and therefore does not adapt. To mark a cell reference as an absolute reference, a dollar sign ($) is entered before the row and column caption. This can be done when entering the cell reference or later via the editing bar.

Example: In this example, cell B1, which contains the VAT rate, must be entered in the formula as the absolute cell reference so that the formula can be copied. The other cell references remain relative because they are to be adapted during copying.

Tip: The $ characters can also be set with a single click of the F4 key on the keyboard.

Mixed cell reference

Cell references can also be entered as mixed references. This means that the entire cell is not taken as a fixed reference, but only the column or row is fixed:

  • for the absolute column, enter the dollar sign before the letters ($A3).
  • for the absolute row, the dollar sign is entered before the number (A$3).

Example: Starting from actual sales, you want to calculate two plan scenarios. For the formula of PLAN B, however, the formula of PLAN A should be used. So, the formula always remains in one row (absolute), but the column adjusts itself (relative).

Names for cells / cell ranges

For better clarity, individual cells or cell ranges can be assigned names. Formulas become clearer and more comprehensible if names are used instead of cell addresses. In addition, the navigation becomes easier, since the named cells/cell ranges can be controlled by their names. Names assigned once are available on all worksheets of the workbook.

There are two ways of naming the cell/cell ranges:

Variant 1:

The cell or area to be named is marked and the cursor is moved to the name field in the upper left corner of the editing bar. The desired name is entered and confirmed with Enter. The name must correspond either to a valid cell reference or to an existing name in the table.

Variant 2:

The name can also be entered via the "Formulas - Name manager – New" tab.

In IDL.XLSLINK, an Excel name field can be selected and inserted in the input window by using the right mouse click:
For an optimal Community experience, Please view on Desktop
Powered by Zendesk