Overview

The Flex Excel Addon adds some new tabs to the ribbon bar in Excel. Using these new tools, you can query data from your Quickbooks Online and/or Xero accounts.

Downloading & Installing

1. Login to your Flex account
2. Click Download the Excel Addon
   
3. Follow the prompts until done and start Excel.

Uninstalling

1. Close down Excel
2. Open Add or Remove Programs and search for FlexFinancialReporting
3. Select it and click Uninstall

Logging in to your account

1. Select either the QuickBooks or Xero tab
   
2. Click Update under the connection list
   
3. Log into your Flex Account
   
4. Your connection list should refresh with all the connections that you've setup.
   

Getting Data

After you have logged into your account and selected a connection, you can click any of the tables to get data.

Once you click on a table, you will see a filter screen that will allow you to narrow down the data that you request (See Table Filters). Some tables may have nested data e.g. in Xero, Invoices can have Credit Notes, Invoices Lines and Payments. Nested tables will generally show a few key columns from the parent table as well as columns from the nest data; this makes reporting a lot easier.

When you click OK on the filter, Flex will create a table with your data.

Table Filters

When you select a table, you will get a filter that allows you to limit what data you get from either QuickBooks Online or Xero. Available filters are dictated by what the API supports; therefore some tables have better filtering than others.

Some filters allow you to set date ranges, these can very useful.

The ranges available are:

• Default - This will return whatever the API uses as default. This change on a table by table basis
• Last 30 Days - From 30 days ago until now
• This Quarter - The current financial quarter
• This Year - From the 1st of January of the current year
• Custom - Allows you to enter the dates
• Sheet - Use an Excel sheet reference to set the date

Using Sheet references to set the date

When you select the Sheet option in a date range filter, click the box(es) below to open the Select Range tool.

Then either type in or click the cell with the appropriate date and click OK. Repeat this if necessary if there are two dates.

When you're done, the sheet reference will be saved in the filter. You can click them again if you want to change them.

Once you're done, press OK and your table will be created. You can see a summary of the filters on an existing table by hovering your mouse over the cell in the top left.

Save Organisation

Save Organisation works when you run Refresh All. It will remember what company you had selected in the connection list and refresh data from that company instead of the current one. This is useful for when you want reports for specific companies.

Refreshing Data

Once you've got some data or built a report, you'll want to refresh it with the latest data. You can find the Refresh menu in both the QuickBooks and Xero tabs, they are just here for convenience and do exactly the same thing.

Refresh Selected

Refresh Selected will open the table filter of the currently selected table and allow you to refresh the data. To select a table, click anywhere within the bounds of that table. This will allow you to adjust the table after it has been created. Press OK on the filter to run the table again and data will be refreshed from the currently selected company.

Refresh All

Refresh All will rerun all tables using the filters last used. Data will refresh from the currently selected company unless you ticked the option for Save Organisation. Refresh all will update in the following order:

1. All Flex Tables
2. All Flex Combined Tables
3. All Flex Joined Tables
4. All Excel data sources (e.g. pivot tables)

Reset Table

The Reset Table option can be found under the Flex tab in the Options menu.

With this option ticked, any removed columns will be restored and new columns will be removed; the table will reset back to the default columns. With the option unticked, you can remove columns, move them around and even add new columns with calculated data.

Join Tool

You can find the join tool in the Flex tab of the ribbon bar.

The Join Tool allows you to connect two tables together and make a new joined table by linking them with a matching column on each table. If you're familiar with SQL, this is similar to an INNER JOIN. You can join any table using any column though you might want to stick to ID columns for best results.

Click on the box labelled table to open a Select Range tool, then click the column you want to join from.

Then repeat for the column you want to join to. By default, Flex will choose the table and column you have clicked, but you can adjust the column from the dropdown list.

Then press OK and a new Joined table will be created which all includes columns from both tables.

Highlight Unmatched

Ticking the Highlight Unmatched option will colour all cells that could not be matched between the two tables

Partial Match

Partial match will match the right table if it can be partially found on the left table. Use this when part of your matching criteria is in the same cell as other data. In the picture below, even though the Account Name and Account Code are in the same cell, we can still do a partial match on the Code to the Code on the Accounts table.

Combine Tool

You can find the join tool in the Flex tab of the ribbon bar.

Use the Combine Tool when you want to combine several similar tables together. Tables will be appended to each other and an additional column will be added as a grouping identifier.

Here we'll step through how to create a consolidated P&L through Xero. First, create 2 or more P&L reports from different companies (make use of the Save Organisation option in the filter, this will allow you to refresh all data in the workbook at anytime using Refresh All). Then click the Combine Tool button.

The Group Name is the name of the column that will be added to the new table. For this example, each of our tables that we want to combine are from a different company, so we'll enter 'Company'. In the box beside table, click on it to open the Select Range Tool then click on one of the P&L tables and press OK. Next we have to enter the Group Value. The Group Value is the cell data that will appear under the Group Name column for every row in this selected table. For this example we'll type in the company name then Press Add/Edit Table.

Repeat these steps for each table, then press OK.

A new table will be created with all tables you configured in the Combined Tool plus the grouping column.

This makes it easy to create Pivot Tables and collate data into one report.

Separate Tool

You can find the join tool in the Flex tab of the ribbon bar.

The Separate Tool allows you split any table by the distinct values on a selected column into separate tables on new worksheets. For example, to split the invoices by Status, click anywhere on the Status column, then click the Separate Tool. This will then create a worksheet with a table for each of the distinct Status values.

VBA & Automation

You can automate some of the features of Flex using VBA. To use the COM+ interface, use the following code snippet.

            
Dim addon As Office.COMAddIn              
Dim automationObject As Object
Set addon = Application.COMAddIns("FlexFinancialReporting")
Set automationObject = addon.Object
                
            

The following methods are available.

            
void RefreshAll();
bool DownloadAllAttachments(int table, string id, string root = null);
bool DownloadInvoice(string id, string root = null);
string[] GetXeroConnections();
string GetXeroConnection();
void SetXeroConnection(string Connection);
object GetProperty(string table, string property);