Announcing Velixo Reports for Excel And Acumatica

As you may have learned from my last blog post, one of my objectives with Velixo is to build a portfolio of unique products and services for the ERP market. My first product is Velixo Reports, an Excel add-in for financial reporting that’s specially designed for Acumatica.

Acumatica and Excel

What makes Velixo Reports unique?

As an Acumatica-first tool, Velixo Reports was designed with the cloud in mind. Retrieval of the data uses the OData protocol that’s natively supported by Acumatica, and no direct connection to the database is needed. To ensure the best possible performance and to minimize round-trips to the ERP server, frequently used data is cached locally to a SQLite database that is updated incrementally. This database is managed by the add-in and is transparent to the user — no configuration is needed.

The add-in also builds on the expertise that I acquired optimizing the ARM calculation engine in Acumatica for a company with 200 branches, 16 000 GL accounts and over 10 million GL entries in their database. The customer for whom I am building this for has a fairly complex setup as well, and I can guarantee that performance will always be the top priority.

Being Acumatica-native means that we can leverage the unique features of Acumatica — many ideas on the roadmap wouldn’t be possible if I had to support 5 different ERPs.

Downloading and installing the add-in

The add-in is currently compatible with Acumatica 5.3 and later, as well as with Excel for Windows 2010 and later. For best performance, Acumatica 6.1 or later is recommended (streaming mode allows Acumatica to send data back to the add-in more quickly and more efficiently).

To install, just double-click the .msi file and follow the steps. The installation process takes just a few seconds, and once completed it will offer you to launch Excel automatically.

Creating your first spreadsheet

The first step after you open Excel is to connect your spreadsheet to Acumatica. The add-in adds a new ribbon tab to Excel, aptly titled Acumatica. Click on the Connection Manager button, and from there, click Add. You need to give a unique name to your connection; you will use this name later when building formulas to refer to it.

As you may have guessed by looking at the screen, it is possible to connect to multiple companies and multiple Acumatica websites from a single spreadsheet. That allows you to easily consolidate data coming from multiple sources.

If you decide to save the password for a connection, please be aware that it is not actually saved inside the spreadsheet, but instead inside your user profile, encrypted with the Windows Data Protection API (DPAPI).

The first time you connect to a company, the system will prompt you to create the generic inquiries needed to support Velixo Reports:

Just click yes, and all the inquiries will be created and configured for you. During the process, the Excel status bar will update, showing you which inquiry the system is working on. The whole process takes about 15 seconds.

Please be aware that you need access to the Generic Inquiries page in Acumatica to do that. Once the inquiries have been created, you only need to have read access to the VelixoReports-*** inquiries (from the Hidden section of the site map) to be able to use the add-in.

Your first formula

We’ll start with something very simple — retrieving the name of an account. In the A1 cell, we will put the account code, in this case, 40000. In the A2 cell, we will use the ACCOUNTNAME function. As you start typing, you’ll notice that the add-in provides you with IntelliSense:

The first parameter is the name of the connection that we created earlier — i’m using “Demo”. The second parameter required by this function is the account code. The account code is in the A1 cell, so we’re just going to click on the cell and Excel will automatically add a reference to the cell in the formula. Our completed formula should look like this:

=ACCOUNTNAME("Demo", A1)

Press Enter. Since it is the first time that we’re accessing the accounts data source, the add-in needs to cache it locally. Unless you have thousands of GL accounts, that should only take 2–3 seconds. Subsequently, Excel will only load modified data, and will only do so once per session. Performance was my #1 priority while building this add-in and I want you to have a great user experience even when working with a very large Acumatica database.

Getting the balance of an account

Let’s continue to build our spreadsheet and do something more useful. We’re going to get the current balance of the Petty Cash account (account 10100). To do so we’re going to use the ACCOUNTENDINGGBALANCE function. There are many more functions exposed by the add-in and a complete list will be presented in the next section of the post.

The ACCOUNTENDINGGBALANCE function expects a few different parameters:

1. Connection: The name of the connection configured in the Connection Manager — in our case, “Demo”.
2. Ledger: The ledger where the calculation should be made. Every type of ledger is supported: Actual, Reporting, Statistical, Budget.
3. Account Class: I’m going to leave this blank since we are specifying the exact GL account to use. You can specify an account class only, an account only or a combination of both and the system will determine which account(s) to use for calculation.
4. Account: The account that we need the balance for. I could type “10100” directly in the formula but since the account number is in cell A1 I’m just going to enter a cell reference. Pro tip: clicking on the cell while typing your formula will automatically enter the right reference — it works even if the reference is in a different sheet.
5. Subaccount: I’m not looking to focus on a specific subaccount so I will leave this parameter blank.
6. Branch: Same thing once again, I leave it blank. I want to current balance for every branch.
7. As Of Period: The financial period for the calculation. We’re going to enter “11–2017” directly in the formula. Don’t forget to put the period between quotes, otherwise, Excel will subtract the numbers and assume you want period -2006!

Note: Cash accounts in Acumatica belong to a single branch and subaccount so you will never get anything but a big fat zero if you specify anything but the subaccount and branch which was configured in the Cash Accounts maintenance.

Once you’re done, press enter and the add-in will automatically calculate the balance:

Since it is the first time you’re calculating a value for this connection, ledger, and financial year, Velixo Reports will connect to Acumatica to retrieve and cache the required data. Subsequent calculations will be instant. In case you have doubts about the accuracy of the result, you can always go to Acumatica to double-check.

Ranges, wildcards and combining multiple accounts, subaccounts or branches

In the previous example, I showed you how you can get the balance of a single account. What if you want to get the total for multiple accounts, subaccounts or branches? Sure, you can use the “+” operator to add the values, but your formula will get messy very quickly (and it won’t be as fast to calculate). You’re in luck — ranges and wildcards are supported. Here are some examples of what’s possible:

1. Range: “20100:20150” — all the accounts between 20100 and 20150
2. Wildcard: “6?000” — any account that starts with a 6 and ends with 000 (ex: 61000, 63000, etc.)
3. A mix of single accounts, ranges and wildcards can be combined together by using the “;” character: “6?000;61100;69000:69999”

You can use the same notation everywhere an Account, Subaccount or Branch is required, with the exception of theACCOUNTNAME function which expects a single account.

More Functions

Here’s an exhaustive list of functions available:

• ACCOUNTNAME(Connection, Account) Returns the name of a GL account.
• ACCOUNTTURNOVER(Connection, Ledger, AccountClass, Account, Subaccount, Branch, FromPeriod, ToPeriod) Calculates the turnover of one or more GL account(s) for a given period.
• ACCOUNTTOTALDEBITS(Connection, Ledger, AccountClass, Account, Subaccount, Branch, FromPeriod, ToPeriod) Calculates the total debits of one or more GL account(s) for a given period.
• ACCOUNTTOTALCREDITS(Connection, Ledger, AccountClass, Account, Subaccount, Branch, FromPeriod, ToPeriod) Calculates the total credits of one or more GL account(s) for a given period.
• ACCOUNTBEGINNINGBALANCE(Connection, Ledger, AccountClass, Account, Subaccount, Branch, AsOfPeriod) Calculates the beginning balance of one or more GL account(s) as of a given period.
• ACCOUNTENDINGGBALANCE(Connection, Ledger, AccountClass, Account, Subaccount, Branch, AsOfPeriod) Calculates the ending balance of one or more GL account(s) as of a given period.
• BRANCHNAME(Connection, Branch) Returns the name of a branch.
• FINANCIALPERIODSTARTDATE(Connection, Period) Returns the start date of a financial period.
• FINANCIALPERIODENDDATE(Connection, Period) Returns the end date of a financial period.

A complete example

By building on the formulas, tools, tips and tricks you learned so far, you should be able to build a full P&L, balance sheet or trial balance using the add-in. If you’re as impatient as I am, you might want to download one of the sample spreadsheets that’s available on our support site and connect it to your own instance of Acumatica.

One thing you will notice is that I’ve placed the financial period in the B3 cell instead of hardcoding it in the formulas. That means that if I change it to something else, Excel will recalculate the spreadsheet instantly. To make formulas cleaner and easier to understand, I used the names features of Excel. That means I can simply type “AsOfPeriod” in my formulas instead of “B3”.

Named references are supported in Velixo Reports functions

Staying up to date

Automatic updating is built into the product. Every time you start Excel, the add-in will check if a newer version is available and offer to download it.

Automatic Updates

Support