Tuesday, 25 April 2017

Blink Reports for Xero Accounting now supports Repeating Invoices

We've made cash flow forecasting easier with the addition of support for retrieving Repeating Invoices from Xero in Blink Reports:
=XeroRepeatingInv(ReCalc,Company ID,Invoice Type,Invoice Status,From Date,To Date,Tracking Category 1,Tracking Option 1,Tracking Category 2,Tracking Option 2,Include Line Items)
Both Sales Repeating Invoices and Purchases Repeating Invoices are supported.

Test it out now by opening the Blink Reports Template and make a copy to take advantage of the new functionality with your own data.

Monday, 13 February 2017

The Google Sheets' QUERY formula is a powerful way to analyze your Xero Accounting data with SQL like commands

Google Sheets has a fantastic built in SQL-like Query function that, when combined with Blink Reports, can instantly answer questions like; what are my total sales by Customer?

The syntax is:
QUERY(data, query, [headers])
The documentation is available at: https://support.google.com/docs/answer/3093343

You can see live examples by opening our Blink Reports Template and clicking on the "XeroInv Query" tab/sheet.

A simple example that calculates total sales by Contact (Customer) based on the output of the Blink Reports custom formula XeroInv() is:
=query(XeroInv!$A$11:$AF$999,"select B, sum(I) group by B",-1)
or expand it to exclude blank rows and use your own column labels:
=query(XeroInv!$A$11:$AF$999,"select B, sum(I) where B != '' group by B label B 'Customer', sum(I) 'Total Sales'",-1)
  • XeroInv!$A$11:$AF$999 is the location of the data in the spreadsheet which in this case is the results of the XeroInv() formula on the sheet named XeroInv
  • "select B, sum(I) where B != '' group by B label B 'Customer', sum(I) 'Total Sales'" is the actual query using Google Visualization API Query Language which is similar to SQL.
  • In this example it outputs the Contact from column B and then calculates the sum by Contact of column I.
  • where B != ''  filters out blank contact records
  • label B 'Customer', sum(I) 'Total Sales' provides more friendly column names
  • -1 means output 1 or more rows of headers depending on the source data
This formula will produce the output:
CustomerTotal Sales
Bank West1299
Basket Case914.55
Bayside Club234
Boom FM1623.75
City Agency593.23
City Limousines1191.83
DIISR - Small Business Services1077.14
Hamilton Smith Ltd2173.75
Marine Systems396
Petrie McLoud Watson & Associates1407.25
Port & Philip Freight1082.5
Rex Media Group1632.5
Ridgeway University12375
Young Bros Transport1082.5

You can also produce pivot tables such as total Sales by customer but split by invoice status across the columns:
=query(XeroInv!$A$11:$AF$999,"select B,sum(I) where B != '' group by B pivot E label B 'Customer',sum(I) 'Total Sales' ",-1)
Which produces a pivot table:
CustomerAUTHORISED Total SalesPAID Total Sales
Bank West1299
Basket Case914.55
Bayside Club234
Boom FM1623.75
City Agency593.23
City Limousines1191.83
DIISR - Small Business Services838.94238.2
Hamilton Smith Ltd5501623.75
Marine Systems396
Petrie McLoud Watson & Associates1407.25
Port & Philip Freight1082.5
Rex Media Group5501082.5
Ridgeway University6187.56187.5
Young Bros Transport1082.5
Unlike Microsoft Excel, this query formula is built in so there are no software plugins or addons to install and keep up to date. Google Sheets make sharing and collaborating on these reports with your team as simple as sending them a web page link since they only need a web browser to view them.

The query formula combined with Blink Reports offers endless possibilities for slicing and dicing your sales or purchase transaction data from your Xero Accounting Software.

Sign up today for a Blink Report free trial at http://www.blinkreports.com/

Friday, 15 January 2016

Xero integrates Google mail - A Potent Mix

Since Blink Reports for Xero users are also Google Apps users, this may be of great relevance to you. Here is a recent update from Xero that is too good not to share.

It's often difficult to get a clear picture of where you stand with a particular customer. It's no surprise that our favourite tool to keep track of this is Xero. With over half a million global subscribers and counting, Xero provides online accounting, bank reconciliation, invoicing services, and integrates with a wide range of powerful software solutions and add-ons. Now, with the integration of Google's Gmail, it's more powerful than ever.
Just before the end of 2015, Xero pushed their final major update of the year: Contacts were updated and Google's Gmail service was integrated into the contacts screens. Essentially, this gives Xero users complete visibility of the business they've done with their customers or suppliers, making it much easier to make decisions and develop opportunities. This vital update makes Contacts a hub for customer communication and demonstrates how cloud accounting has opened the doors of opportunity for innovation and efficiency.

Features in this update enable you to:
  • Connect your Gmail account to view emails with contacts directly in Xero. This saves emails to a contacts activity tab, adds email to a new invoice, quote, or bill, and allows you to download attachments to the contact record.
  • View emails between you and your customers in real time.
  • View emails from the email account that you've connected to Xero while they remain invisible to other users in your organization.
  • Share an email with other users in your organization by simply adding it to the contact's activity tab
    You can see here email messages have been pulled from Blair's account using the contact's email address or emails including the words "GotoMeeting.com"
    Connecting Xero to your Google account is simple, here's how:
    1. From the Contacts menu, select All Contacts.
    2. Select a contact.
    3. Under the bar graph, click Email, then click Connect to Gmail.
    4. Sign in to your Google account.
    5. On the permissions screen, select Allow.
    You can find further details on Xero's Business Help Centre page and Xero's blog.

    If you're already using Google apps and are looking for a great accounting solution to bridge the gap between you and your customers, Xero with Gmail is a robust and ideal solution. We at InterlockIT are proud Xero and Google Apps advocates and are more than happy to help make your business's IT solutions a breeze. Be sure to contact us to learn how we can assist you.

    Monday, 16 November 2015

    Using advanced Google Sheets functionality: ArrayFormula

    Since Blink Reports is built on top of Google App Engine, Google Apps Script, and Google Sheets, you can leverage the power and functionality of all three products to work with your financial data in ways you may not have thought was possible before.

    The ARRAYFORMULA function, in conjunction with OFFSET and MATCH, can help you manage dynamic data sets by allowing you to search for a specific piece of information and then manipulating that data elsewhere. In our template, for example, we use an ARRAYFORMULA on the Operating Expenses Extract sheet to pull data from the XeroPL sheet, which (by default) shows the profit and loss report for a single month.

    The old-fashioned way to do this would be to use hard-coded lookups on each individual row in the Operating Expenses Extract worksheet. While this does work, it makes any changes difficult to manage and can increase the amount of time you spend finessing the spreadsheet instead of focusing on the work you need to do.

    Instead, by using the ARRAYFORMULA, we are able to pick any worksheet as a source, with any date range, and generate the report you see below.

    This chart will now be automatically updated when any changes to the source data occur. Even if a hundred new line items appear in your P&L report, you'll see them in the Operating Expenses Extract right away, without any additional work needed on your end.

    Wednesday, 11 November 2015

    Blink Reports now supports Xero budgeting!

    We've had many requests over the last few months to build in support for Xero's Budget Manager, and we're happy to announce that we've now completed development on this new feature and it's available for all Blink Reports users right now!

    From here, you can now manipulate your budget numbers, compare them directly to your account balances and transactions, and make any changes as necessary to get the results you're looking for.

    This feature is now available to all new and existing Blink Reports users, and highlights the massive productivity improvements you can get by using the reporting engine on Google Sheets. Interested in taking Blink Reports for a spin? Click here to sign up for a free two-week trial and you'll be able to see this brand new functionality right away!

    As ever, we make lots of behind-the-scenes improvements to make your Blink Reports experience better, so keep checking back to see if anything else you're looking for has been implemented.

    Have a suggestion, feature request, or question? Email us at feedback@blinkreports.com.

    Tuesday, 30 June 2015

    New Google Sheets functionality upgrades Blink Reports automatically

    One of the biggest benefits of taking advantage of Google's massive infrastructure and the resulting improvements in services, user interfaces, and more, is that we're able to bring those improvements directly to your reports without any work on your end. Google rolls out some new functionality, and you've got that same functionality right away.

    In mid-June this year, Google introduced a number of enhancements directly related to data manipulation and charting. You can now add data labels to display the exact value of data points in a number of charts; we've integrated this into our standard template now, which you can see in the image below.

    New data point value display thanks to a Google Sheets upgrade.
    Since Blink Reports uses a fairly large number of formulae to return your data in an easily-readable format, Google's new in-line formula preview makes adjusting our formulae to work with your data easier than ever, as you can now see exactly what your formula will return while you're writing it.

    Updated permissions for protecting cells or ranges have now also been integrated into our templates, to ensure that our formulae can keep looking at the correct data.

    It's thanks to the ever-evolving nature of Google's products that we're able to bring you such a powerful reporting engine and constant updates without you needing to lift a finger. Google ships it, and you've got it right away.

    Monday, 5 January 2015

    Happy New Year from Blink Reports!

    Here are a few facts:
    • Blink Reports continually gets better because we built it on the Google Sheets and Google App Engine platform. 100% of our Blink Reports code runs inside the Google Cloud Platform meaning it will automatically scale to match customer demand without us having to manage server instances or configure load balancers. Google does it all for us.

    • Blink Reports data is secured by the massive resources and security team of Google. If you'd like to further secure your Blink Reports data from hackers or malicious websites that might figure out your password simply enable 2-step Verification on your Google Account... Sony should have had this.
    • Google is constantly improving the speed and features of Google Sheets on your web browser and on your Android Tablet or iPad. Blink Reports is accessible anywhere and on any device.
    If you feel that Blink Reports is missing something please Contact Us. We're always open to doing custom development on our Blink Reports platform to meet your needs.