One moment please...
 
Exact Financials   
 

Working with Excel Add-in

Starting the Excel Add-in

  1. Start Excel. 'Exact Financials' is added to the default menu bar.
  2. Choose [Login database]. The login screen appears, in which you can use Exact by entering your normal user name and password.
  3. Before you open or create a workbook, you should start [Login database], so that the Exact database can be approached and the extra functions are shown in the section 'user-defined'.

What should you do when login fails

This can have several causes:

  • You work with a so-called local configuration, with a 'single-user' license and you are already logged in on the database with Exact Financials. In this case you need to close Exact Financials.
  • You cannot approach your database if the ODBC connection does not work properly. Consult your ODBC documentation or contact your system manager.

Using data from the Exact database in Excel

You can generate data from Exact in Excel using functions. These functions are explained in the next chapter. This chapter explains how you can enter a function in a worksheet and which resources can be used.

General remarks on functions

A function consists of:

  • The '=' sign
  • The function name
  • Data that determine the use of the function. In this manual, this data is called parameters. Examples of parameters are general ledger account numbers, the financial year and the company number.

    Parameters are separated by so-called 'list separators'. It is recommended to use a comma or a semicolon. You can indicate this in [Settings, Control Panel, Regional Settings] in Windows.

Example

The function for calculating a general ledger account balance is as follows:

=ACC_BAL(acc_nr,co,FY,period,balance_type,value,calc).

The parameters indicate which data should be used, like the account number, the company number(co), the financial year (FY) and the period.

Entering a function in a worksheet

  1. Select the cell in which the formula should be entered.
  2. The Exact functions are available if you use the function wizard in Excel and choose 'User defined' in the 'Function Category' screen.
  3. Select the function you want to use based on the data in this on-line help file.
  4. Click 'Next' to enter the parameters for the function.
    The parameters determine which data is to be retrieved and how it is to be used.
    • To enter the function directly into the worksheet without entering the parameters, choose 'Finish', instead of 'Next'. You then need to enter the parameters manually. Some parameters should be entered with their numerical value. If you use the wizard, you can choose the actual values.
    • Note that not all parameters are directly visible, use the scroll bar to see more parameters.
  5. Click 'Finish' to enter the filled function into the worksheet.

Example

You want to insert the balance of the general ledger account 8000 'Revenue accessories' in period 12 of 2002. Use the function = ACC_BAL(acc_nr,co,FY,period,balance_type,value,calc). First, select the cell in which the revenue amount should to be entered.

Activate the Exact function wizard, choose 'ACC_BAL' and click 'Next'. Then enter the following data:

Field Value Explanation
acc_nr "8000" Quotes are mandatory
Co 100  
FY 2002  
Period 12  
Balance_type 1 Balance of the entered and posted amounts
Value 3 Balance of the account
Calc 1 Period balance

Click 'Finish'. The chosen balance is shown in the worksheet. The formula entered in the activated cell is:

=ACC_BAL("8000",100,2002,12,1,3,1).

Additional remarks on parameters

  • Combining cell references with parameters.

    Instead of a parameter you can use a cell reference of the cell the parameter is entered in. With this your worksheet becomes very flexible.

    Example

    To retrieve the account balances of period 1 of all revenue accounts in column B. Enter the period number in cell B4 and refer to cell B4 in the functions. To retrieve the balances of period 2, you should copy column B to column C and enter period 2 in cell C4.

  B C
4 1 2
5 =ACC_BAL("8000",100,2002,B4,1,3,1) =ACC_BAL("8000",100,2002,C4,1,3,1)
6 =ACC_BAL("8010",100,2002,B4,1,3,1) =ACC_BAL("8010",100,2002,C4,1,3,1)
7 =ACC_BAL("8020",100,2002,B4,1,3,1) =ACC_BAL("8020",100,2002,C4,1,3,1)

The function =ACC_BAL(acc_nr,co,FY,period,balance_type,value,calc) is used for retrieving account balances.

  • Cell reference can be locked so that it does not change when copying. In the above example, the reference B4 will automatically change into C4 when copying. If you do not want this, you can lock the reference B4 by changing it into $B$4. This is called an absolute cell reference.

Additional remarks on refreshing data

If you want to see the changes in Exact Financials in Excel, Excel does not offer the possibility to update the data directly. The data will only be updated after closing and reopening the spreadsheet. However, by using references to one cell in all formulas, you only need to change the value in this cell to update the spreadsheet. This cell can, for instance, contain the company number.

Example

  B C
4 company 100
5 1 2
6 =ACC_BAL("8000",$C$4,2002,B$5,1,3,1) =ACC_BAL("8000",$C$4,2002,C$5,1,3,1)
7 =ACC_BAL("8020",$C$4,2002,B$5,1,3,1) =ACC_BAL("8020",$C$4,2002,C$5,1,3,1)

See also:

[Alphabetical overview of parameters]



Finance ManualExcel Add-in


 

     
 Main Category: Support Product Know How  Document Type: Support - On-line help
 Category: On-line help files  Security  level: All - 0
 Sub category: Details  Document ID: 18.314.377
 Assortment:  Date: 02-04-2014
 Release:  Attachment:
 Disclaimer