Integration with Microsoft Excel
According to research, nearly a quarter of accountants have experienced financial or reputational damage due to spreadsheet errors.
One way that Aqilla exploits web services is through the use of the Microsoft Excel Add-in. With this add-in installed, the user may very simply analyse live data, update reference data or import transactional data from an Excel worksheet.
Unlike using exported data, which is a snapshot of the information when the export is run, the enquiry functions allow you to define reports in Excel and the data is kept up to date by using the Refresh option. Illustrated are two examples of the enquiry functions. The first (see above right) replicates the functionality of the Account Enquiry offering accounts and period ranges using simple drop-down lists.
Maintain your Data
There are a number of occasions when it is more convenient to collect reference information externally such as setting up suppliers from the previous purchasing system or feeding project information from the project management software.
In these situations, the external data can be imported into Excel and mapped to the layout required by Aqilla. This data is then added into Aqilla using the functions provided with the same validation as if the data had been entered manually through a browser.
Alternatively, you may wish to review data that is currently held in Aqilla, such as reviewing your debtor’s credit limits or updating your item prices. You make the changes in Excel to individual records and the changes are applied back to Aqilla.
Information may be collected from a whole range of external systems. For instance, payroll costs supplied from the payroll system, purchase invoices from the document management system, sales invoices from the sales order processing system, or even account balances from the previous accounting solution. In all cases, this data can be imported into Excel, restructured if necessary to meet the requirement of the Aqilla document formats and submitted to Aqilla for validation and posting. Depending on the workflow defined within Aqilla, the documents may be saved for final checking and approval or automatically posted into the ledger.
Using the Excel Add-In is very straightforward. It is accessed via a menu option or via the tool bar in common with other extensions. Once a secure connection to the database is achieved by logging in with a username and password, a user can select from a range of functions covering the ledger, customers, suppliers, item lists as well as analysis categories such as department, project etc. When modifying information, the Add-In automatically identifies which records have been updated and saved into Aqilla.
Parameters set up in Aqilla can also be included in a spreadsheet as functions. This means for example that the selection of date ranges can be made to reflect that which is set up in the main database - making it straightforward to select data from ‘This Period’, ‘This Quarter’ or the current financial year for example.
The ability to reference a unique piece of data within a cell provides support for in-line editing. This is especially useful when updating lists of information such pricing on the items file or updating contact details on customer records.
Of course, once the data resides in Excel it can be referenced for the production of graphs, tables, reports and further analysis combined with data derived from other sources. Furthermore, Excel can act as an excellent tool to consolidate financial data from more than one company or act as a gateway for Microsoft’s SQL-Server Analysis Services enabling you to enables you to query your most current business data using the latest cube functions to build a custom report from an OLAP database.
Installing the Add-In
With this add-in installed, the user may very simply analyse live data, update reference data or import transactional data from an Excel worksheet.
Contact Aqilla for the password to download this Add-In then download the required Add-In tool here.
Watch a short demonstration to see how you can make the most of your spreadsheets: