Prerequisites

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).

Downloading and installing the add-in

Your partner will provide with you with the download link. 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 Connection. 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. The whole process takes about one minute. 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 VelixoReportsPro-*** 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. 

Now, let’s try to change the account code in the A1 cell, and press enter.

The account name updates instantly. All the functions in Velixo Reports works the same way.

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 is available here.

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!

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.

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. Excluding or subtracting specific accounts from a range: "40000:49999;-45610" — would subtract the balance of account 45610 from the total
4. 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 the ACCOUNTNAME function which expects a single account.

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 available on this site and connect it to your own instance of Acumatica.

Here’s how it looks:

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.