Financial Reporting with XML (Excel®) Templates
AcctVantage supports the ability to export financial reports to an XML file that is readable by Microsoft Excel®.
You can design financial reports using what are called tokens in place of GL account codes, account names, actual or budgeted dollar amounts and reporting periods. When the report is generated, AcctVantage replaces these tokens with the requested data and exports an XML file. You can include formatting, formulas, logos, etc. in the template as in any Excel® spreadsheet. At this time, only single worksheet XML files can be processed. In a multiple page workbook, pages after the first page will be ignored.
We will detail the token definitions below. When designing your financial report, you will use a token in place of the indicated value. If the token represents a range of GL accounts, AcctVantage will automatically expand it to include a row for each account within the range. Any formulas will be adjusted as necessary to accommodate the rows added for a range of accounts.
1. XML Tokens
Tokens begin with *AV (Enter the token in the cell where you want the corresponding data to appear.)
- *AVRY* - Returns the actual Report Year.
- *AVRP* - Returns the actual Report Period.
- *AV* ACCTNAME - Returns the GL Account Name.
- *AV* ACCTCODE - Returns the GL Account Code.
- *AV* ACT - Returns Actual Activity in dollars. Append a Modifier for the Report Period/Year.
- *AV* BUD - Returns the Budget Amount in dollars. Append a Modifier for the Report Period/Year.
- *AV* BU2 - Returns the Budget Amount (2nd Budget) in dollars. Append a Modifier for Report Period/Year.
- *AV* BAL - Returns the Account Balance in dollars. Append a Modifier for Report Period/Year.
2. Token Modifiers
Tokens are entered into cells along with Modifiers that indicate either a GL Account Code range or the desired timeframe for the dollar amounts.
- xxxxx-xxx-xxx - GL Account Code. Used only with *AV* ACCTNAME and *AV* ACCTCODE tokens.
- RP - Returns the actual or budget dollar amounts for the indicated Report Period. This is established in the report dialog by the user when the report is printed.
- RY - Returns the actual or budget dollar amounts for the indicated Report Year. This is established in the report dialog by the user when the report is printed.
- Px - Period x. Where x represents a fiscal period. (If January is the first month of the fiscal year then January = P1)
- xY or xP - When x has a numeric value, you can use this modifier to subtract years or periods.
- , (comma symbol) - A comma is used to group report year and report year modifiers into a single value.
- : (colon symbol) - A colon is used to separate “from” and “to” values in a range.
2.1. Sample Tokens
The lists above are a little bit cryptic, so we’ve included a selection of sample Token/Modifier combinations that you can copy and paste into your XML files.
- *AV* ACCTNAME 40000-000-000:49999-000-000 - This token will be replaced with the GL Account Name. It will create a new row on the report for each account in the range. Any other tokens on the same row will refer to this token.
- *AV* ACCTCODE - This token will be replaced with the GL Account Code. In this example, we are assuming that the account code range was defined in a previous cell on the same row, as in the *AV* ACCTNAME 40000-000-000:49999-000-000 token above. You can also indicate an account code range as a modifier for this token but you only need to enter one account code range modifier per row.
Income Statement
- *AV* ACT RP,RY - Current fiscal period activity for the indicated report period (RP) and report year (RY)
- *AV* ACT RP,RY-1Y - Actual dollar amount for the indicated report period (RP) and one year prior to the indicated report year (RY). In other words, the same month last year.
- *AV* ACT P1,RY:RP,RY - Year to date. Actual dollar amounts for the 1st period of the report year through (:) the indicated report period/report year.
- *AV* ACT P1,RY:P3,RY - 1st quarter. Actual dollar amounts for the 1st period (P1) of the report year (RY) through (:) the 3rd period (P3) of the report year (RY).
- *AV* ACT P1,RY-1Y:P3,RY-1Y - 1st quarter, last year.
- *AV* ACT RP,RY-1P - Last month.
- *AV* ACT RP,RY-11P:RP,RY - Activity for the 12 months prior to the end of the current period.
Balance Sheet
- *AV* BAL RP,RY - Current fiscal period account balance for the indicated report period (RP) and report year (RY).
To add AcctVantage data to your template, you’ll need to use tokens as described above.
- Each row of data on your report must include either the ACCTNAME or ACCTCODE token with the GL Account range modifier. This token must be placed to the left of any other tokens on the same row.
- Other tokens placed on the same row for balances, budget or activity will refer to this account code range.
- You may have rows that are used only for headers, text or calculations that do not refer to an account code range.
3. Create a Template
- Rows 7, 8, 13, 21 & 22 represent data that is to be pulled from the AcctVantage database.
- Notice the cells in Column A that have the token *AV* ACCTNAME as well as an account code range. The leftmost column of data (although not necessarily Column A) must contain either the ACCTNAME or ACCTCODE token plus the account code range.
- Additional tokens or data elements on the same row will use the account code range defined in the leftmost column. Account codes do not need to be called out for each token, only the leftmost token.
- The following cells contain SUM formulas. These cells will sum the activity for the account code range(s).
- C9 & E9 (Total Revenue)
- C15 & E15 (Total COGS)
- C23 & E23 (Total Operating Expense)
- The following cells contain formulas that total the above SUM formulas:
- C17 & E17 (Gross Profit)
- C26 & E26 (Net Income)
- Columns C & E in this example are being used to display the actual (as opposed to budgeted) activity for the reporting period (RP,RY - i.e. report period, report year). Remember that when printing the report, you determine the reporting period (or “as of” period) for the report in the report dialog.
- Column E in this example is being used to display the Year to Date (Period 1 of the report year through reporting period).
Note: When we print this template (see below), each data row (e.g. rows 7, 8, 13, 21 & 22) will expand downward to include a row for each account within the defined range that has activity.
4. Save the Template
Your template must be saved in the XML Spreadsheet (*.xml) format. The system will not read *.xls or standard Excel® spreadsheet format.
4.1. Do you want to continue?
Click Continue when you see this message when saving in Excel®.
After you save the XML template, close the Excel® application. If the application is still open you won't be able to complete the next step of defining the report.
5. Install the Template (Define the Report)
Navigate to General Ledger ➤ Financial Reports and click the Create New Record icon or use the keyboard shortcut Command + N.
5.1. What type of financial report do you want to define?
Choose Excel/XML at the prompt.
5.2. Define the financial report.
- Give the template a Report Name and Report Title
-
Report Type:
- Operating if you are defining an Income Statement.
- Balance if you are defining a Balance Sheet.
- After you define the report click the Load from Disk button and point to your XML file. Click OK to save. Your template will be copied into the AcctVantage data file. You can remove it from your client workstation if you like.
6. Print the Report
- Go to General Ledger ➤ Print Financial Reports.
- Highlight the report to print.
- Choose a period to print the report "as of."
- Click Print.
6.1. Choose a location to save the report.
When you select Print, the system will direct you to give the file a name and a save location. Rather than printing to paper, the system will export an XML file in the save location you indicate.
6.2. View the report.
This is what the above template will look like when the saved report is opened in Excel®.
7. Modify a Template
- If you need to modify your template, you can retrieve it from the AcctVantage data file by opening the record in the General Ledger ➤ Financial Reports screen. Click the Save to disk button to export the template to your client workstation.
- You can then open the template, make changes and use the Load from disk. button to overwrite the installed template with your new one.
