BalanceWare Enabled Excel workbooks are a simple, powerful and flexible way to access BalanceWare client data in Excel

Using a simple formula, client profile information, chart of accounts, trial balance, financial statement totals and history are all available in Excel.

A video on this Help Topic is available on our Video Page.

To create the macro enabled Excel workbook, simply open a client in BalanceWare, go to the Accounting Tab and click on BW Enabled Excel in the Financial Statements group. This will open an explorer Window for selecting a folder in which to create the workbook.

 

The BalanceWare ribbon in a BalanceWare enabled workbook: 

 

 

The workbook will have all the normal Excel features and functions, along with a BalanceWare ribbon. The ribbon contains the following functionality:

• Refresh - Used to refresh the Excel workbook if changes have been made to the client's data in BalanceWare.
• Connect - Connects the workbook to any client using Windows explorer.
• BalanceWare Formula Builder - A formula building assistant.
• Help - Online help facility.

The workbook is originally created by BalanceWare for a given client. Thereafter, the workbook becomes autonomous; that is, it is able to connect to any client on your network without further assistance from BalanceWare software. This an important concept if you are using the workbook for financial statement notes at year-end, or with any other workbook that you would like to carry-over from year to year.

To carry-over a workbook from one year to the next, open the workbook and do a Save-As. Then use the Connect button on the BalanceWare ribbon to connect to the proper client file. Alternatively, you can use explorer to copy and paste the workbook, and then connect.

The bw Formula - =bw([Data],["Acct,Acct,etc"],[Year])

The BalanceWare formula is divided into four segments.

• The first segment is the formula itself, "bw", followed by a left-parenthesis. Progress example =bw(
• Data Segment - this represents the type of data to be returned. A complete list of data types can be found below. A typical data type would be an account balance. The data segment is required for all formulas. In the formula above, "[Data]" would be replaced by the data type, in quotes, followed by a comma. For instance, the data type variable for the account balance is "ab". Progress example =bw("ab",
• Account Segment - The account segment is only required for account information, as in this example. This segment can contain one or more accounts. The return value of the formula will be the sum of the accounts. If more than one account, the account numbers are separated by a comma. The entire account segment is wrapped in quotes. Follow this segment with a comma if the fourth segment is used. In this example, two accounts are included in the formula. Progress example =bw("ab","1000,1010",
• Year Segment - the year segment applies to account balances, fiscal year beginning and ending dates and financial statement lines. This segment is not required for the current year. A numerical one occupies this position for the 1st history year, a 2 for the second and so forth, through the 4th history year. Final formulas for the current year balance in accounts #1000 and #1010: =bw("ab","1000,1010"). The same formula for the prior year is =bw("ab","1000,1010",1)

The Formula Builder

The Formula Builder is a floating form that creates and pastes bw formulas. The formula is created from three combo-boxes, representing the argument segments of the bw formula. The first box contains the Data type selections, the other two boxes contain the chart of accounts and year options. The chart of accounts combo box is a multi-select box.

Account balances can be reversed and the target cell is formatted for two decimal places with the thousands separator. Dates can be formatted as a short date or a long date.

The formula, by default is pasted with an equals sign at the beginning for pasting entire cell contents. The Copy to Clipboard button does not have an equals sign at the beginning of the formula, to accommodate pasting into the Excel formula editor.

Data Variable	Data	Type	Format	Add'l Arguments
ab	Account Balance	Number	#,###.00;(#,###.00)	Account(s) & Years
an	Account Name	Text		Account
cn	Client Name	Text
ye	Last day of fiscal year	Date	mm/dd/yy or mmmm dd, yyyy	Year
yb	First day of fiscal year	Date	mm/dd/yy or mmmm dd, yyyy	Year
##	Financial statement total line	Number	#,###.00;(#,###.00)	Year

The data variable ## is a pre-numbered financial statement line assigned dynamically when the workbook is created. These numbers may be different from client to client, making the Formula Builder an invaluable too.

The ab and ## data variables expose every accounting balance in the client's file. The an and cn data variables expose account names and the client name. The ye and yb data variables retrieve the fiscal year dates for the current and the historical years.

Page Setup - How We Setup the NTFS Sheet For Printing 

The NTFS worksheet has the following print settings.

Font: Calibri 11

Column width: 14.14

Page Layout: Margins: Excel's "normal" setting

Page breaks: set manually in View/Page Break Preview

Page Layout/Print Area set to columns A-F

Page Layout/Print Titles/Lines to repeat at top set to $1:$3

---

Support Ticket 

 

Submitting a support ticket is the best way to expedite your issue. The support ticket automatically includes information we need to service your installation properly. By submitting a support ticket, we won't have to ask you all the preliminaries and, if you add a brief description, we will be better prepared to get to the heart of the matter ! We receive your ticket immediately upon submission and will respond ASAP; usually within minutes. Submit your support ticket from the About form on the Help tab.

 

 

Download AeroAdmin remote desktop software here.