From Journals to Closing, Aico delivers intelligent automation for finance. 

Aico modules provide you with a wealth of possibilities to automate your routines. Account reconciliation, closing and journaling as well as clearing, retrieving and updating live data from your ERP system. These are all standard features of Aico. 

In a number of places, Aico leverages the capability of Excel to allow you to manipulate and upload data into Aico documents. 

This article describes the Excel calculation capabilities of Aico.

• Aico Excel Calculation
• Importing data through Excel
• Import ERP report data using Aico Closing Automation
• Excel calculation
• Supported functions
• Unsupported functions
• Unsupported functionality
• Aico configuration

Importing data through Excel

Import Excel file through Aico end user interface

To import from an Excel spreadsheet:

Click on the Actions button in the top left-hand corner of your screen.

The below dialogue box will appear. Choose the Excel spreadsheet you want to import.

Aico will perform the Excel formula calculation on the file before the Excel data is imported into the Aico document headers and rows.

Import ERP report data using Aico Closing Automation

It is possible to automate the  running of ERP reports when the closing task is created using Aico Closing automation.

When the task is set up, an Excel template file is created and attached to the task so that the ERP raw data can be transformed into a suitable form and automatically read into the Aico document. 

Aico performs the Excel formula calculation on the file before the Excel data is imported into the Aico document headers and rows. 

Excel calculation

Excel can calculate the values in its cells either automatically or when the user chooses to do so.

When the values are calculated, Excel stores the calculated result in the cells. (See the section below about Pivot tables, which are not fully supported by Aico).

When Aico imports values from an Excel file, it first performs the calculation of the cells and then reads the result into the Aico document. However, even when set to Automatic, Excel performs the calculation when the Excel workbook is opened by an end-user.  

When the file is uploaded into Aico then no end-user opens the file, and so Aico must trigger the calculations itself. This means that not 100% of Excel formulas are supported.

Supported functions

Aico supports the most common Excel functions: 

Database 

DAVERAGE,DCOUNT,DCOUNTA,DGET,DMAX,DMIN,DSUM,DVAR,DVARP

Date and time 

DATE,DATEDIF,DATEVALUE,DAY,DAYS,DAYS360,EDATE,EOMONTH,HOUR,ISOWEEKNUM,MINUTE,MONTH,NETWORKDAYS,NETWORKDAYS.INTL,NOW,  SECOND,TIME,TIMEVALUE,TODAY,WEEKDAY,WEEKNUM,WORKDAY,WORKDAY.INTL,YEAR,YEARFRAC 

Engineering 

BESSELI,BESSELJ,BESSELK,BESSELY,BIN2DEC,BIN2HEX,BIN2OCT,BITAND,BITLSHIFT,BITOR,BITRSHIFT,BITXOR,CONVERT,DEC2BIN,DEC2HEX,DEC2OCT, DELTA,ERF,ERF.PRECISE,ERFC,ERFC.PRECISE,HEX2BIN,HEX2DEC,HEX2OCT,OCT2BIN,OCT2DEC,OCT2HEX 

Financial 

COUPDAYBS,COUPDAYS,COUPDAYSNC,COUPNCD,COUPNUM,COUPPCD,CUMIPMT,CUMPRINC,DB,DDB,DISC,DOLLARDE,DOLLARFR,DURATION, EFFECT,FV,FVSCHEDULE,INTRATE,IPMT,IRR,ISPMT,MDURATION,MIRR,NOMINAL,NPER,NPV,PDURATION,PMT,PPMT,PRICE,PV,RATE,RRI,SLN,SYD,XIRR,XNPV, YIELD,YIELDMAT 

Information 

ERROR.TYPE,ISBLANK,ISERR,ISERROR,ISEVEN,ISLOGICAL,ISNA,ISNONTEXT,ISNUMBER,ISODD,ISTEXT,N,NA,SHEET,TYPE 

Logical 

AND,FALSE,IF,IFERROR,IFNA,IFS,MAXIFS,MINIFS,NOT,OR,SWITCH,TRUE,XOR 

Lookup and reference 

ADDRESS,CHOOSE,COLUMN,COLUMNS,HLOOKUP,INDEX,INDIRECT,LOOKUP,MATCH,OFFSET,ROW,ROWS,VLOOKUP 

Math and trig 

ABS,ACOS,ACOSH,ACOT,ACOTH,ASIN,ASINH,ATAN,ATAN2,ATANH,CEILING,CEILING.MATH,CEILING.PRECISE,COMBIN,COMBINA,COS,COSH,COT,COTH,CSC,CSCH,DEGREES,EVEN,EXP,FACT,FACTDOUBLE,FLOOR,FLOOR.MATH,FLOOR.PRECISE,GCD,INT,ISO.CEILING,LN,LOG,LOG10,MOD,MROUND,MULTINOMIAL,ODD,PERMUT,PERMUTATIONA,PI,POWER,PRODUCT,QUOTIENT,RADIANS,RAND,RANDBETWEEN,ROMAN,ROUND,ROUNDDOWN,ROUNDUP,SEC,SECH,SERIESSUM,SIGN,SIN,SINH,SQRT,SQRTPI,SUBTOTAL,SUM,SUMIF,SUMIFS,SUMPRODUCT,SUMSQ,SUMX2MY2,SUMX2PY2,SUMXMY2,TAN,TANH,TRUNC 

Statistical 

AGGREGATE,AVEDEV,AVERAGE,AVERAGEA,AVERAGEIF,AVERAGEIFS,COMPLEX,CONFIDENCE,CONFIDENCE.NORM,CONFIDENCE.T,COUNT,COUNTA,COUNTBLANK,COUNTIF,COUNTIFS,COVAR,COVARIANCE.P,COVARIANCE.S,DEVSQ,GAMMA,GAMMALN,GAMMALN.PRECISE,LARGE,LCM,MAX,MAXA,MEDIAN,MIN,MINA,MODE,MODE.SNGL,PERCENTILE,PERCENTILE.EXC,PERCENTILE.INC,PERCENTRANK,PERCENTRANK.EXC,PERCENTRANK.INC,QUARTILE,QUARTILE.EXC,QUARTILE.INC,RANK,RANK.AVG,RANK.EQ,SMALL,STDEV,STDEV.P,STDEV.S,STDEVA,STDEVP,STDEVPA,VAR,VAR.P,VAR.S,VARA,VARP,VARPA 

Text 

CHAR,CLEAN,CONCAT,CONCATENATE,DOLLAR,EXACT,FIND,FIXED,HYPERLINK,LEFT,LEN,LOWER,MID,NUMBERVALUE,PROPER,REPLACE,REPT,RIGHT,SEARCH,SUBSTITUTE,T,TEXT,TEXTJOIN,TRIM,UNICHAR,UNICODE,UPPER,VALUE

Unsupported functions

There are some Excel functions that Aico does not support. Most notably Aico does not yet support Excel spill functions (or array functions) introduced by Microsoft in 2020. 

Unsupported functions include but are not limited to; 

FILTER,UNIQUE,SORT,SORTBY,SEQUENCE,SINGLE,RANDARRAY

When Aico imports an Excel file with any of these unsupported functions, the result of the calculation fails, and the data imported into Aico is not correct. 

If you need to support importing files with Excel formulas that Aico does not support, the Aico administrator can configure the list of Excel functions that should force the Aico Excel calculation to be skipped upon Excel file import. 

Unsupported functionality

Excel files support linking to data in many ways, including importing data from other files, databases, or web pages. 

As Aico is a web-based tool, it is unable to refresh the data from an external source outside the Excel file it is importing. 

When Aico imports an Excel file that refers to data from an external source, Aico cannot import the remote data and therefore the data imported into Aico might not be correct. 

Pivot tables

Pivot tables in native Excel require special handling. Unlike formulas, they do not automatically recalculate when the data being read into the pivot table is changed. 

They must either be refreshed manually by clicking on the Pivot Table Analyze menu (1) and choosing the appropriate refresh option (2). Or they can be set up in the pivot table options to refresh when the workbook is opened (3). 

Since Aico does not technically open the workbook then it can only read in the last saved values in the pivot table. 

This means that they can be used in Excel uploads through the web client interface provided the pivot table has been fully refreshed before upload    .

However, it cannot be used in a closing task where you are uploading the ERP data, as the underlying data will change each time the task is run and the pivot table will not be refreshed with that data.

Aico configuration

If you need support importing Excel files with formulas that are not yet supported by Aico, then your Aico administrator can set a list of Excel functions upon which Aico will skip the calculation step when importing the Excel file. 

The end-user will be warned that the Excel calculation is skipped. This works when importing the Excel spreadsheet through the UI because Excel has performed the calculation when the user has had the Excel file open in the Excel desktop application.