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.
Add a connection
Click the Connection Manager button, then click Add Connection. Enter a unique Name for the connection, since you will use this name later as you build formulas.
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. Learn more in Connection Manager.
Save Password: If you check this box to save the password for the connection, it's important to realize that that it is not actually saved within the spreadsheet. Rather, it is saved to your Velixo user profile—which is encrypted with the Windows Data Protection API (DPAPI).
The first time you connect to a company, you'll see a popup window that prompts you to create the generic inquiries necessary to support Velixo Reports.
Click Yes, and all the inquiries will be created and configured for you. The entire process takes about one minute, although you will need to have access to the Generic Inquiries page in Acumatica.
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.
|Error 403 - Forbidden: You may encounter an error message indicating a problem with permissions. Learn more in Error 403 Forbidden while refreshing data.|
Your first formula
Let's begin 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, notice that the add-in provides you with the template for the function:
The first parameter for
ACCOUNTNAMEis the name of the connection as it was defined in the Connection Manager , "Demo”. The second parameter that is required by this function is the ACCOUNT code. In our example here, the account code is in the A1 cell. Click on the cell and Excel automatically adds a reference to the cell in the formula. The complete formula should look like this:
Press the Enter key. Since this is the first time that you’re accessing the Accounts data source, the add-in will attempt to cache it locally. Unless you have thousands of GL accounts, this should only take 2–3 seconds. Subsequently, Excel will only load modified data, and will only do so once each session.
Next, try to change the account code in the A1 cell, and then press enter to see an immediate update to the value of the account name cell.
All the functions in Velixo Reports work in a similar way.
Get 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), using the
|Complete list of functions: There are many more functions included in Velixo Reports, and we invite you to explore them all when you complete this tutorial.|
ACCOUNTENDINGGBALANCE function expects a few different parameters:
- Connection — The name of the connection. In this example, it's “Demo”.
- Ledger — The ledger in which the calculation should be made. Every type of ledger is supported: Actual, Reporting, Statistical, Budget.
- Account Class — In this example, leave this empty 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.
- Account — The account for which we want the balance. The value here could be written as “10100” directly in the formula. However, the account number is already in cell A1 so let's use that as a cell reference.
Pro tip: Click on the cell while typing out the formula to automatically enter the correct reference . This works even if the reference is in a different sheet.
- Subaccount — In this example we avoid focusing on a specific subaccount, so this will remain empty.
- Branch — In this example we avoid focusing on a specific branch, so this will remain empty.
- As Of Period — The financial period for the calculation. For this parameter, enter “11–2017” directly in the formula. Don’t forget to surround the period with double-quotes, because otherwise Excel will subtract the numbers and assume you want period -2006!
Press enter to complete the formula. The ending balance will calculate automatically.
Since this 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 necessary data. Any subsequent calculations in this spreadsheet will happen instantaneously.
Ranges and wildcards - Combine multiple accounts, subaccounts or branches
In the previous example, you saw how to get the balance of a single account. Now, what if you want to get the total for multiple accounts, subaccounts, or branches? Yes, you can use the + operator to add the values, but this could result in a messy formula and sluggish calculations. There's a better way—with ranges and wildcards.
You can substitute a range or wildcards in any place that an account, subaccount, or branch is necessary. The only exception is with the
ACCOUNTNAMEfunction, which expects a single account.
Here are some examples of what’s possible:
- Range — “20100:20150” will return all the accounts between 20100 and 20150
- Wildcard — “6?000” will return any account that starts with a 6 and ends with 000 (examples would be 61000 or 63000).
- Excluding or subtracting specific accounts from a range — "40000:49999;-45610" would subtract the balance of account 45610 from the total.
- A mix of single accounts, ranges and wildcards can be combined together by using the “;” character — One example would be “6?000;61100;69000:69999”.
A complete example
By building on the formulas, tools, tips and tricks you've learned here, you should be able to build a full P&L, balance sheet, or trial balance using the Velixo add-in.
Example spreadsheets: Allow us to suggest that you download and explore one of the sample spreadsheets and connect it to your own instance of Acumatica.
Here’s a view of the Rapid Byte example: