Overview

The Flex Excel Addin 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.

The Excel Addin is installed for the current user, do not run the setup as another user account. Please make sure both Windows and Microsoft Office are fully updated. You may also need to temporarily disable any firewall or virus scanner during the installation.

Downloading & Installing

This addin will only work with the Windows Desktop version of Excel 2013/2016/365. Excel Online and Excel for Mac are not supported. You may need to speak to your IT people about installing the software if you don't have sufficient access rights.

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


Uninstalling

You may need to speak to your IT people about removing the software if you don't have sufficient access rights.

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

Uninstalling the addin does not cancel your subscription. See Cancelling a subscription for more details.

Logging in to your account

  1. Select either the QBO 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

When you get data, Excel will connect securely and directly to the API of QuickBooks Online and/or Xero. Your data is never stored at, cached at nor passed through our servers (only your connection details are stored). These API's are constantly evolving and we will release updates to Flex as new data becomes available. You can see what data is available via these API's by looking at their public documentation here:
QuickBooks Online API Documentation
Xero API Documentation

Whilst we do our best to keep Flex up to date with changes in these API's, if you find some data is available in the API but not in Flex, send us an email at support@flexfinancialreporting.com and we'll try add it for the next release.

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.

Filtering data can be a great way to speed things up. If you find a report running slow, look for ways to reduce the data by using filters.

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

Using the Sheet reference can be handy if you want to adjust dates in a report and rerun it. When you press Refresh All, the filter will lookup those dates and rerun the query. You can point multiple tables to the same Sheet reference.

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.

The sheet reference does not work with worksheets that have a space in the name. Also, if you reference a worksheet and change the worksheet name, this break the Sheet reference (you will need to use Refresh Selected to update the Sheet reference).

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 QBO 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 Selected also lets you configure tables created with the Join Tool and Combine Tool.

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)

If you rename a table, Flex will ignore it and you will not be able to refresh it again.

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.

Unticking the Reset Table option can cause performance issues on large tables.

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 able using any column though you might want to stick to ID columns for best results.

Generally you'll want to join a parent and child record. For example, if you were using Xero and you were interested in getting more details about who the contact was on an invoice, you would use the join tool to join the Contacts table (Contact ID column) to the Invoice table (Contact ID column).

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.

Use Refresh Selected on the joined table to adjust the Join Tool configuration after you have run it. You can also use Refresh All to refresh all data and update the joined table.

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.

The Combine Tool is ideal for consolidated reporting. Use the grouping column to group by company, franchise, region etc.

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.

Use Refresh Selected on the combined table to adjust the Combine Tool configuration after you have run it. You can also use Refresh All to refresh all data and update the combined table.

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 addin As Office.COMAddIn              
Dim automationObject As Object
Set addin = Application.COMAddIns("FlexFinancialReporting")
Set automationObject = addin.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);
          
            
You can find VBA sample programs in included 'VBA Macro' sample report found under Xero > Samples > Other > VBA Macro.